Here is how you can calculate ‘sessions’ and ‘engaged sessions’ in GA4 BigQuery.

First thing first. 

Both ‘sessions‘ and ‘engaged sessions’ metrics need to be manually calculated. 

They are not available as default fields in the GA4 BigQuery export schema.

You must know the formula (underlying logic) to calculate a particular GA4 dimension/metric in BigQuery. 

If you do not understand the underlying logic, you will have a hard time using a particular GA4 dimension/metric in BigQuery, even if I give you the exact SQL code to copy and paste. 

There can be ‘N’ use cases, and it is impossible to provide pre-built SQL code for every possible use case.

Note: To find formulas for a particular dimension or metric, refer to the ‘GA4 to BigQuery Mapping Tutorial‘ article.

In addition to knowing the formula, you must be familiar with the GA4 BigQuery export schema so that you can easily refer to a default dimension/metric and identify the data type they use.

If you are new to the schema, refer to the ‘GA4 BigQuery Schema Tutorial‘.

Once you understand the formula and export schema, it will be easier and possible for you to modify the SQL code to meet your unique requirements.

Calculating Sessions in GA4 BigQuery

To calculate the total number of sessions, use ‘Common Table Expressions (CTEs)’ and count each unique combination of ‘user_pseudo_id’ and ‘ga_session_id’.

A Common Table Expression (CTE) is a temporary result set that you define within an SQL statement. It acts like a temporary table that exists only for the duration of the query. 

CTEs allow you to break down complex queries into manageable parts, making the SQL easier to read and understand. 

This is especially helpful when working with intricate logic or multiple steps in a calculation.

With CTEs, you can define a calculation once and reference it multiple times within the same query. 

This avoids duplication of code and reduces the risk of errors.

The ‘user_pseudo_id’ is an event parameter which is extracted as an integer value. It is a unique identifier for a user.

The ‘ga_session_id’ is an array of structures within the ‘event_params’ array of structures. 

It is extracted as a string value and is a unique identifier for a session. 

Once you have created the required SQL code using the logic mentioned above, you should see an output like the one below:

If you struggle with creating the correct SQL code, message me on LinkedIn, and I will share it with you.

Calculating Engaged Sessions in GA4 BigQuery

To calculate ‘engaged sessions’, use Common Table Expression and count distinct sessions for which the value of the ‘session_engaged’ event parameter is 1. 

Needless to say, you need to calculate the ‘sessions’ metric first before you can calculate the ‘Engaged Sessions’ metric.

The ‘session_engaged’ is an array of structures within the ‘event_params’ array of structures. 

The ‘session_engaged’ parameter is extracted as an integer value.

Once you have created the required SQL code using the logic mentioned above, you should see an output like the one below:

If you struggle with creating the correct SQL code, message me on LinkedIn, and I will share it with you.