Google Cloud BigQuery를 이용한 RFM
이 포스팅은 「마케팅 자동화 솔루션, BizSpring Growth Platform™️」 의 기술적 내용을 다룹니다.
해당 포스팅을 확인하려면 아래의 링크를 확인해주세요.
▶ 마케팅 자동화 솔루션, BizSpring Growth Platform™️
▶ BizSpring Growth Platform™️ 자세히 알아보자 - TAM (1)
▶ BizSpring Growth Platform™️ 자세히 알아보자 - TAM (2)
지난 포스팅에서는 클러스터링과 RFM 분석기법을 활용한 Discovery 세그먼트 생성에 대해 살펴보았는데요, 이어서 이번 포스팅에서는 구글 빅쿼리에서 실행하여 생성된 RFM의 쿼리 에 대해 좀 더 자세히 소개드리고자 합니다.
RFM 분석기법
RFM 분석기법은 가치있는 고객을 추출해내어 이를 기준으로 고객을 분류할 수 있는 매우 간단하면서도 유용하게 사용될 수 있는 방법으로 알려져 있어 마케팅에서 가장 많이 사용되고 있는 분석방법 중 하나입니다. RFM은 구매 가능성이 높은 고객을 선정하기 위한 데이터 분석방법입니다.
Recency(거래의 최근성) : 고객이 얼마나 최근에 구입했는가?
Frequency(거래빈도) : 고객이 얼마나 빈번하게 우리 상품을 구입했나?
Monetary(거래규모) : 고객이 구입했던 총 금액은 어느 정도인가?
Growth Platform에서는 RFM 분석 데이터를 생성하기 위해서 4단계를 거쳐 데이터를 생성합니다.
1. 일 기준 성과 생성
고객별로 일(day) 기준으로 recency, frequency,monetary의 데이터를 생성합니다.
WITH
e_page AS (
SELECT pfno,dpuid,sid,vt
,DATE(vt,”Asia/Seoul”) AS stat_date
FROM `data.page_*`
WHERE _TABLE_SUFFIX = “20220531”
AND dpuid != “”
)
,e_conv AS (
SELECT pfno,dpuid,vt,odn,rvn
,DATE(vt,”Asia/Seoul”) AS stat_date
FROM `data.conv_*`
WHERE _TABLE_SUFFIX = “20220531”
AND dpuid != “”
)
,t_session AS (
SELECT DISTINCT stat_date,pfno,dpuid
FROM e_page
)
,t_conv AS (
SELECT stat_date,pfno,dpuid
,COUNT(DISTINCT odn) AS odr
,SUM(oa) AS rvn
FROM e_conv
LEFT JOIN UNNEST(rvn) AS r
GROUP BY 1,2,3
)
,t_user AS (
SELECT stat_date,pfno,dpuid
FROM t_session
LEFT JOIN t_conv USING(stat_date,pfno,dpuid)
)
,t_rfm_rec AS (
SELECT stat_date,pfno,dpuid,MIN(recency) AS recency
FROM (
SELECT DATE(vt,”Asia/Seoul”) AS stat_date,pfno,dpuid
,TIMESTAMP_DIFF(CURRENT_TIMESTAMP,vt,HOUR) AS recency
FROM e_page
)
GROUP BY 1,2,3
)
,t_rfm_fre AS (
SELECT DATE(vt,”Asia/Seoul”) AS stat_date,pfno,dpuid
,COUNT(DISTINCT sid) AS frequency
FROM e_page
GROUP BY 1,2,3
)
,t_rfm_mon AS (
SELECT stat_date,pfno,dpuid,SUM(monetary) AS monetary
FROM (
SELECT DATE(vt,”Asia/Seoul”) AS stat_date,pfno,dpuid
,(SELECT SUM(oa) FROM UNNEST(rvn)) AS monetary
FROM e_conv
)
GROUP BY 1,2,3
)
,t_user_rfm AS (
SELECT stat_date,client_seq,pfno,dpuid
,IFNULL(recency ,30) AS recency
,IFNULL(frequency,0 ) AS frequency
,IFNULL(monetary ,0 ) AS monetary
FROM t_user
FULL JOIN t_rfm_rec USING(stat_date,pfno,dpuid)
FULL JOIN t_rfm_fre USING(stat_date,pfno,dpuid)
FULL JOIN t_rfm_mon USING(stat_date,pfno,dpuid)
)
SELECT *
FROM t_user_rfm
2. 대상기간 동안 합계 구하기
생성된 일별 데이터에서 설정한 기간 동안 frequency,monetary는 합계를 구하고 recency는
마지막 방문으로 부터 경과한 일수를 연산하기 위해 LAST_VALUE를 구합니다.
PERCENT_RANK() 함수를 사용하여 rfm_r, rfm_f, rfm_m을 분류합니다.(범위는 0~1)
WITH
e_user_rfm AS (
SELECT stat_date,pfno,dpuid
,recency,frequency,monetary
FROM `data.user_rfm_*`
WHERE _TABLE_SUFFIX BETWEEN “20220501” AND “20220531”
)
,t_user_rfm_p AS (
SELECT pfno,dpuid
,recency
,SUM(frequency) AS frequency
,SUM(monetary ) AS monetary
FROM (
SELECT pfno,dpuid
,LAST_VALUE(recency) OVER(user_window) AS recency
,frequency,monetary
FROM e_user_rfm
WINDOW user_window AS (
PARTITION BY dpuid ORDER BY stat_date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
)
GROUP BY 1,2
)
,t_rfm_rank AS (
SELECT pfno,dpuid
,PERCENT_RANK() OVER(PARTITION BY pfno ORDER BY recency DESC) AS rfm_r
,PERCENT_RANK() OVER(PARTITION BY pfno ORDER BY frequency ASC) AS rfm_f
,PERCENT_RANK() OVER(PARTITION BY pfno ORDER BY monetary ASC) AS rfm_m
,uv,pv,dt,odr,rvn
FROM t_user_rfm_p
)
SELECT *
FROM t_user_rfm_rank
3. RFM 범주화
rfm_r, rfm_f, rfm_m의 범위에 따라 Lv.1~Lv.5로 정의합니다.
rfm_r이 null이거나 rfm_r<0.2 이면 “Lv.1”, rfm_r<0.4 이면 “Lv.2”, rfm_r<0.6 이면 “Lv.3”, rfm_r<0.8 이면 “Lv.4″이고 그 외에는 “Lv.5″로 범주화합니다.
WITH
e_user_rfm_cat AS (
SELECT pfno,dpuid
,IF(rfm_r IS NULL OR rfm_r<0.2,”Lv.1″,IF(rfm_r<0.4,”Lv.2″,IF(rfm_r<0.6,”Lv.3″,IF(rfm_r<0.8,”Lv.4″,”Lv.5″)))) AS rfm_r
,IF(rfm_f IS NULL OR
rfm_f<0.2,”Lv.1″,IF(rfm_f<0.4,”Lv.2″,IF(rfm_f<0.6,”Lv.3″,IF(rfm_f<0.8,”Lv.4″,”Lv.5″)))) AS rfm_f
,IF(rfm_m IS NULL OR
rfm_m<0.2,”Lv.1″,IF(rfm_m<0.4,”Lv.2″,IF(rfm_m<0.6,”Lv.3″,IF(rfm_m<0.8,”Lv.4″,”Lv.5″)))) AS
rfm_m
FROM `data.user_rfm_rank`
)
SELECT *
FROM e_user_rfm_cat
4. RFM 모델과 연결
RFM 범주화 후 고객별로 정의해 놓은 rfm_model 테이블과 join 하여 최종 데이터를 생성합니다.
아래의 rfm_model 테이블은 고객의 rfm 기준으로 정의하여 사용할 수 있습니다.
rfm_model 테이블 예시 (출처: BizSpring)WITH
e_rfm_model AS (
SELECT *
FROM `asset.rfm_model`
)
,e_user_rfm_cat AS (
SELECT *
FROM `data.user_rfm_cat`
)
SELECT “2022-05-31” AS stat_date,pfno
,rfm,rfm_r,rfm_f,rfm_m
,COUNT(*) AS user
FROM e_user_rfm_cat
LEFT JOIN e_rfm_model USING(rfm_r,rfm_f,rfm_m)
WHERE rfm IS NOT NULL
GROUP BY 1,2,3,4,5,6
5. RFM 처리된 데이터 결과
최종적으로 생성된 데이터는 타깃그룹으로 사용할 수 있습니다.
일자별, 고객별로 정의된 rfm의 rfm_r, rfm_f, rfm_m을 확인할 수 있습니다.
RFM Query 결과(출처: BizSpring)이상으로, BizSpring Growth Platform™️ 내의 RFM 분석기법을 위한 데이터 생성에 필요한 쿼리에 대해서 살펴보았습니다. 비즈스프링의 마케팅 자동화 솔루션인 Bizspring Growth Platform™️에 대해 궁금한 점이 있다면 언제든지 연락주세요.
감사합니다.
문의 02-6919-5516 / ad@bizspring.co.kr
