Basic queries for Google Analytics event data export

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 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: 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.

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 and new user count

• To get the total user count, count the number of distinct user_id. 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.
• For new users, you can take the same count approach described above but for the following values of event_name:
  • first_visit
  • first_open

-- Example: Get 'Total User' count and 'New User' count.

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 user_count,
  SUM(is_new_user) AS new_user_count
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 for all purchase 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_web_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 GetParamString and GetParamInt to reduce duplication and make your queries easier to understand and maintain.

Note: The sample dataset schema does not contain the batch_page_id, batch_ordering_id, or batch_event_index fields. Replace the 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, then join the collected_traffic_source.gclid from Google Analytics event data to the gclid field of ads_ClickStats_customer_id 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_customer_id table per customer.