在前面的课程中,我们学习了如何分析客户的各种特征:购买行为、留存情况、地理位置、时间模式等。但如果我们想要全面理解一个客户,我们需要将这些特征整合在一起,形成一个综合的描述。 这个综合描述就是“客户签名”(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构建客户签名,我们需要将客户的各种特征整合在一起。这通常需要多个表的连接和聚合操作。
首先,我们计算每个客户的购买行为特征:
|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
这个查询整合了客户的所有特征,形成完整的客户签名。从结果中,我们能够全面了解每个客户的情况。
虽然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数据分析的技能,在数据分析的道路上走得更远。记住,数据分析不仅是技术,更是艺术。在掌握技术的同时,我们也要培养数据思维,理解业务逻辑,才能真正发挥数据的价值。