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

with t0 as (select a.campaign_id,
campaign_name,
sum(case when event_time like '2023-02%' and event_type='signup' then 1 else 0 end) as conv_2023_02,
sum(case when event_time like '2024-02%' and event_type='signup' then 1 else 0 end) as conv_2024_02,
sum(case when event_time like '2024-01%' and event_type='signup' then 1 else 0 end) as conv_2024_01,
sum(case when event_time like '2024-02%' and event_type='signup' then 1 else 0 end) -
sum(case when event_time like '2023-02%' and event_type='signup' then 1 else 0 end) as yoy_delta,
sum(case when event_time like '2024-02%' and event_type='signup' then 1 else 0 end) -
sum(case when event_time like '2024-01%' and event_type='signup' then 1 else 0 end) as mom_delta
from Events a
left join Campaigns b on a.campaign_id=b.campaign_id
group by 1,2
)
, t1 as (
    select campaign_id,
    sum(case when region='华北'then 1 else 0 end)/count(*) as north_pct_2023,
     sum(case when region='华南'then 1 else 0 end)/count(*) as south_pct_2023,
      sum(case when region='华东'then 1 else 0 end)/count(*) as east_pct_2023
    from Events a 
    left join Users b on a.user_id=b.user_id
    where event_time like '2023%' and event_type='signup'
    group by 1
)
, 
 t2 as (
    select campaign_id,
    user_id,
    event_time as signup_time
    from Events
    where event_type='signup' and event_time like '2024-02%'
    
)
, t3 as (
    select
    campaign_id,
    user_id,
    event_time as click_time
    from Events
    where event_type='click' and event_time like '2024-02%'


)

,t4 as 
(
    select a.campaign_id,
    a.signup_time,
    b.click_time,
   row_number() over(partition by a.campaign_id,a.user_id,a.signup_time order by b.click_time desc) as rn
    from t2 a
    inner join t3 b on a.campaign_id=b.campaign_id and a.user_id=b.user_id and b.click_time<=a.signup_time and b.click_time is not null
 
)
, t5 as (
select
campaign_id,
avg(timestampdiff(second,click_time,signup_time)/60) as avg_click_to_signup_min_2024_02
from t4
where rn=1
group by 1

)

, t6 as 
(select campaign_id,
channel,
row_number() over(partition by campaign_id order by cnt desc,field(channel,'app','mini','web')) as rn
from 
(
select campaign_id,
channel,
count(*) as cnt
from Events 
where event_type='signup' and event_time like '2024-02%'
group by 1,2
) a
)
,
t7 as (
    select campaign_id,
    channel as top_channel_2024_02
    from t6
    where rn=1
)

select a.campaign_id,
campaign_name,
conv_2023_02,
conv_2024_02,
conv_2024_01,
yoy_delta,
mom_delta,
if(north_pct_2023 is null,0.00 ,round(north_pct_2023*100,2)) as north_pct_2023 ,
if(south_pct_2023 is null,0.00,round(south_pct_2023*100,2)) as south_pct_2023,
if(east_pct_2023 is null,0.00,round(east_pct_2023*100,2)) as east_pct_2023,
round(avg_click_to_signup_min_2024_02,2) as avg_click_to_signup_min_2024_02,
top_channel_2024_02
from t0 a 
left join t1 b on a.campaign_id=b.campaign_id
left join t5 c on a.campaign_id=c.campaign_id
left join t7 d on a.campaign_id=d.campaign_id
order by 1,2