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

with t1 as (
    select 
        campaign_id,
        campaign_name,
        -- 2023年总注册数（用于后续逻辑，但不直接在同一SELECT中引用别名）
        sum(if(event_type='signup' and year(event_time)=2023, 1, 0)) as total_signup_2023,
        -- 2023年2月注册量
        sum(if(event_time between '2023-02-01' and '2023-02-28' and event_type='signup', 1, 0)) as conv_2023_02,
        -- 2024年2月注册量
        sum(if(event_time between '2024-02-01' and '2024-02-29' and event_type='signup', 1, 0)) as conv_2024_02,
        -- 2024年1月注册量
        sum(if(event_time between '2024-01-01' and '2024-01-31' and event_type='signup', 1, 0)) as conv_2024_01,
        -- 同比差异（2024年2月 - 2023年2月）
        sum(if(event_time between '2024-02-01' and '2024-02-29' and event_type='signup', 1, 0)) 
        - sum(if(event_time between '2023-02-01' and '2023-02-28' and event_type='signup', 1, 0)) as yoy_delta,
        -- 环比差异（2024年2月 - 2024年1月）
        sum(if(event_time between '2024-02-01' and '2024-02-29' and event_type='signup', 1, 0)) 
        - sum(if(event_time between '2024-01-01' and '2024-01-31' and event_type='signup', 1, 0)) as mom_delta,
        -- 2023年华北地区注册占比（用实际计算逻辑替代别名引用）
        case 
            -- 直接计算2023年总注册数，判断是否为0
            when sum(if(event_type='signup' and year(event_time)=2023, 1, 0)) = 0 then 0.00
            else round(
                sum(if(region='华北' and event_type='signup' and year(event_time)=2023, 1, 0)) 
                / sum(if(event_type='signup' and year(event_time)=2023, 1, 0)) * 100, 2
            ) 
        end as north_pct_2023,
        -- 2023年华南地区注册占比（同上逻辑）
        case 
            when sum(if(event_type='signup' and year(event_time)=2023, 1, 0)) = 0 then 0.00
            else round(
                sum(if(region='华南' and event_type='signup' and year(event_time)=2023, 1, 0)) 
                / sum(if(event_type='signup' and year(event_time)=2023, 1, 0)) * 100, 2
            ) 
        end as south_pct_2023,
        -- 2023年华东地区注册占比（同上逻辑）
        case 
            when sum(if(event_type='signup' and year(event_time)=2023, 1, 0)) = 0 then 0.00
            else round(
                sum(if(region='华东' and event_type='signup' and year(event_time)=2023, 1, 0)) 
                / sum(if(event_type='signup' and year(event_time)=2023, 1, 0)) * 100, 2
            ) 
        end as east_pct_2023
    from Events 
    join Users using(user_id)
    join Campaigns using(campaign_id)
    group by campaign_id, campaign_name
),
t2 as (
    select 
        campaign_id,
        user_id,
        event_type,
        event_time,
        lead(event_type, 1) over(partition by user_id, campaign_id order by event_time) as next_type,
        lead(event_time, 1) over(partition by user_id, campaign_id order by event_time) as next_time
    from Events 
    where left(event_time, 7) = '2024-02'
),
t3 as (
    select 
        campaign_id,
        round(avg(timestampdiff(minute, event_time, next_time)), 2) as avg_click_to_signup_min_2024_02
    from t2
    where next_type = 'signup' and event_type = 'click'
    group by campaign_id
),
t4 as (
    select 
 campaign_id,channel,count(event_type),row_number()over(partition by campaign_id order by count(event_type) desc,channels asc) 'rk'
    from (
    select c.campaign_id,e.channel,e.event_time, e.event_type,case e.channel when 'app' then 0 
    when 'mini' then 1 
    when 'web' then 2 end as channels
    from Campaigns c inner join Events e using(campaign_id)
    where e.event_type = 'signup' and e.event_time like '2024-02%'
    ) t
    group by 1,2
)
select 
    t1.campaign_id,
    t1.campaign_name,
    t1.conv_2023_02,
    t1.conv_2024_02,
    t1.conv_2024_01,
    t1.yoy_delta,
    t1.mom_delta,
    t1.north_pct_2023,
    t1.south_pct_2023,
    t1.east_pct_2023,
    t3.avg_click_to_signup_min_2024_02,
    t4.channel as top_channel_2024_02
from t1 
join t3 using(campaign_id)
join t4 using(campaign_id)
where t4.rk = 1
order by campaign_id;