一、背景目的
基于电商数据的多样化，从中寻找有价值的数据推断用户购买行为，从而实现利润提升；本项目基于淘宝用户行为数据，探索淘宝用户的行为模式，寻找高购买率背后用户行为特征，具体指标包括：日PV和日UV分析，支付率分析，复购行为分析，漏斗转化率分析和用户价值RFM分析。
二、数据获取与解读
本次竞赛基于阿里巴巴M-Commerce平台上的真实用户 - 商品行为数据。同时，它提供移动时代的典型位置信息。参与者需要构建面向移动商务的商品推荐模型。他们还希望深入了解数据背后的含义，并在适当的时间和地点为移动用户推荐合适的商品。
天池数据集：https://tianchi.aliyun.com/dataset/dataDetail?dataId=46
本数据集(UserBehavior.csv)包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、收藏、加购物车、支付）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：
商品数据集：
item_id: 用户id
item_geohash: 商品id
item_category: 品类id（商品所属品类）
用户数据集：
user_id: 用户id
item_id: 商品id
behavior_type: 用户行为类型（分别用1、2、3、4表示点击、收藏、加购物车、支付四种状态）
user_geohash: 用户地理位置
item_category: 品类id（商品所属的品类）
time: 用户行为发生时间
三、数据预处理
3.1 链接MysSQL，建立数据库taobao；建立用户表。
show databases; create database taobao1; drop database taobao1; create database taobao; use taobao;
create table user(
用户id int not null , #写入auto_increment primary key数据导入报错，
商品id varchar(255),
用户行为 enum('点击','收藏','加购物车','支付'),
用户地理位置 varchar(255),
商品品类 varchar(255),
时间 varchar(255))engine=innodb default charset=UTF8;
create table item(
用户id int not null auto_increment primary key,
商品id varchar(255),
商品品类 varchar(255))engine=innodb default charset=UTF8;
show tables;
3.2 导入数据
通过navicat将数据集导入MySQL库。共有12256906条数据，后续由于数据量过大，导致系统崩溃，故选取部分数据1048575条进行分析。
SELECT COUNT(*) FROM user;
3.3 数据清洗与预处理
3.3.1 缺失值处理
通过观察发现用户地理位置多为空值，经统计共有717785条，为防止对结果造成误导，故该维度特征不做分析。
SELECT COUNT(*) FROM `user` WHERE `用户地理位置` IS NULL;
3.3.2 用户发生行为时间数据一致化处理
由于 time 字段的时间包含（年-月-日）和小时，为了方便分析，将该字段分成 2 个字段，一个日期列和一个小时列。
# 重命名 时间 字段 改为 日期
ALTER TABLE user CHANGE 时间 日期 VARCHAR(255);
# 添加 时间 列
ALTER TABLE user ADD 时间 VARCHAR(20);
# 复制 日期 列到 时间 列
UPDATE user SET 时间 = 日期;
# 在 日期 列截取（年-月-日）信息
UPDATE user SET 日期 = REPLACE(日期, 日期, SUBSTRING_INDEX(日期,' ',1));
# 在 时间 列截取小时信息
UPDATE user SET 时间 = REPLACE(时间, 时间, SUBSTRING_INDEX(时间,' ',-1));
查询当前特征数据结构，结构类型统一方便后续统计计算
# 查询表字段结构
desc user;
# 将日期字段 修改为日期类型
ALTER TABLE user MODIFY 日期 date;
desc user;
四、构建模型和可视化分析
4.1 用户浏览量分析
用户总的浏览量(PV)
PV(访问量)：即Page View, 具体是指网站的是页面浏览量或者点击量，页面被刷新一次就计算一次。
UV(独立访客)：即Unique Visitor,访问您网站的一台电脑客户端为一个访客。
SELECT COUNT(`用户行为`) AS '点击量' FROM `user` GROUP BY `用户行为` HAVING `用户行为` = '点击';
日均访问量
12月12日访问量明显增多，与双十二活动相关，故活动期间可加大商品投放量。
SELECT 日期, COUNT(`用户行为`) AS '日均访问量' FROM USER WHERE `用户行为` = '点击' GROUP BY 日期 ORDER BY 日期;
4.2 用户行为分析
用户总数（UV）
SELECT COUNT(DISTINCT `用户id`) AS '用户总数' FROM user;
4.2.1 用户行为记录
SELECT COUNT(DISTINCT `用户id`) AS '点击用户数量' FROM user
WHERE `用户行为` = '点击';
SELECT COUNT(DISTINCT `用户id`) AS '收藏用户数量' FROM user
WHERE `用户行为` = '收藏';
SELECT COUNT(DISTINCT `用户id`) AS '加购物车用户数量' FROM user
WHERE `用户行为` = '加购物车';
SELECT COUNT(DISTINCT `用户id`) AS '支付用户数量' FROM user
WHERE `用户行为` = '支付';
4.2.2 单个用户购物情况
CREATE VIEW 单个用户行为 AS SELECT `用户id`, count(`用户行为`), SUM(CASE WHEN `用户行为`='点击' THEN 1 ELSE 0 END) AS '点击次数', SUM(CASE WHEN `用户行为`='收藏' THEN 1 ELSE 0 END) AS '收藏次数', SUM(CASE WHEN `用户行为`='加购物车' THEN 1 ELSE 0 END) AS '加购物车数', SUM(CASE WHEN `用户行为`='支付' THEN 1 ELSE 0 END) AS '购买次数' FROM user GROUP BY `用户id` ORDER BY COUNT(`用户行为`) DESC; -- 查看用户购物情况视图 SELECT * FROM 单个用户行为
4.2.3 复购率
SELECT CONCAT(ROUND(SUM(case when 购买次数>=2 then 1 else 0 end) / SUM(case when 购买次数>0 then 1 else 0 end) * 100), '%') AS '复购率' FROM 单个用户行为;
4.2.4 用户购买行为数据漏斗图
用户购买行为，收藏和加购物车两个环节是并行的，不存在上下承接关系
-- 用户购买行为计数 SELECT SUM(点击次数) AS '总点击数', SUM(收藏次数) AS '收藏总数', SUM(加购物车数) AS '加购物车总数', SUM(购买次数) AS '购买总数' FROM 单个用户行为;
-- 用户购买行为转化率
SELECT CONCAT(ROUND(SUM(点击次数)/(SELECT COUNT(*) from `user`)*100,2),'%') AS '点击-浏览转化率',
CONCAT(ROUND((SUM(收藏次数)+SUM(加购物车数))/SUM(点击次数)*100,2),'%') AS '收藏加购物车-点击转化率',
CONCAT(ROUND(SUM(购买次数)/(SUM(收藏次数)+SUM(加购物车数))*100,2),'%') AS '购买-收藏加购物车转化率'
FROM 单个用户行为;
-- 用户购买行为转化率
SELECT CONCAT(ROUND(SUM(点击次数)/(SELECT COUNT(*) from `user`)*100,2),'%') AS '点击-浏览转化率',
CONCAT(ROUND(SUM(收藏次数)/SUM(点击次数)*100,2),'%') AS '收藏-点击转化率',
CONCAT(ROUND(SUM(加购物车数)/SUM(点击次数)*100,2),'%') AS '购物车-点击转化率',
CONCAT(ROUND(SUM(购买次数)/SUM(点击次数)*100,2),'%') AS '购买-点击转化率'
FROM 单个用户行为;
通过漏斗图展示用户从浏览到最终购买的转化率，重视每一环节用户流失可能发生的情况。
从浏览量上来看用户浏览和点击量明显高于收藏加购物车和购买量。
4.2.5 购买率和购买次数之间的关系
-- 将‘购买率’进行倒序排列
SELECT 用户id, 点击次数, 收藏次数, 加购物车数, 购买次数,
ROUND(购买次数/点击次数, 2)AS '购买率'
FROM `单个用户行为`
GROUP BY `用户id`
ORDER BY 购买率 desc;
-- 将‘购买次数’进行倒序排列
SELECT 用户id, 点击次数, 收藏次数, 加购物车数, 购买次数,
CONCAT(ROUND(购买次数/点击次数*100, 2),'%') AS '购买率'
FROM `单个用户行为`
GROUP BY `用户id`
ORDER BY 购买次数 DESC;
-- 将‘购买次数’进行倒序排列
SELECT 用户id, 点击次数, 收藏次数, 加购物车数, 购买次数,
CONCAT(ROUND(购买次数/点击次数*100, 2),'%') AS '购买率'
FROM `单个用户行为`
GROUP BY `用户id`
ORDER BY 购买次数 ASC;
由上表可以发现，购买率高并不代表购买次数就高，由此可以推断，点击次数少，收藏也少但是购买次数越高的人是有目的的购物。这类用户更喜欢看准目标就直接下单，此类用户目标明确，一般都是缺啥买啥，鲜少会为了促销而冲动购物，为理智购物型。
购买率为0用户：此类用户可以认为是等待型或克制型用户，该类用户在一个月里购物欲望为0，如果点击次数较少，可能是不太会购物的或者不喜欢上网的用户；如果点击次数较多，加购或收藏较多，有可能考虑是为商家活动做准备，该类用户，下单欲望较少且自制力较强，思虑多或者不会支付，购物难度较大。
4.3 基于时间维度了解用户的行为习惯
SELECT 时间, COUNT(`用户行为`), SUM(CASE WHEN `用户行为`='点击' THEN 1 ELSE 0 END) AS '点击次数', SUM(CASE WHEN `用户行为`='收藏' THEN 1 ELSE 0 END) AS '收藏次数', SUM(CASE WHEN `用户行为`='加购物车' THEN 1 ELSE 0 END) AS '加购数', SUM(CASE WHEN `用户行为`='支付' THEN 1 ELSE 0 END) AS '购买次数' FROM user GROUP BY `时间` ORDER BY `时间`;
可以看出，每日0点到5点用户活跃度快速降低，降到一天中的活跃量最低值，6点到10点用户活跃度快速上升，10点到18点用户活跃度较平稳，18点到23点用户活跃度快速上升，达到一天中的最高值。与用户产生互动的运营行为可以参考用户比较活跃的时间段。
一周中用户活跃时间段分布
SELECT DATE_FORMAT(日期,'%W') AS weeks, COUNT(`用户行为`), SUM(CASE WHEN `用户行为`='点击' THEN 1 ELSE 0 END) AS '点击次数', SUM(CASE WHEN `用户行为`='收藏' THEN 1 ELSE 0 END) AS '收藏次数', SUM(CASE WHEN `用户行为`='加购物车' THEN 1 ELSE 0 END) AS '加购数', SUM(CASE WHEN `用户行为`='支付' THEN 1 ELSE 0 END) AS '购买次数' FROM user where 日期 BETWEEN '2014-11-23' AND '2014-12-13' GROUP BY weeks ORDER BY DATE_FORMAT(日期,'%w');
每周用户活跃度较稳定，每周五活跃度会有小幅降低，但是周末会慢慢回升。其中第四周周五用户活跃度突增是由双十二大促活动引起。
4.4 用户RFM 模型找出有价值的用户
R-Recency（最近一次购买时间）：客户最近一次交易时间的间隔。R值越大，表示客户交易发生的日期越久，反之则表示客户交易发生的日期越近。
F-Frequency（消费频率）：客户在最近一段时间内交易的次数。F值越大，表示客户交易越频繁，反之则表示客户交易不够活跃。
M-Money（消费金额）：客户在最近一段时间内交易的金额。M值越大，表示客户价值越高，反之则表示客户价值越低。
RFM分析就是根据客户活跃程度和交易金额的贡献，进行客户价值细分的一种方法。
由于数据源没有相关的金额数据，暂且通过 R 和 F 的数据对客户价值进行打分。
SELECT r.用户id,f.frequency,recent_rank,freq_rank, CONCAT( CASE WHEN recent_rank<=(4330)/4 THEN '4' WHEN recent_rank>(4330)/4 AND recent_rank<=(4330)/2 THEN '3' WHEN recent_rank>(4330)/2 AND recent_rank<=4330/4*3 THEN '2' ELSE '1' END, CASE WHEN freq_rank<=(4330)/4 THEN '4' WHEN freq_rank>(4330)/4 AND freq_rank<=(4330)/2 THEN '3' WHEN freq_rank>(4330)/2 AND freq_rank<=4330/4*3 THEN '2' ELSE '1' END )AS user_value FROM (SELECT a.*,(@rank:=@rank+1) AS recent_rank FROM ( SELECT `用户id`, DATEDIFF('2014-12-19',max(日期)) AS recent FROM user AS t1 WHERE `用户行为`='支付' GROUP BY `用户id` ORDER BY recent )AS a,(SELECT @rank:=0) AS b) AS r, (SELECT *,(@rank2:=@rank2+1) AS freq_rank FROM (SELECT `用户id`,count(*) AS frequency FROM user WHERE `用户行为`='支付' GROUP BY `用户id` ORDER BY frequency DESC) AS a,(SELECT @rank2:=0)AS b) AS f WHERE r.用户id=f.用户id ORDER BY user_value DESC;
通过打分可以了解每位顾客的特性，从而实现差异化营销。比如对于客户价值高的用户，为重点用户需要关注；对于客户价值中等且忠诚度高而购买能力不足的，可以可以适当给点折扣或捆绑销售来增加用户的购买频率。对于客户价值低且忠诚度不高而购买能力强的，需要关注他们的购物习性做精准化营销，对于客户价值低且忠诚度不高购买能力不强的客户可作为潜在客户。
五、总结
- 总体转化率只有 1%，用户点击后收藏或加购物车的转化率在 5% ，需要提高用户的购买意愿，可通过活动、精准营销等方式。
- 购买率高且点击量少的用户是比较理智购物的群体，一般是缺什么就买什么只买需要的。而购买率低的用户可以认为是等待型或克制型用户群体，下单欲望较少且自制力较强，购物难度较大。
- 大部分用户的主要活跃时间在10点到23点，在19点到23点达到一天的顶峰。每周五的活跃度有所下降，但周末开始回升。
- 通过 R 和 F 的数据对用户行为进行打分，通过打分可以对每位用户进行精准化营销。
