mysql 做淘宝用户行为分析

mysql 做淘宝用户行为分析

一.项目背景及目的

1. 项目背景
2. 研究目的及问题

 本次数据分析旨在分析淘宝用户在2017年11月25日到2017年12月3日的用户整体购物情况，运用对比分析法、多维度拆解分析法和检验假设分析法，构建AARRR模型对从浏览到购物各个环节转化流失情况做出分析，搭建RFM模型对用户类型按价值进行分类，进而为提高总销量提供建议。
 研究问题如下：
 用户在不同时间周期下的活跃规律是怎样的？分析用户活跃度在一天中和一周中的变化，找到用户在不同时间段中的购买行为规律。
 用户主要在哪个环节流失，流失的原因是什么？绘制淘宝用户从浏览到购买的转化率漏斗，观察漏斗转化率，提出改善转化的建议。
 如何对用户进行精准运营？将用户基于RFM模型分为四类，建立评分机制，对不同价值的用户采用不同的运营刺激消费。

二.理解数据

1.数据来源及介绍

本数据集（UserBehavior）是阿里巴巴提供的一个淘宝用户行为数据集，包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据来源于阿里巴巴天池[添加链接描述]
(https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1)
2.字段含义
数据集包含的字段含义如下表所示
其中的用户行为类型共有四种

关于数据集大小的一些说明如下

2.数据导入

因为数据量过大，内存不足，所以用navicat导入时截取其中10万条数据用于分析。因数据集中不包括数据名称行，导入时需将栏位名称设置为1，第一个数据行设置为2。

三.数据清洗

1.选择子集

出于分析需要，将五个字段都保留。

2.列名重命名

导入数据后，分别修改字段名如下, 分别代表了用户ID，商品ID，商品类目ID，行为类型，时间戳。

3.重复值处理

将含有相同的user_id, item_id, timestamp的数据，视为重复值。查询后发现无重复值，即无需删除。

Select userId,itemId,timestamps
From Userbehavior
Group BY userId,itemId,timestamps
Having Count(userid) > 1;

4.缺失值处理

select count(userid),count(itemid),count(categoryid),count(bahavior),count(timestamps)
from Userbehavior

确认无缺失值

5.一致化处理

新增字段date, time用于存放时间

alter table userbehavior add (dates varchar(255),time varchar(255));

将timestamp字段用FROM_UNIXTIME函数进行格式化，存放于dates, time

update Userbehavior 
set dates=FROM_UNIXTIME(timestamps,'%Y-%m-%d'),
time=FROM_UNIXTIME(timestamps,'%H:%i:%s');

处理后部分结果如下图

6.异常值处理

根据数据集介绍，数据采集时间应该在2017年11月25日至2017年12月3日之间，检查是否存在超出时间范围的数据，作为异常值；

select min(dates),max(dates)
from Userbehavior;

结果发现最小值早于日期范围的最小值

删除不符合时间的数据并验证如下

delete from userbehavior
where dates<'2017-11-25';
select min(dates),max(dates)
from Userbehavior;

查看完成数据清洗后的数据情况

SELECT 
COUNT(distinct userid) AS 用户数量,
COUNT(distinct itemid) AS 商品数量,
COUNT(distinct categoryid) AS 商品类型数量,
SUM(case when bahavior = 'pv' then 1 else 0 end) AS 浏览次数,
SUM(case when bahavior = 'fav' then 1 else 0 end) AS 收藏次数,
SUM(case when bahavior = 'cart' then 1 else 0 end) AS 加车次数,
SUM(case when bahavior = 'buy' then 1 else 0 end) AS 购买次数,
COUNT(dates) AS 数据总量
from userbehavior;

四.数据分析

1.用户行为与时间分析

1.1 日活数据分析
选择pv作为衡量当日用户活跃度的指标，即当用户触发商品详情页时，可计入当日用户活跃次数。

select dates,count(bahavior)
from userbehavior
where bahavior='pv'
group by dates
order by dates;

由图可知，用户日活在2017年的12月2号和3号明显高于其他几天，查询发现这两日是周末时间，猜测可能是在周末用户拥有更多的休闲娱乐时间用于购物，浏览商品详情，因此周末日活明显高于工作日日活。

1.2 查看商品购买数据

使用“buy”行为来统计用户购买量的一周变化，意义在于统计用户单位时间内触发购买行为的次数

select dates,count(bahavior) as 当日购买 
from userbehavior
where bahavior='buy'
group by dates
order by dates;

观察上图可知，用户购买行为数据与用户日活分布相似，当日购买次数在12/02和12/03这两天较高。可总结用户的活跃行为与购买行为确实存在一定规律性，相较于工作日，用户更倾向于在周末浏览商品和购物。

选择pv作为作为衡量用户活跃度的指标，查看用户活跃是否在不同时间段上有显著不同

#工作日日活
select
sum(case when time between '00:00:00' and '00:59:59' then 1 else 0 end) as '0',
sum(case when time between '01:00:00' and '01:59:59' then 1 else 0 end) as '1',
sum(case when time between '02:00:00' and '02:59:59' then 1 else 0 end) as '2',
sum(case when time between '03:00:00' and '03:59:59' then 1 else 0 end) as '3',
sum(case when time between '04:00:00' and '04:59:59' then 1 else 0 end) as '4',
sum(case when time between '05:00:00' and '05:59:59' then 1 else 0 end) as '5',
sum(case when time between '06:00:00' and '06:59:59' then 1 else 0 end) as '6',
sum(case when time between '07:00:00' and '07:59:59' then 1 else 0 end) as '7',
sum(case when time between '08:00:00' and '08:59:59' then 1 else 0 end) as '8',
sum(case when time between '09:00:00' and '09:59:59' then 1 else 0 end) as '9',
sum(case when time between '10:00:00' and '10:59:59' then 1 else 0 end) as '10',
sum(case when time between '11:00:00' and '11:59:59' then 1 else 0 end) as '11',
sum(case when time between '12:00:00' and '12:59:59' then 1 else 0 end) as '12',
sum(case when time between '13:00:00' and '13:59:59' then 1 else 0 end