Do you want to get access to all your raw Google Analytics data starting from today? Learn how to seamlessly connect Google Analytics 4 (GA4) to BigQuery!
Hit-level data is now not only accessible anymore for GA360 (paying customers).
Literally in minutes everybody can set up an integration between the brand new Google Analytics 4 (GA4) property, previously called Google Analytics App + Web, and BigQuery!
In this post I will list all steps to get started with the BigQuery integration and unlock advanced optimization capabilities!
Table of Contents
- Why BigQuery is a Real Game Changer
- Setting up a Google Analytics 4 Property
- Linking a GA4 Property to BigQuery
- Your First Query
Let’s dive right in!
Why BigQuery is a Real Game Changer
Probably most of you have already heard about BigQuery. Learn the basics of digital analytics and data analysis first if you are very new to Analytics.
You really want to expand your skills in this area if you and the organization you work for are already leveraging Analytics data in various ways. Going beyond the reporting (and analysis) interface of GA4 can bring a ton of additional insights.
There are no billing charges associated with exporting data from a Google Analytics 4 property to BigQuery. You can export to a free instance of BigQuery (BigQuery sandbox). However, exports that exceed the sandbox limits incur charges.”
A couple of advantages and use cases are listed below:
- No sampling
- Unlimited amount of dimensions
- Calculate aggregated goal completions
- Build your own Channel Grouping
- Correct data errors on past data
- Combine Google Analytics data with third party data sources
- Use native BigQuery connectors to push data into advanced visualizations tools
You need to set up a GA4 property first before the integration can be enabled.
Setting up a Google Analytics 4 Property
Here is a quick tutorial on how to create a Google Analytics 4 Property.
Prerequisite: Edit-level access to the Google Analytics account where you want to create a GA4 property.
Step 1: navigate to the admin interface of Google Analytics.
Step 2: click on “Upgrade to GA4”.
Please note that the upgrade for GA4 only applies to users of Firebase for Mobile Apps. For current Google Analytics users, there is no planned ability to carry over historical data from Universal Analytics to Google Analytics 4. This means the upgrade refers to implementing the tracking code side-by-side with your existing Google Analytics (Universal) properties.
Step 3: click on “Get Started”.
Step 4: click on “Create Property”.
“If your site uses the gtag.js tag, you’ll have the option to Enable data collection using your existing tags. However, if you’ve implemented any custom tags for your Universal Analytics property, this option won’t replicate the functionality for your Google Analytics 4 property.”
Step 5: explore the “Setup Assistant” and implement the GA4 Configuration Tag (via GTM).
This is how a basic GA4 Configuration Tag looks like:
The great thing is that you can add this GTM Tag to the same container as where all your Universal Analytics tags are running.
Note: the Setup Assistant will take you to all the necessary steps to get up and running with GA4. This blogpost focuses primarily on the Google Analytics 4 – BigQuery integration. I recommend working through the official setup docs from Google if you need extra support with the initial, basic setup of a GA4 property.
Linking a GA4 Property to BigQuery
As you probably have noticed, the BigQuery link has popped up under “Product Linking”.
Step 1: click on “BigQuery Linking” under “Product Linking”.
Step 2: click on “Link”.
Step 3: create a new BigQuery project if not yet done.
- Click on “Learn more” if you need to create a BigQuery project first.
- Click on “Choose a BigQuery” project if you have your project already created.
Step 4: link to a BigQuery Project.
Step 5: click on “Confirm” in the top right corner after selecting your project.
Step 6: select the Google Cloud region for your data when you set up an export.
Data location is important especially in the light of GDPR, privacy etc.
Also, (potential) cost per region may vary so choose wisely.
Note: If your Cloud project already contains an export dataset, then you cannot change this setting. If you want to change the region for the dataset, you need to delete the existing BigQuery link and create a new one.
Step 7: select the data streams and frequency setting.
- If you have a mobile app as one of your data streams, you can also choose to export the mobile advertiser identifiers.
- I highly recommend choosing Daily and Streaming export.
- BigQuery streaming export makes fresher data for the current day available within a few minutes (or even seconds) via BigQuery Export.
- Additional costs should be very low, learn more here.
Step 8: click “Submit” if everything looks good to you.
Step 9: enable the BigQuery API.
This is the API that connects Google Analytics 4 to BigQuery.
- Log in to the Google APIs Console and select your project.
- Navigate to the APIs table.
- Open the Navigation menu, click APIs & Services, then click Library.
Last thing to do is to verify that you’ve added a service account to your Cloud project.
Verify that firebase-measurement@system.gserviceaccount.com has been added as a member of the project, and given the primitive role of “editor”. Add the account if necessary.
This is what it looks like in Google Cloud:
Now your API and connection are ready.
Step 10: wait one day.
Wait until the next morning (some people need to wait a bit longer), then go to your BigQuery project. You will now see your raw Google Analytics data from Google Analytics 4.
Note: data is only exported from the date of enablement, not retroactively.
Step X: delete your link to BigQuery (obviously, only do so if you have a good reason).
You can, for whatever reason, delete your link to BigQuery.
Go to the BigQuery Linking page.
Click on “Delete” in the top right corner.
Your First Query
Getting started isn’t easy if you don’t know BigQuery and SQL. There are a ton of resources available to help you get started with BigQuery.
Also, I expect a lot of awesome tutorials about BigQuery and Google Analytics 4 to be published in the near future!
I recommend checking out these resources to get started:
- Quickstart using the Cloud Console.
- #BigQueryTips: Query Guide to Google Analytics 4.
- Mode SQL Tutorial.
This is it from my side. I hope this post supports you in setting up your newest integration with BigQuery!
Now it’s your turn! Are you already leveraging BigQuery and GA4 and do you have any tips to share?
One last thing... Make sure to get my automated Google Analytics 4 Audit Tool. It contains 30 key health checks on the GA4 Setup.
I have made the link between GA4 and Big Query. That’s easy to do. I’ve enabled billing. I’ve waited several days but no data is coming in? I just did the daily transfer of data. If I do streaming, do you know if we get charged for that? Any ideas why the data is not coming through yet? The main reason I’m using big query is because I can’t get user properties over to Google Data Studio. I was told that if I send the data over to Big Query then I can send over to Google Data Studio and user properties will be there. Please advise. Thanks!
Hi Mike,
Difficult to debug from my end as I can’t see how everything is exactly set up. So far, I haven’t heard anyone having difficulties getting the data into BigQuery (it usually takes a few hours to one day max before it starts). Regarding costs, the Cloud Streaming service is priced around $0.05 per GB I believe. Usually this doesn’t lead to high costs.
Best,
Paul
Hi Paul, thanks for sharing a step by step procedure for linking Google Analytics 4 with BigQuery! I have faced problem in understanding Google Analytics 4, do you have any article explaining what is the difference between the old version and new?
Thanks for the heads up! Here you go: https://online-metrics.com/google-analytics-app-web/
Hi Paul Koks!
I linked the Firebase to Bigquery, But I couldn’t able to see all previous events data, I can see a few day’s events data which is after I linked. cant I get all raw events data?? If yes please could you help to get full events raw data?
Hi,
Unfortunately, you are not able to get all raw events data retroactively.
It only works from the time you have set up the integration.
Best,
Paul
Hi Paul! Thanks a lot for your informative article.
The only thing I’d like to add is the opportunity to export Google Analytics data into BigQuery via third-party tools such as Skyvia. It has plenty of supported sources in addition to GA and GBQ and allows you to automate data export.
https://skyvia.com/data-integration/replicate-google-analytics-to-google-bigquery
Hi Michael,
Thank you for your note – that’s very helpful indeed.
Best,
Paul
Hi Paul! Is there a workaround to get past data? I understand the export will only do it form the day is setup, but wonder if a python query through the API could get the data from before. Have you tried anything similar?
Thanks. Best,
Javier
Hi Javier,
I haven’t tried that yet, but you might want to touch base with Johan van de Werken: https://www.ga4bigquery.com/contact/.
He might be able to give you a proper answer.
Best,
Paul
Hi Paul, how would you go about combining multiple GA4 properties into one BigQuery project? Unfortunately, this doesn’t seem like it’d be a straight forward process. I’m trying to find a way to consolidate all of our clients data into one data source for ease of reporting. I thought BigQuery might be a good solution for this but if not do you know of any better alternatives? Thanks in advance!
Hi Mike,
For this query I recommend contacting Johan van de Werken (https://www.ga4bigquery.com/contact/).
He is a real master in this specific area.
Best,
Paul
Hi Paul,
Do I need to create the dataset in the big query project or will that be done for me once the data starts flowing in?
Thanks!
Hi Luke,
This should all go automatically after you set up the integration / linking. It can take up to 24 hours though before the data starts flowing in.
Best,
Paul
Hi,
Why did you add firebase-measurement as an email, e.g., firebase-measurement@system.gserviceaccount.com .
Can it be something else like petar.petrov@system.gserviceaccount.com
Thanks
Hi Petar,
A service account is a non-human account that is used to run automated services.
For GA4, the service account is firebase-measurement@system.gserviceaccount.com.
You can see all details here.
Hi, please explain how to “Correct data errors on past data” in Google Analytics using BigQuery connection. For example is it possible to remove part of goals that were counted by error?
Hi,
Unfortunately, this goes far beyond this post to explain in all depth.
In short, you can use BigQuery to analyze and correct the data, BUT in most cases not to sent it directly back to GA. It’s done outside of the GA4 UI.
So, to answer your specific question, no, you won’t be able to correct goal measuring mistakes in the GA4 UI.
Thanks Paul! So – if I alter data in BigQuery that is linked to GA I will not see this change in my GA account (after logging to analytics.google.com)? In other words I get access only to GA database that is copy of actual database that my GA account is based on right?
That’s right, your RAW data is exported to BigQuery, but making changes to the data that shows up in BigQuery only affects that environment and the data that is stored there and not the data in the GA4 UI itself.
You can read this article to learn more.
Hey Paul,
im having troubles with load jobs of the tables with previus day data. For ex. today, 28.9.2022 i have the newest data from 25.09.2022 in BigQuery from GA4 and last load job was 25.9.2022. Why is this link GA4-BQ does not work on daily basis? Any idea, what did i do wrong?
Thanks for help, M.
Hi Mitch,
Most probably you didn’t do anything wrong here, but it is a general load job/delay more people are experiencing. I have seen the same pattern on my side. Today, I can access 26.09.2022, but not yet 27.09.2022.
I recommend monitoring it over the next week. These delays seem to affect some people from June this year.
Best,
Paul