Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Engaged Sessions from GA4 in Bigquery
WITH prep AS(
SELECT
PARSE_DATE('%Y%m%d', event_date) AS event_date,
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
CONCAT(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 = 'session_engaged') AS engaged_sessions,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,
FROM `PROJECT_NAME.analytics_PROPERTY_ID.events_*`
WHERE TRUE
AND _TABLE_SUFFIX BETWEEN '20220601' AND FORMAT_DATE('%Y%m%d', DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY))
)
SELECT
event_date,
COUNT(DISTINCT session_id) AS num_of_sessions,
COUNT(DISTINCT
CASE
WHEN engaged_sessions = '1' THEN CONCAT(user_pseudo_id, ga_session_id) ELSE NULL
END) AS num_of_engaged_sessions,
AVG(engagement_time_msec) AS engagement_time_msec
FROM prep
GROUP BY event_date
ORDER BY event_date DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment