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

with a as(
    select 
        campaign_id,
        campaign_name,
        sum(case when date_format(event_time,'%Y-%m')='2023-02' then 1 else 0 end) as conv_2023_02,
        sum(case when date_format(event_time,'%Y-%m')='2024-02' then 1 else 0 end) as conv_2024_02,
        sum(case when date_format(event_time,'%Y-%m')='2024-01' then 1 else 0 end) as conv_2024_01,
        sum(case when year(event_time)='2023' then 1 else 0 end) as total_2023_signup,
        sum(case when year(event_time)='2023' and region ='华北' then 1 else 0 end) as north_2023_signup,
        sum(case when year(event_time)='2023' and region ='华南' then 1 else 0 end) as south_2023_signup,
        sum(case when year(event_time)='2023' and region ='华东' then 1 else 0 end) as east_2023_signup
    from Users 
        join Events using(user_id)
        join Campaigns using(campaign_id)
    where event_type='signup'
    group by campaign_id,campaign_name
),
b as(
    select 
        campaign_id,
        round(avg(timestampdiff(minute,c1t,c2t)),2) as avg_click_to_signup_min_2024_02
    from(
        select 
            c1.campaign_id,
            c1.user_id ,
            c1.event_id,
            c1.event_time as c1t,
            c2.event_time as c2t,
            rank() over(partition by c1.event_id  order by c1.event_time ,c2.event_time) as rk
        from  Events c1
            join Events c2 on c1.user_id =c2.user_id and c1.campaign_id =c2.campaign_id and c1.event_type='click' and  c2.event_type='signup'and date_format(c1.event_time,'%Y-%m')='2024-02'
            and date_format(c2.event_time,'%Y-%m')='2024-02' and c1.event_time<c2.event_time
    ) temp
    where rk=1
    group by campaign_id
),
b2 as (
    select 
        campaign_id,
        channel,
        count(*) as number,
        rank() over(partition by campaign_id order by count(*) desc,CASE channel 
                         WHEN 'app' THEN 1
                         WHEN 'mini' THEN 2
                         WHEN 'web' THEN 3
                     END ASC) as rk    
    from Events
    where event_type='signup' and date_format(event_time,'%Y-%m')='2024-02'
    group by campaign_id,channel 
)
select 
    campaign_id,
    campaign_name,
    conv_2023_02,
    conv_2024_02,
    conv_2024_01,
    conv_2024_02 - conv_2023_02 as yoy_delta,
    conv_2024_02 - conv_2024_01 as mom_delta,
    ifnull(round(north_2023_signup/total_2023_signup*100,2),0.00) as north_pct_2023,
    ifnull(round(south_2023_signup/total_2023_signup*100,2),0.00) as south_pct_2023 ,
    ifnull(round(east_2023_signup/total_2023_signup*100,2),0.00) as east_pct_2023,
    avg_click_to_signup_min_2024_02,
    channel as top_channel_2024_02
from a
    join b using(campaign_id)
    join (select * from b2 where rk=1) temp using(campaign_id)
order by campaign_id,campaign_name;