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_r	rfm_f	rfm_m	rfm
Lv.1	Lv.1	Lv.1	이탈한 고객(휴먼고객)
Lv.2	Lv5	Lv.1	놓치면 안되는 고객
Lv.3	Lv.1	Lv.1	이탈할 우려가 있는 고객
Lv.5	Lv.1	Lv.1	신규고객
Lv.5	Lv.5	Lv.1	VIP 고객

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을 확인할 수 있습니다.

이상으로, BizSpring Growth Platform™️ 내의 RFM 분석기법을 위한 데이터 생성에 필요한 쿼리에 대해서 살펴보았습니다.

비즈스프링의 마케팅 자동화 솔루션인 Bizspring Growth Platform™️에 대해 궁금한 점이 있다면 언제든지 연락주세요.

감사합니다.

Google Cloud BigQuery를 이용한 RFM 분석

Related Posts via Categories