Below, you’ll find queries designed to help you recreate vital Google Analytics 4 (GA4) session metrics right within your BigQuery environment. These metrics provide valuable insights into user engagement and behavior on your digital platforms.
Jump to:
- Sessions
- Engaged sessions
- Engagement rate
- Sessions per user
- Engaged sessions per user
- Bounce rate – Coming Soon
- Average session duration – Coming Soon
- Session conversion rate – Coming Soon
Important Note: In all the queries provided below, make sure to replace the placeholders within the WHERE clause with the specific values for the table you are working with. For example, if you want to extract data for January 31st 2021 from the e-commerce sample dataset, use the following values:
- <project> should be replaced with bigquery-public-data
- <dataset> should be replaced with ga4_obfuscated_sample_ecommerce
- <table> should be replaced with events_20210131
Ensure that you substitute these placeholders with your actual project, dataset, and table names when executing the queries.
Calculate sessions
To identify unique sessions we need to extract the ga_session_id from event_params and then concatenate with the user_pseudo_id. Then do a count of distinct values to calculate Sessions.
SELECT
COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS sessions
FROM `<project>.<dataset>.<table>`Calculate engaged sessions
To calculate Engaged sessions we need to concatenate the ga_session_id (extracted from event_params) with the user_pseudo_id, then count distinct values where session_engaged = 1 (also extracted from event_params).
SELECT
COUNT(DISTINCT CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1' THEN CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) END) AS engaged_sessions
FROM `<project>.<dataset>.<table>`Calculate engagement rate
Engagement rate is calculated as Engaged sessions (see above) divided by Sessions (see above), rounded to 2 decimal places.
SELECT
ROUND(COUNT(DISTINCT CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1' THEN CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) END) / COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))), 2) AS engagement_rate
FROM `<project>.<dataset>.<table>`Calculate sessions per user
Sessions per user is calculated as Sessions (see above) divided by the count of distinct user_pseudo_id, rounded to 2 decimal places.
SELECT
ROUND(COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) / COUNT(DISTINCT user_pseudo_id), 2) AS sessions_per_user
FROM `<project>.<dataset>.<table>`Calculate engaged sessions per user
Engaged sessions per user is calculated as Engaged sessions (see above) divided by the count of distinct user_pseudo_id, rounded to 2 decimal places.
SELECT
ROUND(COUNT(DISTINCT CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1' THEN CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) END) / COUNT(DISTINCT user_pseudo_id), 2) AS engaged_sessions_per_user
FROM `<project>.<dataset>.<table>`