가족이 4년째 판매하는 귤 직거래 주문 기록(2022–2025)을 BigQuery SQL로 분석. 농산물이라 물량은 한정되지만 다 팔림. 사업이 망하지 않으려면 한정된 물량을 누구에게 먼저 줄지가 핵심이라 리텐션 분석으로 세그먼트를 나누고 CRM 액션 매트릭스까지 설계함.
역할: 개인 프로젝트, 문제 정의부터 CRM 설계까지
분석 결과 및 CRM 설계 · 슬라이드 6페이지 (PDF)
원본은 4개년 손 엑셀. 연도당 시트 5개(판매자 3명 + 선물 + 택배비) 중 판매 시트 3개만 사용. 익명화 후 최종 3,981건 주문, 2,117명 고객. 데이터는 data/orders_anonymized.csv.
| 컬럼 | 설명 |
|---|---|
| season | 시즌(연도) |
| order_date / ship_date | 주문일 / 발송일 |
| seller | 판매한 가족 구성원 (익명화된 A/B/C) |
| customer_id | 익명 고객 ID |
| sido / sigungu | 배송지 시도/시군구 |
| qty_10kg / qty_5kg | 10kg/5kg 박스 수량 |
| product | 품명 |
| memo | 배송 관련 메모 |
정제 시 발견
정제 처리
익명화: 이름·전화번호·상세주소 제거. 판매자 익명 코드(seller_A/B/C)와 시도/시군구만 남김.
세 가설을 순서대로 검증. 자세한 분석 흐름과 CRM 설계는 슬라이드 참고.
| 가설 | 결과 | 강도 |
|---|---|---|
| H1 재구매 시 수량 확대는 리텐션 신호다 | keep_single 53.0% / expand_to_multi 59.3% (+6.3%p) | 방향은 맞으나 표본 54명, 격차 작음 → 약함 |
| H2 판매자별 재구매율 차이는 판매자보다 고객군 특성 | 원본으로는 seller_B 우세. 원본이 코호트 구성으로 왜곡될 가능성 → 세그먼트 통제 접근. 통제 후 방향이 코호트마다 갈림 (2024 multi에서 A가 B를 22.9%p 앞섬) | 부분 지지. 판매자 자체 요인은 단정 어려움 |
| H3 누적 시즌 수가 다음 시즌 재구매를 예측한다 | 신규 23.8% / 2시즌 63.0% / 3시즌 78.8% | 신규 대비 3.3배, 강함 |
→ CRM 지렛대는 H3 기반 세그먼트(충성 263명 / 재구매 299명 / 신규 1,555명)에 시즌 주기 액션 매트릭스 연결. 세그먼트 정의와 흉년·풍년 시나리오별 액션은 슬라이드 5–6페이지.
가설 순서대로 4개.
01_retention_by_size_change.sql: H1 두 번째 시즌 수량 변화와 이후 재구매율02_customer_profile_by_seller.sql: H2 판매자별 고객군 프로필03_repurchase_by_seller_segment.sql: H2 세그먼트 통제 후 판매자별 재구매율04_retention_by_season_count.sql: H3 누적 시즌 수별 다음 시즌 재구매율원칙
MAX(CASE WHEN season = first_season THEN seller END)로 첫 구매 시점 값을 대표값으로 처리 (여러 판매자에게 산 고객 38명 확인 후 적용)BigQuery, SQL. 정제·익명화는 AI 활용.