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

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

 

캐글(Kaggle) 브라질 E-Commerce 데이터 분석 프로젝트

 

1. 프로젝트 개요

• 데이터셋: Olist Store (브라질 최대 오픈마켓 플랫폼)
• 분석 목적:
  • 테이블 간 관계 파악
  • 주문·상품·리뷰·결제 데이터 연결 구조 이해
  • 주문/매출/배송/지역별 인사이트 도출
• 데이터베이스명: Brazilian
• 구성 테이블:
  • 주문: olist_orders_dataset
  • 주문 상세: olist_order_items_dataset
  • 고객: olist_customers_dataset
  • 상품: olist_products_dataset
  • 셀러: olist_sellers_dataset
  • 결제: olist_order_payments_dataset
  • 리뷰: olist_order_reviews_dataset
  • 번역 테이블: product_category_name_translation

•  

---

 

2.  테이블 구조 및 주요 관계

✅ 주문과 주문 상세 (1:N)

• olist_orders_dataset.order_id = olist_order_items_dataset.order_id
• 한 주문에 여러 상품이 포함될 수 있음.

✅ 고객과 주문 (1:N)

• olist_customers_dataset.customer_id = olist_orders_dataset.customer_id
• 한 고객은 여러 번 주문 가능하지만, 고유 고객수는 customer_unique_id로 계산해야 함.

✅ 상품과 카테고리

• 상품(olist_products_dataset)은 product_category_name_translation을 참조하여 영어 카테고리명을 얻음.

✅ 결제 정보

• order_id + payment_sequential 조합이 고유함.
• 총 결제 금액은 olist_order_items_dataset의 price + freight_value 합이 더 정확함 (일부 payment_value 불일치 확인됨)

✅ 리뷰

• 한 주문에 여러 셀러가 있을 수 있어 리뷰 ID(review_id)는 PK가 아님.
• 리뷰 점수는 1~5점 분포.

---

3. 탐색적 데이터 분석 (EDA)

(1) 기간별 주문 추이

• 데이터는 2017.01~2018.08 기간이 정상적으로 쌓여 있음.
• 이 시점 이후 데이터는 결측 또는 비정상 레코드가 존재

SELECT DATE_FORMAT(order_purchase_timestamp, '%Y-%m') AS YM,
       COUNT(DISTINCT order_id) AS order_count
FROM olist_orders_dataset
GROUP BY 1
ORDER BY 1 ASC;

 

---

(2) 카테고리별 매출액

• 매출 상위 카테고리:
  1. bed_bath_table
  2. health_beauty
  3. sports_leisure
  4. computers_accessories
  5. furniture_decor
• 일부 상품은 카테고리명이 NULL (번역 불가능하거나 미기재).

---

(3) 도시별 주문 및 고객 수

• Sao Paulo 지역이 주문 및 고객 수 1위 (브라질 수도권 집중 현상).
• 지역별 주문 편차가 뚜렷함.

SELECT c.customer_city,
       COUNT(DISTINCT o.order_id) AS order_count,
       COUNT(DISTINCT c.customer_unique_id) AS customer_unique_count
FROM olist_orders_dataset o
LEFT JOIN olist_customers_dataset c ON c.customer_id = o.customer_id
GROUP BY 1
ORDER BY 2 DESC;

 

---

(4) 고객의 재구매율

• 대부분 1회 구매 고객.
• 2회 이상 구매자는 극히 적음 (충성 고객 비율 낮음).

구매횟수 구간	고객 수
1회 구매	절대 다수
2~3회 구매	매우 소수
4회 이상	극히 일부

→ 고객 유지 전략(Customer Retention)이 필요함.

---

(5) 함께 자주 구매되는 카테고리

• GROUP_CONCAT()로 주문 내 여러 상품 카테고리를 묶어 분석.
• 대표 조합 예시:
  • consoles_games + toys
  • housewares + kitchen_appliances
  • health_beauty + perfumery

→ 상품 교차판매(Cross-selling) 전략 설계에 활용 가능.

---

(6) 데이터 누락 확인

• 주문 테이블과 주문 상세 테이블 JOIN 시, 약 775건의 주문 ID가 매칭되지 않음.
• olist_order_items_dataset 누락 데이터로 판단.

---

(7) 평균 배송시간 분석

분석 단위	주요 결과	해석
카테고리별	책(books_general)과 가구(furniture_decor)는 평균 배송일이 가장 길다.	부피가 크거나 물류 경로 복잡
도시별	novo brasil, capinzal do norte 등 지방 도시에서 배송 지연 심함	물류 인프라 취약
지역(State)별	북부 지역 평균 배송일이 남부보다 2~3배 김	거리 및 교통 인프라 영향

SELECT customer_state, AVG(DATEDIFF(order_delivered_customer_date, order_purchase_timestamp)) AS avg_arrived_day
FROM olist_orders_dataset o
INNER JOIN olist_order_items_dataset oi ON o.order_id = oi.order_id
LEFT JOIN olist_customers_dataset c ON c.customer_id = o.customer_id
WHERE arrived_day IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

 

---

(8) 셀러 한 명당 고객 수

• 도심 외곽 지역은 셀러당 고객 수가 매우 높음.
• 공급자 대비 수요가 몰려 있음 → 신규 셀러 유입 기회 존재.

 

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

---

💡 생각 정리

이번 프로젝트는 SQL을 이용해 실제 전자상거래 데이터 구조와 흐름을 깊이 이해한 경험이었다.

특히 인상 깊었던 점은,

• 단순히 SELECT로 데이터를 보는 것이 아니라
  조인, 서브쿼리, WITH문, 집계함수를 종합적으로 활용해
  “데이터의 관계”와 “비즈니스 의미”를 함께 해석할 수 있었다는 것이다.

또한 데이터 누락·중복·불일치(payment_value vs sale_amt) 등 실무에서 흔히 발생하는 데이터 품질 이슈를 직접 확인하면서, 데이터 클린징과 신뢰도 검증의 중요성을 체감했다.

 

마지막으로, 지역별 배송지연이나 재구매율 문제처럼 SQL 결과를 통해 비즈니스 개선 포인트를 도출할 수 있다는 점이 데이터 분석가로서의 핵심 역량임을 느꼈다.

---

🚀 적용점

• GROUP_CONCAT()를 활용해 고객/주문 기반의 조합 분석 자동화
• 배송시간(DATEDIFF) 기반 SLA(Service Level Agreement) 모니터링 쿼리 설계
• WITH문으로 지역·카테고리별 통계 테이블 생성해 대시보드 연결
• 결제정보 불일치 감지용 데이터 검증 SQL 주기적 실행
• 재구매율(Retention)과 배송 만족도(Review) 연동 분석으로 고객 세분화

👉 이번 프로젝트는 “SQL을 활용한 탐색적 데이터 분석(EDA)”의 완벽한 예시였다.
쿼리 한 줄 한 줄이 데이터의 흐름을 설명해주며, 실무형 데이터 분석가의 사고방식을 완성시켜준다.