客户留存分析

在商业分析中，获取新客户固然重要，但留住现有客户往往更加关键。客户留存率直接影响企业的长期盈利能力，一个高留存率的客户群体能够为企业带来持续稳定的收入。 在这一节课中，我们将学习如何进行客户留存分析，使用生存分析的方法来理解客户的“寿命”，计算客户生命周期价值，并通过SQL和Excel来模拟和可视化客户的留存情况，发现影响客户留存的关键因素。

客户留存率是衡量业务健康度的重要指标。高留存率意味着客户对产品或服务满意，愿意持续使用，这通常伴随着更高的客户生命周期价值。相反，低留存率可能表示产品存在问题、服务不到位，或者市场竞争激烈，客户容易流失。

从财务角度看，获取新客户的成本通常远高于保留现有客户的成本。如果客户留存率低，企业就需要不断投入大量资源来获取新客户，以弥补流失客户造成的损失。这不仅增加了营销成本，还可能导致业务增长不稳定。

客户生命周期价值（Customer Lifetime Value, CLV）是指一个客户在整个关系期间为企业带来的总价值。这个价值不仅包括客户已经产生的收入，还包括未来可能产生的收入。

计算客户生命周期价值需要考虑多个因素：客户的购买频率、平均订单金额、客户关系的持续时间、以及客户流失的概率。通过准确计算CLV，我们能够识别高价值客户，优化资源配置，制定更有针对性的营销策略。

客户“寿命”的概念

生存分析（Survival Analysis）原本是医学和工程领域用来分析“生存时间”的统计方法，但在客户分析中，我们可以用它来分析客户的“寿命”，也就是客户从注册到流失的时间。

生存分析

在生存分析中，我们关注的是“事件”发生的时间。对于客户分析来说，这个“事件”就是客户流失。每个客户都有一个“生存时间”，也就是从注册到流失（或到观察期结束）的时间长度。

生存分析的一个特点是它能够处理“删失”（Censoring）数据。在观察期结束时，有些客户可能还没有流失，我们不知道他们什么时候会流失，只知道他们至少存活到了观察期结束。这种数据在传统分析方法中可能被忽略，但生存分析能够充分利用这些信息。

生存函数和风险函数

生存函数（Survival Function）表示客户在时间t之后仍然“存活”（未流失）的概率。这个函数从1开始（所有客户在开始时都存活），随着时间推移逐渐下降（客户逐渐流失）。

风险函数（Hazard Function）表示在时间t时客户流失的瞬时风险率。如果风险函数在某个时间段较高，说明这个时间段客户流失的风险较大；如果风险函数较低，说明客户流失的风险较小。

通过分析生存函数，我们能够了解客户留存率随时间的变化；通过分析风险函数，我们能够识别客户流失的高风险期，从而采取预防措施。

客户流失的定义

在进行留存分析之前，我们需要明确定义什么是“客户流失”。不同的业务可能有不同的定义标准。对于订阅服务，客户流失可能是取消订阅；对于电商平台，客户流失可能是长时间未购买；对于SaaS产品，客户流失可能是停止使用服务。

定义客户流失时，我们需要考虑业务特点。如果定义太严格（比如一个月不购买就算流失），可能会高估流失率；如果定义太宽松（比如一年不购买才算流失），可能会低估流失率，错过挽留客户的最佳时机。

|
SELECT 
    客户ID,
    最后购买日期,
    DATEDIFF(CURDATE(), 最后购买日期) AS 未购买天数,
    CASE 
        WHEN DATEDIFF(CURDATE(), 最后购买日期) > 90 THEN '已流失'
        WHEN DATEDIFF(CURDATE(), 最后购买日期) > 60 THEN '风险客户'
        ELSE '活跃客户'
    END AS 客户状态
FROM 客户表;

这个查询根据客户最后购买日期来判断客户状态。如果客户超过90天未购买，我们将其标记为“已流失”；如果超过60天未购买，标记为“风险客户”；否则标记为“活跃客户”。这个定义可以根据业务特点进行调整。

查询结果示例：

从结果可以看出，大部分客户都是活跃客户，只有C002客户处于风险状态，因为距离最后购买已经60天了。如果再过30天没有购买，就会被标记为“已流失”。

使用SQL模拟客户留存情况

在SQL中，我们可以通过多种方式来模拟和分析客户的留存情况。虽然SQL不是专门的生存分析工具，但它提供了足够的功能来进行基础的留存分析。 为了更好地理解客户留存分析，我们先来看一个具体的示例。假设我们有一家电商平台，记录了客户的注册信息和订单信息。以下是示例数据：

客户表示例数据：

订单表示例数据：

计算客户注册时间和首次购买时间

首先，我们需要确定每个客户的“起始时间”，也就是客户关系的开始时间。这可能是注册时间、首次购买时间，或者其他业务相关的关键时间点。

|
SELECT 
    客户ID,
    注册日期 AS 起始时间,
    MIN(订单日期) AS 首次购买日期,
    DATEDIFF(MIN(订单日期), 注册日期) AS 注册到首次购买天数
FROM 客户表
LEFT JOIN 订单表 ON 客户表.客户ID = 订单表.客户ID
GROUP BY 客户ID, 注册日期;

这个查询计算每个客户的注册时间和首次购买时间，以及两者之间的时间差。如果客户注册后很长时间才首次购买，可能表示客户对产品兴趣不高，或者存在其他问题。

查询结果示例：

从结果可以看出，所有客户都在注册后1-4天内完成了首次购买，这表明客户转化效率较高。C001客户在注册后1天就完成了首次购买，转化速度最快。

计算客户最后活跃时间

要判断客户是否流失，我们需要知道客户最后活跃的时间。这可能是最后购买日期、最后登录日期，或者其他表示客户活跃的指标。

|
SELECT 
    客户ID,
    注册日期,
    MAX(订单日期) AS 最后购买日期,
    DATEDIFF(CURDATE(), MAX(订单日期)) AS 距最后购买天数
FROM 客户表
LEFT JOIN 订单表 ON 客户表.客户ID = 订单表.客户ID
GROUP BY 客户ID, 注册日期;

这个查询计算每个客户的最后购买日期，以及距离今天的天数。如果这个天数很长，说明客户可能已经流失。

查询结果示例

从结果可以看出，C002和C005客户距离最后购买已经超过50天，需要重点关注。C007和C008客户最近有购买行为，活跃度较高。

构建客户生存时间表

为了进行生存分析，我们需要构建一个包含每个客户生存时间的表。这个表应该包括：客户ID、起始时间、结束时间（流失时间或观察期结束时间）、是否流失（1表示流失，0表示删失）。

|
WITH 客户订单 AS (
    SELECT 
        客户ID,
        注册日期,
        MIN(订单日期) AS 首次购买日期,
        MAX(订单日期) AS 最后购买日期,
        COUNT(*) AS 订单数
    FROM 客户表
    LEFT JOIN 订单表 ON 客户表.客户ID = 订单表.客户ID
    GROUP BY 客户ID, 注册日期
),
客户生存 AS

这个查询使用CTE来逐步构建客户生存时间表。首先，客户订单CTE计算每个客户的关键时间点和订单数；然后，客户生存CTE根据最后购买日期判断客户是否流失，计算生存时间。 如果客户超过90天未购买，我们将其标记为已流失，结束时间设为最后购买日期；否则，结束时间设为今天，标记为删失。

查询结果示例

从结果可以看出，所有客户目前都标记为未流失（是否流失=0），因为距离最后购买都未超过90天。C001客户生存天数最长（91天），说明这个客户从注册到现在已经91天了，仍然保持活跃。C008客户生存天数最短（26天），是最近注册的新客户。

计算不同时间段的留存率

留存率是留存分析的核心指标。我们可以计算不同时间段的留存率，比如30天留存率、60天留存率、90天留存率等。

|
WITH 客户生存 AS (
    SELECT 
        客户ID,
        注册日期,
        DATEDIFF(CURDATE(), 注册日期) AS 注册天数,
        CASE 
            WHEN DATEDIFF(CURDATE(), MAX(订单日期)) > 90 THEN 1
            ELSE 0
        END AS 是否流失
    FROM 客户表
    LEFT JOIN 订单表 ON 客户表.客户ID = 订单表.

这个查询计算30天留存率。我们只考虑注册至少30天的客户（使用HAVING子句），然后统计其中未流失的客户比例。类似地，我们可以计算60天、90天等不同时间段的留存率。

查询结果示例

从结果可以看出，在注册至少30天的6个客户中，所有客户都保持留存，30天留存率为100%。这说明我们的客户留存表现很好。不过需要注意的是，这个结果可能受到观察期较短的影响，随着时间推移，留存率可能会有所下降。

计算队列留存率

队列留存率（Cohort Retention Rate）是按照注册时间将客户分组，然后分析每个队列在不同时间点的留存率。这种方法能够帮助我们识别不同时期注册的客户在留存表现上的差异。

|
WITH 客户队列 AS (
    SELECT 
        客户ID,
        DATE_FORMAT(注册日期, '%Y-%m') AS 注册月份,
        注册日期,
        MAX(订单日期) AS 最后购买日期
    FROM 客户表
    LEFT JOIN 订单表 ON 客户表.客户ID = 订单表.客户ID
    GROUP BY 客户ID, 注册日期
),
队列留存 AS (
    SELECT 

这个查询计算每个注册月份的客户队列在不同时间点的留存率。通过对比不同队列的留存率，我们能够发现哪些时期注册的客户留存率更高，这可能与当时的营销策略、产品特性或市场环境有关。

查询结果示例

从结果可以看出，所有队列的30天留存率都是100%，说明客户在注册后30天内都有购买行为。2024-01队列的60天和90天留存率最高，这可能是因为这个队列的客户注册时间较早，有更多时间进行多次购买。 2024-03队列由于注册时间较晚，还没有足够的时间来评估60天和90天留存率。

计算客户生命周期价值

客户生命周期价值是客户留存分析的重要应用。通过计算CLV，我们能够识别高价值客户，优化资源配置，制定更有针对性的营销策略。

基础CLV计算

最简单的CLV计算方法是：平均订单金额 × 购买频率 × 客户平均生命周期。

|
WITH 客户统计 AS (
    SELECT 
        客户ID,
        COUNT(*) AS 订单数,
        AVG(订单金额) AS 平均订单金额,
        SUM(订单金额) AS 累计消费,
        DATEDIFF(MAX(订单日期), MIN(订单日期)) AS 活跃天数,
        CASE 
            WHEN DATEDIFF(CURDATE(), MAX(订单日期)) <= 90 THEN 1

这个查询计算每个客户的历史CLV（累计消费）和预测CLV（基于整体平均值的预测）。预测CLV使用整体平均的订单金额、购买频率和生命周期来估算客户未来的价值。

查询结果示例：

从结果可以看出，C003客户的历史CLV最高（787.00元），说明这个客户已经产生了较高的价值。所有客户的预测CLV都是2847.50元，这是基于整体平均值的预测。需要注意的是，预测CLV是一个估算值，实际值可能会因为客户行为的变化而有所不同。

考虑客户流失概率的CLV

更准确的CLV计算应该考虑客户流失的概率。如果客户流失概率高，其未来价值就应该打折扣。

|
WITH 客户特征 AS (
    SELECT 
        客户ID,
        COUNT(*) AS 订单数,
        AVG(订单金额) AS 平均订单金额,
        DATEDIFF(CURDATE(), MAX(订单日期)) AS 距最后购买天数,
        DATEDIFF(MAX(订单日期), MIN(订单日期)) AS 活跃天数
    FROM 订单表
    GROUP BY 客户ID
),
流失概率 AS (

这个查询根据客户距最后购买的天数来估算流失概率，然后使用这个概率来调整CLV计算。流失概率越高，调整后的CLV越低，这更符合实际情况。

查询结果示例

从结果可以看出，C002客户的流失概率最高（0.7），因为距离最后购买已经60天了，所以调整后年CLV相对较低（1256.40元）。其他客户的流失概率都是0.1，因为距离最后购买都在30天以内，所以调整后年CLV相对较高。C007客户的调整后年CLV最高（3553.20元），说明这个客户不仅平均订单金额高，而且流失风险低。

分群CLV计算

不同客户群体的CLV可能差异很大。我们可以按照客户特征（如注册渠道、首次购买金额、购买频率等）将客户分组，然后分别计算每个群体的CLV。

|
WITH 客户分群 AS (
    SELECT 
        客户ID,
        注册渠道,
        COUNT(*) AS 订单数,
        AVG(订单金额) AS 平均订单金额,
        SUM(订单金额) AS 累计消费,
        DATEDIFF(CURDATE(), MIN(订单日期)) AS 客户年龄天数
    FROM 订单表
    INNER JOIN 客户表 ON 订单表.客户ID = 客户表.客户ID

这个查询按照注册渠道将客户分组，然后分别计算每个群体的平均CLV。通过对比不同群体的CLV，我们能够识别哪些渠道带来的客户更有价值。

查询结果示例（假设今天是2024-04-15）：

从结果可以看出，不同注册渠道的客户历史CLV存在差异。搜索引擎渠道的客户（C001、C004、C007）平均历史CLV为581.00元，直接访问渠道的客户（C003、C006）平均历史CLV为483.00元，社交媒体渠道的客户（C002、C005、C008）平均历史CLV为352.00元。这说明搜索引擎渠道带来的客户价值相对较高。不过需要注意的是，由于示例数据量较小，这个结论可能不够稳定，在实际分析中需要更多的数据来验证。

使用Excel进行留存分析可视化

SQL查询能够帮助我们计算各种留存指标，但要直观地观察留存趋势和模式，可视化是必不可少的。Excel提供了强大的功能来创建留存分析图表。

创建留存曲线图

留存曲线图是留存分析最常用的可视化方式。它展示客户在不同时间点的留存率，帮助我们直观地观察留存趋势。

在Excel中创建留存曲线图，我们首先需要准备数据。数据应该包括时间点（如注册后的天数）和对应的留存率。我们可以从SQL查询结果中获取这些数据，或者直接在Excel中计算。

可直接复制到Excel的测试数据：

|
时间点（天）	留存率（%）
0	100.00
30	85.00
60	72.00
90	65.00
120	58.00
150	52.00
180	48.00

这个表格展示了客户在不同时间点的留存率。我们可以看到，留存率随着时间推移逐渐下降，这是正常的现象。在Excel中，我们可以将时间点作为横轴，留存率作为纵轴，创建折线图来可视化这个趋势。

将上面的数据复制到Excel中，第一列是时间点，第二列是留存率。选中这两列数据，然后在"插入"选项卡中选择"折线图"，即可创建留存曲线图。

创建队列留存热力图

队列留存热力图能够同时展示多个队列在不同时间点的留存率。这种可视化方式非常适合识别留存模式，发现哪些队列表现更好，哪些时间段留存率下降较快。

在Excel中创建热力图，我们可以使用条件格式功能。首先，我们创建一个表格，行是注册月份（队列），列是时间点（如30天、60天、90天），单元格的值是对应的留存率。

队列留存热力图数据示例：

这个表格展示了不同注册月份的客户队列在不同时间点的留存率。我们可以看到，2024-01队列的留存表现最好，在60天时仍然保持100%的留存率。2024-03队列由于注册时间较晚，还没有足够的数据来评估60天和90天留存率。

然后，我们选中数据区域，在"开始"选项卡中选择"条件格式"，选择"色阶"。Excel会根据留存率的高低应用不同的颜色，留存率越高，颜色越深（或越暖）。这样，我们就能一眼看出哪些队列的留存表现更好，哪些时间段的留存率下降较快。

创建客户生命周期价值分布图

客户生命周期价值的分布图能够帮助我们了解客户价值的分布情况。我们可以使用直方图来展示CLV的分布，或者使用箱线图来展示不同客户群体的CLV差异。

在Excel中创建直方图，我们可以使用数据透视表和数据透视图。首先，我们将CLV数据分组（如0-1000、1000-5000、5000-10000等），然后统计每个区间的客户数。

CLV分布数据示例：

这个表格展示了客户生命周期价值的分布情况。我们可以看到，大部分客户的CLV在0-1000元之间，只有少数客户的CLV超过2000元。这个分布可以帮助我们了解客户价值的集中程度，识别高价值客户的比例。

最后，我们创建柱状图，将CLV区间作为横轴，客户数作为纵轴。这样，我们就能直观地看到客户价值的分布情况。

创建流失风险分析图

流失风险分析图能够帮助我们识别哪些客户存在流失风险。我们可以创建一个散点图，横轴是距最后购买的天数，纵轴是客户价值（CLV），点的颜色或大小表示流失风险。

在Excel中创建这样的图表，我们需要准备包含客户ID、距最后购买天数、CLV和流失风险等级的数据。

流失风险分析数据示例：

这个表格展示了每个客户的流失风险情况。我们可以看到，C002和C005客户的流失风险等级为"中"，因为距离最后购买已经超过50天了。虽然C002客户的历史CLV较高（698.00元），但由于距离最后购买时间较长，存在流失风险，需要重点关注。

然后，我们创建散点图，将距最后购买天数作为横轴，历史CLV作为纵轴，并根据流失风险等级设置不同的颜色。这样，我们就能一眼看出哪些高价值客户存在流失风险，需要优先关注。通常，位于图表右上角的客户（高CLV、长时间未购买）是最需要关注的。

留存分析的商业应用

掌握了留存分析的方法后，我们可以将这些方法应用到实际的商业场景中，解决具体的业务问题。

识别高价值客户群体

通过留存分析和CLV计算，我们能够识别高价值客户群体。这些客户通常具有较高的留存率和生命周期价值，值得我们投入更多资源来维护。

我们可以按照客户特征（如注册渠道、首次购买金额、购买频率等）将客户分组，然后分析每个群体的留存率和CLV。表现最好的群体就是我们的高价值客户群体，我们应该针对这些群体制定专门的 retention 策略。

预测客户流失风险

留存分析能够帮助我们预测客户流失风险。通过分析客户的购买行为、互动频率等指标，我们能够识别哪些客户可能即将流失，从而及时采取措施，挽留这些客户。

我们可以建立一个简单的流失风险评分模型，根据客户的各项指标（如距最后购买天数、购买频率、平均订单金额等）计算流失风险分数。风险分数高的客户应该优先关注，我们可以通过个性化营销、优惠活动等方式来挽留这些客户。

优化客户获取策略

通过对比不同渠道或不同时期获取的客户的留存表现，我们能够优化客户获取策略。如果某个渠道带来的客户留存率特别高，我们应该加大在这个渠道的投入；如果某个渠道带来的客户留存率特别低，我们需要分析原因，或者减少投入。

同样，如果某个时期的营销活动带来的客户留存率特别高，我们可以分析这个活动的特点，将成功的经验应用到其他活动中。

课程小结

这节课后我们应该掌握了客户留存分析和生存分析的整体流程，包括从理解生存分析概念、计算客户留存率，到运用SQL进行模拟与Excel进行可视化。 生存分析为我们提供了分析客户生命周期的框架，客户生命周期价值（CLV）帮助优化资源配置和营销策略，而Excel丰富的可视化工具也助力于更直观地呈现分析结果。

在实践中，留存分析需要结合实际业务，首先要明确定义"客户流失"的标准，并洞察流失背后的深层原因，制定有针对性的retention策略。

在下一节课中，我们将学习"影响生存的因素"，深入探索影响客户留存的各种因素。这将帮助我们理解为什么某些客户留存率高，某些客户留存率低，并学会构建预测模型来识别流失风险客户。