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

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

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.

You will now see the Notifications screen where you need to click ‘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 also see the details of your project, such as the project name and ID.

5. Link Google Analytics 4 to Big Query

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

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

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

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

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

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.

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.

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.

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

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

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

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

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

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

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

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’

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

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.

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>

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.

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.

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.

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.

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.