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 sunset of Universal Analytics and the rise of GA4, the methods to sync analytics into BigQuery also changed. 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?

So, your goal is to export your Google Analytics data to BigQuery. Three different methods allow you to do this:

• GA4 to BigQuery connector by Coupler.io – 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. 
• 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.
• 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 with Coupler.io

Coupler.io is a data automation and analytics platform to turn raw data into meaningful reports. This user-friendly solution allows you to automate data transfer from business apps to BigQuery, Excel, Google Sheets, Looker Studio, or Power BI. 

Google Analytics 4 is one of the 50+ 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 importer.

Select the data source and destination – in our case, GA4 and BigQuery. Then configure the connections.

Step 1. Extract data from Google Analytics

• Connect your Google Analytics account. Select one of the analytics accounts available in your GA4 source account. Then, specify the property for exporting data.
• Specify the report period and select up to 9 dimensions and up to 10 metrics that should be included into your report. 

• 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.

Note: 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.

Step 2. Transform data

Coupler.io will fetch up to 500 rows to preview and transform the GA4 data. You can:

• Hide/unhide columns
• Sort and filter data according to the specified criteria
• Create new columns using the supported formulas. 

Step 3. Manage data

Now you’re ready to 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.

Once the BigQuery account is connected, specify the dataset and the table where Coupler.io should export your data. You can create a new dataset and table by typing new names.

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

Optionally, you can change 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.

Before you click Save and Run, toggle on Automatic data refresh and configure the desired schedule to automate data load from Google Analytics 4 to BigQuery. Coupler.io can refresh data as frequently as every 15 minutes!

After that, press Save and Run to export data 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:

Using a third-party tool, you can set up an automated connection fast and easily. We’ve demonstrated how you can connect Google Analytics 4 to BigQuery with Coupler.io. However, this method is not free, although you can start with a 14-day free trial.

How to export data from GA4 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.

This method for connecting GA4 to BigQuery is free. However, it’s not very comfortable and straightforward compared to what you have with Coupler.io. Is there any other way to complete the connection? Probably not, but you can move data from Google Analytics to BigQuery manually.

How to export 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.

Which of these methods allows you to load GA4 historical data to BigQuery?

You can load GA4 historical data to BigQuery using both Coupler.io and Google Analytics API. However, with Coupler.io, you won’t be able to deep into the user level that you could get with native GA4-BigQuery import.

You can backfill historical data with the GA4 API but this method would require involving new work to extract the data and load it into BigQuery. 

There are talks about a backfill feature, however, the release date is not known yet.

Which Google Analytics properties data can you export to BigQuery?

A property in Google Analytics is a website, blog, or application that is associated with a unique tracking ID. In your GA account, you can have multiple properties. 

As a rule, you can export analytics information about all of these properties using the methods that we’ve described above.

In Coupler.io, you’ll only need to select a Google Analytics property to export data from.

For other methods, you can specify the property on your GA account that you need.

After you’ve exported Google Analytics data to BigQuery, what can you do with your data?

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.

Back to Blog