사용 데이터 Instacart Market Basket Analysis
이번에는 식품 배송 관련 데이터를 모아, 여러 특성을 분석 할 것이다.
문제: 식품배송 데이터 분석
문제는 세 가지다.
식품처럼 신선도가 중요한 제품 같은 경우는 이전 자동차, 옷과는 달리 재고 관리, 구매 주기가 중요하다. 한번 살펴보자
이 페이지에선 지표추출을 풀어본다.
0.ERD
이전과 달리 ERD를 CSV 그대로 불러와 아무런 연결이 없다.
살펴보면 aisles와 departments는 상품의 카테고리를 의미하고, order_products__prior는 각 주문번호의 상세 내용, orders는 기본 주문정보, products는 상품 정보이다.
시작하자.
1
USE instacart;
1. 지표추출
해당 비즈니스의 전반적 상황을 훓어보자
1. 전체 주문, 구매자 수
1
2
3
4
SELECT
COUNT(DISTINCT order_id) AS Order_CNT
, COUNT(DISTINCT user_id) AS User_CNT
FROM orders;
order_id는 고유값이라 중복 될 수는 없지만, 하나의 주문에서 두개의 상품을 결제했을 경우 두 정보로 나뉘어 저장된다. 따라서 중복치를 제거하고 전체 주문 건수를 확인한다.
전체 주문 건수는 3,220건, 구매자는 3159명이다.
2. 상품별 주문 건수
1
2
3
4
5
6
7
8
9
10
SELECT
A.product_id
, B.product_name
, COUNT(order_id) AS Order_CNT
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
GROUP BY 1,2
ORDER BY 3 DESC
;
이제 이 데이터에서 어떤 제품을 많이 구매하는지 확인해보자
주문 정보는 order_products__prior테이블에, 상품 정보는 products 테이블에 있다. 두 테이블을 JOIN 하고 정보를 보자.
1위는 바나나, 2위는 유기농 바나나묶음… 바나나를 좋아하나보다
3. 장바구니에 가장 먼저 넣는 상품 10개
장바구니에 가장 먼저 넣는 상품을 알 수 있는 열은 order_products__prior의 add_to_cart_order 열이다.
아무래도 장바구니에 넣는 순서가 구매 우선순위와 비슷하니 이런걸 찾는거겠지.
그나저나 쇼핑몰에선 이런 정보도 수집하는구나.. 신기하다.
아무튼 add_to_cart_order 열에서 1은 첫 번째, 2는 두 번째로 ‘가장 먼저 넣는 상품’ 은 1을 찾으면 되고, 그 상품을 순서댈로 10개 찾아야 하니 1이 많은 열을 찾아보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
*
FROM (
SELECT
*
, RANK()OVER(ORDER BY CNT DESC) AS RNK
FROM (
SELECT
A.product_id
, B.product_name
, SUM(CASE WHEN A.add_to_cart_order = 1 THEN 1 ELSE 0 END) AS CNT
FROM order_products__prior A
LEFT JOIN products B
ON A.product_id = B.product_id
GROUP BY 1,2
) A
)A
WHERE RNK <= 10
;
역시 바나나가 마찬가지로 1위와 2위를 차지했다.
바나나를 엄청 좋아하나보다
4. 시간별 주문 건수
인터넷 쇼핑몰이면 어떤 시간에 가장 많이들 구매할까?
orders테이블의 order_hour_of_day열이 시간에 관련된 데이터 같다.
1
2
3
4
5
6
7
SELECT
order_hour_of_day
, COUNT(DISTINCT order_id) AS CNT
FROM orders
GROUP BY 1
ORDER BY 2 DESC
;
13시가 피크, 9시 부터 17시까지가 주문량이 많고 그 외 시간은 좀 적다.
인터넷 쇼핑몰인데 생각보다 결과가 오프라인 매장처럼 나왔다.