How to Connect GA4 to BigQuery: Step-by-Step Guide

icon Get in-depth insights

SQL templates for Google Analytics 4 and GA360 schemas

icon Get in-depth insights

SQL templates for Google Analytics 4 and GA360 schemas

In today's data-driven world, merging digital analytics with robust data processing platforms is essential for deriving meaningful insights and making informed decisions. This guide will walk you through the process of connecting Google Analytics 4 (GA4) with BigQuery, a step pivotal for anyone looking to leverage comprehensive data analytics. 

main image

By establishing this connection, you unlock the potential to delve deeper into your website's user interactions, transcending the basic analytical capabilities of GA4. Let's begin this journey to transform your data analysis and decision-making process.

Advantages of Connecting Google Analytics 4 to BigQuery

Integrating Google Analytics 4 with Google BigQuery transforms how you handle website data. This connection enables you to collect raw, unsampled data from your website in cloud-based storage, where each user and their events will be recorded on analysis. You can calculate any parameters and indicators tailored to your needs using SQL queries (or Customizable SQL templates) on this data.

By opting for BigQuery, you bypass the sampling restrictions often encountered in GA4, thereby ensuring a more comprehensive and accurate analysis of your entire dataset.

For instance, GA4 typically allows a user data retention period of 2 months (extendible to 14 months). However, this duration may prove insufficient for analyzing long-term user interactions or getting insights from year-over-year reports.

BigQuery fills this gap effectively, offering data storage capabilities, allowing for extended data analysis over longer periods. With this enhanced data retention and analysis capacity, the quality and depth of insights drawn from your data are significantly enhanced, paving the way for more informed and strategic decision-making.

By collecting site statistics in Google BigQuery, you can avoid data 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 data in GA 4 is two months. You can increase it to 14 months in the resource settings. 

However, this may still not be enough if you want to analyze events of users who 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, streaming, or collecting your data into BigQuery, you can store it without time limits.

What methods can you use to connect Google Analytics 4 to BigQuery?

Connecting Google Analytics 4 with BigQuery opens up a world of advanced data analysis possibilities. Various methods are available to establish this connection, each catering to different needs and technical proficiencies. Whether you prefer automated systems, integrated features, or manual handling, there's a solution that aligns with your workflow and expertise. 

Let's explore these methods to find the one that best suits your requirements for linking GA4 with BigQuery.

Google Analytics API

Utilizing the Google Analytics API is a direct method to link GA4 with BigQuery. This approach enables automated data retrieval, offering a streamlined process for consistent data synchronization between the two platforms. It's particularly beneficial for those seeking a real-time data connection, ensuring that your BigQuery repository remains up-to-date with the latest GA4 metrics. This method requires some technical know-how but is highly effective for ongoing data management.

[GA4] BigQuery Export

The [GA4] BigQuery Export feature is an integrated solution within GA4, designed for seamless data transfer to BigQuery. This method ensures a direct and efficient pipeline for exporting your analytics data, making it a preferred choice for many. It is ideal for users who prioritize ease of use and minimal configuration, as it automates the data export process entirely within the Google ecosystem. The setup is straightforward and does not require extensive technical skills.

Manual (CSV) Method

This approach involves manually exporting data from Google Analytics 4 into CSV format, offering detailed control over the data export process, ideal for specific data manipulation needs before import. The main advantage of this method is the ability to perform customized data manipulations, ensuring greater control over data before its integration into BigQuery. However, it is time-consuming and requires manual effort, making it less efficient for handling large datasets or for frequent data updates. This method is best suited for scenarios where detailed, manual data handling is required.

OWOX BI Streaming

OWOX BI Streaming offers a 100% privacy-compliant cookieless tracking system to collect GA4-like user behavior data in real-time, without unexpected delays. This is a fully automated approach to data collection. 

And then, when your data is stored in BigQuery - you can collect events into sessions, attribute advertising costs to sessions, build standard or advanced attribution models - all in just a few clicks with OWOX BI. So you can automate your whole marketing reporting.

Hassle-free data analysis and reporting

Easily collect, prepare, and analyze marketing data. Stay on top of your marketing performance

Start Free Trial
Automate your digital marketing reporting

Advantages:

  • OWOX BI allows for no-code integration of marketing data sources to Google BigQuery, enabling marketers to focus on analysis while we update the insights​​.

  • It provides templates for making raw marketing data ready for business analysis, including normalizing, blending cost data, sessionizing GA4 data, and building attribution models.

  • OWOX BI facilitates the merging of data from various sources into data marts making it easy to create reports and visualize them in tools like Looker Studio, Google Sheets, or other BI tools​​.

How to export data from GA4 to BigQuery using Google Analytics API?

Exporting data from Google Analytics 4 to BigQuery using the Google API is a straightforward process, involving several key steps. This guide simplifies the process into manageable actions, ensuring a seamless setup for anyone looking to integrate their GA4 data with BigQuery for advanced analytics.

You should do the following steps to set up export data to BigQuery:

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.

2. Enable the Google Cloud API

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

b. Enter BigQuery API in the search bar:

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

d. Make sure BigQuery API is enabled and click Manage.

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

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

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

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

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

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

k. After that, you will see your 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

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

b. Go to the Admin tab and select the property you want to link to BigQuery. In the Admin page find Product links and click BigQuery Linking.

c. Then click the Link button:

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

e. Select the project you need and click Confirm.

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

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

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

i. Finally, click Submit.

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

In summary, exporting data from GA4 to BigQuery can be a game-changer in your data analysis, offering both depth and flexibility in handling large datasets.

Follow these steps to set up a robust data pipeline for your analytics needs:

1. The BigQuery Setup

a. Create a Project in Google API Console: Sign in and either create a new project or choose an existing one.  

b. Enable Google Cloud API: In your project, go to APIs & Services> Library, search for and enable the BigQuery API.  

c. Add a Service Account: Create a Firebase service account (firebase-measurement@system.gserviceaccount.com) for exporting GA4 data. Assign necessary roles and add users who will manage the account.

 2. GA4 Setup

d. Link BigQuery to GA4 Property: Access your GA4 property with appropriate permissions.

e. BigQuery Linking: In GA4, navigate to Admin > Product Links > BigQuery Linking.

f. Configure Export Settings: Choose your BigQuery project, set the location, select data streams, and decide on export frequency (daily or continuous).

Pros of Linking BigQuery to Google Analytics 4

  1. Provides comprehensive access to raw, unaggregated data, allowing for more in-depth analysis.

  2. Facilitates near real-time data processing, which is crucial for timely decision-making.

  3. Offers the flexibility to create customized reports based on specific business needs.

  4. As your data grows, BigQuery's infrastructure can handle increased volumes without sacrificing performance.

  5. Seamlessly integrates with other Google Cloud services and third-party tools, enhancing your data ecosystem.

Cons / Limitations of Linking BigQuery to Google Analytics 4

  1. Setting up and managing the integration requires technical expertise, which may be challenging for non-technical users.

  2. While BigQuery offers a scalable solution, the costs can increase with the volume of data processed and stored.

  3. Handling sensitive data requires strict adherence to privacy laws and regulations, which can be complex.

  4. There are limits on the amount of data that can be transferred daily, which could be a constraint for large datasets.

  5. This integration heavily relies on the Google infrastructure, which might limit flexibility in terms of using other platforms or tools.

How to use [GA4] BigQuery Export

Leveraging the GA4 BigQuery Export feature unlocks the potential to analyze detailed user and event-level data, which is crucial for in-depth analytics. This tool bridges the gap between Google Analytics 4 and BigQuery, allowing for a more granular view of your data. Understanding how to effectively utilize this feature is key to maximizing the insights you can gain from your analytics.

Types of Bigquery Export

BigQuery Export offers two distinct methods to manage your data flow:

  1. Streaming Export: This option is designed for real-time data analysis. As events occur on your site or app, the data is quickly exported to BigQuery, typically within a few minutes. This immediate availability is invaluable for businesses that need to respond swiftly to user interactions, such as e-commerce sites during high-traffic events or apps requiring immediate user feedback analysis. Streaming export is essential for those who rely on up-to-the-minute data to make informed decisions.

  1. Daily Export: Contrasting with the immediacy of streaming export, the daily export method compiles a complete set of data for each day and transfers it to BigQuery in a 24-hour cycle. This approach suits scenarios where daily summaries are sufficient for analysis, such as long-term trend monitoring or when detailed, day-by-day performance metrics are needed. It's particularly useful for businesses focusing on day-to-day comparisons and broader strategic planning, where real-time data is less critical.

BigQuery Export Schema

The export schema in BigQuery is a structured format that organizes the data exported from GA4.

For each GA4 property connected to BigQuery, a unique dataset is created, named ‘analytics_<property_id>’, where the property ID corresponds to your GA4 Property ID found in the property settings. This structured approach ensures organized data storage, making it easier to navigate and analyze.

Within this dataset, tables are generated daily or continuously, depending on the export type chosen. For daily exports, tables are named as 'events_YYYYMMDD', while for streaming exports, they are 'events_intraday_YYYYMMDD'. 

These tables consist of columns that represent specific event parameters, with some data nested within repeatable RECORDS, like 'items' and 'event_params'. This structure allows for detailed event tracking, capturing every aspect of user interaction in an organized manner.

Understanding and navigating this schema is crucial for effective data analysis, as it dictates how you can query and interpret the vast amount of information collected from GA4.

  • For each linked to BigQuery Google Analytics 4 property, a single dataset named "analytics_<property_id>". Property ID refers to your GA4 Property ID, which you can find in the property settings.

  • Within each dataset, a table named “events_YYYYMMDD” is created each day if the Daily export option is enabled.

  • If the Streaming export option is enabled, a table named “events_intraday_YYYYMMDD” is created. This table is populated continuously as events are recorded throughout the day. This table is deleted at the end of each day once “events_YYYYMMDD” is complete.

  • Tables contain columns and rows. Columns represent event-specific parameters. Note that some parameters are nested within RECORDS, and some RECORDS such as items and event_params are repeatable.

  • Data for a single event may be represented in one or multiple rows, depending on whether it contains repeated RECORDS. A page_view event with multiple event_params, for example, would look similar to the following table. The initial row contains the event name, date, timestamp and other non-repeated data items. The event_params RECORD is repeated for each parameter associated with the event. These repeated RECORDS are populated in subsequent rows directly under the initial event row.

Pros of of [GA4] Bigquery Export

1. In Google Analytics 4, standard reports are not subject to sampling, but sampling can happen 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. 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 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. 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. 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.

Cons / Limitations of [GA4] Bigquery Export

  1. BigQuery export does not allow you to export historical data. Data is exported from the day when you set up export.

  2. Standard GA4 properties have a BigQuery Export limit of 1 million events for Daily (batch) exports. There is no limit on the number of events for Streaming export. If your property consistently exceeds the export limit, the daily BigQuery export will be paused and previous days’ exports will not be reprocessed.

  3. You need to have a valid form of payment on file in Google Cloud in order for the export to proceed. If the export is interrupted due to an invalid payment method, you are not going to be able to re-export data for that time.

How to Export GA4 Data to BigQuery Manually

The easiest way to export data is to get it directly from your Google Analytics 4 property.

Exporting data from Google Analytics 4 (GA4) to Google BigQuery manually is a straightforward process that can be invaluable for detailed data analysis. This method allows you to directly transfer specific reports from GA4 to BigQuery, giving you more control over the data you analyze.

Here’s a quick guide

  1. Start in Google Analytics 4: Open the GA4 property and navigate to the standard report you wish to export, such as 'Traffic Acquisition'. Customize the report to your needs.

  2. Export the Report: Click on 'Share this report' in the top right corner of the GA4 interface, then choose 'Download file' and select the CSV format for download.

  3. Prepare in BigQuery: In your BigQuery project, select the dataset where you want to import the file. Use the dropdown menu to select 'Create table'.

  4. Upload and Configure: Choose 'Upload' as the source, select your CSV file, and specify the file format. Name your table in the 'Destination' section and define the schema based on your downloaded data.

  5. Finalize Settings: If necessary, adjust the partition and cluster settings, then click 'Create table' to complete the process.

  6. Verification: Once the data is downloaded to BigQuery, you'll see the table in your chosen dataset, ready for analysis.

However, a step-by-step guide to help you navigate this process efficiently is given below:

1. Open the GA4 standard report you want to keep. For example, Traffic acquisition. Set any customizations you want. 

2. Click Share this report in the top right corner.

3. Select Download file.

4. Select Download CSV format

5. After exporting the CSV file from the GA4 property, open your BigQuery project.

6. Select the dataset where you want to download the file. 

Open the dropdown menu and select Create table

7. Choose source Upload and select your file and file format

8. Select table name in Destination section

9. Add schema of your downloaded data

10. Select Partition and cluster settings if needed

11. Click Create table. 

12. When data is downloaded to Bigquery, you will see a table in the chosen dataset.

Pros of exporting GA4 Data to BigQuery Manually

  1. Manually exporting data allows for specific report customization before the transfer, ensuring that you export only the data you need.

  2. This method provides a direct way to move data from GA4 to BigQuery without the need for intermediate steps or tools.

  3. By manually handling the export, you gain a deeper understanding of the data structure, which can be beneficial for subsequent analysis.

  4. Manually exporting data from GA4 to BigQuery allows you to selectively choose which reports or data segments you want to transfer. This flexibility is particularly useful when you need to focus on specific aspects of your data for detailed analysis.

  5. Once the export process is complete, the data is immediately available in BigQuery for querying and analysis. This immediacy is beneficial for time-sensitive projects where quick access to updated data is crucial.

Cons of exporting GA4 Data to BigQuery Manually

  1. As your data needs grow, the manual process may become less feasible. Manually exporting large volumes of data or handling multiple reports regularly can be challenging and inefficient.

  2. Manual export does not support real-time data synchronization. This means that any changes or updates in the GA4 data will not be automatically reflected in BigQuery, potentially leading to outdated or incomplete data analysis.

  3. Manual export can be time-intensive, especially when dealing with large datasets or frequent updates.

  4. Manual processes are prone to errors, such as selecting the wrong dataset or misconfiguration of the schema.

  5. Unlike automated processes, this method requires repeated manual effort for each export, which can be inefficient for regular data transfers.

How to Export GA4 Data to BigQuery with OWOX BI

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

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.

Fundamental limitations of Google’s architecture cause export delays — 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.

Exporting GA4 Data to Google BigQuery with OWOX BI

OWOX BI helps you avoid all the limitations of Google Analytics 4. 

With OWOX BI Streaming, data from your website is sent to your data warehouse almost instantly, offering several key advantages:

  1. Data is gathered and made available in real time, minimizing unexpected delays.

  2. The service ensures comprehensive data capture, including traffic sources, without any gaps.

  3. Data is securely stored, with options including 10 EU locations, ensuring compliance with privacy regulations.

  4. Tailor your data structure to meet specific business needs, with compatibility for both Google Analytics Universal and GA4 schemas.

  5. Fields like 'is_bot' are included for advanced needs such as fraud detection.

  6. There are no limits on hits and data cardinality, offering more flexibility.

  7. Ensures a transparent and dependable data flow.

Book a demo

OWOX BI Streaming

Get real-time analytics to optimize your marketing efforts by analyzing your data well with OWOX BI

Book a demo

How to load GA4 / GAU Historical data to BigQuery?

Loading historical data from Google Analytics 4 (GA4) into Google BigQuery is a vital process for businesses looking to analyze past performance and make data-driven decisions. 

There are two primary methods to accomplish this: the Manual method and using OWOX BI Pipelines. Each approach has its unique processes and benefits.

1. Manual Loading

The Manual method involves a hands-on approach where you manually export historical data from GA4 and then import it into Google BigQuery. This process requires a good understanding of both GA4 and BigQuery interfaces. While it gives you direct control over the data transfer, it can be time-consuming and may require technical expertise, especially when dealing with large datasets or complex data structures.

2. Historical Data Export via OWOX BI Pipelines

OWOX BI GAU -> GBQ (and GA4 -> GBQ) offers a more streamlined and automated solution. This method significantly simplifies the process of transferring historical data from GA4 to BigQuery. It automates the data export and import tasks, reducing the manual effort and technical challenges associated with the process. Our pipeline ensures that your historical data is accurately and efficiently loaded into BigQuery, allowing for more immediate and comprehensive data analysis. 

This method is particularly beneficial for businesses that require regular, hassle-free updates to their historical data in BigQuery.

Which Google Analytics properties data can you export to BigQuery?

When it comes to exporting data to Google BigQuery, you have the flexibility to work with different types of Google Analytics properties. Specifically, data can be exported from both types of properties in Google Analytics: Standard and Analytics 360. This export capability is available for properties using both Google Analytics 4 (GA4) and the earlier version, Google Analytics Universal.

  1. Standard or Analytics 360 properties using GA4:
    Google Analytics 4, the latest iteration of Google Analytics, allows for the export of data from both its Standard and 360 versions. GA4 is designed with a more event-driven data model and offers a range of new features compared to its predecessor.

  2. Standard or Analytics 360 properties using GA Universal:
    Even for properties that are still using the older Google Analytics Universal version, data export to BigQuery is possible. This includes both Standard and Analytics 360 properties. Google Analytics Universal offers a different data model and set of features compared to GA4 but still provides valuable insights that can be analyzed in BigQuery.

In both cases, whether using GA4 or Google Analytics Universal, the integration with BigQuery enables a deeper and more flexible analysis of your analytics data, leveraging the powerful data processing capabilities of BigQuery.

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

Once your Google Analytics data is transferred to BigQuery, there are numerous possibilities for utilizing this data:

  1. Customized Reporting: You can create reports using any combination of metrics, tailored to your specific analytical needs.

  2. Comprehensive End-to-End Analytics: By integrating GA4 data with additional data sources such as ad spend from advertising platforms, call tracking information, email campaign data, and CRM data on completed orders, you can develop a complete end-to-end analytical view. This integration allows for a holistic understanding of your marketing efforts and customer interactions.

  3. Development of Tailored Sales Funnels and Attribution Models: You can design intricate sales funnels that align with your unique business model. Additionally, you can create custom attribution models to accurately assess how each advertising channel contributes to your overall sales, enabling more informed marketing and sales strategies.

How much will the Google Analytics BigQuery integration cost?

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.

FAQ

Expand all Close all
  • What are the limitations of manually exporting GA4 data to BigQuery?

    Manual export is less scalable for large data volumes, does not support real-time data sync, can be time-consuming, prone to human error, and lacks automation. This method requires repeated effort for each export and may not be efficient for regular data transfers.
  • How does BigQuery Export Schema structure GA4 data?

    BigQuery Export Schema structures GA4 data by creating a unique dataset for each GA4 property, with daily or continuous tables containing detailed event data. This structured approach enables efficient data analysis and querying.
  • Can historical GA4 data be loaded into BigQuery, and how?

    Yes, historical GA4 data can be loaded into BigQuery using manual methods or automated solutions like OWOX BI Streaming. The manual approach involves exporting data from GA4 and then importing it into BigQuery. OWOX BI Streaming automates this process, offering a more efficient solution for transferring historical data.
  • What are the benefits of using OWOX BI for exporting GA4 data to BigQuery?

    OWOX BI offers real-time data collection, enhanced data consistency, privacy-friendly storage, customizable data schemas, additional data fields, unlimited data capacity, and a reliable service level agreement. It overcomes limitations of the standard GA4 export, such as export delays and missing crucial fields.
  • What is the export limit for GA4 BigQuery?

    The export limit for GA4 to BigQuery is primarily governed by your BigQuery quota, with GA4 offering daily exports without a set limit on data volume.
  • How to backfill data from GA4 to BigQuery?

    To backfill data from GA4 to BigQuery, utilize the Data Import feature in GA4, selecting the desired historical data range for import.
  • How to use google analytics sample dataset for bigquery?

    To use the Google Analytics sample dataset for BigQuery, access it from the BigQuery console under the Google Analytics sample dataset project.
  • How do I connect Google Analytics 4 to BigQuery?

    To connect Google Analytics 4 (GA4) to BigQuery, set up a new data stream in GA4, enable BigQuery linking, and configure the data export settings.

icon Get in-depth insights

SQL templates for Google Analytics 4 and GA360 schemas

icon Get in-depth insights

SQL templates for Google Analytics 4 and GA360 schemas