GA4 BigQuery Examples and Sample Data

In this post, I will share several GA4 BigQuery Examples Queries and also provide you with information on getting a sample data set.

GA4 BigQuery Sample Dataset

The best way to get the GA4 data in BigQuery is to send the data from your own site or blog.  If you want to learn how to connect GA4 with BigQuery then check out this post, How to use GA4 with BigQuery?

However, if you don't have a site or don't have enough data then you can also use GA4 public data set that Google provides

You can go to GA4 Public Dataset

or follow these steps

1. You will need to create a bigQuery project
2. Go to BigQuery
3. In the Explorer pane, click +Add.
4. In the Add dialog, search public dataset and then click Public Datasets.
5. Look for ga4_obfuscated_sample_ecommerce dataset

This dataset does not provide all the data but will be a good starting point for you.

Alternatively, we can also provide you access to live data once you become an Optizent Digital Analytics Academy member.

GA4 BigQuery Example Queries

The following queries use the Sample GA4 data (as shown above), to use your own data replaces the table name with your GA4 data table names.

Get all the data for a specific date range

SELECT
*
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'


Get the list of new users for a specific date range

    SELECT
     *
        FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
AND event_name in ('first_visit, 'first_open')

Get the total user count and new user count for a specific date range

WITH
  UserCount AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS user_count,
  SUM(is_new_user) AS new_user_count
FROM UserCount;

Convert the event_timestamp to the local time