Attribuzione medium

with prep as (
select
    event_timestamp,
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    (select value.string_value from unnest(event_params) where key = 'source') as source,
    (select value.string_value from unnest(event_params) where key = 'medium') as medium,
    (select value.int_value from unnest(event_params) where key = 'engagement_time_msec') as engagement_time_msec,
    (select value.string_value from unnest(event_params) where key = 'session_engaged') as session_engaged,
    ecommerce.transaction_id
from
    `tuo_data_set_308251284.events_*`
where
    _table_suffix between '20230801' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
),

prep2 as (
select
    user_pseudo_id,
    session_id,
    concat(
      ifnull(first_value(source ignore nulls) over (partition by user_pseudo_id, session_id order by event_timestamp), '(direct)'),
      ' / ',
      ifnull(first_value(medium ignore nulls) over (partition by user_pseudo_id, session_id order by event_timestamp), '(none)')
    ) as session_source_medium,
    engagement_time_msec,
    session_engaged,
    transaction_id
from
    prep
)

select
    session_source_medium,
    count(distinct concat(user_pseudo_id,session_id)) as sessions,
    count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end) as engaged_sessions,
    count(distinct user_pseudo_id) as users,
    count(distinct case when engagement_time_msec > 0 then user_pseudo_id end) as active_users,
    count(distinct transaction_id) as conversions
from
    prep2
group by
    session_source_medium
order by
    sessions desc