本文目录
- 前言
- 清洗数据:筛选订单状态为“交易成功”的行
- 获取首单时间
- 求月份偏移:求出月份差,并对首次付款时间降采样
- 计算留存量:通过首次付款时间和月份差进行分组,求唯一的用户id数
- 求留存率
前言
数据字段、同期群含义、数据概览等细节,请参照前面几篇文章:
Dima:手把手教你用 Python 实现同期群分析(1)zhuanlan.zhihu.com本文我们依旧选择最经典,也是数分面试中最常考的留存率作为例子。
本文所涉及的完整实战数据和代码等,详见百度云网盘链接:
链接:https://pan.baidu.com/s/1_CTwkdzFbXNH9iZPoYm2bw
提取码:maz8
Ps:本文重代码工具实现,分析的思路及结论暂不涉及!
一、数据清洗
先筛选出 order_status 为“交易成功”的行,并筛选出 nick_name、pay_time 两个字段。由于我们后续的查询都是基于筛选后的数据,因此这里新建一个表 sheet1 去存储查询结果。
二、计算首单时间
对 nick_name 进行groupby,再求最小值即可。
三、计算月份差,重采样首次支付时间
此步骤,会涉及到两个重要的函数:
- MySQL 对时间戳重采样用:YEAR()、MONTH()等函数;
- 用于计算日期差的 TIMESTAMPDIFF,具体语法为:TIMESTAMPDIFF(频率,起始时间,结束时间)。
注意:在计算月份差之前,需要以用户名称作为依据,拼接用户的首单时间。但由于数据量较大,拼接需要重复遍历整个表很多遍,耗时很长。而当前查询的结果并不是最终结果,我们只需要确保查询语句没有问题即可。因此我们引入分页查询(LIMIT语句)来限制查询结果的行数,从而提高查询效率。
四、计算留存量
将前面的三个步骤汇总,作为子查询,并以“首次支付时间 f_time”以及 “月份差 m_diff” 作为条件对数据进行分组,用DISTINCT筛选出唯一的 nick_name 即可求出留存量。
这里创建一个名为 sheet2 的表储存查询结果。
五、计算留存率
为了实现最终结果,需要多层子查询,多层嵌套!
第一步,查询首付月份的用户量;
第二步,计算留存率;
第三步,转置表格,将月份差作为列名;
最后一步,计算留存率!
根据上图查询结果,重点理解下面两行 SQL脚本 的妙处!
AVG(n.`留存量`) AS "本月新增",
CONCAT(SUM(n.`+1月`),"%") AS "+1月"
最后,总结一下,本文涉及到的重要知识点:
- 时间重采样函数:YEAR、MONTH
- 时间差函数:TIMESTAMPDIFF
- 流程控制函数:CASE WHEN