12 / 12

构建客户签名 | 自在学

构建客户签名

在前面的课程中，我们学习了如何分析客户的各种特征：购买行为、留存情况、地理位置、时间模式等。但如果我们想要全面理解一个客户，我们需要将这些特征整合在一起，形成一个综合的描述。这个综合描述就是“客户签名”（Customer Signature）。

构建客户签名

理解客户签名

客户签名就像是客户的“身份证”，它用一组特征来描述一个客户。就像我们的身份证包含了姓名、性别、出生日期、地址等信息一样，客户签名包含了客户的各种特征：基本信息、购买行为、偏好、价值等。

客户签名的价值

客户签名的价值在于它能够帮助我们快速理解一个客户。当我们看到一个客户签名时，我们能够立即了解这个客户的基本情况、购买习惯、价值水平等，而不需要去查看大量的原始数据。

客户签名还能够帮助我们进行客户分群。通过比较不同客户的签名，我们能够识别相似的客户，将他们归为一类，然后针对不同的客户群体制定不同的策略。

更重要的是，客户签名可以作为后续分析的基础。无论是客户流失预测、产品推荐，还是营销活动设计，都可以基于客户签名来进行。

客户签名不是固定不变的，它会随着客户行为的变化而变化。我们需要定期更新客户签名，确保它能够反映客户的最新状态。

客户签名的组成

一个完整的客户签名通常包括多个维度的特征：

维度	主要内容举例	说明
基本信息	年龄、性别、地理位置、注册时间	这些属性较为稳定，变化不频繁
购买行为	购买次数、总消费金额、平均订单金额、最后购买日期	反映客户的购买习惯和整体价值
产品偏好	偏好的产品类别、品牌、价格区间	帮助了解客户的兴趣和需求
互动行为	登录频率、浏览页面、点击行为	体现客户在平台的活跃程度和参与情况
价值指标	客户生命周期价值、留存概率、推荐价值	用于评估客户的整体商业价值

让我们先创建一些测试数据，帮助我们理解客户签名的构建过程：

|
-- 创建客户基本信息表
CREATE TABLE 客户基本信息表 (
    客户ID INT,
    注册日期 DATE,
    年龄 INT,
    性别 VARCHAR(10),
    所在城市 VARCHAR(20)
);
 
-- 创建客户购买行为表
CREATE TABLE 客户购买行为表 (
    客户ID INT,
    订单日期 DATE,
    订单金额 DECIMAL(10,2),
    产品类别 VARCHAR(20)
);
 
-- 插入测试数据
INSERT INTO 客户基本信息表 VALUES
(1, '2023-01-15', 28, '男', '北京'),
(2, '2023-02-20', 35, '女', '上海'),
(3, '2023-03-10', 42, '男', '广州'),
(4, '2023-04-05', 25, '女', '深圳'),
(5, '2023-05-12', 38, '男', '北京');
 
INSERT INTO 客户购买行为表 VALUES
(1, '2023-02-01', 299.00, '电子产品'),
(1, '2023-03-15', 599.00, '服装'),
(1, '2023-04-20', 399.00, '电子产品'),
(2, '2023-03-01', 899.00, '电子产品'),
(2, '2023-04-10', 499.00, '服装'),
(2, '2023-05-20', 699.00, '电子产品'),
(2, '2023-06-15', 299.00, '图书'),
(3, '2023-04-01', 149.00, '图书'),
(3, '2023-05-10', 199.00, '图书'),
(4, '2023-05-01', 799.00, '电子产品'),
(4, '2023-06-10', 599.00, '电子产品'),
(5, '2023-06-01', 399.00, '服装'),
(5, '2023-07-10', 299.00, '服装');

这个测试数据包含了5个客户的基本信息和购买行为。现在，让我们基于这些数据来构建客户签名。

使用SQL构建客户签名

使用SQL构建客户签名，我们需要将客户的各种特征整合在一起。这通常需要多个表的连接和聚合操作。

计算购买行为特征

首先，我们计算每个客户的购买行为特征：

|
WITH 客户购买统计 AS (
    SELECT 
        客户ID,
        COUNT(*) AS 购买次数,
        SUM(订单金额) AS 总消费金额,
        AVG(订单金额) AS 平均订单金额,
        MIN(订单日期) AS 首次购买日期,
        MAX(订单日期) AS 最后购买日期,
        DATEDIFF(CURDATE(), MAX(订单日期)) AS 距最后购买天数
    FROM 客户购买行为表
    GROUP BY 客户ID

|
+----------+--------------+-----------------+--------------------+--------------------+--------------------+-----------------------+
| 客户ID   | 购买次数     | 总消费金额      | 平均订单金额       | 首次购买日期       | 最后购买日期       | 距最后购买天数        |
+----------+--------------+-----------------+--------------------+--------------------+--------------------+-----------------------+
|        1 |            3 |         1297.00 |         432.333333 | 2023-02-01         | 2023-04-20         |                   946 |
|        2 |            4 |

这个查询计算每个客户的基本购买行为特征。从结果中，我们能够看到每个客户购买了多少次，消费了多少钱，平均订单金额是多少，以及距离最后购买有多久。

计算产品偏好特征

接下来，我们计算每个客户的产品偏好特征：

|
WITH 客户产品偏好 AS (
    SELECT 
        客户ID,
        产品类别,
        COUNT(*) AS 类别购买次数,
        SUM(订单金额) AS 类别消费金额
    FROM 客户购买行为表
    GROUP BY 客户ID, 产品类别
),
客户主要偏好 AS (
    SELECT 
        客户ID,
        MAX(CASE WHEN 类别购买次数 = (
            SELECT MAX

|
+----------+-----------------+-----------------+-----------------+
| 客户ID   | 最偏好类别      | 总购买次数      | 购买类别数      |
+----------+-----------------+-----------------+-----------------+
|        1 | 电子产品        |               3 |               2 |
|        2 | 电子产品        |               4 |               3 |
|        3 | 图书            |               2 |               1 |
|

这个查询识别每个客户最偏好的产品类别，以及客户购买的产品类别多样性。从结果中，我们能够了解每个客户的兴趣和需求。

整合客户签名

在前面分析了购买行为和产品偏好之后，接下来我们要把这些不同维度的特征，以及后续可能补充的价值评估、活跃度等综合起来，为每一位客户构建一个结构化的、包含关键信息的“客户签名”。这个过程的本质，是将原本分散在多张表或不同字段中的数据进行聚合，将基础属性、行为表现、兴趣特征和业务层面的价值标签统一汇总到同一份摘要里。这样，每个客户的所有主要特征一目了然，非常方便后续进行群体分析、精准营销或者自动化推荐等应用。

举个例子，最终的客户签名里面可能会包含：基本身份信息（比如城市和注册时间）、最近一次和历史上的购买行为、客户最关注的品类、常买商品类别的数量，再加上基于这些数据计算出的客户价值等级和活跃程度等。整个流程，实际上就是用 SQL 把这些内容分别统计出来后，通过客户ID把这些子结果表合并，最终汇总出全面的客户描述。

|
WITH 客户购买统计 AS (
    SELECT 
        客户ID,
        COUNT(*) AS 购买次数,
        SUM(订单金额) AS 总消费金额,
        AVG(订单金额) AS 平均订单金额,
        MIN(订单日期) AS 首次购买日期,
        MAX(订单日期) AS 最后购买日期,
        DATEDIFF(CURDATE(), MAX(订单日期)) AS 距最后购买天数
    FROM 客户购买行为表
    GROUP BY 客户ID

|
+----------+--------------+--------+--------+--------------+--------------+-----------------+--------------------+--------------------+--------------------+-----------------------+-----------------+-----------------+--------------+-----------+
| 客户ID   | 注册日期     | 年龄   | 性别   | 所在城市     | 购买次数     | 总消费金额      | 平均订单金额       | 首次购买日期       | 最后购买日期       | 距最后购买天数        | 最偏好类别      | 购买类别数      | 价值等级     | 活跃度    |
+----------+--------------+--------+--------+--------------+--------------+-----------------+--------------------+--------------------+--------------------+-----------------------+-----------------+-----------------+--------------+-----------+
|        2

这个查询整合了客户的所有特征，形成完整的客户签名。从结果中，我们能够全面了解每个客户的情况。

使用Excel构建和管理客户签名

虽然SQL可以计算客户签名的各个特征，但Excel更适合用于构建、管理和可视化客户签名。

准备客户签名表

首先，我们将SQL查询结果导出到Excel，创建一个客户签名表。这个表应该包含每个客户的所有特征，每一行代表一个客户，每一列代表一个特征。

在Excel中，我们可以使用数据透视表来快速创建客户签名表。将客户ID拖到行区域，将各种特征拖到值区域，并设置合适的聚合方式（求和、平均值、计数等）。

标准化特征值

不同的特征可能有不同的量级和单位。比如，年龄可能是20-50，而总消费金额可能是0-10000。为了便于比较和分析，我们需要对特征进行标准化。

在Excel中，我们可以使用公式来标准化特征值。比如，我们可以使用Z-score标准化：=(A2-AVERAGE(A:A))/STDEV(A:A)，或者使用最小-最大标准化：=(A2-MIN(A:A))/(MAX(A:A)-MIN(A:A))。

标准化后的特征值都在相似的范围内，便于比较和分析。

创建客户画像

在Excel中，我们可以为每个客户创建一个可视化的画像。我们可以使用条件格式来突出显示重要的特征，或者使用图表来展示客户的特征分布。

比如，我们可以创建一个雷达图，展示客户在各个维度上的表现。每个维度代表一个特征（如购买次数、总消费金额、活跃度等），客户在各个维度上的值用不同的颜色表示。这样，我们就能直观地看出每个客户的特征模式。

应用客户签名

构建好客户签名后，我们可以将它们应用到各种分析和决策中。

客户分群和细分

客户签名最直接的应用是客户分群。通过比较不同客户的签名，我们能够识别相似的客户，将他们归为一类。

|
WITH 客户签名 AS (
    -- 上面的客户签名查询
),
客户分群 AS (
    SELECT 
        客户ID,
        价值等级,
        活跃度,
        最偏好类别,
        CONCAT(价值等级, '-', 活跃度, '-', 最偏好类别) AS 客户群体
    FROM 客户签名
)
SELECT 
    客户群体,
    COUNT(*) AS 客户数,
    ROUND(

|
+----------------------------------+-----------+-----------------+--------------------+
| 客户群体                         | 客户数    | 平均总消费      | 平均购买次数       |
+----------------------------------+-----------+-----------------+--------------------+
| 低价值-不活跃-电子产品           |         2 |         1347.50 |                2.5 |
| 中价值-不活跃-电子产品           |         1 |         2396.00 |                4.0 |
| 低价值-不活跃-图书               |         1 |          348.00 |                2.0 |

这个查询基于客户签名进行分群。从结果中，我们能够看到不同客户群体的数量和特征，从而制定差异化的策略。

客户流失预测

客户签名可以用于预测客户流失。我们可以基于客户签名的特征（如距最后购买天数、购买频率、总消费金额等）来预测客户是否会流失。

|
WITH 客户签名 AS (
    -- 上面的客户签名查询
),
流失风险评分 AS (
    SELECT 
        客户ID,
        距最后购买天数,
        购买次数,
        总消费金额,
        CASE 
            WHEN 距最后购买天数 > 90 THEN 3
            WHEN 距最后购买天数 > 60 THEN 2
            ELSE 1
        END +
        CASE 
            WHEN

|
+----------+--------------------+--------------------+
| 客户ID   | 流失风险分数       | 流失风险等级       |
+----------+--------------------+--------------------+
|        3 |                  5 | 高风险             |
|        5 |                  5 | 高风险             |
|        1 |                  4 | 中风险             |
|        4 |                  4 | 中风险             |
|        2

这个查询基于客户签名计算流失风险分数。风险分数越高的客户，流失的可能性越大，我们应该优先关注。

课程小结

通过构建客户签名，我们能直观掌握每个客户的整体情况，为后续的客户分群、流失预测、产品推荐和营销策略等分析提供数据基础。在实际操作中，我们要根据业务目标挑选合适的特征，并定期维护和更新客户签名，让它始终反映客户的真实变化。

通过这十几节课的学习，我们完成了一次全面的SQL和Excel数据分析之旅。我们从基础的SQL查询开始，逐步深入到数据探索、统计分析、预测建模等高级主题。相信现在，你已经掌握了数据分析的基本技能，能够独立完成数据分析工作。

数据分析是一个不断发展的领域，新的方法、工具和技术不断涌现。我们应该保持学习的心态，持续更新知识，提高技能。同时，我们要多实践，将学到的知识应用到实际工作中，通过实践来巩固和深化理解。

希望这个教程能够帮助你掌握SQL和Excel数据分析的技能，在数据分析的道路上走得更远。记住，数据分析不仅是技术，更是艺术。在掌握技术的同时，我们也要培养数据思维，理解业务逻辑，才能真正发挥数据的价值。