现有一份表格，记录了用户ID及其下单时间
我们使用navicat导入数据
选择表格文件
选择文件路径
导入成功后，我们就可以正式开始了
首先，我们找出每个用户的最小日期，即首次下单时间
select uid,min(date(time)) as mtime from active group by uid;
out:
接着，我们去一下重
select DISTINCT uid,date(time) as t from active;
out:
关联起来，由此我们可以得到用户ID，下单时间，首次下单时间三个字段
select b.uid,b.t,a.mtime from
(select uid,min(date(time)) as mtime from active group by uid) a,
(select DISTINCT uid,date(time) as t from active) b
where a.uid=b.uid;
out:
然后我们可以求出1号的人在各个日期有多少人，这样就很容易求出1号的次日留存率，3日留存率，4日留存率....
方法是按最小日期和下单时间分组
select mtime,t,count(uid) as c from
(select b.uid,b.t,a.mtime from (select uid,min(date(time)) as mtime from active group by uid) a,(select DISTINCT uid,date(time) as t from active) b where a.uid=b.uid) d
group by mtime,t;
out:
其实到这步我们已经一目了然
如果时间差为1，则为次日留存人数，为2，则为三日留存人数，为6，则为七日留存人数；次日留存人数/人数，则为次日留存率
select mtime,sum(if(datediff(t,mtime)=0,c,0)) as '人数',
sum(if(datediff(t,mtime)=1,c,0)) as '次日留存人数',
sum(if(datediff(t,mtime)=2,c,0)) as '三日留存人数',
sum(if(datediff(t,mtime)=6,c,0)) as '七日留存人数',
sum(if(datediff(t,mtime)=1,c,0)) /sum(if(datediff(t,mtime)=0,c,0)) as '次日留存率',
sum(if(datediff(t,mtime)=2,c,0)) /sum(if(datediff(t,mtime)=0,c,0)) as '三日留存率',
sum(if(datediff(t,mtime)=6,c,0)) /sum(if(datediff(t,mtime)=0,c,0)) as '七日留存率'
from (select mtime,t,count(uid) as c from (select b.uid,b.t,a.mtime from (select uid,min(date(time)) as mtime from active group by uid) a,(select DISTINCT uid,date(time) as t from active) b where a.uid=b.uid) d group by mtime,t order by mtime,t) f
group by mtime;
out:
二、使用python
现有一份表，记录了用户ID，购买日期，购买数量，购买金额
数据源：https://pan.baidu.com/s/1pHa3KsS2IWg9ItSaehsgbw
提取码：qza2
共69659条数据，无缺失值
import pandas as pd
columns=['uid','time','order_products','order_amount'] #表头为用户ID，购买日期，购买数量，购买金额
df=pd.read_csv('CDNOW_master.txt',names=columns,sep='\s+')
我们只要用户ID和下单时间这两列信息，并且去重
#我们只要用户ID和下单时间这两列信息，并且去重
data=df[['uid','time']].drop_duplicates()
print(data.head())
out:
改时间列数据类型为时间
data['time']=pd.to_datetime(data['time'],format='%Y%m%d') #修改时间列数据类型为时间
print(data.head())
out:
找出每个用户的最小购买时间，即首次购买时间
#找出每个用户的最小购买时间，即首次购买时间
data2=data['time'].groupby(data['uid']).min()
print(data2.head())
out:
把他们关联起来，得到用户ID，下单时间，首次下单时间
#把他们关联起来，得到用户ID，下单时间，首次下单时间
data3=pd.merge(data,data2,on='uid')
print(data3.head())
data3.columns=['uid','time','mtime'] #重命名列名
out:
计算留存人数，按最小时间、下单时间分组
#计算留存人数，按最小时间、下单时间分组
data4=data3.pivot_table('uid',index=['mtime','time'],aggfunc='count')
print(data4)
out:
现在我们可以清晰的看到1997-01-01的次日留存人数为3，留存率为3/209，三日留存人数为3，留存率为3/209，四日留存人数为2.......
#重置索引
data5=data4.reset_index()
print(data5)
out:
如果我们想看某一日的留存率，我们可以把它筛选出来
data6=data5[data5['mtime']=='1997-01-01']
print(data6)
out:
