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.

Google BigQuery is a multi-cloud data warehouse with a built-in query service and a high level of security and scalability. According to Gartner, “by 2022, 75% of all databases will be deployed or migrated to a cloud platform, with only 5% ever considered for repatriation to on-premises.” And thanks to the fact that BigQuery is part of the Google ecosystem and the Google Cloud Platform in particular, it natively integrates with other Google products and helps you develop your business at a competitive speed.

There are multiple low-level aspects that make BigQuery almost irreplaceable for marketers. Let’s take a closer look at some of its most valuable benefits:

• Ability to upload large volumes of information. With BigQuery, you can perform real-time analysis of any type of data and quickly process it with SQL.
• High level of security. You get full control over your project and can take advantage of two-factor authentication.
• Affordable. Pay only for collected and processed data.
• Native integration with Google products. Easily connect with Google Analytics and other products.
• Scalable. Scale quickly and seamlessly to easily adjust to the rapidly changing world.
• BigQuery ML. Build machine learning prediction models on both structured and semi-structured data using SQL.
• BigQuery GIS. Thanks to the BigQuery Geographic Information System (GIS), you can analyze geospatial information and determine which users should get a mailer for a specific store location, for instance.

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.

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.

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.

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

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.

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 funnel steps. To make this calculation, aggregated data is not suitable — we need data about each user action, linked by User ID.

The better the channel moves the buyer through the most difficult steps, the more value it receives.

With the Funnel-Based Attribution model, you can identify the most effective campaigns and focus on developing them to fulfill your marketing plan. For example, you can find managed campaigns you can develop to increase the elasticity of dependent channels with limited capacity (organic, brand context, CPA, retargeting) and thereby increase your overall sales.

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.

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.

To be fair, channel grouping can be configured in a simpler way:

1. Connect Google Analytics data to Data Studio with a built-in connector.
2. Set up grouping conditions in Data Studio.

It won’t be as reliable as grouping channels in Google BigQuery, but if you need a quick solution, you can use this implementation.

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.

Note: Importing data to BigQuery from Google Analytics 4 is free, but there are limits beyond which you need to pay. You can find details here.

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.

Let’s examine the format and schema of the GA 4 property data that’s exported to BigQuery. One important thing to keep in mind when working with GA 4 is that its structure differs from the structure of Universal Analytics, which is familiar to marketers all over the world.

This is how Google Analytics 4 schema differs from Universal Analytics schema:

• Datasets. GA sample datasets are named analytics_, where property ID is your Analytics Property ID.
• Tables. A separate Google Analytics table is imported into the dataset for each day. The format of such tables is events_YYYYMMDD, unlike in Universal Analytics where it’s ga_sessions_YYYYMMDD.
• Rows. Each row corresponds to an uploaded event, in contrast to Universal Analytics where each row corresponds to a Google Analytics 360 session.
• Columns. The field names largely differ between GA 4 and Universal Analytics. You can compare them by following these links:
• Google Analytics 4 BigQuery Export Schema
• Universal Analytics BigQuery Export Schema

Now let’s get to the main purpose of this article: providing step-by-step instructions on how to export your data from Google Analytics 4 to 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.

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

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

Enter BigQuery API in the search bar:

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

Make sure BigQuery API is enabled and click Manage.

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

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:

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

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.

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

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.

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.

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

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

Finally, click Submit.

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

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

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

Currently, there is only a manual way to import ad cost data into Google Analytics 4. You can automate this process and save valuable time, using the solution from OWOX BI.

Important! If you plan to import advertising costs to Google Analytics 4, then you need to add the required parameter utm_id (campaign identifier) to the links of your advertisement campaigns.

You can easily export your GA 4 information to BigQuery. If the new structure works well for you, you can take advantage of this progressive service and add it to your marketing analytics toolbox.

Of course, the new Google Analytics 4 structure has its advantages. But if you’re not ready for such changes and still want to collect your marketing data in BigQuery, you can use OWOX BI Pipeline.

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

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.