[GA4] BigQuery Export: How to Connect Step-by-Step

In today's data-driven world, merging digital analytics with robust data processing platforms is essential for getting meaningful insights and making informed decisions. 

This guide will walk you through the process of connecting Google Analytics 4 (GA4) with BigQuery, a step important for anyone looking to increase their marketing performance with end-to-end analytics implementation.

In this article, updated in April 2024, you'll learn everything about Google Analytics 4 to BigQuery Export.

We’ll talk about what Google BigQuery is, why GA4 BigQuery export becomes a must-have for web analytics, what of the benefits you get when setting it all up. And how to get started and APPLY this inside your business even if you are not a data professional. Let's begin this journey to transform your data analysis and decision-making process.

Google BigQuery is a fully managed, serverless data warehouse, meaning it stores and processes your data in the cloud.

What’s the beauty of a data warehouse? It allows users to load, store, transform, and query data using SQL. If you want to learn more about the benefits of BigQuery as a data warehouse for marketing teams - read this article:

Integrating Google Analytics 4 with Google BigQuery transforms how you handle your web analytics data and reports. When you start doing that - you are not limited by any of the GA4 limitations.

Prefer watching to reading? Dive deeper into the benefits of linking Google Analytics 4 with BigQuery by watching our informative video guide. Ievgen, from OWOX BI, walks you through the essential steps and advantages of setting up your GA4 to BigQuery export. Whether you're a data pro or just starting out, this video simplifies the complexities of data export and showcases how it can transform your analytics approach. Don't miss out on practical tips and insights — click the video below to start enhancing your data strategy today!

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

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

BigQuery fills this gap, offering data storage, and 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.

You can calculate any parameters and indicators tailored to your needs using SQL queries (or Customizable SQL templates) on this data.

Data warehouses allow you to collect your data from various sources. You can port in data from Google Analytics, Google ads, Facebook ads, LinkedIn Ads, your CRM system, offline sales data, as well as other internal systems.

With this, you can enrich your web analytics reporting, and run way more accurate and more informative analysis of your data.

Just ask yourself… How you could analyze sales performance across channels if you’re not combining the conversion data from GA with the cost data of your advertising channels?

Look, when you do start warehousing your analytics - it all becomes available for you. Without spreadsheets or CSV files.

The GA4 reporting interface is limited. When you have your data in BigQuery - you can use Google Sheets, Looker Studio, Tableau, or PowerBI (and most likely, you can use almost any of the BI tools available).

You can build your own reports from scratch, or use any of the templates available on the market.

Yes, that's right. It's free. That doesn't mean you should necessarily use it right now, but it didn't use to be free with universal analytics. Only GA360 clients were able to do this. 

So if you are looking for a good value, there is almost no downside. Let's make sure you start sending data from GA4 to BigQuery as soon as possible because there is no backfill for historical data you already collected in GA4.

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.

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.

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

Advantages:

• OWOX BI is not just a web analytics tool, but rather a marketing analytics solution. That is why you can collect all of the data about digital marketing into Google BigQuery, and prepare them all the way down to reporting - fully automated, so you can focus on analysis while we update your insights.

• No-code templates for making raw marketing data ready for business analysis, including normalizing, blending cost data, grouping events into sessions, and building attribution models.

• OWOX BI is all about building end-to-end analytics by merging data about costs, online and offline conversions, as well as other marketing data into ready-made reports in tools like Looker Studio, Power BI or Google Sheets

Exporting data from Google Analytics 4 to BigQuery is a straightforward process, but it might be a bit complicated if you’re not familiar with complex BigQuery interface.
This step-by-step guide simplifies the process into a list of actions you should take, and it’s applicable for anyone looking to integrate their GA4 data with BigQuery for advanced analytics.

You should do the following steps to Export GA4 data to BigQuery:

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

a. Open the Google Cloud 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. And click Continue.

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.

a. Sign in to your Google Analytics 4 account.
Note: Your account must have owner access to your BigQuery GCP project and edit access to the GA4 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 Links.

c. Then click the Link button:

d. Click Choose a BigQuery project to view the projects you have access to. If you haven’t created a BigQuery project yet, click Learn more.

e. Select the project you need and click Confirm.

f. Select a Data location and click Next. (If your project already has a dataset for the Analytics property, you won’t be able to configure this option.)

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

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

i. Finally, click Submit.

It’s done! 

Note: You’ll be able to see Google Analytics 4 data in your BigQuery project within 24 hours.

When you leverage GA4 BigQuery Export feature unlocks the advanced capabilities to analyze detailed user and event-level data, which is important for modern web analytics.
This absolutely free export bridge allows for a more granular view of your data.
Basically, you take the steering wheel of the granularity of your reports in your hands.
Everything becomes possible.

BigQuery Export offers two 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 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 important for those who rely on up-to-the-minute data to make informed decisions.

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

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 4 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 system in your reports.

By having access to raw data in your data warehouse, 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. This Export provides you with comprehensive access to raw, unaggregated data, from your website so you get the flexibility to create customized reports based on specific business needs.

These web analytics data 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. As your data grows, BigQuery's infrastructure can handle increased volumes without sacrificing performance.

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

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

4. Setting up and managing the following analytics on GA4 Data in BigQuery requires technical expertise, which may be challenging for non-technical users.

5. While GA4 Export is FREE, BigQuery has its own cost of data processing and those costs can increase with the volume of data processed and stored. There are limits on the amount of data that can be transferred daily, which could be a constraint for large datasets.

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.

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.

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.

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.

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.

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

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.

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.

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

• Navigating the GA4 Events Table Schema in BigQuery: Understand the structure of GA4 event data and master date management techniques. This guide offers digital marketers and analysts deep insights to optimize campaigns and manipulate data efficiently.

• Querying GA4 Event Data in BigQuery: Dive into the advanced querying capabilities of BigQuery for GA4 event data. This article demystifies the process, enabling you to extract meaningful insights that drive data-driven decision-making across your digital platforms.

• UNNESTing GA4 Event Parameters for Deeper Insights: Learn the critical skill of UNNESTing event parameters in GA4 using BigQuery. This technique unveils user engagement layers, providing a detailed view to refine your marketing strategies.

• Extracting and Leveraging GA4 User Properties and Metrics in BigQuery: Explore how to leverage GA4 user properties and metrics for comprehensive user behavior analysis. This guide explains the export process, significance, and querying methods to maximize data utility for targeted strategies.

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

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

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