Using BigQuery and Data Studio with Google Analytics 4

Data flow between GA4, BigQuery, and DataStudio.

The most difficult part of migrating from Universal Analytics to Google Analytics 4 is the reporting. GA4 does not have many of the same built-in reports that Marketers and Analysts have come to expect from previous versions of Google Analytics, and it can be difficult to replicate them.

However, with a little practice, you can create just about any data visualization you want by using a combination of Google Analytics 4 + BigQuery + Data Studio. The goal of this post is to share tips and best practices for visualizing your GA4 data in BigQuery and Data Studio, and give you a few samples to help get started.


Setting Up the Data Flows

Before we begin you will need to enable the BigQuery integration to export your raw data from Firebase Analytics (which is where Google Analytics 4 data is stored) into a BigQuery table daily. Once this is done you will be able to use BigQuery to run SQL queries on your raw data, or connect Data Studio to BigQuery (Google’s free data visualization tool) to create reports and dashboards. Your data flow will work like this:

Data flow

Getting Familiar with Google Analytics 4 Data in BigQuery

The GA4 data stored in BigQuery is not completely raw (some processing has been done on it), but it’s helpful to think of it as a raw and unsampled list of events. Each day Google generates a table with data from the prior day, and each row in the table represents an event.

Now, if you’ve used BigQuery with GA360 you know what’s coming next: each event (or row) in your table is in a JSON format (this is true of Crashlytics data too). Here is a simplified example of a single screen_view event in BigQuery:

event_date event_name event_params.key event_params.value.string_value
20200101 screen_view firebase_screen_class product_detail_page_12345
firebase_previous_class search_results_page

You’ll notice that there is no “screen name” column, but there is a parameter called “firebase_screen_class” that stores the screen name. There’s also another parameter called “firebase_previous_class” that stores the prior screen name. These parameters and their values are stored in the columns “event_params.key” and “event_params.value.string_value”. Here’s what the data actually looks like when you export it from BigQuery.

 2    "event_date": "20200101",
 3    "event_name": "screen_view",
 4    "event_params": [{
 5        "key": "firebase_previous_class",
 6        "value": {
 7            "string_value": "search_results_page"
 8        }
 9    }, {
10        "key": "firebase_screen_class",
11        "value": {
12            "string_value": "product_detail_page_12345"
13        }
14    }]

We have multiple values for the “event_params…” columns because the data is actually an array. If this concept is new to you then you will definitely need to read my post on How to Flatten a BigQuery Table with UNNEST.

Getting Familiar with Google Analytics 4 Data in Data Studio

It was simple and intuitive to build dashboards in Data Studio with the old Google Analytics API because you had a pre-built list of 513 metrics and dimensions to choose from.

Metrics & dimensions

With Google Analytics 4 you have two options:

  1. Use the Google Analytics connector that is built into Data Studio (which relies on the new GA4 reporting API)
  2. Or connect directly to your exported data in BigQuery.

If you choose option 1 (the GA4 connector) your reports are easier to pull, but you are limited to the 155 fields available in the API.

If you choose option 2 (BigQuery), you'll notice that your data is more raw, and you’ll have to transition to using events, parameter names and parameter values.  This has a bit of a learning curve, but when you get use to it will give you unlimited flexibility on how to pull your data.

As an example, here is how you would pull a list of pageviews by URL from your web stream data in BigQuery:

  • Dimension: “Event Param Value (string)”
  • Metric: “Event Count”
  • Filter: Event Name = “page_view”
  • Filter: Event Param Name = “page_location”

The resulting chart will look like the image below:

Pageviews in Data Studio

Resource Library

Data Studio Samples

I've compiled a series of reporting examples in a public Data Studio report that anyone can view and copy.

It can be difficult to explore the events that are being recorded and their parameters in the GA4 User Interface. This may change with future updates, but for now the best way to do this is with Data Studio.

If you are new to using Data Studio with Firebase data, start with The Event & Parameter Explorer.

Any time I build a Data Studio dashboard using GA4, I start by copying this report to help me drill down into each event to view the parameters and their values. This helps you visualize how your data is organized, and also make sure that you spell event and parameter names correctly in your filters.

BigQuery + Data Studio Posts

I've compiled a list of posts below for reporting on GA4 data in BigQuery and Data Studio. This list will grow over time.

Like always, please contact me if you find any problems with these resources or if you would like to contribute new items for me to add.