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

with t1 as(
select
a.campaign_id,
a.campaign_name,
sum(case when left(b.event_time,7)='2023-02' then 1 else 0 end) as conv_2023_02,
sum(case when left(b.event_time,7)='2024-02' then 1 else 0 end) as conv_2024_02,
sum(case when left(b.event_time,7)='2024-01' then 1 else 0 end) as conv_2024_01,
sum(case when left(b.event_time,7)='2024-02' then 1 else 0 end)-sum(case when left(b.event_time,7)='2023-02' then 1 else 0 end) as yoy_delta,
sum(case when left(b.event_time,7)='2024-02' then 1 else 0 end)-sum(case when left(b.event_time,7)='2024-01' then 1 else 0 end) as mom_delta
from Campaigns a
left join Events b on a.campaign_id=b.campaign_id
left join Users c on b.user_id=c.user_id
where b.event_type='signup'
group by a.campaign_id,a.campaign_name),
t2 as(
select
a.campaign_id, 
COUNT(DISTINCT case when b.region='华北' then a.user_id else null end) as north_2023,
COUNT(DISTINCT case when b.region='华南' then a.user_id else null end) as south_2023,
COUNT(DISTINCT case when b.region='华东' then a.user_id else null end) as east_2023,
COUNT(DISTINCT a.user_id) as total_signup_2023
from Events a join Users b using(user_id)
where a.event_type='signup' and year(a.event_time)=2023
group by a.campaign_id),
t3 as(
select
campaign_id, 
round(if(total_signup_2023=0,0,north_2023/total_signup_2023*100),2) as north_pct_2023,
round(if(total_signup_2023=0,0,south_2023/total_signup_2023*100),2) as south_pct_2023,
round(if(total_signup_2023=0,0,east_2023/total_signup_2023*100),2) as east_pct_2023
from t2),
t4 as(
select
a.campaign_id,
a.user_id,
timestampdiff(minute,max(b.click_time),a.signup_time) as time_diff
from (select user_id,campaign_id,channel,event_time as signup_time from Events where left(event_time,7)='2024-02' and event_type='signup') a 
join (select user_id,campaign_id,channel,event_time as click_time from Events where left(event_time,7)='2024-02' and event_type='click') b on a.campaign_id=b.campaign_id and a.user_id=b.user_id and a.signup_time>b.click_time
group by a.campaign_id,a.user_id,a.signup_time
having max(b.click_time) is not null),
t5 as(
select
campaign_id,
ifnull(round(avg(time_diff),2),0.00) as avg_click_to_signup_min_2024_02
from t4
group by campaign_id),
t6 as(
select 
campaign_id,
channel,
count(1) as signup_count
from Events 
where event_type='signup' and left(event_time,7)='2024-02'
group by campaign_id,channel),
t7 as(
select
campaign_id,
channel,
row_number() over(partition by campaign_id order by signup_count desc,field(channel,'app','mini','web') asc) as rn 
from t6),
t8 as(
select
campaign_id, 
channel as top_channel_2024_02
from t7 
where rn=1)
select
t1.*,
COALESCE(t3.north_pct_2023, 0.00) as north_pct_2023,
COALESCE(t3.south_pct_2023, 0.00) as south_pct_2023,
COALESCE(t3.east_pct_2023, 0.00) as east_pct_2023,
COALESCE(t5.avg_click_to_signup_min_2024_02, 0.00) as avg_click_to_signup_min_2024_02,
COALESCE(t8.top_channel_2024_02, '') as top_channel_2024_02
from t1 
left join t3 using(campaign_id)
left join t5 using(campaign_id)
left join t8 using(campaign_id)
order by t1.campaign_id,t1.campaign_name;