[PostgreSQL] 사용자 로그데이터(Log Data) 매출분석(3) - RFM 분석

1. 로그데이터를 분석하고 카테고리/연령별로 집계하는 포스팅

ysyblog.tistory.com/146?category=1176025

[PostgreSQL] 사용자 로그데이터(Log Data)분석(1) - 사용자들의 특징 찾기(사용자 그룹화, 카테고리별

 

2. 로그데이터를 활용하여 DECILE분석하기

ysyblog.tistory.com/147?category=1176025

[PostgreSQL] 사용자 로그데이터(Log Data)분석(2) - DECILE분석 (사용자 그룹화 하기)

 

해당 포스팅은 위 두개의 포스팅의 내용이 이어집니다.

RFM 분석

RFM분석은 Recency, Frequency, Moentary 분석을 의미합니다.

1. Recency(최근 구매일) : 최근 구매한 사용자를 우량 고객으로 취급

2. Frequency(구매 횟수) : 구매 횟수가 많을수록 우량 고객으로 취급

3. Moentary(구매금액) : 구매금액이 높을 수록 우량고객으로 취급

먼저 사용자별로 위 세가지(Recency, Frequency, Moentary)를 집계하겠습니다.

with user_rfm as(
	select customerid
		,MAX(date) as recent_date
		,CURRENT_DATE - MAX(date::date) as recency
		,count(date) as Frequency
		,round(sum(price)::numeric,2) as Moentary
	from online_sales
	group by customerid)
select *
from user_rfm;

 

RFM의 단계/랭크를 정의

- 위 3가지 지표를 5개의 그룹으로 나누어 사용자를 파악해보겠습니다.(RFM분석에서는 일반적으로 사용자를 5개그룹으로 나눕니다.)

- RFM기준표

랭크	Recency	Frequency	Moentary
5	2주(15일) 이내 재구매	40회이상	1000달러 이상
4	1달(30일) 이내 재구매	30회이상	700달러 이상
3	1달반(45일) 이내 재구매	20회이상	400달리 이상
2	2달(60일) 이내 재구매	10회이상	100달러 이상
1	해당 기간동안 재구매 하지 않음	10회미만	100달러 미만

with user_rfm as(
	select customerid
		,MAX(date) as recent_date
		,CURRENT_DATE - MAX(date::date) as recency
		,count(date) as Frequency
		,round(sum(price)::numeric,2) as Moentary
	from online_sales
	group by customerid
)
, rfm_rank as(
	select customerid
		,recent_date
		,recency
		,Frequency
		,Moentary
		,CASE
			WHEN recency < 15 Then 5
			WHEN recency < 30 Then 4
			WHEN recency < 45 Then 3
			WHEN recency < 60 Then 2
			Else 1 
		End as r
		,CASE
			WHEN Frequency >= 40 Then 5
			WHEN Frequency >= 30 Then 4
			WHEN Frequency >= 20 Then 3
			WHEN Frequency >= 10 Then 2
			Else 1 
		End as F
		,CASE
			WHEN Moentary > 1000 Then 5
			WHEN Moentary > 700 Then 4
			WHEN Moentary > 400 Then 3
			WHEN Moentary > 100 Then 2
			Else 1 
		End as m
	from user_rfm
)
select *
from rfm_rank;

이제 각 그룹에 속한 사람 수를 확인할 차례입니다. 1~5까지 인덱스를 저정하는 rfm_index테이블을 만든다음 위에서 만든 rfm_rank를 조인하는 방식입니다.

with user_rfm as(
	select customerid
		,MAX(date) as recent_date
		,CURRENT_DATE - MAX(date::date) as recency
		,count(date) as Frequency
		,round(sum(price)::numeric,2) as Moentary
	from online_sales
	group by customerid
)
, rfm_rank as(
	select customerid
		,recent_date
		,recency
		,Frequency
		,Moentary
		,CASE
			WHEN recency < 15 Then 5
			WHEN recency < 30 Then 4
			WHEN recency < 45 Then 3
			WHEN recency < 60 Then 2
			Else 1 
		End as r
		,CASE
			WHEN Frequency >= 40 Then 5
			WHEN Frequency >= 30 Then 4
			WHEN Frequency >= 20  Then 3
			WHEN Frequency >= 10 Then 2
			Else 1 
		End as F
		,CASE
			WHEN Moentary > 1000 Then 5
			WHEN Moentary > 700 Then 4
			WHEN Moentary > 400 Then 3
			WHEN Moentary > 100 Then 2
			Else 1 
		End as m
	from user_rfm
)
, rfm_index as(
	select 1 as rfm_index
	union all select 2 as rfm_index
	union all select 3 as rfm_index
	union all select 4 as rfm_index
	union all select 5 as rfm_index
)
, rfm_flag as(
	select m.rfm_index
	,case when m.rfm_index = r.r then 1 else 0 end as r_flag
	,case when m.rfm_index = r.f then 1 else 0 end as f_flag
	,case when m.rfm_index = r.m then 1 else 0 end as m_flag
	from rfm_index as m
		cross join
	rfm_rank as r)
select rfm_index
	,sum(r_flag) as r
	,sum(f_flag) as f
	,sum(m_flag) as m
from rfm_flag
group by rfm_index
order by rfm_index desc;

 

사용자를 1차원으로 구분

위의 방법은 사용자를 5*5*5 총 125개의 그룹으로 구분한 것입니다. 이는 너무 많기 때문에 1차원으로 바꾸어서 사용자를 15개의 그룹으로 나누어 보겠습니다.

with user_rfm as(
	select customerid
		,MAX(date) as recent_date
		,CURRENT_DATE - MAX(date::date) as recency
		,count(date) as Frequency
		,round(sum(price)::numeric,2) as Moentary
	from online_sales
	group by customerid
)
, rfm_rank as(
	select customerid
		,recent_date
		,recency
		,Frequency
		,Moentary
		,CASE
			WHEN recency < 15 Then 5
			WHEN recency < 30 Then 4
			WHEN recency < 45 Then 3
			WHEN recency < 60 Then 2
			Else 1 
		End as r
		,CASE
			WHEN Frequency >= 40 Then 5
			WHEN Frequency >= 30 Then 4
			WHEN Frequency >= 20  Then 3
			WHEN Frequency >= 10 Then 2
			Else 1 
		End as F
		,CASE
			WHEN Moentary > 1000 Then 5
			WHEN Moentary > 700 Then 4
			WHEN Moentary > 400 Then 3
			WHEN Moentary > 100 Then 2
			Else 1 
		End as m
	from user_rfm
)
select r+f+m as total_rank
	,r,f,m
	,count(customerid)
from rfm_rank
group by r,f,m
order by total_rank desc, r desc, f desc, m desc;

만약 랭크별로 사용자를 집계하고 싶다면 위의 쿼리에서 아래 SELECT부분을 다음과 같이 바꾸면 됩니다.

select r+f+m as total_rank
	,count(customerid)
from rfm_rank
group by total_rank
order by total_rank desc;

 

2차원으로 사용자 구분하기

rfm 지표를 2개 이용해서 자료를 2차원으로 만들면 좀더 전체적으로 보기가 쉬워집니다.

이번에는 F와 M을 가지고 2차원 표를 만들어보겠습니다.

with user_rfm as(
	select customerid
		,MAX(date) as recent_date
		,CURRENT_DATE - MAX(date::date) as recency
		,count(date) as Frequency
		,round(sum(price)::numeric,2) as Moentary
	from online_sales
	group by customerid
)
, rfm_rank as(
	select customerid
		,recent_date
		,recency
		,Frequency
		,Moentary
		,CASE
			WHEN recency < 15 Then 5
			WHEN recency < 30 Then 4
			WHEN recency < 45 Then 3
			WHEN recency < 60 Then 2
			Else 1 
		End as r
		,CASE
			WHEN Frequency >= 40 Then 5
			WHEN Frequency >= 30 Then 4
			WHEN Frequency >= 20  Then 3
			WHEN Frequency >= 10 Then 2
			Else 1 
		End as F
		,CASE
			WHEN Moentary > 1000 Then 5
			WHEN Moentary > 700 Then 4
			WHEN Moentary > 400 Then 3
			WHEN Moentary > 100 Then 2
			Else 1 
		End as m
	from user_rfm
)
select concat('r_', m) as m_rank
	,count(case when f=5 then 1 end) as f_5
	,count(case when f=4 then 1 end) as f_4
	,count(case when f=3 then 1 end) as f_3
	,count(case when f=2 then 1 end) as f_2
	,count(case when f=1 then 1 end) as f_1
from rfm_rank
group by m
order by m_rank desc;

 

해당 포스팅은 다음의 책을 활용하여 작성하였습니다.

- 가사키 나가토, 다미야 나오토,  『데이터 분석을 위한 SQL 레시피』, 한빛미디어, p186~232