使用mysql对CDNow网站用户进行分析

转载请在文章起始处注明出处，谢谢。

数据来源CDNow网站的用户购买明细。一共有客户ID，购买日期，购买数量，购买金额四个字段。原数据是txt的格式，将它改为csv的格式导入进mysql，由于userid有重复，所以不设主键，只是用作分析练习。

image.png

加载数据：

image.png

paidtime表示购买日期，products表示购买数量，amounts表示购买金额
对表进行优化：新增一列month对paidtime只取年月和第一天，如：1997-01-12只取成1997-01-01，方便后续分析。生成新的表格cdnowtest，并修改cdnowtest中month的类型为date。
create table data.cdnowtest
as
select * from (
select *,date_format(paidtime,'%Y-%m-01') as pmonth
from data.cdnow) t

cdnowtest：

image.png

观测下整个数据，先对整个数据按单笔消费金额amount进行降序排序

image.png

消费最高的一笔是由userId为8830的用户产生的，消费了1286.01，随后单笔消费的金额迅速降低至最高金额的一半。

将数据进行升序的排序：

image.png

发现有些用户消费为0，且都是在1,2,3月份的。因为一个userId有可能产生多笔消费，所以我们观察下这些用户在其他月份有没有产生消费。

select *
from data.cdnowtest
where id in(select id
from(
select * from data.cdnowtest
order by amount) t
where amount = 0)

image.png
观察表格，我们可以发现，有些用户确实一次未消费，有些用户如：2703，在1月份未消费，但在二月份消费了一次，随后就再没消费过，可能是活动促销等原因造成的一次性消费。
我们根据这张表将一次未消费的用户过滤出来：
image.png
部分筛选结果如上表所示，所以我们可以向这些用户询问或者反馈，统计未消费的原因并针对性得找出改善的措施等。

对于整张表，我们大致可将客户分类为不活跃客户（消费次数为0），一般价值客户（消费1次），回头客（消费2-3次），忠实客户（消费3次以上），分别计算下它们的占比。

不活跃客户我们已经由上表得出，总数为68。
计算一般价值客户数为：
select count(id) as'一般价值客户'
from(
select id,count(amount) ca
from data.cdnowtest
where amount != 0
group by id
having ca=1) t

image.png
同样，回头客数量为：
image.png
忠实客户为：
image.png
总客户数为：23570
image.png
由上述查询出的数据可知：
不活跃客户数最少为68，占比约为0.3%
一般价值客户数最多为11843，占比约为50.2%
回头客数为6294，占比约为26.7%
忠实客户数为5365，占比约为22.8%

接下来我们根据用户进行分组，分析每个用户的产品购买数量和购买金额：
select max(sum_p) '最大消费数',
min(sum_p) '最小消费数',
avg(sum_p)'平均消费数',
max(sum_m)'最大消费额',
min(sum_m)'最小消费额',
avg(sum_m)'平均消费额'
from(
select id,sum(products) sum_p,
round(sum(amount),2) sum_m
from data.cdnowtest
group by id) t

image.png

从客户角度看，每位客户平均购买了7张CD，最多的用户购买了1033张，最大的消费金额达到了近一万四，应该是属于忠实粉丝，用户的平均消费金额（客单价）为106元

接下来按月维度进行分析：
select pmonth,sum(products) sp,round(sum(amount),0) sa
from data.cdnowtest
group by pmonth

image.png
根据聚合的结果可知，cd销量前三个月都非常高涨，后期则下降明显且呈现相对平稳状态。
我们再将数据按用户分组，观察第一次的消费时间，再按月份进行分组，统计消费次数。
select minm,count(id) '月消费总次数'
from(
select id,min(pmonth) as minm
from data.cdnowtest
group by id) t
group by minm
image.png
由查询表可知，所有用户的第一次消费都是集中在前三个月的，这也就不难解释为什么cd前三个月的销量和销售额都非常高涨。

接下来分析消费中的复购率和回购率
首先求复购率，复购率的定义是在某时间窗口内消费两次及以上的用户在总消费用户中占比。这里的时间窗口是月，如果一个用户在同一天下了两笔订单，这里也将他算作复购用户。
分别查询出各月消费总人数和各月复购的人数。
各月复购人数：

image.png

各月消费人数：

image.png
将两表连接，求出复购率：
select a.pmonth,a.mbackc,b.mtotalc,(a.mbackc/b.mtotalc) as rate
from(select pmonth,count(cid) mbackc
from(
select pmonth,id,count(id) cid

from data.cdnowtest
group by pmonth,id) t1
where cid>=2
group by pmonth) a
join (select pmonth,count(cid) mtotalc
from(
select pmonth,id,count(id) cid
from data.cdnowtest
group by pmonth,id) t2
group by pmonth) b
on a.pmonth=b.pmonth

image.png

由表可知：看出复购率在早期，因为大量新用户加入的关系，新客的复购率并不高，最低的一月份只有10%。而在后期，在忠实客户的影响下，复购率逐渐上升且趋于稳定，在20%左右。

接下来计算回购率。回购率是某一个时间窗口内消费的用户，在下一个时间窗口仍旧消费的占比。

首先先按用户id和pmonth进行分组，筛选出每个用户在每个月的消费记录，再将表进行自连接，产生笛卡尔积效应，筛选出前后相差一个月的记录：

image.png
再根据筛选出的结果按t1的月份进行分组，分别得出当月消费总人数和回购人数，最后求得回购率
select *,(ct2m/ct1m) rate
from(
select t1.pmonth,count(t1.pmonth) ct1m,count(t2.pmonth) ct2m
from(
select id,pmonth from data.cdnowtest
where amount <> 0
group by id,pmonth) t1
left join(
select id,pmonth from data.cdnowtest
where amount <> 0
group by id,pmonth) t2
on t1.id=t2.id and t1.pmonth=date_sub(t2.pmonth,interval 1 month)
group by t1.pmonth) t3
image.png
image.png
从表中可以看出，用户的回购率是高于复购率的，波动性也较强，早期受新客户的影响，用户的回购率为15%左右，后期稳定在30%左右。最后项目数据为0，是由于并没有接下来7月份的数据，所以无法得出。综合复购率和回购率两张表格分析，新用户不管是从回购率还是复购率看都不及老客户

接下来分析用户质量，首先根据用户分组，计算并按消费额排序出每个用户的消费总额：

image.png

根据消费的二八法则，我们需要知道消费的top20%的客户贡献了多少的额度，根据前面的查询结果，我们知道，此次我们调查的用户共有23570位，那么取前20%也就是4714位，所以我们有：
select t1.top20,t2.total,(t1.top20/t2.total) rate
from(
(select round(sum(suma)) top20
from(
select id,round(sum(amount),2) suma
from data.cdnowtest
group by id
order by suma desc
limit 4714) t) t1,
(select round(sum(suma)) total
from(
select id,sum(amount) suma
from data.cdnowtest
group by id) t) t2
)

image.png
同理，我们可以再筛选出前40%的用户贡献的额度及贡献率，结果如下：
image.png
根据统计的结果，我们可以得出：消费排名前20%的用户贡献了近70%的消费总额，消费排名前40%的用户贡献了约85%的消费总额，确实呈现了28的倾向，所以维护好这批排名靠前的用户显得尤为重要！

接下来简单计算下用户生命周期，这里定义第一次消费至最后一次消费为整个用户生命。
首先根据用户进行分组，筛选出消费次数大于1的用户和消费的最早时间和最晚时间，然后计算出相差的天数。

image.png

有消费间隔为0的，是指当天消费了两次或以上，因为我们已经将消费大于1次的过滤了出来。
接着，我们观察下deltaday的一些相关数据，结果如下：

image.png
maxd表示最长用户的生命周期是544天，
mind表示最短的是0天，
avgd平均生命周期约为273天。

接下来，我们根据用户的生命周期大致划分下等级：
level=0或1 是指生命周期为0-100天的用户标签
level=2 是100-200，以此类推
level=3 200-300
level=4 300-400
level=5 400-500
level=6 500+

先计算出下这些用户的平均消费频次：
由于用了多次嵌套，代码显得稍有些复杂
select level,avg(cid) avgcid
from(
select t3.level,t3.id,count(id) cid
from(
select cd.*,t2.level
from data.cdnowtest cd
join(
select id,ceil(deltaday/100) as level
from(
select id,datediff(maxm,minm) deltaday
from(
select id,min(paidtime) minm,max(paidtime) maxm
from data.cdnowtest
group by id
having count(id)>1) t) t1) t2
on cd.id=t2.id)t3
group by t3.level,t3.id) t4
group by level

运行结果如下：

image.png

可以看出：leve在0-2之间的也就是生命周期在0-200天的，平均消费频次在2-3之间，随着生命周期的增长，消费频次逐渐变高。level=6的忠实客户的值最高，平均消费频次在12次左右。

随后，我们根据level标签观察下这批多次消费用户的分布情况（二次消费的总人数为11662），统计结果如下：

image.png

cid表示各个level消费的人数，rate是指占多次消费人数的比例

我们将两张表格结合起来分析下，结果如下：

image.png

观察表格，我们可以发现：占比图的数值呈一个双峰的趋势，消费频次数值显示呈上升趋势，且越往后上升越快。消费频次高的用户，集中在level 5,6两段，所占比率为30%，这已经是属于忠实的客户。level 0,1的用户虽然平均消费次数也有两次及以上，但却很难持续，应该在消费后这段时间尽量引导，其占比也达到近25%，这是不容忽视的。中间段则相对来说平稳，无论是从占比还是从消费频次上来说。

总结：
1、统计出的未消费的用户已在内容中呈现，可针对性的制定改善的措施。
2、一次性消费的用户群体居多占总消费总用户的一半。
3、销量前三个月高涨，后期下降明显且呈现相对平稳状态。
4、复购率受一次性客户影响，平均稳定在20%左右；回购率则稳定在30%左右。
5、整个消费状况呈现二八倾向，维护好金字塔尖部的客户至关重要！
6、用户生命周期平均在273天，也收到了极端值的影响。各阶段用户占比呈双峰趋势，随之生命周期的增长，平均消费频次也在提高