SQL用户购物行为数据分析

最新推荐文章于 2025-04-25 16:12:41 发布

Loninee

最新推荐文章于 2025-04-25 16:12:41 发布

阅读量956

点赞数 26

文章标签：数据库信息可视化 tableau sql

本文链接：https://blog.youkuaiyun.com/lonine/article/details/147015685

版权

1. 数据说明

数据来源：【淘宝用户购物行为数据集_数据集-阿里云天池】

2. 导入数据

从原始数据集导入100000条数据到Navicat新建数据库“UserBehavior_100K”中

把原来文件长度裁剪到100k行即可，再导入sql中

通过“设计表”修改字段名

字段描述

查看表的基本数据


SELECT * FROM UserBehavior_100K;

3. 数据处理

3.1 删除重复值

查看是否有记录出现多于1次的情况

SELECT * FROM UserBehavior_100K
GROUP BY User_ID,Item_ID,Category_ID,Behavior,Time_info
HAVING COUNT(*)>1;

没有重复值

3.2 缺失值处理

查看每一列的总数

发现也没有缺失值

3.3 分列处理

利用FROM_UNIXTIME( )函数将时间戳信息Time_info转换为日期和时间
添加新列Date_Time，记录日期和时间

ALTER TABLE UserBehavior_100K
ADD Date_time DATETIME NULL;
UPDATE UserBehavior_100K
SET Date_time = DATEADD(SECOND, Time_info, '1970-01-01');
#如果失败了可以分开两条两条执行，也就是先单独添加Date_time

添加新列Date和Time，记录日期和时间段

ALTER TABLE UserBehavior_100K
ADD Date DATE NULL,   -- 用于存储日期部分
    Time TIME NULL;   -- 用于存储时间部分

UPDATE UserBehavior_100K
SET 
    Date = CAST(Date_time AS DATE),  -- 提取日期部分
    Time = CAST(Date_time AS TIME);  -- 提取时间部分

SELECT TOP 10 Date_time, Date, Time
FROM UserBehavior_100K;

3.4 异常值处理

查看是否有不属于2017年的11.25到12.03之间的数据

SELECT MAX(Date),MIN(Date)
FROM UserBehavior_100K;

将不符合条件的数据删除

有1566行数据被删除

4. 数据分析

分析目的

- 了解用户的生命周期情况和行为偏好，从而做到更精准化的推荐
- 分析如何更好的提高购买用户比例
- 通过RFM模型分析出有价值的客户并提出相应的建议

分析方法

- 利用SQL分析数据，获得分析结果并导出至Excel，利用Tableau实现数据可视化

分析思路

4.1 数据总览

（1）数据整体情况

SELECT COUNT(DISTINCT User_ID) AS 用户数,
COUNT(DISTINCT Item_ID) AS 商品总数,
COUNT(DISTINCT Category_ID) AS 商品种类数,
COUNT(Behavior) AS 行为总数
FROM userbehavior;

2）用户行为整体情况

创建行为数据视图并按每个用户的行为数量倒序排列

CREATE VIEW 用户行为数据 AS
SELECT 
    User_ID,
    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_100K
GROUP BY User_ID;

4.2 用户生命周期分析

（1）用户获取情况

通过查询日新增用户数分析用户的获取情况

CREATE VIEW 分组 AS
SELECT User_ID, MIN(Date) AS fd
FROM UserBehavior_100K
GROUP BY User_ID;
SELECT fd, COUNT(User_ID)
FROM 分组
GROUP BY fd;

11.25 日用户新增数量最多，推测有新的促销活动吸引了新用户
整体用户增加数都处于较低的状态，推测目前的推广渠道效果一般，应该制定更加有效的策略

（2）用户活跃情况

通过计算UV、PV等指标和日变化趋势查看用户的活跃情况

① 总体独立访客数UV、点击数PV、人均浏览次数、成交量

人均浏览次数 = 点击数 / 独立访客数

SELECT 
    COUNT(DISTINCT User_ID) AS 独立访客数,
    SUM(CASE WHEN Behavior = 'pv' THEN 1 ELSE 0 END) AS 点击数,
    SUM(CASE WHEN Behavior = 'pv' THEN 1 ELSE 0 END) / COUNT(DISTINCT User_ID) AS 人均浏览次数,
    SUM(CASE WHEN Behavior = 'buy' THEN 1 ELSE 0 END) AS 成交量
FROM UserBehavior_100K;

② 日活跃情况

SELECT 
    Date,
    COUNT(DISTINCT User_ID) AS 日独立访客数,
    SUM(CASE WHEN Behavior = 'pv' THEN 1 ELSE 0 END) AS 日点击数,
    SUM(CASE WHEN Behavior = 'pv' THEN 1 ELSE 0 END) / COUNT(DISTINCT User_ID) AS 日人均浏览次数,
    SUM(CASE WHEN Behavior = 'buy' THEN 1 ELSE 0 END) AS 日成交量
FROM UserBehavior_100K
GROUP BY Date;

右键导出并连接到tableau

日独立访客数和日点击数的增长趋势基本相同，在11.27和11.30号有下降的趋势
日成交量在11.27和11.30号呈现增长的趋势，推测用户在此期间直接购买商品的行为增加

3）用户留存情况

① 用户次日、3日、5日、7日留存人数

第一天（17-11-25）活跃人数

SELECT COUNT(DISTINCT User_ID) AS 第一天活跃人数
INTO retention
FROM UserBehavior_100K
WHERE Date = '2017-11-25';

ALTER TABLE retention 
ADD 第二天留存人数 INT;

ALTER TABLE retention
ADD 第三天留存人数 INT,
    第五天留存人数 INT,
    第七天留存人数 INT;

INSERT INTO retention (第一天活跃人数)
SELECT COUNT(DISTINCT User_ID) AS 第一天活跃人数
FROM UserBehavior_100K
WHERE Date = '2017-11-25';

UPDATE retention
SET 第二天留存人数 = (
    SELECT COUNT(DISTINCT User_ID)
    FROM UserBehavior
    WHERE Date = '2017-11-26'
    AND User_ID IN (
        SELECT DISTINCT User_ID
        FROM UserBehavior
        WHERE Date = '2017-11-25'
    )
);

UPDATE retention
SET 第三天留存人数 = (
    SELECT COUNT(DISTINCT User_ID)
    FROM UserBehavior_100K
    WHERE Date = '2017-11-27'  -- 第三天的日期
    AND User_ID IN (
        SELECT DISTINCT User_ID
        FROM UserBehavior_100K
        WHERE Date = '2017-11-25'  -- 第一日的日期
    )
);
UPDATE retention
SET 第五天留存人数 = (
    SELECT COUNT(DISTINCT User_ID)
    FROM UserBehavior_100K
    WHERE Date = '2017-11-29'  -- 第五天的日期
    AND User_ID IN (
        SELECT DISTINCT User_ID
        FROM UserBehavior_100K
        WHERE Date = '2017-11-25'  -- 第一日的日期
    )
);
UPDATE retention
SET 第七天留存人数 = (
    SELECT COUNT(DISTINCT User_ID)
    FROM UserBehavior_100K
    WHERE Date = '2017-12-01'  -- 第七天的日期
    AND User_ID IN (
        SELECT DISTINCT User_ID
        FROM UserBehavior_100K
        WHERE Date = '2017-11-25'  -- 第一日的日期
    )
);

SELECT 
    第一天活跃人数, 
    第二天留存人数, 
    第三天留存人数, 
    第五天留存人数, 
    第七天留存人数 
FROM retention;

② 留存率

SELECT CONCAT(ROUND(100*第二天留存人数/第一天活跃人数,2),'%') AS 次日留存率,
       CONCAT(ROUND(100*第三天留存人数/第一天活跃人数,2),'%') AS 第3日留存率,
       CONCAT(ROUND(100*第五天留存人数/第一天活跃人数,2),'%') AS 第5日留存率,
       CONCAT(ROUND(100*第七天留存人数/第一天活跃人数,2),'%') AS 第7日留存率
FROM retention;

用户在7日之后还保持76%的留存率，处于比较高的状态

③ 跳失率

只浏览了一个页面的访客数 / 总访客数

SELECT User_ID FROM 用户行为数据
WHERE 用户行为总数=1;

不存在只浏览一次的用户，跳失率为0
结合上述的留存率，说明淘宝的忠实用户比较多

（4）用户购买情况

① 商品、购买数量及次数

商品总数

SELECT COUNT(DISTINCT Item_ID) AS 商品数
FROM UserBehavior_100K;

购买商品数

SELECT COUNT(DISTINCT Item_ID) AS 购买商品数
FROM UserBehavior_100K
WHERE Behavior = 'buy';

购买次数和商品数的对应关系

SELECT a.购买次数,COUNT(a.Item_ID) AS 商品数
FROM (SELECT Item_ID,COUNT(User_ID) AS 购买次数
      FROM UserBehavior_100K
      WHERE Behavior='buy'
      GROUP BY Item_ID) AS a
GROUP BY 购买次数
ORDER BY 购买次数 DESC;

商品共有63585个，用户购买的商品有1960个，其中只购买一次的有1859种
只够买一次的商品占很大比例，说明商品销售不是靠爆款

② 复购率

购买次数>1 / 购买次数>0

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) * 100.0 / SUM(CASE WHEN 购买数 > 0 THEN 1 ELSE 0 END)), 2), '%') AS 复购率
FROM 用户行为数据;

复购率大约66%，说明淘宝足够吸引人，并且能留住用户

4.3 用户行为路径分析

（1）行为转化漏斗（点击行为到其他行为的转化率分别为多少

SELECT 
    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_100K;

导出到csv

连接tableau

可以看出，点击过后的行为比例最高的为加购，推测情况为对同一种商品货比三家，倾向于加入购物车
收藏行为少于加购，可能因为收藏行为不能直接结算
购买行为所占比例仅为2.34%，表明点击后用户大量流失，如何减少点击行为到购买行为的流失将作为分析方向

（2）用户转化漏斗


SELECT 
    SUM(CASE WHEN 点击数 > 0 THEN 1 ELSE 0 END) AS 点击用户数,
    SUM(CASE WHEN 收藏数 > 0 THEN 1 ELSE 0 END) AS 收藏用户数,
    SUM(CASE WHEN 加购数 > 0 THEN 1 ELSE 0 END) AS 加购用户数,
    SUM(CASE WHEN 购买数 > 0 THEN 1 ELSE 0 END) AS 购买用户数
FROM 用户行为数据;

收藏用户数所占比例没有加购高
点击后的用户会有68.27%的付费，购买转化率不错，可以进而分析哪种购物方式促进了购买用户的转化

（3）四种不同行为路径

购买行为可以由大致四种导致：点击+购买、点击+加购+购买、点击+收藏+购买、点击+收藏加购+购买
点击用户数为980

① 点击→购买

SELECT SUM(CASE WHEN 购买数 > 0 THEN 1 ELSE 0 END) AS 购买用户数1
FROM 用户行为数据
WHERE 收藏数 = 0 AND 加购数 = 0;

② 点击→加购→购买

SELECT 
    SUM(CASE WHEN 购买数 > 0 THEN 1 ELSE 0 END) AS 购买用户数2,
    SUM(CASE WHEN 加购数 > 0 THEN 1 ELSE 0 END) AS 加购用户数2
FROM 用户行为数据
WHERE 收藏数 = 0 AND 加购数 <> 0;

③ 点击→收藏→购买

SELECT 
    SUM(CASE WHEN 购买数 > 0 THEN 1 ELSE 0 END) AS 购买用户数3,
    SUM(CASE WHEN 收藏数 > 0 THEN 1 ELSE 0 END) AS 收藏用户数3
FROM 用户行为数据
WHERE 收藏数 <>0 AND 加购数 <> 0;

④ 点击→加购收藏→购买

SELECT 
    SUM(CASE WHEN 购买数 > 0 THEN 1 ELSE 0 END) AS 购买用户数4,
    SUM(CASE WHEN 收藏数 > 0 THEN 1 ELSE 0 END) AS 收藏用户数4,
    SUM(CASE WHEN 加购数 > 0 THEN 1 ELSE 0 END) AS 加购用户数4
FROM 用户行为数据
WHERE 收藏数 <> 0 AND 加购数 <> 0;

（4）不同行为路径的转化漏斗图

可以看出，用户点击后更倾向于加入购物车，直接购买的人比较少
通过“点击+加购+购买”路径，最后的购买人数最多
通过”点击+收藏加购+购买“的最终购买转化效果最高，商家可以通过增加此部分用户所占比例提高销量
比如收藏可有优惠，促进加购消费者进行收藏操作

4.4 用户偏好分析

用户行为时间偏好分析

① 用户行为习惯（按天）

CREATE VIEW 用户行为习惯日分布 AS
SELECT 
    Date,
    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_100K
GROUP BY Date;

点击和加购的趋势基本相同，在12.02号这一天都达到峰值状态
收藏在12.03处于较高的状态
用户的购买行为主要集中在11.27、11.30及12.02之后

② 用户行为习惯（按时）

CREATE VIEW 用户行为习惯小时分布 AS
SELECT LEFT(Time, 2) 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 LEFT(Time, 2);
SELECT * FROM 用户行为习惯小时分布
ORDER BY 时段;

图表说明的情况

整体趋势：“收藏”“加购”“购买” 行为在不同时段呈现波动变化。其中 “收藏” 和 “加购” 行为数值相对较高且波动明显，“购买” 行为数值整体较低且波动较小。
高峰时段：12 - 14 时左右，“收藏” 和 “加购” 行为达到高峰，说明该时段用户对商品的关注和意向较高，是购物前的活跃准备阶段。“购买” 行为在 11 - 15 时也相对活跃，但活跃度低于 “收藏” 和 “加购”。

十四点成为最高峰的可能原因

时间因素：14 点处于午后，很多上班族结束午餐和短暂休息，在工作间隙有时间进行线上购物浏览。学生群体如果在课间休息，也可能利用这段时间查看商品。
消费习惯：经过上午的工作学习，人们会在午后稍作放松，购物成为一种休闲方式。同时，午后相对轻松的氛围也促使人们更愿意进行消费决策。

商家可采取的措施

营销推广：在 12 - 14 时加大广告投放力度，比如在社交媒体、电商平台首页等位置增加曝光。还可以设置限时优惠、秒杀活动等，吸引用户在高峰时段下单。
客服支持：增加该时段的客服人员配置，确保能及时响应和解决用户咨询、疑问，提升用户购物体验，促进购买转化。
数据分析优化：对该时段的用户行为数据进行深度分析，了解用户偏好、浏览路径等，优化商品推荐算法，精准推送商品，提高营销效果。

4.5 用户价值分析

1）RFM模型简介

采用RFM模型进行用户价值分析
由于本数据集不存在金额字段，所以只从RF两个维度进行分析

- R：最近一次购买时间（采用12.03和最近一次购买的时间间隔作为计算依据）
- F：消费频次（采用用户购买次数作为计算依据）

一般来说，R和F的值越大，说明用户的价值越高

（2）R值的计算

数据集的时间跨度为2017年的11.25到12.03，共9天
定义R值的划分规则：

- 间隔时间 > 7天：1
- 间隔时间 5-7天：2
- 间隔时间 3-4天：3
- 间隔时间 0-2天：4

SELECT User_ID,
       CASE 
           WHEN Rtime > 7 THEN 1
           WHEN Rtime BETWEEN 5 AND 7 THEN 2
           WHEN Rtime BETWEEN 3 AND 4 THEN 3
           WHEN Rtime BETWEEN 0 AND 2 THEN 4
           ELSE NULL 
       END AS R值
FROM (
    SELECT User_ID,
           DATEDIFF(DAY, MAX(Date), '2017-12-03') AS Rtime
    FROM UserBehavior_100K
    WHERE Behavior = 'buy'
    GROUP BY User_ID
) AS 购买时间间隔
ORDER BY R值 DESC;

（3）F值的计算

SELECT User_ID,COUNT(Behavior) AS 购买次数
FROM UserBehavior_100K
WHERE Behavior='buy'
GROUP BY User_ID
ORDER BY 购买次数 DESC;

可以看出，购买次数最多的为43次
定义F值的划分规则

- 购买次数 1-10：1
- 购买次数11-20：2
- 购买次数21-30：3
- 购买次数 >30：4

SELECT User_ID,
       CASE 
           WHEN Btime BETWEEN 1 AND 10 THEN 1
           WHEN Btime BETWEEN 11 AND 20 THEN 2
           WHEN Btime BETWEEN 21 AND 30 THEN 3
           WHEN Btime > 30 THEN 4
           ELSE NULL 
       END AS F值
FROM (
    SELECT User_ID,
           COUNT(*) AS Btime
    FROM UserBehavior_100K
    WHERE Behavior = 'buy'
    GROUP BY User_ID
) AS 购买次数
ORDER BY F值 DESC;

（4）用户划分

将用户按照以下规则进行划分：

F(3-4)	重要保持用户	重要价值用户
F(1-2)	重要挽留用户	重要发展用户
	R(1-2)	R(3-4)

创建r_value和f_value分别记录之前计算的R值和F值
将R值和F值合并到一起

SELECT 
    r.User_ID,
    r.R值,
    f.F值
FROM (
    SELECT User_ID,
           CASE 
               WHEN DATEDIFF(DAY, MAX(Date), '2017-12-03') > 7 THEN 1
               WHEN DATEDIFF(DAY, MAX(Date), '2017-12-03') BETWEEN 5 AND 7 THEN 2
               WHEN DATEDIFF(DAY, MAX(Date), '2017-12-03') BETWEEN 3 AND 4 THEN 3
               WHEN DATEDIFF(DAY, MAX(Date), '2017-12-03') BETWEEN 0 AND 2 THEN 4
               ELSE NULL 
           END AS R值
    FROM UserBehavior_100K
    WHERE Behavior = 'buy'
    GROUP BY User_ID
) AS r
JOIN (
    SELECT User_ID,
           CASE 
               WHEN COUNT(*) BETWEEN 1 AND 10 THEN 1
               WHEN COUNT(*) BETWEEN 11 AND 20 THEN 2
               WHEN COUNT(*) BETWEEN 21 AND 30 THEN 3
               WHEN COUNT(*) > 30 THEN 4
               ELSE NULL 
           END AS F值
    FROM UserBehavior_100K
    WHERE Behavior = 'buy'
    GROUP BY User_ID
) AS f
ON r.User_ID = f.User_ID
ORDER BY r.R值 DESC;

计算R值和F值的平均值

SELECT 
  ROUND(AVG(CAST(R值 AS DECIMAL(10, 4))), 4) AS 平均R值,
    ROUND(AVG(CAST(F值 AS DECIMAL(10, 4))), 4) AS 平均F值
FROM 用户价值分析;

查看不同用户对应的份额

CREATE VIEW 用户类型 AS
SELECT User_ID,
(CASE 
    WHEN R值 > 3.3303 AND F值 > 1.0284 THEN '重要价值用户'
    WHEN R值 < 3.3303 AND F值 > 1.0284 THEN '重要保持用户'
    WHEN R值 > 3.3303 AND F值 < 1.0284 THEN '重要发展用户'
    WHEN R值 < 3.3303 AND F值 < 1.0284 THEN '重要挽留用户'
 END) AS user_type
FROM 用户价值分析;
SELECT user_type,COUNT(User_ID) AS 人数
FROM 用户类型
GROUP BY user_type