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

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

 

RFM 고객 세분화 및 지역별 배송 분석

 

1. 지역별 배송 분석

 

(1) 셀러 1인당 고객 수

• 셀러 한 명당 고객 수가 높은 지역: 'PA', 'MA', 'PI', 'MT', 'PE', 'BA', 'SE'
• 셀러가 거의 없는 지역: 'AL', 'AP', 'RR', 'TO'

SELECT c.customer_state, customer_cnt, seller_cnt,
       customer_cnt / seller_cnt AS number_customers_per_seller
FROM (
  SELECT customer_state, COUNT(DISTINCT customer_unique_id) AS customer_cnt
  FROM olist_customers_dataset GROUP BY 1
) c
LEFT JOIN (
  SELECT seller_state, COUNT(DISTINCT seller_id) AS seller_cnt
  FROM olist_sellers_dataset GROUP BY 1
) s ON c.customer_state = s.seller_state
ORDER BY 4 DESC;

 

→ 북동부/북부 지역(‘PA’, ‘MA’, ‘PI’ 등)은 고객 대비 셀러 수가 적어 공급 과밀 상태.

---

 

(2) 지역별 평균 배송일

• 평균 배송일이 가장 긴 지역: ‘PA’, ‘MA’, ‘PI’, ‘MT’, ‘PE’, ‘BA’, ‘SE’, ‘AL’, ‘AP’, ‘RR’, ‘TO’
• 전체 주문의 66.5%는 ‘SP’, ‘MG’, ‘RJ’ (남동부 3대 도시권) 에 집중.

 

SELECT customer_state, AVG(DATEDIFF(order_delivered_customer_date, order_purchase_timestamp)) AS avg_arrived_day
FROM olist_orders_dataset AS o
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
WHERE arrived_day IS NOT NULL
GROUP BY 1 ORDER BY 2 DESC;

 

---

 

(3) 평균 배송시간 추이

기간	평균 배송일	변화율	해석
2017.11~2018.03	높음	배송 지연	물류 병목 현상
2018.04 이후	급감	개선	배송 프로세스 최적화 또는 물류 정책 변경 추정

💡 데이터상 원인은 명확히 드러나지 않지만,
2018년 4월 이후 평균 배송일이 급격히 단축, 물류 효율 개선이 이뤄진 것으로 추정

---

 

 

(4) 상품 무게와 배송일 상관관계

 

SELECT SEG_YN, AVG(arrived_day), AVG(product_weight_g)
FROM DELIVERY_BY_STATE
WHERE arrived_day IS NOT NULL
AND customer_state NOT IN ('SP','MG','RJ')
GROUP BY 1;

 

→ 상품 무게가 상대적으로 무겁다고 해서 배송일이 비례해 늘어나지는 않았음.
→ 배송지(지역) 요인이 무게보다 더 큰 영향을 미침.

---

2. RFM 고객 세분화 연계

(1) 특정 지역 고객 추출

배송 지연 지역(SEG_YN='Y')의 고객만 추출

 

CREATE TABLE DELIVERY_BY_STATE_SEG_Y AS (
SELECT DISTINCT customer_unique_id
FROM DELIVERY_BY_STATE
WHERE SEG_YN = 'Y'
);

---

 

(2) RFM 점수대별 고객 분포

RFM 점수	전체 고객 수	특정 지역 고객 수
3~5점	낮음	낮음
6~8점	중간	중간
9~12점	높음	높음

→ RFM 점수가 높을수록 (‘우수고객’) 배송 지연 지역 비중도 높음.
즉, 충성 고객이 오히려 배송이 느린 지역에 집중되어 있음

---

 

(3) RFM 점수대별 매출 기여도

 

SELECT RFM_SCORE,
       SUM(monetary) AS total_sales,
       SUM(CASE WHEN DELIVERY.customer_unique_id IS NOT NULL THEN monetary END) AS sales_SEG_Y
FROM RFM_LISTS_SCORE AS RFM
LEFT JOIN DELIVERY_BY_STATE_SEG_Y AS DELIVERY
  ON RFM.customer_unique_id = DELIVERY.customer_unique_id
GROUP BY 1 ORDER BY 1;

 

→ 특정 지역(SEG_YN='Y') 고객이 전체 매출의 약 30% 이상 기여.

---

 

(4) 평균 리뷰 평점 비교

SELECT RFM_score,
       AVG(AVG_review_score) AS avg_review_score,
       AVG(CASE WHEN DELIVERY.customer_unique_id IS NOT NULL THEN AVG_review_score END) AS avg_review_score_SEG_Y
FROM RFM_LISTS_SCORE AS RFM
LEFT JOIN DELIVERY_BY_STATE_SEG_Y AS DELIVERY
  ON RFM.customer_unique_id = DELIVERY.customer_unique_id
LEFT JOIN review
  ON RFM.customer_unique_id = review.customer_unique_id
GROUP BY 1;

 

→ 배송이 느린 지역 고객의 평균 리뷰 점수가 낮게 나타남, 배송 품질과 고객 만족도가 직결된다는 것을 보여줌.

---

3. 지역별 상품 추천 (빠른 배송 카테고리 Top 1)

각 지역별 평균 배송일이 가장 짧은 상품 카테고리를 추출해,
해당 지역의 고객에게 추천할 수 있는 로직 구현.

 

WITH BASE AS (
  SELECT customer_state, product_category_name, AVG(arrived_day) AS avg_arrived_day
  FROM DELIVERY_BY_STATE
  WHERE arrived_day IS NOT NULL
  GROUP BY 1,2
),
BASE_RK AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY customer_state ORDER BY avg_arrived_day ASC) RK
  FROM BASE
)
SELECT * FROM BASE_RK WHERE RK = 1;

 

→ 각 지역별로 배송이 가장 빠른 카테고리 TOP 1이 추출되어, 배송만족도 기반 상품추천 시스템으로 확장 가능.

 

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

---

💡 생각 정리

이 프로젝트는 단순한 RFM 고객 세분화에서 한 단계 더 나아가, 배송 품질(Delivery Efficiency) 이라는 물류 데이터를 결합해
고객 만족도 개선 방안을 제시한 점이 인상 깊었다.

 

특히 분석 과정에서 다음 두 가지 사실을 명확히 확인할 수 있었다:
1. RFM 점수가 높은 충성 고객이 배송이 느린 지역에 집중되어 있다.
2. 배송 품질이 낮은 지역의 평균 리뷰 점수도 낮게 나타난다.

 

즉, 고객 가치와 배송 경험 사이의 비대칭성이 존재하며, 이는 기업이 물류 역량을 강화해야 할 핵심 지표로 활용될 수 있다.

---

🚀 적용점

• 배송 성과 지표 + RFM Score 결합 대시보드 구축 (고객·물류 통합 관리)
• 배송 지연 지역별 빠른 배송 카테고리 추천 시스템 운영
• 리뷰 점수, 배송일, RFM Score 상관관계 기반 고객 만족도 예측 모델링
• RFM 상위 고객 중 배송이 느린 지역을 우선 개선 타깃으로 설정
• 배송지연 지역 고객 대상 보상/프로모션 캠페인 설계

👉 이번 분석은 SQL로 고객 데이터와 물류 데이터를 통합적으로 해석한 사례로, 데이터 기반 고객관리(CRM)와 공급망(SCM)의 교차지점을 명확히 보여준다.