제로베이스 데이터사이언스 스쿨 - Part.02 데이터 분석을 위한 SQL-12

 11. 캐글 데이터 분석 프로젝트

 

SQL로 구현하는 RFM 고객 세분화 분석

 

1. RFM 정의

지표	의미	계산 기준
Recency (최근성)	고객의 마지막 구매일로부터 경과한 일수	DATEDIFF('기준일자', MAX(order_purchase_timestamp))
Frequency (빈도)	특정 기간 내 고객의 총 주문 횟수	COUNT(DISTINCT order_id)
Monetary (금액)	고객의 총 구매 금액(상품+배송비)	SUM(price + freight_value)

💡 이번 분석에서는 기준일자 '2018-10-17'을 사용하여 Recency 계산

 

---

 

2. RFM 테이블 생성

olist_orders_dataset, olist_order_items_dataset, olist_customers_dataset을 조인하여 고객별 RFM 데이터를 생성한다.

 

CREATE TABLE RFM_LISTS AS (
SELECT  c.customer_unique_id,
        DATEDIFF('2018-10-17', MAX(order_purchase_timestamp)) AS recency,
        COUNT(DISTINCT o.order_id) AS frequency,
        SUM(IFNULL(oi.price,0)) + SUM(IFNULL(oi.freight_value,0)) AS monetary
  FROM  olist_orders_dataset AS o
  INNER JOIN olist_order_items_dataset AS oi ON o.order_id = oi.order_id
  LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
 GROUP BY 1
);

 

이로써 고객 95,420명에 대한 R/F/M 지표가 계산된다.

---

 

3. 5분위(Quintile) 구간화

NTILE(5) 윈도우 함수를 사용해 각 R/F/M 지표를 5개의 분위로 나눈다.
이 과정은 고객을 상대적 기준으로 그룹화하는 데 사용된다.

SELECT *,  
       NTILE(5) OVER (ORDER BY recency DESC) AS recency_segment,
       NTILE(5) OVER (ORDER BY frequency ASC) AS frequency_segment,
       NTILE(5) OVER (ORDER BY monetary ASC) AS monetary_segment
FROM RFM_LISTS;

• Recency는 높을수록(최근 구매일이 멀수록) 낮은 점수,
• Frequency/Monetary는 높을수록 높은 점수를 받게 된다.

---

4. 세그먼트 통계 요약

각 지표별 5분위별 구간을 UNION ALL로 합쳐 분석 테이블로 저장한다.

CREATE TABLE RFM_LISTS_SEGMENT AS (
WITH NTILE_BASE AS ( ... )
SELECT * FROM recency_segment
UNION ALL SELECT * FROM frequency_segment
UNION ALL SELECT * FROM monetary_segment
);

 

이 테이블은 각 구간별 최소값, 최대값, 고객 수를 포함해
지표별 분포를 한눈에 파악할 수 있다.

 

---

5. RFM 점수 계산

Recency, Frequency, Monetary에 점수를 부여하여
총합(RFM_score)을 계산하고, 이를 기반으로 고객 등급을 나눈다.

CREATE TABLE RFM_LISTS_SCORE AS (
WITH BASE AS (
SELECT *,
       CASE WHEN recency >= 433 THEN 1
            WHEN recency >= 317 THEN 2
            WHEN recency >= 226 THEN 3
            WHEN recency >= 142 THEN 4
            ELSE 5 END AS recency_score,
       1 AS frequency_score,  -- (테스트용 단일값)
       CASE WHEN monetary >= 209 THEN 5
            WHEN monetary >= 133 THEN 4
            WHEN monetary >= 87  THEN 3
            WHEN monetary >= 55  THEN 2
            ELSE 1 END AS monetary_score
FROM RFM_LISTS
)
SELECT *,
       recency_score + frequency_score + monetary_score AS RFM_score
FROM BASE
);

 

---

6. 결과 예시

customer_unique_id	recency	frequency	monetary	recency_score	monetary_score	RFM_score
0000366f3b9a7992...	160	1	141.9	4	3	8
...	...	...	...	...	...	...

RFM_score가 높을수록 우수고객(충성고객), 낮을수록 이탈위험고객으로 분류된다.

 

 

* 이 글은 제로베이스 데이터사이언스 파트타임 스쿨의 강의 자료 일부를 발췌하여 작성되었습니다.

---

 

💡 생각 정리

이번 실습은 RFM 분석 로직을 SQL 레벨에서 완전히 구현한 케이스였다.
Python이나 BI툴을 쓰지 않고도, 오직 SQL 문법만으로 데이터 집계 → 지표 계산 → 구간 분할 → 점수화 → 고객 세분화까지 완결된 파이프라인을 구축할 수 있었다는 점이 인상 깊었다.

 

특히 NTILE() 윈도우 함수를 활용한 분위 구간화는 데이터 과학의 통계적 사고와 SQL의 집계 기능을 결합한 강력한 접근 방식이었다.

 

또한 고객을 단순 조회 대상이 아니라, “최근성–빈도–금액이라는 행동 패턴으로 정의된 개체”로 바라보게 되면서
SQL이 단순한 데이터 질의 언어가 아니라, 고객 인사이트 도출 도구로 확장될 수 있음을 실감했다.

---

🚀 적용점

• RFM_LISTS 구조를 BI 대시보드용 View로 변환해 자동 갱신되도록 설계
• NTILE() 대신 PERCENT_RANK()를 활용해 상대적 고객 지수(Percentile 기반 점수)로 확장
• Frequency를 실제 주문 수 외에 상품 개수(QTY) 기준으로 변경해 다양도 분석 수행
• RFM_score별 고객 비율을 시각화하여 고객 분포 피라미드 차트 작성
• 향후 RFM_score와 리뷰 평점, 지역, 결제방식을 조합해 다차원 세분화 모델 구축

👉 이번 RFM 세분화 실습은 SQL을 데이터 분석에서 전략적 마케팅 도구로 확장하는 결정적 단계였다.
“고객을 숫자가 아닌 행동 데이터로 이해하는 것”이 진정한 데이터 기반 CRM의 출발점이다.