Moving Google Analytics data through BigQuery to Amazon Redshift for Reporting
Overview
This blog will show how to enable data injection from Google Analytics and Firebase Analytics to BigQuery. Furthermore, it will also show how AWS Glue ETL connects to BigQuery tables, migrates the data into Amazon S3, and then moves the aggregated data to Redshift for a centralized reporting source.
Click here if you are looking to migrate Adobe Analytics to Redshift.
BigQuery + Google Analytics
Google Analytics 360, Google Analytics 4 (also known as App + Web), and Firebase Analytics users can enable an automated, daily export of their raw Google Analytics data into Google BigQuery. This feature is a major benefit for analyzing unsampled data as there are limited methods for getting around sampling within the Google Analytics interface. Google BigQuery is a structured database that allows for nested and repeated fields, and gives users the opportunity to perform more in-depth statistical analysis that simply isn’t possible within the Google Analytics web interface. However, on the flip side, it is important to note that it is necessary to manually calculate many of the metrics that we see natively in the GA web interface, such as total users, total sessions, etc.
Costs:
BigQuery is included in the subscription costs for Google Analytics 360 users and accessed by adopting and utilizing Google Analytics 4 properties or Firebase Analytics projects. While the integration is free, there are costs for storing and querying data in BigQuery, but they are in the lower range. At the time of this post, the current costs are:
Setting Up BigQuery with Google Analytics 360
There are a few steps to enable and configure a BigQuery project for Google Analytics 360 views.
- 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.
- Enable Billing – From the hamburger menu, go to Billing and set up your billing account information.
Follow the steps below:
- Add Permissions – From the same hamburger menu, select “IAM & Admin”. The login we used to create the project will be the project owner as well. Also, add the following account with editor permissions to the project (editor permission on this account is required to export data from Analytics to BigQuery): analytics@system.gserviceaccount.com.
Enabling the Analytics 360 + BigQuery Integration
This integration is not available for standard Google Analytics properties, and so we must have the enterprise version, Analytics 360. We can also export only one view per property.
- In the Google Analytics property settings, go to Product linking > All Products.
- Scroll down to BigQuery and click “View Link”.
- Enter the project number. We can find this in the Cloud Console.
- Back in Google Analytics, select the “View to link” from the dropdown. Notice that we can only choose one view.
- 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.
Setting Up BigQuery with Firebase Analytics
For mobile applications, Firebase Analytics also offers integration with Google BigQuery. To begin, we will need to create or use an existing Firebase project to link our project to BigQuery.
Once we have a Firebase Project created and an associated app developed or in place, we’ll want to link this project to Google Analytics if we 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 our existing Google Analytics 4 (App + Web) property if you have one.
From here, we will set up the export within Firebase by navigating back to Project settings > Integrations. Then, on the BigQuery card, click “Link”.
From here, we’ll see several configuration options. By default, BigQuery links all apps in our project, and any apps that we 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 our export. We 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 we are satisfied with our selections, click “Link to BigQuery”.
BigQuery
Once those integrations have been created, check BigQuery the next day to confirm the new events_() or ga_sessions_() tables have been created and are available for use.
Google Analytics 4 Properties:
Google Analytics 360 Properties:
For Google Analytics 360, we should see a dataset for each exported View and a table for each included day.
For Google Analytics 4 property users, we should see a dataset for each exported property and a table for each day moving forward.
For Firebase Analytics users, we should see a dataset for each connected Firebase project and a table for each day moving forward.
Connector for Bigquery to AWS S3
The following architecture diagram shows how AWS Glue connects to Google BigQuery for data ingestion.
AWS Glue is a fully managed extract, transform, and load (ETL) service that makes it easy to prepare and load our data for analytics. AWS Glue provides all the capabilities needed for data integration, and users can analyze in minutes instead of weeks or months. AWS Glue custom connectors are a new capability in AWS Glue and AWS Glue Studio that makes it easy to transfer data from SaaS applications and custom data sources to our data lake in Amazon S3. With just a few clicks, we can search and select connectors from the AWS Marketplace and begin our data preparation workflow in minutes. We can also build custom connectors, share them across teams, and integrate open-source Spark connectors and Athena federated query connectors into data preparation workflows. AWS Glue Connector for Google BigQuery allows migrating data cross-cloud from Google BigQuery to Amazon Simple Storage Service (Amazon S3). AWS Glue Studio is a new graphical interface that makes it easy to create, run, and monitor extract, transform, and load (ETL) jobs in AWS Glue. We can visually compose data transformation workflows and seamlessly run them on AWS Glue’s Apache Spark-based serverless ETL engine.
AWS Glue Studio queries BigQuery tables, saves the data into Amazon Simple Storage Service (Amazon S3) in Parquet format, and then queries it using Amazon Athena. To query BigQuery tables in AWS Glue, we use the new AWS Glue Connector for Google BigQuery from AWS Marketplace.
For more details, follow the link here.
Setup Data Process Pipeline on AWS Using Glue
The diagram below shows the data flow between different stages within the AWS system.
BigQuery connector Lambda/Glue will make the connection between BigQuery and AWS landing S3. Then, we can create tables and schema in Athena using AWS Glue crawler/manually to query the data in all processing stages.
Landing:
BigQuery connector pulls the data from Google Analytics and Firebase Analytics data to AWS Landing S3 bucket. Once the Landing S3 received the data Stage glue job will start.
Stage:
Stage job will be flattened Google and Firebase Analytics JSON data to parquet format data and moved to the staging S3 bucket.
Core:
Once the Core job is done, the aggregation on stage data will move to the core S3 bucket. Another set of AWS Batch jobs will copy the data from core S3 to Redshift. Business intelligent tools such as PowerBI and Tableau can directly access the mart tables from Redshift.
Conclusion
In this post, we learned how to easily enable data injection from Google Analytics and Firebase Analytics to BigQuery. We also learned how to connect AWS Glue ETL to BigQuery tables, migrate the data into Amazon S3, and move the aggregated data to Redshift for a centralized reporting source. With AWS Glue, we can significantly reduce the cost, complexity, and time spent creating ETL jobs. AWS Glue is serverless, so there is no infrastructure to set up or manage. We pay only for the resources consumed while our jobs are running.
