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

WITH
    A as (
        select
            campaign_id,
            campaign_name,
            count(
                case
                    when event_time between '2023-02-01' and '2023-02-28'  then user_id
                end
            ) as conv_2023_02,
            count(
                case
                    when left(event_time, 7) = '2024-02' then u.user_id
                end
            ) as conv_2024_02,
            count(
                case
                    when event_time between '2024-01-01' and '2024-01-31'  then user_id
                end
            ) as conv_2024_01,
            count(
                case
                    when left(event_time, 7) = '2024-02' then u.user_id
                end
            ) - count(
                case
                    when event_time between '2023-02-01' and '2023-02-28'  then user_id
                end
            ) as yoy_delta,
            count(
                case
                    when left(event_time, 7) = '2024-02' then u.user_id
                end
            ) - count(
                case
                    when event_time between '2024-01-01' and '2024-01-31'  then user_id
                end
            ) as mom_delta,
            round(
                ifnull(
                    count(
                        case
                            when region = '华北'
                            and YEAR(event_time) = 2023 then region
                        end
                    ) / count(
                        case
                            when YEAR(event_time) = 2023 then region
                        end
                    ),
                    0
                ) * 100,
                2
            ) as north_pct_2023,
            round(
                ifnull(
                    count(
                        case
                            when region = '华南'
                            and YEAR(event_time) = 2023 then region
                        end
                    ) / count(
                        case
                            when YEAR(event_time) = 2023 then region
                        end
                    ),
                    0
                ) * 100,
                2
            ) as south_pct_2023,
            round(
                ifnull(
                    count(
                        case
                            when region = '华东'
                            and YEAR(event_time) = 2023 then region
                        end
                    ) / count(
                        case
                            when YEAR(event_time) = 2023 then region
                        end
                    ),
                    0
                ) * 100,
                2
            ) as east_pct_2023
        from
            Campaigns c
            left join Events u using (campaign_id)
            left join Users e using (user_id)
        where
            event_type = 'signup'
        group by
            campaign_id,
            campaign_name
    ),
    T1 AS (
        select
            campaign_id,
            user_id,
            event_time as signuptime
        from
            Events e
        where
            event_type = 'signup'
            and event_time between '2024-02-01' and '2024-02-29'
    ),
    T2 AS (
        select
            campaign_id,
            user_id,
            event_time as clicktime
        from
            Events e
        where
            event_type = 'click'
            and event_time between '2024-02-01' and '2024-02-29'
    ),
    T3 AS (
        select
            campaign_id,
            round(
                avg(timestampdiff(minute, maxclicktime, signuptime)),
                2
            ) as avg_click_to_signup_min_2024_02
        from
            (
                select
                    campaign_id,
                    user_id,
                    signuptime,
                    max(clicktime) as maxclicktime
                from
                    T1
                    left join T2 using (campaign_id, user_id)
                where
                    clicktime < signuptime
                group by
                    campaign_id,
                    user_id,
                    signuptime
            ) t
        group by
            campaign_id
    ),
    C AS (
        SELECT
            campaign_id,
            channel,
            ROW_NUMBER() OVER (
                PARTITION BY
                    campaign_id
                ORDER BY
                    COUNT(event_type = 'signup') DESC,
                    CASE
                        WHEN channel = 'app' THEN 1
                        WHEN channel = 'mini' THEN 2
                        WHEN channel = 'web' THEN 3
                    END
            ) rk
        FROM
            Events
        WHERE
            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,
    yoy_delta,
    mom_delta,
    north_pct_2023,
    south_pct_2023,
    east_pct_2023,
    avg_click_to_signup_min_2024_02,
    channel as top_channel_2024_02
from
    A
    left join T3 using (campaign_id)
    left join C using (campaign_id)
where rk = 1
order by campaign_id,campaign_name;