Enable BigQuery Export For Google Analytics: App + Web Properties
Update 7 October 2020: BigQuery Export can now be configured via the property settings of Google Analytics: App + Web, so you don’t need to follow the steps in this article. Check out Charles Farina’s guide for how to do this.
Here’s yet another article inspired by the fairly recent release of Google Analytics: App + Web properties. This new property type surfaces Firebase’s analytics capabilities for websites as well, when before they were restricted to mobile apps only (see my guides for iOS and Android).
Even though the feature set of Google Analytics for Firebase is still somewhat bare in the user interface, here’s a perk which might push you over the edge and give the new measurement model a shot.
BigQuery export is available with no extra cost!
OK, there’s one caveat: you will of course need to pay for BigQuery usage and you’ll need to upgrade to the Firebase Blaze plan. But you won’t have to dish out some $150K a year to have access to raw data, and the free tiers of Google Cloud are extremely generous, so you might end up not having to pay at all for this wonderful storage!
Note! There’s no saying this is a permanent state of affairs. Once Google Analytics: App + Web is out of beta, it’s possible some sort of tiered pricing is introduced. But let’s enjoy this while we can!
The steps you’ll need to take to enable the export are outlined in this article. Remember to check out the BigQuery Export Schema for Firebase so you’ll know how the data will be aligned in the BigQuery table.
XThe Simmer Newsletter
Subscribe to the Simmer newsletter to get the latest news and content from Simo Ahava into your email inbox!
Step 1: Check your Google Analytics: App + Web property
First step is obvious - you need a Google Analytics: App + Web property to be able to export data out of it to Firebase.
If you haven’t created one yet, follow these steps to do so.
To verify it’s up and running and integrated to Firebase, go to the Firebase console, open your project, and go to Project Settings.
Click over to Integrations. You should see Google Analytics in the Enabled state, but click Manage to verify.
Here you should make sure all the details look correct. At this point, make a note of the Property ID in case you want to decide in which geographic location (e.g. US, EU) you want to create the BigQuery dataset in.
Now, head back to the Firebase Project Overview dashboard.
Step 2: Create a dummy app
This is kind of silly, but for the export to work, you need to create an app in the project. The app doesn’t have to do anything, nor do you have to verify it or validate it, it just needs to exist.
The reason for this is that the BigQuery export was created before the web stream concept was introduced with Google Analytics: App + Web, and in its current state, having just a web stream will not enable the export.
I’m certain this will change at some point in the future.
In the dashboard, click the iOS button to create a new iOS app.
Give the app some bundle ID - it can be anything you want. I used com.example.dummyapp. Click Register app.
You can now ignore all the instructions and just click the Next button until you reach the final step. Here, click the Skip this step link to create the app.
Congratulations, you’ve created an iOS app (har har).
Step 3: Upgrade to Blaze plan
To be able to export the data to BigQuery, you’ll need to upgrade your Firebase account to use the Blaze (pay as you go) plan.
Click the Upgrade link in the bottom of your Firebase navigation.
Next, click Select plan in the Blaze column.
Now, you will need to choose a Billing Account configured for your Google Cloud organization / login. If you don’t have one yet, you will be prompted to create it. You will need your credit card for this!
Click Continue and Purchase to confirm that you will use the selected billing account for any charges incurred by your Firebase and BigQuery usage.
Step 4: (Optional) Create the BigQuery dataset
In case you want to store the BigQuery data elsewhere than the United States, you can actually create the BigQuery dataset beforehand, choosing the data storage location that way.
If this is what you want to do, you’ll need to visit the BigQuery console for your project, so open the console in your browser.
First, make sure you have the correct project selected from your project selector. The project should match the name of your Firebase project (www-simoahava-com in my case).
In the BigQuery project navigator you should find your project as well. The BigQuery project will have the same name as your Firebase project ID (simoahava-com for me).
Click the project to open its datasets (there should be none).
Now, click CREATE DATASET in the right-hand side of the dataset explorer.
In the overlay that opens, you need to type in the Dataset ID. The Dataset ID must be named in the following format:
analytics_<GA property ID>
This is where the Property ID from step 1 comes into play, as you’ll need to name the dataset accordingly.
If you do not name the dataset correctly, the data will not flow into it, and another dataset (one named correctly) will be automatically created for you in the United States.
Now you can also set the location to your liking.
Click Create dataset at the bottom of the overlay when done.
This is what you should see in the dataset explorer view:
Step 5: Set up the export
Finally, you can set up the export itself.
In the Firebase console, head on over to Project Settings and click to Integrations again. This time, in the BigQuery box, click Link.
Make sure you read the fine print in the first step, and click Next when adequately enlightened.
In the next step, you’ll have an overview of what is included in the export. If you want to include “advertising identifiers” in the export, be sure to check the box. It sounded nefarious to me so I chose not to do so.
Click Link to BigQuery when ready.
The next view shows you that the link is now done. Make sure the toggle for your dummy app is ON in the Analytics card. This enables the data export from your web stream to BigQuery.
And that’s it! The day after creating this link, you should see a new table in the dataset prefixed with events_, which will contain all the data for each day of the export, and you can start running those sweet queries on your raw data in no time!
Summary
It’s great that we have this “free” BigQuery export available for our Firebase analytics projects. I really, really hope it won’t be taken away from us. Ever.
In its current state, the user interface and reporting capabilities of Google Analytics: App + Web leave quite a lot to be desired, but the BigQuery export pretty much makes up for the limitations of the UI. Being able to work with row-level data is huge, as it, for example, gives us access to event parameters beyond the 50 each project is allocated by default.
Have fun playing around with BigQuery, and let me know if you had trouble with setting up the export!
