Prep Google Analytics Data for Reporting in BigQuery

with Johan van de Werken and Brian Martin

This course was created by DiveTeam, a data and analytics consulting firm that helps you master the marketing data stack.

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:

1. 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.

2. 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.

3. Ongoing access to a community of previous course participants (in the TLC in Slack), where you can ask questions and 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

We will meet for 3 hours per week over 4 weeks (Mondays & Wednesdays from 10:30 AM EST to 12:00 PM EST) for a total of 12 hours.

Can’t make all the sessions? No problem! Each session will include a recorded lecture (with breaks for discussion that will not be recorded to maintain your privacy). After each session the lecture recording will be shared with participants, so if you need to miss a session or two you will still be able to catch up.

• 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 Participate

The price for this course is $900 USD

1. Click the button below to reserve your spot for $10 USD

2. Block your calendar on Mondays & Wednesdays from September 9 to October 2, 2024

   • Sessions will run from 10:30 AM to 12:00 PM EST (16:30 PM to 18:00 PM CET)

   • We try to keep courses small, so if we have a large number of participants we will offer multiple options

3. The week before the course begins you will receive the official calendar invites along with a link to pay the remaining $890 USD

Instructors

Questions?

Feel free to email DiveTeam with any questions you have about the course: hello@dive.team

Clips from Previous Cohorts