- GA4 BigQuery For Marketing Analytics
- Posts
- How to Calculate GA4 Engaged Sessions in BigQuery
How to Calculate GA4 Engaged Sessions in BigQuery
Creating GA4 Standard Metrics 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
Advanced notes are available for premium members only.
I am spending a lot of hours researching and coming up with these posts and SQL code. I don’t want you to waste your time researching the same thing.
I highly encourage you to upgrade for a very low price today and save a lot of hours, headaches, and money.
You can also sign up for my BigQuery for Marketing Analytics course at https://academy.optizent.com/courses/bigquery-for-marketers-and-marketing-analysts
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
Reply