行为模式
在商业分析中，客户的重复行为往往比单次行为更有价值。一个客户重复购买、重复登录、重复互动，说明他对产品或服务有持续的认可和需求。 在这一节课中，我们将学习如何分析客户的重复行为，使用SQL查询来识别和统计重复事件，并通过Excel来可视化这些行为的频率和模式，发现隐藏在重复行为背后的商业价值。
理解重复事件的价值
重复事件是指客户在一段时间内多次发生的相同或类似行为。最常见的重复事件是重复购买，但重复登录、重复浏览、重复互动等行为同样重要。这些重复行为能够帮助我们理解客户的忠诚度、活跃度和价值。
重复购买的意义
重复购买是客户忠诚度的重要指标。如果一个客户只购买一次就不再购买，他可能只是尝试性购买，对产品兴趣不高；但如果一个客户多次购买，说明他对产品满意，有持续的需求，更可能成为长期客户。
从商业角度看，重复购买的客户通常具有更高的生命周期价值。他们不仅带来了多次收入，还降低了企业的获客成本（因为不需要重新获取这些客户），并且可能通过口碑传播带来新客户。
其他重复事件的价值
除了购买，其他重复事件也有重要价值。比如，客户重复登录说明他对产品有持续的兴趣，重复浏览某些页面可能表示他在考虑购买，重复使用某个功能可能表示这个功能对他很有价值。
分析这些重复事件，我们能够识别高价值客户、发现产品使用模式、优化用户体验，并制定更有针对性的营销策略。
重复事件分析不仅关注"是否重复"，更重要的是关注"重复的频率"和"重复的模式"。不同的频率和模式可能代表不同类型的客户。
让我们先创建一些测试数据，帮助我们理解分析过程。假设我们有一个订单表，记录了客户的购买行为：
-- 创建订单测试表
CREATE TABLE 订单测试表 (
订单ID INT,
客户ID INT,
订单日期 DATE,
订单金额 DECIMAL(10,2),
产品类别 VARCHAR(20)
);
-- 插入测试数据
INSERT INTO 订单测试表 VALUES
(1, 101, '2024-01-05', 299.00, '电子产品'),
(2, 101, '2024-02-10', 599.00, '服装'),
(3, 101, '2024-03-15', 399.00, '电子产品'),
(4, 102, '2024-01-20', 199.00, '图书'),
(5, 103, '2024-02-01', 899.00, '电子产品'),
(6, 103, '2024-02-25', 499.00, '服装'),
(7, 103, '2024-03-10', 699.00, '电子产品'),
(8, 103, '2024-04-05', 399.00, '图书'),
(9, 104, '2024-01-15', 149.00, '图书'),
(10, 105, '2024-02-20', 799.00, '电子产品'),
(11, 105, '2024-03-25', 599.00, '电子产品'),
(12, 105, '2024-04-10', 299.00, '服装');这个测试数据包含了12个订单，涉及5个客户。我们可以看到，客户101、103和105都有多次购买，而客户102和104只有一次购买。通过分析这些数据，我们能够理解如何识别和分析重复购买行为。
识别重复购买的客户
首先，我们需要识别哪些客户有重复购买行为。这看起来很简单，但实际上需要考虑很多细节。
统计每个客户的购买次数
最基本的分析是统计每个客户购买了多少次：
SELECT
客户ID,
COUNT(*) AS 购买次数,
SUM(订单金额) AS 总消费金额,
AVG(订单金额) AS 平均订单金额,
MIN(订单日期) AS 首次购买日期,
MAX(订单日期) AS 最后购买日期
FROM 订单测试表
GROUP BY 客户ID
ORDER BY 购买次数 DESC;这个查询会返回类似这样的结果：
+----------+--------------+-----------------+--------------------+--------------------+--------------------+
| 客户ID | 购买次数 | 总消费金额 | 平均订单金额 | 首次购买日期 | 最后购买日期 |
+----------+--------------+-----------------+--------------------+--------------------+--------------------+
| 103 | 4 | 2496.00 | 624.000000 | 2024-02-01 | 2024-04-05 |
| 101 | 3 | 1297.00 | 432.333333 | 2024-01-05 | 2024-03-15 |
| 105 | 3 | 1697.00 | 565.666667 | 2024-02-20 | 2024-04-10 |
| 102 | 1 | 199.00 | 199.000000 | 2024-01-20 | 2024-01-20 |
| 104 | 1 | 149.00 | 149.000000 | 2024-01-15 | 2024-01-15 |
+----------+--------------+-----------------+--------------------+--------------------+--------------------+从这个结果可以看出，客户103购买次数最多（4次），总消费金额也最高。客户102和104都只购买了一次，可能是新客户或者已经流失的客户。
识别重复购买客户
我们可以进一步筛选出有重复购买的客户（购买次数大于1的客户）：
SELECT
客户ID,
COUNT(*) AS 购买次数,
SUM(订单金额) AS 总消费金额,
DATEDIFF(MAX(订单日期), MIN(订单日期)) AS 购买周期天数
FROM 订单测试表
GROUP BY 客户ID
HAVING COUNT(*) > 1
ORDER BY 购买次数 DESC;+----------+--------------+-----------------+--------------------+
| 客户ID | 购买次数 | 总消费金额 | 购买周期天数 |
+----------+--------------+-----------------+--------------------+
| 103 | 4 | 2496.00 | 64 |
| 101 | 3 | 1297.00 | 70 |
| 105 | 3 | 1697.00 | 50 |
+----------+--------------+-----------------+--------------------+这个查询使用HAVING子句来筛选购买次数大于1的客户。购买周期天数计算了从首次购买到最后购买的时间跨度，这能够帮助我们了解客户的购买频率。
分析购买频率
购买频率是重复购买分析的重要指标。我们可以计算每个客户的平均购买间隔：
WITH 客户订单 AS (
SELECT
客户ID,
订单日期,
ROW_NUMBER() OVER (PARTITION BY 客户ID ORDER BY 订单日期) AS 订单序号
FROM 订单测试表
),
购买间隔 AS (
SELECT
c1.客户ID,
c1.订单日期 AS 当前订单日期,
c2.订单日期 AS 上次订单日期,
DATEDIFF(c1.订单日期, c2.订单日期) AS 间隔天数
FROM 客户订单 c1
LEFT JOIN 客户订单 c2
ON c1.客户ID = c2.客户ID
AND c1.订单序号 = c2.订单序号 + 1
WHERE c2.订单日期 IS NOT NULL
)
SELECT
客户ID,
COUNT(*) AS 购买间隔次数,
ROUND(AVG(间隔天数), 1) AS 平均间隔天数,
MIN(间隔天数) AS 最短间隔,
MAX(间隔天数) AS 最长间隔
FROM 购买间隔
GROUP BY 客户ID
ORDER BY 平均间隔天数;+----------+--------------------+--------------------+--------------+--------------+
| 客户ID | 购买间隔次数 | 平均间隔天数 | 最短间隔 | 最长间隔 |
+----------+--------------------+--------------------+--------------+--------------+
| 103 | 3 | 21.3 | 14 | 26 |
| 105 | 2 | 25.0 | 16 | 34 |
| 101 | 2 | 35.0 | 34 | 36 |
+----------+--------------------+--------------------+--------------+--------------+这个查询使用窗口函数和自连接来计算每次购买之间的间隔天数，然后计算平均间隔。平均间隔越短，说明客户购买频率越高，对产品需求越持续。
分析重复购买的模式
除了统计重复购买的次数和频率，我们还可以分析重复购买的模式。比如，客户是否总是购买同一类产品，还是购买不同的产品类别。
分析产品类别偏好
让我们看看每个客户购买的产品类别分布：
SELECT
客户ID,
产品类别,
COUNT(*) AS 购买次数,
SUM(订单金额) AS 类别消费金额
FROM 订单测试表
GROUP BY 客户ID, 产品类别
ORDER BY 客户ID, 购买次数 DESC;+----------+--------------+--------------+--------------------+
| 客户ID | 产品类别 | 购买次数 | 类别消费金额 |
+----------+--------------+--------------+--------------------+
| 101 | 电子产品 | 2 | 698.00 |
| 101 | 服装 | 1 | 599.00 |
| 102 | 图书 | 1 | 199.00 |
| 103 | 电子产品 | 2 | 1598.00 |
| 103 | 服装 | 1 | 499.00 |
| 103 | 图书 | 1 | 399.00 |
| 104 | 图书 | 1 | 149.00 |
| 105 | 电子产品 | 2 | 1398.00 |
| 105 | 服装 | 1 | 299.00 |
+----------+--------------+--------------+--------------------+这个查询会返回每个客户在每个产品类别上的购买情况。从结果中，我们可能会发现某些客户总是购买同一类产品（比如总是买电子产品），而某些客户会购买多种不同的产品类别。
识别忠诚客户
我们可以根据购买次数和消费金额来识别忠诚客户。一个简单的定义是：购买次数大于等于3次，且总消费金额超过1000元的客户：
SELECT
客户ID,
COUNT(*) AS 购买次数,
SUM(订单金额) AS 总消费金额,
AVG(订单金额) AS 平均订单金额,
COUNT(DISTINCT 产品类别) AS 购买类别数
FROM 订单测试表
GROUP BY 客户ID
HAVING COUNT(*) >= 3 AND SUM(订单金额) > 1000
ORDER BY 总消费金额 DESC;+----------+--------------+-----------------+--------------------+-----------------+
| 客户ID | 购买次数 | 总消费金额 | 平均订单金额 | 购买类别数 |
+----------+--------------+-----------------+--------------------+-----------------+
| 103 | 4 | 2496.00 | 624.000000 | 3 |
| 105 | 3 | 1697.00 | 565.666667 | 2 |
| 101 | 3 | 1297.00 | 432.333333 | 2 |
+----------+--------------+-----------------+--------------------+-----------------+这个查询识别出符合忠诚客户标准的客户。这些客户通常具有较高的生命周期价值，值得我们重点关注和维护。
分析其他重复事件
除了购买，我们还可以分析其他重复事件，比如登录、浏览、互动等。虽然这些事件的数据结构可能不同，但分析方法类似。
创建登录事件测试数据
让我们创建一个登录事件的测试数据：
-- 创建登录事件测试表
CREATE TABLE 登录事件测试表 (
登录ID INT,
客户ID INT,
登录日期 DATE,
登录时间 TIME,
登录设备 VARCHAR(20)
);
-- 插入测试数据
INSERT INTO 登录事件测试表 VALUES
(1, 101, '2024-01-05', '09:15:00', '手机'),
(2, 101, '2024-01-10', '14:30:00', '电脑'),
(3, 101, '2024-01-15', '20:45:00', '手机'),
(4, 102, '2024-01-20', '10:00:00', '电脑'),
(5, 103, '2024-02-01', '08:30:00', '手机'),
(6, 103, '2024-02-05', '16:20:00', '手机'),
(7, 103, '2024-02-10', '11:15:00', '电脑'),
(8, 103, '2024-02-15', '19:30:00', '手机'),
(9, 103, '2024-02-20', '13:45:00', '手机'),
(10, 104, '2024-01-15', '15:00:00', '电脑');这个测试数据包含了10次登录事件，涉及4个客户。我们可以看到，客户103登录次数最多（5次），而客户104只登录了一次。
分析登录频率
我们可以用类似的方法分析登录频率：
SELECT
客户ID,
COUNT(*) AS 登录次数,
COUNT(DISTINCT 登录日期) AS 登录天数,
MIN(登录日期) AS 首次登录日期,
MAX(登录日期) AS 最后登录日期,
DATEDIFF(MAX(登录日期), MIN(登录日期)) AS 活跃天数
FROM 登录事件测试表
GROUP BY 客户ID
ORDER BY 登录次数 DESC;+----------+--------------+--------------+--------------------+--------------------+--------------+
| 客户ID | 登录次数 | 登录天数 | 首次登录日期 | 最后登录日期 | 活跃天数 |
+----------+--------------+--------------+--------------------+--------------------+--------------+
| 103 | 5 | 5 | 2024-02-01 | 2024-02-20 | 19 |
| 101 | 3 | 3 | 2024-01-05 | 2024-01-15 | 10 |
| 102 | 1 | 1 | 2024-01-20 | 2024-01-20 | 0 |
| 104 | 1 | 1 | 2024-01-15 | 2024-01-15 | 0 |
+----------+--------------+--------------+--------------------+--------------------+--------------+这个查询统计每个客户的登录情况。登录天数统计了客户有多少天登录过，这能够帮助我们了解客户的活跃程度。如果登录次数很多但登录天数很少，说明客户在短时间内频繁登录，可能是对某个功能或产品特别感兴趣。
分析登录时间模式
我们还可以分析客户登录的时间模式，比如客户更喜欢在什么时间登录：
SELECT
客户ID,
CASE
WHEN HOUR(登录时间) BETWEEN 6 AND 11 THEN '上午'
WHEN HOUR(登录时间) BETWEEN 12 AND 17 THEN '下午'
WHEN HOUR(登录时间) BETWEEN 18 AND 21 THEN '晚上'
ELSE '深夜'
END AS 时间段,
COUNT(*) AS 登录次数
FROM 登录事件测试表
GROUP BY 客户ID,
CASE
WHEN HOUR(登录时间) BETWEEN 6 AND 11 THEN '上午'
WHEN HOUR(登录时间) BETWEEN 12 AND 17 THEN '下午'
WHEN HOUR(登录时间) BETWEEN 18 AND 21 THEN '晚上'
ELSE '深夜'
END
ORDER BY 客户ID, 登录次数 DESC;+----------+-----------+--------------+
| 客户ID | 时间段 | 登录次数 |
+----------+-----------+--------------+
| 101 | 上午 | 1 |
| 101 | 下午 | 1 |
| 101 | 晚上 | 1 |
| 102 | 上午 | 1 |
| 103 | 上午 | 2 |
| 103 | 下午 | 2 |
| 103 | 晚上 | 1 |
| 104 | 下午 | 1 |
+----------+-----------+--------------+这个查询分析每个客户在不同时间段的登录情况。了解客户的登录时间模式，我们能够在合适的时间推送消息或进行营销活动，提高效果。
使用Excel分析重复事件频率
SQL查询能够帮助我们获取重复事件的数据，但要直观地观察频率和模式，Excel的可视化功能非常有用。
准备分析数据
首先，我们将SQL查询结果导出到Excel。数据应该包括客户ID、事件类型、事件日期、事件次数等信息。
创建购买频率分布图
我们可以创建一个柱状图来展示购买频率的分布。横轴是购买次数（如1次、2次、3次等），纵轴是对应的客户数量。这样，我们就能直观地看出有多少客户只购买一次，有多少客户购买多次。
在Excel中创建这个图表很简单。我们首先使用数据透视表来统计不同购买次数的客户数量，然后创建柱状图。如果发现大部分客户只购买一次，说明我们需要加强客户 retention 策略；如果发现有很多客户购买多次，说明我们的产品和服务得到了客户的认可。
购买次数 客户数量
1 2
2 0
3 2
4 1将上面的数据复制到Excel中，第一列是购买次数，第二列是对应的客户数量。选中这两列数据，然后在"插入"选项卡中选择"柱状图"，即可创建购买频率分布图。从数据可以看出，有2个客户只购买1次，有2个客户购买3次，有1个客户购买4次。
创建时间序列图
我们可以创建一个时间序列图来展示客户购买的时间分布。横轴是时间（日期），纵轴是购买次数或客户数。这样，我们就能看出购买活动是否有时间规律，比如是否在某些日期购买特别集中。
在Excel中，我们可以使用折线图或柱状图来创建时间序列图。如果数据是按日统计的，我们可以使用折线图；如果数据是按周或按月统计的，我们可以使用柱状图。通过观察时间序列图，我们能够识别购买高峰期和低谷期，从而优化库存和营销策略。
日期 购买次数
2024-01-05 1
2024-01-10 0
2024-01-15 2
2024-01-20 2
2024-02-01 1
2024-02-10 1
2024-02-15 0
2024-02-20 1
2024-02-25 1
2024-03-10 1
2024-03-15 1
2024-03-25 1
2024-04-05 1
2024-04-10 1将上面的数据复制到Excel中，第一列是日期，第二列是当天的购买次数。选中这两列数据，然后在"插入"选项卡中选择"折线图"，即可创建时间序列图。从数据可以看出，1月15日和1月20日的购买次数较多，可能是购买高峰期。
创建客户活跃度热力图
我们可以创建一个热力图来展示客户的活跃度。行是客户ID，列是时间段（如周一到周日，或上午、下午、晚上），单元格的值是登录次数或购买次数。这样，我们就能直观地看出哪些客户最活跃，以及他们活跃的时间模式。
在Excel中创建热力图，我们可以使用条件格式功能。选中数据区域，然后在"开始"选项卡中选择"条件格式"，选择"色阶"。Excel会根据数值大小应用不同的颜色，数值越大，颜色越深。这样，我们就能一眼看出哪些客户在哪些时间段最活跃。
客户ID 上午 下午 晚上 深夜
101 1 1 1 0
102 1 0 0 0
103 2 2 1 0
104 0 1 0 0
105 0 1 1 0将上面的数据复制到Excel中，第一列是客户ID，后面各列是不同时间段的登录次数。选中包含数据的区域（不包括标题行），然后在"开始"选项卡中选择"条件格式"，选择"色阶"，Excel会根据数值大小应用不同的颜色。从数据可以看出，客户103在上午和下午的活跃度最高，客户101在各个时间段都有活动。
分析重复购买周期
我们可以分析客户的重复购买周期，看看客户平均多久购买一次。这能够帮助我们预测客户的下次购买时间，从而在合适的时间进行营销。
在Excel中，我们可以创建一个散点图，横轴是客户ID，纵轴是平均购买间隔天数。如果发现某些客户的购买间隔特别短，说明他们是高频客户，值得我们重点关注；如果发现某些客户的购买间隔特别长，可能需要通过营销活动来唤醒他们。
客户ID 平均购买间隔天数
101 35.0
103 21.3
105 25.0将上面的数据复制到Excel中，第一列是客户ID，第二列是平均购买间隔天数。选中这两列数据，然后在"插入"选项卡中选择"散点图"，即可创建重复购买周期分析图。从数据可以看出，客户103的购买间隔最短（21.3天），说明他是高频客户；客户101的购买间隔最长（35.0天），可能需要通过营销活动来唤醒。
课程小结
这节课我们不仅学会了通过数据来识别高价值客户、分析消费特征，还掌握了选择合适时间窗口和定义“重复”标准的方法，并认识到重复事件分析要兼顾重复的频率与模式，才能制定更具针对性的客户管理和营销策略。
重复事件分析是持续优化客户价值和提升业务增长的有力工具。通过这些分析，我们能不断发现市场中的新机会，及时调整运营策略，提高客户留存和生命周期价值。下一个部分我们将帮助大家理解客户的购买组合和产品之间的内在关联。