[TIL] 51일차 TIL(20230418) - SQL RFM 분석, 재구매율, 이탈률 분석

[TIL] 51일차 TIL(20230418) - SQL RFM 분석, 재구매율, 이탈률 분석

 

 

📗 RFM 분석

 

✅ 분석 개요

- 구매 가능성이 높은 고객을 식별하기 위한 데이터 분석 방법

- 마케팅에서 사용자 타겟팅을 위한 방법

- Recency: 얼마나 최근에 구매했는가? (최근에 구매한 고객이 추후 구매 확률이 더 높다고 가정)

- Frequency: 얼마나 빈번하게 구매했는가?

- Monetary: 얼마나 많은 금액을 지불했는가?

- R > F > M 순으로 비중을 둔다.

- 집계 기간을 어떻게 설정할 것인지 고려할 필요가 있다.

 

✅ 분석 방법

1. 먼저 recency, frequency, monetary를 구한다.

SELECT customer_id, max(max(order_date)) over() - max(order_date) recency,
	count(DISTINCT order_id) frequency, sum(amount) monetary
FROM cte_customers
GROUP BY customer_id
ORDER BY recency DESC

 

2. 단계정의

각 지표를 5단계로 나누어 1점부터 5점까지 점수를 부여한다.

단계를 나눌때는 지표에 맞는 기준을 따로 설정할 수도 있고, 동일하게 5등분으로 나누는 방법도 있다.

예제에서는 5등분으로 나누어 점수를 부여한다.

SELECT customer_id, 
	recency, ntile(5) over(ORDER BY recency desc) recency_score,
	frequency, ntile(5) over(ORDER BY frequency) frequency_score,
	monetary, ntile(5) over(ORDER BY monetary) monetary_score
FROM cte_rfm_table
ORDER BY 2,3,4

 

하지만 이와 같이 ntile을 이용해서 분류할 경우 동일한 수치임에도 다른 그룹으로 분리될 위험이 있다. 따라서 ntile로 대략적인 수치를 확인한 뒤, case ~ when을 이용해 직접 구간의 기준을 지정해주었다.

SELECT *,
	CASE
		WHEN recency <= 6 THEN 5
		WHEN recency <= 15 THEN 4
		WHEN recency <= 30 THEN 3
		WHEN recency <= 70 THEN 2
		ELSE 1
	END AS r,
	CASE
		WHEN frequency >= 14 THEN 5
		WHEN frequency >= 10 THEN 4
		WHEN frequency >= 7 THEN 3
		WHEN frequency >= 5 THEN 2
		ELSE 1
	END AS f,
	CASE
		WHEN monetary >= 22000 THEN 5
		WHEN monetary >= 12000 THEN 4
		WHEN monetary >= 5500 THEN 3
		WHEN monetary >= 3000 THEN 2
		ELSE 1
	END AS m
FROM cte_rfm_group

 

total_score까지 생성해준뒤 최종적으로 산출된 고객별 rfm 점수는 다음과 같다. 이제 산출된 rfm 점수들을 통해 고객을 분류하여 고객별 관리를 수행할 수 있다.

 

✅ RFM 활용

SELECT r,
	count(CASE WHEN f=5 THEN 1 END) AS f5,
	count(CASE WHEN f=4 THEN 1 END) AS f4,
	count(CASE WHEN f=3 THEN 1 END) AS f3,
	count(CASE WHEN f=2 THEN 1 END) AS f2,
	count(CASE WHEN f=1 THEN 1 END) AS f1
FROM cte_rfm_score2
GROUP BY r
ORDER BY r DESC

RFM에서 중요도가 높았던 R과 F를 기준으로 각 구간별 고객의 수를 나타낸 표이다.

여기서 r과 f가 5인 고객은 최근 구매 시점도 최근이고 방문 빈도도 잦아 '단골 고객'이라고 파악할 수 있다.

 

또 f와 m은 5로 총 구매 횟수와 구매 금액도 높지만, 최근에 방문하지 않았을 경우(r = 1,2,3,4) 과거에는 우수고객이었으나 현재는 구매하지 않아 재방문 유도가 필요한 고객으로 정의할 수 있다.

 

 

total_score가 15인 고객은 '핵심 고객'으로 파악할 수 있다. 핵심 고객이 구매한 상품들이나, 핵심 고객이 전체 매출에서 차지하는 비율, 구매 패턴 등을 추가적으로 분석할 수 있을 것이다.

 

 

 

📗 연도별 재구매율 

 

✅ 재구매율 정의

연도별로 고객이 구매했는지 여부를 파악하여, 연속된 2년동안 모두 구매했다면 '재구매'했다고 하고, 2개년 이상 구매했더라도 연속되지 않았다면 재구매하지 않은 것으로 정의한다.

예를 들어 1996년에 구매하고 1997년에 연속으로 구매한 고객은 '1996년 재구매'라고 정의하고, 1996년에 구매한 뒤 1998년에 구매한 고객은 재구매하지 않은 것이다.

 

✅ 재구매율 구하기

고객의 년도별 구매이력 리스트를 구하고, 다음에 구매한 년도를 구해서 바로 다음 해에 구매했다면 값을 표시하고, 바로 다음 해가 아니라면 NULL을 반환하여 테이블을 구성했다.

-- 1. 고객, 구매년도를 중복되지 않게 불러옴
cte_select AS (
SELECT customer_id, YEAR
FROM cte_customers
GROUP BY 1,2  -- 중복제거
ORDER BY 1,2
),
-- 2. 다음 연도와 매칭되도록 self join
cte_reorder_list AS (
SELECT a.customer_id, a.YEAR, b.YEAR next_year
FROM cte_select a
LEFT JOIN cte_select b
ON a.customer_id = b.customer_id AND (a.year::numeric)+1 = b.year::NUMERIC
)

 

위와 같은 테이블을 구성하는 방법으로 self join이외에도 lag를 사용할 수 있을 것 같아 시도해보았다. lag를 사용해 고객의 다음번 구매년도를 끌고온 뒤, 현재 구매년도 +1과 같다면 그대로 반환하고, 같지 않다면 NULL을 반환했다.

-- 2-1. lag사용해서 재구매연도 구하기
SELECT *,
	CASE WHEN
	(year::NUMERIC) + 1 = lag(YEAR, -1) over(PARTITION BY customer_id ORDER BY customer_id, year)::numeric
	THEN lag(YEAR, -1) over(PARTITION BY customer_id ORDER BY customer_id, year)
	ELSE NULL
	END AS next_year
FROM cte_select

 

이를 바탕으로 연도별 구매자 수와 재구매자 수를 구해서 연도별 재구매율을 구했다.

SELECT count(year) 당해구매자수, count(next_year) 당해재구매자수,
	round((count(next_year)::numeric / count(year)::numeric * 100)::NUMERIC, 2) || '%' 재구매율
	-- 정수끼리의 나눗셈은 정수로 반환되어 '몫'만 나오게 된다. 따라서 실수형인 numeric으로 변환해줘야 함.
FROM cte_reorder_list
GROUP BY year

 

 

📗 월별 재구매율 

 

✅ Timestamp

날짜형식 중 timestamp형식은 '1 year', '1 month', '1 day' 등 원하는 기간의 날짜를 더하거나 빼는 연산이 가능하다. 월별 재구매율을 구하기 위해 다음 월을 끌어와야하므로 timestamp 형식으로 변환이 필요하다.

timestamp형식으로 만들어주기 위해서 date_trunc를 사용할 수 있다.

 

date_trunc('month', now()) = 2023-04-01 00:00:00.000 +0900

 

✅ 월별 재구매율 구하기

SELECT c1.customer_id, c1.order_date, c2.order_date next_order_date
FROM cte_select c1 LEFT JOIN cte_select c2
ON c1.order_date + '1 month' = c2.order_date AND c1.customer_id = c2.customer_id

앞서 구한 방식과 유사하게 1달 후의 주문 날짜와 self join해주면 다음 달 주문 내역이 있다면 날짜를 반환하고, 없다면 NULL을 반환하는 테이블을 만들 수 있다.

 

SELECT to_char(order_date, 'YYYY-MM') order_date, count(order_date) 당월구매자수, count(next_order_date) 당월재구매자수,
	round((count(next_order_date)::numeric / count(order_date)::NUMERIC * 100)::NUMERIC, 2) || '%' 재구매율
FROM cte_reorder_list
GROUP BY 1
ORDER BY 1;

 

 

📗 이탈률 

 

✅ 이탈률 정의

데이터 내에서 가장 최근 주문일자를 기준으로, 각 고객의 최근 주문일자가 90일 이상 경과했다면 이탈고객으로, 90일 미만이라면 이탈고객이 아니라고 정의한다.

 

✅ 고객의 이탈률 구하기

1. 먼저 고객별 가장 최근 구매일자를 가져오고, 전체 데이터에서 가장 최근 구매일자(기준일)과의 차이를 구한다.

2. 앞서 구한 차이가 90일 이상이라면 이탈고객유무를 1로, 90일 미만이라면 이탈고객여부를 0으로 정의한다.

3. 이탈고객의 수를 세기 위해 sum(이탈고객)을 해주고 전체 고객 수로 나눠주면 이탈률을 구할 수 있다.

-- 고객별 가장 최근 구매일자와 기준일과의 차이
customer_datediff AS (
SELECT customer_id, max(order_date) customer_order_date,
	max(max(order_date)) over() - max(order_date)  order_date_diff
FROM cte_customers
GROUP BY customer_id
ORDER BY 2 desc
),
-- 이탈고객 여부
customer_turnover AS (
SELECT *,
	CASE WHEN order_date_diff >= 90 THEN 1
	ELSE 0
	END AS 이탈고객
FROM customer_datediff
)

-- 이탈률 계산
SELECT round((sum(이탈고객)::numeric / count(*)::numeric * 100)::NUMERIC, 2) || '%' 이탈률
FROM customer_turnover

 

 

📗 백분위수, 최빈값

데이터에서 백분위수와 최빈값을 찾기 위한 sql 함수는 다음과 같다.

percentile_cont(백분위) within group (order by column): 데이터의 개수가 짝수일 경우 백분위수로 둘의 평균을 반환

percentile_disc(백분위) within group (order by column): 데이터의 개수가 홀수일 경우 백분위수로 둘 중 작은 값을 반환

이 때 백분위 값으로 0.5를 넣으면 중앙값을 구할 수 있다.

-- 제품 가격 사분위수 구하기
SELECT
	percentile_cont(0.25) WITHIN GROUP (ORDER BY unit_price) q1,
	percentile_cont(0.5) WITHIN GROUP (ORDER BY unit_price) q2,
	percentile_cont(0.75) WITHIN GROUP (ORDER BY unit_price) q3
FROM products

 

최빈값 또한 동일한 방식으로 mode() within group (order by column)을 통해 구한다.

 

 

 

📗 과제. 제품의 연도별 재구매율

동일 고객이 다음 년도에 같은 제품을 연속해서 구매했을 경우 재구매했다고 간주하고 상품별 재구매율을 구한다.

먼저 고객, 상품, 년도별 구매여부를 파악하고, 다음 년도에 동일 상품을 구매했는지 여부를 self join을 통해 확인했다.

이후 이 결과를 년도와 상품별로 group by하여 최종적인 재구매율을 산출했다.

-- 고객이 년도별로 구매한 상품들 리스트
customer_product_list AS (
SELECT customer_id, product_name, YEAR FROM cte_customers
GROUP BY customer_id, product_name, YEAR
ORDER BY 1,2,3
),
-- 고객이 상품을 2년 연속 구매했는지 판단
customer_product_list2 AS (
SELECT c1.customer_id, c1.product_name, c1.YEAR, c2.YEAR next_year
FROM customer_product_list c1 LEFT JOIN customer_product_list c2
ON c1.customer_id = c2.customer_id AND c1.product_name = c2.product_name
	AND c1.YEAR::int +1 = c2.YEAR::int
)
-- 상품의 년도별 재구매율
SELECT product_name, YEAR, count(year) 주문수, count(next_year) 재주문수,
	round(count(next_year) / count(year)::NUMERIC * 100, 2) || '%' 재구매율
FROM customer_product_list2
GROUP BY 1,2
ORDER BY 1,2

 

 

---

 

 

소감 및 정리

이탈률과 재구매율 등 타겟 고객을 특정하기 위해 사용할 수 있는 여러 지표들을 생성하는 법에 대해 배웠다. 고객 관리를 목적으로 특정 타겟을 설정할 때 이런 지표들을 사용해본 적은 없었는데, 사용할만한 유용한 지표들을 알아갈 수 있었다. 며칠 전부터 계속 기본 쿼리문을 사용해 추출하고, 집계해서 새로운 컬럼을 만들어내고, case ~ when을 이용해 정리하는 과정을 거쳐가면서 쿼리를 작성하고 있는데 초반에 비해 많이 익숙해졌고 헤매는 횟수도 훨씬 줄어들어서 빠르게 해결할 수 있었다. 길을 잘못 들어가서 헤매는 경우도 거의 발생하지 않았다. 내일부터는 미니 프로젝트를 수행하게 되는데, 지금까지 배운 여러 지표들을 다양하게 활용해서 새로운 결과를 얻어내고 싶다.🐰