How to Connect and Export Data from GA4 to BigQuery?

As Google Analytics transitions to GA4, an important feature of many analytics pipelines has also been changing. This transition makes it necessary for users to sync their analytics from GA4 into BigQuery with different methods than before. To make this process simpler and easier to understand, we have put together this guide which explains all the options currently available for exporting GA4 data into BigQuery. From this guide, you can learn key details about how each method works and what it’s best suited for so that you can make the best decisions for your company’s specific usage needs.

Why Enable the BigQuery Linking to GA4?

The new version of Google Analytics — GA4 — introduces several new features and changes, one of which is the ability to link GA4 to BigQuery. BigQuery is Google’s cloud-based data warehousing service. Enabling the BigQuery linking feature will allow you to export your GA4 data to BigQuery for further analysis.

There are several reasons why you should enable the BigQuery linking feature in GA4. First, it will allow you to take advantage of BigQuery’s powerful data processing capabilities. With BigQuery, you can run complex queries on large datasets that would be difficult or impossible to do with other data processing tools. Second, linking GA4 to BigQuery will give you access to your raw data. This is important because it will allow you to perform custom analyses that are impossible with the out-of-the-box reports in GA4. Finally, linking GA4 to BigQuery will help you save money on storage costs. This is because BigQuery charges based on the amount of data processed, not stored.

Further Reading: Why Migrate to Google Analytics 4 in 2023? UA vs. GA4 Metrics Comparison

Methods to Connect Google Analytics 4 to BigQuery

There’re three different methods that allow you to connect Google Analytics 4 to BigQuery. They are:

1. Google APIThe first method for connecting GA4 to BigQuery is through the Google Analytics Data Export API. This option requires some technical know-how and coding skills, as you will need to set up an application to access the API. Once set up, you can use the API to pull raw analytics data into BigQuery, where it can be analyzed further using advanced analytics tools like SQL or Python.
2. Third-Party Tools A second option for connecting GA4 to BigQuery is using third-party tools such as Stitch Data or FiveTran. These solutions offer an easier setup process than using the Google API, as they take care of all the intricate details such as authentication, scheduling, filtering, and more. They also offer additional features like automatic schema detection, custom transformations, and more that make it easy to get actionable insights from your data.
3. Manual Export The final option for connecting GA4 data to BigQuery is manual export. This involves manually exporting your analytics data from GA4 into CSV files which can then be imported into BigQuery using either the web UI or one of its command line utilities. This method may not be suitable for large datasets due to its manual nature, but it might be helpful if you want quick access to specific metrics without having to go through complex setup processes.

Now let’s look into them in detail.

Connecting GA4 with BigQuery through Google API

The first step in connecting GA4 with BigQuery is setting up your account. You’ll need to set up a Google Cloud Platform (GCP) account and a Google Analytics 4 property for the connection to work.

Once those are set up, you can start the process of setting up the connection between them by following these steps:

1. 1. Go to the Google Cloud Platform Console and select either an existing project or create a new one

Source: console.cloud.google.com

1. 1. Select “APIs & Services” from the left menu bar, then click on “Library”

Source: console.cloud.google.com

1. Find and select “BigQuery API” and enable itBefore you move further, you’ll also need to add the firebase-measurement@system.gserviceaccount.com account to your BigQuery project. To do that:
   • • Click the menu in the top-left corner of the screen and go to IAM & Admin -> IAM, and click Add on top

   

   Source: console.cloud.google.com

   • Paste the address above and give it Editor access; then click Save

   

   Source: console.cloud.google.com

2. Then you need to sign in to your Google Analytics account and use an email address with the Owner access to the BigQuery project and an Editor role for the Analytics property that includes the data stream you want to link
3. In the GA4 Admin page, make sure you have the desired account and property scheduled.
4. Then click BigQuery Links in the Property column under PRODUCT LINKS.
5. 
6. Click Link → Choose a BigQuery project and select a project from the list; click Confirm
7. Then select a location for the data; if your project already has a dataset for the Analytics property, you can’t configure this option
8. Click Next and choose Configure data streams and events to select 1) the data streams to include with the export and 2) specific events to exclude from the export.
9. Choose a Daily or Streaming export of data (or both of them):

Note about the table updating:

• Updates to the tables created as part of BigQuery Export are governed by the time zone of the Analytics property from which data is being exported.
• Streaming-export tables (events_intraday_YYYYMMDD) are updated continuously throughout the day (e.g., from 12:00:00 am until 11:59:59 pm in the property’s time zone); once a new day starts in the property’s time zone, events are written to a new intraday table.
• Daily export tables (events_YYYYMMDD) are created after Analytics collects all of the events for the day. Analytics will update daily tables for up to 72 hours beyond the date of the table with events that are time-stamped with the date of the table, e.g., event bundles that come in late from Measurement Protocol or the Firebase SDKs. For example, if the table date is 20220101, then Analytics will update the table through 20220104 with events that are time-stamped 20220101.

You can check more information here.

We recommend not using the data in the reporting until the full update occurs. Tables are usually updated within 12 – 24 hours.

Finally, review your settings and confirm that you want a configured link; the data will soon start flowing from GA4 to BigQuery at the chosen frequency.

Exporting Data from GA4 to BigQuery Manually

It’s not a common situation, but sometimes you’ll need to export only the specific data to BigQuery and do it individually. In this case, you won’t need to connect GA4 and BigQuery and then import it into BigQuery. To export a report from Google Analytics 4, load it and click the Share button in the top-right corner. Select Download file and pick CSV as a format. Another option is to choose PDF, which won’t be good for importing into BigQuery.

Enable the BigQuery Linking for GA4

Connecting and exporting data from GA4 into BigQuery is an efficient way for digital marketing specialists or digital analysts who rely heavily on their website analytics for insights about their customer’s behavior and preferences. By following these simple steps outlined above, anyone can connect GA4 with BigQuery quickly so they can start leveraging their website analytics more effectively! But if you still need help connecting GA4 and BigQuery or feel confused about migrating to GA4, our team is here to help you. Contact us now and get our experts on a call.

Get in Touch Now