The Google Analytics and BigQuery Integration

The Google Analytics-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.
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.

More questions on how to get started with your GA data in BQ?

Submit a Contact Us form to talk with one of our activation specialists.

Author

  • Pam Castricone

    Pam Castricone is currently an Emerging Solutions Strategist at InfoTrust. Specializing in statistical and machine learning models, Pam is passionate about helping her clients uncover greater insights and value from their data assets. As a Google Cloud Certified Professional Data Engineer, Pam also helps her clients put their models into production in the cloud to drive long-term usability and success. When she isn’t analyzing data, Pam enjoys reading, the arts, and going out for brunch.

Facebook
Twitter
LinkedIn
Email
Originally Published: November 4, 2021

Subscribe To Our Newsletter

February 1, 2023
Originally published on November 4, 2021

Other Articles You Will Enjoy

How Does BigQuery Data Import for Google Analytics 4 Differ from Universal Analytics?

How Does BigQuery Data Import for Google Analytics 4 Differ from Universal Analytics?

All Google Analytics 4 (GA4) property owners can now enable ‌data export to BigQuery and start to utilize the raw event data collected on…

2-minute read
Leveraging Custom Dimensions and Metrics in Google Analytics 4 for Content Performance Measurement: Best Practices and Real-World Examples

Leveraging Custom Dimensions and Metrics in Google Analytics 4 for Content Performance Measurement: Best Practices and Real-World Examples

In today’s digital landscape where content reigns supreme, understanding how your audience interacts with your content is paramount for success. For news and media…

5-minute read
Predictive Analytics in Google Analytics 4: How to Use Machine Learning to Forecast User Behavior and Outcomes

Predictive Analytics in Google Analytics 4: How to Use Machine Learning to Forecast User Behavior and Outcomes

Google Analytics 4 (GA4) is embracing the power of machine learning by incorporating predictive analytics within the platform so that you can use your…

7-minute read
What Is Consent Mode in Google Analytics 4 and How Does It Work? | A Beginner’s Guide

What Is Consent Mode in Google Analytics 4 and How Does It Work? | A Beginner’s Guide

Consent Mode in Google Analytics 4 (GA4) is a helpful tool for website owners to respect user privacy preferences when it comes to tracking…

3-minute read
App Install Attribution in Google Analytics 4: What You Need to Know

App Install Attribution in Google Analytics 4: What You Need to Know

App install attribution in Google Analytics for Firebase (GA4) is a feature that helps you understand how users discover and install your app. It…

6-minute read
How to Integrate Google Analytics 4 with BigQuery for Enhanced Data Analysis and Reporting

How to Integrate Google Analytics 4 with BigQuery for Enhanced Data Analysis and Reporting

Has your business found that its reporting needs require advanced analysis of your analytics data beyond what is practical in the Google Analytics 4…

4-minute read
How Data Maturity Can Cultivate a Data-Driven Culture

How Data Maturity Can Cultivate a Data-Driven Culture

Data-driven decisions are a buzz topic in Martech. It is essential for C-suite executives to understand and more importantly, use their data to move…

4-minute read
Leveraging Attribution Models in Google Analytics 4 to Improve Your Marketing Strategy: Tips and Best Practices

Leveraging Attribution Models in Google Analytics 4 to Improve Your Marketing Strategy: Tips and Best Practices

In the dynamic landscape of digital marketing, understanding the customer journey is crucial for optimizing strategies and maximizing ROI. Google Analytics 4 (GA4) introduces…

5-minute read
Google Analytics 4 Implementation Checklist: Ensure You’re Tracking Everything You Need

Google Analytics 4 Implementation Checklist: Ensure You’re Tracking Everything You Need

In the dynamic landscape of digital marketing, data is supreme. Understanding user behavior, preferences, and interactions on your website is crucial for making informed…

4-minute read

Get Your Assessment

Thank you! We will be in touch with your results soon.
{{ field.placeholder }}
{{ option.name }}

Talk To Us

Talk To Us

Receive Book Updates

Fill out this form to receive email announcements about Crawl, Walk, Run: Advancing Analytics Maturity with Google Marketing Platform. This includes pre-sale dates, official publishing dates, and more.

Search InfoTrust

Leave Us A Review

Leave a review and let us know how we’re doing. Only actual clients, please.