Skip to main content

GA4 | dimensions & metrics

Session dimensions & metrics (GA4)

How to query the most important session dimensions and metrics from the GA4 BigQuery export, like (engaged) sessions, engagement rate and time, bounce rate and bounces.

A lot of Google Analytics 4 dimensions and metrics 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.

💡
Session acquisition dimensions can be found here.

Other dimensions and metrics that you might need are a bit harder to access. You will have to (re)calculate them, which can require some serious SQL-skills.

While designing the course Query GA4 Data In Google BigQuery I've learned a lot about best practices to calculate dimensions and metrics. To share this knowledge with you I will provide a combined query for default dimensions and metrics, and a single example query for every non-default dimension or metric.

As always, drop a line in the comments if you have any questions, feedback or suggestions related to this article.

💡
Reclaim a significant amount of your time by subscribing to our premium membership. You'll gain immediate access to a ready-to-go GA4 session acquisition report query.

Calculated sessions metrics

  • sessions
select
    count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) as sessions
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20240701'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
  • engaged sessions
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
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20240701'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
  • engagement rate
select
    -- engagement rate (metric | the percentage of engaged sessions compared to all sessions)
    safe_divide(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),count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')))) as engagement_rate
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20240701'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
  • engagement time
with prep as (
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,
    sum((select value.int_value from unnest(event_params) where key = 'engagement_time_msec'))/1000 as engagement_time_seconds
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20240701'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    session_id)

select
    -- 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(sum(engagement_time_seconds),count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end)) as engagement_time
from
    prep
  • bounces
select
    count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) - 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 bounces
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20240701'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
  • bounce rate
select
    safe_divide(count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) - 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),count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')))) as bounce_rate
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20240701'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
  • event count per session
select
    -- 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,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end),count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')))) as event_count_per_session,
from
    -- change this to your google analytics 4 export location in bigquery
    `ga4bigquery.analytics_250794857.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20240701'
    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
  • average session duration (in seconds)
with prep as (
select
  concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id,
  (max(event_timestamp)-min(event_timestamp))/1000000 as session_length_in_seconds
from
  -- change this to your google analytics 4 export location in bigquery
  `ga4bigquery.analytics_250794857.events_*`
where
  -- define static and/or dynamic start and end date
  _table_suffix between '20240701'
  and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
  session_id)

select
  -- average session duration (metric | the average duration (in seconds) of users' sessions)
  sum(session_length_in_seconds) / count(distinct session_id) as average_session_duration_seconds
from
  prep
  • views per session
with prep as (
select
  concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id,
  countif(event_name = 'page_view') as views
from
  -- change this to your google analytics 4 export location in bigquery
  `ga4bigquery.analytics_250794857.events_*`
where
  -- define static and/or dynamic start and end date
  _table_suffix between '20240701'
  and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
  session_id)

select
  -- views per session (metric| the number of web pages your users viewed per session)
  sum(views) / count(distinct session_id) as views_per_session
from
  prep