[Kaggle/MYSQL]식품 배송 데이터분석 3️⃣Instacart Market Basket Analysis

[이전 글] 구매자 분석

[Kaggle/MYSQL]식품 배송 데이터분석 2️⃣Instacart Market Basket Analysis

이제 재구매를 많이 하는 상품을 알아보고, 각 상품의 판매 특성에 대해 살펴보자.

상품 분석

먼저 재구매 비중이 높은 상품을 찾아보자. 상품별 재구매 비중(%)과 주문 건수를 계산한다.

---

SELECT PRODUCT_ID,
SUM(REORDERED)/SUM(1) REORDER_RATE,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDER_PRODUCTS__PRIOR
GROUP
BY PRODUCT_ID
ORDER
BY REORDER_RATE DESC

[쿼리 실행 결과]

 

주문 건수가 일정 건수(10건) 이하인 상품은 제외하고 보자. HAVING을 이용하면 일정 건수 이하인 상품들을 쉽게 제외할 수 있다.

SELECT A.PRODUCT_ID,
SUM(REORDERED)/SUM(1) REORDER_RATE,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDER_PRODUCTS__PRIOR A
LEFT
JOIN INSTACART.PRODUCTS B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP
BY PRODUCT_ID
HAVING COUNT(DISTINCT ORDER_ID) > 10

[쿼리 실행 결과]

 

• HAVING vs WHERE

WHERE 절은 FROM에 위치한 테이블에만 조건을 걸 수 있다. SELECT 문에서 새롭게 생성한 칼럼에 조건을 걸어야 하는 경우가 있다.많은 초급자가 SELECT에서 새롭게 생성한 칼럼을 WHERE에서 사용하는 실수를 한다고 한다^^

반면 HAVING은 그룹핑한 데이터에 조건을 생성하고 싶을 때 사용한다.

 

SELECT A.PRODUCT_ID,
B.PRODUCT_NAME,,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDER_PRODUCTS__PRIOR A
LEFT
JOIN INSTACART.PRODUCTS B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP
BY PRODUCT_ID,
B.PRODUCT_NAME
HAVING COUNT(DISTINCT ORDER_ID) > 10

[쿼리 실행 결과]

PRODUCT_ID로 데이터를 그룹핑한 뒤, 주문 번호를 카운트했다. 이처럼 그룹핑한 데이터에 조건을 생성하고 싶은 경우 HAVING을 이용하면 된다. 

위 쿼리를 실행하면 주문 건수가 10보다 큰 데이터만 출력된다.

 

어떤 상품들이 재구매율이 높은지 보기 위해 PRODUCTS테이블을 Join해 살펴보자.

SELECT A.PRODUCT_ID,
B.PRODUCT_NAME,
SUM(REORDERED)/SUM(1) REORDER_RATE,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDER_PRODUCTS__PRIOR A
LEFT
JOIN INSTACART.PRODUCTS B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP
BY PRODUCT_ID,
B.PRODUCT_NAME
HAVING COUNT(DISTINCT ORDER_ID) > 10

[쿼리 실행 결과]

다음 구매까지의 소요 기간과 재구매 관계

재구매와 관계가 있는 변수는 무엇이 있을까? 커머스 사이트에서 자주 재구매하는 상품에는 어떤 종류가 있는가? 가장 대표적으로 생수, 세제, 휴지와 같은 생활 필수품이 있을 것이다. 만약, 가정에 아이가 있다면, 기저귀 같은 상품도 일정한 주기로 구매할 것이다.

 

'고객이 자주 구매하는 상품은 그렇지 않은 상품보다 일정한 주기를 가질 것이다.'라는 가정을 세우고 수치를 살펴보자. 재구매율이 높은 순서대로 상품을 10가지 그룹으로 구분하고, 각 그룹에서의 구매 소요 기간의 분산을 구해 보자.

 

분산은 그 확률 변수가 기댓값에서 얼마나 떨어진 곳에 분포하는지를 나타내는 값이다. 즉 분산이 낮을수록 데이터가 평균에 모이게 되고, 분산이 클수록 관측치는 평균에서 멀리 분포한다.

 

먼저 다음과 같은 방법으로 상품별 재구매율을 계산하고, 가장 높은 순서대로 순위를 매겨보자.

---

SELECT *
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY RET_RATIO DESC) RNK
FROM
(SELECT PRODUCT_ID,
SUM(CASE WHEN REORDERED =1 THEN 1 ELSE 0 END)/COUNT(*) RET_RATIO
FROM INSTACART.ORDER_PRODUCTS__PRIOR
GROUP
BY 1) A) A

[쿼리 실행 결과]

고객 분석에서 했던 10분위 분석과 동일한 방법으로 각 상품을 10개의 그룹으로 나누자.

 

임시테이블 생성
CREATE TEMPORARY TABLE INSTACART.PRODUCT_REPURCHASE_QUANTILE AS
SELECT A.PRODUCT_ID,
CASE WHEN RNK <= 929 THEN 'Q_1'
WHEN RNK <= 1858 THEN 'Q_2'
WHEN RNK <= 2786 THEN 'Q_3'
WHEN RNK <= 3715 THEN 'Q_4'
WHEN RNK <= 4644 THEN 'Q_5'
WHEN RNK <= 5573 THEN 'Q_6'
WHEN RNK <= 6502 THEN 'Q_7'
WHEN RNK <= 7430 THEN 'Q_8'
WHEN RNK <= 8359 THEN 'Q_9'
WHEN RNK <= 9288 THEN 'Q_10' END RNK_GRP
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY RET_RATIO DESC) RNK
FROM
(SELECT PRODUCT_ID,
SUM(CASE WHEN REORDERED =1 THEN 1 ELSE 0 END)/COUNT(*) RET_RATIO
FROM INSTACART.ORDER_PRODUCTS__PRIOR
GROUP
BY 1) A) A
GROUP
BY 1,2
;

CREATE TEMPORARY TABLE INSTACART.ORDER_PRODUCTS__PIOR2 AS
SELECT PRODUCT_ID,
DAYS_SINCE_PRIOR_ORDER
FROM INSTACART.ORDER_PRODUCTS__PRIOR A
INNER
JOIN INSTACART.ORDERS B
ON A.ORDER_ID = B.ORDER_ID

위의 쿼리로 PRODUCT_ID별 분위 수를 계산할 수 있다. 계산된 상품별 분위 수는 PRODUCT_REPURCHASE_QUANTILE 테이블에 생성된다. 이제 각 분위 수별로 재구매 소요 시간의 분산을 구해본다.

• 상품별 부위 수: PRODUCT_REPURCHASE_QUANTILE
• 주문 소요 시간: INSTACART.ORDERS
• 주문 번호와 상품 번호: INSTACART.ORDER_PRODUCTS__PRIOR

먼저 INSTACART.ORDER_PRODUCTS__PRIOR에 ORDERS 테이블을 결합해 PRODUCT_ID의 DAYS_SINCE_PRIOR_ORDER를 구한다.

CREATE TEMPORARY TABLE INSTACART.ORDER_PRODUCTS__PIOR2 AS
SELECT PRODUCT_ID,
DAYS_SINCE_PRIOR_ORDER
FROM INSTACART.ORDER_PRODUCTS__PRIOR A
INNER
JOIN INSTACART.ORDERS B
ON A.ORDER_ID = B.ORDER_ID
;

 

다음으로 결합한 테이블에서 분위수, 상품별 구매 소요 기간의 분산을 계산한다.

SELECT A.RNK_GRP,
A.PRODUCT_ID,
VARIANCE(DAYS_SINCE_PRIOR_ORDER) VAR_DAYS
FROM INSTACART.PRODUCT_REPURCHASE_QUANTILE A
LEFT
JOIN INSTACART.ORDER_PRODUCTS__PRIOR2 B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP
BY 1,2
ORDER
BY 1;