By - Neal Cole

How to connect Google Analytics 4 to BigQuery.

Google BigQuery

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.

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 customer are often offered free credits to use for the Google Cloud in the first 90 days. Customer 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

 

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

2. Select Create Project

 

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

3 Create Project in Big Query

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

4. Select Project

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 see the details of your project, such as the project name and ID.

5. GA4 Project Created in 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

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

7. BigQuery Linking in GA4

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

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

Edit the data location for the cloud region where your data will be 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

 

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

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

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

 

6. GA4 Data in BigQuery:

 

Go back to your BigQuery account here: https://console.cloud.google.com/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

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

16. Enable APIs

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

17. BigQuery API

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

18. Manage BigQuery API

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

19. Credentials

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

20. Create Credentials in 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

 

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

Congratulations you have now created your API account to begin exporting GA4 data to BigQuery. You should 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

 

7. Access GA4 Tables in BigQuery:

Once you have waited 24 hours you can go back to BigQuery using this link: : https://console.cloud.google.com/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

 

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 select an individual date to view the data for that particular date.

25. BigQuery Analytics Project - Events

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

26. BigQuery Project Details and Query

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

 

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

 

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.

  • About the author:  Neal  (@northresearch) provides web analytics and CRO consultancy services and has worked in many sectors including financial services, online gaming and retail. He has helped brands such Hastings Direct, Manchester Airport Group Online and Assurant Inc to improve their digital marketing measurement and performance.

 

1 thought on “How to connect Google Analytics 4 to BigQuery.

Sai Krishna D 31st May 2021 at 7:27 pm

Great insights Neal! appreciated for this step-by-step guide about connecting bigquery with ga4! even 8th or 9th grade student can understand this stepbystep process guide! Thanks again for this wonderful piece of insights neal! Have a great day!

Reply

Leave a Reply

Your e-mail address will not be published.
*
*

Call Now ButtonCall Me Now!