How to link Google Analytics 4 to BigQuery? The ultimate guide.

How to link Google Analytics 4 to BigQuery? The ultimate guide.

Google Analytics (GA) has long been the leading website analytics software available, and with good reason. The program provides detailed information on your site visitors and their interactions, offers a range of interactive features that enables you to analyse and quickly identify traffic patterns, ensures that the data is accurate and complete, and even allows you to measure how well your marketing campaigns are performing! 

Google Analytics can also be used to track conversions. This means that website owners can see not only how many people are visiting their site, but also how many of those visitors are taking the desired action (such as making a purchase or signing up for a newsletter). This information is indispensable for understanding whether or not a website is successful in achieving its goals.

5 Key Benefits of Google Analytics 4

Google Analytics 4 (GA4) is the latest version of Google Analytics, and it offers a number of benefits over the previous Universal Analytics version that can be extremely helpful for businesses. 

  • Google Analytics 4 allows cross-domain tracking that does not rely on code adjustments.
  • GA4 allows you to track conversions more effectively.
  • It makes it easier to segment your audience.
  • It provides more options for customising reports.
  • Built-in GA4 and BigQuery integration

BigQuery and Google Analytics 4 integration

When it comes to Google and data, it’s no mystery that the company has been at the forefront of cutting-edge technologies for over 20 years. One of those technologies that have taken off in recent years is Google BigQuery, a service built on their big data infrastructure that allows users to extract, store and explore large datasets with just a few clicks.

In the past BigQuery was only available to paying users but with the introduction of Google Analytics 4, it is now accessible for free. Even though there is a ceiling on the amount of data you can store and query via the free BigQuery sandbox, in my opinion, your business can do a lot with the monthly free data allowance – 10GB of free storage and free processing of up to 1TB of query data.

Connecting Google Analytics 4 to BigQuery – a step-by-step guide

Connecting your Google Analytics 4 property to BigQuery is easy and it allows you to gain a better understanding of your digital information and data. To connect GA4 to BigQuery you need to follow the 

  • Step 1: Set up a Project in Google BigQuery
  • Step 2: Enable Google Analytics 4 BigQuery Linking
  • Step 3: Enable Google Cloud API
  • Step 4: Create a Service account

Step 1: Create a BigQuery Project:

Start by going to https://console.cloud.google.com/bigquery and creating a new project. Click on ‘Select a project” and then ‘New project’.

Create a BigQuery Project
Create a BigQuery Project

If you are not a paid user you are allowed to have up to 25 projects. Your project name will automatically create a project ID so choose something descriptive as a project ID cannot be changed once it has been set.

New Project BigQuery
New Project BigQuery

After choosing your project ID click on ‘Create’ and your project will be created. Well done, you have now created your first Google BigQuery project and you should be able to see it in the top left window corner.

First BigQuery Project
First BigQuery Project

Step 2: Link Google Analytics 4 to BigQuery

Login to your Google Analytics 4 property and navigate and click on the GA 4 Admin option button in the bottom-left corner of the window.

GA4 Admin BIgQuery Links
GA4 Admin BIgQuery Links

Clicking on the BigQuery button will open a screen which allows you to select your BigQuery project. 

Find your BigQuery Project
Find your BigQuery Project

To find your BigQuery project click on ‘Link’ and then ‘Choose a BigQuery’ project.

Chose a BIgQuery Project
Chose a BIgQuery Project

Select the desired property ID and click Confirm to continue. 

Confirm BigQuery Project
Confirm BigQuery Project

Now, select the Google Cloud region for your data from the drop-down menu and press ‘Next’.

BigQuery Data Location
BigQuery Data Location

On the next screen, you have to configure which data streams and events to export. I wouldn’t touch ‘Include advertising identifiers for mobile app streams’ unless you want to export advertiser identifiers to BigQuery.

BigQuery Data Events Export
BigQuery Data Events Export

Normally I would choose both Daily (once a day) or Streaming (continuous export) frequency of data import as I can always change these options at a later date. 

Clicking on the ‘Next’ button will allow you to review the settings of your link to a BigQuery project and if you’re happy with everything you see click on ‘Submit to complete the process. 

Good job! You have now successfully linked your Google Analytics 4 property to a BigQuery project. This should be confirmed on the screen below.

BigQuery Link
BigQuery Link

Quick review: you have created a BigQuery project and linked a GA4 property to it but they are still not connected. To accomplish that you need to create an API. 

Step 3: Enable Google Cloud API

To start the process go to the Google Cloud Console and select ‘APIs & Services’ followed by ‘Library’. 

APIS and Services Library
APIS and Services Library

Make sure that the project that you want to link is selected in the top navigation. 

API Library
API Library

Search for BiqQuery API and click on it. Make sure the BIgQuery API is enabled and click ‘Manage’.

BigQuery API Manage
BigQuery API Manage

Now you gonna add the Service account by first clicking on ‘Credentials’ and then clicking on ‘Create Credentials.

Create credentials BigQuery
Create credentials BigQuery

Select ‘Service Account’ and you gonna see a window like that.

Create Service Account
Create Service Account

In the ‘Service account name’ field, type this special email address [email protected]. The Service account will then be automatically generated for you.

Service Account Details
Service Account Details

After describing your Service Account click on ‘Create and Continue’. On the new window grant the editor access to the Service account and select ‘Done’. 

Service Account Role
Service Account Role

Congratulations, now the whole process has been completed – you’ve created a project in Google BigQuery Project, linked your GA4 property to BigQuery, enabled the Google Cloud API and added a Service account. 

Service Account
Service Account

All you have to do now is wait (it may take up to 24 hours) for the first of your GA4 data to be exported to BigQuery.

Note: If you want to know the format and schema of the GA4 property data and the Google Analytics for Firebase data that is exported to BigQuery read [GA4] BigQuery Export schema.

Accessing your data in BigQuery

Once you have left your data to populate, come back to your BigQuery account using this link https://console.cloud.google.com/bigquery to check the data.

BigQuery Dataset
BigQuery Dataset

Click on your project name to reveal a list of datasets in your project. Here you can only see one dataset (in the example above analytics_251301421) as we are only sending the data from Google Analytics 4 into this project. 

A dataset is a top-level container that’s used to organise and control access to your data. In simple terms, it’s a kind of folder in which your information is stored in the form of tables and views.

Within your dataset, you can see a list of tables:

  • Events_(number of days) – GA4 data from the previous day gets automatically exported from the property to BigQuery every day. To see the individual dates you have to click on the date filter next to ‘Events’. 
  • Events_intraday_<current date> – Data from the GA4 property is automatically imported throughout the day and this will correspond with the ‘streaming’ frequency setting in Google Analytics 4.
To see the individual dates you have data for click on the date next to ‘Events’.
To see the individual dates you have to click on the date filter next to ‘Events’. 

Clicking on any of the tables will show you the structure of that table.

Clicking on any of the tables will show you the structure of that table.
Clicking on any of the tables will show you the structure of that table.

Clicking on the ‘Details’ tab will show you the size of the table, the number of rows and when the table was created. To analyse a table you can either run a query or by simply clicking on the ‘Preview’ tab you can inspect your data without the need to run a query. Needless to say, this is a good practice as it allows you to have a quick view of the data you have imported and the structure of your tables.

Clicking on any of the tables will show you the structure of that table.
Clicking on any of the tables will show you the structure of that table.

If you do want to query a table click on the Query button at the top of the data table.

To query a table click on the Query button at the top of the data table.
To query a table click on the Query button at the top of the data table.

The system will automatically write a sequel statement (SQL SELECT Statement) for you but you have to add ‘*’ between SELECT and FROM in the query before clicking on ‘Run’. 

Add ‘*’ between SELECT and FROM in the query before clicking on ‘Run’.
Add ‘*’ between SELECT and FROM in the query before clicking on ‘Run’.
SELECT
 *
FROM
-- Replace with your table name.
 `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
LIMIT
 10

Note: SQL keywords are NOT case sensitive: select is the same as SELECT. MySQL supports the LIMIT clause to select a limited number of records which can save you a lot of money when querying big websites. 

Query results
Query results

I know that some of you may be instantly taken aback by the structure of the BigQuery tables so I feel like I have to try to explain what ‘nested fields’ mean.

BigQuery Nested Fields

Nested fields in BiQuery are fields that are linked together as a single entity, like a struct or an object.

Nested fields in BigQuery
Nested fields in BigQuery

In the previous version of Google Analytics, every row in the data set represented a single session. As you probably already know Google Analytics 4 is event-based so every row in our data set represents an event and every event can contain multiple event parameters and corresponding values. 

Going back to the previous example after querying the table only rows 1,2, and 3 are ‘real’ and the other ‘rows’ are in fact nested fields (most of the time with NULL values).

To extract the nested values and essentially flatten the tables we must use the UNNEST function.

SELECT
  DISTINCT (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'page_title' ) AS Page_Title
FROM
  -- Replace with your table name.
  `ga4-omi-sido.analytics_251301421.events_*`
ORDER BY
  1
LIMIT
  10

This code will give us the following result.

How to Flatten a BigQuery Table with UNNEST
How to Flatten a BigQuery Table with UNNEST

To truly understand the UNNEST concept in detail visit this article by Todd Kerpelman.

BigQuery SQL Examples

Before wrapping up this article let me give you a few simple examples so you can get some practice using BigQuery with your Google Analytics 4 data. Copy and paste any of the SQL queries on this page into the BigQuery web console at https://console.cloud.google.com/bigquery but don’t forget to replace the example table name with your table name.

Total user count v New users count

WITH
  UserInfo AS (
  SELECT
    user_pseudo_id,
    MAX(
    IF
      (event_name IN ('first_visit',
          'first_open'),
        1,
        0)) AS is_new_user
    -- Replace with your table name.
  FROM
    `ga4-omi-sido.analytics_251301421.events_*`
    -- Replace with your date range.
  WHERE
    _TABLE_SUFFIX BETWEEN '20220519'
    AND '20220524'
  GROUP BY
    1 )
SELECT
  COUNT(*) AS user_count,
  SUM(is_new_user) AS new_user_count
FROM
  UserInfo;

This code will give you the following result:

Total Users v New Users BigQuery example
Total Users v New Users BigQuery example

Most Viewed Pages by Page Title

SELECT
  value.string_value as Page_Title,
  COUNT(*) as Page_Views
FROM
  `ga4-omi-sido.analytics_251301421.events_*`,
  UNNEST(event_params)
WHERE
  key = "page_title"
  AND event_name = "page_view"
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10

This code will give you the following result:

Most Viewed Pages by Page Title BigQuery Example
Most Viewed Pages by Page Title BigQuery Example

Top items added to shopping cart

SELECT
  item_name AS Top_Products,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  -- Replace with your table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST(items)
WHERE
  -- Replace with your date range.
  _TABLE_SUFFIX BETWEEN '20201101'
  AND '20220131'
  AND event_name IN ('add_to_cart')
GROUP BY
  1
ORDER BY
  user_count DESC
LIMIT
  15;

This code will give you the following result:

Top Items Added To Basket BigQuery Example
Top Items Added To Basket BigQuery Example

Notice that in the latter example I am using the [GA4] Google Analytics 4-property sample dataset for BigQuery which you can access using this link.

Another way to find and use the Google BigQuery public dataset is to search for public into your BigQuery console.

To find the Google BigQuery public dataset search for 'public'
To find the Google BigQuery public dataset search for ‘public’

Then click on ‘Narrow search to pinned projects.”

Narrow search to pinned projects.
Narrow search to pinned projects.

When you see ‘bigquery-public-data’ pin it to your project so it’s always there for you to explore.

bigquery-public-data
bigquery-public-data

This post has gotten rather lengthy so I think it’s time to wrap it up here, but as you can see, there is a lot to cover in the new Google Analytics 4.

I hope you’ve enjoyed this article and feel a bit more confident to utilise your own Google Analytics 4 data in Google BigQuery


2 responses to “How to link Google Analytics 4 to BigQuery? The ultimate guide.”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.