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

[이전 글] MYSQL을 사용하여 데이터를 살펴보기

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

 

이전까지 매출과 관련된 지표들을 주로 살펴보았다면, 이제는 구매자에 집중해 데이터를 살펴보자.

먼저 10분위 분석을 통해 서비스의 주문 수가 VIP 고객에게 얼마나 집중되어 있는지 살펴보자.

구매자 분석(10분위 분석)

• 10분위 분석이란?

전체를 10분위로 나누어 각 분위 수에 해당하는 집단의 성질을 나타내는 방법.

10분위 분석을 진행하려면 먼저 각 구매자의 분위 수를 구해야 한다. 우리는 고객들의 주문 건수를 기준으로 분위 수를 나눈다.

 

---

고객별 주문 건수에 따라 순위

SELECt *,
ROW_NUMBER() OVER(ORDER BY F DESC) RNK
FROM
(SELECT USER_ID,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDERS
GROUP
BY 1) A

[쿼리 실행 결과]

 

각 등수에 다른 분위 수로 설정

SELECT *,
CASE WHEN RNK BETWEEN 1 AND 316 THEN 'Quantile_1'
WHEN RNK BETWEEN 317 AND 632 THEN 'Quantile_2'
WHEN RNK BETWEEN 633 AND 948 THEN 'Quantile_3'
WHEN RNK BETWEEN 949 AND 1264 THEN 'Quantile_4'
WHEN RNK BETWEEN 1265 AND 1580 THEN 'Quantile_5'
WHEN RNK BETWEEN 1581 AND 1895 THEN 'Quantile_6'
WHEN RNK BETWEEN 1896 AND 2211 THEN 'Quantile_7'
WHEN RNK BETWEEN 2212 AND 2527 THEN 'Quantile_8'
WHEN RNK BETWEEN 2528 AND 2843 THEN 'Quantile_9'
WHEN RNK BETWEEN 2844 AND 3159 THEN 'Quantile_10' END quantile
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY F DESC) RNK
FROM
(SELECT USER_ID,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDERS
GROUP
BY 1) A) A

[쿼리 실행 결과]

 

각 분위 수별 전체 주문 건수의 합 구하기

CREATE TEMPORARY TABLE INSTACART.USER_QUANTILE AS
SELECT *,
CASE WHEN RNK <= 316 THEN 'Quantile_1'
WHEN RNK <= 632 THEN 'Quantile_2'
WHEN RNK <= 948 THEN 'Quantile_3'
WHEN RNK <= 1264 THEN 'Quantile_4'
WHEN RNK <= 1580 THEN 'Quantile_5'
WHEN RNK <= 1895 THEN 'Quantile_6'
WHEN RNK <= 2211 THEN 'Quantile_7'
WHEN RNK <= 2527 THEN 'Quantile_8'
WHEN RNK <= 2843 THEN 'Quantile_9'
WHEN RNK <= 3159 THEN 'Quantile_10' END quantile
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY F DESC) RNK
FROM
(SELECT USER_ID,
COUNT(DISTINCT ORDER_ID) F
FROM INSTACART.ORDERS
GROUP
BY 1) A) A

SELECT QUANTILE,
SUM(F) F
FROM INSTACART.USER_QUANTILE
GROUP
BY 1

[쿼리 실행 결과]

 

주문 전체 건수

SELECT SUM(F) FROM INSTACART.USER_QUANTILE;

[쿼리 실행 결과]

 

각 분위 수의 주문 건수를 전체 주문건수로 나누기

SELECT QUANTILE,
SUM(F)/3220 F
FROM INSTACART.USER_QUANTILE
GROUP
BY 1

[쿼리 실행 결과]

 

결과를 보면 각 분위 수별로 주문 건수가 거의 균등하게 분포되어 있다.

즉 해당 서비스는 매출이 VIP에게 집중되지 않고, 전체 고객에 고르게 분포되어 있음을 알 수있다.