If you don’t have your own GA4 data in BigQuery, you can query the GA4 sample data available via BigQuery Public datasets.

The sample data is derived from Google Merchandise Store (an ecommerce website owned by Google) and Google ‘Flood-it’ mobile app (a mobile gaming app from Google).

How to access GA4 sample data in BigQuery?

Follow the steps below to access and query the GA4 sample dataset in BigQuery:

Step-1: Create a new Google Cloud platform account. If you already have the account, then log in. 

Step-2: Create a new BigQuery project. If you already have a BigQuery project, then navigate to it.

Step-3: Click on the ga4_obfuscated_sample_ecommerce dataset to access the BigQuery sample dataset for GA4 ecommerce web implementation.

You should now see a screen like the one below, which shows the schema of the data table named: bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131

The data table ‘events_(92)’ contains 92 days of obfuscated data from November 2020 to January 2021. 

Obfuscated data means certain fields contain placeholder values, including <Other>, NULL, and ”, and the internal consistency of the dataset might be somewhat limited.

Google recommends not to compare the data in the data table with the Google Analytics Demo Account for the Google Merchandise store as the data is different.

Get this 70 page detailed checklist containing screenshots, step-by-step instructions and links to articles

Do you want to set up Google Analytics 4 (GA4) fast and correctly?

Yes I want the ebook

Step-4: Click on the ‘QUERY’ drop-down menu and then click on ‘In new tab’:

You should now see a screen which shows SQL query with a syntax error:

You see the syntax error: ‘SELECT list must not be empty at [1:9]’ because the SQL query is not correct. 

Step-5: Type the * character just after the SELECT statement to instruct BigQuery to retrieve all the columns of the data table:

The green checkmark indicates that our SQL query is correct.

Step-6: Click on the ‘MORE’ drop-down menu and then click on ‘Format query’ to make the SQL query more readable:

Your formatted query should now look like the one below:

Step-7: Click on the ‘Run’ button to run the query:

You should now see the query results at the bottom of the data table:

That’s how you can query GA4 sample data in BigQuery.

Step-8: Navigate to the document ‘[GA4] BigQuery Export schema’ to help you read the query results. Through this document, you can get a brief description of each field.

For example, through this document, you can understand that the field named ‘event_date’ denotes the date when the event was logged (i.e. recorded):

Step-9: Run some of the advanced sample queries provided by Google. For example, let’s run a SQL query which shows what other products were purchased by customers who purchased a specific product.

Step-10: Navigate to this page: https://developers.google.com/analytics/bigquery/advanced-queries#simplified and then click on the ‘Copy Code Sample’ button:

Step-11: Paste the copied sample code in the SQL editor and then click on the ‘Run’ button:

You should now see the query results at the bottom of the data table, which shows what other products were purchased by customers who purchased a specific product.

Step-12: Scroll all the way up and then click on the star button next to the project named ‘BigQuery-public-data’:

In BigQuery, the star button is used to mark a project, dataset, or table as a favourite.

It provides a convenient way to quickly access frequently used resources, even when you have a large number of projects, datasets, and data tables in your account.

Get this 70 page detailed checklist containing screenshots, step-by-step instructions and links to articles

Do you want to set up Google Analytics 4 (GA4) fast and correctly?

Yes I want the ebook

How to access and query GA4 sample mobile app data in BigQuery?

Follow the steps below:

Step-1: Create a new Google Cloud platform account. If you already have the account, then log in. 

Step-2: Create a new BigQuery project. If you already have a BigQuery project, then navigate to it.

Step-3: Click on the flood it dataset to access the BigQuery sample dataset for GA4 gaming app implementation.

You should now see a screen like the one below, which shows the schema of the data table named: firebase-public-project.analytics_153293282.events_20181003

The data table ‘events_(114)’ contains 114 days of obfuscated data from the year 2018.

Step-4: Click on the ‘QUERY’ drop-down menu and then click on ‘In new tab’:

You should now see a screen which shows SQL query with a syntax error:

You see the syntax error: ‘SELECT list must not be empty at [1:9]’ because the SQL query is not correct. 

Step-5: Type the * character just after the SELECT statement to instruct BigQuery to retrieve all the columns of the data table:

The green checkmark indicates that our SQL query is correct.

Step-6: Click on the ‘MORE’ drop-down menu and then click on ‘Format query’ to make the SQL query more readable:

Your formatted query should now look like the one below:

Step-7: Click on the ‘Run’ button to run the query:

You should now see the query results at the bottom of the data table:

That’s how you can query GA4 sample mobile app data in BigQuery.

Step-8: Click on the star button next to the project named ‘firebase-public-project’ to mark the project as favorite and quickly access it: