留存/复购cohort

留存cohort

新用户留存cohort

-- 留存cohort
select
    coh.pro_name
    ,coh.city_name
    ,coh.warehouse_code
    ,coh.warehouse_name
    ,coh.warehouse_type
    ,coh.agent_id
    ,coh.agent_name
    ,coh.moz_id
    ,coh.moz_name
    ,coh.fir_wk
    ,count(distinct coh.actual_user_id) usr_cnt
    ,count(distinct (case when coh.date_diff = 7 then coh.actual_user_id else null end)) as wk1
    ,count(distinct (case when coh.date_diff = 14 then coh.actual_user_id else null end)) as wk2
    ,count(distinct (case when coh.date_diff = 21 then coh.actual_user_id else null end)) as wk3
    ,count(distinct (case when coh.date_diff = 28 then coh.actual_user_id else null end)) as wk4
    ,count(distinct (case when coh.date_diff = 35 then coh.actual_user_id else null end)) as wk5
    ,count(distinct (case when coh.date_diff = 42 then coh.actual_user_id else null end)) as wk6
    ,count(distinct (case when coh.date_diff = 49 then coh.actual_user_id else null end)) as wk7
    ,count(distinct (case when coh.date_diff = 56 then coh.actual_user_id else null end)) as wk8
    ,1 as bk1
    ,2 as bk2
    ,3 as bk3
    ,4 as bk4
    ,5 as bk5
from
(
select
    t1.fir_wk
    ,t1.city_name
    ,t1.pro_name
    ,t1.warehouse_code
    ,t1.warehouse_name
    ,t1.warehouse_type
    ,t1.agent_id
    ,t1.agent_name
    ,t1.moz_id
    ,t1.moz_name
    ,t1.actual_user_id
    ,t2.week_last_date
    ,datediff(t2.week_last_date,t1.fir_wk) date_diff

from
(
select
    cad.week_last_date fir_wk
    ,wh.city_name
    ,wh.pro_name
    ,ord.warehouse_code
    ,wh.warehouse_name
    ,wh.warehouse_type
    ,ord.agent_id
    ,agt.supplier_name agent_name
    ,agt.moz_id
    ,agt.moz_name
    ,ord.actual_user_id

from
(
select
    dt
    ,agent_id
    ,warehouse_code
    ,actual_user_id
    ,sum(sale_cnt) sale_cnt
from cx_agt.dwd_cx_fin_sub_order_di
where dt  between date_sub('${end_date}',62) and date_sub('${end_date}',7)
group by dt
    ,agent_id
    ,warehouse_code
    ,actual_user_id
) ord

--是否首单
left join (
select
    user_id
    ,fst_ord_date
from    cx_agt.dwm_cx_mem_usr_beh_td
where   dt = '${end_date}'
group by user_id
    ,fst_ord_date
) agt_user
on ord.actual_user_id = agt_user.user_id

left join
(
select
    new_warehouse_code
    ,warehouse_name
    ,warehouse_type
    ,city_name
    ,pro_name

from wujie.dim_cx_perf_warehouse_info_df
where  dt  = '${end_date}'
group by new_warehouse_code
    ,warehouse_name
    ,warehouse_type
    ,city_name
    ,pro_name
) wh on ord.warehouse_code = wh.new_warehouse_code

left join
(
select
    dt
    ,supplier_id
    ,supplier_name
    ,warehouse_code
    ,warehouse_name
    ,moz_id
    ,moz_name
    ,supplier_city_name
    ,supplier_pro_name
from cx_agt.dim_cx_pub_moz_wh
where dt between date_sub('${end_date}',62) and date_sub('${end_date}',7)
group by dt
    ,supplier_id
    ,supplier_name
    ,warehouse_code
    ,warehouse_name
    ,moz_id
    ,moz_name
    ,supplier_city_name
    ,supplier_pro_name
) agt on ord.dt = agt.dt and ord.agent_id = agt.supplier_id and ord.warehouse_code = agt.warehouse_code

left join
(
select
    date_id, --天ID，格式：YYYYMMDD
    calendar_date, --日期，格式：YYYY-MM-DD
    week_first_date,
    week_last_date,
    week_id,  -- 周ID，1到2位周ID，1、2、3到53
    year_week_id -- 年周ID，6位年周编号周为2位，不足需补零，格式：201701、201720
from
    whole_dw.dim_date
where
    dt = '${end_date}' 
    and calendar_date between date_sub('${end_date}',62) and date_sub('${end_date}',7)
group by     date_id, --天ID，格式：YYYYMMDD
    calendar_date, --日期，格式：YYYY-MM-DD
    week_first_date,
    week_last_date,
    week_id,  -- 周ID，1到2位周ID，1、2、3到53
    year_week_id -- 年周ID，6位年周编号周为2位，不足需补零，格式：201701、201720
) cad on ord.dt = cad.calendar_date
where ord.dt = agt_user.fst_ord_date
group by cad.week_last_date
    ,wh.city_name
    ,wh.pro_name
    ,ord.warehouse_code
    ,wh.warehouse_name
    ,wh.warehouse_type
    ,ord.agent_id
    ,agt.supplier_name
    ,agt.moz_id
    ,agt.moz_name
    ,ord.actual_user_id
) t1

left join
(
select
    cad.week_last_date
    ,ord.agent_id
    ,ord.warehouse_code
    ,ord.actual_user_id
    ,sum(ord.sale_cnt) sale_cnt
from cx_agt.dwd_cx_fin_sub_order_di ord

left join
(
select
    date_id, --天ID，格式：YYYYMMDD
    calendar_date, --日期，格式：YYYY-MM-DD
    week_first_date,
    week_last_date,
    week_id,  -- 周ID，1到2位周ID，1、2、3到53
    year_week_id -- 年周ID，6位年周编号周为2位，不足需补零，格式：201701、201720
from
    whole_dw.dim_date
where
    dt = '${end_date}' 
    and calendar_date between date_sub('${end_date}',55) and '${end_date}' 
group by     date_id, --天ID，格式：YYYYMMDD
    calendar_date, --日期，格式：YYYY-MM-DD
    week_first_date,
    week_last_date,
    week_id,  -- 周ID，1到2位周ID，1、2、3到53
    year_week_id -- 年周ID，6位年周编号周为2位，不足需补零，格式：201701、201720
) cad on ord.dt = cad.calendar_date

where ord.dt  between date_sub('${end_date}',55) and '${end_date}'
group by 
    cad.week_last_date
    ,ord.agent_id
    ,ord.warehouse_code
    ,ord.actual_user_id
) t2
on t1.warehouse_code = t2.warehouse_code and t1.actual_user_id = t2.actual_user_id

) coh
where coh.agent_id <> '1123419' and coh.warehouse_type is not null
group by     coh.pro_name
    ,coh.city_name
    ,coh.warehouse_code
    ,coh.warehouse_name
    ,coh.warehouse_type
    ,coh.agent_id
    ,coh.agent_name
    ,coh.moz_id
    ,coh.moz_name
    ,coh.fir_wk

累计复购cohort

用户累计复购cohort

--********************************************************************--
select
    '$[YYYY-MM-DD - 1D]' as `分区`
    ,coh.pro_id
    ,coh.pro_name
    ,coh.city_id
    ,coh.city_name
    ,coh.agent_id
    ,coh.agent_name
    ,coh.popular_name
    ,coh.moz_id
    ,coh.moz_name
    ,coh.warehouse_code
    ,coh.warehouse_name
    ,coh.warehouse_type
    ,coh.fir_wk
    ,count(distinct coh.actual_user_id) usr_cnt
    ,count(distinct (case when coh.date_diff = 7 then coh.actual_user_id else null end)) as wk1
    ,count(distinct (case when coh.date_diff between 7 and 14 and coh.fir_wk <= date_sub(next_day('$[YYYY-MM-DD]','MO'),21) then coh.actual_user_id else null end)) as wk2
    ,count(distinct (case when coh.date_diff between 7 and 21 and coh.fir_wk <= date_sub(next_day('$[YYYY-MM-DD]','MO'),28) then coh.actual_user_id else null end)) as wk3
    ,count(distinct (case when coh.date_diff between 7 and 28 and coh.fir_wk <= date_sub(next_day('$[YYYY-MM-DD]','MO'),35) then coh.actual_user_id else null end)) as wk4
    ,count(distinct (case when coh.date_diff between 7 and 35 and coh.fir_wk <= date_sub(next_day('$[YYYY-MM-DD]','MO'),42) then coh.actual_user_id else null end)) as wk5
    ,count(distinct (case when coh.date_diff between 7 and 42 and coh.fir_wk <= date_sub(next_day('$[YYYY-MM-DD]','MO'),49) then coh.actual_user_id else null end)) as wk6
    ,count(distinct (case when coh.date_diff between 7 and 49 and coh.fir_wk <= date_sub(next_day('$[YYYY-MM-DD]','MO'),56) then coh.actual_user_id else null end)) as wk7
    ,count(distinct (case when coh.date_diff between 7 and 56 and coh.fir_wk <= date_sub(next_day('$[YYYY-MM-DD]','MO'),63) then coh.actual_user_id else null end)) as wk8

from
(
select
    t1.fir_wk
    ,t1.pro_id
    ,t1.pro_name
    ,t1.city_id
    ,t1.city_name
    ,t1.agent_id
    ,t1.agent_name
    ,t1.popular_name
    ,t1.moz_id
    ,t1.moz_name
    ,t1.warehouse_code
    ,t1.warehouse_name
    ,t1.warehouse_type
    ,t1.actual_user_id
    ,t2.week_last_date
    ,datediff(t2.week_last_date,t1.fir_wk) date_diff

from
(
select
    cad.week_last_date fir_wk
    ,agt.moz_pro_id pro_id
    ,agt.moz_pro_name pro_name
    ,agt.moz_city_id city_id
    ,agt.moz_city_name city_name
    ,ord.agent_id
    ,agt.supplier_name agent_name
    ,agt.popular_name
    ,agt.moz_id
    ,agt.moz_name
    ,ord.new_warehouse_code warehouse_code
    ,agt.warehouse_name
    ,agt.warehouse_type
    ,ord.actual_user_id

from
(
select
    dt
    ,agent_id
    ,new_warehouse_code
    ,actual_user_id
    ,sum(sale_cnt) sale_cnt
from cx_agt.dwd_cx_fin_sub_order_di
where dt between date_sub(next_day('$[YYYY-MM-DD]','MO'),70) and date_sub(next_day('$[YYYY-MM-DD]','MO'),15)
group by dt
    ,agent_id
    ,new_warehouse_code
    ,actual_user_id
) ord

--是否首单
left join (
select
    user_id
    ,fst_ord_date
from    cx_agt.dwm_cx_mem_usr_beh_td
where   dt = '$[YYYY-MM-DD - 1D]'
group by user_id
    ,fst_ord_date
) agt_user
on ord.actual_user_id = agt_user.user_id


left join
(
select
    supplier_id ,supplier_name ,warehouse_code ,warehouse_name ,moz_id ,moz_name ,moz_city_id 
    ,moz_city_name ,moz_pro_id ,moz_pro_name ,test_wh_moz_flag ,popular_name ,warehouse_type
from cx_agt.dim_cx_pub_moz_wh
where dt = CASE WHEN '$[YYYY-MM-DD - 1D]' <= '2022-02-09' THEN '2022-02-09' ELSE '$[YYYY-MM-DD - 1D]' end
group by  supplier_id ,supplier_name ,warehouse_code ,warehouse_name ,moz_id ,moz_name ,moz_city_id 
    ,moz_city_name ,moz_pro_id ,moz_pro_name ,test_wh_moz_flag ,popular_name ,warehouse_type
) agt on ord.agent_id = agt.supplier_id and ord.new_warehouse_code = agt.warehouse_code

left join
(
select
    date_id, --天ID，格式：YYYYMMDD
    calendar_date, --日期，格式：YYYY-MM-DD
    week_first_date,
    week_last_date,
    week_id,  -- 周ID，1到2位周ID，1、2、3到53
    year_week_id -- 年周ID，6位年周编号周为2位，不足需补零，格式：201701、201720
from
    whole_dw.dim_date
where
    dt = '$[YYYY-MM-DD - 1D]'
    and calendar_date between date_sub(next_day('$[YYYY-MM-DD]','MO'),70) and date_sub(next_day('$[YYYY-MM-DD]','MO'),15)
group by     date_id, calendar_date, week_first_date, week_last_date, week_id, year_week_id
) cad on ord.dt = cad.calendar_date
where agt.test_wh_moz_flag <> 1
    and agt.warehouse_type <> 5 and agt.warehouse_type is not null
group by     cad.week_last_date
    ,agt.moz_pro_id
    ,agt.moz_pro_name
    ,agt.moz_city_id
    ,agt.moz_city_name
    ,ord.agent_id
    ,agt.supplier_name
    ,agt.popular_name
    ,agt.moz_id
    ,agt.moz_name
    ,ord.new_warehouse_code
    ,agt.warehouse_name
    ,agt.warehouse_type
    ,ord.actual_user_id
) t1

left join
(
select
    cad.week_last_date
    ,ord.agent_id
    ,ord.warehouse_code
    ,ord.actual_user_id
    ,sum(ord.sale_cnt) sale_cnt
from cx_agt.dwd_cx_fin_sub_order_di ord

left join
(
select
    date_id, --天ID，格式：YYYYMMDD
    calendar_date, --日期，格式：YYYY-MM-DD
    week_first_date,
    week_last_date,
    week_id,  -- 周ID，1到2位周ID，1、2、3到53
    year_week_id -- 年周ID，6位年周编号周为2位，不足需补零，格式：201701、201720
from
    whole_dw.dim_date
where
    dt = '$[YYYY-MM-DD - 1D]'
    and calendar_date between date_sub(next_day('$[YYYY-MM-DD]','MO'),63) and date_sub(next_day('$[YYYY-MM-DD]','MO'),8)
group by     date_id, --天ID，格式：YYYYMMDD
    calendar_date, --日期，格式：YYYY-MM-DD
    week_first_date,
    week_last_date,
    week_id,  -- 周ID，1到2位周ID，1、2、3到53
    year_week_id -- 年周ID，6位年周编号周为2位，不足需补零，格式：201701、201720
) cad on ord.dt = cad.calendar_date

where ord.dt  between date_sub(next_day('$[YYYY-MM-DD]','MO'),63) and date_sub(next_day('$[YYYY-MM-DD]','MO'),8)
group by 
    cad.week_last_date
    ,ord.agent_id
    ,ord.warehouse_code
    ,ord.actual_user_id
) t2
on t1.warehouse_code = t2.warehouse_code and t1.actual_user_id = t2.actual_user_id

) coh
where coh.agent_id <> '1123419' and coh.warehouse_type is not null
group by  
    coh.pro_id
    ,coh.pro_name
    ,coh.city_id
    ,coh.city_name
    ,coh.agent_id
    ,coh.agent_name
    ,coh.popular_name
    ,coh.moz_id
    ,coh.moz_name
    ,coh.warehouse_code
    ,coh.warehouse_name
    ,coh.warehouse_type
    ,coh.fir_wk;