[식품 배송 데이터 분석] 매출 지표 분석

본 포스팅은 아래 도서의 내용을 스터디, 참고했습니다.

 

instacart라는 회사의 데이터를 기반으로 분석을 수행해 보자.

먼저, 주요 지표를 추출하는 것으로 시작한다. 보통 다음과 같은 지표로 비즈니스의 전반적인 현황을 파악한다.

---

1. 전체 주문 건수

2. 구매자 수

3. 상품별 주문 건수

4. 카트에 가장 먼저 넣는 상품 10개 

5. 시간별 주문 건수

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

7. 주문 건 당 평균 구매 상품 수(UPT; Unit Per Transaction)

8. 인당 평균 주문 건수

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

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

 

이 중, 구하기 조금 까다로웠던 지표 몇 가지만 살펴보겠다.

 

🛒 카트에 가장 먼저 넣는 상품 10개

고객들이 우리 쇼핑몰을 이용할 때 어떤 상품을 가장 먼저 담을까? 에 대한 의문을 해결해 보자.

• 먼저 서브쿼리를 사용해서, 상품별(product_id)로, product_id 가 1인 경우만 집계(합)한다. case when을 써서 처리한다.
• 위 쿼리를 서브 쿼리로 하여, row_number()로 가장 먼저 넣어진 경우를 기준으로 order by 하여 등수를 매긴다.
• 이후, 10위까지만 출력한다.

select *
from (select *, row_number() over(order by F_1st desc) as RNK
		from (  select product_id, 
				sum(case when add_to_cart_order = 1 then 1 else 0 end) as F_1st
				from order_products__prior
				group by 1) a) b
where RNK <= 10;

 

 

🛒 첫 주문 후 다음 주문까지 걸린 평균 일 수

• 테이블과 칼럼을 잘 이해하고 있어야 풀 수 있다.
• days_since_prior_order의 평균을 구하면 되는데, 이때 첫 주문 후 다음 주문, 즉 order_number 가 2인 건들 만 봐야 한다.
  • where 절에 해당 필터를 걸어준다.

select avg(days_since_prior_order) as AVG_recency
from orders
where order_number = 2;

 

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

• 상품별로 재구매율을 구해야 한다. 이때 case when을 사용하여 재구매인 경우만 뽑아 sum 해주는 방식을 사용한다.
  • group by 는 상품 id
  • reordered 가 1인 경우만 1 (재구매이므로), 재구매가 아닌 경우는 0으로 하여 sum
  • 재구매가 일어난 결과를 전체 구매 수(count(*))로 나누면 재구매율
• 다시 서브쿼리를 활용하여 row_number()로 순위를 구하고
• 10위까지를 Limit으로 자른다.

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(*) as RET_RATIO
	from order_products__prior
	group by 1) a
limit 10;