Write data to BigQuery from Google Analytics 4

Exporting data from Google Analytics 4 to BigQuery opens up a world of possibilities for advanced data analysis and insights. In this article, we'll explore the essentials of BigQuery, including its key features and how it operates. We'll guide you through the steps of how to write data to BigQuery, detailing two scenarios.

BigQuery is a cloud-based data warehouse provided by Google that allows users to store and analyze large datasets quickly and efficiently. It's designed to handle massive amounts of data, making it possible for businesses and researchers to run complex queries without worrying about the limitations of traditional databases.

Key features:

• BigQuery can scale automatically to handle massive datasets. Whether you have a few gigabytes or petabytes of raw data, BigQuery adjusts to your needs without requiring manual intervention. BigQuery Export has a limit of 1 million events for daily exports.
• With BigQuery, you don’t need to worry about managing the underlying infrastructure. Google takes care of provisioning, configuring, and managing resources, allowing you to focus solely on data analysis.
• Supports real-time data analysis. You can quickly query and analyze data as it arrives, making it ideal for applications that require up-to-date information. Moreover, all gathered data can be stored indefinitely, and it also serves as a backup of GA4 data outside the Google Analytics interface, since businesses own the underlying tables and can retain records permanently.
• BigQuery uses standard SQL, making it accessible to anyone familiar with SQL. This means you can write queries in a language you already know and integrate with existing SQL-based tools and workflows.
• Integrates seamlessly with other Google Cloud services such as Google Data Studio, Google Sheets, and Google Analytics, enabling a cohesive data ecosystem.
• Modern versions of BigQuery allow integration of machine learning models without any coding skills requirement.

BigQuery charges for storage and query processing, while the BigQuery sandbox can be used for free exploration with limits.

BigQuery uses a distributed architecture, meaning it splits the data across many machines to process it in parallel, which speeds up the analysis significantly.

To use BigQuery data storage, you load your data into the system, which can come from various sources like Google Cloud Storage, other databases, or even real-time streaming data. Once the data is loaded, you can write SQL queries to explore and analyze it. SQL, or Structured Query Language, is a standard programming language for managing and manipulating databases. BigQuery's ability to handle SQL makes it accessible for users familiar with this language, allowing them to perform tasks such as filtering, aggregating, and joining data.

Yes! With this integration, you can export raw event data from GA4 to BigQuery for more advanced analysis and reporting. It’s also possible to perform custom queries, combine it with other datasets, and use GA4 to gain deeper insights into user behavior.

In this article, we will cover two options in detail: BigQuery export from Google Analytics 4 admin and using the server-side tag “Write to BigQuery”. You will need:

• BigQuery project and dataset (to store your GA4 data);
• Google Analytics 4 account and property;
• Google Tag Manager account with configured web and server containers.

You will also need proper permissions in both GA4 and Google Cloud to enable the integration.

As soon as you have everything prepared, proceed with the next steps. If all this is new to you, but you follow the guidelines below precisely, you should not have any issues with configuring it.

Native Google Analytics data export is the simplest option if you only need the raw data about the events in BigQuery (names, parameters, etc.). Google handles all the processes in this case automatically, and it is free for all users. This makes native export a great choice for analytics or reporting. Remember, though, that data starts flowing to BigQuery within 24 hours after linking, so short delays are possible at the start. As a pleasant bonus, though, BigQuery exports can be configured for daily or streaming frequency.

1. Open your Google Analytics account and click on Admin.
Under Product Links, click BigQuery links.

2. Click Link and choose your BigQuery project. 

3. Configure data streams and events to select which data streams to include with the export and specific events to exclude from the export.

You can exclude events by either clicking Add to select from a list of existing events or by clicking Specify event by name to choose existing events by name or to specify event names that have yet to be collected on the property.

Don’t forget to click the Submit button. 

Check Google's official documentation on how to link a GA4 property to BigQuery if you have more questions or have faced any issues or cases not described here.

If you are looking for more flexibility and want to fully control what data is stored and how it is structured, the Write to BigQuery tag is what you need. Its configuration is a bit more complex, but its ultimate effectiveness is worth it.

1. Create a server GTM container and set up server-side GA4 tracking. 

2. Create or log in to the Google Cloud Platform web console.

3. Select IAM & Admin → Service Accounts → Click Create service account.

4. Add account → Click Create and continue → Select Roles - BigQuery Data Editor role for BigQuery access or the Cloud Datastore User role for Firestore.

If you want to use Google Service Account only for BigQuery, choose only the BigQuery Data Editor role. The same for Firestore. 

When you get to the 3rd step, just click Done. 

5. Click on your new service account →  open the Keys tab → click Add key → Create new key → Select JSON → select Create → JSON will be downloaded to your computer. 

6. Open your Stape account → open sGTM container → open Power-ups tab → Click on Google Service account → Upload JSON file that you’ve downloaded from Google Cloud → Click Save. 

7. Find Write to BigQuery in the Template Gallery and add it:  open templates sections in the server Google Tag Manager container → Click Search Gallery.

8. Find the Write to BigQuery tag. Click Add to workspace → Add. The tag will appear in your container.

10. Provide an ID for the BigQuery table you want all the data to be recorded to (such a table should be created beforehand via Google Cloud Console). When done, you can choose what data you want to be sent there:

• All Event Data - if you select this option, this tag will try to write all event data that the responsible client creates to BigQuery. Only those event-keys that also exist in the schema of the target table will be accepted by BigQuery. All other event-keys will be discarded.

• Custom Data Only - if you select this option, then only the column specified below will be written to your BigQuery table. No default data from the event will be written.

You can also choose to Add Event Timestamp: it will add the millisecond timestamp to the event data written to BigQuery. The BigQuery target column will need to be of the INTEGER data type.

Additionally, you can add the tag name and other metadata to the packages the tag sends via Advanced Settings. By default, however, this section is rarely used, so unless you know exactly what you need it for, you may not open it at all.

For more advanced server-side setups, you can also store events in Google Cloud Storage before querying them in BigQuery. In this approach, server-side events are first saved as files in a Cloud Storage bucket, and BigQuery can then query this data using external tables. This setup can be useful when you need more control over how event data is stored before analysis, but it requires additional Google Cloud configuration and is usually more technical than the standard GA4 BigQuery export or direct server-side BigQuery setup. 

Both methods send data to BigQuery, but they are different in configuration, flexibility, and type of supported data, as well as may be more suitable for different use cases. The table below highlights the key differences between them and is aimed at helping you to decide which approach fits your needs best.

Here we will cover several significant advantages, particularly for businesses and analysts who require more advanced data handling and analysis capabilities:

1. 1.
   Access raw, event-level data collected from the website that is unsampled and available in full, giving you all the information needed for more detailed and customized analysis compared to the aggregated data presented in GA4.
2. 2.
   BigQuery allows you to write and execute SQL queries on your GA4 data. This allows you to perform complex analyses, such as joining multiple datasets, filtering, aggregating, and performing calculations that go beyond the default reports available in GA4.
3. 3.
   BigQuery allows you to combine GA4 data with other data sources, such as CRM systems, advertising platforms, ecommerce data, or custom APIs, giving you a more complete view of user behavior and business performance.
4. 4.
   Create custom metrics and dimensions that are not available in GA4’s default reports to meet unique business requirements.
5. 5.
   Create custom metrics and dimensions that are not available in GA4’s default reports to meet unique business requirements. BigQuery ML creates new possibilities for prediction and segmentation directly with SQL.

Small differences between the Google Analytics interface and BigQuery raw data are normal because of processing and modeling.

Both BigQuery and Google Analytics 4 are essential tools for modern data-driven businesses. BigQuery’s ability to handle large packages of historical data with speed and efficiency makes it invaluable for deep data analysis and complex queries. Unlike older versions, e.g., Universal Analytics, Google Analytics 4 provides comprehensive insights into user behavior, and its seamless integration with BigQuery allows for enhanced data exploration and reporting in other tools like Data Studio, Google Sheets, and Google Docs. 

We hope this article has provided you with the necessary guidance to export your data from Google Analytics 4 to BigQuery. Should you need any assistance with the setup or further optimization, our team of experts is ready to help you unlock the full potential of your analytics.