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

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

 

SQL을 활용한 상품 카테고리별 리뷰·배송 분석

 

1. 카테고리별 매출 및 리뷰 점수 분석

 

SELECT pct.product_category_name,
       SUM(IFNULL(oi.price,0)) + SUM(IFNULL(oi.freight_value,0)) AS sale_amt,
       COUNT(DISTINCT o.order_id) AS order_cnt,
       COUNT(DISTINCT customer_unique_id) AS customer_cnt,
       AVG(review_score) AS review_score_avg
FROM olist_orders_dataset AS o
INNER JOIN olist_order_items_dataset AS oi ON o.order_id = oi.order_id
LEFT JOIN olist_products_dataset AS p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation AS pct ON p.product_category_name = pct.product_category_name
LEFT JOIN olist_order_reviews_dataset AS r ON r.order_id = o.order_id
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
WHERE pct.product_category_name IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

 

결과 요약:

• 상위 매출 카테고리: beleza_saude, esporte_lazer, informatica_acessorios, moveis_decoracao, cama_mesa_banho
• 리뷰 평점이 낮은 주요 카테고리:
  • cama_mesa_banho (침구/인테리어)
  • moveis_escritorio (사무용 가구)

→ 두 카테고리는 공통적으로 배송 기간이 길고 리뷰 점수가 낮은 경향을 보임.

---

2. 특정 카테고리(가구·침구) 월별 상세 분석

SELECT pct.product_category_name,
       DATE_FORMAT(order_purchase_timestamp, '%Y-%m') AS YM,
       SUM(IFNULL(oi.price,0)) + SUM(IFNULL(oi.freight_value,0)) AS sale_amt,
       COUNT(DISTINCT o.order_id) AS order_cnt,
       COUNT(DISTINCT customer_unique_id) AS customer_cnt,
       COUNT(DISTINCT seller_id) AS seller_cnt,
       AVG(review_score) AS review_score_avg,
       AVG(DATEDIFF(order_delivered_customer_date, order_purchase_timestamp)) AS arrived_day_avg
FROM olist_orders_dataset AS o
INNER JOIN olist_order_items_dataset AS oi ON o.order_id = oi.order_id
LEFT JOIN olist_products_dataset AS p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation AS pct ON p.product_category_name = pct.product_category_name
LEFT JOIN olist_order_reviews_dataset AS r ON r.order_id = o.order_id
LEFT JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
WHERE pct.product_category_name IN ('moveis_escritorio', 'cama_mesa_banho')
GROUP BY 1,2
ORDER BY 1,2 ASC;

 

결과 분석:

• 2017년 11월 ~ 2018년 3월: 평균 배송일 상승, 리뷰 점수 하락
• 2018년 4월 이후: 배송일 급감, 리뷰 점수 상승 추세
• 두 카테고리 모두 공통적으로 배송시간 개선에 따른 평점 상승 효과 확인

---

3. 리뷰 응답시간 분석

 

SELECT DATE_FORMAT(order_purchase_timestamp, '%Y-%m') AS YM,
       AVG(DATEDIFF(review_answer_timestamp, review_creation_date)) AS review_answer_day_avg
FROM olist_orders_dataset AS o
INNER JOIN olist_order_items_dataset AS oi ON o.order_id = oi.order_id
LEFT JOIN olist_products_dataset AS p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation AS pct ON p.product_category_name = pct.product_category_name
LEFT JOIN olist_order_reviews_dataset AS r ON r.order_id = o.order_id
WHERE pct.product_category_name IN ('moveis_escritorio', 'cama_mesa_banho')
GROUP BY 1
ORDER BY 1 ASC;

 

→ 리뷰 응답까지의 평균 소요시간은 카테고리 간 큰 차이 없음
→ 즉, 리뷰 점수 하락의 원인은 배송 지연, 응답 품질과는 무관.

---

4. 지역별 주문 비중 분석

 

SELECT product_category_name, SEG_YN, COUNT(DISTINCT order_id) AS ORDER_NO_CNT
FROM DELIVERY_BY_STATE
WHERE product_category_name IN ('moveis_escritorio', 'cama_mesa_banho')
GROUP BY 1,2;

 

결과 해석:

• 두 카테고리 모두 배송지연 지역(SEG_YN='Y') 주문 비중 낮음
• 즉, 리뷰 하락은 특정 지역 문제라기보다 상품 자체의 배송 특성(부피, 설치 필요 등) 이 원인.

---

5. 종합 결론

구분	주요 결과
평균 리뷰 점수	cama_mesa_banho, moveis_escritorio 낮음
배송시간 추이	2018.04 이후 급격히 단축
리뷰 응답시간	카테고리 간 큰 차이 없음
지역별 비중	특정 지역 편중 현상 없음
핵심 요인	① 배송 지연이 리뷰 점수 하락의 주원인 ② 2018년 이후 물류 인프라 개선으로 회복 추세

 

📰 배경 참고:
2018년 이후 브라질 내 물류 인프라 투자 확대,
중국 자본의 전자상거래 진출, 정부 주도의 물류 시스템 현대화 정책으로
평균 배송일이 2018년 4월 이후 급격히 단축된 현상이 반영된 것으로 보인다

 

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

---

 

💡 생각 정리

이번 분석에서는 단순한 매출 집계가 아니라, 리뷰 평점 하락의 원인을 배송 데이터와 시계열적으로 추적한 점이 흥미로웠다.

특히 cama_mesa_banho와 moveis_escritorio 카테고리처럼 물리적으로 부피가 크고 배송 부담이 큰 제품군의 경우,
리뷰 점수 하락이 단순한 품질 이슈가 아니라 물류 효율성의 문제일 수 있음을 보여줬다.

또한 SQL만으로 시계열 비교, 지역별 분석, 응답 시간까지 통합 분석이 가능하다는 점에서 SQL의 데이터 해석 도구로서의 확장성을 실감했다.

---

🚀 적용점

• 카테고리별 평균 배송일을 실시간 KPI 모니터링 지표로 설정
• 리뷰 점수가 낮은 상품군에 배송 리드타임 개선 전략 우선 적용
• 배송 지연 구간별(지역별) 고객 불만 자동 탐지 시스템 구축
• 리뷰 점수 변동과 배송시간의 상관계수 추적 자동화 (예: corr(review_score, arrived_day))
• 향후 BI 대시보드에 “배송 리스크 카테고리 Top5” 시각화하여 주기적 점검

👉 이번 프로젝트는 리뷰 데이터와 배송 데이터를 통합한 실무형 품질 분석 사례로,
고객 경험(CX) 개선을 위한 데이터 기반 접근법의 좋은 예시였다.