Optimization Up

BigQuery is a cloud data warehouse designed to help you turn big data into valuable business insights. Linking BigQuery to Google Analytics was available only to GA360 users – the enterprise versions of Google Analytics. However, since the introduction of the new GA4, linking BigQuery to GA has become now available to everyone with no extra cost.

You can export all raw data from Google Analytics 4 properties to BigQuery, and then use SQL syntax to query and model the data. When you export GA4 data to BigQuery, you own that data, and you can analyze users and their traffic on your property.

To get started, you will need a Google Analytics 4 account and Google Cloud Project. If you haven’t upgraded your universal analytics properties to GA4, you can read our full guide here.

Why to Link BigQuery to GA4

BigQuery allows processing SQL queries at a very high speed enabling faster analysis for marketers and analysts. The native integration with GA is one of the most powerful features you can use to get the most out of your data and take your reporting to the next level. Here are some of the benefits of connecting GA4 to BigQuery

• Unsampled data, you get access to raw events data
• Hight reporting speed thanks to Google infrasturcture
• Integration with other data sources like CRM, Facebook Ads and more
• Flexibility in manipulating data – create unlimited dimensions or custom channel grouping
• Apply machine learning to your data set and make accurate predictions

How to link BigQuery to GA4

1- Navigate to your Google Analytics 4 property, then go to Admin page.

2- In the Property column, click BigQuery Linking:

3- Select Link:

4- Click Choose a BigQuery project to display a list of your cloud projects:

5- Select your cloud project. If you don’t have a Google Cloud project, then you will need to create a new one as explained here:

6- After selecting your project, click Next:

7- Select Include advertising identifiers for mobile app streams, if you have a mobile app and we’d like to export the user ids to BigQuery:

8- Select either or both a Daily (once a day) or Streaming (continuous) export of data, after that click Next:

9- Click Submit:

10- Navigate to Google Cloud Console then Click Enable APIs and Services:

11- Look for BigQuery, then select the API:

12- Make sure the Google BigQuery API is enabled, then Click Manage:

13- Click Credentials from the sidebar menu:

14- Select Service Account.

15- Add [email protected] to enable GA4 to export data to BigQuery:

16- Grant the service account email Editor access

17- At this step, you should be all good to go. All you need to do is to wait for 24 hours for the data to be export to your Bigquery dataset.

Write your First Query

Within each dataset, you will find two tables as seen in the screenshot above:

• events_intraday_YYYYMMDD: it includes today’s raw events where data is exported continuously throughout the day.
• events_YYYYMMDD: it includes full daily export of events.

In order to query all the raw events data for 11-07-2021 for example, we can write something like this:

SELECT * FROM `civic-bruin-237115.analytics_258822732.events_20210711` 

Replace civic-bruin-237115 with your project id.

Replace analytics_258822732 with your GA4 property id.

You may check here this SQL query that allows you to recreate the traffic report in Google Analytics.