03. 반품 패턴 분석 (Returns Analysis)
1. 개요 및 시나리오
상황: 매출은 늘었는데 이익이 늘지 않습니다. 알고 보니 “반품(Returns)” 때문이었습니다. CFO가 심각한 얼굴로 데이터를 가져왔습니다.
“지난달 반품 환불액이 너무 큽니다. 도대체 왜 반품하는 겁니까? 우리 잘못인가요, 고객 변심인가요?”
반품은 이커머스의 ‘필요악’이지만, 데이터를 통해 충분히 관리하고 줄일 수 있습니다. 이번 챕터에서는 반품 사유, 책임 소재(Responsibility), 카테고리별 패턴을 분석하여 반품 방지 전략을 세워봅니다.
2. 데이터 준비
returns_reason_dummy 테이블을 사용합니다.
이 테이블은 주문(order_id), 상품(product_id)과 연결되어 있습니다.
BigQuery (SQL)
# BigQuery 연결 설정
from google.cloud import bigquery
client = bigquery.Client()주요 컬럼
reason_code: 반품 사유 코드 (e.g.,size_issue,defect)responsibility: 책임 소재 (customer,merchant,logistics)return_date: 반품 신청일
3. 반품 사유 분석
먼저, 고객들이 “왜” 반품하는지 알아봅시다.
❓ 문제 1: 반품 사유별 분포
Q. 가장 많이 발생하는 반품 사유 5가지를 찾고, 각 사유의 비율(%)을 계산하세요.
BigQuery (SQL)
Hint: GROUP BY reason_code와 윈도우 함수 SUM(COUNT(*)) OVER()를 사용하세요.
정답 코드 보기
SELECT
reason_code,
COUNT(*) as return_count,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM `your-project-id.retail_analytics_us.returns_reason_dummy`
GROUP BY reason_code
ORDER BY return_count DESC
LIMIT 5;주요 사유 코드:
changed_mind: 단순 변심defect: 제품 불량shipping_delay: 배송 지연size_issue: 사이즈 문제damaged: 배송 중 파손
4. 책임 소재(Responsibility) 분석
반품의 원인이 누구에게 있느냐에 따라 해결책이 다릅니다.
- Customer: 단순 변심, 사이즈 착오 → 상세페이지 개선
- Merchant: 불량, 오배송 → 품질 검수 강화
- Logistics: 파손, 지연 → 물류사 교체 또는 패키징 보완
❓ 문제 2: 책임 소재별 환불 금액
Q. 반품으로 인해 발생한 총 환불 금액을 책임 소재(responsibility)별로 집계하세요.
(주의: 환불 금액은 src_order_items 테이블의 sale_price를 사용해야 합니다.)
BigQuery (SQL)
Hint: order_id와 product_id를 키로 사용하여 src_order_items와 JOIN하세요.
정답 코드 보기
SELECT
r.responsibility,
COUNT(*) as return_count,
ROUND(SUM(oi.sale_price), 2) as total_refund_cost
FROM `your-project-id.retail_analytics_us.returns_reason_dummy` r
JOIN `your-project-id.retail_analytics_us.src_order_items` oi
ON r.order_id = oi.order_id
AND r.product_id = oi.product_id
GROUP BY r.responsibility
ORDER BY total_refund_cost DESC;5. 심화: 카테고리별 반품 패턴
모든 카테고리가 똑같지 않습니다.
속옷(Intimates)은 “단순 변심”이 많고, 코트(Outerwear)는 “사이즈 문제”가 많을 수 있습니다.
❓ 문제 3: 카테고리별 악성 반품 사유 분석
Q. 반품 건수가 가장 많은 상위 5개 카테고리를 찾고, 각 카테고리의 1위 반품 사유를 찾으세요.
BigQuery (SQL)
Hint: APPROX_TOP_COUNT 함수를 쓰면 그룹 내 최빈값을 쉽게 찾을 수 있습니다.
정답 코드 보기
SELECT
p.category,
COUNT(*) as return_count,
-- 각 카테고리에서 가장 많이 등장한 reason_code 1개 추출
APPROX_TOP_COUNT(r.reason_code, 1)[OFFSET(0)].value as top_reason
FROM `your-project-id.retail_analytics_us.returns_reason_dummy` r
JOIN `your-project-id.retail_analytics_us.src_products` p
ON r.product_id = p.product_id
GROUP BY p.category
ORDER BY return_count DESC
LIMIT 5;💡 요약 및 인사이트
반품 데이터를 분석해보니 놀라운 사실들을 발견했습니다.
- Top 반품 사유: 단순 변심(
changed_mind)과 제품 불량(defect)이 비슷하게 높습니다. - 책임 소재: 고객 책임만큼이나 **물류(Logistics)**와 판매자(Merchant) 책임으로 인한 반품액이 큽니다.
- 배송 지연/파손만 잡아도 전체 반품 비용의 30%를 줄일 수 있습니다.
- 카테고리 특성:
Jeans(청바지): 사이즈 이슈가 많음 → 사이즈 가이드 개선 필요Intimates(속옷): 단순 변심 많음 → 위생 관련 반품 정책 검토 필요
다음 단계: 고객도 만족도도 알았고, 반품 원인도 알았습니다. 그렇다면 “이 모든 지표들은 시간이 지나면서 어떻게 변하고 있을까요?” 다음 챕터에서는 시계열(Time Series) 데이터 분석을 통해 트렌드를 파악해봅니다.