The sample queries in this page apply to the BigQuery event export data for
Google Analytics.

### Query your dataset instead of the sample dataset

Unless otherwise noted, all queries listed here use sample datasets and should
produce valid results. To use your own Google Analytics property's BigQuery
event export data, look for the comment `-- Replace table` in each query and
replace the sample table. To copy the table name from your dataset:

1. Go to the [BigQuery UI](https://console.cloud.google.com/bigquery) and select the project that contains your dataset.
2. Locate the table in the **Explorer**.
3. Click the three vertical dots to the right of the table, then click **Copy ID**.
4. Paste the table name in place of the sample table in the query.
5. Replace the date portion of the table with `*`.

For example, if **Copy ID** copied the BigQuery table name
`my-first-gcp-project:analytics_28239234.events_20240718`, then replace:

      -- Replace table
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

with:

      -- Replace table
      `my-first-gcp-project.analytics_28239234.events_*`

> [!NOTE]
> **Note:** When adding your table in the sample queries, replace '`:`' with '`.`'.

### Query a specific date range

To query a specific date range from a BigQuery event export dataset, use the
_TABLE_SUFFIX pseudo column in the WHERE clause of your query. For more info,
view [Filtering selected tables using _TABLE_SUFFIX](https://cloud.google.com/bigquery/docs/querying-wildcard-tables#filtering_selected_tables_using_table_suffix).

For example, the following query counts unique events by
date and by event name for a specifc period of days and selected events:

    -- Example: Query a specific date range for selected events.
    --
    -- Counts unique events by date and by event name for a specifc period of days and
    -- selected events(page_view, session_start, and purchase).

    SELECT
      event_date,
      event_name,
      COUNT(*) AS event_count
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name IN ('page_view', 'session_start', 'purchase')
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1, 2;

### User count, new users, and returning users

- To get the total **active** user count, count the number of distinct `user_id` values. However, if your Google Analytics client does not send back a `user_id` with each hit or if you are unsure, count the number of distinct `user_pseudo_id` values.
- For new users, count the number of distinct users whose `event_name` is:
  - [`first_visit`](https://support.google.com/analytics/answer/9234069#first_visit)
  - [`first_open`](https://support.google.com/analytics/answer/9234069#first-open)
- For returning users, count the number of users who had activity during the period, but did not initiate their first session or launch the app for the first time during the period (active users minus new users).

    -- Example: Get 'Total User' (Active User), 'New User', and 'Returning User' counts.

    WITH
      UserInfo AS (
        SELECT
          user_pseudo_id,
          MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
        -- Replace table name.
        FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
        -- Replace date range.
        WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
        GROUP BY 1
      )
    SELECT
      COUNT(*) AS active_users,
      SUM(is_new_user) AS new_users,
      COUNT(*) - SUM(is_new_user) AS returning_users
    FROM UserInfo;

### Average number of transactions per purchaser

The following query shows the average number of transactions per purchaser.

    -- Example: Average number of transactions per purchaser.

    SELECT
      COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name IN ('in_app_purchase', 'purchase')
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

### Values for a specific event name

The following query shows the [`event_timestamp`](https://support.google.com/analytics/answer/7029846) for all
[`purchase`](https://support.google.com/analytics/answer/9268036) events and the associated event parameter
values:

    -- Example: Query values for a specific event name.
    --
    -- Queries the individual timestamps and values for all 'purchase' events.

    SELECT
      event_timestamp,
      (
        SELECT COALESCE(value.int_value, value.float_value, value.double_value)
        FROM UNNEST(event_params)
        WHERE key = 'value'
      ) AS event_value
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name = 'purchase'
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

The previous query can be modified to show the total of event parameter
values instead of a list:

    -- Example: Query total value for a specific event name.
    --
    -- Queries the total event value for all 'purchase' events.

    SELECT
      SUM(
        (
          SELECT COALESCE(value.int_value, value.float_value, value.double_value)
          FROM UNNEST(event_params)
          WHERE key = 'value'
        ))
        AS event_value
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name = 'purchase'
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

### Top 10 items added to cart

The following query shows the top 10 item added to cart by the most number of users.

    -- Example: Top 10 items added to cart by most users.

    SELECT
      item_id,
      item_name,
      COUNT(DISTINCT user_pseudo_id) AS user_count
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
    WHERE
      -- Replace date range.
      _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
      AND event_name IN ('add_to_cart')
    GROUP BY
      1, 2
    ORDER BY
      user_count DESC
    LIMIT 10;

### Average number of pageviews by purchaser type (purchasers vs non-purchasers)

The following query shows the average number of pageviews purchaser type
(purchasers vs non-purchasers) of users:

    -- Example: Average number of pageviews by purchaser type.

    WITH
      UserInfo AS (
        SELECT
          user_pseudo_id,
          COUNTIF(event_name = 'page_view') AS page_view_count,
          COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
        -- Replace table name.
        FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
        -- Replace date range.
        WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
        GROUP BY 1
      )
    SELECT
      (purchase_event_count > 0) AS purchaser,
      COUNT(*) AS user_count,
      SUM(page_view_count) AS total_page_views,
      SUM(page_view_count) / COUNT(*) AS avg_page_views,
    FROM UserInfo
    GROUP BY 1;

### Sequence of pageviews

This query shows the sequence of pageviews made by each user. The query orders
the results using the following fields so that events are listed in the order
they occurred for the user, even if the events were sent in the same batch:

- `user_pseudo_id`
- `user_id`
- `batch_page_id`
- `batch_ordering_id`
- `batch_event_index`

Although the sample limits the results to only `page_view` events, you can use
the same `ORDER BY` clause to correctly order all events by removing the
`WHERE` clause condition for `event_name`.

The query also shows how to use [user-defined
functions](https://cloud.google.com/bigquery/docs/user-defined-functions)
`GetParamString` and `GetParamInt` to reduce duplication and make your queries
easier to understand and maintain.

> [!NOTE]
> **Note:** The sample dataset schema does not contain the `batch_page_id`, `batch_ordering_id`, or `batch_event_index` fields. [Replace the table](https://developers.google.com/analytics/bigquery/basic-queries#replace_table) in the `FROM` clause of the query with your table.

    -- Example: Sequence of pageviews.

    /** Temporary function to retrieve the string_value of an event parameter by event name. */
    CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
    AS ((SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name));

    /** Temporary function to retrieve the int_value of an event parameter by event name. */
    CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
    AS ((SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name));

    SELECT
      user_pseudo_id,
      user_id,
      batch_page_id,
      batch_ordering_id,
      batch_event_index,
      event_name,
      GetParamInt(event_params, 'ga_session_id') as ga_session_id,
      GetParamString(event_params, 'page_location') as page_location,
      GetParamString(event_params, 'page_title') as page_title,
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      event_name = 'page_view'
      -- Replace date range.
      AND _TABLE_SUFFIX BETWEEN '20240718' AND '20240731'
    ORDER BY
      user_pseudo_id,
      user_id,
      batch_page_id,
      batch_ordering_id,
      batch_event_index;

### Event parameter list

The following query lists all event parameters appearing in your dataset:

    -- Example: List all available event parameters and count their occurrences.

    SELECT
      EP.key AS event_param_key,
      COUNT(*) AS occurrences
    FROM
      -- Replace table name.
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
    WHERE
      -- Replace date range.
      _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY
      event_param_key
    ORDER BY
      event_param_key ASC;

### Joining with Google Ads

To retrieve additional Google Ads data for your Google Analytics events, set up the
[BigQuery Data Transfer Service for Google
Ads](https://cloud.google.com/bigquery/docs/google-ads-transfer), then
join the `collected_traffic_source.gclid` from Google Analytics event data to the `gclid`
field of `ads_ClickStats_`<var translate="no">customer_id</var> from the Google Ads transfer.

Keep in mind that the Google Analytics event data export creates a table for each day, while
the Google Ads transfer populates a single
`ads_ClickStats_`<var translate="no">customer_id</var> table per customer.