Back to Blog

GA4 to BigQuery Integration – 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!

How to connect Google Analytics 4 to BigQuery?

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 explain each step in a lot more detail in the following chapter.

That’s the default approach that will suit most users. However, if you want to run a GA4 export to BigQuery on a one-time basis, for example, when preparing a periodic report, a manual method is a viable option too. It relies on exporting data from Google Analytics as a .csv file and loading it into BigQuery. We’ll explain it in the later chapter too.

Steps to link GA4 to BigQuery

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

BigQuery setup

The first step of the process is to set up a project in BigQuery. You can either use an existing one or create a new project strictly for GA4 data. Head over to your BigQuery console and click the projects menu.

1 bq project

Choose an existing project from a list or create a new one using the New Project button on top. In the new window, pick a project name, decide on the organization it should be attached to, and pick a location in the projects’ structure.

2 set up bq project

Afterwards, click the Create button.

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.
3 bq library
  • 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.
4 bq add principals

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.
5 add bq link
  • Here, click the Choose a BigQuery project button and select the right one from the list. Also pick the data location.
6 choose project
  • 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 continously 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.
7 link created

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.

8 bq tables view

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.

9 ga4 download report

Once you’ve downloaded the file, import it into BigQuery using 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.

Another option you may find useful is exporting GA4 data via BigQuery and into a spreadsheet – for example, Google Sheets or Excel. Coupler.io can automatically sync the results of SQL queries into the desired destination and do so on the frequency that works best for you. For example, you can choose to export GA4 to BigQuery daily and then sync some of that data into a spreadsheet every morning.

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

Back to Blog

Comments are closed.

Focus on your business
goals while we take care of your data!

Try Coupler.io