05. 고객 세분화 (Customer Segmentation)
1. 개요 및 시나리오
상황: 모든 고객에게 똑같은 CS 서비스를 제공하다 보니 비용은 늘고 만족도는 제자리입니다. CMO가 제안합니다.
“VIP 고객한테는 더 빨리 답장하고, 이탈할 것 같은 고객한테는 쿠폰이라도 줘야 하지 않을까요?”
하지만 누가 VIP이고 누가 이탈 위기인지 모릅니다. 이번 챕터에서는 RFM 분석 기법을 사용하여 고객 등급을 나누고, 각 그룹에 맞는 전략을 세워봅니다.
2. 데이터 준비
주문 데이터(src_orders)와 주문 상세(src_order_items)가 필요합니다.
CS 티켓 데이터(cs_tickets_dummy)와 연결하여 분석합니다.
BigQuery (SQL)
# BigQuery 연결 설정
from google.cloud import bigquery
client = bigquery.Client()3. 기본 세분화: 티켓 빈도별 고객
가장 쉬운 방법은 “얼마나 자주 문의하는가?”를 보는 것입니다.
❓ 문제 1: 티켓 빈도 그룹 나누기
Q. 고객별 총 티켓 수를 계산하고, 다음 기준으로 그룹을 나누세요.
- 1회: 1회 문의
- 2-3회: 2-3회 문의
- 4회 이상: 다빈도 문의
BigQuery (SQL)
Hint: CASE WHEN을 사용하여 그룹핑하세요.
정답 코드 보기
WITH user_tickets AS (
SELECT user_id, COUNT(*) as ticket_count
FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
GROUP BY user_id
)
SELECT
CASE
WHEN ticket_count = 1 THEN '1회 문의'
WHEN ticket_count BETWEEN 2 AND 3 THEN '2-3회 문의'
ELSE '4회 이상 (다빈도)'
END as frequency_segment,
COUNT(*) as user_count
FROM user_tickets
GROUP BY 1
ORDER BY user_count DESC;4. RFM 분석 (Recency, Frequency, Monetary)
가장 빈번하게 쓰이는 마케팅 세분화 기법입니다.
- Recency: 얼마나 최근에 구매했나?
- Frequency: 얼마나 자주 구매했나?
- Monetary: 얼마나 많이 질렀나?
❓ 문제 2: 고객별 RFM 지표 계산
Q. 각 유저의 Recency(마지막 구매 후 경과일), Frequency(총 주문 수), Monetary(총 구매액)를 계산하세요.
BigQuery (SQL)
Hint: DATE_DIFF와 CURRENT_DATE()를 사용해 경과일을 구하세요.
정답 코드 보기
SELECT
o.user_id,
DATE_DIFF(CURRENT_DATE(), MAX(DATE(o.created_at)), DAY) as recency,
COUNT(DISTINCT o.order_id) as frequency,
ROUND(SUM(oi.sale_price), 2) as monetary
FROM `your-project-id.retail_analytics_us.src_orders` o
JOIN `your-project-id.retail_analytics_us.src_order_items` oi
ON o.order_id = oi.order_id
WHERE o.status NOT IN ('Cancelled', 'Returned')
GROUP BY o.user_id
LIMIT 10;❓ 문제 3: RFM 점수 부여 및 세그먼트 분류
Q. NTILE(5) 함수를 사용해 각 지표를 1-5점으로 나누고, 다음 규칙으로 세그먼트를 정의하세요.
- Champions: R, F, M 모두 4점 이상
- Loyal: R, F, M 모두 3점 이상
- At Risk: R 2점 이하, F 3점 이상 (자주 샀으나 최근 안 옴)
- New: R 4점 이상, F 2점 이하 (최근에 왔으나 빈도 낮음)
- Others: 나머지
BigQuery (SQL)
Hint: CTE를 사용하여 점수를 먼저 계산하고 다시 CASE WHEN으로 분류하세요.
정답 코드 보기
WITH rfm_base AS (
-- (위의 문제 2 쿼리)
SELECT
o.user_id,
DATE_DIFF(CURRENT_DATE(), MAX(DATE(o.created_at)), DAY) as recency,
COUNT(DISTINCT o.order_id) as frequency,
SUM(oi.sale_price) as monetary
FROM `your-project-id.retail_analytics_us.src_orders` o
JOIN `your-project-id.retail_analytics_us.src_order_items` oi ON o.order_id = oi.order_id
WHERE o.status NOT IN ('Cancelled', 'Returned')
GROUP BY o.user_id
),
rfm_scores AS (
SELECT *,
NTILE(5) OVER (ORDER BY recency ASC) as r_score, -- 최근일수록 높은 점수(5)가 아니라 낮은 숫자 1이 좋은 것일 수도 있으나
-- 보통 5점이 좋은 것이므로 ORDER BY recency DESC가 맞습니다.
-- 하지만 SQL NTILE(5) OVER (ORDER BY col) 은 작은 값이 1입니다.
-- Recency: 작을수록(최근) 좋아야 하므로 -> 1등급이 짱?
-- 편의상 내림차순 정렬해서 큰 값이 5점이 되도록 하거나
-- 문제 정의에 따라 다릅니다. 여기서는 '5점 척도'를 씁니다.
-- 수정: 편하게 5가 좋은 점수가 되도록 로직 구성
NTILE(5) OVER (ORDER BY recency DESC) as r_score_raw, -- 오래된게 1, 최근이 5
NTILE(5) OVER (ORDER BY frequency ASC) as f_score, -- 많을수록 5
NTILE(5) OVER (ORDER BY monetary ASC) as m_score -- 많을수록 5
FROM rfm_base
)
SELECT
user_id,
CASE
WHEN r_score_raw >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
WHEN r_score_raw >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Loyal'
WHEN r_score_raw <= 2 AND f_score >= 3 THEN 'At Risk'
WHEN r_score_raw >= 4 AND f_score <= 2 THEN 'New'
ELSE 'Others'
END as segment
FROM rfm_scores;💡 요약 및 인사이트
고객은 모두 다르며, 다르게 대우받길 원합니다.
- Top 20%가 매출의 80%를 만듭니다 (파레토 법칙): Champions를 특별 대우하세요.
- 이탈 징후 감지: ‘At Risk’ 그룹(자주 사다가 뜸해진)에게는 복귀 쿠폰을 보내세요.
- 리소스 최적화: 모든 티켓에 긴급 대응하는 대신, VIP 티켓을 우선 처리하는 시스템을 만들 수 있습니다.
Part 1을 마치며 수고하셨습니다! 지금까지 데이터 분석의 기초(탐색, 지표, 시계열, 세분화)를 다졌습니다. 다음 챕터부터는 조금 더 깊이 있는 **머신러닝(Sentiment Analysis)**과 AI로 넘어갑니다.