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

WITH t1 AS (
    SELECT C.campaign_id,
           campaign_name,
           COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2023-02' THEN event_id END) AS conv_2023_02,
           COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2024-02' THEN event_id END) AS conv_2024_02,
           COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2024-01' THEN event_id END) AS conv_2024_01,
           COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2024-02' THEN event_id END)-COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2023-02' THEN event_id END) AS yoy_delta,
           COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2024-02' THEN event_id END)-COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2024-01' THEN event_id END) AS mom_delta,
           ROUND(IFNULL(COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2023-02' AND region='华北' THEN event_id END)/COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2023-02' THEN event_id END)*100,0),2) AS north_pct_2023 ,
           ROUND(IFNULL(COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2023-02' AND region='华南' THEN event_id END)/COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2023-02' THEN event_id END)*100,0),2) AS south_pct_2023,
           ROUND(IFNULL(COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2023-02' AND region='华东' THEN event_id END)/COUNT(CASE WHEN DATE_FORMAT(event_time,'%Y-%m')='2023-02' THEN event_id END)*100,0),2) AS east_pct_2023
    FROM Campaigns C 
    LEFT JOIN Events E ON C.campaign_id=E.campaign_id
    LEFT JOIN Users U ON E.user_id=U.user_id
    WHERE event_type='signup'
    GROUP BY C.campaign_id,campaign_name
),
t2 AS (
    SELECT e1.campaign_id,
           ROUND(AVG(TIMESTAMPDIFF(MINUTE,click_time,signup_time)),2) AS avg_click_to_signup_min_2024_02
    FROM (
        SELECT campaign_id,user_id,MAX(event_time) AS click_time
        FROM Events
        WHERE event_type='click' AND DATE_FORMAT(event_time,'%Y-%m')='2024-02'
        GROUP BY campaign_id,user_id
    )  e1 
    LEFT JOIN (
        SELECT campaign_id,user_id,MIN(event_time) AS signup_time
        FROM Events
        WHERE event_type='signup' AND DATE_FORMAT(event_time,'%Y-%m')='2024-02'
        GROUP BY campaign_id,user_id
    ) e2 USING(campaign_id,user_id)
    WHERE e1.click_time IS NOT NULL AND e1.click_time<e2.signup_time
    GROUP BY e1.campaign_id
),
t3 AS (
    SELECT campaign_id,
           channel,
           ROW_NUMBER() OVER(PARTITION BY campaign_id ORDER BY COUNT(event_id) DESC,CASE channel 
                         WHEN 'app' THEN 1
                         WHEN  'mini' THEN  2
                         WHEN  'web' THEN 3
                     END ASC) AS channel_rnk
    FROM Events
    WHERE event_type='signup' AND DATE_FORMAT(event_time,'%Y-%m')='2024-02'
    GROUP BY campaign_id,channel
),
t4 AS (
    SELECT campaign_id,
           channel
    FROM t3
    WHERE channel_rnk=1
)
SELECT t1.*,
       t2.avg_click_to_signup_min_2024_02,
       t4.channel AS top_channel_2024_02
FROM t1 
LEFT JOIN t2  ON t1.campaign_id=t2.campaign_id
LEFT JOIN t4  ON t1.campaign_id=t4.campaign_id
ORDER BY t1.campaign_id,t1.campaign_name