Kevin Anchi

To identify unique parameter values in a single session in GA4, you can use the following steps:

• First, you need to select the event parameter that you want to analyze. For example, if you want to see the unique values of the item_name parameter for the view_item event, you can use this query:

-- Example: Select the item_name parameter for the view_item event
SELECT
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_name') AS item_name
FROM
  -- Replace table name
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'view_item'
  -- Replace date range
  AND _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'

Copy

• Next, you need to group the results by user_pseudo_id and session_id, and use the ARRAY_AGG function to create an array of the parameter values for each session. You can also use the ARRAY_LENGTH function to count the number of values in each array. For example:

-- Example: Group by user_pseudo_id and session_id, and create an array of item_name values
WITH ParameterValues AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_name') AS item_name
  FROM
    -- Replace table name
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE
    event_name = 'view_item'
    -- Replace date range
    AND _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
)
SELECT
  user_pseudo_id,
  session_id,
  ARRAY_AGG(item_name) AS item_names,
  ARRAY_LENGTH(ARRAY_AGG(item_name)) AS item_count
FROM ParameterValues
GROUP BY user_pseudo_id, session_id

Copy

• Finally, you need to use the APPROX_TOP_COUNT function to estimate the number of unique values in each array. This function returns an array of structs that contain the value and its estimated count. You can also use the SUM function to calculate the total estimated count of unique values for each session. For example:

-- Example: Estimate the number of unique item_name values for each session
WITH ParameterValues AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_name') AS item_name
  FROM
    -- Replace table name
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE
    event_name = 'view_item'
    -- Replace date range
    AND _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
),
ParameterArrays AS (
  SELECT
    user_pseudo_id,
    session_id,
    ARRAY_AGG(item_name) AS item_names,
    ARRAY_LENGTH(ARRAY_AGG(item_name)) AS item_count
  FROM ParameterValues
  GROUP BY user_pseudo_id, session_id
)
SELECT 
  user_pseudo_id,
  session_id,
  APPROX_TOP_COUNT(item_names, 1000) AS unique_item_names,
  SUM((SELECT count FROM UNNEST(APPROX_TOP_COUNT(item_names, 1000)))) AS unique_item_count 
FROM ParameterArrays 
GROUP BY user_pseudo_id, session_id 

Copy

This query will give you an output like this:Open in browser

user_pseudo_id	session_id	unique_item_names	unique_item_count
000001	1606746000	[{“value”:“Men’s Hooded Sweatshirt”,“count”:1},{“value”:“Men’s Casual Shoes”,“count”:1}]	2
000002	1606752000	[{“value”:“Women’s T-Shirt”,“count”:1},{“value”:“Women’s Sunglasses”,“count”:1},{“value”:“Women’s Leather Wallet”,“count”:1}]	3
…	…	…	…

I hope this helps you identify unique parameter values in a single session in GA4. If you want to learn more about GA4 BigQuery queries, you can check out some resources such as Basic queries for Google Analytics 4 event data export, Session dimensions & metrics (GA4), and Unique count approximation in Google Analytics.