现有一份表格，记录了用户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，购买日期，购买数量，购买金额

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: