BigQuery Starter Guide: Google Analytics 360, Google Analytics 4 Properties, & Firebase Analytics
Google Analytics 360, the most recent version of Google Analytics, Google Analytics 4 Properties (previously known as App + Web), and Firebase Analytics users will immediately want to get started using Google BigQuery, so we’ve summarized the steps and benefits to get off the ground running. We’ll cover how to enable BigQuery within Google Analytics or Firebase to enable the auto-export of Google Analytics data, plus we’ll provide some resources near the end for querying the data.
General Overview
Google BigQuery is Google’s cloud data warehousing solution which is part of the Google Cloud Platform. It’s designed to handle "big data" reporting, analysis, and data science.
Data can be queried using standard SQL syntax or the legacy BigQuery syntax, and it can be accessed from within the web interface or via API. BigQuery can be used to store and integrate many different kinds of data, though for our purposes we’ll focus on Google Analytics data and the Analytics 360, Google Analytics 4 Properties, and Firebase Analytics integrations.
BigQuery + Google Analytics
If you’ve heard about BigQuery and you’ve read some of our blog posts on it, you likely know that it is capable of much deeper analysis than that of the Google Analytics or Firebase Analytics interfaces.
Google Analytics 360 subscribers, Google Analytics 4 property (formerly known as App + Web) adopters, and Firebase Analytics users can enable an automated, daily export of their raw Google Analytics data. This is a huge benefit for anyone looking to report on or analyze unsampled data as there are limited methods for getting around sampling within the Google Analytics interface. This is also critical for more in-depth, statistical analysis that simply isn’t possible within the Google Analytics interface.
One of the most important details you should be aware of with regards to Google or Firebase Analytics data within BigQuery is the format of the tables. BigQuery is a structured database that allows for nested and repeated fields; which you’ll need to wrap your head around when querying Google Analytics data.
Because Google Analytics data is hierarchical, you’ll often find yourself needing to write a query that accesses values from nested or repeated fields (such as accessing custom dimension values, for example). We have some resources near the end of this post for querying Google Analytics data.
Another important detail about Google Analytics data in BigQuery is the fact that you will need to manually calculate many of the metrics that you see in the interface—they aren’t all provided for you! For instance, total users, total sessions
Costs
BigQuery is included in the subscription costs for Google Analytics 360 users, but can also be accessed by adopting and utilizing Google Analytics 4 Properties or Firebase Analytics projects. While the integration is free, there are costs to storing and querying data in BigQuery, but they are very low. At the time of this post, the current costs are:
- Active Storage – $0.02 per GB/month
- Long-Term Storage – $0.01 per GB/month
- Queries – First 1 TB/month is free. Subsequent queries are $5 per TB
Setting Up BigQuery
The way you set up BigQuery will depend on which Analytics platform you’re using. In the section below, we outline integration to BigQuery for:
Note: If you have both Google Analytics 360 and Google Analytics 4 Properties, you’ll most likely want to set up streaming exports for both analytics tools. This will give you the long-term flexibility to analyze your data using the session-based Google Analytics 360 model and the event-based Google Analytics 4 model.
IMPORTANT: If you are using Firebase Analytics and Google Analytics 4 (e.g., you have Firebase mobile application data streams as well as Google Analytics 4 web data streams), you’ll only need to link your data to BigQuery one time. You can choose whether to link via the Firebase Analytics interface or the Google Analytics 4 interface if you have connected your Firebase Project to your existing Google Analytics 4 property.
If you’ve already linked your Firebase project to BigQuery, you may not be able to access the product linking in the Google Analytics 4 property interface (e.g., the BigQuery icon may be missing). This is because your Data Streams are already integrated into BigQuery and Google Cloud Platform via the Firebase console. Long story short, there’s nothing left for you to do, and you can start using BigQuery right away!
Setting Up BigQuery with Google Analytics 360
There are a few steps you’ll need to take in order to enable and configure a BigQuery project for Google Analytics 360 views. You can see a detailed list of those steps by visiting the documentation on Setting up a BigQuery Export, but here is a general rundown:
1. Create a Google API project – Start by navigating to the Google Developers Console. Create a new project and be sure to enable the BigQuery API.
2. Enable Billing – From the hamburger menu, go to Billing and set up your billing account information:
3. Add Permissions – From the same hamburger menu, select IAM & Admin. The login you used to create the project will already be added as the project owner. Also add the following account with Editor permissions to the project (editor permission on this account is required in order to export data from Analytics to BigQuery): analytics-processing-dev@system.gserviceaccount.com.
Add any other users you would like to have access to the data with "Can view" or "Can edit," or additional administrators with "Is owner."
Enabling the Analytics 360 + BigQuery Integration
This integration is not available for standard Google Analytics properties; you must have the enterprise version, Analytics 360.
You can export only one View per property. You’ll likely want to choose the main production View (which has internal traffic filters, for example).
Compile a list of property IDs and View IDs that you want to import into BigQuery if you have more than one.
The following steps must be taken by the owner of the Developers Console Project—they must also have Edit access to the Google Analytics properties you are trying to link to BigQuery.
1. In the Google Analytics Property Settings, go to Product Linking > All Products.
2. Scroll down to BigQuery and click View Link.
3. Enter the project number. You can find this in the Cloud Console.
4. Back in Google Analytics, select the View you’d like to link from the dropdown. You can only choose one View.
5. Add the appropriate users as contacts and then Confirm the link. Contacts are individuals that should receive email notifications about daily exports and potential problems (you must have Manage Users permissions in Google Analytics in order to add individuals other than yourself as a contact).
That should be it!
Setting Up BigQuery with Google Analytics 4 Properties (App + Web)
If you’ve adopted the newest version of Google Analytics, known as Google Analytics 4 Properties (previously App + Web), you most likely will want to take advantage of the free, native BigQuery Connection.
To begin, navigate to the admin section of your Google Analytics 4 property. Within the Property settings, you’ll find "BigQuery Linking" available under the Product Linking section.
Reminder: If you’ve already linked your Firebase project to BigQuery, you may not be able to access the product linking in the Google Analytics 4 property interface (e.g., the BigQuery icon may be missing). This is because your Data Streams are already integrated into BigQuery & Google Cloud Platform via the Firebase console.
Once you click on the BigQuery Linking icon, you will be prompted to choose a BigQuery Project ID by clicking on the blue "Link" button.
From here, you have two options:
If you or your company already has a BigQuery project (i.e., you’ve used Google Cloud Console before) you can click the "Choose a BigQuery Project" link.
If you do not have a BigQuery project (i.e., you’ve not used Google Cloud console before), you’ll want to follow these detailed instructions to set up the Google APIs Console project and enable BigQuery.
Once you have a GCP project, you can prepare your project for the BigQuery Export. Back in the Google Analytics interface, you’ll click the Choose a BigQuery project link.
This will prompt you to select from several BigQuery projects that you have access to. Once you make a single selection, the "Confirm" button will turn blue, enabling you to go to the next step.
Once confirmed, you’ll see several new features that have never before been options for Google Analytics users, including choosing your data location and configuring your streaming options!
Selecting your data location will determine the Google Cloud region for which your data is stored.
You’ll then choose your streaming options, which include the standard daily export as well as real-time data streaming! With Google Analytics 4 Properties, you may choose either option, or both!
This (very cool!) new feature within our new Google Analytics 4 Properties will provide you with near-real-time access to your data, or continuous exports, in order to conduct more timely analysis.
Note that this feature isn’t available with sandbox accounts and that you’ll need to have a credit card or BigQuery billing account set up to be able to pay for the additional, continuous export. If you don’t have an associated billing account set up, you will be prompted with the message below (streaming only available for Cloud Projects with Billing Enabled).
At this point, you’ve almost made it! Google will give you a second screen to review your selections and click submit. You’ll then need to wait about 24 hours to be able to see this data appear in BigQuery.
Setting Up BigQuery with Firebase Analytics
For mobile applications, Firebase Analytics also offers native integration with Google BigQuery. To begin, you will need to create or use an existing Firebase project to link your project to BigQuery. We’re going to walk through the overview of integrating Firebase Analytics with BigQuery, but you may also refer to this detailed documentation for more information.
Once you have a Firebase Project created and an associated app developed or in place, you’ll want to link this project to Google Analytics if you have associated web data streaming in a Google Analytics 4 (App + Web) property. To do so, open Project settings > Integrations.
On the Google Analytics card, click "Link" and select your existing Google Analytics 4 (App + Web) property if you have one.
If you don’t have an existing Google Analytics 4 (App + Web) property, linking to Google Analytics from the firebase console will create one for you. This will enable you to view additional reports and utilize advanced reporting tools, such as the Analysis hub, so it’s useful to link Firebase Analytics to Google Analytics even if you don’t plan on implementing website tracking.
From here, we will set up the export within Firebase by navigating back to Project Settings > Integrations. On the BigQuery card, click Link.
Firebase will prompt you to read about each aspect of the Analytics export, including free-tier limitations. Once you’ve scanned this information, click Next.
From here, you’ll see several configuration options. By default, all apps in your project are linked to BigQuery and any apps that you later add to the project are automatically linked to BigQuery, as well.
There are (2) export settings available, including enabling advertising identifiers or streams in your export. You may also choose to include streams in the export to switch to control the export of web streams from the associated Firebase project and the associated Analytics App + Web (Google Analytics 4) property.
Once you are satisfied with your selections, click "Link to BigQuery."
Note: If you’re interested in only sending a handful of apps within your project to BigQuery, you can return to settings and click "Manage" on the BigQuery card. From here, click the pencil next to the apps exported, enabling you to manage which apps send data.
From here, you can check or uncheck apps that you’d like to be sent to BigQuery.
Finally, it’s important to note that you’ll need to upgrade to a Blaze plan within Firebase. The Google documentation doesn’t call out this step, but it’s required, even if you intend to stay within the sandbox limits.
You may also choose to create the BigQuery dataset before linking to Firebase for complete control over your naming conventions and data location. If you want to store the BigQuery data somewhere other than the United States, create the dataset in BigQuery first.
Now What?
Check BigQuery tomorrow. You should see new events_() or ga_sessions_() tables available for use!
Firebase or Google Analytics 4 Properties:
Google Analytics 360 Properties:
- For Google Analytics 360, you should see a dataset for each View that is being exported and a table for each of the days that are included.
- For Google Analytics 4 property users, you should see a dataset for each property that has been exported and a table for each day moving forward.
- For Firebase Analytics users, you should see a dataset for each Firebase project that has been connected, and a table for each day moving forward.
For Google Analytics 360 users only, Google will backfill data for each View up to 13 months or 10 billion hits, whichever is smaller (or to the beginning of the View if it’s less than either of those). The backfilled data may take up to two weeks to show up in the project.
It can sometimes take longer than one day for all of the backfilled data to appear—keep checking back until you see everything that you expect.
There will also be an intra-day table for each View that provides some current-day data as well.
For Google Analytics 4 (App + Web) property or Firebase users, your data should start flowing into BigQuery within 24-48 hours, however, the data may take longer to appear. Note that there won’t be any historical backfill for these Analytics products, so you’ll want to integrate as soon as possible!
Just like Google Analytics 360, you should also see an intra-day table for each Google Analytics 4 property and/or Firebase project that has been integrated.
Google Analytics and BigQuery Resources
Once you have BigQuery setup, check out the resources below for running some queries!
All BigQuery Resources, Regardless of Analytics Product
- BigQuery Recipes – A great list of handy queries you can put to use today.
- Query Reference – This document details BigQuery’s query syntax and functions.
- Using BigQuery in DS reports – How to connect BigQuery to Data Studio reports to visualize your data.
Google Analytics 360 Only Resources
- Querying GA data – The fundamentals of querying Google Analytics data from BigQuery.
- What’s missing from BigQuery export – A rundown on what exactly is not included in the automated Google Analytics export to BigQuery, plus some tips on calculating metrics yourself.
- BigQuery Export Schema – This article explains the format and schema of the Google Analytics Premium data that is imported into BigQuery.
- BigQuery Cookbook – This article contains examples of how to construct queries of the Google Analytics data you export to BigQuery. We have made available a sample dataset so you can practice with the queries in this article.
- Connect Google Analytics Data to your Tools via BigQuery – This post outlines the differences between using custom queries, tables, and views in BigQuery and the benefits of each option regarding integrating that data with tools like Data Studio.
Google Analytics 4 (App + Web) Property and Firebase Analytics Resources
- Guide for Querying data - The fundamentals of querying Google Analytics data from BigQuery for Google Analytics 4 (App + Web) or Firebase projects.
- BigQuery Export Schema - This article explains the format and schema of the Google Analytics & Firebase Analytics data that is imported into BigQuery.
