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

Instacart Market Basket Analysis | Kaggle

 

데이터 생성

이번에는 Instacart라는 e-commerce 회사의 데이터를 분석해 보는 시간을 가져보자.

먼저 해당 데이터 세트는 kaggle(Machine Learning Competition)에 존재하는 데이터 세트이다.

데이터 세트에는 aisles, departments, order_product_prior, orders, products 테이블이 존재한다.(order_products_train은 사용하지 않는다.)

• aisles, departments는 상품의 카테고리를 의미
• order_products_prior는 각 주문 번호의 상세 구매 내역
• orders는 주문 대표 정보
• products는 상품 정보

지표 추출

재구매와 관련된 요인들을 찾기 전 해당 Business의 전반적인 현황을 파악하자.

1. 전체 주문 건수
2. 구매자 수
3. 상품별 주문 건수
4. 카트에 가장 먼저 넣는 상품 10개
5. 시간별 주문 건수
6. 첫 구매 후 다음 구매까지 걸린 평균 일수
7. 주문 건당 평균 구매 상품 수(UPT, Unit Per Transaction)
8. 인당 평균 주문 건수
9. 재구매율이 가장 높은 상품 10개
10. Department별 재구매율이 가장 높은 상품 10개

---

전체 주문 건수

SELECT COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDERS;​

[쿼리 실행 결과]

 

구매자 수

SELECT COUNT(DISTINCT USER_ID) BU
FROM INSTACART.ORDERS;

[쿼리 실행 결과]

 

상품별 주문 건수

 

주문번호(ORDER_ID)는 ORDER_PRODUCTS_PRIOR라는 테이블에 존재하고, 상품명(PRODUCTS_NAME)은 PRODUCTS라는 테이블에 존재한다.

→ 2개의 테이블을 결함(JOIN)해야 한다.

SELECT *
FROM INSTACART.ORDER_PRODUCTS__PRIOR A
LEFT
JOIN INSTACART.PRODUCTS B
ON A.PRODUCT_ID = B.PRODUCT_ID
;

[쿼리 실행 결과]

이제 결합한 결과에서 PRODUCT_NAME으로 데이터를 그룹핑하고, ORDER_ID를 카운트하면 된다. 

이때 주문 번호(ORDER_ID)는 동일한 값이 중복으로 존재하므로 중복을 제거하고 집계해야 한다.

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

[쿼리 실행 결과]

 

장바구니에 가장 먼저 넣는 상품 10개

 

ORDER_PRODUCTS_PRIOR라는 테이블을 보면 ADD_TO_CART_ORDER라는 컬럼이 존재한다.

카트에 가장 먼저 담기는 상품을 조회하기 위해서 상품별로 가장 먼저 카트에 담긴 경우를 카운트 해야한다.

먼저 ORDER_PRODUCTS_PRIOR의 PRODUCT_ID별로 가장 먼저 담긴 경우(ADD_TO_CART_ORDER=1)에는 1을 출력하는 칼럼을 생성해 보자.

SELECT PRODUCT_ID,
CASE WHEN ADD_TO_CART_ORDER = 1 THEN 1 ELSE 0 END F_1ST
FROM INSTACART.ORDER_PRODUCTS__PRIOR
;

[쿼리 실행 결과]

다음으로 상품 번호(PRODUCT_ID)로 데이터를 그룹핑하고 위의 F_1ST 칼럼을 합하면, 상품별로 장바구니에 가장 먼저 담긴 건수를 계산할 수 있다.

SELECT PRODUCT_ID,
SUM(CASE WHEN ADD_TO_CART_ORDER = 1 THEN 1 ELSE 0 END) F_1ST
FROM INSTACART.ORDER_PRODUCTS__PRIOR
GROUP
BY
1;

[쿼리 실행 결과]

이제 F_1ST(장바구니에 가장 먼저 담긴 건수)로 데이터에 순위를 매긴다.

SELECT *,
ROW_NUMBER() OVER(ORDER BY F_1ST DESC) RNK
FROM
(SELECT PRODUCT_ID),
SUM(CASE WHEN ADD_TO_CART_ORDER =1 THEN 1 ELSE 0 END) F_1ST
FROM INSTACART.ORDER_PRODUCTS__PRIOR
GROUP
BY 1)A
;

[쿼리 실행 결과]

하지만 !! 우리는 1 ~ 10 위의 상품 번호만 궁금하므로 WHERE 절에 조건을 추가해 RNK가 1 ~ 10 사이인 데이터만 출력한다. 

이때 RNK는 SELECT문에서 새롭게 생성한 칼럼이므로 WHERE절에서 바로 사용 XX 따라서 SUBQUERY로 사용해 조건을 생성해 보자.

SELECT *
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY F_1ST DESC) RNK
FROM
(SELECT PRODUCT_ID,
SUM(CASE WHEN ADD_TO_CART_ORDER = 1 THEN 1 ELSE 0 END) F_1ST
FROM INSTACART.ORDER_PRODUCTS__PRIOR
GROUP
BY 1) A) BASE
WHERE RNK BETWEEN 1 AND 10;

[쿼리 실행 결과]

ORDER BY를 이용하면 간단히 상위 10개의 데이터를 호출할 수 있다.

SELECT PRODUCT_ID,
SUM(CASE WHEN ADD_TO_CART_ORDER = 1 THEN 1 ELSE 0 END) F_1ST
FROM INSTACART.ORDER_PRODUCTS_PRIOR
GROUP
BY 1
ORDER
BY 2 DESC LIMIT 10
;

[쿼리 실행 결과]

 

시간별 주문 건수

 

ORDERS의 ORDER_HOUR_OF_DAY로 그룹핑한 뒤, ORDER_ID를 카운트한다.

SELECT ORDER_HOUR_OF_DAY,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDERS
GROUP
BY 1
ORDER
BY 1;

[쿼리 실행 결과]

 

첫 구매 후 다음 구매까지 걸린 평균 일수

 

ORDERS 테이블에서 DAYS_SINCE_PRIOR_ORDER는 이전 주문이 이루어진 지 며칠 뒤에 구매가 이루어졌는지를 나타내는 값이다. 이 기간을 평균하면 첫 구매 후 다음 구매까지 걸린 평균 일수를 구할 수 있다.

SELECT AVG(DAYS_SINCE_PRIOR_ORDER) AVG_RECENCY
FROM INSTACART.ORDERS
WHERE ORDER_NUMBER =2;

[쿼리 실행 결과]

 

주문건당 평균 구매 상품 수(UPT, UnitPer Transaction)

SELECT COUNT(PRODUCT_ID)/COUNT(DISTINCT ORDER_ID) UPT
FROM INSTACART.ORDER_PRODUCTS__PRIOR;

[쿼리 실행 결과]

 

인당 평균 주문 건수

SELECT COUNT(DISTINCT ORDER_ID)/COUNT(DISTINCT USER_ID) AVG_F
FROM INSTACART.ORDERS;

[쿼리 실행 결과]

 

재구매율이 가장 높은 상품 10개

상품별로 재구매율을 계산한 뒤, 재구매율을 기준으로 랭크를 계산한다.

상품 별 재무구매율 계산

SELECT PRODUCT_ID,
SUM(CASE WHEN REORDERED=1 THEN 1 ELSE 0 END)/COUNT(*) RET_RATIO
FROM INSTACART.ORDER_PRODUCTS__PRIOR
GROUP
BY 1
;

[쿼리 실행 결과]

재구매율로 랭크(순위) 열 생성하기

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
;

[쿼리 실행 결과]

재구매율(Top10) 상품 추출

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
WHERE RNK BETWEEN 1 AND 10
;

 

Department별 재구매율이 가장 높은 상품 10개

Department별로 재구매율이 높은 상품을 추출하려면, Department별, 상품별 재구매율을 계산한다.

다음 Department로 Partition을 생성한 뒤 랭크를 계산하면 된다. 앞의 예제에서 추가된 내용은 ORDER_PRODUCTS_PRIOR에 PRODUCTS테이블을 조인해 DEPARTMENT를 가져와야 한다는 점과 DEPARTMENT로 PARTITION을 생성해 순위를 매겨야 한다는 점이다.

 

SELECT *
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY RET_RATIO DESC) RNK
FROM
(SELECT	C.DEPARTMENT,
PRODUCT_ID,
SUM(CASE WHEN REORDERED =1 THEN 1 ELSE 0 END)/COUNT(*) RET_RATIO
FROM INSTACART.ORDER_PRODUCTS__PRIOR A
LEFT
JOIN INSTACART.PRODUCTS B
ON A.PRODUCT_ID = B.PRODUCT_ID
LEFT
JOIN INSTACART.DEPARTMENTS C
ON B.DEPARTMENT_ID = C.DEPARTMENT_ID
GROUP
BY 1,2) A) A
WHERE RNK BETWEEN 1 AND 10 ;

 

⛔️Error Code: 1052. Column 'PRODUCT_ID' in field list is ambiguous

열의 모호성 때문에 에러가 뜨는데.. 왜 여기서만 막히는지 모르겠다 ㅜㅜ