In this tutorial, you’ll discover how to immediately start exploring the GA4 BigQuery export using one of the 2 sample datasets Google makes available to everyone. Keep reading to discover more about these datasets and then learn how to connect to and run queries against them.
This is part 2 of 10 in our series, “Get Started with the GA4 BigQuery Export”. Check out the course overview for a full list of all the topics covered.
Introduction
You can start exploring the GA4 BigQuery export immediately, without having to enable the export in your property and wait for data to start flowing through.
Google have very kindly made 2 sample datasets available for anyone to query. The first contains e-commerce data from a web implementation and the second gaming data from an app implementation.
Below you’ll find an overview of these 2 datasets, followed by a step-by-step guide on how to navigate Google Cloud, locate the data and execute your first query to gain insights into the structure and content of the exported tables. Let’s get started!
About the e-commerce sample dataset
Available through the BigQuery Public Datasets program, this dataset contains data collected from the Google Merchandise Store, an online store that sells Google-branded merchandise.
Some of the data has been obfuscated for privacy reasons and therefore contains placeholder values in some fields. As a result, the internal consistency of the data might be somewhat limited.
About the gaming app sample dataset
This dataset contains data collected from Flood-It!, a puzzle game available on both Android and iOS. The app uses Google Analytics 4’s standard gaming app implementation through Firebase.
As with the e-commerce dataset, some of the data has been obfuscated so certain fields will contain placeholder values such as <Other>, NULL and ‘ ‘.
How to access the sample datasets
Note: while the following steps primarily pertain to the e-commerce dataset, we’ll also provide supplementary guidance on accessing the app dataset too.
1. Sign in to your Google Cloud account, or create a new account if you haven’t already done so. Although you will be asked to provide credit/debit card details when signing up, you’ll have 90 days (and $300 of credit) to use the platform for free.
2. After you have completed the registration process you will be taken to the Google Cloud homepage. From here you can use the navigation menu on the left hand side, or the search box at the top of the page, to make your way to BigQuery.
3. Once you are on the BigQuery interface, click +ADD at the top of the screen and then select Star a project by name from the menu that appears. Type “bigquery-public-data” in the popup and then click STAR.
Note: to access the app dataset instead type “firebase-public-project” and then click STAR.
4. You should now see the bigquery-public-data project show up in your workspaces. Click on the arrow next to it to view all the datasets underneath and then scroll down until you find the ga4_obfuscated_sample_ecommerce dataset.
Note: if accessing to the app dataset instead the name is analytics_153293282.
5. You can then open that dataset to view the tables included within. You’ll actually just see 1 table called events_ but we’ll explain a bit more about that in the next tutorial.
6. For now, click on the events_ table name to see some information about the table. On the Schema tab you’ll see an overview of the table schema. Details includes information about the size of the table and Preview will show you a sample of the data.
7. Finally, click QUERY to open up a new query tab, which will open with the name of the current table pre-populated in the editor.
8. This will provide a starting point from which to begin querying the data. Lets modify this into a simple query to return a count of the number of rows in the table. Copy and paste the below SQL into the query editor and then click RUN.
SELECT COUNT(*)
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`9. The query will be run against the selected table, with the result output at the bottom of the screen.
Note: the below query could be used to return a count of rows in one of the tables in the app dataset instead.
SELECT COUNT(*)
FROM `firebase-public-project.analytics_153293282.events_20181003`Wrapping up
That’s it! You’ve just connected to the sample dataset and run your first query! Whether you’re exploring e-commerce data or gaming app information, these datasets serve as excellent starting points for your analytics journey.
In our next tutorial, we’ll dive deeper into understanding the structure of the export, setting the stage for more advanced querying and analysis. Stay tuned as we continue our exploration of the GA4 data in BigQuery.