Google Analytics 360 Starter Guide for Google BigQuery/
August 3, 2017
Recent Google Analytics 360 customers 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 specifically about how to enable BigQuery and the auto-export of Google Analytics data, plus we’ll provide some resources near the end for querying the data.
Google BigQuery is Google’s cloud data warehousing solution which is part of the Google Cloud Platform. It is 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 integration.
BigQuery + Analytics 360
Google Analytics 360 subscribers 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 GA interface.
One of the most important details you should be aware of with regards to Google Analytics data within BigQuery is the format of the tables. BigQuery is a structured database that allows for nested and repeated fields; which you will need to wrap your head around when querying Google Analytics data. Because Google Analytics data is hierarchical, you will 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 are not all provided for you! For instance, total users, total sessions, total events, etc. Again, check out the resources at the end of this post for more information.
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:
- Storage – $0.02 per GB/month
- Queries – First 1 TB/month is free. Subsequent queries are $5 per TB
Analytics 360 subscribers receive a credit each month for BigQuery usage. It is uncommon for Analytics 360 customers to exceed this credit except in the most intensive cases. If you are an Analytics 360 customer, talk to your Analytics 360 partner if you do not already have access to a coupon.
Setting Up BigQuery
There are a few steps you’ll need to take in order to enable and configure a BigQuery project. 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:
You must do this regardless of whether or not you have a coupon.
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): firstname.lastname@example.org.
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.
If you already have a coupon code (which you likely received from your Analytics 360 partner), utilize it by going here. Make sure you select an organization when you do this – none will be selected for you.
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 ID’s and View ID’s that you want to import into BQ 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!
Check BigQuery tomorrow. You should see a dataset for each View that is being exported and a table for each of the days that are included.
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.
Google Analytics and BigQuery Resources
Once you have BigQuery set up, check out the resources below for running some queries!
- Querying GA data – the fundamentals on querying Google Analytics data from BigQuery
- BQ Recipes – a great list of handy queries you can put to use today
- What’s missing from BQ 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.
- Query Reference – This document details BigQuery’s query syntax and functions.
- Using BQ in DS reports – how to connect BigQuery to Data Studio reports to visualize your data.
- 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.