You don’t have your own GA4 export data available in BigQuery, or you don’t have an ecommerce type of website? No worries. You can run your queries against Google’s publicly available sample ecommerce data set, which is available through the BigQuery Public Datasets program.
The BigQuery Public Datasets program is a collection of public datasets from Google and other organisations hosted in Google Cloud.
dataset contains a sample of obfuscated BigQuery event export data from the Google Merchandise Store, an online store that sells Google-branded merchandise. The data covers a three-month period from November 2020 to January 2021 and includes information on user behavior, product views, and transactions.
To access this dataset, you will need to have a Google Cloud project with the BigQuery API enabled. You can use the BigQuery Sandbox mode for free, with certain limitations, or enable billing to go beyond the free usage tier.
One of the main benefits of using this dataset is that it allows you to explore and experiment with the data in a safe and secure environment, without having to worry about exposing sensitive information.
However, it is important to note that the data in this dataset is obfuscated, which means that certain fields may contain placeholder values and the internal consistency of the dataset may be somewhat limited.
To use this dataset, you can query it directly or access it through the BigQuery UI in the side bar (project
bigquery-public-data, data set
ga4_obfuscated_sample_ecommerce) in the Cloud Console, and then run queries to extract and analyse the data.
From there, you can explore the schema for the Google Analytics 4 BigQuery event export schema, run more advanced queries, and even visualise the data using Looker Studio or Google Sheets.
The GA4 BigQuery sample ecommerce dataset is a great resource for those who want to explore and experiment with the events data without having to set up a GA4 property or export their own data.
select event_date, count(distinct user_pseudo_id) as total_users from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` group by event_date order by event_date
Sample data set for app event data
select event_date, count(distinct user_pseudo_id) as total_users from `firebase-public-project.analytics_153293282.events_*` group by event_date order by event_date
Now it's your turn!
I hope you've enjoyed this and feel a bit more confident to utilise your own Google Analytics data in BigQuery. Drop a line in the comments if you have any questions, feedback or suggestions related to this article.