简单易懂，棒棒哒。
2021-02-13 阅读量: 1941
学生：mysql用户留存率的查询 我想看当日注册的下单新用户 在次日的留存率 三日的留存率 七日的留存率
学生：急死了 都找不到人解决 明天又要给老板交作业 好烦
老师：数据有哪些字段
学生：统计日期 用户ID 提交资料日期 审核通过日期 销量
学生提交的数据我经过脱敏处理上传到云盘上了，有需要的同学可以下载
链接：https://pan.baidu.com/s/1dcKvc-bxa_qH5yjUt61UtQ
提取码：7cmh
复制这段内容后打开百度网盘手机App，操作更方便哦
老师：告诉我计算留存率的方法，比如1日留存率是不是在注册后第一日有消费则是留存
学生：次日留存就是 注册当日有消费 并且次日有消费
老师：好的，必须用mysql吗？我mysql不怎么样。
老师：我看这个数据量比较小，能不能放到python里面，加工后返给数据库。
学生:不用mysql也可以
老师：好，我想下怎么计算
学生：我现在很着急想要这个 好烦 还在加班
老师：好，必须完成任务
学生：你可以把审核通过日期为-1的数据清洗一下 -1表示审核不通过
老师：我这个电脑太不给力，一打开程序和视频就快死机了，你别着急呀，肯定能做出来
学生：不着急 嗯
老师：销量为0是没有消费的意思吗
学生：是的
老师：团长id是唯一的一个人的识别码吗
学生:是的
老师：你的数据是2021年1月1日到2021年1月31日全部用户的观测数据，我们切取其中一个用户的数据看一下，比如团长ID取值位1的用户。这个用户的审核通过日期从2020年10月27日，我们是无法观察到他第一天的消费记录的，因此后面分析的时候要注意。
老师：那应该是只研究1月份注册的用户对吧，以前注册的用户是不需要研究的
学生：对的
老师：好的
老师：我们只需要计算2021年1月通过审核的用户的留存率
对吧？
学生：是的。
老师：你的数据还是有问题，那些只观察了0天的怎么算
学生：我天 那就最后一周的不算
老师：好的
学生：你用python跑的？
老师：是的，mysql几乎没用过
老师：必须要有一个完整的7日观测周期，你如果需要1月份的完整数据，那就要求审核通过日期在1月1日-1月31日，统计日期为1月1日-2月7日。你筛选下数据发给我应该没问题，必须要有完整的7日观测日期
学生：这是1月1到31的数据，观测就到1月份的最后一个能观测的周期就好了，也就是到24号新注册的用户
老师：那就是观测1月1日到1月24日审核通过的用户的情况
学生：对 这个周期内新注册用户的留存率
老师：好，计算是否1日留存 次日留存 还有7日留存吗
学生：就次日留存 和7日留存就好了
老师：那个7日算当天吗
学生：
你是算还是不算的
可以以你定义的来
老师：
我算的是0-6天，
但是这个数值怎么那么大
次日留存率为 0.3364916436373207
7日留存率为 0.14238715620476378
学生：
那就这样~
老师：
我算的是人数
你这个留存率怎么这么高
学生：嗯 有的
老师：
我给你代码，你直接跑呀，我觉得速度还可以
你电脑肯定更没有问题
import pandas as pd
import numpy as np
#导入数据生成数据框data1
#可能由于一些原因导致审核通过日期等变量导入到python中之后变成了字符串变量
data1=pd.read_csv("D:\\A2021_02_13.csv")
#先把审核通过的数据挑出来生成新数据框data2
#这个数据是从mysql数据库中导出的，在mysql数据库中如果审核没有通过，则审核通过日期用-1表示
#这种为-1的数据不是我们需要的，
#所以我们把非-1数据挑选出来。
data2=data1[data1["审核通过日期"]!="-1"]
#需要说明的是学生在mysql数据库中提取这个数据的时候帮我进行了筛选，所以我们这个数据中不存在-1的情况
#我之所以还是把这个命令放到这里，是希望大家在做数据清洗的时候需要注意到这样的业务问题。
#将统计日期和提交资料日期字符串变成日期时间型
data2["统计日期"]=data2["统计日期"].map(lambda x :pd.Timestamp(x))
data2["提交资料日期"]=data2["提交资料日期"].map(lambda x :pd.Timestamp(x))
data2["审核通过日期"]=data2["审核通过日期"].map(lambda x :pd.Timestamp(x))
#根据审核通过日期对数据进行筛选
data3=data2[(data2["审核通过日期"]>=pd.Timestamp("2021-01-01")) & (data2["审核通过日期"]<=pd.Timestamp("2021-01-24"))]
#计算每个人在每个统计日期的总销量
#团长ID为每一个人的唯一标识ID
#每一个人是有很多行记录的
#在一个审核通过日期，可能有很多人通过审核
pivot_table1=data3.pivot_table(values=["销量"],
index=["审核通过日期","团长ID","统计日期"],
aggfunc={ "销量":np.sum})
#更改变量名字，为了以后的调用方便
pivot_table1.columns=["销量"]
#将索引变成普通字段
pivot_table1.reset_index(inplace=True)
#生成是否消费字段
pivot_table1["是否消费"]=pivot_table1["销量"].map(lambda x:1 if x>0 else 0)
def f1(x):
aa=x["统计日期"]-x["审核通过日期"]
bb=aa.days
return bb
pivot_table1["距离审核通过第多少天"]=pivot_table1.apply(f1,axis=1)
#生成去重后的团长ID数据框,在这个数据框里面团长id是没有重复值的，字段包含团长id，审核通过日期，还有一列取值全为1的字段temp
#这个temp是为了以后方便汇总计算
QQQ=pivot_table1.drop_duplicates(subset=["团长ID"])
QQQ["temp"]=1
QQQ1=QQQ.loc[:,["团长ID","审核通过日期","temp"]]
QQQ1.set_index(["团长ID"],inplace=True)
#计算每一个团长第0天是否有消费
#我们的数据是存在一些问题的，比如有的人的第0填的消费记录是缺失值，也就是没有这一行记录
#后面几天可能又有了。
temp0=pivot_table1[pivot_table1["距离审核通过第多少天"]==0]
temp0.rename(columns={"是否消费":"第0天是否消费"},inplace=True)
temp0.set_index(["团长ID"],inplace=True)
temp0.drop(columns=["距离审核通过第多少天","统计日期","销量","审核通过日期"],inplace=True,axis=1)
#计算每一个团长第一天是否有消费
temp1=pivot_table1[pivot_table1["距离审核通过第多少天"]==1]
temp1.rename(columns={"是否消费":"第1天是否消费"},inplace=True)
temp1.set_index(["团长ID"],inplace=True)
temp1.drop(columns=["距离审核通过第多少天","统计日期","销量","审核通过日期"],inplace=True,axis=1)
#计算每一个团长第2天是否有消费
temp2=pivot_table1[pivot_table1["距离审核通过第多少天"]==2]
temp2.rename(columns={"是否消费":"第2天是否消费"},inplace=True)
temp2.set_index(["团长ID"],inplace=True)
temp2.drop(columns=["距离审核通过第多少天","统计日期","销量","审核通过日期"],inplace=True,axis=1)
temp3=pivot_table1[pivot_table1["距离审核通过第多少天"]==3]
temp3.rename(columns={"是否消费":"第3天是否消费"},inplace=True)
temp3.set_index(["团长ID"],inplace=True)
temp3.drop(columns=["距离审核通过第多少天","统计日期","销量","审核通过日期"],inplace=True,axis=1)
temp4=pivot_table1[pivot_table1["距离审核通过第多少天"]==4]
temp4.rename(columns={"是否消费":"第4天是否消费"},inplace=True)
temp4.set_index(["团长ID"],inplace=True)
temp4.drop(columns=["距离审核通过第多少天","统计日期","销量","审核通过日期"],inplace=True,axis=1)
temp5=pivot_table1[pivot_table1["距离审核通过第多少天"]==5]
temp5.rename(columns={"是否消费":"第5天是否消费"},inplace=True)
temp5.set_index(["团长ID"],inplace=True)
temp5.drop(columns=["距离审核通过第多少天","统计日期","销量","审核通过日期"],inplace=True,axis=1)
temp6=pivot_table1[pivot_table1["距离审核通过第多少天"]==6]
temp6.rename(columns={"是否消费":"第6天是否消费"},inplace=True)
temp6.set_index(["团长ID"],inplace=True)
temp6.drop(columns=["距离审核通过第多少天","统计日期","销量","审核通过日期"],inplace=True,axis=1)
temp7=pivot_table1[pivot_table1["距离审核通过第多少天"]==7]
temp7.rename(columns={"是否消费":"第7天是否消费"},inplace=True)
temp7.set_index(["团长ID"],inplace=True)
temp7.drop(columns=["距离审核通过第多少天","统计日期","销量","审核通过日期"],inplace=True,axis=1)
#将多个数据横向拼接在一起，这里没有写成循环的命令，是为了让大家更好理解这个拼接过程
#这个拼接过程相当于把一个长数据变成一个宽数据
ddd=pd.concat([QQQ1,temp0,temp1,temp2,temp3,temp4,temp5,temp6,temp7],axis=1)
ddd.sort_values(by=["审核通过日期"],ascending=[True],inplace=True)
#计算是否一日留存
ddd["是否一日留存"]=ddd.apply(lambda x:1 if x["第0天是否消费"]==1 else 0,axis=1)
#计算是否次日留存
ddd["是否次日留存"]=ddd.apply(lambda x:1 if x["第0天是否消费"]==1 and x["第1天是否消费"]==1 else 0,axis=1)
#计算是否次日留存
ddd["是否7日留存"]=ddd.apply(lambda x:1 if x["第0天是否消费"]==1 and x["第1天是否消费"]==1 and x["第2天是否消费"]==1 and x["第3天是否消费"]==1 and x["第4天是否消费"]==1 and x["第5天是否消费"]==1 and x["第6天是否消费"]==1 else 0,axis=1)
result=ddd.loc[:,["temp","是否次日留存","是否7日留存"]].sum(axis=0)
print("次日留存率为",result["是否次日留存"]/result["temp"])
print("7日留存率为",result["是否7日留存"]/result["temp"])学生:
我可以试一下
老师：
学生：但是这个7日留存率 有这么低吗
老师：
你可以把ddd那个数据导出来看有没有问题
ddd里面有每个人每日的消费情况
7日留存那就是7天都消费
学生：
这个7日留存率 是所有的用户的 还是说啥
噢噢 了解了
老师：你看下ddd那个数据框就知道了
学生：我懂了
老师：
这个是1月1日到1月24日所有新用户的消费情况
没啥问题我去睡觉啦
那么也就是这个次日留存 就是首日下单 次日也下单是吧
学生：
如果有一天没下单 就不算是不是
老师：是的
学生：
好的 老师辛苦了 我研究一下
老师新年快乐呀 大年三十的还请您搞这么晚 哎
在理解意思的情况下，下面这个代码更简洁一些
import pandas as pd
import numpy as np
#导入数据生成数据框data1
#可能由于一些原因导致审核通过日期等变量导入到python中之后变成了字符串变量
data1=pd.read_csv("D:\\A2021_02_13.csv")
#先把审核通过的数据挑出来生成新数据框data2
#这个数据是从mysql数据库中导出的，在mysql数据库中如果审核没有通过，则审核通过日期用-1表示
#这种为-1的数据不是我们需要的，
#所以我们把非-1数据挑选出来。
data2=data1[data1["审核通过日期"]!="-1"]
#需要说明的是学生在mysql数据库中提取这个数据的时候帮我进行了筛选，所以我们这个数据中不存在-1的情况
#我之所以还是把这个命令放到这里，是希望大家在做数据清洗的时候需要注意到这样的业务问题。
#将统计日期和提交资料日期字符串变成日期时间型
data2["统计日期"]=data2["统计日期"].map(lambda x :pd.Timestamp(x))
data2["提交资料日期"]=data2["提交资料日期"].map(lambda x :pd.Timestamp(x))
data2["审核通过日期"]=data2["审核通过日期"].map(lambda x :pd.Timestamp(x))
#计算重要变量
def f1(x):
aa=x["统计日期"]-x["审核通过日期"]
bb=aa.days
return bb
data2["距离审核通过第多少天"]=data2.apply(f1,axis=1)
#根据审核通过日期对数据进行筛选
data3=data2[(data2["审核通过日期"]>=pd.Timestamp("2021-01-01")) & (data2["审核通过日期"]<=pd.Timestamp("2021-01-24"))]
#计算每个人在每个统计日期的总销量
#团长ID为每一个人的唯一标识ID
#每一个人是有很多行记录的
#在一个审核通过日期，可能有很多人通过审核
pivot_table1=data3.pivot_table(values=["销量"],
index=["审核通过日期","团长ID","距离审核通过第多少天"],
aggfunc={ "销量":np.sum})
#更改变量名字，为了以后的调用方便
pivot_table1.columns=["销量"]
#将索引变成普通字段
pivot_table1.reset_index(inplace=True)
#生成是否消费字段
pivot_table1["是否消费"]=pivot_table1["销量"].map(lambda x:1 if x>0 else 0)
#只保留"距离审核通过第多少天"小于等于7的情况
pivot_table1.drop(index=pivot_table1.index[pivot_table1["距离审核通过第多少天"]>7],axis=0,inplace=True)
pivot_table1.set_index(["审核通过日期","团长ID","距离审核通过第多少天"],drop=True,inplace=True)
#将序列由长变宽
ddd=pivot_table1["是否消费"].unstack(level=-1)
#计算是否一日留存
ddd["是否一日留存"]=ddd.apply(lambda x:1 if x[0]==1 else 0,axis=1)
#计算是否次日留存
ddd["是否次日留存"]=ddd.apply(lambda x:1 if x[0]==1 and x[1]==1 else 0,axis=1)
#计算是否次日留存
ddd["是否7日留存"]=ddd.iloc[:,:7].sum(axis=1)==7
ddd["temp"]=1
result=ddd.loc[:,["temp","是否次日留存","是否7日留存"]].sum(axis=0)
print("次日留存率为",result["是否次日留存"]/result["temp"])
print("7日留存率为",result["是否7日留存"]/result["temp"])
