Integrating BigQuery with Google Analytics 4

Integrating GA4 with BigQuery.

Let me start by saying that you need to do this.  BigQuery is such a fundamental part of Google Analytics 4, that neglecting to enable the integration is like eating nachos without chips.  You don’t want to be that guy at the superbowl party just spooning queso into your mouth, do you? 

BigQuery is Google’s fully managed data warehouse, and the Firebase integration gives you access to all of your data in a format that can be analyzed and joined with other sources.

LAST UPDATE (Dec 30, 2021)

Updated recommendations for companies who are not using Firebase.

Contents

OPTION 1: How to Set Up the Integration from Firebase

If you’ve read my post about How to Set Up a Google Analytics 4 Property then you know that I recommend everyone with an app to create projects in the Google Cloud Platform and Firebase when building your GA4 property.

If you've followed these instructions, then you will want to enable the BigQuery integration from the Firebase Console. This is because the enabline from the Firebase console passes data from all of your Firebase services (not just the Analytics data).

Simply choose “Project Settings” > “Integrations” > “BigQuery” (NOTE: You do not want to use the extension “Export Collections to BigQuery” because this is something different) and follow the instructions.

Integrate Firebase with BigQuery

Wait! There’s One More Step for Web Streams. If you have only set up a web stream in your Firebase project, it will not export any data and you’ll see this message on the “Integrations” page.

Project not linked

This is sort of a bug that only exists because web streams are new, and surely it will be fixed soon. But, to get around this all you need to do is follow a few short steps:

  1. Go to the “Project Overview” page, click “Add an App” and select “iOS”

    Add an app

  2. Create an iOS bundle ID (it can be anything. I like to use “com.this.isDumb” as shown below) and click “Register App”.

    Create an iOS bundle ID

  3. That’s it. Now just click the Back button to skip the rest of the setup steps and return to the “Project Overview” screen. Then navigate back to “Integrations” and click “Manage” in the BigQuery card. You’ll now see your iOS app with a radio button next to it. You must flip this on to enable the export.

    Enable streams radio button

OPTION 2: How to Set Up the Integration from Google Analytics

If you have not created a Firebase project and linked it to your Google Analytics 4 property, then you can follow Google's well documented instructions here:

See Google's Official Documentation

When does data show up in BigQuery?

Tomorrow. After about 24 hours you’ll see a new dataset in BigQuery with two groups of tables.

BigQuery Tables

  • Daily Tables [follow the naming convention “events_”]: Similar to the BigQuery integration with GA360, every day the prior days data will be added as a new table with the date as the suffix of the table name.

  • Intraday Tables [follow the naming convention “events_intraday_”]: If you need real-ish-time data, the intraday tables will give you a glimpse of the current day. However, beware that your web streams currently do not appear in the intraday tables.

Set Table Expiration

By default your data will be set to delete after 60 days, but you can modify this in BigQuery by clicking on your dataset name ("analytics_") and selecting the pencil next to "Dataset Info".

Set table expiration in BigQuery

From now on, new tables will be created daily without an expiration date. But if you already have tables in your dataset then you'll need to click on each one and manually change the expiration.

What does the data look like?

Each row in the table represents an event that has undergone some light processing (it’s not completely raw data). Since an event can have multiple parameters each row is stored in the JSON format you may be familiar with from Universal Analytics.

I've written another article called Using BigQuery and DataStudio with Google Analytics 4 to help get you started. You can also see the full export schema HERE.

What does it cost?

Billing is calculated by the amount of data you store and the processing required to run your queries (more here). This is extremely vague, which is a big blocker for many companies, but just know that there is a very generous free tier (I've never been charged for my blog) and my average client probably pays about $200 per month.

It’s worth it — especially considering that Google Analytics 4 is free and you previously had to pay $150K/year for GA360 to get the BigQuery integration. Also, you can test it out on a small site to take advantage of the free tier, and you can set billing alerts so you will not get a surprise bill.