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

select a.campaign_id 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,channel top_channel_2024_02 
from (
select Campaigns.campaign_id campaign_id,campaign_name
,count(case when date_format(event_time,'%Y-%m')='2023-02' and event_type ='signup' then 1 else null end) conv_2023_02
,count(case when date_format(event_time,'%Y-%m')='2024-02' and event_type ='signup' then 1 else null end) conv_2024_02
,count(case when date_format(event_time,'%Y-%m')='2024-01' and event_type ='signup' then 1 else null end) conv_2024_01
,count(case when date_format(event_time,'%Y-%m')='2024-02' and event_type ='signup' then 1 else null end)-count(case when date_format(event_time,'%Y-%m')='2023-02' and event_type ='signup' then 1 else null end) yoy_delta
,count(case when date_format(event_time,'%Y-%m')='2024-02' and event_type ='signup' then 1 else null end)-count(case when date_format(event_time,'%Y-%m')='2024-01' and event_type ='signup' then 1 else null end) mom_delta
,coalesce(round(count(case when date_format(event_time,'%Y')='2023' and event_type ='signup'  and region='华北' then 1 else null end)/count(case when date_format(event_time,'%Y')='2023' and event_type ='signup' then 1 else null end)*100,2),0.00) north_pct_2023 
,coalesce(round(count(case when date_format(event_time,'%Y')='2023' and event_type ='signup'  and region='华南' then 1 else null end)/count(case when date_format(event_time,'%Y')='2023' and event_type ='signup' then 1 else null end)*100,2),0.00) south_pct_2023  
,coalesce(round(count(case when date_format(event_time,'%Y')='2023' and event_type ='signup'  and region='华东' then 1 else null end)/count(case when date_format(event_time,'%Y')='2023' and event_type ='signup' then 1 else null end)*100,2),0.00) east_pct_2023 
from Campaigns
left join Events
on Campaigns.campaign_id=Events.campaign_id
left join Users
on Events.user_id=Users.user_id
group by Campaigns.campaign_id,campaign_name)a 
left join (
    select campaign_id
    ,coalesce(round(avg(min_2024_02),2),0.00) avg_click_to_signup_min_2024_02
    from(
    select campaign_id ,min_2024_02
    from(
    select campaign_id ,user_id,event_time ,event_type
    ,timestampdiff(minute,lag(event_time)over(partition by campaign_id,user_id order by event_time),event_time) min_2024_02
    from Events
    where date_format(event_time,'%Y-%m')='2024-02'
    )b 
    where event_type='signup'
    and min_2024_02!=event_type)c 
    group by campaign_id
)d 
on a.campaign_id=d.campaign_id
left join (
    select campaign_id,channel
    from(
    select campaign_id,channel 
    ,count(case when event_type ='signup' then 1 else null end) cnt
    ,row_number()over(partition by campaign_id order by count(case when event_type ='signup' then 1 else null end) desc,FIELD(channel, 'app', 'mini', 'web')) rn 
    from Events
    where date_format(event_time,'%Y-%m')='2024-02'
    group by campaign_id,channel )e 
    where rn=1
)f 
on a.campaign_id=f.campaign_id