Analytics, Technology

The Google Analytics and BigQuery Integration

Estimated Reading Time: 6 minutes

Intro

One of the key benefits of Google Analytics 4 (GA4) and Google Analytics 360 (GA360) is the integration between Google Analytics (GA) and BigQuery (BQ), a cloud data warehouse within Google Cloud Platform (GCP). The integration provides the complete, hit-level dataset from Analytics which allows marketers to do their own analysis and modeling. In this article, we’ll provide an overview of the benefits of the integration, how to set it up, what to expect, and common mistakes.

Note: this article covers the integrations with BQ for both GA360 and GA4, the next generation of GA. When we refer to GA, the information is relevant for both platforms; when there are differences between the platforms, we’ll call them out explicitly.

For more advanced topics related to the integration, check out our other posts: Getting Started with BigQuery for Marketers, Data Concepts and How They Apply to BQ, and An Overview of the Benefits of BigQuery. Stay tuned for additional upcoming posts: I Just Connected Google Analytics to BigQuery – Now What? and Getting Started with Audience Targeting using GA Data.

Why you should integrate GA with BQ

There are several benefits to setting up the integration between GA and BQ. First, it’s a key step toward greater analytics maturity. You own your analytics data in BQ and since it’s a first-party dataset, you can use your GA data as part of a durable digital marketing strategy.

Additionally, having the complete, hit-level dataset creates opportunities for advanced analysis and modeling (no sampling!), integration with other datasets or platforms, and visualization/dashboarding. With those insights in-hand, marketers can focus on activation via audience targeting to reach customers and provide better experiences.

Finally, for more ideas of how to get value from GA data stored in BQ, check out these case studies:

How to set up the integration

There are three key steps for setting up the integration between GA and BQ. For complete instructions, see Google’s documentation: GA4, GA360.

1. In GCP, create a project (if necessary) and enable BQ.

1. All activities in GCP must belong to a project so the first step is to create a project (if one does not already exist) and enable BigQuery.

2. Add the service account as a member of the project.

1. A service account is a non-human account that is used to run automated services.
2. For both GA4 and GA360, the service account needs to have Editor access for the project.

- - - For GA4, the service account is firebase-measurement@system.gserviceaccount.com.

- - - For GA360, the service account is analytics-processing-dev@system.gserviceaccount.com.

3. Link to billing account.

1. Enable Billing in GCP, create a billing account, and link to the GCP project.
2. There is a free tier of usage in GCP that may cover the data storage and processing costs associated with the GA export but you do need to have a form of payment on file in Cloud to complete the integration.

4. Configure the linking in the GA UI.

1. For GA4, from the Admin screen in the GA UI, click on Product Linking in the Property column. Select the GCP project, data location, data streams to export, and export frequency (daily or continuous streaming). Congratulations—you’re done!
2. For GA360, from the Admin screen in the GA UI, click on All Products in the Property column and then click on Link BigQuery. Enter the BigQuery project ID or number (found on the Home screen of GCP), select the view to export to BQ, and choose the export frequency (three times per day or continuous streaming). Congratulations—you’re done!

For GA360:
- Dataset: The dataset will be labeled XXXXXXXXX, where XXXXXXXXXX is the UA view ID.
- Tables: Within that dataset, there will be ga_sessions_YYYYMMDD tables, one for each day. You’ll also see the ga_sessions_intraday_YYYYMMDD table that contains data for the current day (exported three times per day).
- Streaming tables: If you enabled streaming, instead of ga_sessions_intraday_YYYYMMDD, you’ll see ga_realtime_sessions_YYYYMMDD and ga_realtime_sessions_view_YYYYMMDD for the current day.
- First-time export: Data should start flowing into BQ within 24 hours.
- Daily export: A new table will be created each day that contains the previous day’s data (typically in the morning in the timezone set for reporting).

Historical backfill: The first time you connect a GA property to BQ, GA will provide an export of historical data. Within four weeks, you’ll see GA data in BQ going back 13 months or the last 10 billion hits, whichever is smaller.

Common mistakes

Here are the most common issues we see with our clients when setting up the integration. These errors create permanent gaps in the data exported to BQ.

Inadequate permissions for the user setting up the integration
- The user setting up the integration must have Edit access within the GA UI and access to the Cloud project.
Incorrect permissions granted to the service account
- There are dozens of roles in GCP that can be assigned to users. We’ve seen clients assign BQ Editor access to the service account instead of project-level Editor access.
Billing issues
- The data export will not occur if there is not a valid, up-to-date payment method on file in GCP. As a safety net, you can set up a backup credit or debit card.
BQ API not enabled
- BQ is not enabled by default so it’s necessary to enable it via the APIs & Services screen in GCP.

Next steps

Now that you’ve completed the integration between GA and BQ, it’s time to get to work! Check out our other posts about how to get value from your analytics data in BQ.