In our post Google Analytics 360: The Solution For Data Driven Enterprises we talked about the rich data residing within Google Analytics 360 (Premium). Google Analytics 360 provides programmatic access to the data via APIs. The principle method of access is that Google provides the ability to export session and hit-level data to BigQuery.
However, if you have consolidated all your data into a Redshift warehouse you will want to mobilize data in BigQuery there. This guide will walk you through the steps to get that setup and operational.
Step 1: Create a Google-APIs-Console project and enable BigQuery
You will need to have a Google Cloud account setup. This is where BigQuery resides. These steps assume you have that account setup:
Log in to the Google APIs Console.
Create a Google APIs Console project. You can create a new project or select an existing project.
Navigate to the APIs table.
Open the Products & services menu in the top-left corner, click API Manager, then click Google APIs.
Activate BigQuery: under Google Cloud APIs, click BigQuery API. On the following page, click Enable API.
If prompted, review and agree to the Terms of Service.
Step 2: Prepare your project for BigQuery Export
Ensure Billing is enabled for your project. If you do not have Billing enabled for your project, open the Products & services menu in the top-left corner, then click Billing.
If prompted, create a billing account. A billing account is necessary to apply billing to a project. A single billing account may be shared across multiple projects. Follow the steps in the API console to create your billing account.
Accept the free trial if it’s available. If you are offered a free trial, it is safe to accept it; however, you must also enter billing details for BigQuery to continue receiving exported data once the free trial is over.
Validate Billing enablement.
Open your project at https://bigquery.cloud.google.com, and try to create a data set in the project. Click the blue arrow next to project name, then click Create data set. If you can create the data set, billing is setup correctly. If there are any errors, make sure billing is enabled.
Add the service account to your project.
Add analytics-processing-dev@system.gserviceaccount.com as a member of the project, and ensure that permission at the project level is set to Editor (as opposed to BigQuery Data Editor). Editor permission is required in order to export data from Analytics to BigQuery.
Redeem your Analytics 360 coupon code. Go to cloud.google.com/redeem to redeem your rolling $500-per-month Google Analytics 360 credit for BigQuery. Your code will be included in an email from your Account Manager. This step is necessary in order to receive the $500-per-month Google Analytics 360 credit for BigQuery, and must be completed before the export is initiated.
If you are prompted with an alert to create a new Billing account, then add your organization ID to the end of the URL (https://console.developers.google.com/billing/redeem?organizationId=your organization ID).
Step 3: Link BigQuery to Google Analytics 360
After you complete the first two steps, you can enable BigQuery Export from Analytics Admin.
Sign in to Google Analytics. Use an email address that has OWNER access to the BigQuery project, and also has Edit permission for the Analytics property that includes the view you want to link.
In the PROPERTY column, click All Products, then click Link BigQuery.
Enter your BigQuery project number or ID. (Learn more about how to locate your project number and ID.)
Select the view you want to link.
Optional: Select the email addresses at which you would like to receive daily success and/or failure notifications.
Confirm that you have enabled billing and applied any relevant credits or coupons to your project.
Click Save.
If you need to stop the export, return to this page, and click View Link in the BigQuery section.
NOTE: The Google best practice is that you link no more than 300 Google Analytics reporting views to a single BigQuery Project. More than 300 will degrade the export of intraday data.
Step 4: Reviewing Your 360 Data
Once that Steps 1–3 are complete Google will start pushing data from Google Analytics 360 into BigQuery. Within each dataset, a table is imported for each day of export. Daily tables have the format ga_sessions_YYYYMMDD. Intraday data is imported every 8 hours. Intraday tables have the format ga_sessions_intraday_YYYYMMDD. During the same day, each import of intraday data overwrites the previous import in the same table.
Keep in mind that each row corresponds to one session. The data is very WIDE, meaning there can be hundreds of columns in the raw data. The data will also include your own custom dimensions and metrics you specified as part of your tagging.
Here is an example layout of what the data will look like:
To get familiar with the data you will want to run test queries. Here is a query that will get a report for the last seven days:
SELECT
date,
SUM (totals.visits) visits,
SUM (totals.pageviews) pageviews,
SUM (totals.transactions) transactions,
SUM (totals.transactionRevenue)/1000000 revenue
FROM (TABLE_DATE_RANGE([xxxxx.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -7, ‘DAY’), DATE_ADD(CURRENT_TIMESTAMP(), -1, ‘DAY’)))
GROUP BY
date
ORDER BY
date ASC
Step 5: Copy Google Analytics 360 Data To Redshift
Once you have validated and tested your Google Analytics 360 to BigQuery Export process, you are ready to begin your transfer to Amazon Redshift with our Google Analytics 360 integration.
Once Step 5 is complete data will start flowing to Redshift within 24 hours! You will have an automated pipeline of data from BigQuery to Redshift occurring daily.
Helpful Tips
If you are just getting started with Google Analytics 360 data, make sure you check out how to resolve conflicts between the data and the Google Analytics 360 Reporting UI. Differences between the raw data and the Reporting UI can cause unnecessary friction between team members who reference both:
You might also be interested in a set of test queries you can run to cross check your data with the Google Analytics 360 Reporting UI:
Interested in rolling your own data pipeline from Google Analytics 360 to Redshift? Check out our open source toolset!