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

with conv_temp as (
select t.campaign_id, t.campaign_name, t.conv_2023_02, t.conv_2024_02, t.conv_2024_01,
t.conv_2024_02 - t.conv_2023_02 as yoy_delta,
t.conv_2024_02 - t.conv_2024_01 as mom_delta,
t.north_pct_2023, t.south_pct_2023, t.east_pct_2023
from (select c.campaign_id ,c.campaign_name ,
sum(case when YEAR(event_time)=2023 and month(event_time)=2 then 1 else  0 end) as conv_2023_02,
sum(case when YEAR(event_time)=2024 and month(event_time)=2 then 1 else  0 end) as conv_2024_02,
sum(case when YEAR(event_time)=2024 and month(event_time)=1 then 1 else  0 end) as conv_2024_01,
COALESCE(round(sum(case when  region='华北' and YEAR(event_time)=2023 then 1 else 0 end)/
NULLIF(sum(case when  YEAR(event_time)=2023 then 1 else 0 end), 0)*100,2), 0.00) as north_pct_2023,
COALESCE(round(sum(case when  region='华南' and YEAR(event_time)=2023 then 1 else 0 end)/
NULLIF(sum(case when  YEAR(event_time)=2023 then 1 else 0 end), 0)*100,2), 0.00) as south_pct_2023,
COALESCE(round(sum(case when  region='华东' and YEAR(event_time)=2023 then 1 else 0 end)/
NULLIF(sum(case when  YEAR(event_time)=2023 then 1 else 0 end), 0)*100,2), 0.00) as east_pct_2023
from  Campaigns c
left join  Events e on e.campaign_id=c.campaign_id
and e.event_type='signup'
left join  Users u on u.user_id=e.user_id
group by c.campaign_id,c.campaign_name
) t 
),
avg_click_temp as (
select t.campaign_id, t.user_id, max(e.event_time) as min_click, t.sign_time
from ( select  campaign_id,user_id,event_time as sign_time
from Events
where event_type='signup'
and YEAR(event_time)=2024 and month(event_time)=2
group by campaign_id,user_id,event_time
)  t
join Events e on t.campaign_id=e.campaign_id
and t.user_id=e.user_id
where e.event_time<t.sign_time
and e.event_type='click'
and YEAR(e.event_time)=2024 and month(e.event_time)=2
group by t.campaign_id, t.user_id, t.sign_time
),
top_channel_temp as (
select campaign_id,channel ,rank() over(partition by campaign_id order by channel_count desc, CONCAT(channel)) as rn
from (select campaign_id,channel,count(*) as channel_count 
from Events 
where YEAR(event_time)=2024 and month(event_time)=2
and event_type='signup'
group by campaign_id,channel) t
)
select ct.campaign_id, ct.campaign_name, ct.conv_2023_02, ct.conv_2024_02, ct.conv_2024_01,
ct.yoy_delta, ct.mom_delta, ct.north_pct_2023, ct.south_pct_2023, ct.east_pct_2023,
COALESCE(round(avg(timestampdiff(SECOND, act.min_click, act.sign_time))/60,2), 0.00) as avg_click_to_signup_min_2024_02,
tct.channel as top_channel_2024_02
from conv_temp ct
left join avg_click_temp act on act.campaign_id=ct.campaign_id
left join top_channel_temp tct on tct.campaign_id=ct.campaign_id
where tct.rn=1
group by ct.campaign_id, ct.campaign_name, ct.conv_2023_02, ct.conv_2024_02, ct.conv_2024_01,
ct.yoy_delta, ct.mom_delta, ct.north_pct_2023, ct.south_pct_2023, ct.east_pct_2023, tct.channel
order by ct.campaign_id, ct.campaign_name;