How to Calculate GA4 Engagement Time in BigQuery

In this post, I will show you how to calculate engagement time in BigQuery.

As always, we will use the concepts that we covered in the past, these concepts include unnest and subquery. If you are unfamiliar with them, please check out the past posts.

In GA4 Enagagement Time is stored as a parameter with various events. For us to calculate the total engagement time, we will follow the following steps:

1. Unnest the event parameters and join them to the events table

2. narrow down the data to only pull the records that contain the engagement time metrics

3. Sum the values to calculate the total engagement time on your site.

Continue reading after the ChatGPT Cheat Sheet

Here is the SQL to get this data

There are different ways to achieve the results. I will explain them here and then show them another way in the “Advanced” section.

SELECT  b.key, sum(value.int_value) 
FROM  `your-project.your-dataset.events_######`a, UNNEST(event_params) b
WHERE b.key = 'engagement_time_msec'
GROUP BY b.key

The above will give you the total engagement time on your site in milliseconds.

The following modification will give you the time in seconds.

SELECT  b.key, sum(value.int_value)/(1000*60) 
FROM  `your-project.your-dataset.events_######`a, UNNEST(event_params) b
WHERE b.key = 'engagement_time_msec'
GROUP BY b.key

Now you need to calculate total sessions and total active users to get the following GA4 metrics

1. Average Engagement Time

2. Average engagement time per session

If you would like to know how to calculate that in one query then check out the advanced section.

Advanced Section

Are you enjoying this newsletter? Would you be willing to write a testimonial?
If yes then please hit reply with your comments.

Thank you,
Anil Batra, Optizent.com

P.S. Need help with GA4 BigQuery? Email me at [email protected]