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

with conv_cnt as (
    select
        c.campaign_id,
        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
    from Campaigns c 
    left join Events e on c.campaign_id=e.campaign_id
    where event_type = 'signup'
    group by c.campaign_id
),
# 2023 年各区域注册占比 = 该区域注册数/该活动总的注册数
pct_data as(
    select
        campaign_id,
        max(north_pct_2023) as north_pct_2023,
        max(south_pct_2023) as south_pct_2023,
        max(east_pct_2023) as east_pct_2023
    from (
        select
            campaign_id,
            round(ifnull((
                case
                when region = '华北' then region_ratio
                else null
                end
            ),0),2) as north_pct_2023,
            round(ifnull((
                case
                when region = '华南' then region_ratio
                else null
                end
            ),0),2) as south_pct_2023,
            round(ifnull((
                case
                when region = '华东' then region_ratio
                else null
                end
            ),0),2) as east_pct_2023
        from (
            select
                campaign_id,
                region,
                round(ifnull((count(event_id) over(partition by campaign_id, region))*100/count(event_id) over(partition by campaign_id),0),2) as region_ratio
            from Events e 
            join Users u on e.user_id=u.user_id
            where year(event_time) = 2023 and event_type = 'signup'
        )as t1
    )as t2
    group by campaign_id
),
max_click as (
    select
        campaign_id,
        user_id,
        max(event_time) as max_click_time
    from Events
    where event_type = 'click'
    group by campaign_id, user_id
),
max_signup as(
    select
        campaign_id,
        user_id,
        max(event_time) as max_signup_time
    from Events
    where event_type = 'signup'
    group by campaign_id, user_id
),
avg_data as (
    select
        campaign_id,
        round(sum(diff_time)/count(distinct user_id),2) as avg_click_to_signup_min_2024_02
    from(
        select
            mc.campaign_id,
            mc.user_id,
            timestampdiff(minute,max_click_time,max_signup_time) as diff_time
        from max_click mc
        left join max_signup ms on mc.campaign_id=ms.campaign_id and mc.user_id=ms.user_id
        where max_click_time <= max_signup_time
    )as t1
    group by campaign_id
),
-- 强制转换字符类型，channel字段为枚举类型，无法升序排列
top_channel as (
    select
        campaign_id,
        device as top_channel_2024_02
    from (
        select
            campaign_id,
            device,
            row_number() over(partition by campaign_id order by device_cnt desc, cast(device as char) asc) as rk 
        from (
            select
                campaign_id,
                device,
                count(*) as device_cnt
            from Events e 
            left join Users u on e.user_id = u.user_id
            where date_format(event_time, '%Y-%m') = '2024-02' and event_type = 'signup'
            group by campaign_id, device
        )as t1
    )as t2
    where rk = 1
)
select
    c.campaign_id,
    campaign_name,
    ifnull(conv_2023_02, 0) as conv_2023_02,
    ifnull(conv_2024_02, 0) as conv_2024_02,
    ifnull(conv_2024_01, 0) as conv_2024_01,
    ifnull(conv_2024_02 - conv_2023_02, 0) as yoy_delta,
    ifnull(conv_2024_02 - conv_2024_01, 0) as mom_delta,
    ifnull(north_pct_2023, 0) as north_pct_2023,
    ifnull(south_pct_2023, 0) as south_pct_2023,
    ifnull(east_pct_2023, 0) as east_pct_2023,
    ifnull(avg_click_to_signup_min_2024_02, 0) as avg_click_to_signup_min_2024_02,
    ifnull(top_channel_2024_02, 0) as top_channel_2024_02
from Campaigns c 
left join conv_cnt on c.campaign_id=conv_cnt.campaign_id
left join pct_data on c.campaign_id=pct_data.campaign_id
left join avg_data on c.campaign_id=avg_data.campaign_id
left join top_channel on c.campaign_id=top_channel.campaign_id
order by c.campaign_id asc, campaign_name asc