保姆级教学：手把手教你如何实现同期群分析

作者简介
HeoiJin：立志透过数据看清世界的产品策划，专注爬虫、数据分析、产品策划领域。
万物皆营销 | 资本永不眠 | 数据恒真理
CSDN：https://me.csdn.net/weixin_40679090

目录：

1. 前言
2. 项目准备
3. 同期群分析概念讲解
4. 材料梳理
5. Excel实现
6. MySQL实现
7. Python实现
8. 复盘总结

一、前言

后互联网时代，获客拉新的成本越来越高，如何增加客户的留存，提高客户的复购次数、购买金额等变得十分重要，同期群分析便是当中非常重要的分析方法。

关于同期群分析概念和思路的文章很多，但分享如何实现的文章非常罕见。因此，本文将简单介绍同期群分析的概念，并用数据分析师的三板斧ESP（Excel、MySQL、Python）分别实现同期群分析。

二、项目准备

• Excel：
  • office或wps均可，office 2013后的版本更好
• MySQL：
  • 版本： 8.0（本次不涉及窗口函数，其他版本亦可）
  • Navicat
• Python：
  • 版本：3.7
  • IDE：pycharm
  • 库：pandas、xlrt

PS.

• 因篇幅原因，可能会有未能详细讲解的过程
• 完整源码及数据集请移步至文末链接或阅读原文

三、同期群分析概念讲解

数据分析最终目标都是为了解决业务问题，任何分析方法都只是工具。因此在详细讲解如何实现之前，需要先明晰方法的含义是什么，能带来什么收益，才能在合适的问题上选对分析方法。

3.1 同期群分析含义

同期群(Cohort)即相同时间内具有相似或特定属性 、行为的群体。核心要素为时间+特定属性，比如把00后出生的人划分为一个群组。

同期群分析指将用户进行同期群划分后，对比不同同期群用户的相同指标。我们耳熟能详的留存率就是同期群分析的其中一种，案例如下图：

同期群分析包含了3个重要元素：

1. 客户首次行为时间，这是我们划分同期群的依据
2. 时间维度，即上图中+N月或者N日留存率中的N日
3. 指标，注册转化率、付款转化率、留存率等等

3.2 意义

同期群分析给到更加细致的衡量指标，帮助我们实时监控真实的用户行为、衡量用户价值，并为营销方案的优化和改进提供支撑：

• 横向比较：观察同一同期群在不同生命周期下的行为变化，推测相似群体的行为随时间的变化

• 纵向比较：观察不同的同期群在同一个生命周期下的行为变化，验证业务行为是否取得预期效果

四、材料梳理

4.1 数据情况梳理

拿到数据的第一步，自然是了解数据的情况。针对本次同期群分析，我们可能需要用到的字段有：

• 客户昵称
• 付款时间：时间戳形式
• 订单状态：交易失败/交易成功
• 支付金额
• 购买数量

通过进一步计算，发现付款时间中缺失值所在行的订单状态均为“交易失败”，那么下文分析都需要将订单状态为“交易失败”的行全部剔除。

4.2 分析方法确定

针对此份数据，有3个分析方向可以选择：

1. 留存率或付款率
2. 人均付款金额
3. 人均购买次数

我们选择其中最经典，也是数分面试中最常考的留存率作为例子，需要用到的字段有：

• 客户昵称
• 付款时间
• 订单状态

相信各位对留存率都十分熟悉，不过多介绍。在本次的分析中，留存率的具体计算方式为：+N月留存率=（+N月付款用户数/首月付款用户数）*100%

注意：公式中的+N月存在歧义，会有两种计算方法：

1. 以自然月作为月份偏移的依据：即所有首次行为在9月的用户，只要10月有付款行为，都计算进+1月留存
2. 以每30天作为月份偏移的依据：即9月30日首次付款的用户，在10月30日-11月29日之间有付款行为，才计算进+1月留存

具体的差距会在Excel（用算法1）和MySQL（用算法2）两种工具实现的结果中分别展示。没有相关技术背景的看官老爷可直接对比最终的留存率结果。

五、Excel实现

Excel的实现方式是三个当中门槛最低的，只需要掌握数据透视表和一些基础函数，但过程相对繁杂。实现思路如下：

实现思路一共分为4大部分：数据清洗 -> 计算首单时间 -> 计算首单时间与付款时间差 -> 利用透视表计算同期群留存量和留存率。其中由于部分版本的office和wps的数据透视表不支持非重复计数，因此需要先计算各月中各用户出现的次数。

数据清洗部分只需要筛选+删除便可完成，相信如此简单的操作难不倒各位看官老爷们，那么我们便从第二部分开始详细讲解。

5.1 计算每个客户首单时间

首先通过数据透视表求每一个用户首次付款时间。数据透视表，说白了就是通过特定的条件进行分组，并对数据进行求和、求均值、求方差等聚合操作。在制作数据透视表时要注意以下几点：

1. 数据区域的第一行为标题栏（字段名称）
2. 标题栏不能出现空单元格，亦不要出现重复的标题名
3. 数据中避免有合并单元格
4. 不能出现非法日期

5.1.1 创建透视表

全选数据 -> 插入 -> 数据透视表 -> 确定

5.1.2 选择分组字段和值字段

将“客户昵称”拖进“行”，将付款时间拖进“值”，并将值字段设置中的汇总方式设置为最小值

这里最小付款时间显示为10位的时间戳，只要调整显示格式便可转为我们常见的xx年xx月xx日。

5.1.3 将首单时间拼接到每个用户所在行

此步骤需要使用到vlookup函数进行匹配。VLOOKUP函数是一个纵向查找的函数，包含4个参数，具体语法为=VLOOKUP（查找的依据，查找的区域，返回的值在查找区域中的列号，是否近似匹配）

注意：

1. 查找的位置如果要保持不变，要使用A:B或者$A$1:$B$15的形式锁定匹配区域
2. 参数[ 查找的位置 ]中，“!”号前为表的名称
3. 列号的计数是从1开始，且第一列必须是与查找依据对应的列
4. 近似匹配参数中，0为否（即必须与查找依据一模一样才匹配），1为是（即依据为“同期”时，可以匹配出“同期”、“同期群”或者“同期群分析”）

=VLOOKUP(A2,首付时间透视表!A:B,2,0)

利用VLOOKUP拼接之后，首单时间同样显示为10位的时间戳，设置单元格格式后即可显示为上图的形式。

5.2 计算时间差

5.2.1 对付款时间和首单时间进行降采样

如按算法2进行计算，可直接省略此步骤。

可能有看官老爷对重采样的概念并不是很清楚，简单说下：

• 将时间序列从一个频率转化为另外一个频率的过程即重采样
• 常见的时间频率由低到高依次为：年 -&