使用者行為分析&RFM分析

總行為次數歸類

SELECT action_type,COUNT(*) AS count 
FROM user_action_logs
GROUP BY action_type

瀏覽紀錄的次數歸類

SELECT view_page,COUNT(*) AS count 
FROM user_action_logs
GROUP BY view_page

瀏覽紀錄的次數歸類

SELECT user_id,
    SUM(CASE WHEN action_type="VIEW" THEN 1 ELSE 0 END)AS view_count,
    SUM(CASE WHEN action_type="PURCHASE" THEN 1 ELSE 0 END)AS purchase_count,
    SUM(CASE WHEN action_type="SEARCH" THEN 1 ELSE 0 END)AS search_count,
    SUM(CASE WHEN action_type="ADD_CART" THEN 1 ELSE 0 END)AS add_cart_count
FROM user_action_logs
GROUP BY user_id

RFM

顧客分析是一種透過顧客消費行為進行顧客分類的一種分析方式，透過細部的分析了解哪些顧客是所謂的 VIP 顧客，哪些是最近有需求的顧客，進而規劃細部的行銷方案。RFM 顧客分析法除了可以在網路電子商務零售通路使用也可以用於一般的數位產品服務顧客分析上。

Recency:the freshness of the customer activity,be it purchases or visits.ex:上次消費購買或使用服務時間離目前時間越近分數越高，上次使用者打開 App 的時間。
Frequency:the frequency of the customer transactions or visits.ex:服務頻率越高分數越高，一段時間使用 App 的次數。
Monetary:the intention of customer to spend or purchasing power of customer.ex:累計購買商品金額或使用服務時間越長愈高分數越高，一段時間內閱讀電子報的時間。

RFM初步統計分析

SELECT 
    user_id,
    MAX(create_at) AS recency_dt,/* 最近一次消費時間 */
    DATEDIFF("2020-07-30",DATE(MAX(create_at))) AS recency,/* 最近購買日期和目前相差天數 */
    SUM(CASE WHEN action_type = 'PURCHASE' THEN 1 ELSE 0 END) AS frequency,/* 統計消費總次數 */
    SUM(purchase_amount) AS monetary/* 統計消費總金額 */
FROM user_action_logs
GROUP BY user_id;

RFM三指標分析

SELECT
    user_id,
    recency,
    monetary,
    frequency,
    CASE
        WHEN recency < 7 THEN 5
        WHEN recency < 14 THEN 4
        WHEN recency < 30 THEN 3
        WHEN recency < 60 THEN 2
        ELSE 1
    END AS recency_point,
    CASE
        WHEN frequency > 11 THEN 5
        WHEN frequency > 6 THEN 4
        WHEN frequency > 3 THEN 3
        WHEN frequency > 2 THEN 2
        ELSE 1
    END AS frequency_point,
    CASE
        WHEN monetary > 150000 THEN 5
        WHEN monetary > 15000 THEN 4
        WHEN monetary > 1500 THEN 3
        WHEN monetary > 500 THEN 2
        ELSE 1
    END AS monetary_point
FROM RFM;

圖片來源：數位時代

RFM_points

CREATE VIEW RFM_points
AS
    SELECT
        user_id,
        recency,
        monetary,
        frequency,
        CASE
            WHEN recency < 7 THEN 5
            WHEN recency < 14 THEN 4
            WHEN recency < 30 THEN 3
            WHEN recency < 60 THEN 2
            ELSE 1
        END AS recency_point,
        CASE
            WHEN frequency > 10 THEN 5
            WHEN frequency > 5 THEN 4
            WHEN frequency > 3 THEN 3
            WHEN frequency > 2 THEN 2
            ELSE 1
        END AS frequency_point,
        CASE
            WHEN monetary > 100000 THEN 5
            WHEN monetary > 10000 THEN 4
            WHEN monetary > 1000 THEN 3
            WHEN monetary > 500 THEN 2
            ELSE 1
        END AS monetary_point
    FROM RFM;

SELECT
    user_id,
    recency_point,
    frequency_point,
    monetary_point,
    (recency_point + frequency_point + monetary_point) AS RFM_points
FROM RFM_points
ORDER BY RFM_points DESC;