Google Cloud BigQuery를 이용한 RFM 분석

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

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

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

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