KPI 선정을 위한 가설 설정
- 이커머스 주문은 평일보다는 시간적으로 여유가 있는 주말에 더 많이 발생할 것이다.(기각)
- 배송 소요일수가 짧을수록 고객 리뷰 점수가 높을 것이다. (채택)
- 대도시 고객일수록 상품 가격 대비 실제 결제 금액이 더 높을 것이다. (채택)
EDA를 위한 KPI 선정
- 아직 전처리는 하지 않은 상태에서, Raw Data를 훑어보며 주요 KPI를 선정하였다.
- 이커머스에서 활용되는 주요 KPI가 있지만, 세부적인 EDA를 위해 세워둔 일부 가설을 기반으로 더 디테일 한 KPI를 선정하였다 .
Olist Store E-commerce analysis Project KPI
- KPI1: 주말 vs 평일 주문 기준 결제 통계 (order_purchase_timestamp) - KPi2: 리뷰스코어 5점이고 결제 수단이 신용카드인 주문 건수 - KPI3: 펫샵(pet_shop) 카테고리 주문의 평균 배송 소요일수 - KPI4: 상파울로 거주 고객의 평균 상품 가격 및 평균 결제 금액 - KPI5: 배송 소요일수(배송완료일-주문일시)와 리뷰 평점 간의 관계
EDA 1
KPI1: 주말 vs 평일 주문 기준 결제 통계
[쿼리]
SELECT * FROM olistdata.orders;
SELECT * FROM olistdata.order_payments;
select kpi1.day_end,
concat(round(kpi1.total_payment / (select sum(payment_value) from order_payments) * 100, 2)
, '%') as percentage_payment_values
from
(select ord.day_end , sum(pmt.payment_value) as total_payment
from order_payments as pmt
join
(select distinct order_id,
case
when weekday(order_purchase_timestamp) in (5,6) then "Weekend"
else "Weekday"
end as Day_end
from orders) as ord
on ord.order_id = pmt.order_id
group by ord.day_end) as kpi1 ; [Result]
[Insight]
- 전체 결제 금액 중, 약 3/4가 평일에 발생하였음
- 즉, 고객들은 주말보다 평일에 훨씬 더 많이 구매
- 주말 쇼핑이 주력일 거라는 가설과는 다른 결과
KPI2: 리뷰스코어 5점이고 결제 수단이 신용카드인 주문 건수
[쿼리]
select
count(pmt.order_id) as Total_Orders
from
order_payments pmt
inner join order_reviews rev on pmt.order_id = rev.order_id
where
rev.review_score = 5
and pmt.payment_type = 'credit_card' ;[Result]
[Insight]
- 고객 만족도 + 핵심 결제수단이 동시에 충족된 주문이 상당히 많음
KPI3: 펫샵(pet_shop) 카테고리 주문의 평균 배송 소요일수
[쿼리]
select
prod.product_category_name,
round(avg(datediff(ord.order_delivered_customer_date , ord.order_purchase_timestamp)), 0) as Avg_delivery_days
from orders ord
join
(select product_id, order_id, product_category_name
from products
join orders_items using(product_id)) as prod
on ord.order_id = prod.order_id
where prod.product_category_name = "Pet_shop"
group by prod.product_category_name ; [Result]
[Insight]
- 평균 11일의 소요 시간은 이커머스 기준으로 다소 긴 편에 속함
- 추가분석1: "배송이 느린 카테고리는 고객 만족도에 어떠한 영향을 주는가?"
- 추가분석2: "펫샵 vs 전체 평균 배송일 비교"
- 추가분석3: "펫샵 내 배송일수 구간별 리뷰 분포"
KPI4: 상파울로 거주 고객의 평균 상품 가격 및 평균 결제 금액
[쿼리]
with orderItemsAvg as (
select round(avg(item.price)) as avg_order_item_price
from orders_items item
join orders ord
on item.order_id = ord.order_id
join customers cust on ord.customer_id = cust.customer_id
where cust.customer_city = "Sao Paulo"
)
select
(select avg_order_item_price from orderItemsAvg) as avg_order_item_price,
round(avg(pmt.payment_value)) as avg_payment_value
from order_payments pmt
join orders ord on pmt.order_id = ord.order_id
join customers cust on ord.customer_id = cust.customer_id
where
cust.customer_city = "Sao Paulo" ;[Result]
[Insight]
- 실제 상품 가격 평균은 108(약 29,000원), 최종 결제 금액은 136(약 36,700원)
- 상품 가격 외 추가 비용이 결제 금액에 반영되고 있음을 알 수 있음(ex.배송비, 세금, 복수 상품 구매 가능성)
- 추가분석 1: 상파울루 vs 타 도시 평균 비교
- 추가분석 2: 상파울루 배송비 평균 추정
KPI5: 배송 소요일수(배송완료일-주문일시)와 리뷰 평점 간의 관계
[쿼리]
select
rew.review_score,
round(avg(datediff(ord.order_delivered_customer_date , order_purchase_timestamp)),0) as "Avg shipping days"
from orders as ord
join order_reviews as rew on rew.order_id = ord.order_id
group by rew.review_score
order by rew.review_score ;[Result]
[Insight]
- 배송이 빠를수록 리뷰 점수가 높고, 배송이 느릴수록 리뷰점수가 낮다
- 리뷰1점 고객은 평균 21일 대기, 리뷰 5점 고객은 평균 11일 대시(10일 차이)
- 배송 속도는 고객 만족도를 결정짓는 핵심 요인 중 하나
[총평]
본 분석은 고객의 구매 시점, 결제 방식, 배송 리드타임이 고객 만족도에 미치는 영향을 검증하기 위해 다섯 가지 가설을 설정하였다. 분석 결과, 주문은 주말보다 평일에 집중되어 있었으며, 고만족 고객은 신용카드 결제에 집중되는 경향을 보였다. 또한 특정 카테고리에서는 배송 기간이 상대적으로 길게 나타났고, 대도시 고객일수록 상품 가격 대비 실제 결제 금액이 높았다. 마지막으로 배송 소요일수와 리뷰 점수 간에는 명확한 음의 상관관계가 확인되어, 배송 속도가 고객 만족도를 결정짓는 핵심 요인임을 데이터로 입증하였다.
