[에이블스쿨] RFM, Retention

지난 포스팅에서는 90일 이상 구매 이력이 없는 고객을 장기 미구매 고객이라 정의하고, 해당 고객군의 특성을 파악했다. 이번에는 RFM 분석으로 전체 판매 데이터를 활용해 고객 등급을 지정하고, 또 단순 90일이 아니라 Retention을 파악해서 첫 구매일 이후 고객의 잔존율도 확인해보자.

 

먼저, python으로 만든 csv 파일을 로드했다.

 

CSV LOAD

 

결과가 문제없이 출력됨을 확인했으니, RFM 분석을 시작해보자. 혹시 RFM 분석이 뭔지 모르신다면, 아래의 포스팅을 먼저 읽고 오시는걸 추천드린다.

 

[RFM] 고객 세분화 분석이란?

 

RFM 분석

 

판매 데이터로 RFM을 구하기 위해서는 고객 ID, 고객 별 최신 구매일자, 구매 횟수, 구매 금액의 합이 필요하다. 또한, R/F/M의 기준도 세워야한다. 우리는 90일 이상 미구매 고객을 식별하므로, 90일을 기준으로 Recency를 분류하면된다. 그렇다면, Frequency와 Monetary는 어떻게 분류하는게 좋을까?

 

대규모 판매 데이터에는 분명 VIP가 있기 마련이고, 평균값은 극단값에 취약하다는 단점이 있다. 따라서, 판매 횟수와 구매금액의 합의 중앙값을 활용해서 F와 M을 2가지로 나눠보자. 편의상 RFM을 모두 2가지로 분할했지만, 고객/산업의 특성에 따라 더 세세하게 분류해도 무방하다.

 

-- 원본 테이블에서 필요한 정보 CTE로 추출

with records as(
	select CustomerID as ID, 
        Max(OrderDate) as last_order_date,
        count(CustomerID) as cnt_orders,
        sum(Amt) as sum_sales
    from long_cust
    group by CustomerID
)

-- F의 기준, M의 기준 설정(중앙값 활용)

select avg(cnt_orders)
from(
	select cnt_orders, percent_rank() over (order by cnt_orders) as percent
	from records) t
where percent between 0.49 and 0.51;

select avg(sum_sales)
from(
	select sum_sales, percent_rank() over (order by sum_sales) as percent
	from records) t
where percent between 0.49 and 0.51;

 

주문 횟수의 중앙값은 12, 구매 금액의 중앙값은 29,555인데 편의상 30,000으로 지정했다. MySQL은 median을 직접 구하는 함수가 없기 때문에, percent_rank() 함수를 사용해서 가운데 있는 값들의 평균으로 추출하면된다. 

 

기준을 정했으니, RFM 그룹별로 몇 명의 고객이 있는지 확인해보자.

select 
	case 
		when last_order_date >= '2017-01-01'
		then 'recent'
        else 'past'
        end as recency,
	case 
		when cnt_orders >= 12
		then 'high'
        else 'low'
        end as frequency,
	case 
		when sum_sales >= 30000
        then 'high'
        else 'row'
        end as monetary,
		count(*) as customers
from records
group by recency, frequency, monetary
order by customers desc;

recency	frequency	monetary	customers
recent	high	high	954
past	low	row	717
recent	low	row	337
past	high	high	95
recent	low	high	49
recent	high	row	48
past	high	row	23
past	low	high	20

 

결과를 다음과 같이 해석할 수 있다,

• 가장 가치 있는 충성 고객 : 954명
• 떠나간 VIP : 95명
• 구매 빈도와 금액이 낮은 과거 고객 : 717명

프로젝트의 목적은 이탈 위험이 있는 고객군을 찾아 그들에게 프로모션을 제공, 고객의 이탈을 방지하는 것이다. 우리는 95명의 고객들의 특성을 파악해서 장기 미구매 요인을 탐색하고, 그들의 마음을 되돌릴 방안을 고민해볼 필요가 있다. 또한, 기존 충성 고객들을 위한 프로모션도 고민해야한다. 즉, VIP 고객들을 중점적으로 프로모션을 진행해보자. 먼저, VIP 고객들만을 추출하고, 그들의 비중을 확인해보았다.

 

select 
    recency,
    frequency,
    monetary,
    customers,
    round(customers / sum_cust,2) as cust_ratio
from(
select 
	case 
		when last_order_date >= '2017-01-01'
		then 'recent'
        else 'past'
        end as recency,
	case 
		when cnt_orders >= 12
		then 'high'
        else 'low'
        end as frequency,
	case 
		when sum_sales >= 30000
        then 'high'
        else 'row'
        end as monetary,
		count(*) as customers,
        sum(count(*)) over() as sum_cust
from records
group by recency, frequency, monetary) t
where
	recency in ('recent', 'past')
    and frequency = 'high'
    and monetary = 'high'
order by recency desc;

 

recency	Frequency	Monetary	Customers	Ratio
recent	high	high	954	0.43
past	high	high	95	0.04

 

전체 고객 중 43%가 충성 고객이고, 4%가 떠나간 VIP이다. 47%가 높은 가치 고객층이므로, 이들에 대한 세분화된 분석을 통해 효과적인 리텐션 및 마케팅 전략을 수립할 필요가 있다.

 

Retention(리텐션)

RFM 분석 결과, 고객들의 리텐션을 확인해볼 필요가 있다. 1년 3개월 동안 방문 횟수의 중앙값이 12회 이므로, 클래식 리텐션보다 롤링 리텐션을 사용하는게 좋다고 판단했다. 롤링 리텐션은 최초 활성화 시점부터 특정 시점까지 아직 이탈하지 않은 고객의 비율을 계산한다. 리텐션의 개념을 잘 모른다면, 아래의 포스팅을 참조하면 된다.

 

[Retention] 리텐션

 

바로 MySQL로 실습을 진행해보자.  2016년 데이터로 1년간 리텐션 변화도를 확인했다.

 

WITH ROLL_RT AS(
SELECT DISTINCT CustomerID, 
       DATE_FORMAT(MIN(OrderDate),'%Y-%m-01') AS first_order_month, 
       DATE_FORMAT(MAX(OrderDate),'%Y-%m-01') AS last_order_month
FROM long_cust
GROUP BY CustomerID)

SELECT first_order_month,
  COUNT(DISTINCT CustomerID) as month0,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 1 month) <= last_order_month then CustomerID end) as month1,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 2 month) <= last_order_month then CustomerID end) as month2,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 3 month) <= last_order_month then CustomerID end) as month3,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 4 month) <= last_order_month then CustomerID end) as month4,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 5 month) <= last_order_month then CustomerID end) as month5,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 6 month) <= last_order_month then CustomerID end) as month6,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 7 month) <= last_order_month then CustomerID end) as month7,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 8 month) <= last_order_month then CustomerID end) as month8,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 9 month) <= last_order_month then CustomerID end) as month9,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 10 month) <= last_order_month then CustomerID end) as month10,
  COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 11 month) <= last_order_month then CustomerID end) as month11
FROM ROLL_RT
WHERE first_order_month < '2017-01-01'
GROUP BY first_order_month
ORDER BY first_order_month

 

Rolling Retention

 

결과 표를 보면, 몇 가지 주요 포인트를 발견할 수 있다.

 

• 첫 달(month0) 사용자 기준으로 시간이 지날수록 리텐션율이 급격히 감소하는 추세를 보인다. 예를 들어 1월의 경우 1개월 후 92.65%, 3개월 후 90.35%로 떨어졌지만 11개월 후에는 61.28%까지 하락했다.
• 초기 1~3개월의 리텐션율은 상대적으로 높은 편이지만, 그 이후에는 가파르게 감소하는 모습을 보인다. 이는 신규 유입 사용자 대비 장기 지속 사용자 비중이 낮다는 것을 의미한다.
• 가장 최근인 2016-11-01의 경우 1개월 리텐션율이 24%에 불과하다. 이는 새로 가입한 사용자 4명 중 1명만이 한 달 후에도 제품/서비스를 사용한다는 뜻이다.
• 전체적으로 초기에는 높았다가 시간이 지날수록 리텐션율이 급격히 하락하는 형태를 보이고 있다. 이는 신규 사용자 유입에 비해 기존 사용자 유지가 잘 이뤄지지 않고 있음을 시사한다.

롤링 리텐션의 특성상 리텐션이 개선되고 있는지에 대한 해석은 어렵다. 다만, 연말에 비해 연초 첫 구매자들의 리텐션이 좋음을 확인할 수 있다. 특히, 1월 첫 구매자들의 리텐션이 좋은데 당시 어떤 프로모션을 진행했는지 확인해볼 필요가 있다.

사용자 지표

또 다른 방법으로, 리텐션이 가장 좋았던 2016년 1월의 구매자들의 2월 DAU, MAU, Stickness를 계산해보자. DAU(Daily Active Users)는 매일 실제로 제품/서비스를 사용하는 활성 사용자 수를 의미하므로, 1월에 가입한 신규 사용자들이 2월부터 본격적으로 이용을 시작할 것으로 가정할 수 있기 때문이다. 1월에는 신규 가입자들이 제품을 익히고 활용하는 기간이었다면, 2월부터는 안정적으로 일상 사용이 이루어졌을 것으로 예상된다.

따라서 2016년 1월 신규 고객의 리텐션이 좋다면, 그들의 실제 활성 사용 추이를 파악하기 위해서는 2월 DAU 데이터를 활용하는 것이 가장 적절하다. 1월 DAU는 아직 대다수가 신규 가입 단계에 있었기 때문에 실제 활성 사용량을 제대로 반영하지 못할 수 있다.

 

with MAU as(
SELECT d.OrderDate as dt,
       count(DISTINCT d.CustomerID) as dau,
       count(distinct m.CustomerID) as mau,
       round(count(DISTINCT d.CustomerID) / count(DISTINCT m.CustomerID),2) as stickiness
from long_cust as d
left join long_cust as m on m.OrderDate BETWEEN DATE_SUB(d.OrderDate, INTERVAL 30 DAY) and d.OrderDate
where d.OrderDate between '2016-02-01' and '2016-02-29'
group by d.OrderDate
order by d.OrderDate)

select *, dayofweek(dt) as weekday
from MAU
order by stickiness desc;

 

 

Stickiness가 높은순으로 정렬한 결과, 2016년 2월 초와 금요일에 비교적 사용자가 많음을 확인할 수 있다. 당시 진행했던 프로모션 혹은 인기 상품을 확인해서 판매 전략에 활용할 수 있다.

결론

E-커머스 데이터로 간단한 분석을 수행해보았다. 이탈 위험 고객군을 90일로 지정하고, EDA / RFM / 리텐션 / 사용자 지표를 분석해보았다. 알아낸 사항은 다음과 같다.

• 이탈 위험 예상 고객은 30~50대의 경기도 거주 여성이 다수
• 채소, 반찬류의 구매 빈도가 많은 것으로 보아 가정 주부로 추정
• 2016년 1월 ~ 2017년 3월 사이의 충성고객은 약 43%, 떠나간 VIP는 4%
• 2016년 리텐션을 롤링 리텐션으로 확인한 결과, 2016년 1월 가입자의 리텐션이 제일 좋음
• 2016년 1월 가입자의 특성을 파악하고자 2016년 2월 데이터로 사용자 지표 추출 => 2월초와 금요일에 비교적 일일 사용자가 많음

2월 초와 금요일에 활성화되는 패턴으로 미루어볼 때, 주말 식재료 구매를 위해 가입했던 주부 고객들이 점차 이탈한 것으로 판단된다. 결과적으로 주부 고객 확보와 초기 리텐션은 성공적이었지만, 장기 고객 유지에는 실패한 것으로 보인다. 이는 주부 고객의 니즈 변화를 제대로 반영하지 못했기 때문인 것으로 분석된다.

따라서 앞으로는 주부 고객의 라이프스타일과 식품 소비 패턴 변화를 지속적으로 모니터링하고, 그에 맞는 차별화된 마케팅과 서비스 전략을 수립할 필요가 있다. 아울러 초기 유치 못지않게 장기 리텐션 제고를 위한 프로그램과 이벤트도 강화된다면 매출을 제고할 수 있을 것으로 예상된다.