사용 데이터 Instacart Market Basket Analysis
이번에는 식품 배송 관련 데이터를 모아, 여러 특성을 분석 할 것이다.
문제: 식품배송 데이터 분석
문제는 세 가지다.
식품처럼 신선도가 중요한 제품 같은 경우는 이전 자동차, 옷과는 달리 재고 관리, 구매 주기가 중요하다. 한번 살펴보자
이 페이지에선 구매자 특성 분석(10분위)을 풀어본다.
0.ERD
이전과 달리 ERD를 CSV 그대로 불러와 아무런 연결이 없다.
살펴보면 aisles와 departments는 상품의 카테고리를 의미하고, order_products__prior는 각 주문번호의 상세 내용, orders는 기본 주문정보, products는 상품 정보이다.
시작하자.
1
USE instacart;
2. 구매자 특성 분석
1. 10분위 분석
10분위 분석은 하나의 집단을 10분위로 나누어 각 분위 수에 해당하는 집단의 성질으 나타내는 방법이다.
사용하는 MYSql에는 10부위 분석해주는 함수가 없다… 직접 풀어 써야한다.
각 구매자의 전체 주문 건 수에 따라 10분위로 나눈다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
user_id ,F ,RNK
, CASE WHEN RNK <= CNT/ 10 THEN 'Quantile_1'
WHEN RNK <= (CNT/ 10) * 2 THEN 'Quantile_2'
WHEN RNK <= (CNT/ 10) * 3 THEN 'Quantile_3'
WHEN RNK <= (CNT/ 10) * 4 THEN 'Quantile_4'
WHEN RNK <= (CNT/ 10) * 5 THEN 'Quantile_5'
WHEN RNK <= (CNT/ 10) * 6 THEN 'Quantile_6'
WHEN RNK <= (CNT/ 10) * 7 THEN 'Quantile_7'
WHEN RNK <= (CNT/ 10) * 8 THEN 'Quantile_8'
WHEN RNK <= (CNT/ 10) * 9 THEN 'Quantile_9'
ELSE 'Quantile_10'
END quantile
FROM (
SELECT
*
, ROW_NUMBER() OVER(ORDER BY F DESC) RNK
, (SELECT COUNT(DISTINCT user_id) FROM orders) AS CNT
FROM (
SELECT
user_id
, COUNT(DISTINCT order_id) AS F
FROM orders
GROUP BY 1
) A
) A
;
엄청엄청 길어져서 SELECT 내부에 서브쿼리를 만들었다.
음 CASE WHEN 내부의 변수를 함수로 만들 수 있을 거 같은데.. 이건 좀 찾아봐야 할 것 같다.
10분위로 나누었다!
그런데 2, 1회 주문자가 너무 많아 분위를 나누는게 의미있는 것 같진 않다.
2. 상품 분석
고객들을 비교하기엔 차이가 너무 적다.
이번엔 상품을 비교해보자.
order_products__prior의 reordered 열이 해당 정보를 제공한다. reordered / (그 상품 총 구매) 가 재구매율 일 것 이다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
*
, RANK()OVER(ORDER BY Rate DESC) AS RNK
FROM(
SELECT
A.product_id
, B.product_name
, COUNT(DISTINCT order_id) AS CNT
, SUM(CASE WHEN reordered = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT order_id) AS Rate
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
GROUP BY 1,2
)A
WHERE CNT >= 20
;
20개 미만 구매는 재구매율의 신뢰에 영향을 줘서 제거했다.
의외로 바나나가 낮다!
1위는 아이스랜드 풍의 블루베리 무지방 요거트. 무려 재구매율 95(24회중)%로 꽤 맛있나보다.