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 overview

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 data, BigQuery adjusts to your needs without requiring manual intervention.

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

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

How BigQuery works

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

Does Google Analytics 4 support export to BigQuery?

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.

How to write data to BigQuery from Google Analytics 4

In this article, we will cover two options in detail:

1. BigQuery Export from Google Analytics 4 Admin.
2. Using server-side tag “Write to BigQuery”.

What is needed:

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

1. BigQuery Export from Google Analytics 4 Admin

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. 

You can find the official documentation on how to link a Google Analytics 4 property to BigQuery.

2. Using server-side tag “Write to BigQuery”

1. Create and set up a server Google Tag Manager container. 

2. Send data to the server GTM container: set up GA4 tracking.

3. Create or login to the Google Cloud Platform web console.

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

5. Add account → Click Next → 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. 

6. Create private key → Select JSON → select Create → JSON will be downloaded to your computer. 

7. Open your stape.io 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. 

8. Download Write to BigQuery on GitHub or find it in the Template Gallery → Open templates sections in the server Google Tag Manager container → Click New.

9. Click three dots in the top right corner → Click Import → Select Write to BigQuery tag template you’ve recently downloaded from GitHub.

10. You can choose what data to write:

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

- add new fields

- override existing fields by specifying a new value for a key in the event data (e. g. page_location)

- delete existing fields by specifying an field without a value (leave the field blank)

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

How you can use this data

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

1. Access raw, event-level data, which allows for more detailed and customized analysis compared to the aggregated data presented in GA4.
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. With BigQuery’s distributed architecture, queries are processed in parallel, resulting in faster data analysis and reduced query execution time.
4. Create custom metrics and dimensions that are not available in GA4’s default reports to meet unique business requirements.

Conclusion

Both BigQuery and Google Analytics 4 are essential tools for modern data-driven businesses. BigQuery’s ability to handle large datasets with speed and efficiency makes it invaluable for deep data analysis and complex queries. Google Analytics 4 provides comprehensive insights into user behavior, and its seamless integration with BigQuery allows for enhanced data exploration and reporting. 

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.