Export Google Analytics 4 data into BigQuery
In some scenarios when you are trying to merge online behavior data with other data sources or do a more advanced data analysis you might need to access raw data from Google Analytics, which is not available for export directly from the UI.
The easiest solution to this problem, in my opinion, is to set up an automated data export into BigQuery, which then will be available for any kind of data blending/transformation/analysis using SQL.
In this short article, we will go through how to set up an automated data export from Google Analytics 4 into BigQuery from scratch.
In case you prefer video format, you can watch it below. You can also subscribe to my YouTube channel!
Step 1: Start GA4 BigQuery linking
Google Analytics 4 has a few linking options out of the box (and they keep adding more), luckily, BigQuery linking is also available in the free tier.
In the Admin section of your Google Analytics account select your property and find the “BigQuery links” section. (Admin > Property > BigQuery links)
Click on “Link” button to create new link
Choose a BigQuery project that is managed by the same Google account.
You might see no BigQuery projects here or are unable to create a link, and there could be multiple reasons for that:
- You haven’t created any BigQuery projects in Google Cloud yet
- You have a BigQuery project but your current Google account doesn’t have access to it
- You have insufficient rights to do the linking (OWNER rights in BigQuery project + EDITOR rights for GA4 property)
If somebody else already manages BigQuery or other Google Cloud projects in your organization then you would need to grant that person Edit rights to GA4 and ask to create this link.
Otherwise, you could set up a Google Cloud project on your own.
Step 2: Set up the Google Cloud project
Go to the Google APIs Console and log in.
You should see a similar dashboard as I have below. Open the organization dropdown and click on the “New project” button.
Pick a name for your project (it can be anything) and hit “Create”.
Now we need to enable BigQuery API if it wasn’t enabled automatically.
From the left menu open APIs and services > Enabled APIs and services
If BigQuery was automatically enabled you should see it on the list below
If that’s not the case, click on “Enable APIs and Services” at the top of the page and search for BigQuery API.
Select “BigQuery API” and enable it.
We should be all set to proceed with the linking in the GA4 interface now.
Step 3: Finishing BigQuery linking
Now back in Google Analytics reopen the project link window and you should see your new project here if you have done everything from step 2. Alternatively, if you already had a project linked to your Google account with OWNER rights, you should be able to see it here from the start.
To proceed select your project and click “Confirm”.
Next, you need to pick a location where this data will be stored. I will store this data in Europe.
Now you can configure what data is exported and how often you want it to be refreshed.
- You can configure what events you want to export since in the free version you are limited to 1M events per day
- You can export advertising identifiers if you have a mobile app stream
- You can select if you want your data to refresh daily or almost in real-time (Streaming).
If you have quite a lot of events or traffic that generates over 1M events daily, you might consider filtering the data based on your needs. At the moment you can only exclude events based on their names (not the attributes). So, for example, if you see that quite a big portion of collected hits is from page scroll events, you might exclude “scroll” from the export to save some quotas.
When ready, you can Apply settings and you should see a success message that the data export link was successfully created.
Step 4: Check your data export
After a day or two, you should be able to see the first daily data export in BigQuery.
If you go to the Google Cloud Console you should be able to find “BigQuery > SQL workspace” from the left menu (make sure you are logged into the same account where you have created the BigQuery project).
On the left side, you will see the list of all your projects and data sets. Expand the project that you have linked to your GA4 account and then expand the data set that has GA4 property ID in its name (in case you have multiple linked properties or other data sets).
You should see grouped “events_” tables for each exported date. If you see this table it means data from GA4 is being successfully exported to BigQuery on a daily basis.
You can already preview exported data and make SQL queries on available information.
And that’s it, we have now successfully set up data export from GA4 into BigQuery.
