Session: dimensions & metrics (GA4)
2 min read

Session: dimensions & metrics (GA4)

The most important sessions dimensions and metrics for GA4 BigQuery export, like (engaged) sessions, engagement rate and time, bounce rate and bounces.
Session: dimensions & metrics (GA4)

A lot of Google Analytics 4 data can be derived straight from the tables without performing calculations on the data. With the help of unnest and parse for dates or timestamps we can do the trick.

Other dimensions & metrics you are used to in your Google Analytics analysing and reporting are a bit harder to access. You’ll have to calculate them, using your own queries.

As I’ve tried to sort out the most used dimensions and metrics already, I hope I can be of help with the following example queries. There are plenty other, probably even better ways to achieve these results, but here is my two cents.

If you only need one dimension or metric, look at the -- comments in the example query for names and definitions and copy the part you need from the SELECT clause. Make sure that you also add any additional conditions (i.e. with, from, where, group by, having and order by) that are necessary to calculate the results correctly.

Calculated sessions metrics

  • sessions
  • engaged sessions
  • engagement rate
  • engagement time
  • bounces
  • bounce rate
  • event count per session

Example query

-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
    '20201101' as start_date,
    format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date),

-- subquery to prepare and calculate engagement data
engagement as (
select
    count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end) as engaged_sessions,
    sum(engagement_time_msec)/1000 as engagement_time_seconds,
    count(distinct case when session_engaged = '0' then concat(user_pseudo_id,session_id) end) as bounces
from (
    select
        user_pseudo_id,
        (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
        max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged,
        max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec
    from
        -- change this to your google analytics 4 export location in bigquery
        `ga4bigquery.analytics_250794857.events_*`,
        date_range
    where
      _table_suffix between date_range.start_date and date_range.end_date
    group by
        user_pseudo_id,
        session_id))

-- main query
select
    -- sessions (metric | the total number of sessions)
    count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end) as sessions,
    -- engaged sessions (metric | the number of sessions that lasted longer than 10 seconds, or had a conversion event, or had 2 or more screen or page views)
    max(engaged_sessions) as engaged_sessions,
    -- engagement rate (metric | the percentage of engaged sessions compared to all sessions)
    safe_divide(max(engaged_sessions),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as engagement_rate,
    -- engagement time (metric | the average length of time in seconds that the app was in the foreground, or the web site had focus in the browser)
    safe_divide(max(engagement_time_seconds),max(engaged_sessions)) as engagement_time,
    -- bounces (metric | the total number of non-engaged sessions)
    max(bounces) as bounces,
    -- bounce rate (metric | bounces divided by total sessions)
    safe_divide(max(bounces),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as bounce_rate,
    -- event count per session (metric | number of times an individual event (i.e. 'page_view') was triggered divided by all sessions)
    safe_divide(count(distinct case when event_name = 'page_view' then concat(user_pseudo_id, cast(event_timestamp as string)) end),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as event_count_per_session
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`,
    date_range,
    engagement
where
    _table_suffix between date_range.start_date and date_range.end_date

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.