RFM 분석 연습 2

저번에 이어서 남은 RFM

 

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

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

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

3. 거래별 고액 고객 구매

 

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

select
  o.customer_id,
  c.first_order_date,
  o.order_date
from
  records as o
  join customer_stats as c on o.customer_id = c.customer_id
group by
  o.customer_id,
  o.order_date,
  c.first_order_date
having o.order_date > c.first_order_date and 
  o.order_date <= DATE_ADD(c.first_order_date, INTERVAL 1 MONTH)   
order by
  c.first_order_date,
  o.order_date

customer_id	first_order_date	order_date
DP-13390	2020-01-01	2020-01-06
TM-21010	2020-01-20	2020-01-29
CV-12805	2020-01-20	2020-02-18
BD-11770	2020-01-29	2020-02-06
CK-12595	2020-01-29	2020-02-23
HW-14935	2020-02-20	2020-03-20
DB-13660	2020-02-28	2020-03-25
KD-16615	2020-03-02	2020-03-16
KF-16285	2020-03-13	2020-04-08
CL-12565	2020-03-17	2020-03-21
SA-20830	2020-03-19	2020-04-03
SA-20830	2020-03-19	2020-04-09
ND-18370	2020-03-19	2020-04-16
RD-19720	2020-03-20	2020-03-29

3개월 이내 여러 번 구입했으면 다 출력함. 나는 한 달 이내 재구매 고객이 몇 명인지 보고싶은건데..

 

select
  distinct o.customer_id
from
  records as o
  join customer_stats as c on o.customer_id = c.customer_id
group by
  o.customer_id,
  o.order_date,
  c.first_order_date
having o.order_date > c.first_order_date and 
  o.order_date <= DATE_ADD(c.first_order_date, INTERVAL 1 MONTH)

잘 나오긴 하지만 select 에 count()까지 붙이면 cutomer_id의 날짜별 주문 건수가 나오므로

모든 행의 값이 1, 레코드 수도 늘어남

-> 어떻게 풀어야하는가...

 

 

 

 

 

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

DATE_ADD에서 인터벌을 2 week으로만 바꾸면 되니까 생략

 

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

select
  o.customer_id,
  o.order_date,
  round(sum(o.sales),3) as reorder_sale
from
  records as o
  join customer_stats as c on o.customer_id = c.customer_id
group by
  o.customer_id,
  o.order_date,
  c.first_order_date
having o.order_date > c.first_order_date and 
  o.order_date <= DATE_ADD(c.first_order_date, INTERVAL 1 MONTH)
order by reorder_sale desc

customer_id	order_date	reorder_sale
JW-15220	2020-10-13	5325.884
KF-16285	2020-04-08	2633.51
SP-20620	2020-09-09	2457.141
CL-12565	2020-03-21	1805.88
SL-20155	2020-10-21	1702.03
MC-17845	2020-12-10	1669.6
AR-10825	2020-07-21	1629.712
ND-18460	2020-06-10	1347.52
TS-21610	2020-06-16	1231.5
LC-17140	2020-09-22	1083.07
LS-16975	2020-11-28	1071.74
JG-15310	2020-12-22	916.14
MB-18085	2020-05-06	817.829
RB-19435	2020-12-11	798.93

역시나 중복 재구매건도 나온다

 

# 3. 거래별 고액 구매 고객

여기서 거래별 구매액이란 고객의 order_id 별 총 구매액 (sum(sales))를 말한다 

select customer_id
  , order_id
  , round(sum(sales),3) as order_sale 
from records
group by customer_id, order_id
order by sum(sales) desc

customer_id	order_id	order_sale
RB-19360	CA-2017-140151	14052.48
TA-21385	CA-2017-127180	13716.458
HL-15040	CA-2017-166709	10499.97
GT-14635	US-2017-168116	8167.42
SV-20365	CA-2017-100111	7359.918
AR-10540	CA-2017-138289	5802.7
TS-21370	CA-2017-143112	5509.142
JW-15220	CA-2017-135909	5325.884
NC-18535	CA-2017-149881	5282.648
JA-15970	CA-2017-158379	4749.327
CP-12085	US-2017-167402	4619.33
PO-18850	CA-2017-129021	4590.344
HW-14935	CA-2017-121559	4472.32
TP-21415	CA-2017-118892	4416.174