用 RFM 看懂客户价值

客户总消费高，不一定最近还活跃；最近刚买过，也不一定长期有价值。RFM 把最近一次购买间隔、购买频次和累计消费放在一起，帮助我们区分高价值、稳定、成长和待唤回客户。

统一观察日期

RFM 中的“最近”必须相对于一个固定日期计算。本项目数据截止到 2025 年 6 月 30 日，所以观察日期定为 2025 年 7 月 1 日。

sql

SELECT date('2025-07-01') AS analysis_date;

不要直接使用 date('now')。课程在不同日期运行时，最近购买间隔会改变，示例结果便无法复核。定期生产报表可以把观察日期设为参数。

观察日期通常设在数据截止日的下一天。这样 2025-06-30 下单的客户，其最近购买间隔是 1 天，含义直观。若观察日期早于最后订单日期，recency_days 会变成负数，应立即停止分群并检查数据时间范围。

计算每位客户的 RFM

sql

WITH customer_metrics AS (
    SELECT
        customer_id,
        MAX(order_date) AS last_order_date,
        COUNT(DISTINCT order_id) AS frequency,
        ROUND(SUM(revenue), 2) AS monetary
    FROM v_sales_detail
    GROUP BY customer_id
)
SELECT
    customer_id,
    last_order_date,
    CAST(
        julianday('2025-07-01') - julianday(last_order_date)
        AS INTEGER
    ) AS recency_days,
    frequency,
    monetary
FROM customer_metrics
ORDER BY monetary DESC;

recency_days 越小越好，因为距离最近一次购买越近；frequency 和 monetary 通常越大越好。三个方向不完全相同，解释时要说清楚。

这里先聚合再算日期差，是因为 RFM 的粒度是一行一客户。MAX(order_date) 找最近一次购买，COUNT(DISTINCT order_id) 避免多商品订单放大频次，SUM(revenue) 汇总客户累计贡献。运行后应得到 120 行，与完成订单客户数一致。

设阈值前先看分布，不要凭感觉写数字：

sql

WITH customer_metrics AS (
    SELECT
        customer_id,
        CAST(julianday('2025-07-01') - julianday(MAX(order_date)) AS INTEGER)
            AS recency_days,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(revenue) AS monetary
    FROM v_sales_detail
    GROUP BY customer_id
)
SELECT
    MIN(recency_days) AS min_recency,
    ROUND(AVG(recency_days), 1) AS avg_recency,
    MAX(recency_days) AS max_recency,
    MIN(frequency) AS min_frequency,
    ROUND(AVG(frequency), 2) AS avg_frequency,
    MAX(frequency) AS max_frequency,
    ROUND(MIN(monetary), 2) AS min_monetary,
    ROUND(AVG(monetary), 2) AS avg_monetary,
    ROUND(MAX(monetary), 2) AS max_monetary
FROM customer_metrics;

分布检查告诉我们阈值处在数据的什么位置。frequency >= 5 和 monetary >= 1800 是本项目可复核的业务规则，不是可以直接复制到所有公司的“标准答案”。

用透明规则完成分群

sql

WITH customer_metrics AS (
    SELECT
        customer_id,
        MAX(order_date) AS last_order_date,
        COUNT(DISTINCT order_id) AS frequency,
        ROUND(SUM(revenue), 2) AS monetary
    FROM v_sales_detail
    GROUP BY customer_id
)
SELECT
    customer_id,
    CAST(
        julianday('2025-07-01') - julianday(last_order_date)
        AS INTEGER
    ) AS recency_days,
    frequency,
    monetary,
    CASE
        WHEN frequency >= 5 AND monetary >= 1800
            THEN '高价值客户'
        WHEN frequency >= 4
            THEN '稳定客户'
        WHEN julianday('2025-07-01') - julianday(last_order_date) > 120
            THEN '待唤回客户'
        ELSE '成长客户'
    END AS customer_segment
FROM customer_metrics
ORDER BY monetary DESC;

这是一套业务规则，不是机器学习模型。它的优点是容易解释和调整。条件有顺序：满足高价值条件的客户会先被归入高价值，不会继续落入稳定客户。

以一位频次 5、消费 2200 元、最近购买间隔 150 天的客户为例：它同时满足“高价值”和“待唤回”的描述，但 CASE 在第一条满足后就停止，因此会被归为高价值客户。这不一定错，却说明规则表达的是优先级。如果业务更关注流失风险，应把过久未购条件提前，或拆成“价值等级 + 活跃状态”两个独立字段，避免一个标签承载两个维度。

检查各分群是否合理

sql

WITH customer_metrics AS (
    SELECT
        customer_id,
        MAX(order_date) AS last_order_date,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(revenue) AS monetary
    FROM v_sales_detail
    GROUP BY customer_id
), segmented AS (
    SELECT
        *,
        CASE
            WHEN frequency >= 5 AND monetary >= 1800 THEN '高价值客户'
            WHEN frequency >= 4 THEN '稳定客户'
            WHEN julianday('2025-07-01') - julianday(last_order_date) > 120
                THEN '待唤回客户'
            ELSE '成长客户'
        END AS customer_segment
    FROM customer_metrics
)
SELECT
    customer_segment,
    COUNT(*) AS customers,
    ROUND(AVG(frequency), 2) AS avg_frequency,
    ROUND(AVG(monetary), 2) AS avg_monetary
FROM segmented
GROUP BY customer_segment
ORDER BY customers DESC;

customer_segment	customers	avg_frequency	avg_monetary
稳定客户	83	4.22	1737.81
高价值客户	24	5.00	2876.76
成长客户	8	3.00	1333.21
待唤回客户	5	3.00	1192.67

高价值客户的平均消费明显更高，规则方向符合预期。若某个分群人数为 0，先检查阈值是否超过数据实际范围，而不是直接删除这个分群。

再做一条人数对账，确保所有客户只进入一个分群：

sql

WITH customer_metrics AS (
    SELECT
        customer_id,
        MAX(order_date) AS last_order_date,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(revenue) AS monetary
    FROM v_sales_detail
    GROUP BY customer_id
), segmented AS (
    SELECT
        customer_id,
        CASE
            WHEN frequency >= 5 AND monetary >= 1800 THEN '高价值客户'
            WHEN frequency >= 4 THEN '稳定客户'
            WHEN julianday('2025-07-01') - julianday(last_order_date) > 120
                THEN '待唤回客户'
            ELSE '成长客户'
        END AS customer_segment
    FROM customer_metrics
)
SELECT
    COUNT(*) AS segmented_rows,
    COUNT(DISTINCT customer_id) AS distinct_customers
FROM segmented;

两列都应是 120。边界值也要单独抽查：频次刚好为 4 或 5、消费刚好接近 1800、最近购买间隔在 120 天附近的客户，最容易暴露 > 与 >= 写错。

为不同客户定义下一步

分群只有连接到动作才有用：

分群	可以执行的动作	需要继续观察的指标
高价值客户	新品优先体验、专属权益	毛利、复购间隔
稳定客户	组合推荐、会员升级	频次、客单价
成长客户	第二单激励、内容推荐	30 天复购率
待唤回客户	限定唤回活动	唤回率、活动毛利

这些动作是分析后的假设，仍需要实验验证。

例如对“成长客户”的第二单激励，可以随机留出一组不发券的客户作为对照，比较 30 天复购率，同时比较优惠成本后的增量毛利。只看使用优惠券的人群会产生选择偏差，因为本来就更愿意购买的人更可能领券。

客户标签还涉及使用边界：只使用完成分析所必需的数据，不把敏感属性塞进不可解释的分群规则，并让运营动作具有退出和频控机制。分群的目的应是改善服务和评估策略，不是给客户贴永久标签。下一节会用队列留存把“客户回来没有”放到统一时间尺度上比较。