Prep Google Analytics Data for Reporting in BigQuery
About This Course
This course will walk you through the steps to link Google Analytics with BigQuery and use Dataform to transform the raw data into a format that is ready for visualization and advanced analysis. At the end of this course you will have three things:
A working data model that converts your raw Google Analytics data into a format that can be used for reporting. Your model will be deployed through Dataform and scheduled to refresh daily as new data arrives.
All of the skills and resources needed to customize the model to your needs, including a series of Looker Studio dashboards that help you quickly start working with the data.
Ongoing and eternal access to a community of previous course participants (in the TLC in Slack), where you can collaborate with your peers as needed.
Who is it for?
Are you a Google Analytics pro who is not quite comfortable with BigQuery? This course is for the analyst who is ready to go beyond the reporting available in the GA4 user interface. You do not need to be a data engineer or expert in writing SQL because we will provide code for you to copy/paste, but you will need to understand Google Analytics 4 and how data is collected.
Prerequisites
Before the course begins you will need to create a Google Cloud Platform project that is linked to a billing account (it is extremely unlikely that you will be charged any fees during this course, but Google requires a credit card just in case). If you need help with this process then you can follow the steps HERE.
We will start out by deploying our model to the sample Google Analytics dataset provided by Google, but if you have your own Google Analytics 4 property that is already linked with a Google Cloud Platform project we will help you deploy the model there as well.
Schedule
The full course includes 12 hours of content. Private teams may choose to complete this in 2 full days, but individuals taking the course through our Test & Learn Community learning groups will meet twice per week for 8 weeks.
Session 1) The benefits and limitations of the raw Google Analytics export
The objective of session 1 is to give you a thorough understanding of the link between Google Analytics and BigQuery. We’ll discuss how “raw” is the data in BigQuery, and how to estimate cost. Then we will explore the data structure and introduce the concept of UNNEST().Session 2) Introducing Dataform for model orchestration
This is the one you don’t want to miss! We will show you how Dataform can be used to orchestrate your queries into a data model. We will cover the key concepts and make sure everyone has deployed a working model.Session 3) Setting the cornerstone with the flat events table that is customized to your settings
Convert the raw GA4 data into a flattened events table that is customized to your settings. Create a refresh schedule to update as new data arrives daily.Session 4) How to deploy an identity graph
Create a new user identifier (we call it the “primary user id”) to stitch unauthenticated events to the most recent authenticated user on the same device. We will introduce window functions and explore how you can modify this technique to match the reporting in the GA user interface, or extend it to build user profiles that feed a CDP.Session 5) How to recreate the “Navigation Summary” report from UA for pathing analysis
Revisit the window functions introduced in session 4 to create a table that can be used for pathing analysis.Session 6) Attribution modeling, and how to replicate the session attribution available in the user interface
Replicate the session attribution available in the “User Acquisition” and “Traffic Acquisition” reports in the GA4 user interface. We will also create a table that generates session-scoped metrics/dimensions.Session 7) How to create segments and enable Ecommerce reporting
The objective of session 6 is to create a method for generating segments and either the session or user scope. We will also show how to create a separate table for Ecommerce reporting that handles item-scoped dimensions and metrics.Session 8) How to extend from here: automated QA, advanced analysis, CDP’s and more.
In our final session we will wrap up any outstanding questions from prior sessions, and discuss how the model can be extended in a variety of ways: automatic data audits, blending with Google Ads and Search Console, forecasting and anomaly detection, advanced analysis, and hydrating CDP’s.
What it isn’t
This is not a full deep dive into Google Cloud Platform and BigQuery. We will cover the specifics that you need to know to accomplish the goals of this course.
Not a SQL course, but you do not need to be an expert in SQL — If you can copy/paste then you’ll be able to follow along
No prior experience expected or necessary, but familiarity with Google Analytics concepts is assumed
How to Sign Up
Private Training for Teams
Click the button below to schedule a meeting with a member of our team so that we can discuss your needs.
Cohort Training for Individuals
Click the button below to see when the next course will begin through our partnership with the Test & Learn Community.