mysql 실무 적용 코딩 (월별 리텐션 분석)

상황 : oo님 우리 회사 2025년 1~5월까지 월별 리텐션 분석 자료 부탁해요..asap

---

 

1단계 설계

• 리텐션 비중 : 월 방문자 수/첫 월 방문자 수 
• 단계
  • 고객별 첫 방문 월
  • 고객별 월 데이터 셋팅
  • 첫 월, 기준 월, 월 간 gap, 첫 월 유저 수, 기준 월 유저 수, 리텐션 비율

 

sql 쿼리 작성

-- inner join 
with cust_first_month as (
    --고객별 첫 방문 월
    select cust_id
    	, min(date_format(order_date, '%Y-%m')) first_ym 
    from orders
    group by cust_id
)
, cust_orders as (
	--고객별 월 데이터 셋팅
	select cust_id
    	, date_format(order_date, '%Y-%m') ym
	from orders
)
--첫 월, 기준 월, 월 간 gap, 첫 월 유저 수, 기준 월 유저 수, 리텐션 비율
select cm.first_month, co.ym
	, timestampdiff(month , str_to_date(cm.first_month, '%Y-%m'), str_to_date(co.ym, '%Y-%m')) month_gap
	, count(distinct cm.cust_id) first_cnt
    , count(distinct co.cust_id) reten_cnt
    , first_cnt/reten_cnt*100 retention_rate
from cust_first_month cm
inner join cust_orders co
	on cm.cust_id = co.cust_id
group by cm.first_month, co.ym
order by first_month, month_gap

/*
select *
from retention_tmp
where first_month = '2025-01' and month_gap <=6
*/
;
;

 

---

DATE_FORMAT(order_date, '%Y-%m')의 결과

• 변환 결과 문자열 (VARCHAR),  DATETIME이 X
• 결과: '2025-01' (문자열)