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.
Leave a ReplyCancel reply