有一个多月没有用Python了,有些生疏o(╥﹏╥)o。通过秦路老师的一道题目,分别使用sql和python求解,顺便复习下python点,重点关注【复购率】、【回购率】的解法
☞秦路老师视频讲解(使用SQL来求解)
已知信息:所需数据:order_info.csv、user_info.csv
【表userinfo】字段为:userid,sex,birth
【表order_info.csv】字段为:orderid,userid,ispaid,price,paidtime
数据连接,提取码:b4ca
求出以下:统计用户不同月份的回购率和复购率
统计不同月份的下单人数
统计不同性别的消费频次是否有差异
统计多次消费的用户,第一次和最后一次消费时间的间隔(按天计算间隔)
统计不同年龄段的用户人均消费频次(年龄段按[0,10),[10,20),[20,30),[30,40),[40,50),[50.60),[60,70),[70,80),[80,90)来划分,统计时间截止为2017年,保留两位小数)
统计消费的二八法则,消费金额的top20%用户,贡献了多少额度
从拿到数据到形成报告,无外乎以下步骤理解数据及背景
清洗数据(重复值、空值、异常值、数据一致化)
明确问题
分析问题(借助工具对数据进行分析)
解决问题
直接进入分析问题步骤:
【使用SQL进行求解】
1.统计用户不同月份的回购率和复购率【复购率】当月消费多次
【回购率】当月消费,次月仍消费【复购率】先求出每月每个用户的消费次数,再筛选出消费多次的用户。注意两位数百分数的求法concat(round(数值,2),'%')
SELECT t.`月份`,count(DISTINCT t.userid) 当月消费人数,
count(DISTINCT case when t.`消费次数`>1 then t.userid else null end) as 当月复购人数,
concat(round(count(DISTINCT case when t.`消费次数`>1 then t.userid else null end)/count(DISTINCT t.userid)*100,2),'%')as 复购率 FROM
(SELECT DATE_FORMAT(paidtime,'%Y-%m') as 月份,userid,
count(orderid) 消费次数
from 03order_info
where ispaid='已支付'
GROUP BY DATE_FORMAT(paidtime,'%Y-%m'),userid) t
GROUP BY t.`月份`
;【回购率】将order_info连接,求出diff,进而求解。类似留存率的计算
SELECT t.date1,count(DISTINCT t.userid)as 当月购买人数,
count(DISTINCT case when t.diff=1 then t.userid else null end)as 次月购买人数,
count(DISTINCT case when t.diff=1 then t.userid else null end)/count(DISTINCT t.userid) as 回购率
FROM
(SELECT t1.userid,
DATE_FORMAT(t1.paidtime,'%Y-%m') as date1,
DATE_FORMAT(t2.paidtime,'%Y-%m') as date2,
TIMESTAMPDIFF(month,DATE_FORMAT(t2.paidtime,'%Y-%m-01'),DATE_FORMAT(t1.paidtime,'%Y-%m-01')) as diff
from 03order_info t1
LEFT JOIN 03order_info t2
on t1.userid=t2.userid
where t1.ispaid='已支付' and t2.ispaid='已支付')t
GROUP BY t.date1
;
2.统计不同月份的下单人数
SELECT DATE_FORMAT(paidtime,'%Y-%m') as 月份,
COUNT(DISTINCT userid) as 下单人数 ,
count(userid) as 下单数 from 03order_info
where ispaid='已支付'
GROUP BY DATE_FORMAT(paidtime,'%Y-%m');
3.不同性别的消费频次
SELECT u.sex,
count(o.orderid)/count(DISTINCT o.userid) as 消费频次
from 03order_info o
LEFT JOIN 03user_info u
on o.userid=u.userid
where o.ispaid='已支付'
GROUP BY u.sex;
4.统计多次消费的用户,第一次和最后一次消费时间的间隔(按天计算间隔)
SELECT userid,
DATEDIFF(max(date(paidtime)),min(date(paidtime))) as 时间间隔
from 03order_info
where ispaid='已支付'
group by userid
having count(orderid)>1;
5.统计不同年龄段的用户人均消费频次
在剔除birth为空的数据后,按照birth升序排列,user_info表格顺序如下。需要剔除年龄极值,规定year(birth)>1900
#年龄段划分用【case when】函数
SELECT t.`年龄段`,COUNT(t.orderid) as 消费次数,
count(DISTINCT t.userid) as 消费人数,
round(COUNT(t.orderid)/count(DISTINCT t.userid),2) as 人均消费频次 from
(SELECT o.userid,o.orderid,
case
WHEN 2017-year(u.birth)<10 then '[0,10)'
WHEN 2017-year(u.birth)<20 then '[10,20)'
WHEN 2017-year(u.birth)<30 then '[20,30)'
WHEN 2017-year(u.birth)<40 then '[30,40)'
WHEN 2017-year(u.birth)<50 then '[40,50)'
WHEN 2017-year(u.birth)<60 then '[50,60)'
WHEN 2017-year(u.birth)<70 then '[60,70)'
WHEN 2017-year(u.birth)<80 then '[70,80)'
WHEN 2017-year(u.birth)<90 then '[80,90)'
else '90+'
end as 年龄段
from 03order_info o
LEFT JOIN 03user_info u
on o.userid=u.userid
where o.ispaid='已支付' and year(u.birth)>1900) t
GROUP BY t.`年龄段`
6.统计消费的二八法则,消费金额的top20%用户,贡献了多少额度求前百分比的数值时,利用窗口函数排序,然后让排序小于百分比与总数的乘积即可
SELECT sum(t.sum) FROM
(SELECT userid,round(sum(price),2) as 'sum',
row_number() over (order by sum(price) desc) as 'ranking'
from 03order_info
where ispaid='已支付'
GROUP BY userid) t
where t.ranking
where ispaid='已支付')
;
【使用python求解】
1.统计用户不同月份的回购率和复购率
复购率:
df=order.groupby(['month','userid']).count().reset_index()
s1=df.groupby('month')['userid'].count() #当月消费人数
s2=df[df['orderid']>1].groupby('month')['userid'].count() #当月多次消费人数
df_new= pd.concat([s1,s2],axis=1)
df_new.columns=['当月消费人数','当月多次消费人数']
df_new['复购率']=(df_new['当月多次消费人数']/df_new['当月消费人数']).apply(lambda x: format(x,'.2%')) #两位小数百分数
print(df_new)
回购率:
order['month']=order['paidtime'].dt.month
order1=order.groupby(['userid','month']).count().reset_index()[['userid','month']] #统计出各userid再各月消费情况
order2=pd.merge(order1,order1,on='userid',how='left') #两表连接
order2['diff']=order2['month_y']-order2['month_x']
order3=order2.groupby(['month_x','userid']).count().reset_index()['month_x'].value_counts() #当月消费人数
order4=order2[order2['diff']==1].groupby(['month_x','userid']).count().reset_index()['month_x'].value_counts() #次月消费人数
df=pd.concat([order3,order4],axis=1)
df.columns=['当月购买人数','次月回购人数']
df['回购率']=df['次月回购人数']/df['当月购买人数']
print(df)
2.统计不同月份的下单人数【知识点】①时间函数用法(见图,参考)②如何去重计数
order['month']=order['paidtime'].dt.strftime('%Y-%m')
order=order[order['ispaid']=='已支付']
df=order.groupby(['month','userid']).count().reset_index().groupby('month').agg({'userid':'count','orderid':'sum'})
df=df.rename(columns={'userid':'下单人数','orderid':'下单次数'})
print(df)
3.统计不同性别的消费频次表连接pd.merge(table1,table2,left_on,right_on,how)
df=pd.merge(order,user,left_on='userid',right_on='userId',how='left')
df=df.groupby(['sex','userId']).count().reset_index().groupby('sex').agg({'userId':'count','orderid':'sum'})
df=df.rename(columns={'userId':'消费人数','orderid':'消费次数'})
df['消费频次']=round(df['消费次数']/df['消费人数'],2)
print(df)
4.统计多次消费的用户,第一次和最后一次消费时间的间隔(按天计算间隔)
df=order.pivot_table(index='userid',values='paidtime',aggfunc=['min','max'])
df['diff']=df['max']-df['min']
print(df.head())
5.统计不同年龄段的用户人均消费频次(年龄段按[0,10),[10,20),[20,30),[30,40),[40,50),[50.60),[60,70),[70,80),[80,90)来划分,统计时间截止为2017年,保留两位小数)df['label']=pd.cut(列,bins,right,labels),数据类型为category,是无法使用groupby的,必须先使用.astype(str)将category转换类型
#提取年份,计算年龄
#使用pd.cut(data,bins,right,label)分组
user=user[user['birth']>datetime(1900,1,1)]
user['birth']=user['birth'].dt.year
user['age']=datetime(2017,1,1).year-user['birth']
user['label']=pd.cut(user['age'],bins=[0,10,20,30,40,50,60,70,80,90,100],right=False).astype(str)#要把category转换成str类型,才能groupby
#表连接
df=pd.merge(order,user,left_on='userid',right_on='userId',how='left').dropna() #删除缺失值所在行
#数据分组求解
df=df[['userid','orderid','label']]
df=df.groupby(['label','userid']).count().reset_index().groupby('label').agg({'userid':'count','orderid':'sum'})
df=df.rename(columns={'userid':'消费人数','orderid':'消费次数'})
df['消费频次']=df['消费次数']/df['消费人数']
print(df)
6.统计消费的二八法则,消费金额的top20%用户,贡献了多少额度
#计算每个用户的消费金额
df=order.groupby('userid')['price'].sum().reset_index()
#计算贡献排在前20%的分位数
data=df['price'].quantile(0.8,interpolation='nearest') #分位数为1314
#筛选出前20%贡献数据,并计算求和
df=df[df['price']>=data]
print(df['price'].sum())
结果:272222167.4584
(~ ̄▽ ̄)~