[개인] RFM·코호트·이탈 분석을 통한 상위 고객 구매 유지율 하락 문제 정의 및 가설 검증 - 데이터 처리 및 분석①
데이터 출처
Kaggle – E-commerce Behavior Data from Multi-category Store
본 프로젝트에서는 Kaggle에서 제공하는 총 7개월(2019-10 ~ 2020-04)의 로그 데이터를 다운로드하여 사용함
- 압축파일(월별) 약 20GB 수준의 초대용량 데이터
- 단순 DB 적재 시 로딩 및 집계 시간이 매우 오래 걸리는 문제 발생
- 따라서 대규모 데이터 처리 구간은 Apache Spark / SparkSQL로 수행
- 이후 가벼워진 분석 테이블을 PostgreSQL에 적재하여 세부 지표 산출 및 대시보드 연동
Spark → 전처리·집계
PostgreSQL → 상세 분석·지표 계산
Tableau → 시각화 대시보드 구축
지표 산출
1. 전체 기간 고객 세그먼트 분석 (RFM 기반)
전체 기간의 고객을 세그먼트별로 분류함으로써, 각 세그먼트의 특성과 지표를 한눈에 비교 가능함
RFM_base
RFM 분석의 기반이 되는 Recency, Frequency, Monetary 값을 SparkSQL로 산출
- Recency :
(전체 event_time 최대값 + 1) - 고객의 마지막 구매일 - Frequency : 구매 이벤트 수
- Monetary : 총 구매 금액 합계
| user_id | last_order_ts | last_order_date | standard_date | recency | frequency | monetary |
|---|---|---|---|---|---|---|
| 620926350 | 2020-03-08 03:00:18.000 +0900 | 2020-03-08 | 2020-05-02 | 55 | 3 | 395.16 |
| 573823453 | 2020-03-11 16:06:19.000 +0900 | 2020-03-11 | 2020-05-02 | 52 | 7 | 1558.29 |
| 577255943 | 2020-03-07 19:11:51.000 +0900 | 2020-03-07 | 2020-05-02 | 56 | 6 | 3718.63 |
| 574724069 | 2020-02-27 16:25:55.000 +0900 | 2020-02-27 | 2020-05-02 | 65 | 10 | 4859.46 |
| 513391571 | 2020-04-27 02:37:48.000 +0900 | 2020-04-27 | 2020-05-02 | 5 | 14 | 437.91 |
| 597932095 | 2020-03-12 04:57:06.000 +0900 | 2020-03-12 | 2020-05-02 | 51 | 31 | 11259.71 |
| 620715566 | 2020-02-27 16:46:51.000 +0900 | 2020-02-27 | 2020-05-02 | 65 | 1 | 178.10 |
| 527034052 | 2020-03-08 17:18:57.000 +0900 | 2020-03-08 | 2020-05-02 | 55 | 16 | 6961.05 |
RFM_score
ntile(5)를 사용하여 각 고객의 행동 수준을 상대적 점수로 변환
- R : 높을 수록 낮은 점수
- F : 높을 수록 높은 점수
- M : 높을 수록 높은 점수
| user_id | segment | recency | recency_score | frequency | frequency_score | monetary | monetary_score |
|---|---|---|---|---|---|---|---|
| 646832123 | 일반 고객 | 7 | 5 | 1 | 3 | 0.31 | 1 |
| 648343179 | 일반 고객 | 3 | 5 | 1 | 3 | 0.42 | 1 |
| 648016884 | 일반 고객 | 4 | 5 | 1 | 3 | 0.49 | 1 |
| 575831066 | 일반 고객 | 13 | 5 | 1 | 3 | 0.53 | 1 |
| 601133752 | 일반 고객 | 9 | 5 | 1 | 3 | 0.54 | 1 |
| 522283903 | 일반 고객 | 9 | 5 | 1 | 3 | 0.60 | 1 |
| 598230050 | 일반 고객 | 9 | 5 | 1 | 3 | 0.60 | 1 |
| 630749234 | 일반 고객 | 7 | 5 | 1 | 3 | 0.63 | 1 |
RFM_stat
아래 조건으로 RFM 최종 고객 군을 분류
-
VIP 고객 정의: 모든 RFM 지표가 최고 수준인 최상위 핵심 고객 조건: R=5 AND F=5 AND M=5
-
우수 고객 정의: 전반적으로 높은 활동과 매출을 보이는 상위 고객층 조건: R ≥4 AND F ≥4 AND M ≥4 (단, VIP 조건 제외)
-
성장 고객 정의: 구매 빈도와 매출이 중상위이며 성장 잠재력이 높은 고객 조건: R ≥3 AND F ≥3 AND M ≥3 (우수/VIP 미포함)
-
이탈 고위험 고객 정의: 장기간 미재구매 상태로 복귀 가능성이 매우 낮은 고객 조건: R = 1 AND F ≤ 2
-
이탈 예정 고객 정의: 구매 활동이 둔화되며 이탈 신호가 포착된 고객 조건: R = 2 AND F ≤ 2
-
일반 고객 정의: 특정 패턴이 없는 평균적이고 보통 수준의 고객 조건: 위 조건에 해당하지 않는 모든 고객
| segment | cnt | ratio |
|---|---|---|
| 일반 고객 | 921,007 | 44.60% |
| 이탈 고위험 | 263,957 | 12.78% |
| 이탈 예정 | 219,153 | 10.61% |
| 성장 고객 | 353,083 | 17.09% |
| 우수 고객 | 243,268 | 11.78% |
| VIP | 64,431 | 3.12% |
RFM_detail_stat (대시보드에 사용)
세그먼트별 핵심 지표를 산출
- 세그먼트별 인원수
- 총 매출액 / 매출 비중
- ARPU (고객 1인당 매출)
- 평균 구매횟수
- 평균 구매기간
| segment | cnt | monetary | monetary ratio | ARPU | avg_frequency | avg_recency |
|---|---|---|---|---|---|---|
| 이탈 예정 | 219,153 | 59,712,143 | 2.9% | 272.47 | 1.0 | 131.93 |
| 이탈 고위험 | 263,957 | 72,596,473 | 3.5% | 275.03 | 1.0 | 183.17 |
| 일반 고객 | 921,007 | 558,161,275 | 27.1% | 606.03 | 2.37 | 86.66 |
| 성장 고객 | 353,083 | 480,756,138 | 23.4% | 1361.59 | 4.50 | 58.64 |
| VIP | 64,431 | 310,032,350 | 15.1% | 4811.85 | 13.39 | 11.04 |
| 우수 고객 | 243,268 | 576,188,846 | 28.0% | 2368.53 | 7.11 | 33.37 |
RFM_funnel (대시보드에 사용)
세그먼트별로 조회/장바구니/구매 전환 지표를 산출
| segment | View_to_cart_cvr | Cart_to_purchase_cvr | Cur | View_cnt | Cart_cnt | Purchase_cnt |
|---|---|---|---|---|---|---|
| 이탈 예정 | 0.060 | 0.276 | 0.016 | 13,106,567 | 792,315 | 219,153 |
| 이탈 고위험 | 0.047 | 0.381 | 0.018 | 14,615,622 | 692,580 | 263,957 |
| 일반 고객 | 0.069 | 0.418 | 0.029 | 74,666,783 | 5,225,307 | 2,185,058 |
| 성장 고객 | 0.087 | 0.479 | 0.041 | 37,990,846 | 3,312,154 | 1,588,132 |
| VIP | 0.119 | 0.542 | 0.064 | 13,309,204 | 1,592,116 | 863,218 |
| 우수 고객 | 0.100 | 0.494 | 0.049 | 34,857,995 | 3,494,742 | 1,729,306 |
2. 월별 고객 세그먼트 분석 (RFM 기반)
월별 스냅샷을 기준으로 세그먼트를 독립적으로 재분류하여, 시점별 고객 이동(Transition)과 카테고리별 소비 패턴을 함께 파악 가능
new_monthly_rfm
월별 분석을 위해 각 스냅샷(month-end 날짜)을 기준으로 Recency, Frequency, Monetary 값을 SparkSQL로 산출
- Recency : 스냅샷 기준 이전까지의 누적 마지막 구매일
- Frequency : 해당 월의 구매 이벤트 수
- Monetary : 해당 월의 구매 금액 합계
| user_id | snapshot_date | rfm_segment | recency | recency_score | month_freq | frequency_score | month_monetary | monetary_score |
|---|---|---|---|---|---|---|---|---|
| 512741995 | 2019-10-31 | 이탈 고위험 고객 | 26 | 1 | 1 | 1 | 0.88 | 1 |
| 513085966 | 2019-10-31 | 이탈 고위험 고객 | 24 | 1 | 1 | 1 | 0.88 | 1 |
| 514423158 | 2019-10-31 | 이탈 예정 고객 | 20 | 2 | 1 | 2 | 0.88 | 1 |
| 538224876 | 2019-10-31 | 이탈 예정 고객 | 17 | 2 | 1 | 2 | 0.88 | 1 |
| 534066409 | 2019-10-31 | 일반 고객 | 7 | 4 | 1 | 3 | 0.88 | 1 |
| 563893002 | 2019-10-31 | 일반 고객 | 7 | 4 | 1 | 3 | 0.88 | 1 |
| 535243241 | 2019-10-31 | 일반 고객 | 3 | 5 | 1 | 3 | 0.88 | 1 |
| 524539978 | 2019-10-31 | 이탈 고위험 고객 | 26 | 1 | 1 | 1 | 0.90 | 1 |
RFM_detail_stat (대시보드에 사용)
각 세그먼트에 대해 다음 지표를 월별 산출
- 총 매출액 / 매출 비중
- ARPU (고객 1인당 매출)
- 평균 구매 횟수
- 평균 구매 기간
| snapshot_date | rfm_segment | avg_recency | avg_frequency | arpu | total_monetary | monetary_ratio |
|---|---|---|---|---|---|---|
| 2019-10-31 | VIP | 2.72 | 9.39 | 3978.63 | 60224540.21 | 0.2637 |
| 2019-10-31 | 성장 고객 | 8.96 | 2.14 | 715.11 | 46466985.57 | 0.2034 |
| 2019-10-31 | 우수 고객 | 6.29 | 3.84 | 1362.82 | 45428089.09 | 0.1989 |
| 2019-10-31 | 이탈 고위험 고객 | 27.19 | 1.00 | 265.70 | 13882744.37 | 0.0608 |
| 2019-10-31 | 이탈 예정 고객 | 19.94 | 1.00 | 252.51 | 11793493.27 | 0.0516 |
| 2019-10-31 | 일반 고객 | 13.71 | 1.73 | 380.90 | 50601123.17 | 0.2215 |
RFM_detail_funnel (대시보드에 사용)
월별 세그먼트 이동(Transition)을 기반으로 다음 지표를 산출
- 이전 세그먼트 → 현재 세그먼트 이동 인원 수
- 이전/현재 매출 & 매출 차이
- 1인당 매출 증가액 (avg_diff_amount)
- 매출 증가율 (avg_uplift_pct)
| snapshot_month | prev_segment | curr_segment | transition_cnt | total_prev_amount | total_curr_amount | total_diff_amount | avg_diff_amount | avg_uplift_pct |
|---|---|---|---|---|---|---|---|---|
| 2019-12-31 | VIP | VIP | 3027 | 16404175.15 | 23880313.43 | 7476138.28 | 2469.82 | 1.0520 |
| 2020-04-30 | VIP | VIP | 1352 | 8396683.31 | 7502776.27 | -893907.04 | -661.17 | 0.4700 |
| 2020-03-31 | VIP | VIP | 2879 | 26247214.23 | 21965211.70 | -4282002.53 | -1487.32 | 0.3310 |
| 2020-02-29 | VIP | VIP | 3235 | 22094123.28 | 32528399.65 | 10434276.37 | 3225.43 | 1.2719 |
| 2020-01-31 | VIP | VIP | 3065 | 22324567.46 | 22608571.31 | 284003.85 | 92.66 | 0.6896 |
| 2019-11-30 | VIP | VIP | 2506 | 15704030.00 | 16292619.80 | 588589.80 | 234.87 | 0.6842 |
| 2020-02-29 | VIP | 성장 고객 | 2306 | 8786060.02 | 7146411.78 | -1639648.24 | -711.04 | 0.0889 |
| 2020-03-31 | VIP | 성장 고객 | 2412 | 12348772.92 | 5756837.12 | -6591935.80 | -2732.98 | -0.4003 |
RFM_detail_category (대시보드에 사용)
월별로 세그먼트 × 카테고리 조합에 대해 다음 지표를 산출
- 매출/매출비중
- 평균 구매 기간
- 총 구매 횟수
| snapshot_date | category_id | category_code | rfm_segment | avg_recency | sum_frequency | sum_monetary | monetary_ratio |
|---|---|---|---|---|---|---|---|
| 2019-10-31 | 2053013561847841545 | furniture.bedroom.blanket | 이탈 고위험 고객 | 28.0 | 13 | 394.87 | 0.0000009538 |
| 2019-10-31 | 2100064855133258156 | apparel.shoes | 일반 고객 | 9.86 | 19 | 989.88 | 0.0000023910 |
| 2019-10-31 | 2053013558752445019 | 일반 고객 | 12.99 | 486 | 23928.15 | 0.0000577989 | |
| 2019-10-31 | 2053013563307459413 | 성장 고객 | 8.64 | 65 | 3813.46 | 0.0000092115 | |
| 2019-10-31 | 2053013561243861733 | 일반 고객 | 12.40 | 215 | 27356.57 | 0.0000660803 | |
| 2019-10-31 | 2053013558223962683 | 일반 고객 | 12.01 | 346 | 20619.74 | 0.0000498074 | |
| 2019-10-31 | 2090228413959766319 | 이탈 예정 고객 | 18.33 | 3 | 239.10 | 0.0000005776 | |
| 2019-10-31 | 2076715364204413467 | 일반 고객 | 13.47 | 59 | 5537.80 | 0.0000133767 |