用户行为分析

发表于 | 更新于:
字数统计: 3.1k 字 | 阅读时长 ≈ 13 分钟

提出问题

  1. 探索用户行为的规律
  2. 找到高销量的商品和商品类目以及用户
  3. 用户价值分析

理解数据

1.数据来源：阿里云天池数据集

本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为

数据集具体情况为：用户数量约100万（987,994），商品数量约410万（4,162,024），商品类目数量约1万（9,439），用户行为数量约为1亿条（100,150,807）

数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成
用户行为类型共四种：点击（pv），加入购物车（cart），收藏（fav），购买（buy）

2.字段含义
关于数据集中每一列的详细描述如下：

列名称 说明
用户ID 整数类型，序列化后的用户ID
商品ID 整数类型，序列化后的商品ID
商品类目ID 整数类型，序列化后的商品所属类目ID
行为类型 字符串，枚举类型，包括(‘pv’, ‘buy’, ‘cart’, ‘fav’)
时间戳 行为发生的时间戳
字段 中文说明 数据类型 字段说明
user_id 用户ID INT 用户标识
item_id 商品ID INT 商品标识
category_id 商品类目ID INT 商品类目标识
behavior_type 用户行为模式 VARCHAR 四种类型：pv,cart,fav,buy
times_tamp 时间戳 INT 行为发生的时间戳

取样

1.创建表格，导入100万数据

1
2
3
4
5
6
7
8
CREATE TABLE u_behavior(
user_id INT NOT NULL,
item_id INT NOT NULL,
category_id INT NOT NULL,
behavior_type VARCHAR(50) NOT NULL,
time_stamp INT NOT NULL,
primary key(user_id, item_id, time_stamp)
);

从数据集随机抽取100万条数据插入UBehavior表

1
2
-- 随机抽取100万条行为数据并插入表u_behavior
INSERT INTO u_behavior SELECT * FROM Userbehavior WHERE time_stamp >=((SELECT MAX(time_stamp) FROM Userbehavior) - (SELECT MIN(time_stamp) FROM Userbehavior)) * RAND() + (SELECT MIN(time_stamp) FROM Userbehavior) LIMIT 1000000;

用户数量约1.1万（11,334），商品数量约40万（405,309），商品类目数量约6千（5836）

1
2
-- 查询表u_behavior各字段量级
SELECT COUNT(DISTINCT(user_id)) user_id, COUNT(DISTINCT(item_id)) item_id, COUNT(DISTINCT(category_id)) category_id, COUNT(behavior_type) behavior_type, COUNT(time_stamp) time_stamp FROM u_behavior;

数据清洗

1.检查重复数据

因为用了复合主键（user_id，item_id，times_tamp）保证唯一性，数据导入后没有重复数据。

1
2
3
4
-- 检查个字段是否有重复值
SELECT user_id, item_id, category_id, behavior_type, time_stamp FROM u_behavior
GROUP BY user_id, item_id, category_id, behavior_type, time_stamp
HAVING COUNT(*) > 1;

2.检查是否有缺失值

1
SELECT COUNT(user_id) user_id, COUNT(item_id) item_id,COUNT(category_id) category_id, COUNT(behavior_type) behavior_type, COUNT(time_stamp) time_stamp FROM u_behavior;

前面创建表已经约束字段NOT NULL可以看到没有缺失

3.格式一致化处理
将时间戳转换

1
2
3
4
5
6
7
8
ALTER TABLE u_behavior ADD b_datetime timestamp NOT NULL;
UPDATE u_behavior SET b_datetime = FROM_UNIXTIME(time_stamp);

ALTER TABLE u_behavior ADD b_date char(10) NOT NULL;
UPDATE u_behavior SET b_date = SUBSTRING(b_datetime, 1, 10);

ALTER TABLE u_behavior ADD b_time char(8) NOT NULL;
UPDATE u_behavior SET b_time = SUBSTRING(b_datetime, 12, 8)

查看一下数据情况

1
SELECT * FROM u_behavior LIMIT 100;

异常值处理

数据集日期是2017年11月25日至2017年12月3日

1
2
-- 查看是否有异常值
SELECT MAX(b_datetime), MIN(b_datetime) FROM u_behavior;

明显有异常值，将异常值删除

1
2
3
DELETE FROM u_behavior
WHERE b_datetime < '2017-11-25 00:00:00'
OR b_datetime > '2017-12-04 00:00:00';

删除了479条异常

流量指标

计算基本的流量指标：独立访客数UV，访问量PV，平均访问量PV/UV

1
2
3
4
5
-- 独立访客数UV，访问量PV，平均访问量PV/UV
SELECT COUNT(DISTINCT user_id) AS 'UV',
(SELECT COUNT(*) FROM u_behavior WHERE behavior_type = 'pv') AS 'PV',
(SELECT COUNT(*) FROM u_behavior WHERE behavior_type = 'pv') / (SELECT COUNT(DISTINCT user_id)) AS 'PV/UV'
FROM u_behavior;

总体数据：独立访客数11334，访问量896540，平均访问量79.1018

绘制行为类型和访客数的占比图

1
2
3
4
5
6
7
8
9
10
11
-- 绘制行为类型和访客数的漏斗图
SELECT behavior_type AS '行为类型',
CONCAT(ROUND(COUNT(behavior_type) * 100/896540), '%') AS '行为占比'
FROM u_behavior
GROUP BY behavior_type


SELECT behavior_type AS '行为类型',
CONCAT(ROUND(COUNT(DISTINCT user_id)* 100/11334), '%') AS '访客占比'
FROM u_behavior
GROUP BY behavior_type

用户行为包括点击、加入购物车、收藏和购买，根据计算结果和漏斗图可知：
1） 从用户点击占比100%，加入购物车行为占比6%，收藏行为占比3%，购买行为占比2%
其中从点击行为到加入购物车，转化率骤降94%，可以确认加入购物车环节是夹点位置，该环节较低的转化率影响最后的成交率

2） 从点击行为占比100%，加入购物车行为占比73%，收藏行为占比38%，购买行为占比65%，
其中收藏转化率偏低，但购买转化率很高，购买用户占比高达65%。
可以看出大部分用户习惯浏览后直接购买，（但是也有可能是时间周期不够长，一些收藏的商品或加入购物车的商品来不及转化为消费，还是需要一段时间才可以看出来）

探索用户行为规律，为改善用户体验提供数据依据

计算用户的日活量

1
2
3
4
-- 每日活跃用户
SELECT b_date, COUNT(DISTINCT user_id) AS '日活跃用户'
FROM u_behavior
GROUP BY b_date

很明显12月2日、3日用户活跃数涨幅明显，这两日是周六日

对比上周末平均活跃8036，本周末活跃10893.5，增长了35.56%

上周末平均 8036
本周末平均 10893.5
上涨 35.56%

推测：平台是否在做什么活动？

可能12月临近“双12”活动，很多商家在12月1号开始做“双12”的活动预热，从而吸引大量用户活跃起来

计算用户行为日活跃量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 计算用户行为日活量
-- 点击
SELECT b_date, COUNT(behavior_type)
FROM u_behavior
WHERE behavior_type = 'pv'
GROUP BY b_date
ORDER BY b_date;

-- 加入购物车
SELECT b_date, COUNT(behavior_type)
FROM u_behavior
WHERE behavior_type = 'cart'
GROUP BY b_date
ORDER BY b_date;

-- 收藏
SELECT b_date, COUNT(behavior_type)
FROM u_behavior
WHERE behavior_type = 'fav'
GROUP BY b_date
ORDER BY b_date;

-- 购买
SELECT b_date, COUNT(behavior_type)
FROM u_behavior
WHERE behavior_type = 'buy'
GROUP BY b_date
ORDER BY b_date;

计算本周末和上周末的行为增幅对比

b_date pv cary fav buy
上周末平均值 94848.5 5728.5 2906.5 2030
本周末平均值 122981.5 7737.5 3808 2559.5
对比上涨 22.88% 25.96% 23.67% 20.69%

计算用户行为时活跃量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 点击
SELECT b_hour, COUNT(behavior_type)
FROM u_behavior
WHERE behavior_type = 'pv'
GROUP BY b_hour
ORDER BY b_hour;

-- 加入购物车
SELECT b_hour, COUNT(behavior_type)
FROM u_behavior
WHERE behavior_type = 'cart'
GROUP BY b_hour
ORDER BY b_hour;

-- 收藏
SELECT b_hour, COUNT(behavior_type)
FROM u_behavior
WHERE behavior_type = 'fav'
GROUP BY b_hour
ORDER BY b_hour;

-- 购买
SELECT b_hour, COUNT(behavior_type)
FROM u_behavior
WHERE behavior_type = 'buy'
GROUP BY b_hour
ORDER BY b_hour;

可以看见

1）四种行为时活跃有两个明显的增长区间为：6点—10点，18点—22点

2）10点—17点是一个相对平缓的增长区间

3）pv,cart,fav,buy时活量，从晚上18点开始迅速上升，在晚上22点达到了一天的峰值

4）18点—22点对比6点—10点的四种行为时活跃增幅分别为114.28% 、109.10% 、90.55% 、81.73%

pv cart fav buy
18-22均值 62725.6 3806.8 1811 1237.2
6-10均值 29272.4 1820.6 950.4 680.8
增幅 114.28% 109.10% 90.55% 81.73%

探索高销量的商品和商品类目

探索销量TOP10商品和商品类目

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- TOP10销量的商品
SELECT item_id, COUNT(*) AS '商品购买次数'
FROM u_behavior
WHERE behavior_type='buy'
GROUP BY item_id, category_id
ORDER BY 商品购买次数 DESC
LIMIT 10;


-- TOP10销量的商品类目
SELECT
category_id, COUNT(*) AS '商品类目购买次数'
FROM u_behavior
WHERE behavior_type='buy'
GROUP BY category_id
ORDER BY 商品类目购买次数 DESC
LIMIT 10;

找出核心付费用户，统计核心付费用户的购买商品类目信息，为其提供定制化的服务

找出购买次数最多的TOP10用户(原数据集没有成交金额之类的数据，于是用购买次数的多少来标的核心付费用户。)

1
2
3
4
5
6
SELECT user_id,COUNT(*) AS '购买次数'
FROM u_behavior
WHERE behavior_type='buy'
GROUP BY user_id
ORDER BY 购买次数 DESC
LIMIT 10;

计算购买最多的user_id为107932的TOP商品类目占比

1
2
3
4
5
6
SELECT category_id, CONCAT(ROUND(COUNT(*)*100/67),'%') AS '购买占比'
FROM u_behavior
WHERE behavior_type='buy' AND user_id=107932
GROUP BY category_id
ORDER BY 购买占比 DESC
LIMIT 10;

RFM 分析

  • R（Recency）：客户最近一次交易时间的间隔。R值越大，表示客户交易发生的日期越久，反之则表示客户交易发生的日期越近。
  • F（Frequency）：客户在最近一段时间内交易的次数。F值越大，表示客户交易越频繁，反之则表示客户交易不够活跃。
  • M（Monetary）：客户在最近一段时间内交易的金额。M值越大，表示客户价值越高，反之则表示客户价值越低。

由于数据集没有金额，这里只对Recency和Frequency进行分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- RFM分析
-- 先构建视图
CREATE VIEW recency
AS
SELECT d.recency, COUNT(user_id)

FROM (SELECT a.user_id, DATEDIFF(last_purchase, 2nd_last_purchase) AS recency
-- 最大日期
FROM (SELECT user_id, max(b_date) AS last_purchase
FROM u_behavior
GROUP BY user_id) AS a

RIGHT JOIN

-- 第二大日期
(SELECT user_id, max(b_date) AS 2nd_last_purchase
FROM u_behavior
WHERE b_date NOT IN(SELECT max(b_date) AS last_purchase FROM u_behavior GROUP BY user_id)
GROUP BY user_id) AS b

ON a.user_id=b.user_id) AS d

GROUP BY d.recency
ORDER BY d.recency

-- 查询视图
SELECT * FROM recency

分析表明很多人不会在购物完马上选择再进行购物，购买后经过两三天的快递，体验两三天后再进行复购。第七天的复购对比其他时间尤为突出。

所以将评分系统分为

rencency score
4~5天 3
6~7天 2
8天以上 1

查看用户消费时间间隔和评分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT user_id, recency,(
CASE WHEN recency=4 then 3
WHEN recency = 5 then 3
WHEN recency = 6 then 2
WHEN recency = 7 then 2
WHEN recency = 8 then 1
END) AS rscore
FROM

(SELECT a.user_id, DATEDIFF(last_purchase, 2nd_last_purchase) AS recency
-- 最大日期
FROM (SELECT user_id, max(b_date) AS last_purchase
FROM u_behavior
GROUP BY user_id) AS a

RIGHT JOIN

-- 第二大日期
(SELECT user_id, max(b_date) AS 2nd_last_purchase
FROM u_behavior
WHERE b_date NOT IN(SELECT max(b_date) AS last_purchase FROM u_behavior GROUP BY user_id)
GROUP BY user_id) AS b

ON a.user_id=b.user_id) AS C

frequency作为消费频率是顾客在限定的期间内所购买的次数，就是常客，也是对产品的粘性比较高，忠诚度也高

1
2
3
4
5
6
7
8
9
10
-- frequency:客户在最近一段时间内交易的次数
SELECT frequency, COUNT(user_id)
FROM
(SELECT user_id, SUM(CASE
WHEN behavior_type='buy' THEN 1 ELSE 0 END
) AS frequency
FROM u_behavior
GROUP BY user_id) AS a
GROUP BY frequency
ORDER BY frequency

将frequency分为三个层次，分别对应的分数如下图：

frequecy score
少于3次 1
4~7次 2
8次以上 3 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 建立视图查询并计算用户交易次数，建立对应的评分
CREATE VIEW fscore
AS
SELECT frequency,
COUNT(user_id),
(CASE WHEN frequency <=3 then 1
WHEN frequency =4 then 2
WHEN frequency =5 then 2
WHEN frequency =6 then 2
WHEN frequency =7 then 2
WHEN frequency >=8 then 3 end) AS fscore
FROM
(SELECT user_id, SUM(CASE
WHEN behavior_type='buy' THEN 1 ELSE 0 END) AS frequency
FROM u_behavior
GROUP BY user_id) AS a
GROUP BY frequency
ORDER BY frequency

根据recency和frequency 的分数，把用户分为四种类型的客户

R F 用户分层
高 高 重要价值客户
高 低 重要保持客户
低 高 重要发展客户
高 低 重要挽留客户

对用户分类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- 建立用户价值uvalue(User value)

CREATE VIEW uvalue
AS
SELECT f.user_id,fscore,rscore,
(CASE WHEN fscore >1 AND r.rscore=3 THEN '重要价值客户'
WHEN fscore =1 AND r.rscore =3 THEN '重要保持客户'
WHEN fscore >1 AND r.rscore <3 THEN '重要发展客户'
WHEN fscore =1 AND r.rscore <3 THEN '重要挽留客户'
END) AS '客户价值体系'

FROM
-- 这个是客户在最近一段时间内交易的次数评分
(SELECT user_id,frequency,
(CASE WHEN frequency <=3 then 1
WHEN frequency =4 then 2
WHEN frequency =5 then 2
WHEN frequency =6 then 2
WHEN frequency =7 then 2
WHEN frequency >=8 then 3 end) AS fscore
FROM
(SELECT user_id, SUM(CASE
WHEN behavior_type='buy' THEN 1 ELSE 0 END) AS frequency
FROM u_behavior
GROUP BY user_id) AS a
GROUP BY user_id
ORDER BY user_id) AS f

JOIN
-- 这个是客户最近一次交易时间的间隔评分
(SELECT user_id, recency,(
CASE WHEN recency=4 then 3
WHEN recency = 5 then 3
WHEN recency = 6 then 2
WHEN recency = 7 then 2
WHEN recency = 8 then 1
END) AS rscore
FROM

(SELECT a.user_id, DATEDIFF(last_purchase, 2nd_last_purchase) AS recency
-- 最大日期
FROM (SELECT user_id, max(b_date) AS last_purchase
FROM u_behavior
GROUP BY user_id) AS a

RIGHT JOIN

-- 第二大日期
(SELECT user_id, max(b_date) AS 2nd_last_purchase
FROM u_behavior
WHERE b_date NOT IN(SELECT max(b_date) AS last_purchase FROM u_behavior GROUP BY user_id)
GROUP BY user_id) AS b

ON a.user_id=b.user_id)AS C)AS r
ON f.user_id = r.user_id

对客户价值体系分类汇总

可以看到客户主要分为两类：重要挽留客户和重要发展客户
可能是因为淘宝的客户基数比较大，重要价值客户较少
接下来可以放在重要发展用客户的购买频率，针对这部分客户定制对应的策略或活动来提高他们的购买欲，推荐他们购物车或者收藏中的同类商品，提高他们对应的浏览量和销售的转化率等

0%