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

with t0 as(
select campaign_id,channel as top_channel_2024_02 from (
select campaign_id,channel,count(channel) as dt,DENSE_RANK() over(partition by campaign_id order by count(channel) desc,case when channel='app' then 1 when channel='mini' then 2 else 3 end) as rk from Events where date_format( event_time, '%Y-%m' )= '2024-02' and event_type-'signup' group by 1,2 ) tmp where rk=1),
t1 as (
SELECT
	campaign_id,
	round(sum(
	timestampdiff( MINUTE, event_time, dt ))/count(*),2) AS avg_click_to_signup_min_2024_02 
FROM
	(
	SELECT
		campaign_id,
		user_id,
		event_type,
		channel,
		event_time,
		lead( event_time ) over ( PARTITION BY campaign_id, user_id ORDER BY event_time ) AS dt 
	FROM
	Events
	WHERE
		date_format( event_time, '%Y-%m' )= '2024-02' 
	) tmp
WHERE
	event_type = 'click' 
GROUP BY
	1),
t2 as (
SELECT
	c.campaign_id,
	campaign_name,
	sum( CASE WHEN event_type = 'signup' AND date_format( event_time, '%Y-%m' )= '2023-02' THEN 1 ELSE 0 END ) AS conv_2023_02,
	sum( CASE WHEN event_type = 'signup' AND date_format( event_time, '%Y-%m' )= '2024-02' THEN 1 ELSE 0 END ) AS conv_2024_02,
	sum( CASE WHEN event_type = 'signup' AND date_format( event_time, '%Y-%m' )= '2024-01' THEN 1 ELSE 0 END ) AS conv_2024_01,
	sum( CASE WHEN event_type = 'signup' AND date_format( event_time, '%Y-%m' )= '2024-02' THEN 1 ELSE 0 END )-sum( CASE WHEN event_type = 'signup' AND date_format( event_time, '%Y-%m' )= '2023-02' THEN 1 ELSE 0 END ) as yoy_delta,
	sum( CASE WHEN event_type = 'signup' AND date_format( event_time, '%Y-%m' )= '2024-02' THEN 1 ELSE 0 END )-sum( CASE WHEN event_type = 'signup' AND date_format( event_time, '%Y-%m' )= '2024-01' THEN 1 ELSE 0 END ) as mom_delta,
	round(coalesce(sum(case when region='华北' and year(event_time)=2023 and event_type = 'signup' then 1 else 0 end)/sum( CASE WHEN event_type = 'signup' AND year( event_time)= 2023 THEN 1 ELSE 0 END )*100,0),2) as north_pct_2023,
	round(coalesce(sum(case when region='华南' and year(event_time)=2023 and event_type = 'signup' then 1 else 0 end)/sum( CASE WHEN event_type = 'signup' AND year( event_time)= 2023 THEN 1 ELSE 0 END )*100,0),2) as south_pct_2023,
	round(coalesce(sum(case when region='华东' and year(event_time)=2023 and event_type = 'signup' then 1 else 0 end)/sum( CASE WHEN event_type = 'signup' AND year( event_time)= 2023 THEN 1 ELSE 0 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 u.user_id=e.user_id
GROUP BY
	1,
	2)
	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,top_channel_2024_02 from t2 join t1 using(campaign_id) join t0 using(campaign_id) order by 1,2