[Olist 이커머스 분석 #3] 분석용 데이터 마트 생성

🎯 데이터 마트 생성

매번 5개 테이블을 JOIN하는 복잡한 쿼리를 짜는 건 비효율적이고 실수도 잦습니다. 이번 글에서는 RFM · 배송 · 리뷰 데이터마트 3종을 설계하고 생성한 과정을 정리했습니다.

🛠️ 사용 도구: MySQL 8.x + MySQL Workbench 📁 데이터: Olist Brazilian E-Commerce (9개 테이블, 약 10만 주문)

---

🗂️ 데이터마트란?

"분석 목적에 맞게 미리 가공해둔 전용 테이블"

구분 원본 테이블 데이터마트

구조	정규화된 9개 테이블	분석 단위로 평탄화
사용	매번 JOIN 필요	SELECT만으로 바로 사용
속도	느림	빠름
관리	자동	수동 갱신

---

📐 마트 설계 원칙

원칙 내용

분석 단위 명확히	RFM은 고객 단위, 배송·리뷰는 주문 단위
필터 조건 고정	WHERE order_status = 'delivered' 로 분석 범위 일관성 유지
파생 컬럼 미리 계산	is_delayed, delay_days, review_sentiment 등
이름 규칙 통일	mart_ 접두어로 원본 테이블과 구분

---

📦 MART 1. RFM 마트

RFM은 이커머스 고객 분석의 기본 프레임워크입니다.

지표 의미 해석

Recency	마지막 구매 후 경과일	낮을수록 최근 구매
Frequency	총 구매 횟수	높을수록 단골
Monetary	총 결제 금액	높을수록 VIP

CREATE TABLE mart_rfm AS
SELECT
    c.customer_unique_id,
    DATEDIFF(
        (SELECT MAX(order_purchase_timestamp) FROM orders),
        MAX(o.order_purchase_timestamp)
    ) AS recency,
    COUNT(DISTINCT o.order_id)     AS frequency,
    ROUND(SUM(p.payment_value), 2) AS monetary
FROM customers c
JOIN orders o       ON c.customer_id = o.customer_id
JOIN order_payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered'
GROUP BY c.customer_unique_id;

적재 결과: 93,357행 ✅

---

🚚 MART 2. 배송 마트

배송 SLA 분석과 지역별 지연율 분석의 기반이 되는 마트입니다.

CREATE TABLE mart_delivery AS
SELECT
    o.order_id,
    c.customer_unique_id,
    c.customer_state,
    s.seller_id,
    s.seller_state,
    o.order_purchase_timestamp,
    o.order_delivered_customer_date,
    o.order_estimated_delivery_date,
    DATEDIFF(
        o.order_delivered_customer_date,
        o.order_purchase_timestamp
    ) AS actual_delivery_days,
    DATEDIFF(
        o.order_estimated_delivery_date,
        o.order_purchase_timestamp
    ) AS estimated_delivery_days,
    CASE
        WHEN o.order_delivered_customer_date > o.order_estimated_delivery_date
        THEN 1 ELSE 0
    END AS is_delayed,
    DATEDIFF(
        o.order_delivered_customer_date,
        o.order_estimated_delivery_date
    ) AS delay_days
FROM orders o
JOIN customers c  ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN sellers s    ON oi.seller_id = s.seller_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL
  AND YEAR(o.order_delivered_customer_date) > 1
  AND YEAR(o.order_estimated_delivery_date) > 1;

적재 결과: 110,189행 ✅

💡 orders JOIN order_items 구조라 한 주문에 상품이 여러 개면 행이 늘어납니다. 배송 분석 시 COUNT(DISTINCT order_id)로 집계하면 됩니다.

---

⭐ MART 3. 리뷰 마트

리뷰 점수와 배송 지연의 관계를 분석하기 위한 마트입니다.

CREATE TABLE mart_review AS
SELECT
    r.order_id,
    r.review_score,
    r.review_creation_date,
    o.order_purchase_timestamp,
    o.order_delivered_customer_date,
    o.order_estimated_delivery_date,
    c.customer_state,
    CASE
        WHEN o.order_delivered_customer_date > o.order_estimated_delivery_date
        THEN 1 ELSE 0
    END AS is_delayed,
    DATEDIFF(
        o.order_delivered_customer_date,
        o.order_estimated_delivery_date
    ) AS delay_days,
    CASE
        WHEN r.review_score >= 4 THEN 'Positive'
        WHEN r.review_score = 3  THEN 'Neutral'
        ELSE 'Negative'
    END AS review_sentiment
FROM order_reviews r
JOIN orders o    ON r.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL
  AND YEAR(o.order_delivered_customer_date) > 1
  AND YEAR(o.order_estimated_delivery_date) > 1;

적재 결과: 96,353행 ✅

---

✅ 마트 검증 쿼리

SELECT 'mart_rfm'      AS mart, COUNT(*) AS row_cnt FROM mart_rfm
UNION ALL
SELECT 'mart_delivery' AS mart, COUNT(*) AS row_cnt FROM mart_delivery
UNION ALL
SELECT 'mart_review'   AS mart, COUNT(*) AS row_cnt FROM mart_review;

마트 행 수

mart_rfm	93,357
mart_delivery	110,189
mart_review	96,353

---

🚨 시행착오 & 트러블슈팅

❌ 오류 1. Error Code 1292 — 잘못된 DATETIME 값

Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00'

원인: 일부 행에 '0000-00-00 00:00:00' 더미 날짜값이 존재 첫 시도: != '0000-00-00 00:00:00' 조건 추가 → Error 1525 발생

Error Code: 1525. Incorrect DATETIME value: '0000-00-00 00:00:00'

MySQL strict mode에서 비교 자체를 거부하는 것

해결: YEAR() 함수로 우회

AND YEAR(o.order_delivered_customer_date) > 1
AND YEAR(o.order_estimated_delivery_date) > 1

---

❌ 오류 2. Error Code 1064 — rows 예약어 충돌

Error Code: 1064. You have an error in your SQL syntax
... right syntax to use near 'rows FROM mart_rfm'

원인: rows가 MySQL 예약어라 컬럼 별칭으로 사용 불가 해결: row_cnt로 변경

-- ❌ 에러
SELECT COUNT(*) AS rows FROM mart_rfm

-- ✅ 해결
SELECT COUNT(*) AS row_cnt FROM mart_rfm

---

⚠️ 과제 실수 — 비율 계산 함정

상황: 재구매 고객 비율을 구하는데 WHERE frequency >= 2 필터 후 비율 계산 문제: 분모가 이미 재구매 고객만 남아 전부 100% 출력

-- ❌ 잘못된 쿼리 (분모 왜곡)
SELECT ...
FROM mart_rfm
WHERE frequency >= 2   -- 이미 필터링된 상태에서 비율 계산 → 100%
GROUP BY frequency;

-- ✅ 올바른 쿼리 (전체 기준 비율)
SELECT
    COUNT(*) AS total_customers,
    SUM(CASE WHEN frequency >= 2 THEN 1 ELSE 0 END) AS repeat_customers,
    ROUND(SUM(CASE WHEN frequency >= 2 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS repeat_pct
FROM mart_rfm;

교훈: 비율 계산 시 분모가 무엇인지 항상 먼저 정의하기

---

⚠️ 과제 실수 — ORDER BY 없는 LIMIT

-- ❌ 정렬 없는 LIMIT = 의미 없는 TOP N
SELECT seller_state, AVG(actual_delivery_days)
FROM mart_delivery
GROUP BY seller_state
LIMIT 5;

-- ✅ ORDER BY 후 LIMIT
SELECT seller_state, ROUND(AVG(actual_delivery_days), 1) AS avg_delivery
FROM mart_delivery
GROUP BY seller_state
ORDER BY avg_delivery DESC
LIMIT 5;

교훈: LIMIT은 항상 ORDER BY와 함께 — 순서 없는 TOP N은 랜덤

---

💡 회고

잘한 점

• 마트 3종을 각각 다른 분석 단위(고객/주문)로 설계한 것
• 오류 메시지를 읽고 YEAR() 우회 방식을 빠르게 적용한 것
• mart_ 접두어로 원본 테이블과 명확히 구분한 것

아쉬운 점

• 비율 계산 시 분모 왜곡 실수 반복 → "분모가 무엇인가" 를 먼저 생각하는 습관 필요
• ORDER BY 없는 LIMIT 사용 → TOP N 쿼리엔 정렬 필수

배운 것

"데이터마트는 분석가의 작업대다." 좋은 마트 하나가 이후 Tableau 대시보드, Python 분석, 발표자료까지 전부 연결된다. 설계에 30분 투자하면 분석에 드는 시간이 10배 줄어든다.

---

#실습과제

1. RFM 마트에서 재구매 고객(Frequency >= 2) 비율은?

2. 배송 마트에서 지연율 가장 높은 고객 주(state) TOP 3는?

3. 리뷰 마트에서 Negative 리뷰의 평균 지연 일수는?

4. 배송 마트에서 판매자 주(seller_state)별 평균 배송 소요일 TOP 5는?

🔜 다음 포스팅

[Olist 이커머스 분석 #4] Tableau로 RFM 대시보드 만들기

---

본 시리즈는 Kaggle의 Olist Brazilian E-Commerce 데이터셋을 활용한 포트폴리오 프로젝트입니다.