基于Mysql和Tableau实现的淘宝用户购物行为可视化分析

一、数据说明

本次数据来源：阿里云天池《淘宝用户购物行为数据可视化分析》https://tianchi.aliyun.com/dataset/145889

数据日期范围：2014-11-18 ~ 2014-12-18

字段	字段说明	提取说明
user_id	用户id	脱敏
item_id	商品id	脱敏
behavior_type	用户行为类型	浏览、收藏、加购物车、购买，分别对应1、2、3、4
item_category	商品分类标识	脱敏
time	用户行为时间	精准到天

可以看出，用户id、商品id、商品分类标识均做过脱敏处理，时间信息精准到天；用户行为类型按照浏览、收藏、加购物车、购买行为，分别记为数字1、2、3、4

二、数据读取与数据处理

用mysql读取csv格式的数据

LOAD DATA LOCAL INFILE 'C:\\Users\\28217\\Downloads\\user_action.csv'
into table my_table
fields terminated by ','
enclosed by '"'
lines terminated by '\n';

查看数据缺失情况

select count(*)
from my_table
where user_id is null or item_id is null or behaviour_type is null
or item_category is null or time is null;

 结果返回0，可以看出本次数据质量较好，不存在空字段，不需要进行空数据、缺失值的处理。

三、流量分析

主要关注UV和PV指标。访问量PV基于用户对页面的刷新次数，用户每刷新一次页面或打开新的一个页面就算一次访问。在本项目中，每出现一次user_id即PV+1。

UV一个用户若多次访问，只记录一次UV+1，本质上是sql语言的distinct或unique操作。

#计算PV并存储在表user_pv中
create table user_pv
as
select time,count(user_id) as pv from my_table
group by time
order by time;

得到user_pv表如下

#计算UV并存储在表user_uv中
create table user_uv
as
select time,count(distinct user_id) as uv from my_table
group by time
order by time;

 得到user_uv表如下：

得到用户的UV和PV数据，如果直接查看不太直观，下面利用tableau做出可视化结果.

首先导出uv和pv表格中的数据至csv文件中

#将user_pv表格导出至pv.csv文件中，uv同理
select time,pv 
into outfile 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\pv.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
from user_pv;


select time,uv 
into outfile 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\uv.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
from user_uv;

再用tableau制作Pv和Uv的图像，从图像中可以看出，不管是Pv还是Uv都在12月12日达到了峰值，这正是因为双十二购物节优惠，用户集中性进行消费。

四、基于AARRR漏斗模型的用户转化率分析

利用该模型分析用户在“浏览——加入购物车/收藏——购买”这一环节不同链条的转化率（加入购物车和收藏之间没有必然的递进关系，因此合并来看转化率）

（1）2014-11-18 ~ 2014-12-18时间段

分别计算四种行为对应的数量

#分别计算不同行为的数量,并存储到表格behaviour_count中
create table behaviour_count
as
select behaviour_type,count(*) as count
from my_table
group by behaviour_type;

①收藏加购转化率

公式=（加入购物车+收藏量）/浏览量=(343564+242556)/11550581=5.07%

②购买转化率

公式=购买量/（加入购物车+收藏量）=120205/(343564+242556)=20.50%

（2）2014年双12当日

#分别计算不同行为的数量,并存储到表格behaviour_count12中
create table behaviour_count12
as
select behaviour_type,count(*) as count
from my_table
where time='2014-12-12'
group by behaviour_type;

同样，代入公式计算

①收藏加购转化率

公式=（加入购物车+收藏量）/浏览量=(10446+24508)/641507=5.45%

②购买转化率

公式=购买量/（加入购物车+收藏量）=15251/(10446+24508)=43.63%

	11-18 ~ 12-18	12-12	变化量
收藏加购转化率	5.07%	5.45%	+0.38%
购买转化率	20.50%	43.63%	+23.13%

通过比较可以发现，双12当天，购买转化率是平时的2倍以上，收藏加购转化率也比平时略高。由此可以说明双12大促对用户转化起到了很好的效果。商户可以考虑在特定的节日采取一定的优惠策略，提高商品的曝光量及用户的留存度。

四、不同时间用户行为分析

①平常周7日用户行为分析

可以看出，平常周的周五为访问最低点，周三/四和周日为访问高峰期，猜测是因为淘宝的用户主要为工薪阶层，周五下班以后忙着休息、放松，淘宝使用率较低；随着周末放松以后淘宝使用率回升，工作日中的某天工作压力不大，使用率较高。

②双十二周及以后用户行为分析

 双十二周，星期五（双十二当天）用户访问量达到最高峰，此后用户访问量较为疲软。

五、品类分析

首先考虑商品总操作top10的品类

#查询商品总操作top10的商品
select item_category,count(item_category) as count_item
from my_table
group by item_category
order by count_item desc
limit 10;

其次考虑消费者人数top10的品类

#首先用新的表consumers记录消费(behaviour_type=4)的数据
create table consumers
as
select * from my_table
where behaviour_type=4;
#其次记录consumers表里user_id的去重数量，代表每类商品的消费者人数
select item_category,count(distinct user_id) as count_item
from consumers
group by item_category
order by count_item desc
limit 10;

最后考虑平均购买次数top10的品类

#计算平均购买次数
select item_category,count(user_id)/count(distinct user_id) as count_item
from consumers
group by item_category
order by count_item desc
limit 10;

可以看出，商品总操作top10和商品消费人数top10具有相当一部分重合的品类，说明受到关注的商品，消费者有较高的倾向性去购买。商家可以考虑投放广告、采取低价等策略增加商品的曝光量，从而增大商品的消费。