SQL淘宝用户数据分析

---

一、项目背景

本项目使用sql和Excel对淘宝用户行为数据进行分析和可视化展示，通过建立用户行为转化漏斗模型、商品销售分析、使用RFM模型对用户分层，找到针对不同商品、用户群体的营销策略。

数据来源及介绍

数据来源:阿里巴巴天池

本数据集(UserBehavior.csv)包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。

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

维度	数量
用户数量	987,994
商品数量	4,162,024
商品类目数量	9,439
所有行为数量	100,150,807

数据集字段含义

列名称	说明
用户ID	字符串类型，用户ID
商品ID	字符串类型，商品ID
商品类目ID	字符串类型，商品所属类目ID
行为类型	字符串，枚举类型，包括('pv’商品详情页pv，等价于点击, 'buy’商品购买, 'cart’加入购物车, 'fav’收藏商品)
时间戳	整型，行为发生的时间戳

二、数据清洗

1. 导入数据

使用Navicat导入数据，并在‘设计表’中修改列名

2. 数据清洗

1. 统计重复值

# 统计重复值
SELECT *FROM userbehavior 
GROUP BY user_id,item,category,time
HAVING count(user_id)>1;

结果显示没有重复值出现。

2. 查看缺失值

# 统计缺失值
SELECT count(user_id),count(item),count(category),count(behavior),count(time)
FROM userbehavior;

所有数据统计为999999，没有缺失值。

3. 时间格式转换

# 新增date、hour时间字段
ALTER TABLE userbehavior ADD date VARCHAR(20),ADD hour VARCHAR(20); 
# 时间格式转换
UPDATE userbehavior SET date = FROM_UNIXTIME(time,"%Y-%m-%d");
UPDATE userbehavior SET hour = FROM_UNIXTIME(time,"%H");
UPDATE userbehavior SET time = FROM_UNIXTIME(time);
# 调整一下time字段数据的样式
UPDATE userbehavior SET time = SUBSTRING_INDEX(time,'.',1);

最终结果：

4. 异常值处理

#筛选异常值
select * from userbehavior 
where date<'2017-11-25' or date>'2017-12-03';
#删除异常值
delete from userbehavior 
where date<'2017-11-25' or date>'2017-12-03';

删除了470个不在这个时间段里的值。

三、数据分析

1. AARRR模型漏斗分析

AARRR模型，又称海盗模型，代表的是Acquisition、Active、Retain、Revenue、Refer五个阶段，简称为拉新、促活、留存、创收和分享。

因为数据集限制，这里只能分析促活、留存、创收三个阶段。

1.1 活跃度分析(Active )

#11/25-12/3时间段总活跃度分析
SELECT count(DISTINCT user_id) as UV,
	   sum(case when behavior='pv' then 1 else 0 end) as PV,
	   sum(case when behavior='pv' then 1 else 0 end)/count(DISTINCT user_id) as 'PV/UV'
FROM userbehavior;

#日活跃度分析
SELECT date, count(DISTINCT user_id) as UV,
	   sum(case when behavior='pv' then 1 else 0 end) as PV,
	   sum(case when behavior='pv' then 1 else 0 end)/count(DISTINCT user_id) as 'PV/UV'
FROM userbehavior
group by date;
#每小时活跃度分析
SELECT `hour`, count(DISTINCT user_id) as UV,
	   sum(case when behavior='pv' then 1 else 0 end) as PV,
	   sum(case when behavior='pv' then 1 else 0 end)/count(DISTINCT user_id) as 'PV/UV'
FROM userbehavior
group by `hour`;

• 在这9天里，访问用户总数（UV）是9739；页面总访问量（PV）为895636
  9天时间内平均每人页面访问量（UV/PV）：约为92次
• 从折线图可以看出，在12月2日到12月3日（周末）的访问量有提升，每日平均每人页面访问量（UV/PV）为13次。
• 从每日时段上看，晚上9点-11点是访问量最高的时候。

1.2 留存（Retain）

1）用户行为转化漏斗分析

数据集包含点击、加入购物车、收藏和购买四个行为，收藏并不是购买流程的必须环节，所以购买流程可以分为“点击详情页-加入购物车-下单购买” 三个环节。

# 用户行为漏斗
SELECT behavior,COUNT(*) as 总行为数,count(DISTINCT user_id) as 用户数
FROM userbehavior
GROUP BY behavior
order by behavior desc;

从两个漏斗分析中可以发现：

• 从总行为转化漏斗看，pv-cart 的转化率只有6.19%，说明用户在加购之前花了很多时间在点击商品页上。
• 从用户转化漏斗上看，从浏览商品页面到加入购物车，转化率为75.45%；最终购买用户转化率是68.92%。
• 可能需要优化商品推荐和匹配的功能，减少用户的在点击浏览上花费的时间。

2）用户流失情况分析

下面进一步分析每个环节的用户流失情况。

create view 流失分析表
as
SELECT user_id,
	   sum(case when behavior='pv' then 1 else 0 end) as PV,
	   sum(case when behavior='buy' then 1 else 0 end) as Buy,
	   sum(case when behavior='cart' then 1 else 0 end) as Cart,
	   sum(case when behavior='fav' then 1 else 0 end) as Fav
FROM userbehavior
GROUP BY user_id;

用户的流失，即没有任何购买行为（Buy=0），存在以下情况：
1）用户点击后就流失了(PV>0; Buy=0; Cart=0; Fav=0)
2）用户在点击-收藏环节流失(PV>0; Buy=0; Cart=0;Fav>0)；
3）用户在点击-加购物车流失(PV>0; Buy=0; Cart>0;Fav=0)；
4）用户在点击-收藏-加购物车后流失(PV>0; Buy=0; Cart>0;Fav>0)。

select count(*) as '点击后流失'
from 流失分析表
where fav=0 and cart=0 and buy=0;

select count(*) as '点击-收藏流失'
from 流失分析表
where fav>0 and (cart=0 and buy=0);

select count(*) as '点击-加购流失'
from 流失分析表
where (fav=0 and buy=0) and cart>0;

select count(*) as '点击-收藏-加购流失'
from 流失分析表
where (fav>0 and cart>0) and buy=0;

• 有将近一半的流失用户（45%），在将商品加入购物车后就流失掉了；
• 其次是点击-收藏-加购物车后流失的用户，占比21%。
• 可以进一步研究回访用户为什么最后放弃购买？以及用户的每个环节的决策时间，用户从点击到加入购物车/收藏最后到购买支付，每个环节需要考虑的时间间隔是多少？

1.3 创收（Revenue）

由于数据缺少金额相关数据，主要从客户购买次数和购买率方面分析。

SELECT
	a.date,
	每日购买数,每日购买用户数,每日访问用户数,
	每日购买用户数 /每日访问用户数 AS 每日购买率 
FROM
	( SELECT date, count( DISTINCT user_id ) AS '每日购买用户数' FROM userbehavior WHERE behavior = 'buy' GROUP BY date ) AS a
	LEFT JOIN (
	SELECT
		date,
		count( DISTINCT user_id ) AS '每日访问用户数',sum( CASE WHEN behavior = 'buy' THEN 1 ELSE 0 END ) AS '每日购买数' 
	FROM
		userbehavior 
	GROUP BY
	date 
	) AS b ON a.date = b.date;

• 每日购买数>每日购买用户数：表明有的用户同一天购买了一次以上。
• 每日购买率在18%-20%之间。11月27日到12月1日稳定在20%左右（周一至周五），自12月2日-12月3日（周六日）由于访问量上涨，购买率明显下降到18%附近。
• 数据表明，周末期间用户访问次数较多，但购买率相对下降，可以多做直播活动、投放新产品广告等。

进一步分析用户的复购情况：

# 复购率
SELECT 
	sum(case when buy_amount>1 then 1 else 0 end) as "复购总人数",
	count(user_id) as "购买总人数",
	sum(case when buy_amount>1 then 1 else 0 end)/count(user_id) as "复购率"
FROM
	(SELECT user_id,count(behavior) as buy_amount
	FROM userbehavior
	WHERE behavior = 'buy'
	GROUP BY user_id) a;

在9日内，有66.21%的用户再次购买，复购率较高。

---

2. 商品分析

根据客户行为分类，分析客户购买次数最多、收藏次数最多、点击次最多的前十个商品。

2.1 单个商品分析

#销量前十
SELECT item, count(behavior) as '购买次数'
FROM userbehavior
WHERE behavior='buy'
GROUP BY item 
ORDER BY count(behavior) DESC
LIMIT 10;

#点击前十
SELECT item, count(behavior) as '点击次数'
FROM userbehavior
WHERE behavior='pv'
GROUP BY item 
ORDER BY count(behavior) DESC
limit 10;

#收藏前十
SELECT item, count(behavior) as '收藏次数'
FROM userbehavior
WHERE behavior='fav'
GROUP BY item 
ORDER BY count(behavior) DESC
limit 10;

• 购买次数最多的商品 也只卖出了17次。表明商店并没有爆款商品，主要是依靠商品种类多累计销量。可以考虑推出爆款商品来带动整体销量。
• 分析表明销量前十、收藏前十、点击前十的商品，没有相关重合的商品。

查看每个前十商品的销售、点击、收藏情况：

select a.item, 购买次数,点击次数,收藏次数 FROM
(SELECT item, count(behavior) as '购买次数'
FROM userbehavior
WHERE behavior='buy'
GROUP BY item 
ORDER BY count(behavior) DESC
LIMIT 10) as a 
LEFT JOIN 
(SELECT item, 
				sum(case when behavior='pv' then 1 else 0 end) as '点击次数',
				sum(case when behavior='fav' then 1 else 0 end) as '收藏次数'
FROM userbehavior
GROUP BY item) as b on a.item=b.item;

销量前十	点击前十	收藏前十
点击和收藏次数相对较少	收藏量相对有增加 ，但是购买次数很少	点击次数也相应比较高，但是购买次数很少

• 可以看出点击量和收藏量关联性比较高，需要重点分析这类高点击、高收藏的商品销量低的原因。

2.2 商品类别分析

按商品类别分析前十个种类。

#销量前十
SELECT category, count(behavior) as '购买次数'
FROM userbehavior
WHERE behavior='buy'
GROUP BY category
ORDER BY count(behavior) DESC
LIMIT 10;

#点击前十
SELECT category, count(behavior) as '点击次数'
FROM userbehavior
WHERE behavior='pv'
GROUP BY category
ORDER BY count(behavior) DESC
limit 10;

#收藏前十
SELECT category, count(behavior) as '收藏次数'
FROM userbehavior
WHERE behavior='fav'
GROUP BY category 
ORDER BY count(behavior) DESC
limit 10;

#销量前十的类别
select a.category, 购买次数,收藏次数,点击次数
from(
SELECT category, count(behavior) as '购买次数'
FROM userbehavior
WHERE behavior='buy'
GROUP BY category
ORDER BY count(behavior) DESC
LIMIT 10) as a 
left join
(SELECT category, 
				sum(case when behavior='fav' then 1 else 0 end) as '收藏次数',
				sum(case when behavior='pv' then 1 else 0 end) as '点击次数'
FROM userbehavior
GROUP BY category) as b on a.category=b.category;

• 商品类别分析结果，再次强调了高点击、高收藏的商品，转化率很低的问题。
• 销量最高的两类商品2735466和1464116在点击和收藏前十以外， 这部分商品可能是固定用户群体在购买，可以收集用户信息进行研究， 将商品推荐给符合这类用户特征的新客户。

3. RFM模型分析

RFM模型是客户关系管理（CRM）中被广泛使用，是衡量客户价值的重要工具。通过客户的近期交易行为、交易频率和交易金额三项指标，分析描述客户的价值，并且将客户划分为八类：

RFM知识点：http://www.woshipm.com/pd/2209492.html

R维度（Recency): 计算最近的一次消费时间距离2017年12月3日有多久。消费间隔越小，表示R值越小，价值越高。

F维度(Frequency)：消费频率，在这个时间段里，用户消费的次数。

create view RFM分析
as
select user_id,
DATEDIFF('2017-12-03',max(date)) as R,
sum(case when behavior='buy' then 1 else 0 end) as F
from userbehavior 
where behavior='buy'
group by user_id;

#查看F和R值范围
select DISTINCT R from rfm分析 order by R DESC;
select DISTINCT F from rfm分析 order by F DESC;

#统计R、F值
select R, count(R) from rfm分析 
group by R 
order by R DESC;

select F, count(F) from rfm分析 
group by F
order by F DESC;

• 从R值分析中可以发现，57%的用户在2天内会再次购买，20%的用户在第3、4天回来购物；用户的重复购买的情况比较多。
• 58%的用户在9天内来消费了1-2次，16%的用户9天内来了3次，
• 还有的用户在9天内来了30次以上。

R值在0-8之间；F值在1-72之间。下面将R和F值分成5组，分别给1-5分。

R	F	分数
0-1天	30次以上	5
1-2天	20-30次	4
3-4天	10-20次	3
5-6天	1-10次	2
7-8天	0次	1

create view rfm得分
as
select user_id,R,
(case when R between 0 and 1 then 1
			when R between 1 and 2 then 2
			when R between 3 and 4 then 3
			when R between 5 and 6 then 4
			when R between 7 and 8 then 5
else 0 end) as R_score,F,
(case when F =0 then 1
			when F between 1 and 10 then 2
			when F between 10 and 20 then 3
			when F between 20 and 30 then 4
			when F >30 then 5
else 0 end) as F_score
from rfm分析

#计算平均分
select avg(R_score) as R平均分,avg(F_score) as F平均分
from rfm得分;

#将平均分结果用来客户分层
select 用户分类,count(user_id) as '用户数量'
from (
select user_id,
(case when R_score>2.2836 and F_score>2.0306 then "重要价值客户"
when R_score>2.2836 and F_score<2.0306 then "重要发展客户"
when R_score<2.2836 and F_score>2.0306 then "重要保持客户"
when R_score<2.2836 and F_score<2.0306 then "重要挽留客户"
end) as '用户分类'
from rfm得分)as b
group by 用户分类;

结果分析：

• 重要挽回客户的占比最高（55%），这部分客户消费时间较远，消费频次低，将要流失或者已经流失。可以进行跟踪回访，了解用户流失的原因（产品/流程），再针对性地改进以减少流失。
• 重要发展客户（42%）消费时间比较近，但是消费频次不高。针对这个用户群体可以定期上新提醒、促销活动等方法，以提高客户的消费次数。
• 重要保持客户（2%），这部分客户消费频次比较高，但是距离上一次消费时间较长，说明这是个一段时间没来的忠诚客户，我们需要主动和他保持联系。可适当发送促销活动提醒，发放优惠券的方式召回，刺激再次消费。
• 重要价值客户（1%），最近消费时间近、消费频次很高，是VIP客户。最好能够提供会员服务和优惠，比如会员礼品、生日优惠等定期维护客户关系。

总结

项目从三个维度分析了淘宝用户的消费行为：用户的行为分析、商品分析和RFM模型分析。

1） 用户的行为分析

• 用户活跃度：周末的访问量在一周时间里面最高；从每日时段上看，晚上9点-11点访问量最高。
  建议： 店铺的促销、直播活动可以选择在这个时间段，并且适当在周末延长直播时间。

• 留存情况分析：

  • 从行为转化漏斗分析可见，用户在加购之前花了很多时间在点击商品页上。
    建议： 优化商品推荐和匹配的功能、优化商品页界面，减少用户的在点击浏览上花费的时间。
  • 用户流失情况分析：45%的用户在将商品加入购物车后就流失掉了；其次是点击-收藏-加购物车后流失的用户，占比21%。
    建议： 可以进一步研究回访用户为什么最后放弃购买？以及用户的每个环节的决策时间，用户从点击到加入购物车/收藏最后到购买支付，每个环节需要考虑的时间间隔是多少？

• 创收情况分析：
  在9日内，有66.21%的用户再次购买，复购率较高；周末期间用户访问次数较多，但购买率相对下降。
  建议： 可以多做直播活动、投放新产品广告、优惠促销等，提高用户复购率和周末的购买率。

2）商品分析：

• 没有爆款商品，主要是依靠各类商品累计销量； 同时，很多商品“高点击高收藏”但是“低购买”，表明产品对用户是有吸引力的，但是因为未知原因用户没有购买。
  建议： 进一步研究调查这类“双高”商品销量低的原因，再针对性改进。比如可能是价格太高，就可以捆绑销售、送优惠券等，将这类商品打造成爆款产品，带动整体销量。
• 销量最高的两类商品2735466和1464116在点击和收藏前十以外， 这部分商品可能是固定用户群体在购买。
  ***建议：**可以收集用户信息进行研究， 将商品推荐给符合这类用户特征的新客户。

3）RFM分析：

• 主要用户为挽回客户和发展客户，表明整体客户忠诚度不高，缺少客户粘度。
  建议： 进行跟踪回访，了解用户流失的原因（产品/流程），再针对性地改进以减少流失。同时定期上新提醒、促销活动等方法，以提高客户的消费频次、挽回客户。