[TIL] 71일차

QCC6-3

코호트 분석

WITH cohort AS (
	SELECT customer_id
		   ,date_format(min(order_date), '%Y-%m-01') FIRST_ORDER_MONTH
	FROM customer_orders
	GROUP BY 1
), acitve_orders as(
SELECT ch.FIRST_ORDER_MONTH
		,date_format(order_date, '%Y-%m-01') active_month
		,co.CUSTOMER_ID 
FROM customer_orders co
JOIN cohort ch
ON co.CUSTOMER_ID = ch.customer_id
), cohort_counts as(
	SELECT FIRST_ORDER_MONTH 
			,active_month 
			,COUNT(DISTINCT CUSTOMER_ID) user_count
	FROM acitve_orders
	GROUP BY 1, 2
)SELECT first_order_month
		,coalesce(sum(CASE WHEN active_month = first_order_month THEN user_count ELSE 0 END), 0) AS COHORT_USER_COUNT
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 1 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_1_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 2 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_2_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 3 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_3_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 4 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_4_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 5 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_5_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 6 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_6_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 7 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_7_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 8 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_8_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 9 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_9_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 10 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_10_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 11 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_11_MONTH_LATER
		,COALESCE(sum(CASE WHEN active_month = date_add(first_order_month, INTERVAL 12 MONTH) THEN user_count ELSE 0 END), 0) AS USER_COUNT_12_MONTH_LATER
FROM cohort_counts
GROUP BY 1;

고객의 첫 주문 월(FIRST_ORDER_MONTH)을 기준으로, 그 이후 각 월별로 활동 중인 고객 수를 집계하여 **코호트 분석(Cohort Analysis)**을 수행하는 SQL 쿼리입니다. 코호트 분석은 특정 시점(첫 주문) 기준으로 고객의 유지율 및 활동 패턴을 분석하는 데 사용됩니다.

---

쿼리 구성 요소별 상세 설명

---

1. CTE: cohort

WITH cohort AS ( 
	SELECT customer_id ,date_format(min(order_date), '%Y-%m-01') FIRST_ORDER_MONTH 
FROM customer_orders GROUP BY 1 
)

WITH cohort AS ( SELECT customer_id ,date_format(min(order_date), '%Y-%m-01') FIRST_ORDER_MONTH FROM customer_orders GROUP BY 1 )

• 목적: 각 고객의 첫 주문 월(FIRST_ORDER_MONTH)을 계산합니다.
• min(order_date): 고객별 최초 주문 날짜를 가져옵니다.
• date_format(..., '%Y-%m-01'): 날짜를 YYYY-MM-01 형식으로 변환하여 해당 월의 첫날로 정규화합니다.
• 결과: 고객 ID와 첫 주문 월로 구성된 데이터셋을 생성합니다.

---

2. CTE: active_orders

 

acitve_orders as ( SELECT ch.FIRST_ORDER_MONTH ,date_format(order_date, '%Y-%m-01') active_month ,co.CUSTOMER_ID FROM customer_orders co JOIN cohort ch ON co.CUSTOMER_ID = ch.customer_id )

acitve_orders as ( 
	SELECT ch.FIRST_ORDER_MONTH 
			,date_format(order_date, '%Y-%m-01') active_month ,co.CUSTOMER_ID 
FROM customer_orders co 
JOIN cohort ch 
ON co.CUSTOMER_ID = ch.customer_id 
)​

• 목적: 각 고객의 모든 주문 데이터를 첫 주문 월(FIRST_ORDER_MONTH)과 연결합니다.
• JOIN cohort: 고객 ID를 기준으로 cohort CTE와 customer_orders 테이블을 조인합니다.
• date_format(order_date, '%Y-%m-01'): 각 주문이 발생한 월(active_month)을 YYYY-MM-01 형식으로 정규화합니다.
• 결과: 고객의 첫 주문 월과 모든 활동 월(active_month)을 연결한 데이터셋을 생성합니다.

---

3. CTE: cohort_counts

 

cohort_counts as ( SELECT FIRST_ORDER_MONTH ,active_month ,COUNT(DISTINCT CUSTOMER_ID) user_count FROM acitve_orders GROUP BY 1, 2 )

cohort_counts as ( 
	SELECT FIRST_ORDER_MONTH 
    ,active_month 
    ,COUNT(DISTINCT CUSTOMER_ID) user_count 
    FROM acitve_orders GROUP BY 1, 2 
 )​

• 목적: 각 첫 주문 월(FIRST_ORDER_MONTH)과 활동 월(active_month)에 대해 고유 고객 수(user_count)를 계산합니다.
• COUNT(DISTINCT CUSTOMER_ID): 중복되지 않는 고객 수를 집계합니다.
• GROUP BY 1, 2: 첫 주문 월과 활동 월을 기준으로 데이터를 그룹화합니다.
• 결과: 각 코호트의 특정 월별 고객 수를 나타냅니다.

---

4. 최종 SELECT 문

 

SELECT first_order_month ,COALESCE(...) AS COHORT_USER_COUNT ,COALESCE(...) AS USER_COUNT_1_MONTH_LATER ... FROM cohort_counts GROUP BY 1;

SELECT first_order_month ,COALESCE(...) AS COHORT_USER_COUNT 
	,COALESCE(...) AS USER_COUNT_1_MONTH_LATER ... 
FROM cohort_counts GROUP BY 1;​

• 목적: 각 코호트의 첫 주문 월(FIRST_ORDER_MONTH) 기준으로 0개월부터 12개월까지의 월별 활동 고객 수를 계산합니다.
• CASE WHEN active_month = first_order_month THEN user_count ELSE 0 END:
  • 각 코호트의 첫 주문 월(FIRST_ORDER_MONTH)에 활동한 고객 수를 계산합니다.
• date_add(first_order_month, INTERVAL N MONTH):
  • 첫 주문 월 기준으로 N개월 이후의 활동 고객 수를 계산합니다.
• COALESCE(...):
  • NULL 값을 0으로 대체하여 결과를 정리합니다.
• GROUP BY 1:
  • 첫 주문 월(FIRST_ORDER_MONTH)별로 데이터를 그룹화합니다.

---

결과 예시

FIRST_ORDER_MONTHCOHORT_USER_COUNTUSER_COUNT_1_MONTH_LATERUSER_COUNT_2_MONTH_LATER...

2023-01-01	100	80	60	...
2023-02-01	120	90	70	...
2023-03-01	150	100	80	...

---

쿼리의 주요 포인트

1. 코호트 분석
   • 고객의 첫 주문 월(FIRST_ORDER_MONTH)을 기준으로 고객 활동 데이터를 분류합니다.
2. 월별 고객 유지율 확인
   • 각 코호트에서 첫 주문 이후 N개월 차에 활동 중인 고객 수를 확인합니다.
3. 유지율 패턴 분석
   • 고객의 활동이 시간이 지남에 따라 감소하는 경향을 분석할 수 있습니다.

---

활용 가능성

1. 고객 유지율 분석
   • 특정 월의 신규 고객이 얼마나 오랫동안 활동을 지속하는지 파악할 수 있습니다.
2. 마케팅 효과 측정
   • 특정 시점의 마케팅 캠페인이나 프로모션이 고객 유지에 미친 영향을 분석할 수 있습니다.
3. 제품 개선 및 전략 수립
   • 유지율이 낮은 시점을 파악하여 개선 방안을 수립하거나 타겟화된 캠페인을 실행할 수 있습니다.

---

이 쿼리는 고객 행동을 장기적으로 추적하여 비즈니스 전략 수립에 필요한 인사이트를 제공하는 데 매우 유용합니다.