English Posts

How to Link Google Analytics 4 to BigQuery

How to Link Google Analytics 4 to BigQuery

The new Google Analytics 4 gives also the possibility to use BigQuery for free and this is simply one of the greatest feature improved.

BQ is a really powerful tool to analyze the raw data collected in GA. The possibility to link the two tools will open lot of possibilities: imagine what you can do with unsampled data or connect this data with other platforms and so on.

google analytics 4 bigquery

With GA4 every user has the possibility to connect to BQ even without having a GA360 account.

In this post I’ll show you how to link Google Analytics 4 to BigQuery in few steps. It’s very easy.

Here you can see the path I’ll follow:

ho to link ga4 to bigquery

Introduction

Google allows us two possibilities by using BigQuery:

  1. Without enabling billing for the project I want to use in BQ;
  2. By enabling billing (i.e.:Credit Card) for the project;

By enabling billing doesn’t mean you have to pay. There are several quotas and limits. Only if you exceed them you have to pay as you can read in the offical guide, but you have lot of flexibility and you can do many things within these limits. That’s really cool!

Without enabling billing (option ): you see that because you’ll use the Sandbox BQ:

sandox bigquery

The Sandbox lets you use Cloud Console without a Credit Card and also you can “play” with BigQuery. Obviously, Sandbox has several limitations, for example the exiparation set to 60 days but think about this: you can play with the tool with your own data and learn many things, without spending money. That’s awesome, right?

By enabling billing (option 2) gives you more flexibility and, as I’ll explain below, you can also export GA4 data continuously.

Let’s now take a look how you can link GA4 to BQ.

Create a Project in BigQuery

First of all, you need a BigQuery Project. Here, GA4’s going to send the data to.

Let’s go to BigQuery , login with your Google account and select “My First Project” (1), then “New Project”(2):

new project in google bigquery

Let’s give it a name, as you can see in my example:

  • Project Name: DataEnthusiast Project
  • Location: No Organisation
new project in bq

By clicking on Create, a new project will be created.

The newly created project and all the others you’ll create, you can simply select them from the blue bar:

select projects in bigquery

Link Google Analytics 4 to BigQuery

Now we have to go into the Google Analytics 4 Admin panel and link the new project to the Property.

Select BigQuery:

google analytics 4 bigquery

Now click on Link:

click on link tab

Then select the newly created project (if you do not see it, simply refresh the page):

select the project created in bigquery

All the projects will appear, choose yours (1) and click on Confirm (2):

select your project in bigquery

In this way, we tell Google Analytics where to send the data in BigQuery.

After selecting the project, we have to select the Data location:

select the project and the data location

Next, we need to configure which Data Streams we want to select (if we had more than one) and have to pick the frequency of these data:

  • Daily: it takes a full export of data once a day (everyone can select this option, both if you do not have enabled billing and if you enabled it);
  • Streaming: the data are sent in real time (this option can be selected only by users who have enabled billing in the Google Cloud Platform);
select the data stream

if you do not want to enter a payment method and you want to start “playing” with BQ, Google allows you to do so by offering a data export at the end of the day. My advice, however, is to enable billing in order to select the Streaming option.

Then, click on Next and go on the third step, a simple summary of what you selected above. Chek if all it’s correct and click on the Submit button:

select submit button

For those who have enabled billing, there is the possibility to select both methods of data export (recommended):

data export in google analytics 4

Now the Google Analytics property is linked to BQ, but we need to make other steps.

Enable API

Let’s check if the API is enabled.

Go back to BigQuery, in Menu > APIs & Services > Library:

api enabled

Search in the bar the keyword “bigquery” and select the entry BigQuery API:

select bigquery in the library

Let’s enable the API, that’s make the connection between GA4 and BigQuery:

attivare l'API di bigquery

With API enabled, it’s going to take the data from Analytics and transfer it over to that project.

Add a Service Account

We now need to verify that firebase-measurement@system.gserviceaccount.com has been added as an “Editor” member of the project we created.

Into IAM & Admin > Service Account > Create service account we have to add the following information:

  1. Paste the following name into the first box: firebase-measurement@system.gserviceaccount.com
  2. Type a name for the description
service account added in big query

Then click on Create.

Now select Editor for the service account just added, as it explains the Google guide.

editor access

Let’s continue and confirm.

Use BigQuery with GA4 data

Now that we have set up all, we’ve to wait 24 hours for the data to be transferred from GA4 to BQ.

In fact, if after all these steps, you simply go back to your project in BigQuery, you’ll notice that there is no dataset or table yet (let’s see the difference between the newly created project 1 and another project 2):

projects in bigquery

After 24 hours, you can see this:

dataset and tables in bigquery project

Just for information: from when I assigned the ‘Editor’ right, until I saw datasets and tables created, 19 hours have passed. You can have the possibility to see all your project’s activities in this link (thanks to Khristina Rustanovich @naoborote for the idea):

activity bigquery

Conclusions and resources

The possibility to link GA4 to BQ is a really powerful feature and it’ll open up new scenarios and possibilities, I’m sure.

Google offers today the possibility to approach to BigQuery simply by using the Sandbox. In this way, anyone can approach and better understand the advantages that the connection between these two tools offers.

If you wanted to change, for whatever reason, the link between GA4 and BQ, you need to go into the Admin panel and delete it:

delete the bq project in ga4

If you want to discover more about the query syntax, you can read the official guide.

I also suggest you the free course on Mode, if you want to improve your SQL’s knowledge.

Good analysis!


You may also be interested by the following articles:

How to delete gtm_debug=x in Google Analytics

How to filter internal traffic in Google Analytics 4

Track Clicks within Navigation Menu with Google Tag Manager

Tracking Google Data Studio Dashboards with Google Analytics

How to track Single Page Applications with Google Tag Manager

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *