[SQL] 고객 잔존율을 구해보자

고객 잔존율

잔존율의 정의를 가져와보면 다음과 같다. 

 

잔존율
은 앱 설치 이후 남아있는 활성 유저의 비율입니다. 특정 시점 이후 신규 유저가 언제까지 남아있는지 알려주는 지표로 모든 마케팅 성공의 결정적인 지표입니다. 앱 설치 후 1일, 2일, 3일 ~ 7일(n일차) 각 경과 일자별로 재접속하는 비율입니다.

 

마케팅을 하다 보면, 고객의 잔존율을 봐야하는 순간이 필수적으로 생긴다.

특정 이벤트에 참여한 고객들이 얼마나 남아있는지 살펴봐야 할 때도 있고

그 이벤트로 유입된 고객들의 건강도를 가늠하기도 하고,

지금 우리 서비스가 건강한지를 알아보기도 하고, 이탈한 고객들의 추이를 보기도 하고

지금 활동하는 유저들의 활동 수준을 보기도 한다. 

 

 

이렇게 중요한 잔존율을 나는 약간 무식하게(?) 뽑는 타입인데, 

간단하게 공유해보고자 한다. 

 

 

1. 전체 주문 데이터를 뽑는다.

2. 기준 주문일 데이터를 뽑는다.

(아래 쿼리에서는 첫주문일이 기준일이다.)

3. 전체 주문에 기준 주문일을 left join 한다. (T 테이블)

4. 월별로 쭈욱 늘어놓는다.(FOT 테이블)

5. 해당 월에 주문이 있으면 count_if로 센다.

6. count_if 로 센 것을 월별로 다시 늘어놓아서 센다.

7. 잔존수 추출 끗!  이제 엑셀로 가져가서 백분율로 가공하면 된다. 

%presto

with AO as(
 select
       a.ord_dt
      , a.ord_no
      , a.mem_no
from all_ord_table a
where 1=1
    and a.part_date >= '${start_date}'
    and a.part_date <= '${end_date}'
),--전체 주문 

 FO as (
select mem_no, min(first_ord_dt) as first_ord_dt
from
 (select
        mem_no,
        first_ord_dt
 from first_ord_table
)-- 전체 첫주문

, T as (
 select AO.ord_dt 
        , AO.mem_no
        , AO.ord_no
        , FO.first_ord_dt
from AO
    left join FO
    on AO.mem_no = FO.mem_no
where ord_dt >='${start_date}'
      and ord_dt <='${end_date}' --기반데이터 테이블
)

, FOT as(
select FT.mem_no
      ,substr(cast(date_add('month', 0, date(ord_dt)) as varchar),1,7) as Order_Month, ord_no
      ,substr(cast(date_add('month', 0, date(first_ord_dt)) as varchar),1,7) as M0
      ,substr(cast(date_add('month', 1, date(first_ord_dt)) as varchar),1,7) as M1
      ,substr(cast(date_add('month', 2, date(first_ord_dt)) as varchar),1,7) as M2
      ,substr(cast(date_add('month', 3, date(first_ord_dt)) as varchar),1,7) as M3
      ,substr(cast(date_add('month', 4, date(first_ord_dt)) as varchar),1,7) as M4
      ,substr(cast(date_add('month', 5, date(first_ord_dt)) as varchar),1,7) as M5
      ,substr(cast(date_add('month', 6, date(first_ord_dt)) as varchar),1,7) as M6     
from 
  (
    select distinct mem_no, first_ord_dt
    from T
    where
        ord_dt = first_ord_dt --첫구매
        and ord_dt >= '${start_date}'
        and ord_dt <= '${end_date}'  
    ) 
    as FT left join
    (
    select ord_no, mem_no, ord_dt
     from T
     where ord_dt >= '${start_date}'
        and ord_dt <= '${All_Order_End_Dt}'
           
      -- 구매기간 
     ) as O 
     on FT.mem_no = O.mem_no
     
     
     )
, MT as(
select mem_no, M0
        ,count_if(Order_Month = M0) as e0
        ,count_if(Order_Month = M1) as e1
        ,count_if(Order_Month = M2) as e2
        ,count_if(Order_Month = M3) as e3
        ,count_if(Order_Month = M4) as e4
        ,count_if(Order_Month = M5) as e5
        ,count_if(Order_Month = M6) as e6
from FOT 
group by 1,2
order by 1,2,3
)

select M0
    ,count_if(e0 > 0) as count_M0
    ,count_if(e1 > 0) as count_M1
    ,count_if(e2 > 0) as count_M2
    ,count_if(e3 > 0) as count_M3
    ,count_if(e4 > 0) as count_M4
    ,count_if(e5 > 0) as count_M5
    ,count_if(e6 > 0) as count_M6
from MT
group by 1
order by 1

 

다른 분들은 더 간단하게 뽑던데 나는 왜 이렇게 화려한건지.. 

조금 더 간단하게 뽑는 방법을 생각해 봐야겠다.