Erstellung des Nutzer-Akquisitions-Berichtes in Big Query mit Google Analytics 4 Daten

-- subquery to prepare the data
with prep as (
select
    user_pseudo_id,
    ifnull(traffic_source.medium,'(not set)') as medium,
    ifnull(traffic_source.source,'(not set)') as source,
    ifnull(traffic_source.name,'(not set)') as name,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    max((select value.int_value from unnest(event_params) where event_name = 'session_start' and key = 'ga_session_number')) as session_number,
    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,
    -- change event_name to the event(s) you want to count
    countif(event_name = 'click') as event_count,
    -- change event_name to the conversion event(s) you want to count
    countif(event_name = 'purchase') as conversions,
    sum(ecommerce.purchase_revenue) as total_revenue
from
    -- change this to your google analytics 4 export location in bigquery
    `privat-327611.analytics_266663932.events_*`
where 
    -- change the date range by using static and/or dynamic dates
   _table_suffix between '20211223' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by
    user_pseudo_id,
    medium,
    source,
    name,
    session_id)

-- main query
select
    medium as user_medium,
    -- source as user_source,
    -- concat(source,' / ',medium) as user_source_medium,
    -- name as user_campaign,
    count(distinct case when session_number = 1 then user_pseudo_id else null end) as new_users,
    count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end) as engaged_sessions,
    safe_divide(count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end),count(distinct concat(user_pseudo_id,session_id))) as engagement_rate,
    safe_divide(count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end),count(distinct user_pseudo_id)) as engaged_sessions_per_user,
    safe_divide(sum(engagement_time_msec/1000),count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end)) as average_engagement_time_seconds,
    sum(event_count) as event_count,
    sum(conversions) as conversions,
    ifnull(sum(total_revenue),0) as total_revenue
from
    prep
group by
    user_medium
    --,user_source
    --,user_source_medium
    --,user_campaign
order by
    new_users desc