Last Updated: December 18, 2024

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.

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’.

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 a string 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.

The ‘ga_session_id’ is extracted as a string value and is a unique identifier for a session.

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

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 understand the logic/formula, create a text prompt in ChatGPT based on the information I provided above.

The SQL that you see below is entirely created from a single text prompt and is optimized for speed and efficiency.

To learn more, enrol in my GA4 BigQuery course where I teach you how to write complex SQL queries from text prompts in ChatGPT without understanding a single line of SQL code.

It is one of a kind course, which you won’t find anywhere else. In the age of AI, manually creating SQL queries is insanity.

Related Article: Counting GA4 Sessions in BigQuery? Watch for These Caveats!