SQL淘宝用户行为分析

一、定义问题

本次分析通过对淘宝用户行为数据进行分析，为以下问题提供解释和改进建议：
1.分析用户消费行为，通过分析常见电商分析指标，确定各个环节的流失率，找到需要改进的环节
2.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律
3.对用户群体进行分群，对不同价值对用户给出不同对营销建议

二、数据理解

建表

CREATE TABLE `tb` (
  `user_id` varchar(7) NOT NULL,
  `item_id` varchar(7) DEFAULT NULL,
  `category_id` varchar(7) DEFAULT NULL,
  `behavior_type` varchar(4) DEFAULT NULL,
  `timestamps` datetime DEFAULT NULL,
  `dates` date DEFAULT NULL,
  `time_hour` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据

LOAD DATA LOCAL INFILE '/Users/terese/Downloads/UserBehavior_million.csv'
INTO TABLE tb
FIELDS TERMINATED BY ',';
--IGNORE 1 LINES;

删除index、修改tmiestamp，index、timestamp是MySQL保留字，为了保证后续操作不报错，删除index列、改tmiestamp为timestamps。

SQL中查询前几条

SELECT * FROM tb LIMIT 5;

三、数据清洗

查看各字段的数据

SELECT
	COUNT( DISTINCT tb.user_id ) 用户数,
	COUNT( DISTINCT tb.item_id ) 商品数,
	COUNT( DISTINCT tb.category_id ) 商品类目数,
	count( tb.behavior_type ) AS 所有行为数 
FROM tb;

本次分析9739名用户共计100万条行为数据，商品数399115，商品类目数5796。

删除重复值

查询重复

SELECT user_id,item_id,category_id,behavior_type,timestamps FROM tb
GROUP BY
	user_id,item_id,category_id,behavior_type,timestamps
HAVING
	COUNT(*)>1;

可以看到无重复值

查询缺失值

（对比共计100万条行为数据）

SELECT 
	COUNT(user_id), COUNT(item_id), COUNT(category_id),
	COUNT(behavior_type), COUNT(timestamps)
FROM tb;

数据全部为100万，所以没有缺失值。

一致化处理

一致化处理就是对数据进行数据抽取，使得数据字段满足基本的数据分析需求。

将时间戳转化为日期并提取年月日和小时

update tb
set timestamps=FROM_UNIXTIME(timestamps);

update tb
set dates=date_format(timestamps,'%Y-%m-%d');

update tb
set time_hour=time_format(timestamps,'%H');

异常值处理

select 
	count(timestamps),
	count(dates),count(dates),count(time_hour) 
from tb;

select dates,count(behavior_type) from tb group by dates;

由于分析的日期在2017-11-25到2017-12-03日的行为数据，其他日期数据不全，应该删除。

delete from tb where dates between '2017-09-11' and '2017-11-24';
delete from tb where dates='2017-12-04';

查看空值

SELECT * FROM tb WHERE user_id IS NULL OR item_id IS NULL OR category_id IS NULL OR behavior_type IS NULL OR timestamps IS NULL OR dates IS NULL OR time_hour IS NULL;

可以看到没有空值

四、构建模型（描述分析）

基于AARRR行为漏斗模型分析用户行为

用户角度

（1）用户总行为漏斗分析

UV、PV、PV/UV

SELECT  COUNT(DISTINCT user_id) uv,
	(SELECT COUNT(*) FROM tb WHERE behavior_type = 'pv') pv,
	(SELECT COUNT(*) FROM tb WHERE behavior_type = 'pv') / COUNT(DISTINCT user_id) as 'pv/uv'
FROM tb;

!

独立访客数（UV）：9739
页面访客数（PV）：895637
人均页面访问数（PV/UV） ：92

跳出率

跳出率=只点击一次浏览的用户数量/总用户访问量

SELECT 
	count(*) as 跳出用户, COUNT(*)/895637 as 跳出率
	FROM
(SELECT 
 	user_id FROM tb
 	GROUP BY user_id
 	HAVING COUNT(behavior_type)=1) as look_once;

跳出率为0%

用户激活转化率

SELECT
	behavior_type, count(*)
FROM
	tb
GROUP BY behavior_type;

用户行为流失率

select 1-((55447+20359)/895637)  流失率1;select 1-(20359/(55447+28088)) 流失率2;select 1-(20359/895637) 流失率3;

浏览详情页——加入购物车+收藏流失率： 91.54%
加入购物车+收藏——购买流失率： 75.63%
浏览详情页——购买流失率：97.73%

用户浏览-购买转化率

select (20359/895637) 用户浏览到购买转化率;

用户浏览-购买转化率为2.27%

（2）独立访客漏斗模型计算

**用户付费转化率 **

SELECT COUNT(DISTINCT user_id) 独立访客数
FROM tb
GROUP BY behavior_type
ORDER BY COUNT(DISTINCT user_id) DESC;

计算得到付费转化率为：

上面是每一步用户行为的独立用户数，可以看到使用淘宝的用户中有68%的付费用户，用户付费转化率相当高。

（3）用户复购率

-- 计算复购人数
select count(*) 复购人数
from 
(select distinct user_id
 from tb
where behavior_type='buy'
group by user_id
having count(behavior_type)>1) as buy2;

SELECT COUNT(DISTINCT user_id) 购买总人数
FROM tb
WHERE behavior_type = 'buy';

复购率达到66.16%，这个比率如果长期达到60%以上，则说明公司进入忠诚度模式，应将经营中心放在客户忠诚度上，即鼓励忠诚客户更加频繁地消费

不同时间尺度下用户行为模式分析

（1）分析九天中每天的用户行为

select dates,
	sum(case when behavior_type = 'pv' THEN 1 ELSE 0 END) pv_count,
	sum(case when behavior_type = 'buy' THEN 1 ELSE 0 END) buy_count,
	sum(case when behavior_type = 'cart' THEN 1 ELSE 0 END) cart_count,
	sum(case when behavior_type = 'fav' THEN 1 ELSE 0 END) fav_count
from tb
group by dates
order by dates;

（2）分析一天内用户每小时的行为

SELECT time_hour as hours,
SUM(case WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) pv_count,
SUM(case WHEN behavior_type = 'buy' THEN 1 ELSE 0 END) buy_count,
sum(case WHEN behavior_type = 'cart' THEN 1 ELSE 0 END) cart_count,
sum(case WHEN behavior_type = 'fav' THEN 1 ELSE 0 END) fav_count
FROM tb
GROUP BY time_hour
ORDER BY time_hour;

不同商品种类的用户行为

（1）热度最高的前20个商品

-- 求商品总数
SELECT COUNT(DISTINCT item_id) 
FROM tb;

商品共计398972种。

-- 求热度最高的前20个商品
SELECT item_id, COUNT(behavior_type)
FROM tb
GROUP BY item_id
ORDER BY COUNT(behavior_type) DESC
limit 20;

（2）商品被浏览、收藏、购买、加入购物车排行榜

# 销量前20位的商品
SELECT item_id,COUNT(*)
FROM tb
WHERE behavior_type = 'buy'
GROUP BY item_id
ORDER BY COUNT(*) desc
LIMIT 20;

可以看到销量前20位的商品

# 浏览前20的商品
SELECT item_id,COUNT(*) 
FROM tb
WHERE behavior_type = 'fav' GROUP BY item_id
ORDER BY COUNT(*) desc 
LIMIT 20;

-- 收藏前20的商品
SELECT item_id,COUNT(*) 
FROM tb
WHERE behavior_type = 'fav' GROUP BY item_id
ORDER BY COUNT(*) desc 
LIMIT 20;

（3）商品复购分析

​ a. 商品复购排行榜

SELECT item_id, COUNT(distinct user_id) buy_times
	FROM tb
	WHERE behavior_type = 'buy'
	GROUP BY item_id 
	order by COUNT(user_id) desc
	limit 20;

可以看到被复购次数最多的商品id是3122135，被一个用户复购了17次。以上是商品复购top20排行榜

b. 商品复购率

-- 统计商品被用户的购买次数
SELECT buy_times,count(item_id)
FROM 
	(SELECT item_id, COUNT(user_id) buy_times
	FROM tb
	WHERE behavior_type = 'buy'
	GROUP BY item_id) as t 
GROUP BY buy_times
ORDER BY COUNT(*) DESC;

# 被购买商品总种数
SELECT COUNT(DISTINCT item_id) 
FROM tb
WHERE behavior_type = 'buy';

select 1-(15536/17565) as 商品复购率;

被用户购买1次的商品有 15536种，被购买商品总种数为17565种，商品复购率为11.55%

五、数据可视化及分析

数据分析报告见：淘宝用户行为分析及用户价值分析报告