사용 데이터 Instacart Market Basket Analysis
이번에는 식품 배송 관련 데이터를 모아, 여러 특성을 분석 할 것이다.
문제: 식품배송 데이터 분석
문제는 세 가지다.
식품처럼 신선도가 중요한 제품 같은 경우는 이전 자동차, 옷과는 달리 재고 관리, 구매 주기가 중요하다. 한번 살펴보자
이 페이지에선 다음 구매까지의 소요기간과 재구매 관계를 풀어본다.
0.ERD
이전과 달리 ERD를 CSV 그대로 불러와 아무런 연결이 없다.
살펴보면 aisles와 departments는 상품의 카테고리를 의미하고, order_products__prior는 각 주문번호의 상세 내용, orders는 기본 주문정보, products는 상품 정보이다.
시작하자.
1
USE instacart;
3. 다음 구매까지의 소요기간과 재구매 관계
하나 가정을 세워보자.
‘고객이 자주 재구매하는 상품은 그렇지 않은 상품보다 일정한 주기를 가지고 판매 될 것이다’
휴지, 샴푸 등 생필품은 판매 주기가 있을 것이다.
우선 재구매율이 높은 순서대로 상품으 10분위로 나눈 후 각 그룹에서 기간의 분산(Varince)를 구해보자!
1. 재구매율 계산과 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
28
SELECT
product_id
, CASE WHEN RNK <= (CNT_A/10) *1 THEN 'Q_1'
WHEN RNK <= (CNT_A/10) *2 THEN 'Q_2'
WHEN RNK <= (CNT_A/10) *3 THEN 'Q_3'
WHEN RNK <= (CNT_A/10) *4 THEN 'Q_4'
WHEN RNK <= (CNT_A/10) *5 THEN 'Q_5'
WHEN RNK <= (CNT_A/10) *6 THEN 'Q_6'
WHEN RNK <= (CNT_A/10) *7 THEN 'Q_7'
WHEN RNK <= (CNT_A/10) *8 THEN 'Q_8'
WHEN RNK <= (CNT_A/10) *9 THEN 'Q_9'
ELSE 'Q_10'
END RNK_GRP
FROM (
SELECT
*
, ROW_NUMBER()OVER(ORDER BY CNT_REOrder) AS RNK
, (SELECT COUNT(DISTINCT order_id) FROM order_products__prior) AS CNT_A
FROM (
SELECT
product_id
, SUM(CASE WHEN reordered = 1 THEN 1 ELSE 0 END)/COUNT(*) AS CNT_REOrder
FROM order_products__prior
GROUP BY 1
) A
) A
GROUP BY 1
;
2. 재구매 시간 찾기
그 다음은 위 코드를 활용해 각 분위별로 재구매에 소요된 시간의 분산을 분석해보자!
분위별 재구매 소요시간(기간)의 분산을 구하려면 다음과 같은 정보를 결합해 구해야 한다.
- 상품별 분위 수: 위 코드
- 주문 소요 시간:
orders테이블 - 주문 번호와 상품 번호:
order_products__prior테이블
우선 order_products__prior 테이블의 product_id와 orders테이블의 DAYS.. 를 JOIN한다.
단, 이때는 LEFT JOIN이 아닌 INNER JOIN 한다. 행이 좀 적어지더라도 결측치를 제거하기 위해서 이다.
1
2
3
4
5
6
7
SELECT
A.product_id
, B.days_since_prior_order
FROM order_products__prior A
INNER JOIN orders B
ON A.order_id = B.order_id
;
재구매 일자와 상품 ID가 결합했으니 이제 분산을 구해보자.
3. 분산 분석
MySQL에서 분산 분석 하는 함수는 VAEIANCE() 이다.
참고로 위의 코드들이 너무 길어서 재구매율 계산과 10분위 함수는 A1로, 재구매 시간 찾기 는 A2라는 이름들의 테이블로 만들었다.
1
2
CREATE TEMPORARY TABLE A1
--함수들~~
1
2
3
4
5
6
7
8
9
10
SELECT
A.RNK_GRP
, A.product_id
, VARIANCE(days_since_prior_order) VAR_DAYS
FROM A1 A
LEFT JOIN A2 B
ON A.product_id = B.product_id
GROUP BY 1,2
ORDER BY 1
;
각 분위 수, 상품의 구매 소요 기간의 분산을 계산했다.
이 포스트 가장 위에서 세운 가설
‘고객이 자주 재구매하는 상품은 그렇지 않은 상품보다 일정한 주기를 가지고 판매 될 것이다’
은 참인가 거짓인가?
이를 확인하기 위해 분산을 찾았으니 각 분위의 중위 수를 찾아야 한다.
참고로 MySQL 에서는 중위 수를 찾는 Median을 제공하지 않아서 임의로 AVG() 로 대체한다.
1
문제생김. 추후 작성