How to connect Google Analytics 4 to BigQuery

1 comment

Connecting Google Analytics 4 to BigQuery – A Step-by-Step Guide

Google Analytics 4 brings data science to the mass market by allowing you to export data for free to Google BigQuery, Google’s powerful cloud based data warehouse platform. Google Analytics 4 has many innovative features which makes it a valuable complement to Universal Analytics. One of these benefits is the ability to export raw and unsampled data from Google Analytics 4 to BigQuery for free. You can also use a free version of BigQuery, called BigQuery Sandbox.

If your website has a high volume of traffic or you try to analyse data from a long date range, there is a risk of sampling of data in GA4 if you run a non pre-existing standard report. Sampling will occur in GA4 when a non pre-existing report exceeds 10 million events, and it is also prone to sampling when you analyse data over more than 60 days.

If you are not yet using GA4, you can read my step-by-step guide to upgrading to Google Analytics 4 here.

You can view the video or read the blog below.

1. What is BigQuery?

BigQuery is an enterprise multi-cloud data warehouse platform which can process high volumes of data in a few seconds. It allows you to conduct real-time analysis of data and use SQL to process it within a few seconds. Because it’s part of the Google suite of solutions it easily integrates with other Google products like Data Studio and Google Sheets.

The real power of BigQuery comes though comes from integrations with many third-party CRM and other marketing tools. This includes HubSpot, Slack, Facebook Leads, and Salesforce.

2. Why Link Google Analytics 4 to BigQuery?

As with any data warehouse you need a high level of security and BigQuery offers two-factor authentication and gives you secure by design infrastructure from Google.

No sampling of data. Sampling of data is common is many Google Analytics reports when you have a website with high volumes of visitors or you are using time series data. But sampling reduces data reliability because it can distort reporting and lead to misinterpretation of results. BigQuery allows you to export raw data without any sampling and so you can conduct much more granular analysis with confidence.

Affordability. BigQuery allows you to just pay for what data is collected and processed.

  • A scalable solution which can easily and quickly adjust to large volumes of data.
  • Export custom event parameters and dimensions.
  • Connect GA4 data with third-party API’s.
  • Connect data from BigQuery data with popular data visualisation tools such as Data Studio, Power BI and Tableau.

3. How to connect Google Analytics 4 with BigQuery:

New BigQuery customers are often offered free credits to use for the Google Cloud in the first 90 days. Customers also receive 10 GB storage and up to 1 TB for queries per month for free.

4. Create a BigQuery Project:

Go to your BigQuery account here: https://console.cloud.google.com/bigquery?

Click on the drop down menu for ‘My first project’ and then select ‘New Project’.

1. New Project in BigQuery
Source: BigQuery

Now click on ‘Create project’ and a ‘New Project’ screen will open where you can name your project.

2. Select Create Project
Source: BigQuery

Your project name will automatically create a project ID which cannot be changed once it has been set. Click ‘CREATE’ to continue. With your free account you can have up to 25 projects.

3 Create Project in Big Query
Source: BigQuery

You will now see the Notifications screen where you need to click ‘Select Project’.

4. Select Project
Source: BigQuery

Well done, you have now created your Google BigQuery project. You should be able to see your project name at the top of the screen. On the right of the screen you should also see the details of your project, such as the project name and ID.

5. GA4 Project Created in BigQuery
Source: BigQuery

5. Link Google Analytics 4 to Big Query

Now login to your Google Analytics 4 property and navigate to the ‘Admin’ area.

6. Google Analytics 4 Admin
Source: BigQuery

Go to the Product Linking section of the admin console and click on ‘BigQuery Linking’.

7. BigQuery Linking in GA4
Source: BigQuery

Click on the ‘Link’ button and this will open a screen which allows you to select your BigQuery project.

8. Link GA4 to BigQuery

Select the ‘Choose a BigQuery project’ button and this will show you all your existing project.

9 BigQuery Link
Source: BigQuery

Select the project ID that you have already created to send the data from the GA4 property. Then click ‘Confirm’ to continue.

10. Select a BigQuery Project for GA4
Source: BigQuery

Edit the data location for the cloud region where your data is stored. As I am based in the UK I select London. You can then click on the ‘Next’ button.

11. BigQuery Location and Next in GA4
Source: BigQuery

You can now adjust your configure settings. This allows you to edit your data streams if necessary. Select the checkbox to ‘Include advertising identifiers for mobile app streams’ if you are sending data from a mobile app and want to export advertiser identifiers to BigQuery.

12. GA4 advertising identifiers and frequency of data import to BigQuery
Source: BigQuery

Choose the frequency of your data import to BigQuery by selecting by ‘Daily’ and ‘Steaming’ options on the screen. You can now click ‘Next’ to continue.

You should now be able to review your link to a BigQuery project and if you are happy with it you can ‘Submit’ to complete the process.

13. GA4 BigQuery Link Review and Submit
Source: BigQuery

Fantastic, you have now successfully linked your GA4 property to a BigQuery project. This should be confirmed in the screen below.

14. GA4 BigQuery Link Confirmation
Source: BigQuery

6. GA4 Data in BigQuery:

Check that your GA4 project is selected in the top menu. From the left-hand navigation select ‘APIs & Services’ and then ‘Dashboard’.

15. BigQuery APIs
Source: BigQuery

In the dashboard you need to click on ‘+ Enable APIs and Services’.

16. Enable APIs
Source: BigQuery

Here you need to search for ‘BigQuery’ in the search input field. Select the ‘BigQuery API’ as shown below.

Source: BigQuery
17. BigQuery API
Source: BigQuery

You will now see the BigQuery API and click on the ‘Manage’ button.

18. Manage BigQuery API
Source: BigQuery

Here you will also need to select ‘Credentials’ to add a service account for the API.

19. Credentials
Source: BigQuery

Select ‘+ Create Credentials’ and this will open a drop-down menu for you to select a ‘Service account’.

20. Create Credentials in BigQuery
Source: BigQuery

You will now see a screen to set a service account name. Use the account ID and add ‘.gserviceaccount.com’ to the end of it. The service account ID will then be generated automatically. Give you service account a suitable name to reflect the Google Analytics 4 data it will be exporting to BigQuery. You can now click ‘Create’

21. BigQuery Service Account Details
Source: BigQuery

We are now on the ‘Create service account’ screen. Click ‘Done’ to complete setting up your service account to export data from GA4.

22. BigQuery Service Account Done
Source: BigQuery

Congratulations you have now finally created your API account and begin exporting GA4 data to BigQuery. You should also see your service account name under your BigQuery project as shown below. You may have to wait up to 24 hours for the first of your GA4 data to be exported to BigQuery.

23. Login to BigQuery and Select Service Account
Source: BigQuery

7. Access GA4 Tables in BigQuery:

Once you have waited 24 hours you can go back to BigQuery and you should be able to see your GA4 project under pinned projects.

Below your project name, you should see a data set with your GA4 property ID appended to the name as shown here “analytics_property_ID”. The analytics data set contains two tables which hold your Google Analytics 4 data.

  • Events_(number of days)
  • Events_intraday_<current date>
24. Select BigQuery Project
Source: BigQuery

Events Data Table:

Your GA4 data from the previous day will be automatically exported from the property to BigQuery every day. You will notice this as the number appended to the events data set will reflect the number of days imported into BigQuery.

Click on events_(number of days) and this will display the structure of the table schema. Above the table you will see the last date when data was imported. If you click on the date below ‘Events’ you will open a drop down which shows the individual dates you have data for. You can also select an individual date to view the data for that particular date.

25. BigQuery Analytics Project - Events
Source: BigQuery

Select the ‘Details’ tab if you want to see the size of the table, number of rows and when the table was made. If you click on ‘Query’ you can begin to run analysis using SQL.

26. BigQuery Project Details and Query
Source: BigQuery

However, if you select the ‘Preview’ tab you can inspect your data without having to run a query. This is good practice as it allows you to view the data you have imported and check it as you expected for your analysis.

27. BigQuery Analytics Project - Events - Preview
Source: BigQuery

Events Intraday Table:

Data from today will be imported into the events_intraday table. The data is automatically imported throughout the day and this will correspond with the ‘streaming’ frequency setting in Google Analytics 4.

As with the events_(number of days) data table, you have separate tabs for schema, details and preview.

28. BIgQuery events_intraday Table for GA4
Source: BigQuery

8. Conclusion:

BigQuery is a powerful cloud-based data warehouse that can automatically import your raw and unsampled GA4 data into. This avoids distorting your reporting by using unsampled data and allows you to undertake deep analysis of metrics without any limits imposed by GA4. Other benefits of using BigQuery with GA4 data is that it allows you to:

  • Track the whole user journey by freeing yourself of the limits of analysis within the GA4 console.
  • Create reports without any limits on the amount of data or the dimensions you apply.
  • Connect BigQuery with many other third-party solutions such as Snowflake and many other data analysis platforms.
  • BigQuery also integrates with popular data visualisation tools such as Data Studio and Tableau.

Begin the process of taking your GA4 data analysis to the next level by connecting it to Google BigQuery.