[Big Query] RFM분석 : 세부적으로 사용자 그룹핑하기

 Today's Topic

• RFM분석의 개요
• RFM분석 쿼리기반의 실습

- Used Function

• CASE구문
• UNION ALL

 

안녕하세요~!!

오늘은 RFM분석에 대해 살펴볼 예정입니다.

지난 시간에는 고객을 10등급으로 그룹화해서 Decile분석을 해 보았습니다.

Decile분석은 '구매액'이라는 한 가지의 조건만을 고려했기 때문에 과거에는 구매액이 많았으나, 최근에는 적다던지 하는 경우(휴면고객)도 상위권에 포함될 수 있다는 단점이 보였습니다.

하지만 오늘 살펴볼 rfm분석은 이러한 점을 보완하여 좀 더 세부적으로 그루핑할 수 있습니다.

그렇다면 지금 바로 살펴보도록 하겠습니다.

 

---

1.  RFM 분석의 개요

 

RFM분석의 개념은 다음과 같다.

 

R: Recency (최근 구매일)

-> 최근 구매일을 기준으로 한다. 즉, 최근 특정 상품을 많이 구매한 고객일수록 우량 고객으로 취급한다.

 

F: Frequency (구매 횟수)

->  고객들이 구매한 횟수를 카운팅하여, 많을수록 우량고객으로 취급한다.

 

M: Momentory (구매 금액의 합계)

-> 고객의 구매 금액 합계를 집계하고, 해당 금액이 많을수록 우량고객으로 취급한다.

 

 

RFM분석에서는 최근 구매일이 언제인지, 구매횟수가 얼마나 되는지, 그 금액의 합계는 얼마나 되는지를 고려한다그래서 Decile분석과 달리, 한번에 100만원을 지불한 고객과, 10만원씩 10번 지불한 고객이 다른 그룹에 묶이게 된다.

 

RFM분석은 3지표씩 5그룹으로 나뉘는 것이 일반적이다. 그렇기에 총 3^5=125개의 그룹으로 나뉜다.

 

그렇다면 이 rfm분석은 big query에서 어떻게 진행할 수 있는지 확인해보도록 하자.

 

---

2.  RFM 분석 실습 

RFM분석의 쿼리 작성 절차는 다음과 같다.

RFM분석 절차
1) 교객별로 RFM을 집계한다.
2) 고객들의 RFM랭크를 계산한다.
3) 각 그룹의 사람 수를 확인한다.

 

그리고 오늘 사용할 테이블은 다음과 같다. 참고로, 이전 decile분석때와 같은 테이블이다.

테이블 이름은 purchase이며, 칼럼 설명은 다음과 같다.

데이터출처: https://data.seoul.go.kr/, 일부 가공하였습니다.

user_id: 유저의 id

reg_date: 가입일자

amount: 구매금액

stamp: 구매일자

 

 

1) 고객별로 RFM을 집계한다.

rfm분석을 하기 위해서는 제일 먼저 고객별로 rfm을 집계하여야 한다.

하지만 바로 RFM 분류를 나누는 것이 아니고, 그 전처리 단계에 해당한다고 본다.

즉, 해당 쿼리를 실행하면 고객의 최근 구매 일자, 최근구매일자와 현재날짜와의 차이, 구매횟수, 구매금액을 확인할 수 있게 된다.

--4-1) 사용자별로 RFM을 집계하기
WITH
purchase_log as (
  select user_id, amount, substr(cast(stamp as string),1,10) as dt
  from inlaid-lane-373607.sqlr202301.lec11_6
)
, user_rfm as (
  select user_id, max(dt) as recent_date, 
  date_diff(current_date, date(timestamp(max(dt))), day) as recency,
  count(dt) as frequency,
  sum(amount) as monetary
  from purchase_log
  group by user_id
)
select * from user_rfm
order by monetary desc;

예를 들어, user_id가 A2016인 고객은 최근 구매 일자가 2022년 12월 10일이고,

현재(2023년 2월 17일)로부터 69일 전에 구매한 것이 최근 구매일이 된다.

또한, 역대 구매 횟수는 총 104회 구매했으며, 그 금액은 370200원인 것을 알 수 있다.

 

이렇게 각 고객이 이때까지 했던 Action들을 취합한것이라고 보면 된다.

 

 

2) 고객별들의 RFM 랭크를 집계한다.

이제 위에서 구매 데이터를 모두 구했으므로, 사용자별로 rfm등급을 나눌 수 있게 되었다.

r, f, m각각 5등급씩 나누어 고객들을 총 125개의 그룹에 맞추어 집계할 수 있다.

하지만 RFM 각각의 등급을 모두 더한 15개의 그룹으로도 맞출 수 있다.

--4-2) 사용자들의 rfm랭크를 계산하기
WITH
purchase_log as (
  select user_id, amount, substr(cast(stamp as string),1,10) as dt
  from inlaid-lane-373607.sqlr202301.lec11_6
)
, user_rfm as (
  select user_id, max(dt) as recent_date, 
  date_diff(current_date, date(timestamp(max(dt))), day) as recency,
  count(dt) as frequency,
  sum(amount) as monetary
  from purchase_log
  group by user_id
)
, user_rfm_rank as (
  select user_id, recent_date, recency, frequency, monetary,
  case 
  when recency < 70 then 5
  when recency < 100 then 4
  when recency < 150 then 3
  when recency < 200 then 2
  else 1 end as r
  , case
  when frequency >= 55 then 5
  when frequency >= 45 then 4
  when frequency >= 35 then 3
  when frequency >= 25 then 2
  else 1 end as f
  , case
  when 250000 <= monetary then 5
  when 200000 <= monetary then 4
  when 150000 <= monetary then 3
  when 100000 <= monetary then 2
  else 1 end as m
  from user_rfm
)
, total_rfm as (
  select user_id, recent_date, frequency, monetary, r, f, m, r+f+m as total_rfm
  from user_rfm_rank
)
select * from total_rfm
order by user_id;

주로 case문을 사용하였다.

어떤 기준 이상이면 상위등급, 아니면 하위등급을 표시하는 방식으로 작성되기 때문이다.

참고로, 맨 하단의 order by절은 선택사항이지만, 필자는 정렬하기 위해 user_id별로 오름차순으로 정렬해 보았다.

결과를 보면 이렇게 각 사용자들의 모든 정보가 나온다.

이제는 앞서 쿼리에서 지정한 조건에 따라 r(최근 구매일), f(구매 횟수), m(구매 금액 합계)별로 등급이 자세히 나오는 것을 확인할 수 있다.

또한 rfm등급의 합계까지 나오므로 전체등급이 15에 가까울수록 핵심 고객이라고 말할 수 있다.

반면 3에 가까울수록 비우량적 고객이라고 볼 수 있다.

참고로 3이 나온 것은, rfm각각의 최소값은 1이므로, 총 합계가 3미만이 될 수 없기 때문이다.

위 표에서 제일 핵심고객층은 A106고객으로, 총합계 14점이다.

 

<우량 고객> = 2023년 2월 17일 기준, 우리 회사의 매출 증대에 도움이 되는 최우수 고객님.

 

15등급을 받은 고객은 우리 회사에서 중점적으로 관리해야 할 VIP고객님들이다.

즉 구매도 최근에 했고, 구매횟수도 많고, 구매금액도 많은 이들이다.

A2016고객은 2022년 12월 10일 구매에, 무려 104회 구매라는 우리 회사의 매출증대에 기여를 하고 있는 회원이라고 볼 수 있다.

 

하지만 회사에는 우량고객만 있는 것이 아니다. 이탈한 고객도 분명 있을 것이다.

 

 

<비우량고객> = 이탈 조짐이 있거나 이탈한 고객

위 표에서 특히 A1129고객은 최근구매일자가 2019년 6월로, 약 4년 전에 구매하고 구매 이력이 없다. 또한 구매금액도 41000원으로 소액이고, 구매횟수도 타 회원에 비해 많지 않은 점을 볼 때, 이미 이탈한 고객이라고 볼 수 있다.

 

A1164고객 역시 최근 구매일자가 2019년 12월이고, 구매횟수 3회, 구매액도 불과 10000원에 불과한 이탈 고객이다.

 

2022년에 구매건수가 있는 A102, A1142, A140 등의 회원은 앞선 이들에 비해 비교적 짧은 기간이나, 그래도 전체적으로 구매데이터가 빈약한 모습이다.

 

 

<안정고객> = 적당히 구매하는 일반적인 고객층. 많은 매출을 올리지는 않지만, 그렇다고 이탈할 조짐이 있는 것도 아님

rfm등급의 합계가 10인 회원만 추출해 보았다.

전체적으로 뛰어나지는 않지만, 그렇다고 이탈할 조짐이 있는 것도 아닌 상태의 고객층이다.

 

 

3) 그룹별(등급별) 사용자 수 구하기

마지막으로 위 데이터를 기반으로 각 그룹에는 몇 명의 사용자가 있는지 살펴보도록 하겠다.

--4-3) 각 그룹에 속한 사람의 수 확인하기
WITH
purchase_log as (
  select user_id, amount, substr(cast(stamp as string),1,10) as dt
  from inlaid-lane-373607.sqlr202301.lec11_6
)
, user_rfm as (
  select user_id, max(dt) as recent_date, 
  date_diff(current_date, date(timestamp(max(dt))), day) as recency,
  count(dt) as frequency,
  sum(amount) as monetary
  from purchase_log
  group by user_id
)
, user_rfm_rank as (
  select user_id, recent_date, recency, frequency, monetary,
  case 
  when recency < 70 then 5
  when recency < 100 then 4
  when recency < 150 then 3
  when recency < 200 then 2
  else 1 end as r
  , case
  when frequency >= 55 then 5
  when frequency >= 45 then 4
  when frequency >= 35 then 3
  when frequency >= 25 then 2
  else 1 end as f
  , case
  when 250000 <= monetary then 5
  when 200000 <= monetary then 4
  when 150000 <= monetary then 3
  when 100000 <= monetary then 2
  else 1 end as m
  from user_rfm
)
, mst_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 mst_rfm_index as m cross join user_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_flag.rfm_index
order by rfm_index desc;

먼저 5개의 그룹이므로 MST_RFM_INDEX테이블에는 1부터 5까지 숫자를 가지는 테이블을 만든다.

이후, 각 r,f,m과 rfm_index테이블과 cross join하여 모든 값이 테이블에 들어갈 수 있도록 만든다.

 

이렇게 등급별로 r,f,m 각각의 사람의 수가 나오는 것을 확인할 수 있다.

즉, r의 5등급은 총 88명임을 확인할 수 있다.

 

또한, 앞서 구한 rfm 각각의 랭크의 합계인 15그룹의 등급을 기반으로 고객 수를 카운팅하고자한다면 다음과 같이 적용해볼 수 있다.

--4-4) 사용자를 1차원으로 구분하기
WITH
purchase_log as (
  select user_id, amount, substr(cast(stamp as string),1,10) as dt
  from inlaid-lane-373607.sqlr202301.lec11_6
)
, user_rfm as (
  select user_id, max(dt) as recent_date, 
  date_diff(current_date, date(timestamp(max(dt))), day) as recency,
  count(dt) as frequency,
  sum(amount) as monetary
  from purchase_log
  group by user_id
)
, user_rfm_rank as (
  select user_id, recent_date, recency, frequency, monetary,
  case 
  when recency < 70 then 5
  when recency < 100 then 4
  when recency < 150 then 3
  when recency < 200 then 2
  else 1 end as r
  , case
  when frequency >= 55 then 5
  when frequency >= 45 then 4
  when frequency >= 35 then 3
  when frequency >= 25 then 2
  else 1 end as f
  , case
  when 250000 <= monetary then 5
  when 200000 <= monetary then 4
  when 150000 <= monetary then 3
  when 100000 <= monetary then 2
  else 1 end as m
  from user_rfm
)
select
r + f + m as total_rank,
count(user_id) as count
from user_rfm_rank
group by total_Rank
order by total_rank desc;

우리 회사에는 15점인 회원이 총 7명 있고,  3점에 가까운, 즉 이탈하거나 이탈 조짐이 있는 회원도 50명 이상 존재하는 것을 확인할 수 있다. 이들에게는 무료배송 쿠폰을 지급하여 좀 더 구매율을 끌어올릴 필요가 있을 것이다.

---

 

이렇게 오늘은 rfm분석에 대해 살펴보았습니다.

정한 조건을 걸어서 조금 더 세부적으로 사용자층을 분석하고자 할 경우 매우 유용하게 쓰일 수 있으며

경영 및 마케팅관련하여 고객관리, 관련 데이터를 분석할 시 중요한 역할을 할 것으로 생각됩니다.

 

이상입니다.

 

---

 

본 내용은 데이터 분석을 위한 sql레시피 11장 내용을 참고하여 작성되었습니다.