RFM 분석 연습 1

 

 

 

https://solvesql.com/playground/

solvesql - 플레이그라운드

테스트셋: US E-Commerce Records 2020

(원본은 캐글데이터)

 

#가장 많이 판매된(구매건) 상품의 종류

select category
  , sub_category
  , count(*)
from records
group by category, sub_category
order by count(*)

 

#고객별 매출

select records.customer_id
  , count(records.order_id) total_item -- 주문한 품목 갯수
  , count(distinct records.order_id) total_order  -- 누적 주문 횟수
  , sum(records.sales) total_sales -- 주문 합계금액
  , sum(records.sales)/count(records.order_id) avg_item -- 품목별 평균 금액
from records
group by records.customer_id
order by total_order desc

생각해보니까 이건 customer_stats테이블 정보라서 같은 결과인지 비교해봄

select *
from customer_stats
order by cnt_orders desc

약간 순서는 다르지만 같군...

 

#고객 segment별 매출

select segment
  , sum(sales)
  , count(order_id)
  , count(distinct order_id)
from records
group by segment
order by sum(sales) desc

가장 많이 구매하는 고객군은 무엇인지?

-> 나아가서 고객군별로 가장 많이 구매하는 상품 종류나 가격대, 이런 것들을 분석해 볼 수 있을 듯

 

 

RFM의 본분으로 돌아가서

*R*ecency: 가장 마지막 구매 시간, 얼마나 최근
*F*requency: 총 거래/방문 횟수, 평균 거래/방문 시간, 얼마나 자주
*M*onetary: 총/평균 거래 금액, 얼마나 많이 지출

을 기준으로 생각해보자

 

1. 최근 3개월 이내 최고 구매 고객 (구매액)

    1-1. 최근 3개월 이내 최다 구매 고객 (구매건)

    1-2. 전체 고객 중 3개월 이내 구매 내역이 있는 고객의 비율

    1-3. 전체 기간 최고 구매 고객 상위 10% 중 3개월 이내 구매 내역이 있는 고객의 비율

2. 첫 구매 후 한 달 이내 재구매 고객

    2-1. 첫 구매 후 2주 이내 재구매 고객

    2-3. 2와 2-1의 재구매건 매출액

3. 거래별 고액 고객 구매

 

이것들이 실제로 유의미한 분석인지는 모르겠지만 일단 연습해보는게 중요하니까...!

 

가장 마지막 구매 날짜는 2020년 12월 30일임

select customer_id, sum(sales) 
from records
where order_date >= DATE_SUB(max(order_date),INTERVAL 6 MONTH)
group by customer_id
order by sum(sales) desc

근데 돌리면 invalid use of group function 에러가 난다ㅠㅠ

 

검색해보니 

 

Aggregate functions like AVG(), COUNT(), MAX(), MIN(), and many others can’t be used in the WHERE() clause

There are two ways you can solve this error in MySQL:
- Wrap the aggregate function call in a subquery

- Use the HAVING clause for the aggregate function call

 

라네.. 흠

 

# 서브쿼리

select customer_id, sum(sales) 
from records
where order_date >= DATE_SUB(
  (select max(order_date) from records)
  ,INTERVAL 3 MONTH)
group by customer_id
order by sum(sales) desc

 

#having

select customer_id
  , sum(sales)
  , count(order_id)
from records
group by customer_id, order_date
having order_date >= DATE_SUB(max(order_date), INTERVAL 3 MONTH)
order by sum(sales) desc

결과값이 다르다.. 근데 having은 내가 쓰면서도 이게 맞나싶음

에러나서 그냥 group by에 order_date 넣긴했는데 이게 맞나?

-> 최근 3개월 이내 구매한 고객의 모든 구매액을 보여주는 것 같다... 

    (근데 또 그렇다기엔 HL-15040 고객의 having 결과가 구매액이 더 적다 뭐지?)

 

맞네.. 1-2, 1-3을 풀 때 이렇게 풀어야겠다

 

#1-1. 최근 6개월 이내 최다 구매건

customer_id	sum(sales)	count(order_id)
SV-20365	8459.936	20
JL-15835	5018.47	16
Dp-13240	3885.871	16
FM-14290	1986.19	13
AC-10615	2293.798	13
EP-13915	1322.518	13
CD-12280	1205.584	13
SP-20620	3136.667	12
KH-16510	4021.235	12

 

#1-2. 전체 고객 중 3개월 이내 구매 내역이 있는 고객의 비율

select (select count(*)
        from customer_stats
        where last_order_date >= 
          (select DATE_SUB(max(order_date), INTERVAL 3 MONTH)
          from records) --3개월 이내 구매고객
        order by cnt_orders desc)/
        (select count(*)
        from customer_stats) * 100 -- 전체 고객 
        AS '3개월 이내 구매고객 비율'

더 깔끔하게 푸는 방법이 있을 것 같다.. 이상해

-> percent_rank 함수가 있던데 윈도우 함수 공부하고 다시 풀어보기

 

 

갑자기 최고 구매 고객의 고객군을 알아보고 싶어졌는데

select customer_id, distinct segment
from records
where customer_id IN ('TA-21385', 'HL-15040', 'SV-20365')

불가 -> 원하는 결과 얻으려면 group by에 customer_id, segment 추가해야함