Connect GA4 to BigQuery – Your Step-by-Step Guide

BigQuery has long been a preferred destination for data export from Google Analytics, and for good reason. Native integration makes it straightforward to access raw analytical data in a data warehouse and freely query and manipulate it. This, as a result, can help you drill down into vital events, filter out the irrelevant or faulty fields and make your analysis a lot more accurate.

With the approaching sunset of Universal Analytics and the rise of GA4, the methods to sync analytics into BigQuery also change. To help you with the transition, we’ve prepared this guide explaining the options available for a GA4 export to BigQuery. So, let’s start!

What methods can you use to connect Google Analytics 4 to BigQuery?

There are three different methods that allow you to do this:

• Through Google API – this way, you can directly connect GA4 to BigQuery – to do so, you will need to configure both apps accordingly and spend some time adjusting the settings. The advantage of this method is that it’s free and it allows you to stream GA4 data in real time. The downside is that it’s relatively complex and might be time-consuming for an inexperienced user.
• With a third-party tool – you can select a user-friendly solution that will allow you to easily connect GA4 to BigQuery in just a few minutes and export your data automatically on a schedule. The advantage of this method is its simplicity and the ability to use the same tool to load marketing data from other apps to BigQuery for cross-channel analysis. 
• Manual export – this implies using native functionality and exporting GA4 data as a CSV file and then importing it to BigQuery. This method is very simple, and it will be a good fit for a one-time GA4 export to BigQuery. However, it’s inefficient for repeated data transfers – in this case, it’s better to opt for an automated connection.

Now, let’s explore each of these methods in more detail.

How to connect Google Analytics 4 to BigQuery using Google API?

Google API lets you connect GA4 to BigQuery and stream your analytical data at a chosen frequency. The process comes down to four steps:

1. Create a BigQuery project.
2. Enable and configure BigQuery API.
3. Link GA4 property to the BigQuery project.
4. Review your first GA4 to BigQuery export and start processing the data.

We’ll now explain step by step how to link Google Analytics 4 to BigQuery for smooth data transfers with this method.

BigQuery setup

First, you’ll need to create a new project in BigQuery.

The next step is to enable BigQuery API if you haven’t done so before. To do that:

• Go to the Google APIs Console.
• Open the APIs & Services menu
• Then, pick Library from the left-most menu.

• Find and click on the BigQuery API and choose to enable it.

Before you proceed, you also need to add the firebase-measurement@system.gserviceaccount.com account to your BigQuery project. To do that:

• Click the hamburger menu in the top-left corner of the screen and go to IAM & Admin -> IAM.
• Click Add on top.
• Paste the address above and grant it an Editor access. Then click Save.

GA4 setup

Now let’s set up Google Analytics. 

Before you proceed, you’ll want to make sure that:

• You sign in to the GA account with an email address that also has owner access to the BigQuery project you just set up.
• The GA account has editor access to the property you’re going to export the data from.

Then:

• Jump to the account meeting this criteria at the Google Analytics platform.
• Click the Admin button with the gear icon in the bottom-left corner of the screen.
• Scroll down until the Product Links section and select BigQuery Links.
• Click the Link button to the right.

• Here, click the Choose a BigQuery project button and select the right one from the list. Also pick the data location.

• Then, configure the remaining settings. 

An important aspect here is the frequency of exports. You can choose to run a GA4 export to BigQuery once a day or do so continuously via streaming. You can also select both options to have both daily and real-time data synced into your database.

When you stream data from GA4 to BigQuery, the events arrive into a database within seconds after they were recorded. For each day, an events_intraday_YYYYMMDD table is created and it’s populated throughout the day with the fresh data. 

This option is useful for analyzing real-time data but it’s not recommended if you, for example, want to query for events recorded on a particular date. Due to the real-time nature of the functionality, certain events recorded towards the end of the day could be skipped. Failed uploads could also factor into data inconsistency.

Another option is to connect GA4 to BigQuery with a daily export. It will result in an events_YYYYMMDD table created towards the end of a day. This is a more reliable source of information for queries encompassing days or any longer periods.

One thing is worth noting though for when you choose to use both options. When a daily events_YYYYMMDD is created towards the end of the day, an events_intraday_YYYYMMDD table for the same data will be automatically deleted from BigQuery, so as not to unnecessarily duplicate the data.

• As the final set, review the information and confirm that you want to export data with a configured link. You’ll see the confirmation, and the data will start flowing from GA4 to BigQuery at the chosen frequency.

And this is the result of our data export. The dataset contains a property ID from Google Analytics while the date in YYYYMMDD format is visible in the table name.

How to export data from GA4 to BigQuery with a third-party tool

As you can see, connecting GA4 to BigQuery via API requires investing some time and dealing with quite a few settings. Using a third-party tool, you can set up an automated connection much faster and easier. 

In our example, we’ll demonstrate how you can connect Google Analytics 4 to BigQuery with Coupler.io. This is a user-friendly data integration solution that can automatically transfer data to BigQuery, Excel, or Google Sheets. Google Analytics 4 is one of the 30+ data sources available in Coupler.io, along with Google Ads, Facebook Ads, LinkedIn Ads, Mailchimp, Pipedrive, Shopify, and many more. This makes it a good choice if you want to collect your marketing data from various apps and load it to BigQuery for further processing. In addition to this, Coupler.io can automatically refresh your GA4 data in BigQuery on a schedule. This is very convenient for backup purposes or deep analysis.

Let’s see how to export data from GA4 to BigQuery automatically with Coupler.io.

• First, sign up for Coupler.io. You can do it in just one click using your Google account or email address.
• Then, go to My importers -> Add new.
• Select the data source and destination – in our case, GA4 and BigQuery. Then, name your importer.

Data source

• Connect your Google Analytics account.

• Select one of the sub-account available in your GA4 source account. 
• Then, specify the property for exporting data. Press Continue.

• Select up to 9 dimensions and specify up to 10 metrics that should be included into your report. 
• Choose the time range for the exported data. Or leave these fields blank to extract information for the last 30 days.

• Coupler.io can create a separate entry showing the total, minimum and/or maximum value of the selected metrics. Specify your preferences if you want to include a separate row with this information in the BigQuery table. If this is unnecessary, leave this field blank.

• If you want to export more metrics or dimensions, you can use Coupler.io’s Data Stitching functionality. To do so, click Add one more source and specify your preferences, selecting other metrics or dimensions. Both datasets (the first and the second one) will be imported into the same destination file.

Data destination 

• Connect your BigQuery account.
• To complete the connection, you will need to provide the Key file for your BigQuery project. See these instructions on how to get the key. 
• Once you get the key, upload it in the connection form. The Project Id and Connection name fields will be filled in automatically. Press Save.

• Specify the dataset and the table where Coupler.io should export your data.

• Toggle on Autodetect table schema  – this will help to structure your data correctly while importing.

• Select the import mode. For the first import, it won’t change the result, but it will matter when you set a schedule for the updates. If you select Replace, during each data refresh, the old GA4 dataset will be replaced with its updated version. If you opt for Append, the updated version will be placed below the previous one. The latter can be useful if you need to track changes chronologically.

Scheduling updates

• Toggle on Automatic data refresh.
• Select the interval for the updates. You can choose between daily, weekly or monthly updates, or you can opt for refreshing your data every 15 or 30 minutes.
• Specify the days of the weeks and the time interval for the updates. 
• Select your time zone and press Save and Run.

• After this, your data will be exported from GA4 to BigQuery. Coupler.io will automatically keep it up to date, refreshing it on your schedule. Here’s an example of data exported from GA4 to BigQuery:

How to export data from GA4 to BigQuery manually

It’s not a common situation, but it may happen that you’ll want to export only the specific data to BigQuery and do so on an individual basis. For example, you’ve just finished a campaign driving traffic to a specific landing page and want to back up the related analytics into a data warehouse.

You won’t need to connect GA4 to BigQuery to do that. Instead, you can export a report into a .csv file and then import it into BigQuery.

To export a report from Google Analytics, load it and click the Share button in the top-right corner. Choose Download file and pick CSV as a format. PDF is another option but it won’t be any good for importing into BigQuery.

Once you’ve downloaded the file, import it into BigQuery using one of the available methods.

GA4 to BigQuery – recap

Exporting data from Google Analytics 4 to BigQuery opens up many opportunities for you. You can drill down into the raw data behind your website or app and extract precisely the insights you need. Such data can then be loaded into other databases, such as AWS or Azure, and showcased on dashboards in Data Studio, Tableau, or elsewhere.

In this blog post, we covered three different methods for exporting data from GA4 to BigQuery. This includes connecting the two apps via Google API, extracting data manually, and setting up an automated integration with a third-party tool, Coupler.io. 

To sum up, manual export is suitable for a limited number of cases, mostly for one-time exports or for rare updates. Connecting via Google API is a viable option for ongoing data streaming. It’s free, and it’s natively available. This is a good method if you don’t mind dealing with the settings and if you only need to connect GA4 to BigQuery. However, if you prefer a simpler flow with an intuitive interface, and/or want to load to BigQuery data from other marketing apps as well, then, Coupler.io is an efficient solution to use in such a case. The tool can automatically transfer data from multiple sources to your database and keep it up to date without any manual effort from your side.

If you wish to visualize your data, take a look at our guide explaining how to connect GA4 with Looker Studio.

Try it out and see how it works for you. Thanks for reading.