코호트, 리텐션

주제: 신규 고객 활성화 기준 설정
분석 방법: 코호트 분석을 통해 신규 고객이 활성화 될 수 있는 주문수 기준 설정
기간: 23년 7월 첫 주문 고객의 첫 주문일로부터 1달간 누적 주문수 별 리텐션 확인

SQL 코드

WITH
fst AS (
	SELECT mem_no, first_ord_dt
	FROM first_ord_table_practice1 fotp
	WHERE 1=1
	AND first_ord_dt >= '2023-07-01'
	AND first_ord_dt <= '2023-07-31'
),
ord as(
	SELECT DISTINCT omp.mem_no, 
			first_ord_dt,
			ord_dt,
			sum(CASE WHEN first_ord_dt < ord_dt AND ord_dt <= date_add(first_ord_dt, INTERVAL 7 day) THEN 1
				ELSE 0 END) over(PARTITION BY omp.mem_no) as is_w1_ord,
			dense_rank() over(PARTITION BY omp.mem_no ORDER BY ord_dt) ord_seq
	FROM fst
	JOIN order_master_practive1 omp ON fst.mem_no = omp.mem_no
	WHERE 1=1
	AND ord_dt >= '2023-07-01'
	AND ord_dt <= '2023-08-31'
	ORDER BY 1, 2
)
SELECT ord_seq,
		CASE WHEN is_w1_ord > 0 THEN 1 ELSE 0 END is_w1_ord,
		count(DISTINCT mem_no) mem_cnt
FROM ord
GROUP BY 1, 2
ORDER BY 2;

dense_rank를 이용해서 회원별로 누적 주문수를 구한 다음 count 했다 - 이건 1개 또 배웠다

파이썬 코드

# 첫 주문 이후 1주일 이내 주문 여부에 따라 그룹 나누기
true = df[df.is_w1_ord == 1].sort_values('mem_cnt', ascending=False)
false = df[df.is_w1_ord == 0].sort_values('mem_cnt', ascending=False)

# 리텐션 구하기
true['retention'] = true.mem_cnt/max(true.mem_cnt)
false['retention'] = false.mem_cnt/max(false.mem_cnt)

# 시각화를 위해 데이터 합치기
new_df = pd.concat([true, false])

# 시각화
fig = px.line(data_frame=new_df, x='ord_seq', y='retention', color='is_w1_ord')
fig.update_traces(selector=dict(name='1'), name='첫 구매 이후 1주이내 재구매 고객')
fig.update_traces(selector=dict(name='0'), name='첫 구매 이후 1주이내 미구매 고객')
fig.show()

결론

1주 이내 신규 고객을 재구매 고객으로 만들어야 한다
첫 주문일 이후 고객이 활성화될 가능성을 높이려면 한 달 이내 5번 이상 주문해야 한다 - 기준