数据分析项目练习——使用mysql进行淘宝用户行为分析

1.背景介绍

        本文主要是练习使用 mysql 进行数据分析，结合 excel 、tableau进行可视化分析，数据来源为阿里云天池的淘宝用户数据集，本数据集(UserBehavior.csv)包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。

        关于数据集中每一列的详细描述如下图：

        注意到，用户行为类型共有四种，它们分别是：

Pv --- 浏览

Buy --- 购买

Cart --- 加购

Fav --- 收藏

        注：本文仅使用部分数据进行分析（49999条）。

2.数据清洗

        首先备份数据

2.1重复值处理：

        重复值处理：此处重复值的定义为：所有字段数据一致。

         由结果知无重复值。

2.2缺失值处理：

        对各列进行计数，查看是否存在缺失：

 

        每列计数结果均为49999，无缺失值

2.3日期数据转换：

        新建date,time字段，分别用unixtime()将时间戳转stamp换为时间和日期便于后续分析。

 

2.4 异常值处理:

        查询是否存在超出时间范围的数据，本数据集应包含2017年11月25日-12月3日之间共9天的数据

结果：共有20条记录不在分析日期内，进行删除操作

删除该20条记录后，数据集处理完毕，最终数据集为49979行数据。

3.数据分析

3.1用户行为分析（漏斗分析)

        用户进行购买，共有以下4条路径：

        浏览-购买

        浏览-加购-购买

        浏览-收藏-购买

        浏览-加购并收藏-购买

        首先创建视图，按用户、商品两个字段，计算出各行为的数量：

#创建便于统计各行为数量的视图
create view user_p
AS
SELECT user_id,auction_id,
sum(case when behavior = 'pv' then 1 else 0 end) as pv,
sum(case when behavior = 'cart' then 1 else 0 end) as cart,
sum(case when behavior = 'fav' then 1 else 0 end) as fav,
sum(case when behavior = 'buy' then 1 else 0 end) as buy
FROM userbehavior
group by user_id,auction_id

 

 计算各个路径的数量和转化率：

#总浏览量=44915个
select sum(pv)
from user_p

#浏览-购买  540  转化率=1.20%
select sum(buy),concat(round(sum(buy)/44915 *100,2),'%') as rate
from user_p
where pv>0
and fav = 0
and cart =0
and buy>0



#浏览-加购  1392个  转化率=3.10%
select sum(cart),concat(round(sum(cart)/44915 *100,2),'%') as rate
from user_p
where pv>0
and fav = 0
and cart >0

#浏览-加购-购买 119个   转化率=8.55%
select sum(buy),concat(round(sum(buy)/1392 *100,2),'%') as rate
from user_p
where pv>0
and fav = 0
and cart >0
and buy >0

#浏览-收藏       406个   转化率=0.9%  
select sum(fav),concat(round(sum(fav)/44915 *100,2),'%') as rate
from user_p
where pv > 0
and  fav > 0
and cart = 0

#浏览-收藏-购买       39个   转化率=9.61%  

select sum(buy),concat(round(sum(buy)/406 *100,2),'%') as rate
from user_p
where pv>0
and fav > 0
and cart =0
and buy >0

#浏览-收藏并加购   41个  转化率0.09%

select sum(cart),concat(round(sum(cart)/44915 *100,2),'%') as rate
from user_p
where pv>0
and fav > 0
and cart >0

#浏览-收藏并加购-购买   13个  转化率31.71%

select sum(buy),concat(round(sum(buy)/41 *100,2),'%') as rate
from user_p
where pv >0
and fav  >0
and cart >0
and buy  >0

select sum(pv),concat(round(sum(pv)/44915 *100,2),'%') as rate
from user_p
where pv >0
and fav  =0
and cart =0
and buy  =0

结果:

        从上述结果可以看出，用户浏览后直接购买的转化率为1.2%，加购后购买的转化率为8.55%，收藏后购买的转化率为9.61%，加购并收藏后购买的转化率为31.71%，显然，用户在加购、收藏等行为后的购买转化率比只浏览的购买转化率高的多，针对这种情况，商家可以通过优化产品宣传页面、开展鼓励用户收藏和加购的优惠活动等方面促进用户的收藏加购行为，从而在一定程度上提升整体的购买转化率。

        另外，浏览量很大，但是有购买行动的却是非常少，大量用户流失88.30%，从浏览到直接购买、加购、收藏、加购并收藏的转化率都非常低，也许是用户在浏览过程中没有找到喜欢的，对此，可以针对不同用户的喜好，精准推送顾客喜欢的比较热销的一些产品，从而促