BigQuery Abfrage für Google Analytics 4 (GA4): Sitzungen: Dimensionen und Metriken

-- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
    '20211222' 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
        `privat-327611.analytics_266663932.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
    `privat-327611.analytics_266663932.events_*`,
    date_range,
    engagement
where
    _table_suffix between date_range.start_date and date_range.end_date