EDA (데이터 마트 생성 및 일별판매량 확인)

1. 데이터마트 생성

• EDA를 시작하기에 앞서, 간편함을 위해 데이터마트를 생성하였다.
• 컬럼으로는 주문ID/고객ID/고객 주/배송소요일/지연일수/리뷰스코어/결측치를 제외한 order_purchase_timestamp, order_delivered_customer_date, order_estimated_Date가 9개의 컬럼이 포함되었다.

2. 일별 주문량 계산

SELECT
  DATE(order_purchase_timestamp) AS order_date,
  COUNT(*) AS daily_orders
FROM orders
WHERE order_status = 'delivered'
  AND order_purchase_timestamp IS NOT NULL
GROUP BY order_date
ORDER BY order_date;

• MySQL에서 전체 기간 주문량을 일별로 나타낸 CSV 파일을 파이썬에서 시각화하였다.
  

  • 가장 눈에 띄는 특징으로는 2017-11-24일 주문량에서 매우 큰 이상치가 발견된다는 점이다.

• 리서치 결과, 해당 일자는 블랙프라이데이로 확인되었다.
• 따라서, 블랙프라이데이에 대한 세부적인 EDA를 진행해보았다.

3. 블랙프라이데이 카테고리별 판매량

select 
p.product_category_name as category, 
count(*) as items_sold 
from orders o
join orders_items oi 
on o.order_id = oi.order_id 
join products p 
on oi.product_id = p.product_id
where o.order_status = 'delivered' 
and date(o.order_purchase_timestamp) = '2017-11-24'
group by p.product_category_name
order by items_sold desc;

• 마찬가지로 SQL에서 CSV파일을 생성한 뒤 Top10 판매 카테고리를 파이썬에서 시각화하였다.

• 그 결과, 침구/식탁/욕실용품이 압도적인 판매량을 보였고, 가구/인테리어, 공구/정원용품, 스포츠/레저, 뷰티/헬스, 휴대폰/통신기기, 시계/선물용품, 장난감, 컴퓨터/IT/악세사리, 향수가 뒤를 이었다.
• 1-2개 카테고리만 압도적으로 나오는 관계로 로그 변환를 해주어 재확인하였다.

4. 블랙프라이데이 카테고리별 AOV

SELECT
  category,
  AVG(order_value) AS avg_order_value
FROM (
  SELECT
    o.order_id,
    p.product_category_name AS category,
    SUM(oi.price + oi.freight_value) AS order_value
  FROM orders o
  JOIN orders_items oi
    ON o.order_id = oi.order_id
  JOIN products p
    ON oi.product_id = p.product_id
  WHERE o.order_status = 'delivered'
    AND DATE(o.order_purchase_timestamp) = '2017-11-24'
  GROUP BY o.order_id, p.product_category_name
) t
GROUP BY category
ORDER BY avg_order_value DESC;

• 판매량에 이어 Top10 AOV(판매금액)을 확인하였다.

• 농업/산업/상업용 장비가 압도적인 판매량을 기록하였다.
• 대형가전, 가전제품, 건설/공구/조명, 시계/선물용품, 악기, 카메라/영상장비, 거실가구, 사무용가구, 아이디어/기획상품이 뒤를 이었다.

5. 블랙프라이데이 할부율 분석

• 가설: 세일 이벤트 기간이 고객들의 고가 물품 소비를 촉진하여 할부율이 높아졌을 것이다.

SELECT
  period,
  COUNT(*) AS orders,
  ROUND(AVG(payment_installments), 2) AS avg_installments,
  ROUND(SUM(CASE WHEN payment_installments > 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS installment_rate
FROM (
  SELECT
    o.order_id,
    CASE
      WHEN DATE(o.order_purchase_timestamp) BETWEEN '2017-11-10' AND '2017-11-23' THEN 'Before'
      WHEN DATE(o.order_purchase_timestamp) = '2017-11-24' THEN 'Black Friday'
      WHEN DATE(o.order_purchase_timestamp) BETWEEN '2017-11-25' AND '2017-12-08' THEN 'After'
    END AS period,
    op.payment_installments
  FROM orders o
  JOIN order_payments op ON o.order_id = op.order_id
  WHERE o.order_status = 'delivered'
    AND DATE(o.order_purchase_timestamp) BETWEEN '2017-11-10' AND '2017-12-08'
) t
GROUP BY period;

• 비교 기간은 블랙프라이데이 전후 2주로 잡았다.
• 전체 기간으로 잡고 비교한다면 블랙프라이데이가 아닌 계절 요인, olist 서비스 확대 요인 등 불순물이 추가되어 목적이 흐려질 수 있다는 판단으로 전후 비교 기간을 2주로 설정하였다.

• 블랙프라이데이의 평균 할부 개월 수는 3.51개월로, 블랙프라이데이 전 2주와 후 2주와 비교했을 때 가장 많았다.
• 할부율 또한 57.95%로 가장 높음을 확인할 수 있다.
• 이는 블랙프라이데이가 고가 상품 구매를 유도하거나, 평소 미루던 소비를 앞당긴 이벤트 였음을 시사하고 있다.

6. 블랙프라이데이 배송 지연율 분석

• 가설: 주문량이 폭증한만큼, 배송 지연율 또한 높아졌을 것이다.
  SELECT
  period,
  round(AVG(is_delayed)*100,2) AS delay_rate
  FROM (
  SELECT
  CASE
  WHEN DATE(order_purchase_timestamp) BETWEEN '2017-11-10' AND '2017-11-23' THEN 'Before'
  WHEN DATE(order_purchase_timestamp) = '2017-11-24' THEN 'Black Friday'
  WHEN DATE(order_purchase_timestamp) BETWEEN '2017-11-25' AND '2017-12-08' THEN 'After'
  END AS period,
  is_delayed
  FROM delivery_review_dm
  WHERE DATE(order_purchase_timestamp) BETWEEN '2017-11-10' AND '2017-12-08'
  ) t
  GROUP BY period;

• 앞서 생성한 배송-리뷰 데이터마트(delivery_review_dm)를 활용하였다.

• 블랙프라이데이의 배송지연율이 평소(Before)이 배해 거의 1.7배 가까이 상승했음을 알 수 있다.
• 즉, 블랙프라이데이 기간 중 주문 급증으로 인해 배송 시스템의 처리 한계를 초과했고, 그 영향이 이벤트 이후까지 이어졌음을 알 수 있다.

• 종합적으로, 고객은 블랙프라이데이에 더 많은, 더 큰 금액을, 더 쉽게 결제했지만, 운영 측면(배송)은 이를 따라가지 못했음을 알 수 있다.
• 이는 앞서 분석한 지역별 배송 지연 인사이트 발굴과 연결지어 해석할 수 있다.