阿里天池项目：淘宝电商数据分析（mysql）

随着互联网人口红利的消失，利用大数据分析深入的了解用户、进行精准化运营变得越来越重要。本项目通过电商角度，选取阿里天池项目中的淘宝App用户行为数据利用MYSQL进行数据分析。

1 理解数据

• 数据来源

阿里天池：https://tianchi.aliyun.com/dataset/dataDetail?dataId=649

包含了淘宝App由2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、收藏）。由于性能限制，本次共随机抽样10w条记录进行数据分析。

• 字段含义

user_id：用户id

item_ id：商品id

category_ id：商品类目id

behaviour_type：行为类型（pv代表浏览；fav代表收藏；cart代表加购；buy代表购买）

timestamp：用户行为时间

2 分析方向和目的

通常电商分析会包含销售、流量&搜索、用户、商品、以及促销&优惠券等维度，基于本淘宝用户行为数据包的具体情况，会从流量、用户、商品三方面进行分析，探索用户行为规律，为用户和产品运营提供更精准的策略，从而提高GMV：

 

3 数据清洗

对数据进行预处理，包括缺失值、异常值和重复值的处理

3.1 导入数据

创建新表ub，由于源数据集中没有主键，因此通过rand()和limit函数随机抽样10w条数据，插入新表中

create table ub like userbehavior 
insert into ub 
select * from userbehavior order by rand() limit 100000

3.2 删除重复值

涉及到删除数据，首先创建新表避免删除数据无法恢复，并以用户、行为、时间为唯一索引删除重复行数据

create table ub2 (select distinct user_id, item_id, category_id, behaviour_type, timestamp from ub)

数据量仍然为10万，无重复值，右键删除ub2表即可

3.3 缺失值处理

计算缺失值所占比重：无缺失

select sum(case when user_id is null then 1 else 0 end) as userid,
sum(case when item_id is null then 1 else 0 end) as itemid, 
sum(case when category_id is null then 1 else 0 end) as cateid, 
sum(case when behaviour_type is null then 1 else 0 end) as bt, 
sum(case when timestamp is null then 1 else 0 end) as date 
from ub

3.4 一致化处理

时间戳的处理：转换为日期格式，同时提取日期数和小时数，并便于后续时间序列分析

--时间戳转为日期格式

alter table ub add ubtime datetime 
update ub set ubtime = from_unixtime('timestamp','%y-%m-%d %h:%i:%s')

--长日期转为短日期

alter table ub add ubdate datetime 
update ub set ubdate = date_format(ubtime,'%y-%m-%d')

--提取小时数

alter table ub add ubhour int 
update ub set ubhour = hour(ubtime)

3.5 添加主键

alter table ub add column behaviour_id int not null auto_increment primary key first

 

3.6 异常值处理

只保留 2017年11月25日至2017年12月3日 之间的数据，因为涉及到删除，先用select验证查询语句是否正确

select* from ub where ubdate not between'2017-11-25' and '2017-12-03' 

注：mysql中between and 包含边界；not between不包含边界

查询出48条数据是在11.25号前发生的，条件设置正确，用delete语句删除

delete from ub where ubdate not between'2017-11-25' and '2017-12-03'

经过数据清理后的主表 ubs 的记录数为：99952

4 数据分析

按照相关方法，对数据进行分析

4.1流量分析

4.1.1 PV/UV随时间变化趋势

--按日统计<