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

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

 

SQL로 수행한 카테고리별 구매주기 분석

 

1. 분석 개요

분석 목표

• 카테고리별 평균 구매주기를 계산하여, 재구매 마케팅 타이밍 도출
• 카테고리별 매출 기여도를 함께 분석해 우선순위 마케팅 타깃 파악
• 회원별 구매주기를 병행 분석하여 고객 기반 리텐션 전략 수립

구매주기 정의 (CIRCLE)

(최근구매일자 − 최초구매일자) ÷ (구매횟수 − 1)

 

예시: 2025.01.01 / 2025.01.10 / 2025.01.31 구매 → (31−1)/(3−1) = 15일 주기로 구매

---

 

2. 카테고리별 구매주기 계산

1. 카테고리별 데이터 마트 생성

 

CREATE TABLE ORDER_INFO_BY_CATEGORY AS (
SELECT pct.product_category_name,
       MIN(order_purchase_timestamp) AS MIN_order_purchase_timestamp,
       MAX(order_purchase_timestamp) AS MAX_order_purchase_timestamp,
       COUNT(DISTINCT o.order_id) AS COUNT_DISTINCT_ORDER_NO
  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
 WHERE pct.product_category_name IS NOT NULL
 GROUP BY 1
);

 

2. 카테고리별 구매주기 계산

CREATE TABLE ORDER_INFO_BY_CATEGORY_CIRCLE AS ( 
WITH CIRCLE AS (
SELECT *,
       DATEDIFF(MAX_order_purchase_timestamp, MIN_order_purchase_timestamp) /
       (COUNT_DISTINCT_ORDER_NO - 1) AS CIRCLE
  FROM ORDER_INFO_BY_CATEGORY
 WHERE COUNT_DISTINCT_ORDER_NO > 1
)
SELECT product_category_name, CIRCLE, COUNT_DISTINCT_ORDER_NO
  FROM CIRCLE
);

💡 평균 구매주기(CIRCLE) 가 클수록 재구매 주기가 길다는 의미.

 

---

3. 카테고리별 매출 기여도

 

WITH BASE AS (
SELECT pct.product_category_name,
       SUM(IFNULL(oi.price,0)) + SUM(IFNULL(oi.freight_value,0)) AS sale_amt
  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
 WHERE pct.product_category_name IS NOT NULL
 GROUP BY 1
)
SELECT BASE.*, CIRCLE, COUNT_DISTINCT_ORDER_NO
  FROM BASE
  LEFT JOIN ORDER_INFO_BY_CATEGORY_CIRCLE AS CIRCLE
    ON BASE.product_category_name = CIRCLE.product_category_name
 ORDER BY sale_amt DESC;

 

주요 결과:

• 매출 상위 7개 카테고리가 전체 매출의 약 71% 차지.
• 대표 카테고리:
  beleza_saude, relogios_presentes, cama_mesa_banho, moveis_decoracao, esporte_lazer, informatica_acessorios, telefonia.

→ 특정 소수 카테고리에 매출 의존도가 매우 높음.

---

 

4️⃣ 회원별 구매주기 분석

1. 회원별 데이터 마트 생성

CREATE TABLE ORDER_INFO_BY_CUSTOMER AS (
SELECT c.customer_unique_id,
       MIN(order_purchase_timestamp) AS MIN_order_purchase_timestamp,
       MAX(order_purchase_timestamp) AS MAX_order_purchase_timestamp,
       COUNT(DISTINCT o.order_id) AS COUNT_DISTINCT_ORDER_NO
  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_customers_dataset AS c ON o.customer_id = c.customer_id
 GROUP BY 1
);

 

 

2. 회원별 구매주기 계산

CREATE TABLE ORDER_INFO_BY_CUSTOMER_CIRCLE AS (
WITH CIRCLE AS (
SELECT *,
       DATEDIFF(MAX_order_purchase_timestamp, MIN_order_purchase_timestamp) /
       (COUNT_DISTINCT_ORDER_NO - 1) AS CIRCLE
  FROM ORDER_INFO_BY_CUSTOMER
 WHERE COUNT_DISTINCT_ORDER_NO > 1
)
SELECT customer_unique_id, CIRCLE, COUNT_DISTINCT_ORDER_NO_1
  FROM CIRCLE
);

평균 회원별 구매주기 = 113일,
첫 구매 후 약 100일 경과 시 재구매 유도 마케팅을 수행하는 것이 효과적.

---

 

5. 분석 결론

1. 카테고리별 구매주기 편차가 매우 크다.
2. 상위 7개 카테고리가 전체 매출의 71%를 차지한다.
3. 구매주기가 긴 중간 매출 카테고리(예: bebes, pcs, eletrodomesticos_2)에 마케팅 집중 필요.
4. 회원 평균 구매주기가 약 113일로, 100일 경과 고객에게 재구매 유도 프로모션 권장.
5. 카테고리 다양화를 통해 매출 구조 안정화 전략이 필요하다

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

---

 

💡 생각 정리

이번 실습은 “SQL만으로 고객 행동 분석을 완성할 수 있다”는 사실을 보여준 대표적인 예시였다.
단순 조회를 넘어서,

• 데이터 마트 설계,
• 구매주기 계산 로직,
• 매출 연계,
• 고객 세분화까지
  SQL 내에서 체계적으로 수행했다는 점이 인상 깊었다.

특히 (MAX - MIN) / (COUNT - 1) 공식을 활용한 구매주기 모델링은 Python이나 통계 툴 없이도 고객 리텐션 전략을 세울 수 있는 매우 실용적인 접근이었다.

또한 카테고리별 구매 패턴의 불균형은, 매출 집중 리스크와 마케팅 효율화의 중요성을 함께 보여줬다.

---

🚀 적용점

• 구매주기(CIRCLE)를 고객 마케팅 자동화 시스템에 연동해,
  일정 기간이 지난 고객에게 자동 프로모션 발송.
• 구매주기 × 매출 비중을 기반으로 우선순위 리텐션 타깃 고객군 설정.
• 구매주기가 긴 카테고리에 대해 신규 프로모션/리마인드 마케팅 강화.
• ORDER_INFO_BY_CUSTOMER_CIRCLE 데이터를 RFM 모델과 결합해
  “고객 가중치 기반 마케팅 스코어” 개발.
• SQL 결과를 BI 대시보드로 시각화해, 카테고리별 매출·주기 Heatmap 구축.

👉 이번 분석은 SQL을 통해 매출 구조, 고객 행동, 리텐션 포인트를 정량화한 완성형 데이터 마케팅 프로젝트였다.