사용자 분석 - 고객 세분화 그룹 구하기(RFM)

충성고객에게 혜택을 주는 이벤트를 기획 중입니다
구매금액이 큰 순서로 충성고객 리스트를 뽑아주세요 

문제 정의하기

충성고객은 장기적이고 지속적인 관점에서 서비스 온보딩 여부(+사용자의 가치)를 판단해야 하기 때문에 단순히 구매금액이 큰 사용자만을 충성고객으로 정의하기는 어렵다. 따라서 RFM 분석을 통해 언제, 얼마나 자주, 얼마나 많은 금액을 지불했는지 사용자 그룹을 나누어 봐야 한다.

RFM은 다음 3가지의 기준을 의미한다.
✔ Recency(최근 구매일): 최근 구매 이력이 있는 사용자를 충성고객으로 취급 (숫자가 작을수록 충성고객)
✔ Frequency(누적 구매 횟수): 사용자가 구매한 횟수 (숫자가 클수록 충성고객)
✔ Monetary(구매 금액 합계): 사용자의 구매 금액 합계 (숫자가 클수록 충성고객)

필요한 데이터

유저 데이터(유저id), 주문 데이터(주문일자, 주문금액, 주문id)

풀이

with sub as (
select 
    user_id,
    amount,
    substr(stamp, 1, 10) as dt
from action_log
where action = 'purchase')

select
    user_id,
    max(dt) as recent_date,
    current_Date,
    TIMESTAMPDIFF(day, max(dt), current_Date) as recency,
    count(*) as frequency,
    sum(amount) as monetary
from sub
group by user_id

DATEDIFF/TIMESTAMPDIFF
-> 결과 값의 반환 단위에 따른 두 날짜 간의 차이를 구하는 함수

DATEDIFF(날짜 1, 날짜 2)
TIMESTAMPDIFF(단위, 날짜 1, 날짜 2)

최근 구매일(recency)의 경우
TIMESTAMPDIFF(day, max(dt), current_Date)
TIMESTAMPDIFF(week, max(dt), current_Date)
TIMESTAMPDIFF(month, max(dt), current_Date)
TIMESTAMPDIFF(quarter, max(dt), current_Date)
-> 최근 n일/n주/n월/n분기 이내 구매 고객으로 그룹을 세분화 할 수 있음

RFM 사용자 분석

사용자 그룹 정의하기 (예시)

등급	Recency	Frequency	Monetary
5	14일 이내	20회 이상	300만원 이상
4	28일 이내	10회 이상	100만원 이상
3	60일 이내	5회 이상	30만원 이상
2	90일 이내	2회 이상	5만원 이상
1	91일 이내	1회	5만원 미만

✔ 전체 등급을 몇 등급으로 나눌 것인지(3등급, 5등급 등)
✔ 각 등급의 금액과 주기를 어떻게 정할 것인지를 고려하여 사용자 그룹을 정의하고 분류한다.

풀이

-- RFM 사용자 분석 부분 쿼리 생략

, sub3 as (
select
    user_id,
    recent_date,
    current_Date,
    recency,
    frequency,
    monetary,
    case 
    	when recency < 14 then '5'
        when recency < 28 then '4'
        when recency < 60 then '3'
        when recency < 90 then '2'
        else '1' end as r,
    case 
    	when frequency >= 20 then '5'
        when frequency >= 10 then '4'
        when frequency >= 5 then '3'
        when frequency >= 2 then '2'
        else '1' end as f,
    case 
    	when monetary >= 300000 then '5'
        when monetary >= 100000 then '4'
        when monetary >= 30000 then '3'
        when monetary >= 5000 then '2'
        else '1' end as m
from sub2
)

select
    user_id,
    recent_date,
    current_Date,
    recency,
    frequency,
    monetary,
    r,
    f,
    m,
    r+f+m as score
from sub3

RFM 사용자 그룹

해석

Recency와 Frequency, Monetary의 각 등급의 합을 구해 충성도(score)를 정의할 수 있다. 그리고 이벤트의 목적과 달성 지표에 따라 대상 사용자 그룹을 선정해야 한다. 가령 위 경우와 같이 구매 합계가 큰 사용자를 대상으로 하는 이벤트라면 구매 합계가 크면서도 최근 구매를 하지 않은 사용자 그룹에 대해 "재방문 유도" 목적의 이벤트를 실시할 수 있다.

데이터 출처: 데이터 분석을 위한 SQL 레시피