题解 | 25年得物-潮鞋新品发售后 N 日复购留存矩阵

select t0.series_id
    ,series_name
    ,count(distinct t0.user_id) first_buyer_cnt
    ,round(count(distinct t1.user_id)/count(distinct t0.user_id),2) d1_rate
    ,round(count(distinct t3.user_id)/count(distinct t0.user_id),2) d3_rate
    ,round(count(distinct t7.user_id)/count(distinct t0.user_id),2) d7_rate
    ,round(count(distinct t14.user_id)/count(distinct t0.user_id),2) d14_rate
from (
    select user_id,series_id,min(date(pay_time)) day0
    from t_order
    group by user_id,series_id
) t0
left join t_order t1 on t0.user_id = t1.user_id 
    and t0.series_id = t1.series_id
    and date_add(day0,interval 1 day)=date(t1.pay_time)
left join t_order t3 on t0.user_id = t3.user_id 
    and t0.series_id = t3.series_id
    and date_add(day0,interval 3 day)=date(t3.pay_time)
left join t_order t7 on t0.user_id = t7.user_id 
    and t0.series_id = t7.series_id
    and date_add(day0,interval 7 day)=date(t7.pay_time)
left join t_order t14 on t0.user_id = t14.user_id 
    and t0.series_id = t14.series_id
    and date_add(day0,interval 14 day)=date(t14.pay_time)
left join t_series b on t0.series_id = b.series_id
group by t0.series_id
    ,series_name
order by d7_rate desc,first_buyer_cnt desc,t0.series_id

注意小细节

1. 匹配的时候需要 date_add(day0,interval 14 day)=date(t14.pay_time)，这里转换格式，否则留存率全是0
2. 分子是 count(distinct t1.user_id)，如果用count(t1.pay_time)，一个用户一天内若有两单复购会重复计算