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

WITH t1 AS (
			SELECT c.campaign_id,
				   c.campaign_name,
  				   c.category,
  				   u.user_id,
  				   u.region,
				   u.device,
				   e.event_type,
				   e.channel,
				   e.event_time
			FROM Campaigns c 
			LEFT JOIN Events e ON e.campaign_id = c.campaign_id
			LEFT JOIN Users u ON u.user_id = e.user_id
),
-- 计算每个活动不同月份注册数 
t2 AS (
		SELECT campaign_id,
    		   campaign_name,
			   COUNT(CASE 
			   			WHEN DATE_FORMAT(event_time,'%Y-%m') = '2023-02' 
							THEN event_time END) AS conv_2023_02,
			   COUNT(CASE 
			   			WHEN DATE_FORMAT(event_time,'%Y-%m') = '2024-02' 
							THEN event_time END) AS conv_2024_02,
			   COUNT(CASE 
			   			WHEN DATE_FORMAT(event_time,'%Y-%m') = '2024-01' 
							THEN event_time END) AS conv_2024_01
		FROM t1
		WHERE event_type = 'signup'
		GROUP BY campaign_id
),	
-- 	2023年总注册数
t3 AS (
		SELECT campaign_id,count(*) AS total_num
		FROM t1
		WHERE YEAR(event_time) = 2023 AND event_type = 'signup'
		GROUP BY campaign_id
),
-- 2023年分区域注册数 
t4 AS( 
		SELECT DISTINCT campaign_id,
			   COUNT(CASE WHEN region = '华北' THEN 1 END)OVER(PARTITION BY campaign_id) AS north_num_2023,
			   COUNT(CASE WHEN region = '华南' THEN 1 END)OVER(PARTITION BY campaign_id) AS south_num_2023,
			   COUNT(CASE WHEN region = '华东' THEN 1 END)OVER(PARTITION BY campaign_id) AS east_num_2023
		FROM t1
		WHERE YEAR(event_time) = 2023 AND event_type = 'signup'
),
-- 2023-02的注册数(必须要先点击，且最晚点击时间小于注册时间）
t5 AS (
		SELECT campaign_id,
			   ROUND(SUM(diff_time)/COUNT(DISTINCT user_id),2) AS avg_click_to_signup_min_2024_02
		FROM (
				SELECT campaign_id,
					   user_id,
					   TIMESTAMPDIFF(MINUTE,max_click_time,max_singup_time) AS diff_time
				FROM (
						SELECT a.campaign_id,a.user_id,max_click_time,max_singup_time
						FROM (
								 SELECT campaign_id,
								 		user_id,
										max(event_time) AS max_click_time
								 FROM t1
								 WHERE event_type = 'click'
								 GROUP BY campaign_id,user_id
							 )a
		      LEFT JOIN (
					      SELECT campaign_id,user_id,max(event_time) AS max_singup_time
						  FROM t1 
						  WHERE event_type = 'signup' 
						     -- AND DATE_FORMAT(event_time,'%Y-%m') = '2024-02' 
						  GROUP BY campaign_id,user_id
			=			)b ON a.user_id = b.user_id AND a.campaign_id = b.campaign_id
					)c
		WHERE max_click_time <= max_singup_time
		)d
							GROUP BY campaign_id
),
-- 计算注册数最多的渠道
t6 AS (
		SELECT campaign_id,channel AS top_channel_2024_02
		FROM (
	     	   SELECT campaign_id,
			   		  channel,
					  -- 强制转换字符类型，channel字段为枚举类型，无法升序排列
					  ROW_NUMBER()OVER(
					  		PARTITION BY campaign_id 
							ORDER BY cnt DESC,CAST(channel AS CHAR) ASC)rn
				FROM (
					   SELECT campaign_id,channel,COUNT(*)AS cnt
					   FROM t1
					   WHERE DATE_FORMAT(event_time,'%Y-%m') = '2024-02'
					   GROUP BY campaign_id,channel
					  )a
			)b
		WHERE rn = 1
)									
SELECT t2.campaign_id,		
	   t2.campaign_name,
	   t2.conv_2023_02,
	   t2.conv_2024_02,
	   t2.conv_2024_01,
	   t2.conv_2024_02 - t2.conv_2023_02 AS yoy_delta,
	   t2.conv_2024_02 - t2.conv_2024_01 AS mom_delta,
	   COALESCE(
				CASE
					WHEN t3.total_num = 0 THEN 0.00
					WHEN t3.total_num !=0  THEN ROUND(t4.north_num_2023/t3.total_num * 100,2)
				END 
				,0) AS north_pct_2023,
	   COALESCE(
				CASE
					WHEN t3.total_num = 0 THEN 0.00
					WHEN t3.total_num !=0  THEN ROUND(t4.south_num_2023/t3.total_num * 100,2)
				END 
				,0) AS south_pct_2023,
	   COALESCE(
				CASE
					WHEN t3.total_num = 0 THEN 0.00
					WHEN t3.total_num !=0  THEN ROUND(t4.east_num_2023/t3.total_num * 100,2)
				END 
				,0) AS east_pct_2023,
	   t5.avg_click_to_signup_min_2024_02,
	   t6.top_channel_2024_02
FROM t2 
LEFT JOIN t3 ON t3.campaign_id = t2.campaign_id
LEFT JOIN t4 ON t4.campaign_id = t2.campaign_id
LEFT JOIN t5 ON t5.campaign_id = t2.campaign_id
LEFT JOIN t6 ON t6.campaign_id = t2.campaign_id