Integrating BigQuery with Google Analytics for Firebase

Let me start by saying that you need to do this.  BigQuery is such a fundamental part of GA4F, 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 queried and joined with other sources.

How To Set Up the Integration

I have two caveats before we begin:

  1. If you’ve read my post about How to Set Up an App + Web Property then you know that I recommend everyone create projects in the Google Cloud Platform and Firebase before building your App+Web property in Google Analytics.  At the time of this post, you can only enable the BigQuery integration from your Firebase project.  
  2. To use the BigQuery integration you must be on at least the “Blaze” plan.  This is so there is a credit card on file in case you need to be charged (see “What does it cost” under FAQ’s below).  

Otherwise, all you have to do to enable the BigQuery integration is find your project in the Firebase Console, choose “Project Settings” > “Integrations” > “BigQuery” (NOTE: You do not want to use the extension “Export Collections to BigQuery” because this is something different).

From here it’s pretty simple to follow the instructions to enable the integration.  You’ll notice that the integration does not just pass data from Firebase Analytics, but data from several other services too.

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_<property ID>”) and selecting the pencil next to “Dataset Info”.

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.

Wait!  There’s One More Step for Web Streams.

Now, here’s something really silly.  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.

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

The integration is now set up, and you’ll need to wait 24 hours before you can verify that data is transferring properly.  In the meantime take a look at the following frequently asked questions.

FAQ’s

When does data show up in BigQuery?

  • Tomorrow, you’ll see a new dataset in BigQuery with two groups of tables.
    BigQuery Tables
  • Daily Tables [follow the naming convention “events_<date>”]: 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_<date>”]: 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.

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. 
  • If this is new to you, definitely take a look at the query library that I’ve posted to help you get 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 my average client probably pays about $200 per month. 
  • It’s worth it — especially considering that Firebase Analytics is free and you previously had to pay $150K/year for GA360 to get the BigQuery integration. 
  • Also, there is a free tier if you want to run it on a small site to test it out, and you can set billing alerts so you will not get a surprise bill.

Once you’re set up, head over to my post on Using BigQuery and Data Studio with GA4F to get started.

Leave a Reply

Your email address will not be published.