淘宝用户行为分析

前言

现在越来越多的线下转型到了线上经营，线下体验店线上购物将成为日后消费的大趋势。分析用户行为，走好转型之路，掌握先机快人一步。

一、背景

Ⅰ 数据来源

数据来源于阿里池公共数据集，搜索userbehavior就可出来。

Ⅱ 数据背景

本次数据分析从数据集中选取包含了2014年11月18日至2014年12月18日之间，10000名随机用户共12256906条行为数据，数据集的每一行表示一条用户行为，共6列。
列字段包含以下：
user_id：用户身份
item_id：商品ID
behavior_type：用户行为类型（包含点击、收藏、加购物车、购买四种行为，分别用数字1、2、3、4表示）
user_geohash：地理位置（有空值）
item_category：品类ID（商品所属的品类）
time：用户行为发生的时间
PS：我在建表的时候根据自己的使用习惯设置了列名。

Ⅲ 分析目的

二、探索性分析

Ⅰ 导入数据

1、创建数据表

CREATE TABLE `tb` (
  `userid` VARCHAR(30) DEFAULT NULL,
  `itemid` VARCHAR(30) DEFAULT NULL,
  `type` VARCHAR(30) DEFAULT NULL,
  `address` VARCHAR(30) DEFAULT NULL,
  `categoryid` VARCHAR(30) DEFAULT NULL,
  `time` VARCHAR(30) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

2、插入数据

LOAD DATA INFILE 'D:/user.csv'  
INTO TABLE tb  
CHARACTER SET  utf8  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'  
LINES TERMINATED BY '\n'
IGNORE 1 LINES 
; 

3、数据一致化处理
1）时间数据

-- 增加一个时间类在categoryid后面
ALTER TABLE tb ADD DATE VARCHAR(30) NOT NULL AFTER categoryid;
-- 复制time列数据到date列里
UPDATE tb SET DATE=TIME;
-- 截取日期
UPDATE tb SET DATE=REPLACE(DATE,DATE,SUBSTRING_INDEX(DATE,' ',1));
-- 截取时间
UPDATE tb SET TIME=REPLACE(TIME,TIME,SUBSTRING_INDEX(TIME,' ',-1));
-- 由于创建时date是varchar型，所以需要更改
alter table tb modify date date;

2）行为类型数字更改为指定字符

update tb set type =replace(type,1,'pv');
update tb set type =replace(type,2,'fav');
update tb set type =replace(type,3,'cart');
update tb set type =replace(type,4,'buy');

Ⅱ 数据处理

1、缺失值处理

address列表示地理位置信息，由于数据存在大量空值，且位置信息被加密处理，因此不对该列进行分析。

2、异常值处理

选取的时间段为2014年11月18日至2014年12月18日之间，看是否有不在该范围的数据。如果有则删除不在该范围的数据：DELETE FROM tb WHERE date BETWEEN '2014-11-18' AND '2014-12-18 '

SELECT MIN(date),MAX(date) FROM tb;

3、重复值处理

在此次分析中，没有主键存在，所以列可存在重复值，不需要进行重复值处理

三、数据分析

Ⅰ 整体消费情况

总访问量（PV）

SELECT COUNT(TYPE)AS 总访问量 FROM tb WHERE TYPE='pv';

日均访问量

SELECT DATE,COUNT(TYPE)AS 日访问量 FROM tb WHERE TYPE='pv' GROUP BY DATE;

用户总数（UV）

SELECT COUNT(DISTINCT userid) AS 用户总数 FROM tb ;

购买用户数量及占总访问比

SELECT COUNT(DISTINCT userid)AS 购买人数 FROM tb WHERE TYPE='buy';

SELECT (SELECT COUNT(DISTINCT userid)AS 购买人数 FROM tb WHERE TYPE='buy')/(SELECT COUNT(TYPE)AS 总访问量 FROM tb WHERE TYPE='pv')*100 AS 购买占比 FROM tb;

用户购物行为

CREATE VIEW user_behavior AS
SELECT userid,COUNT(TYPE),
SUM(CASE WHEN TYPE='pv' THEN 1 ELSE 0 END) AS "点击次数",
SUM(CASE WHEN TYPE='fav' THEN 1 ELSE 0 END) AS "收藏次数",
SUM(CASE WHEN TYPE='cart' THEN 1 ELSE 0 END) AS "加购数",
SUM(CASE WHEN TYPE='buy' THEN 1 ELSE 0 END) AS "购买数"
FROM tb GROUP BY userid ORDER BY COUNT(TYPE);
SELECT *FROM user_behavior;

复购率

SELECT SUM(CASE WHEN 购买数>1 THEN 1 ELSE 0 END)AS 购买数大于一次,
SUM(CASE WHEN 购买数>0 THEN 1 ELSE 0 END)AS 总购买数,
CONCAT(ROUND(SUM(CASE WHEN 购买数>1 THEN 1 ELSE 0 END)/SUM(CASE WHEN 购买数>0 THEN 1 ELSE 0 END)*100,2),'%')AS 复购率
FROM user_behavior;

Ⅱ 用户行为转化漏斗

在实际购物过程中，收藏与加入购物车没有先后之分，所以将两个行为视作一个行为分析。

SELECT SUM(点击次数)AS 总点击次数,SUM(收藏次数)AS 总收藏次数,SUM(加购数)AS 总加购数,SUM(购买数)AS 总购买数 FROM user_behavior;

SELECT CONCAT(ROUND(SUM(点击次数)/SUM(点击次数)*100,2),'%')点击,CONCAT(ROUND(SUM(收藏次数)+SUM(加购数)/SUM(点击次数)*100,2),'%')点击到收藏加购,
CONCAT(ROUND(SUM(购买数)/SUM(点击次数)*100,2),'%')点击到购买 FROM user_behavior;

不同行业转化率不同，据12年研究表明，整个互联网行业范围内，平均转化率为2.13%，本次转化率才1.04%，离平均转化率还存在较大差异，因此还存在很大的增长空间。

Ⅲ 用户画像

购买率高的用户特征画像

SELECT userid,点击次数,收藏次数,加购数,购买数,ROUND(购买数/点击次数*100,2)购买率 FROM user_behavior GROUP BY userid ORDER BY 购买率 DESC;

SELECT userid,点击次数,收藏次数,加购数,购买数,ROUND(购买数/点击次数*100,2)购买率 FROM user_behavior GROUP BY userid ORDER BY 购买数 DESC;

对比分析可看出购买率高的用户点击率反而不是最多的，收藏与加购数也很少，由此可得出这部分用户目的性很强，基本上不会被广告或促销吸引，属于理智型用户。

购买率低的用户特征画像

SELECT userid,点击次数,收藏次数,加购数,购买数,ROUND(购买数/点击次数*100,2)购买率 FROM user_behavior GROUP BY userid ORDER BY 购买数;

由以上结果可以看出，购买率为低用户分为两类：
第一类是点击次数少的，一方面的原因是这类用户可能是不太会购物或者不喜欢上网的用户，可以加以引导，另一方面是从商品的角度考虑，是否商品定价过高或设计不合理；
第二类用户是点击率高、收藏或加购物车也多的用户，此类用户可能正为商家的促销活动做准备，下单欲望较少且自制力较强，思虑多或者不会支付，购物难度较大。

Ⅳ 用户的行为习惯

一天用户活跃分布

SELECT TIME,COUNT(TYPE)用户行为总数,
SUM(CASE WHEN TYPE='pv' THEN 1 ELSE 0 END) AS "点击次数",
SUM(CASE WHEN TYPE='fav' THEN 1 ELSE 0 END) AS "收藏次数",
SUM(CASE WHEN TYPE='cart' THEN 1 ELSE 0 END) AS "加购数",
SUM(CASE WHEN TYPE='buy' THEN 1 ELSE 0 END) AS "购买数"
FROM tb GROUP BY TIME ORDER BY TIME;

可以看出，每日0点到5点用户活跃度快速降低，降到一天中的活跃量最低值，6点到10点用户活跃度快速上升，10点到18点用户活跃度较平稳，17点到23点用户活跃度快速上升，达到一天中的最高值。

一周中用户活跃时段分布

由于选取的时间段中有些不是完整的一周所以在分析的时候人为去掉那些不成整周的数据。

SELECT DATE_FORMAT(DATE,'%W')星期,COUNT(TYPE)用户行为总数,
SUM(CASE WHEN TYPE='pv' THEN 1 ELSE 0 END) AS "点击次数",
SUM(CASE WHEN TYPE='fav' THEN 1 ELSE 0 END) AS "收藏次数",
SUM(CASE WHEN TYPE='cart' THEN 1 ELSE 0 END) AS "加购数",
SUM(CASE WHEN TYPE='buy' THEN 1 ELSE 0 END) AS "购买数"
FROM tb 
WHERE DATE BETWEEN '2014-11-23' AND '2014-12-13'
GROUP BY 星期 ORDER BY DATE_FORMAT(DATE,'%W');

由以上结果可以看出，每周用户活跃度较稳定，每周五活跃度会有小幅降低，但是周末会慢慢回升。其中周五用户活跃度突增，这是由于这中间存在双十二电商大促销活动。

四、结论

1. 总体转化率只有 1%，用户点击后收藏和加购物车的转化率在 5% ，需要提高用户的购买意愿，可通过活动促销、精准营销等方式。
2. 购买率高且点击量少的用户属于理智型购物者，有明确购物目标，受促销和广告影响少；而购买率低的用户可以认为是等待型或克制型用户群体，下单欲望较少且自制力较强，购物难度较大。
3. 大部分用户的主要活跃时间在10点到23点，在19点到23点达到一天的顶峰。每周五的活跃度有所下降，但周末开始回升。可以根据用户的活跃时间段精准推送商家的折扣优惠或促销活动，提高购买率。