题解 | 内容社区用户活跃度、转化与广告归因分析

with a1 as (
    select 
    campaign_id,
    event_type,
    event_time,
    lead(event_time) over(order by event_time) as next_event_time,
    lead(event_type) over(order by event_time) as next_event_type
    from Events
    where event_time between '2024-02-01 00:00:00' and '2024-02-29 23:59:59'
),

a2 as (
    select 
    campaign_id,
    round(avg(case when event_type='click' and next_event_type='signup' then timestampdiff(minute,event_time,next_event_time) end),2) as avg_time
    from a1
    group by campaign_id
),

a3 as (
    select 
    campaign_id,
    channel,
    rank() over(partition by campaign_id order by count(*) desc , CONVERT(channel, CHAR)) as rk # CONVERT(channel, CHAR) 将ENUM转为字符串，字母排序：app(a) < mini (m) < web (w)
    from Events
    where event_time between '2024-02-01 00:00:00' and '2024-02-29 23:59:59'
    group by campaign_id,channel
)

select 
campaign_id,
campaign_name,
sum(case when event_type='signup' and event_time between '2023-02-01 00:00:00' and '2023-02-28 23:59:59' then 1 else 0 end ) as conv_2023_02,
sum(case when event_type='signup' and event_time between '2024-02-01 00:00:00' and '2024-02-29 23:59:59' then 1 else 0 end ) as conv_2024_02,
sum(case when event_type='signup' and event_time between '2024-01-01 00:00:00' and '2024-01-31 23:59:59' then 1 else 0 end ) as conv_2024_01,
sum(case when event_type='signup' and event_time between '2024-02-01 00:00:00' and '2024-02-29 23:59:59' then 1 else 0 end ) -sum(case when event_type='signup' and event_time between '2023-02-01 00:00:00' and '2023-02-28 23:59:59' then 1 else 0 end ) as yoy_delta,
sum(case when event_type='signup' and event_time between '2024-02-01 00:00:00' and '2024-02-29 23:59:59' then 1 else 0 end )-sum(case when event_type='signup' and event_time between '2024-01-01 00:00:00' and '2024-01-31 23:59:59' then 1 else 0 end ) as mom_delta,
ifnull(round(100*sum(case when year(event_time)=2023 and event_type='signup' and user_id = 1 then 1 else 0 end ) / sum(case when year(event_time)=2023 and event_type='signup' then 1 else 0 end) ,2),0.00) as north_pct_2023,
ifnull(round(100*sum(case when year(event_time)=2023 and event_type='signup' and user_id = 3 then 1 else 0 end ) / sum(case when year(event_time)=2023 and event_type='signup' then 1 else 0 end) ,2),0.00) as south_pct_2023,
ifnull(round(100*sum(case when year(event_time)=2023 and event_type='signup' and user_id = 2 then 1 else 0 end ) / sum(case when year(event_time)=2023 and event_type='signup' then 1 else 0 end) ,2),0.00) as east_pct_2023,
max(avg_time) as avg_click_to_signup_min_2024_02,
max(a3.channel) as top_channel_2024_02
from Events
join Users using(user_id)
join Campaigns using(campaign_id)
join a2 using(campaign_id)
join a3 using(campaign_id)
where rk=1
group by campaign_id,campaign_name
order by campaign_id,campaign_name