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.

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:

Create a Project in BigQuery
Link Google Analytics 4 to BigQuery
Enable API
Add a Service Account
Use BigQuery with GA4 data

Introduction

Google allows us two possibilities by using BigQuery:

Without enabling billing for the project I want to use in BQ;
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:

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):

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

Project Name: DataEnthusiast Project
Location: No Organisation

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:

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:

Now click on Link:

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

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

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);

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:

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

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:

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

Let’s enable the API, that’s make the connection between GA4 and 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:

Paste the following name into the first box: firebase-measurement@system.gserviceaccount.com
Type a name for the description

Then click on Create.

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

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):

After 24 hours, you can see this:

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):

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: