淘宝电商用户数据分析
一、分析背景
电商野蛮生长的时代已经过去，由“流量为王”的玩法逐渐转变为“用户至上”的运营思路。本文就利用提取MySQL的数据，通过写SQL的数据处理方式，对用户的消费行为特点进行分析。
一、数据来源和数据理解
User Behavior Data from Taobao for Recommendation-数据集-阿里云天池tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1
本数据集包含了2017年11月25日至2017年12月3日之间，约五百万随机用户的所有行为(行为包括点击、购买、加购、收藏)。
数据集的列字段包含以下：
user_id：用户身份
item_id：商品ID
behavior_type：用户行为类型(包含点击、收藏、加购物车、购买四种行为，分别用数字1、2、3、4表示)
user_geohash：地理位置(有空值)
item_category：品类ID(商品所属的品类)
time：用户行为发生的时间
二、提出问题
用户角度：
通过AARRR模型分析各个购物环节的流失率，有什么需要改进的地方?;
不同时间维度下用户的购物行为特点和活跃规律是什么样的?
有高价值的用户都有哪些?
产品角度：
1.热销产品有哪些?
2.差销产品有哪些?
三、数据清洗
1.选择子集
我们选择导入10万条数据集合来进行数据分析，所选数据皆为有用子集
2.列重命名
导入的原数据集没有列名，因此给数据添加对应的列名。
3.删除重复值
SELECt DISTINCT
user_id,
item_id,
category_id,
behavior,
timestamps
FROM
userbehavior;
查询结果中没有发现重复值
4.缺失值处理
查询每列是否有空值
SELECt
count( userid ),
count( itemid ),
count( categoryid ),
count( behavior ),
count( timestamps )
FROM
userbehavior
WHERe
userid IS NULL
OR itemid IS NULL
OR categoryid IS NULL
OR behavior IS NULL
OR timestamps IS NULL;
查询结果中没有发现重复值
5.一致化处理
将timestamps列的数据类型由字符型转换成时间格式，并创建date列和hour列分别存放对应的日期和小时
# 将timestamps列转换为日期格式
UPDATe userbehavior SET timestamps=FROM_UNIXTIME(timestamps,'%Y-%m-%d %H:%i:%s');
# 新建date列,从timestamp列截取日期
ALTER TABLE userbehavior add date varchar(10);
UPDATe userbehavior SET date=SUBSTRINg(timestamps,1.10);
# 新建hour列,从timestamp列截取小时
ALTER TABLE userbehavior add hours varchar(10);
UPDATE userbehavior SET hours=SUBSTRINg(timestamps,12.2);
6.异常值处理
导入的数据选取的时间是2017年11月25日至2017年12月3日之间。
(1)通过查询date列，看是否存在异常值
(2)删除制定日期之外的异常值
# 删除指定日期之外的异常值
DELETE FROM userbehavior
WHERe date > '2017-12-03' or date < '2017-11-25';
# 查询日期列最小值和最大值检查是否处理异常值
SELECt min(date),max(date) FROM userbehavior;
四.分析问题
用户角度
（一）用户的整体购物情况
对总用户数，商品数，商品类别数，用户行为数进行分析汇总
# 统计数据集中所有行为数据，总用户数，商品数，商品类别数，用户行为数
SELECt
count( * ) AS 行为数,
count( DISTINCT userid ) AS 用户数,
count( DISTINCT itemid ) AS 商品数,
count( DISTINCT categoryid ) AS 商品类别数,
count( DISTINCT behavior ) AS 用户行为数
FROM
userbehavior
由上可知，数据集中总共有99956条，一共包含983位用户，64440个商品，3128个类别，4种用户行为。
(二)用户行为分析
(1)用户总行为数漏斗
由于收藏和加购都为浏览和购买阶段之间确定购买意向的用户行为，且不分先后顺序，因此将其算作一个阶段。
SELECt
behavior,
count(*)
FROM
userbehavior
GROUP BY
behavior;
从上图可以看出，从浏览到有购买意向只有9%的转化率。只有少量用户是直接购买而未通过收藏和加入购物车，从页面浏览到收藏/加购的转化率偏低，该阶段的转化率应重点提升。
(2)独立访客数漏斗
SELECt
behavior,
count(distinct userid) AS '用户数'
FROM
`userbehavior`
GROUP BY
behavior
如图所示，约有68%的付费用户，用户付费转化率相当高。
(3)用户行为路径分析
分析用户购买行为路径中主要考虑以下几种：
计算过程如下：
select sum(case when pv>0 then 1 else 0 end) as 'pv',
sum(case when pv>0 and buy>0 and cart=0 and fav=0 then 1 else 0 end) as 'pv-buy',
sum(case when pv>0 and (cart>0 or fav>0) then 1 else 0 end) as 'pv-cart/fav',
sum(case when pv>0 and (cart>0 or fav>0) and buy>0 then 1 else 0 end) as 'pv-cart/fav-buy',
sum(case when pv>0 and (cart>0 or fav>0) and buy=0 then 1 else 0 end) as 'pv-cart/fav-lost',
sum(case when pv>0 and cart=0 and fav=0 and buy=0 then 1 else 0 end) as 'pv-lost'
from userbehavior;
结果如下：
从以上结果可以看出：
1) 用户点击后流失率为7%，这说明平台对用户具有很大吸引力。
2) 用户点击-收藏/加购-购买的转化率比点击-购买的转化率高，用户更喜欢把商品收藏或加入购物车后购买,可以从产品交互界面和营销机制等方面积极引导用户把商品加入购物车或收藏
3) 从流失率分析，用户点击收藏/加购
(4)用户流失原因
这里运用假设检验分析的方法,从产品和用户两个维度，收集相关数据进行分析。
假设一：平台推荐商品不是用户想买商品？
收集数据：想要验证平台推荐的商品是不是用户想买的，可以分析用户浏览量和用户购买量是否一致
I.创建视图统计浏览量TOP10商品种类和购买量TOP10商品种类
# 创建浏览量TOP10商品种类视图
create view pv_top10_category as
select categoryid,count(*) as 浏览量top10
from userbehavior
WHERe behavior='pv'
GROUP BY categoryid
ORDER BY count(*) desc
limit 10;
# 创建购买量TOP10商品种类视图
create view buy_top10_category as
select categoryid,count(*) as 购买量top10
from userbehavior
WHERe behavior='buy'
GROUP BY categoryid
ORDER BY count(*) desc
limit 10;
根据categoryid对比分析浏览量TOP10和购买量TOP10重合部分商品种类
# 浏览量TOP10商品种类
SELECt * FROM pv_top10_category as a
left join buy_top10_category as b
USING(categoryid);
# 购买量TOP10商品种类
SELECt * FROM buy_top10_category as a
left join pv_top10_category as b
USING(categoryid);
结果如下表所示：
由上表可以看出，浏览量TOP10商品种类和购买量TOP10商品种类中仅有50%的商品种类重合，重合度并不高。
II.创建视图统计浏览量TOP10商品和购买量TOP10商品
# 创建浏览量TOP10商品视图
create view pv_top10_item as
select itemid,count(*) as 浏览量top10
from userbehavior
WHERe behavior='pv'
GROUP BY itemid
ORDER BY count(*) desc
limit 10;
# 创建购买量TOP10商品视图
create view buy_top10_item as
select itemid,count(*) as 购买量top10
from userbehavior
WHERe behavior='buy'
GROUP BY itemid
ORDER BY count(*) desc
limit 10;
根据itemid对比分析浏览量TOP10和购买量TOP10重合部分商品
# 浏览量TOP10商品
SELECt * FROM pv_top10_item as a
left join buy_top10_item as b
USING(itemid);
# 购买量TOP10商品
SELECt * FROM buy_top10_item as a
left join pv_top10_item as b
USING(itemid);
结果如下表所示：
由上表结果可以看出，浏览量TOP10商品和购买量TOP10商品中没有一个重复的商品ID，重合度为0.
得出结论：用户高浏览量并不能带来高购买量，即平台推荐商品不是用户想买商品。
假设二：商品种类太少？
收集数据：通过统计商品类目数量和涵盖的商品数量来验证商品种类是否太少
# 整体商品类目与对应的商品数量
SELECt
count( DISTINCT categoryid ) AS 商品类目数量,
count( DISTINCT itemid ) AS 商品数量
FROM
userbehavior;
结果如下表所示：
得出结论：数据集共有3128中商品类目涵盖64440中商品，供用户选择的商品种类很多。
假设三：商品没有吸引力？
收集数据：通过分析商品购买次数的占比，从而验证商品是否有吸引力。
统计每个商品的购买次数
# 每个商品的购买次数
SELECt itemid,count(*) AS 购买次数
FROM userbehavior
WHERe behavior = 'buy'
GROUP BY itemid
ORDER BY count(*) DESC;
结果如下表所示下：
# 购买次数占比
SELECt
a.购买次数,
count( a.itemid ) AS 商品数,
concat(
round( count( a.itemid ) / ( SELECT count( DISTINCT itemid ) FROM userbehavior WHERe behavior = 'buy' ) * 100. 2 ),
'%'
) AS 占比
FROM
( SELECt itemid, count( * ) AS 购买次数 FROM userbehavior WHERe behavior = 'buy' GROUP BY itemid ) AS a
GROUP BY
a. 购买次数;
结果如下表所示：
得出结论：商品购买次数最多不超过4次，且购买1次的商品占比94.81%，说明产品复购率低，商品对用户没有足够多的吸引力，假设三成立。
(4)用户粘性分析
1)跳出率
跳出率：跳出率：用户只产生pv行为后没有后续其他行为的次数占所有访问次数的比例
select concat(round(sum(pv)/select count(*) from userbehavior)*100.2)，'%')as 跳出率
from(
select userid,sum(case behavior when 'pv' then 1 else 0 end ) as pv
sum(case behavior when 'fav' then 1 else 0 end ) as 'cart'
sum(case behavior when 'cart' then 1 else 0 end ) as 'cart'
sum(case behavior when 'buy' then 1 else 0 end ) as 'buy'
from userbehavior
groupby userid) as a
where 'pv'>0 and fav=0 and cart=0 and buy=0
结果如下表：
得出结论：页面的跳出率为2.51%，用户的跳出率低，说明平台对用户的吸引力较大。
2)用户复购率
复购用户：产生两次或两次以上购买的用户并且购买日期不在同一天
用户复购率=复购用户数/总购买用户数
复购用户数
select count(*) as 用户复购数 from (select userid,count(*) as 用户复购数 from userbehavior
where behavior='buy'
group by userid
having count(distinct date)>=2) as a
总购买用户数
select count(distinct userid)
from userbehavior
where behavior='buy'
得出结论：用户复购率=复购用户数/总购买用户数=365/671=54.40%。用户的复购率超过50%，用户具有较高的忠诚度。
3)复购用户购买次数占比
# 复购用户购买次数
SELECt sum(购买次数) as 复购用户购买次数
FROM # 查找复购用户
(SELECt userid,count(*) as 购买次数 FROM `userbehavior`
WHERe behavior='buy'
GROUP BY userid
HAVINg count(DISTINCT date)>=2) as a;
# 总购买次数
SELECt count(*) as 总购买次数
FROM userbehavior
WHERe behavior='buy';
得出结论：用户的复购率超过了50%，同时复购用户购买次数占比达到了79.40%，说明平台用户粘性很高，且购买积极性也高。
4)留存率
把每天新增的用户进行分组，可以通过每天新增用户的留存率来判断平台对用户的吸引力，由于数据集仅有九天的数据，这里使用2017年11月25日的所有用户作为第一天新增用户，仅计算这九天的新增用户留存率。
# 计算每日留存率
select a.date, count(distinct a.userid) as 日新增用户数,
concat(round(100*count(distinct if(datediff(b.date,a.date)=1. b.userid, null))/count(distinct a.userid),2),'%') as 次日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=2. b.userid, null))/count(distinct a.userid),2),'%') as 二日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=3. b.userid, null))/count(distinct a.userid),2),'%') as 三日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=4. b.userid, null))/count(distinct a.userid),2),'%') as 四日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=5. b.userid, null))/count(distinct a.userid),2),'%') as 五日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=6. b.userid, null))/count(distinct a.userid),2),'%') as 六日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=7. b.userid, null))/count(distinct a.userid),2),'%') as 七日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=8. b.userid, null))/count(distinct a.userid),2),'%') as 八日留存率
from #统计每日新增的用户名单
(select d1.*
from lading as d1 left join lading as d2 on d1.userid=d2.userid and d1.date>d2.date
where d2.date is null) as a
left join
lading as b on a.userid=b.userid
group by a.date;
得出结论:每日的留存率都处在较高的水平，这说明用户的忠诚度较高。
综上所述：从用户的跳出率、复购率和留存率可以看出，平台用户粘性很高，忠诚度高且购买积极性也高。
(三)用户购物行为分析
(1)每天用户访问变化情况
UV(unique visitor)，代表独立访客人数。
PV/UV：就是平均一个独立访问者所浏览的页面访问量，引申含义就是页面重复访问量，反映用户购买意向，比值越大反映用户购买意向强烈。
SELECt date as 日期,
sum(case when behavior='pv' then 1 else 0 end) as 点击量,
count(DISTINCT userid) as 用户数,
(sum(case when behavior='pv' then 1 else 0 end)/count(DISTINCT userid)) as 重复访问量
FROM userbehavior
GROUP BY date
结果如下表所示：
数据可视化图表如下：
得出结论：由以上结果可以看出，在11-30日至12-3日期间，点击量和用户数呈明显上升趋势，都在12-2日到达峰值，而重复访问量先快速攀升而后又迅速下降，在12-1日达到峰值(12-1日为周五)。
(2)每天用户购买行为的特点
SELECt date as '日期',count(behavior) as 用户行为总量,
sum(case when behavior='pv' then 1 else 0 end) as 点击量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏数,
sum(case when behavior='cart' then 1 else 0 end) as 加购数,
sum(case when behavior='buy' then 1 else 0 end) as 购买数
FROM userbehavior
GROUP BY date;
结果如下表所示：
数据可视化图表如下：
由以上结果可以看出，在11-30日至12-3日期间用户购买行为整体呈上升趋势，综合(1)、(2)的结果，通过查询可知是由于双十二大促的预售活动导致访问量的激增，用户表现强烈的购买意愿，将商品收藏、加入购物车为双十二当天购买做准备。
(3)一天中用户的活跃时段分布
SELECt hours as '时段',count(behavior) as 用户行为总量,
sum(case when behavior='pv' then 1 else 0 end) as 点击量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏数,
sum(case when behavior='cart' then 1 else 0 end) as 加购数,
sum(case when behavior='buy' then 1 else 0 end) as 购买数
FROM userbehavior
GROUP BY hours
ORDER BY hours;
结果如下表所示：
数据可视化图表如下：
从结果可以看出，每日0点到4点用户活跃度快速降低，降到一天中的活跃量最低值，符合人们正常作息时间规律，5点到10点用户活跃度快速上升，12点到15点用户活跃度缓慢回升，18点到21点用户活跃度快速上升，达到一天中的最高值，在此时段用户最活跃可推送促销活动或产品直播，刺激消费者购买。
(4)一周中用户活跃时段分布
SELECt DATE_FORMAT(date,'%W') as '星期',
count(behavior) as 用户行为总量,
sum(case when behavior='pv' then 1 else 0 end) as 点击量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏数,
sum(case when behavior='cart' then 1 else 0 end) as 加购数,
sum(case when behavior='buy' then 1 else 0 end) as 购买数
FROM userbehavior
GROUP BY DATE_FORMAT(date,'%W')
ORDER BY WEEKDAY(date);
结果如下表所示：
数据可视化图表如下：
从结果可以看出，每周一至周四用户活跃度较稳定，周五活跃度会有小幅上升，周末活跃度大幅度攀升达到最大值，这是周末双十二大促销活动引起，可见在周末推送促销活动更能刺激消费者购买。
(四)用户价值分析
运用RFM分析方法，对用户按价值分层，找出最有价值用户。
由于数据源没有相关的金额数据，暂且通过 R 和 F 的数据对客户价值进行分类。
1)计算R、F 值
由于数据集包含的时间是从2017年11月25日至2017年12月3日，这里选取2017年12月3日作为计算日期，统计客户最近产生交易的日期距离2017年12月3日间隔天数和交易的次数。
# 最近一次消费时间间隔R和消费频率F
SELECt userid,DATEDIFF('2017-12-04',max(date)) AS R,count(*) as F
FROM userbehavior
WHERe behavior = 'buy'
GROUP BY userid;
结果如下表所示
2)给R、F 值按价值打分
根据上面计算得到的用户R、F值，定义一个打分规则，如下图：
打分计算过程如下：
# 对最近一次消费时间间隔(R)和消费频率(F)进行评分，并创建视图rfm
create view rfm as
SELECt a.*,(case
when R>20 then 1
when R between 10 and 20 then 2
when R between 5 and 10 then 3
when R between 3 and 5 then 4
when R<=3 then 5
else 0 end) as Rscore,
(case
when F<=2 then 1
when F between 2 and 6 then 2
when F between 6 and 8 then 3
when F between 10 and 20 then 4
when F>20 then 5
else 0 end) as Fscore
FROM
(SELECt userid,DATEDIFF('2017-12-04',max(date)) AS R,count(*) as F
FROM userbehavior
WHERe behavior = 'buy'
GROUP BY userid) as a;
结果如下表所示：
3)计算打分平均值
# 计算打分平均值
SELECt avg(Rscore) as avg_R, avg(Fscore) as avg_F
FROM rfm;
结果如下表所示：
4) 用户分类
按照R值和F值的平均分对用户进行分类
# 用户分类
select userid,Rscore,Fscore,
(case when Rscore>4.158 then '高' else '低' end) as 'R值高低',
(case when Fscore>1.5484 then '高' else '低' end) as 'F值高低'
from rfm;
结果如下表所示：
因为我们这里仅考虑R、F因素，把R、F值都低的用户定义为一般价值用户，因此按下面用户分类规则进行分类：
对用户进行标记：
#对用户进行标记
select userid,R值高低,F值高低,
(case
when R值高低='高' and F值高低='高' then '重要价值用户'
when R值高低='低' and F值高低='高' then '重要保持用户'
when R值高低='高' and F值高低='低' then '重要发展用户'
when R值高低='低' and F值高低='低' then '一般价值用户'
else 0
end) as '用户分类'
from
(select userid,Rscore,Fscore,
(case when Rscore>4.158 then '高' else '低' end) as 'R值高低',
(case when Fscore>1.5484 then '高' else '低' end) as 'F值高低'
from rfm) as a;
结果如下表所示：
5) 对用户分类进行统计
# 对用户分类统计
select 用户分类,count(*) as 用户数
FROM
(select userid,R值高低,F值高低,
(case
when R值高低='高' and F值高低='高' then '重要价值用户'
when R值高低='低' and F值高低='高' then '重要保持用户'
when R值高低='高' and F值高低='低' then '重要发展用户'
when R值高低='低' and F值高低='低' then '一般价值用户'
else 0
end) as '用户分类'
from
(select userid,Rscore,Fscore,
(case when Rscore>4.158 then '高' else '低' end) as 'R值高低',
(case when Fscore>1.5484 then '高' else '低' end) as 'F值高低'
from rfm) as a) as b
GROUP BY 用户分类;
结果如下表所示：
对数据结果进行可视化：
由以上结果可知：
1)重要价值客户占比23.70%，这部分用户应积极维护奖励(免费赠与VIP会员资格)，使其变成我们的忠实用户。
2)重要发展用户占比21.46%，这部分的用户应积极引导刺激消费(发放优惠券)，提升用户的忠诚度和粘性。
3)重要保持用户占比19.08%，这部分用户可根据过往用户消费记录进行大数据分析，进行个性化推荐精准营销，以提高复购率;
4)一般价值用户占比35.77%，这部分用户大概率已经流失，要想办法主动联系客户，调查清楚哪里出了问题，通过邮件推送、活动短信提醒等方法挽回用户。
产品角度
商品购买次数
统计所有商品的购买次数，同时找到购买次数、浏览次数、收藏次数和加入购物车次数最多的商品。
SELECt product_buytimes, COUNT(*) AS product_type_count
FROM
(SELECt COUNT(user_id) AS product_buytimes
FROM User
WHERe behavior_type = 'buy'
GROUP BY item_id) AS product_buypool
GROUP BY product_buytimes
ORDER BY product_buytimes ASC;
本次分析的商品共有64440中，用户购买的商品共有16743种，购买数量非常集中的商品比较少;在本次统计的数据中，只购买一次的商品有56154种，占用户购买商品数的79.26%，说明商品售卖主要依靠长尾商品的累积效应，而非爆款商品的带动。
商品类目销售情况
SELECt categoryid , COUNT(*) AS cat_count
FROM user
WHERe behavior = 'buy'
GROUP BY category_id
ORDER BY cat_count DESC;
如图所示，商品类目销售情况中有较为明显的集中趋势，可根据畅销类目优化商品展示、加强商品捆绑，进而提高销量。
六、总结与建议
总结：
1.平台对用户吸引力巨大，用户在平台的行为路径中通过点击-收藏/加购-购买的转化率最高，应积极引导用户把商品加入购物车或收藏
2. 用户流失的环节主要在收藏/加购，流失的原因主要是平台推荐的商品并非用户喜欢的商品，同时平台也缺乏足够吸引的爆款产品
3 .优化平台推荐机制，把更多流量给到顾客愿意购买的商品，通过打造爆款产品，吸引更多用户复购
4.用户在平台的跳出率为2.51%、商品复购率超过了50%，每日新增用户的留存率都比较高，说明用户粘性很高，忠诚度高且购买积极性也高。
5.从用户的消费行为习惯看，用户在周末的时候PV、UV较高，活跃度大幅上升，且购买意愿强烈，在一天中的18点到21点是活跃高峰期，可以根据用户的活跃时间段进行广告投放、精准推送商家的折扣优惠或促销活动，同时可以利用新媒体平台进行微信、抖音等来获取更多新用户。
6. 通过对分用户行为路径分析及各个环节转化率的分析，用户在点击-收藏/加购-购买的转化率最高，应从产品交互界面、营销机制等方面积极引导用户把商品加入购物车或收藏。
7.从流失率分析，用户把商品收藏或加入购物车后流失率达28.9%，这部分用户流失的主要原因是平台推荐商品不是用户想买商品以及商品没有吸引力，通过优化平台推荐机制，把更多流量给到顾客愿意购买的商品，通过打造爆款产品，吸引更多用户复购。
8. 通过RFM模型对用户进行价值分类，发现用户主要集中在一般价值用户，占比高达35.77%，这一部分的用户极有可能已流失，建议通过APP定向推送、短信和邮件等形式进行精准发放有偿问卷主动联系用户，调查清楚哪里出了问题，制定相应的挽回策略。
9.对销量高的商品类目，应该主动加大这类商品的推广力和供应力度。
乐发网超市批发网提供超市货源信息,超市采购进货渠道。超市进货网提供成都食品批发,日用百货批发信息、微信淘宝网店超市采购信息和超市加盟信息.打造国内超市采购商与批发市场供应厂商搭建网上批发市场平台,是全国批发市场行业中电子商务权威性网站。
本文来源： 淘宝电商用户数据分析
