How to Calculate GA4 Engaged Sessions in BigQuery

In this post, I will show you how to recreate GA4 Engaged Session Metrics in BigQuery.

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

Before we look at the SQL, let’s first make sure we understand the definition of Enagged Session in GA4.

Engaged sessions are the sessions that last more than 10 seconds, or have 1 or more conversion events or 2 or more page/screen views. (Note: you can change the 10 seconds to 60 seconds if that makes sense for your business)

If we were to write SQL to calculate the session with the above condition then it would be very complicated.

To make our life easy, GA4 adds a parameter to the event called “session_engaged” When the session is engaged, the value of this paramater is 1 and when the session is not engaged the value is 0 (the default value).

So we will use this paramater and value to calculate the number of engaged sessions.

First I am going to write the SQL and then explain it.

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

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
  `your-project.your-dataset.events_######`

/

Now let me explain the various parts of this query.

First I am going to break this query into two main parts, the main query and the CASE statement within the main query.

The main query counts distinct values that are returned from the embedded CASE Statement and calls them “engaged_session_count”, see below.

ELECT
 COUNT(DISTINCT
 [RESULT of CASE STATEMENT] ) as engaged_sessions_count
from
  `your-project.your-dataset.events_######`

The embedded CASE Statement is the one that finds the engaged sessions, using a subquery, and if it is found then returns a unique value by concatenating “user_pseduo_id” and “session_id” that are associated with that record.

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

There is yet another way to calculate Engaged Sessions, that I have covered in the “Advanced Notes” section below. Both of the ways will lead to the same result. You can decide which one is easier for you to understand and uses less processing resources.

Advanced Notes

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