本文目录
- 前言
- 数据清洗：删除订单状态为“交易失败”的行
- 计算首单时间
- 计算首单时间与付款时间差：求出月份差，并对首单付款时间降采样
- 利用透视表计算同期群留存量和留存率：：通过首单时间和月份差进行分组，求唯一的用户id数
前言
为什么最后才写Excel呢，实际中，计算同期群，可能数据量会非常大，大多数情况下，Excel很难应付下来。
数据字段、同期群含义、数据概览等细节，请参照前面几篇文章：
Dima：手把手教你用 Python 实现同期群分析（1）zhuanlan.zhihu.com
Dima：手把手教你用 Python 实现同期群分析（2）zhuanlan.zhihu.com
Dima：手把手教你用 SQL 实现同期群分析zhuanlan.zhihu.com
本文我们依旧选择最经典，也是数分面试中最常考的留存率作为例子。
本文，采用第一种方法实现！
本文所涉及的完整实战数据和建模过程等，详见百度云网盘链接：
链接：https://pan.baidu.com/s/1_CTwkdzFbXNH9iZPoYm2bw
提取码：maz8
本文涉及到的数据源
清洗后的数据建模全流程
本文使用 Excel 2016 版实现。低版本的 office 和 wps的数据透视表，不支持非重复计数！
Ps：本文重工具实现，分析的思路及结论暂不涉及！
一、数据清洗
只需要 筛选 + 删除。筛选出订单状态为“交易失败”的行，再将其删除，数据清洗即可完成。操作极其简单，不赘述！
二、计算首单时间
1、创建透视表
将清洗后的数据，建立透视表。具体步骤为：
【Ctrl+A 全选数据】-【插入】-【数据透视表】-【确定】。
2、选择分组字段和值字段
将“客户昵称”拖进【行】，将“付款时间”拖进【值】，并将值字段设置中的汇总方式设置为【最小值】。
再修改【最小值：付款时间】的格式：
本小节最终结果
3、将首单时间拼接到每个用户所在行
使用 VLOOPUP 函数；具体语法为：=VLOOKUP（查找的依据，查找的区域，返回的值在查找区域中的列号，是否近似匹配）。
Ps：公式所有括号及逗号，均为英文状态下！此处仅仅是为了排版方便，使用的中文书写！
注意要绝对引用！
记得再次修改成日期格式！
三、计算时间差
1、对付款时间和首单时间进行重采样（处理成年月格式）
2、计算首单时间与付款时间差（月份差）
此步骤中需要用到DATEDIF函数，此公式常用于计算两个日期之间的天数、月份、年数差。
语法为：=DATEDIF（起始时间，结束时间，时间频率），常用的时间频率参数有['Y','M','D']，分别对应年月日
为了截屏方便，隐藏部分不重要的字段
3、重置月份差标签
修改透视表的标签并不方便，因此先重置月份差标签，需要用到一个IF函数便可。
具体语法：=IF（条件，为真的操作，为假的操作）。
四、计算同期留存量和留存率
1、创建留存量数据透视表
具体步骤为：【Ctrl+A 全选数据】-【插入】-【数据透视表】-【将此数据添加到数据模型】-【确定】。
以“首单时间重采样”作为【行】，以“月份差标签”作为【列】，以直接以“客户昵称”作为【值】，并在值字段设置当中，将计算类型设置为【非重复计数】。
得到以下结果，再继续美化处理一下：
透视后得到的原始数据
2、计算留存率
最后，将数据复制到空白处，手动处理一下顺序，即可得到我们想要的留存量及留存率！
最后的最后，总结一下，本文涉及到的Excel重要知识点：
- 数据透视表
- VLOOKUP函数
- 时间重采样函数：YEAR、MONTH
- 时间差函数：DATEDIF
- 条件函数：IF
转播
