Question: Average engagement time per session different in GA4 and BigQuery for screens Here is my code to calculate monthly average engagement time per session for
Average engagement time per session different in GA4 and BigQuery for screens
Here is my code to calculate monthly "average engagement time per session" for screens in BigQuery:
select DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), month) as trunc_month, params1.value.string_value AS screen_name, avg(params2.value.int_value/60000) as avg_engagement_time_min, FROM `table_name` as base, unnest(event_params) as params1 , unnest(event_params) as params2 where event_name = 'user_engagement' and params1.key='firebase_screen' and params2.key='engagement_time_msec' AND FORMAT_TIMESTAMP('%Y%m%d', DATE_TRUNC(TIMESTAMP_MICROS(event_timestamp), month)) BETWEEN @DS_START_DATE AND @DS_END_DATE group by 1,2 order by 1,avg_engagement_time_min desc
--------------
The resulting figures are quite different from GA4 results. For example, for a particular screen in November, GA4 is showing 49 seconds as the "Average engagement time per session" when I select a date range of 1 Nov to 30th Nov, and bigquery is showing 4.73 minutes for November.
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
