Google Analytics 4 and Google BigQuery linking — complete guide

One of the main advantages of the new Google Analytics 4 is the free export of raw unsampled data to Google BigQuery. Recall that in Universal Analytics, this option was available only in the paid version of Google Analytics 360.

In this article, we explain how to correctly export data from Google Analytics 4 to Google BigQuery and what you need to pay attention to in order to get the most out of the collected information.

If you want to collect raw data in Google BigQuery without Google Analytics 4 limits, use OWOX BI. With OWOX BI Streaming, you get each event from your website in your data warehouse in near real-time.

Table of contents

Why you need Google Analytics 4 and Google BigQuery integration

By integrating Google Analytics 4 with Google BigQuery, you can collect raw, unsampled data from your website in cloud storage, where each user and their events will be displayed on separate lines. Using SQL queries on this data, you can calculate any parameters and indicators you need.

By collecting site statistics in Google BigQuery, you can avoid sampling and other limitations of Google Analytics 4. You will be able to analyze complete data, which means the quality of decisions made based on this data will be much higher.

For example, the default retention period for user and event data in GA 4 is two months. You can increase it to 14 months in the resource settings. However, this may not be enough if you want to analyze events of users who last logged in more than 14 months ago. That is, you can’t generate a report for a range of two years. This is where Google BigQuery can help. After exporting data to BigQuery, you can store it without time limits.

Benefits of Google Analytics 4 and Google BigQuery linking

1. Full and unsampled data

In Google Analytics 4, standard reports are not subject to sampling, but sampling can be used in custom reports: when comparing data, when using additional parameters and filters, when the limit of 10 million events is exceeded. Sampling is also applied when the date range is greater than 60 days.

Sampling can distort data, so you can’t trust 100% of reports built using sampling. This is especially true for reports over a long period of time or for custom reports with a large number of parameters and indicators. By uploading raw data from Google Analytics 4 to Google BigQuery, you can avoid this problem.

2. Reports without restrictions (and with custom adjustments)

The interface of Google Analytics or any other analytics system limits your ability to create reports. For example, there are limits on the number of parameters and indicators as well as on how you can combine them and perform calculations based on them. Also, in GA 4, there is no ability to import cost data, create custom channel groupings, or include metrics from third-party sources such as a CRM in your reports.

By having access to raw data in Google BigQuery, you can build reports with any number and combination of metrics you need. For example, you can perform cohort analysis based on any indicators that are important for your business.

3. Reports based on sales & customer data (completed orders)

Raw data from your website can be merged with data on ad spending from advertising services, call tracking data, email newsletter data, and data on redeemed orders from your CRM to set up end-to-end analytics.

This will allow you to take into account all points of contact with your company, analyze users’ conversion paths, evaluate the impact of all marketing efforts (both online and offline) on business performance, find the most effective marketing channels, and quickly optimize channels that bring losses.

4. Custom attribution models based on your rules

Once you have collected and merged all your marketing data, you can create complex sales funnels that fit your business structure and build your own attribution models to evaluate the contribution of each advertising channel to sales.

For example, you can evaluate the contribution of channels to the marketing funnel with OWOX Funnel-Based Attribution. The Funnel-Based Attribution model calculates the value of each channel and campaign in the funnel according to its contribution to the user’s progress through the funnel steps. To make this calculation, aggregated data is not suitable — we need data about each user action, linked by User ID.

5. Predicting conversions with a custom feature set

Google Analytics 4 provides predictive metrics (purchase probability, churn probability, and revenue forecast) that are calculated using machine learning. However, the model is a black box and operates solely on GA 4 data.

Let’s say a data analyst is tasked with creating a model that predicts the probability of a purchase. It’s expected that this model will be as transparent as possible (it will be clear which features it’s trained on) and formed based on all available data, including custom data. In this case, the Google Analytics 4 interface is no longer enough for the analyst, as they don’t know what’s inside the predicted metric that Google Analytics calculates. If you need to understand and manage the model — set your own features for training it, for example — it’s best to upload your data to BigQuery.

OWOX BI has developed an ML-based solution that calculates the purchase probability considering purchased orders for each website user. Based on this calculation, you can create audiences, use them to target advertising campaigns, and double your ROI, as one of our clients did.

6. Grouping individual channels

In Google Analytics 4, a standard predefined channel grouping is available (Paid, Search, Organic, etc.). This means you won’t be able to build a report with your own channel grouping in the GA 4 interface — at least not yet. (Perhaps this option will be added in the future.)

For example, your facebook/cpc campaigns go to the Paid channel group by default, but you may need to separate them into a Paid Social group. In this case, uploading data to BigQuery and grouping channels using SQL will help.

7. Filtering statistics

Say you need to show statistics to a partner based only on a UTM tag and not disclose any other information. In the Google Analytics 4 interface, you cannot create a separate view for this. There is only a single view of the resource — that is, all partners will see all data.

Google BigQuery allows you to load large amounts of data and quickly process it using SQL. You can automate and schedule reports so your team can focus on getting insights instead of preparing reports.

Google Analytics 4 and Google BigQuery cost linking

In Google Analytics Universal, this integration was available only for Google Analytics 360 enterprise resources. In Google Analytics 4, exporting data is free for everyone, and you only pay for the actual data storage and processing when you exceed the Google Cloud free tier limits of 1 TB of requests per month and 10 GB of data in storage.

You can also use the BigQuery sandbox for free data export. In this case, sandbox restrictions will apply and you risk data tables expiring after 60 days. If you upgrade to a paid account before this deadline, make sure you update your table expiration settings so you don’t lose your data.

How to link Google Analytics 4 and Google BigQuery

If you’ve already had a project in Google BigQuery and the information you need is collected in Google Analytics 4, you can start exporting it.

1. Create a project in the Google API Console

Sign in to the Google API Console. Create a new project or select an existing one.

Google API Console. Create a new project

2. Enable the Google Cloud API

Open the Google API Console project you’ve created and select APIs & Services from the sidebar, then select Library:

Google API Console Library

Enter BigQuery API in the search bar:

BigQuery API

Find BigQuery API in the search results and click on it:

Make sure BigQuery API is enabled and click Manage.

BigQuery API is enabled

Add a service account to your Cloud project. To do this, click the Create credentials button in the upper right corner:

Create credentials

In the opened window, select Application data to create a Firebase service account. It will be used to export Google Analytics 4 data to BigQuery:

Scroll down the screen, select No, I’m not using them and click Next:

Enter firebase-measurement@system.gserviceaccount.com as the account name and click Create and continue:

firebase-measurement@system.gserviceaccount.com

Then you can add the role to the account. For example, Editor.

add the role to the account

In the third step, add the email addresses of everyone who will use or administer the account and click Done.

After that, you will see your new account in the list.

new account in the list

It’s done! You have successfully enabled and configured the BigQuery API.

3. Link BigQuery to your Google Analytics 4 property

Sign in to your Google Analytics account. The account must have owner access to your BigQuery project and edit access to the Google Analytics 4 property you’re working with.

Go to the Admin tab and select the property you want to link to BigQuery. In the Property column, click BigQuery Linking.

BigQuery Linking

Then click the Link button:

Click Choose a BigQuery project to view the projects you have access to. To create a new BigQuery project, click Learn more.

create a new BigQuery project

Select the project you need and click Confirm.

Select a location. (If your project already has a dataset for the Analytics property, you can’t configure this option.)

Select a location

Click Next and select the data streams you want to export information about:

If you need to include advertising identifiers, check Include advertising identifiers for mobile app streams.

Set Frequency: Daily or Streaming (continuous) export (you can choose both as well).

Set Frequency: Daily or Streaming

Finally, click Submit.

It’s done! You’ll see Google Analytics 4 information in your BigQuery project within 24 hours.

How to avoid Google Analytics 4 limits

Of course, free exporting to Google BigQuery has a big advantage — you don’t need to buy Google Analytics 360 to get data into Google BigQuery.

At the same time, Google BigQuery export also has limitations — it’s impossible to build high-quality reports on the received data. The standard upload comes with a delay and the data is updated even after three days. Additionally, intraday uploads do not contain the most important fields: traffic sources and transactions.

Export delays are caused by fundamental limitations of Google’s architecture — data is collected and stored in geographically distributed data centers, and the data of one user can get into different data centers.

It’s very difficult and hardly possible to guarantee when data will all be collected together, sorted by time, and exported to a specific dataset in Google BigQuery. Especially for users of the free version.

OWOX BI helps you avoid all the limitations of Google Analytics 4. With OWOX BI Streaming, you get each event from your website in your data warehouse in near real-time:

  1. Data is collected in real time, with no unexpected delays
  2. Better data consistency, with no gaps or missed traffic sources
  3. Privacy-friendly data storage, including in 10 EU locations
  4. Customizable business-ready data schemas
  5. Backwards compatibility with Google Analytics and Google Analytics 4 schemas
  6. Additional fields like is_bot for fraud detection
  7. Unlimited hits and data cardinality
  8. SLA and transparent data flow
OWOX BI Streaming
BOOK A DEMO

Read more about this in the article What makes OWOX BI different from Google Analytics 4?

How to upgrade your reporting to Google Analytics 4

If you’re migrating from Google Analytics Universal to Google Analytics 4, you’ll need to upgrade your reporting to a new data schema.

Analysts will have to write and rewrite multi-page SQL queries over and over again to achieve an exact match between Google Analytics Universal and Google Analytics 4 metrics. Learning a new data structure and rewriting all SQL queries is a nightmare for any analyst. But you can avoid those challenges, free your analytics department from this work, and allow analysts to create ad hoc reports in minutes without rewriting SQL queries.

To do this, you can create universal and easy-to-understand flat tables on top of Google Analytics Universal and Google Analytics 4 schemas. The main advantages of this approach are reports that are easier to explain and simplified ad hoc analysis.

Instead of copying and pasting normalization logic across dozens of SQL queries, you can do it once during the data modeling phase. This selection of SQL templates is definitely a go-to solution for those who have regular ad hoc reporting needs.

Here is an example of what a data model for ecommerce businesses might look like:

a data model for ecommerce businesses

OWOX BI analysts have prepared SQL templates for modeling Google Analytics 360 and Google Analytics 4 data. You can check them out by downloading additional materials in this article.

bonus for readers

SQL templates for Google Analytics 360 and Google Analytics 4 schemas

Download now

Summary

Migration to Google Analytics 4 is an inevitable reality that most companies will face very soon. OWOX helps make this transition as smooth as possible.

OWOX will merge data and bring it into the necessary structure for your business model. Thanks to this, you can immediately create new reports or edit old ones as well as change the data source in reports (for example, to Google Analytics 4) without rewriting hundreds of SQL queries.

We recommend all our clients start collecting data in Google Analytics 4 right now in parallel with Universal Analytics. The earlier you install Google Analytics 4, the more historical data you will collect.

The OWOX team will help you develop and implement a metrics system, correctly set up parallel tracking, and integrate Google Analytics 4 with Google BigQuery.

BOOK A DEMO