Google BigQuery and Google Analytics 4 (GA4) together offer a powerful solution for managing, analyzing, and utilizing your data.
Whether you run a small business or a large company, in this guide you will learn:
- the benefits of using BigQuery
- how to set it up
- and what costs to expect.
So you can make the most out of your analytics.
Why Use BigQuery?
BigQuery is a Data Warehouse Solution by Google that allows you to store and analyze large datasets on the cloud where you can easily access and analyze your data.
Once you connect Google Analytics 4 to BigQuery it will create a Raw data export either daily or even in the real-time depending on the export type.
And since this data is stored outside of Google Analytics under your Google Cloud account this leads to several benefit:
1) Data Ownership
Export your raw data and control how you store and analyze it. Keep your data indefinitely, even if Google upgrades Analytics versions (e.g., GA4 to a future GA5).
In Universal Analytics, BigQuery export was available only for Google Analytics 360 customers that most of the analytics users couldn’t afford. So having the possibility to export and manage your data on your own is one of the most loved features of G4 update.
2) Access to Raw Data
- Work with unsampled data for precise analysis.
- Avoid limitations of GA4’s interface and gain deeper insights into user behavior.
By default custom reports in GA4 will be based only on a subset of your data once you’re looking at more than 10 million events.
More than that all information displayed in default GA4 reports is only an estimation so you will not see exact numbers of users, events, Etc. So using BigQuery you can look at the most accurate information.
3) Join with other Data Sources
Your GA4 exported data can be joined together with offline and online data sources in the same report.
Use this to understand your customer Journey better.
4) No reporting limitations by UI
Using raw data you can analyze and segment your data the way you want. You have no limitations imposed by user interface or available report templates.
The data analysis you do in BigQuery is only limited by your SQL knowledge.
5) Customizable Reports
- Create tailored reports beyond GA4’s built-in templates.
- Use any Business Intelligence (BI) tool (e.g., Power BI, Tableau) for visualization.
6) Unlimited Data Retention
In the free Google Analytics version you can create custom reports only including up to 14 months.
So if you want to have a custom report that shows data for more than 14 months then having BigQuery export would be the cheapest way to do this.
So if you’re feeling convinced that this feature will benefit your company let’s proceed and set up BigQuery export in Google Analytics. 👇
Setting Up BigQuery with Google Analytics 4
To set up BigQuery export with Google Analytics 4, we will first verify whether BigQuery is already linked with GA4 or not.
Check BigQuery Export Settings in GA4
Go to Google Analytics 4, open Admin > Product Links > BigQuery Links.
If you haven’t linked BigQuery to your GA4 account, you will have zero active links. But if you see BigQuery project ID linked that you can open, just click on the link and see who has created that. And then contact the person to give you access to BigQuery data.
You can link Google Analytics 4 only to one BigQuery project so if it’s already in place you would need either to get access or create a new link.
So to create a new link, click on the “Link” button and then you need to select the query project where you want to import the data.
If you see no BigQuery projects or you are unable to create a link, there could be multiple reasons for that:
- 👉First you haven’t created any BigQuery projects on Google Cloud
- 👉And Second you have access to BigQuery project
So in my case I haven’t created any BigQuery projects on Google Cloud yet. So I will teach you how to set up a Google Cloud project on your own.
Create a Google Cloud Project
To create a new Google Cloud account and a project go to cloud.google.com and sign in with your Google account. It should be the same account where you have at least edit rights in Google Analytics.
So click on Get started for free.
In Step 1 Select your country and click on Agree & Continue.
In Step 2 – Verify your Identity.
To start using Google Cloud Services, you need to create a payment profile and add a payment method. Google may charge a small amount (like a penny) to verify your payment details, but this is just a one-time verification step.
Now at this point many people get scared😱that if they provide credit card information then Google will charge them insane amounts of money just for using BigQuery.
Here’s why you shouldn’t be concerned:
- Free Credits: You’ll receive $300 in free credits to use over the first 90 days, giving you plenty of time to explore the platform without spending a dime.
- Low Costs for Small Websites: If your website gets less than 1 million visitors per month, your storage costs will likely be very low, or even close to zero, especially during the first few years.
After creating your Google Cloud account, you will land at the homepage. Now we will create a new project by clicking on My first Project.
Then click on New Project.
And give a distinct name to your project.
It doesn’t really matter how you name it here and if you have an organization account you might want to select it in the Location tab. If you don’t have any Organization for this account click create.
After a few seconds your new project should be created.
You should see a notification here in the navigation now you can navigate to your project either by clicking select project or from the drop down on the top.
Enable BigQuery API
By default BigQuery API is enabled on new projects.
However you can double check if it’s enabled by searching API’s and services.
And from here open “Library” now and search for “BigQuery API”.
Click on the third result. 👇
And you should see that enabled here. If not just click on the enable button that would appear here.
Now your BigQuery API is enabled, we will connect Google Analytics 4 with Big Query. But before that let’s Activate your full Account.
Understanding BigQuery Costs
Adding your credit card may not automatically activate the full account. You might need to click the “Activate full account” button shown in the image below to transition from free trial to a paid account.
If you don’t activate your billing account you will work in a sandbox mode. This means that your data export will be stored a maximum of up to 60 days and then deleted from BigQuery.
So you won’t have more than two months of data at the same time. This could be okay for testing or learning purposes but not for the long-term setup.
So let’s quickly break down the cost of using BigQuery. There are two main components of BigQuery pricing:
1) Computing Cost
Computing costs are applied when you work with your exported data by running SQL queries. They become relevant once you already have data export from analytics and you are creating custom reports.
These costs are not applied when you are initially exporting and storing your data inside BigQuery. Each month you get 1TiB of computing volume for free and each next TiB will cost you around $6 at the moment.
2) Storage Cost
Storage costs are directly related to the amount of events that you have in Google Analytics and store in BigQuery. First 10 GB of storage are free and then you will be charged around $0.02 per GB for recent data and $0.01 per GB for any events older than 3 months.
So data import from GA4 will be mostly Active logical storage for the most recent data and Long-term logical storage for data longer than 90 days.
So you can see for the US it’s $0.02 and one cent for older data and this price can vary depending on the region where you store your data 10 GB of data will be around 6 million events as per Google so until you store more than that you won’t be charged for the storage a single cent.
In the real world 99% of your total BigQuery costs will be coming from Computing queries. Once you actually start creating large reports from this data. So hopefully this gives a better understanding of the costs of storage in BigQuery.
Link BigQuery to GA4
Now you can now finalize linking of BigQuery to Google Analytics. Return to GA4 and select the newly created BigQuery project and click Confirm.
Next choose a location where you want to store event data. This should follow your company policies and location.
Then you can configure what data you want to export. In free version of GA4 there is a limit of daily exports of up to 1 million events per day so Google allows you to exclude certain data streams or events from the export.
To comply with that limit if you click on “Configure Data streams and events.” You can select data stream and also exclude certain events by name. If you click on “specify event by name” and enter scroll then scroll events won’t be included in the export.
So in the similar way you can exclude any events that are not adding any value or you can use other events to achieve the same goal.
Next you can export advertising identifiers if you have a mobile app stream.
Choose Export Type
Then you can choose either to have only “Daily” export or real-time data which is called “Streaming.”
Daily data will be free upto certain volume. You can’t pick “Streaming” realtime export if you haven’t added a billing account. Streaming export will cost you around $0.05 per 1 GB of streaming data or $0.05 per 600,000 events.
So if you have 600,000 events per day it will cost you around $0.05 per day if you enable this export.
If you’re okay with a couple of day delays then enable “Daily” export.
User Data
Last option we have is User Data Export. This would be useful if you want to monitor how user level attributes have changed over time. Or get number of users whose lifetime value has increased in certain date ranges. And you can do the same for other user attributes as well.
This export won’t contain any event data as the standard daily export. So once you’re ready click Next.
Review the settings, and once you submit you should see success message that BigQuery link has been created now.
See your First Data in BigQuery
Typically within 24 hours you will start seeing First Data in BigQuery so let’s see how to find it.
After the first 24 hours and sometimes a bit longer you should be able to see your first data in Google Cloud. Open your Cloud console again on cloud google.com and from navigation menu find BigQuery > BigQuery Studio
From there expand your project name and you should see a new data set that is named “Analytics underscore your account ID.”
Inside of this data set you will find all the tables that are exported from Google Analytics based on what export type you have selected.
Daily export will be under “events_(1)” and the number in the brackets will show how many tables or days of day data you have stored there.
So in this example we have 1 day data.
Then there will be another table called “events intraday” where streaming data export stores data. Typically it is stored for the current day plus any days that haven’t made it yet into Daily export table. Once given date has made it to the Daily export it is deleted from the intraday table.
And finally you could see up to 2 additional table if you have enabled user data export. You will see at least one table for pseudonymous_users and one for visitors that had user ID attached.
Now your setup is complete. Unlock the potential for advanced reporting and deeper customer insights.
Conclusion
In this blog we have covered how to export your GA4 data in BigQuery, the cost-effective pricing structure, and export options to suit your needs.
Integrating Google Analytics 4 with BigQuery is a game-changer to use the power of raw data.
This setup gives you complete ownership of your analytics, access to granular insights, and the flexibility to join GA4 data with other data sources.
The ability to create custom reports and retain data indefinitely makes this an invaluable tool for both small businesses and large enterprises.
For the next step, we will learn how to query your GA4 data in BigQuery. This guide will teach you how to extract meaningful insights from your exported data using SQL. Read more about querying GA4 data in BigQuery here.