Olist 기업 분석 후 서비스 개선 방안 제시

김준호·2024년 7월 3일
pythonsql개인 프로젝트데이터 분석
post-thumbnail

개요

테이블들을 살펴보다보니 reviews 테이블의 review_score와 review_comment_message가 눈에 들어왔다. 대부분 review를 통해 소비자들은 개선해줬으면 할 사항들을 적거나, 어떤 부분이 좋았는지에 대해 적어주기 때문에 서비스의 개선을 하기 위해서 review쪽을 먼저 바라봐야할 것 같았다.
먼저 review 1점 혹은 2점의 리뷰 메시지들을 번역해 읽어보았다.




대부분의 리뷰가 이런 내용이다 요약하자면, 오배송과 배송 지연, 배송 누락이 낮게 평가된 리뷰들의 대다수를 이루었다
반면, 4점 혹은 5점을 받은 리뷰메세지를 보면,



높게 평가된 리뷰들은 당연하겠지만, 좋은 제품들과 빠른 배송, 적어도 정시배송이 되었을 때 좋은 평가들을 받았다.
이를 통해 올바른 배송과 짧은 배송기간이 소비자들에게 굉장히 중요한 요소임을 깨닫고 어떻게 하면 평점이 낮은 리뷰를 줄일 수 있을까를 고민으로 시작해보았다.

데이터 분석

먼저, 데이터베이스 다이어그램을 그려보았다.

이를 통해 각 1 : N 관계를 갖게되는 컬럼들에서 N을 갖게되는 컬럼들에게 인덱스를 걸어 조금 더 각 테이블들을 JOIN하거나 데이터를 찾아야할 때 빠르게 탐색할 수 있도록 해주었다.

그리고 이제 각 테이블들을 하나하나 조립해보면서, 시각화를 해보았다.

월별 매출 추이 시각화

sales_month = """
SELECT
	DATE_FORMAT(orders.order_purchase_timestamp, "%Y-%m") AS dt,
    SUM(order_payments.payment_value) AS month_sales
FROM
	orders
JOIN
	order_payments
ON
	order_payments.order_id=orders.order_id
GROUP BY
	dt
ORDER BY
	dt
"""

SQL문을 작성해주고

sales_df = pd.read_sql(sales_month, conn)

판다스로 SQL을 읽어왔다.

sales_df = sales_df.fillna(0)
sales_df = sales_df.set_index("dt")

Nan값을 0으로 바꾸어주고, 인덱스를 각 월로 지정.

이 데이터 프레임을 시각화

plt.style.use("ggplot")
plt.figure(figsize = (12, 6))
plt.plot(sales_df)
_ = plt.xticks(size = 8, rotation = 45, ha = "right")

월별 매출 추이를 뽑아보았다.

카테고리별 매출 (상위 20개)

cg_sales = """
SELECT
	products.product_category_name AS category,
    SUM(order_items.price) AS price
FROM
	products
JOIN
	order_items
ON
	products.product_id = order_items.product_id
GROUP BY
	category
HAVING
	category is not NULL
ORDER BY
    price DESC
LIMIT 20
"""

SQL문을 작성해주고,

cg_sales_df = pd.read_sql(cg_sales, conn)
cg_sales_df = cg_sales_df.set_index("category")

약간의 전처리 후,

plt.style.use("ggplot")
plt.figure(figsize = (12, 6))
cg_sales_df.plot(kind = "bar", figsize = (12,6))
plt.title("Category Sales")
_ = plt.xticks(size = 8, rotation = 45, ha = "right")
plt.ylabel("Price")
plt.xlabel("Category")

데이터 프레임 시각화

이런 형식으로 데이터를 쭉쭉 시각화 해보았다.

지역별 매출 (상위 50곳)

지불 수단 별 매출

리뷰 중 1점을 가장 많이 받은 카테고리 (상위 25개)

리뷰 중 5점을 가장 많이 받은 카테고리 (상위 25개)

리뷰 중 1점을 받은 카테고리들의 판매자 도시 카운팅 (상위 30곳)

low_category_city = """
WITH casa_city AS (
	WITH seller_products AS (
		SELECT
			order_items.order_id,
			order_items.seller_id,
			order_items.product_id,
			order_items.price,
			sellers.seller_city
		FROM
			order_items
		JOIN
			sellers
		ON
			order_items.seller_id = sellers.seller_id
	)

	SELECT
		seller_products.order_id,
		seller_products.seller_id,
		seller_products.product_id,
		products.product_category_name,
		seller_products.seller_city,
		seller_products.price
	FROM
		seller_products
	JOIN
		products
	ON
		seller_products.product_id = products.product_id
)

SELECT
    casa_city.seller_city,
    COUNT(casa_city.seller_city) AS CNT
FROM
	casa_city
JOIN
	reviews
ON
	casa_city.order_id = reviews.order_id
WHERE
	reviews.review_score = 1
GROUP BY
	casa_city.seller_city
ORDER BY
	CNT DESC
LIMIT
	30;
"""

리뷰 중 5점을 받은 카테고리들의 판매자 도시 카운팅 (상위 30곳)

high_category_city = """
WITH casa_city AS (
	WITH seller_products AS (
		SELECT
			order_items.order_id,
			order_items.seller_id,
			order_items.product_id,
			order_items.price,
			sellers.seller_city
		FROM
			order_items
		JOIN
			sellers
		ON
			order_items.seller_id = sellers.seller_id
	)

	SELECT
		seller_products.order_id,
		seller_products.seller_id,
		seller_products.product_id,
		products.product_category_name,
		seller_products.seller_city,
		seller_products.price
	FROM
		seller_products
	JOIN
		products
	ON
		seller_products.product_id = products.product_id
)

SELECT
    casa_city.seller_city,
    COUNT(casa_city.seller_city) AS CNT
FROM
	casa_city
JOIN
	reviews
ON
	casa_city.order_id = reviews.order_id
WHERE
	reviews.review_score = 5
GROUP BY
	casa_city.seller_city
ORDER BY
	CNT DESC
LIMIT
	30;
"""

1점을 남긴 구매자들의 도시 카운팅(상위 30곳)

low_customer_city = """
WITH customer_cities AS(
	SELECT
		customers.customer_id,
		orders.order_id,
		customers.customer_city
	FROM
		customers
	JOIN
		orders
	ON
		customers.customer_id = orders.customer_id
)

SELECT
    customer_cities.customer_city,
    COUNT(customer_cities.customer_city) AS total
FROM
	customer_cities
JOIN
	reviews
ON
	customer_cities.order_id = reviews.order_id
WHERE
	review_score = 1
GROUP BY
	customer_city
ORDER BY
	total DESC
LIMIT 30
"""

5점을 남긴 구매자들의 도시 카운팅

high_customer_city = """
WITH customer_cities AS(
	SELECT
		customers.customer_id,
		orders.order_id,
		customers.customer_city
	FROM
		customers
	JOIN
		orders
	ON
		customers.customer_id = orders.customer_id
)

SELECT
    customer_cities.customer_city,
    COUNT(customer_cities.customer_city) AS total
FROM
	customer_cities
JOIN
	reviews
ON
	customer_cities.order_id = reviews.order_id
WHERE
	review_score = 5
GROUP BY
	customer_city
ORDER BY
	total DESC
LIMIT 30
"""


도시들을 시각화 해봤지만 패턴을 못찾겠어서 넘어가야겠다고 판단했다.

평점 1점 많이 받은 카테고리와 5점을 많이 받은 카테고리에서 높은 순위에 기록된 cama_mesa_banho에 집중.

이유를 알아보기 위해 판매자의 도시, 생산지에 문제가 있지 않나 하고 구분해보았다.

평점 1점을 많이 받은 cama_mesa_banho 판매자들 도시

low_casa_seller_city = """
WITH casa_city AS (
	WITH seller_products AS (
		SELECT
			order_items.order_id,
			order_items.seller_id,
			order_items.product_id,
			order_items.price,
			sellers.seller_city
		FROM
			order_items
		JOIN
			sellers
		ON
			order_items.seller_id = sellers.seller_id
	)

	SELECT
		seller_products.order_id,
		seller_products.seller_id,
		seller_products.product_id,
		products.product_category_name,
		seller_products.seller_city,
		seller_products.price
	FROM
		seller_products
	JOIN
		products
	ON
		seller_products.product_id = products.product_id
	HAVING
		products.product_category_name = "cama_mesa_banho"
)

SELECT
    casa_city.seller_city,
    COUNT(casa_city.seller_city) as total
FROM
	casa_city
JOIN
	reviews
ON
	casa_city.order_id = reviews.order_id
WHERE
	reviews.review_score = 1
GROUP BY
	casa_city.seller_city
ORDER BY
    total DESC
"""


Ibitinga 라는 도시에서 제일 많이 생산된 것을 파악할 수 있었다.

평점 5점을 많이 받은 cama_mesa_banho 판매자들 도시

high_casa_seller_city = """
WITH casa_city AS (
	WITH seller_products AS (
		SELECT
			order_items.order_id,
			order_items.seller_id,
			order_items.product_id,
			order_items.price,
			sellers.seller_city
		FROM
			order_items
		JOIN
			sellers
		ON
			order_items.seller_id = sellers.seller_id
	)

	SELECT
		seller_products.order_id,
		seller_products.seller_id,
		seller_products.product_id,
		products.product_category_name,
		seller_products.seller_city,
		seller_products.price
	FROM
		seller_products
	JOIN
		products
	ON
		seller_products.product_id = products.product_id
	HAVING
		products.product_category_name = "cama_mesa_banho"
)

SELECT
    casa_city.seller_city,
    COUNT(casa_city.seller_city) as total
FROM
	casa_city
JOIN
	reviews
ON
	casa_city.order_id = reviews.order_id
WHERE
	reviews.review_score = 5
GROUP BY
	casa_city.seller_city
ORDER BY
    total DESC
"""


여기도 마찬가지로 Ibitinga라는 곳이 가장 많았다.
1점의 생산지와 5점의 생산지가 다르다면 1점인 생산지에서 문제가 있을 것 같다 생각하려고 했지만 같은 것을 보니 각 판매자 별로 편차가 심한 것 같다.

평점 1점을 많이 준 cama_mesa_banho 판매자들 도시

평점 5점을 많이 준 cama_mesa_banho 판매자들 도시

인사이트 도출을 위한 추가 분석

배송 예정일보다 늦게 도착한 주문건 카운트

SELECT
	COUNT(*)
FROM
	orders
WHERE
	DATE_FORMAT(order_delivered_customer_date, "%Y-%m-%d") > DATE_FORMAT(order_estimated_delivery_date, "%Y-%m-%d")

총 6535 건이었다.

배송예정일에 도착하거나 일찍 도착한 주문건 카운트

SELECT
	COUNT(*)
FROM
	orders
WHERE
	DATE_FORMAT(order_delivered_customer_date, "%Y-%m-%d") <= DATE_FORMAT(order_estimated_delivery_date, "%Y-%m-%d")

총 89941 건이었다.

배송예정일보다 늦게 도착한 누적 주문건 수는 6535 / 96476 건이었다.
대략 전체 주문 건 비율 7% 가량 배송지연이 발생함을 확인.

결제일과 배송시작일의 차이 계산

SELECT
	DATE_FORMAT(order_purchase_timestamp, "%Y-%m-%d") AS purchase_dt,
    DATE_FORMAT(order_approved_at, "%Y-%m-%d") AS approved_dt,
    DATE_FORMAT(order_delivered_carrier_date, "%Y-%m-%d") AS carrier_dt,
	TIMESTAMPDIFF(DAY, order_purchase_timestamp, order_delivered_carrier_date) AS order_preparation_time
FROM
	orders
WHERE
	DATE_FORMAT(order_delivered_carrier_date, "%Y-%m-%d") is not NULL
    AND TIMESTAMPDIFF(DAY, order_approved_at, order_delivered_carrier_date) is not NULL
    AND TIMESTAMPDIFF(DAY, order_approved_at, order_delivered_carrier_date) >= 0

0 이상인 값을 넣은 이유는 테이블을 살펴보니 결제하기 이전에 배송을 시작한 이상한 데이터값도 포함되어있어 0 이상이라는 조건을 넣었다.
결제하고 제품 준비까지 가장 오래 걸린 기간은 125일, 짧은 날은 당일 발송이었다 제품마다 차이가 있겠지만 1일 이상 걸린 데이터들만의 평균을 구해보면 대략 4일이었다.

주문 접수하고 예상 배송 예정일을 며칠 뒤로 잡았는가.

SELECT
	order_id,
    DATE_FORMAT(order_approved_at, "%Y-%m-%d") AS approved_dt,
    DATE_FORMAT(order_estimated_delivery_date, "%Y-%m-%d") AS estimated_delivery_dt,
    TIMESTAMPDIFF(DAY, order_approved_at, order_estimated_delivery_date) AS Difference
FROM
	orders
WHERE
	DATE_FORMAT(order_delivered_customer_date, "%Y-%m-%d") <= DATE_FORMAT(order_estimated_delivery_date, "%Y-%m-%d")
    AND DATE_FORMAT(order_approved_at, "%Y-%m-%d") is not NULL
    AND TIMESTAMPDIFF(DAY, order_approved_at, order_estimated_delivery_date) >= 0
ORDER BY
	Difference DESC

제일 긴 예상 배송일은 153일 뒤였다. 평균 약 23일 뒤였다.
또한, 예상 배송일을 7일 이상으로 잡은 주문건수가 89925건 중 88545건이었다.

인사이트 도출

  • 판매자의 도시와 구매자의 도시의 상관관계 없이 배송과 관련된 문제가 적어도 Olist에서는 발생하고 있다.
  • 오배송과 배송누락과 관련된 데이터는 주어진 데이터로써 한계가 있기에 확인할 수 없었다.
  • 전체 주문 중 7% 가량 배송이 늦어지고, 늦게 도착한 주문 6535건 중 14% (884건)이 20일 이상 늦어진 주문이다.
  • 판매자가 배송 예정일을 너무 멀리잡는다. 4일 이상으로 잡은 주문은 대략 90000건 가까이 된다.

결론

오배송과 배송누락은 주어진 데이터만으로 뽑아내기에는 한계가 있어 유의미한 값을 찾지 못했지만 분석할 수 있는 것으로 해본 결과, 생각보다 늦게 도착하는 물품의 비율이 높지 않았다. 하지만 내 생각보다이지 대략 배송된 전체 주문 중 7% 가량이 배송이 늦어지는 것이다. 배송예정일보다 늦게 도착한 주문 6535건 중 14% (884건) 이 20일 이상 늦어진 것이다. 근본적으로 판매자가 배송 예상일을 평균적으로 너무 멀리 잡는다. 예상배송일을 4일 이상으로 잡은 주문이 대략 90000건이 되는데 이러한 부분이 소비자의 불만을 계속해서 초래하는 것으로 보인다.

매 월 평점 1점의 비율과 배송이 늦어질수록 평점이 낮아지는 걸 나타내는 데이터프레임이다.
배송이 늦어질수록 소비자들의 평가는 안좋아진다는 것을 알고 이러한 부분이 개선해야할 점이라고 생각했다.

  1. 주문 접수부터 배송시작까지의 시간을 줄이기 위해 대규모 창고를 건설하여 판매량 순으로 재고를 미리 받아놓아 주문이 접수되었을 때에 바로바로 나갈 수 있도록 한다.
  1. 예상 배송일을 길게 잡는 이유가 택배사 때문인지, 도로상황 혹은 치안 때문인지 정확히 알 수는 없으나 후자는 기업에서 해결할 수 없는 문제이니 넘어가고 택배사의 문제라면 olist 라는 기업은 pax라는 물류회사를 같이 운영하고 있는데, 허브를 확장하거나, 배송 차량 또는 직원을 늘려 조금이나마 땡길 수 있게 해야한다.
  1. 오배송 및 배송 누락의 관한 데이터는 뽑아낼 수 없어 아쉬웠지만 이 또한 필히 개선해야한다고 생각한다. 먼저, 각 판매처에서 올바른 검수가 이루어질 수 있도록 해야하고 후에 olist에서 물건을 받아와 또 한 번 검수를 하는 방식으로 이루어질 수 있도록 해야한다.
  1. 고객센터의 부재도 크다고 생각한다. 추출한 리뷰 중에서도 간간히 보이지만 문의를 보냈는데 답변을 받지 못했다는 리뷰도 상당수를 차지한다. 고객센터 및 고객들의 문의를 방치하지않고 CS팀을 꾸려 충분히 고객서비스를 개선할 수 있도록 해야한다.
profile
김준호
어렵지만 이겨내보겠습니다
이전 포스트

Russian Roulette 미니게임 만들기

다음 포스트

음주운전 데이터 분석

0개의 댓글