M+3 의 재 구매율, M+(1,2,3)의 재 구매율

M+3 의 '재구매율' 이란?

예를 들어 1월의 (신규) 구매자들 중에 4월달에도 재구매를 한 비율을 뜻한다.

 

M+(1,2,3) 의 '재구매율' 이란?

예를 들어 1월의 (신규) 구매자들 중에 2월부터 4월까지 재구매를 한 비율을 뜻한다.

이를 SQL를 이용하여 계산해보도록 하자.

 

1. 아래와 같이 tbl_uesr, tbl_order 라는 테이블이 있다고 가정합니다.

2. 2019년1월 부터 2019년 12월까지 12개월을 대상으로 'M+3 재구매율'

select base_mon.now_dt
	, count(distinct base_mon.user_id) as cnt_curmon
	, count(distinct future_mon.user_id) as cnt_nextmon
	, count(distinct future_mon.user_id)/count(distinct base_mon.user_id) as rate 
from (
	select date_format(first_order_date, '%Y-%m') as now_dt
		, user_id  
	from tbl_user 
    where firt_order_date >= '2019-01-01' and first_order_date < '2020-01-01'
	) base_mon 
left join (
	select date_format(date_sub(created, interval 3 month), '%Y-%m') as future_dt
    	, user_id  
	from tbl_order 
	where order_date >= '2019-04-01' and order_date < '2020-04-01'
	) future_mon on base_mon.now_dt = future_mon.future_dt 
    	and base_mon.user_id = future_mon.user_id 
group by base_mon.now_dt;

3. 2019년1월 부터 2019년 12월까지 12개월을 대상으로 'M+(1,2,3) 재구매율'

select new_user.base_mon
	, count(distinct new_user.user_id) as cnt_base_mon 
	, count(distinct tbl_order.user_id) as cnt_m123
    , count(distinct tbl_order.user_id) / count(distinct new_user.user_id) as retention
from (
	select date_format(first_order_date, '%Y-%m') as base_mon
		, date_foramt(date_add(first_order_date, interval 1 month), '%Y-%m-01') as start_dt
		, date_foramt(date_add(first_order_date, interval 4 month), '%Y-%m-01') as end_dt
		, user_id  
	from tbl_user
	where first_order_date >= '2019-01-01' and first_order_date < '2020-01-01'
	) new_user
left join tbl_order on tbl_order.user_id = new_user.user_id 
	and tbl_order.order_date between new_user.start_dt and new_user.end_dt
group by new_user.base_mon;