淘宝用户行为数据分析（个人项目）

最新推荐文章于 2025-04-15 16:28:33 发布

THOR_IVAN

最新推荐文章于 2025-04-15 16:28:33 发布

阅读量1.7k

点赞数 39

文章标签：数据分析 mysql 大数据

本文链接：https://blog.csdn.net/THOR_IVAN/article/details/146120193

版权

一、准备

1.介绍

淘宝用户购物行为数据集_数据集-阿里云天池包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢），以逗号分隔。

列名称	说明
用户ID	整数类型，序列化后的用户ID
商品ID	整数类型，序列化后的商品ID
商品类目ID	整数类型，序列化后的商品所属类目ID
行为类型	字符串，枚举类型，包括('pv', 'buy', 'cart', 'fav')
时间戳	行为发生的时间戳

其中

行为类型	说明
pv	商品详情页pv，等价于点击
buy	商品购买
cart	将商品加入购物车
fav	收藏商品

使用了MySQL以及Tableau的数据分析工具，MySQL用于数据清洗和取数操作，Tableau用于数据可视化。

思路框架如下：

2.导入

CREATE TABLE userbehavior (`用户id`INT,`商品id`INT,`商品类目id`INT,`行为类型` VARCHAR(100),`时间戳` INT);#初始化空表
LOAD DATA INFILE 'D:/programming/MySQL Server 9.2/data/UserBehavior.csv'I
NTO TABLE userbehavior
FIELDS TERMINATED BY ','#逗号分隔
LINES TERMINATED BY '\n';#设置行结束符

#查看重复值
SELECT * FROM userbehavior GROUP BY `用户id`,`商品id`,`商品类目id`,`行为类型`,`时间戳` HAVING COUNT(*)>1;
#查看空值
SELECT * FROM userbehavior0 WHERE `用户id`is NULL OR`商品id`is NULL OR`商品类目id`is NULL OR`行为类型`is NULL OR`时间戳`is NULL;
#查看异常值
SELECT * FROM userbehavior0 WHERE `用户id`<0 OR`商品id`<0 OR`商品类目id`<0 OR`时间戳`<0;

重复值

空值

异常值

二、数据处理

#去重到新表
CREATE TABLE userbehavior0 (`用户id`INT,`商品id`INT,`商品类目id`INT,`行为类型` VARCHAR(100),`时间戳` INT);
INSERT INTO userbehavior0 (`用户id`,`商品id`,`商品类目id`,`行为类型`,`时间戳`)
SELECT DISTINCT `用户id`,`商品id`,`商品类目id`,`行为类型`,`时间戳` FROM userbehavior;

ALTER TABLE userbehavior0 ADD COLUMN `日期` DATE,ADD COLUMN `时间` TIME,
processed TINYINT DEFAULT 0;#添加日期、时间、标记列
CREATE INDEX idx_processed ON userbehavior0 (processed);#添加索引可直接跳到 processed=0 的行，时间复杂度约O(logn)
EXPLAIN SELECT * FROM userbehavior0 WHERE processed=0;#EXPLAIN查看查询计划，确认索引是否被使用

-- 数据过大分批更新
DELIMITER $$  
CREATE PROCEDURE batch_update()#声明存储过程函数
BEGIN
	DECLARE rows_affected INT DEFAULT 1;#初始化影响的行数为1，确保进入循环  
   WHILE rows_affected > 0 DO#没有行被更新结束循环
		START TRANSACTION;#开启事务
		UPDATE userbehavior0 SET`日期`=DATE(FROM_UNIXTIME(`时间戳`)),`时间`=TIME(FROM_UNIXTIME(`时间戳`)),processed = 1#标记为已处理  
    WHERE processed = 0#只更新未处理的行
		LIMIT 100000;
		SET rows_affected = ROW_COUNT();
		COMMIT;#提交事务，释放锁和日志
	END WHILE;
END$$
DELIMITER;
CALL batch_update();

DROP INDEX idx_processed ON userbehavior0;
ALTER TABLE userbehavior0 DROP `时间戳`,DROP processed;
-- 官网表明数据集为2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为，剔除不符合的数据
DELETE FROM userbehavior0 WHERE `日期` NOT BETWEEN '2017-11-25'AND'2017-12-3';
DELETE FROM userbehavior0 WHERE `日期` IS NULL;

三、数据分析

-- 数据概览

SELECT COUNT(DISTINCT `用户id`) 用户数,COUNT(DISTINCT `商品id`) 商品数,COUNT(DISTINCT `商品类目id`) 商品类数,COUNT(`行为类型`) 行为次数,MIN(`日期`) 最早日期,MAX(`日期`) 最晚日期
FROM userbehavior0;

#按日统计
SELECT `日期`,SUM(CASE WHEN `行为类型`='pv' THEN 1 ELSE 0 END) 日点击数,
COUNT(DISTINCT `用户id`)日活人数,
FLOOR(SUM(CASE WHEN `行为类型`='pv' THEN 1 ELSE 0 END)/COUNT(DISTINCT `用户id`)) 日人均点数,
SUM(CASE WHEN `行为类型`='buy' THEN 1 ELSE 0 END) 日成交量
FROM userbehavior1
GROUP BY `日期`
ORDER BY `日期`;

从上述图中发现，2017年11月27日周一的日点击量、日活跃人数以及日人均点击量，对于上一个周末的数据有相应减少，原因可能是周末用户有空闲时间进行网购消费，而进入工作日之后，空闲的时间相对周末大幅度减少，不过属于正常的周期性现象;2017年12月2日的日点击量，日活跃用户数，以及成交量有明显增长，相比于12月1日增长分别为21.1%、31.1%、18.6%，且该周周末比上周周末的数据也有明显提高，可能原因是双十二从12月2号开始预热活动，刺激用户消费。

#统计用户行为
CREATE TABLE ub(`用户id`INT,`点击数`INT,`收藏数`INT,`加购数`INT,`成交数`INT);
INSERT INTO ub
SELECT `用户id`,
	SUM(CASE WHEN `行为类型`='pv' THEN 1 ELSE 0 END)点击数,
	SUM(CASE WHEN `行为类型`='fav' THEN 1 ELSE 0 END)收藏数,
	SUM(CASE WHEN `行为类型`='cart' THEN 1 ELSE 0 END)加购数,
	SUM(CASE WHEN `行为类型`='buy' THEN 1 ELSE 0 END)成交数
FROM userbehavior0
GROUP BY `用户id`
ORDER BY `用户id`;

-- 用户行为习惯

#计算复购率=（购买次数> 1的用户数）/（总购买用户数），通常用于衡量用户的忠诚度和购买频率
SELECT SUM(CASE WHEN 成交数>1 THEN 1 END)/COUNT(*)复购率
FROM ub
WHERE 成交数<>0;

用户复购率达到了66%，说明用户粘性以及忠诚度较高，购物体验基本符合用户心理预期，能够满足用户购物的需求。

#按小时统计
CREATE TABLE ub(`日期`DATE,`时刻`VARCHAR(5) ,`用户id`INT,`点击数`INT,`收藏数`INT,`加购数`INT,`成交数` INT);
INSERT INTO ub(`日期`,`时刻`,`用户id`,`点击数`,`收藏数`,`加购数`,`成交数`)
SELECT `日期`,TIME_FORMAT(`时间`,'%H时')时刻,`用户id`,#用max过滤用户重复行为
	SUM(CASE WHEN `行为类型`='pv' THEN 1 ELSE 0 END)点击数,
	SUM(CASE WHEN `行为类型`='fav' THEN 1 ELSE 0 END)收藏数,
	SUM(CASE WHEN `行为类型`='cart' THEN 1 ELSE 0 END)加购数,
	SUM(CASE WHEN `行为类型`='buy' THEN 1 ELSE 0 END)成交数
FROM userbehavior0
GROUP BY `用户id`,`日期`,TIME_FORMAT(`时间`,'%H时')
ORDER BY `日期`,`时刻`;

从上图可知，从下午6点开始到晚上10点时间段，是用户活跃时间，商品的点击，加购，收藏，购买均有所提升，不过从点击到购买的总转化率相比上午10点到下午3点的时间较低，所以针对下午6点到晚上10点时间段，可以进行一些促销类的活动，提高购买人数，进而提高总交易金额。

-- 基于漏斗模型分析用户转化率

漏斗模型是一种用于分析和优化用户行为路径的工具，广泛应用于市场营销、产品设计、用户行为分析等领域。其意义在于帮助企业和团队更好地理解用户行为，优化业务流程，提高转化率和用户满意度。用户可能的行为路径如下图所示：

#行为路径
WITH behavior_route AS(#窗口函数
	SELECT COUNT(DISTINCT`用户id`)`总点击数`,
		COUNT(DISTINCT CASE WHEN `收藏数`=0 AND `加购数`=0 AND `成交数`>0 THEN `用户id` END)`点击-成交人数`,
		COUNT(DISTINCT CASE WHEN `收藏数`=0 AND `加购数`=0 AND `成交数`>0 THEN `用户id` END)/COUNT(DISTINCT`用户id`)`点击-成交转化率`,
		
		COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`=0 THEN `用户id` END)`点击-收藏人数`,
		COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`=0 THEN `用户id` END)/COUNT(DISTINCT`用户id`)`点击-收藏转化率`,
		COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`=0 AND `成交数`>0 THEN `用户id` END)`点击-收藏-成交人数`,
		COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`=0 AND `成交数`>0 THEN `用户id` END)/COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`=0 THEN `用户id` END)`点击-收藏-成交转化率`,
		
		COUNT(DISTINCT CASE WHEN `收藏数`=0 AND `加购数`>0 THEN `用户id` END)`点击-加购人数`,
		COUNT(DISTINCT CASE WHEN `收藏数`=0 AND `加购数`>0 THEN `用户id` END)/COUNT(DISTINCT`用户id`)`点击-加购转化率`,
		COUNT(DISTINCT CASE WHEN `收藏数`=0 AND `加购数`>0 AND `成交数`>0 THEN `用户id` END)`点击-加购-成交人数`,
		COUNT(DISTINCT CASE WHEN `收藏数`=0 AND `加购数`>0 AND `成交数`>0 THEN `用户id` END)/COUNT(DISTINCT CASE WHEN `收藏数`=0 AND `加购数`>0 THEN `用户id` END)`点击-加购-成交转化率`,
		
		COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`>0 THEN `用户id` END)`点击-收藏&加购人数`,
		COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`>0 THEN `用户id` END)/COUNT(DISTINCT`用户id`)`点击-收藏&加购转化率`,
		COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`>0 AND `成交数`>0 THEN `用户id` END)`点击-收藏&加购-成交人数`,
		COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`>0 AND `成交数`>0 THEN `用户id` END)/COUNT(DISTINCT CASE WHEN `收藏数`>0 AND `加购数`>0 THEN `用户id` END)`点击-收藏&加购-成交转化率`
	FROM ub)
#排版
SELECT'点击'路径,`总点击数`人数,NULL 转化率 FROM behavior_route
UNION ALL
SELECT'点击-成交'路径,`点击-成交人数`人数,`点击-成交转化率`转化率 FROM behavior_route
UNION ALL
SELECT'点击-收藏',`点击-收藏人数`,`点击-收藏转化率`FROM behavior_route
UNION ALL
SELECT'点击-收藏-成交',`点击-收藏-成交人数`,`点击-收藏-成交转化率`FROM behavior_route
UNION ALL
SELECT'点击-加购',`点击-加购人数`,`点击-加购转化率`FROM behavior_route
UNION ALL
SELECT'点击-加购-成交',`点击-加购-成交人数`,`点击-加购-成交转化率`FROM behavior_route
UNION ALL
SELECT'点击-收藏&加购',`点击-收藏&加购人数`,`点击-收藏&加购转化率`FROM behavior_route
UNION ALL
SELECT'点击-收藏&加购-成交',`点击-收藏&加购-成交人数`,`点击-收藏&加购-成交转化率`FROM behavior_route;

从上述漏斗图中转化效果最好的用户路径为点击-加购-购买，其次是点击-收藏&加购-成交，用户行为路径存在加购行为的用户，最终购买用户数有显著提升，基本达到70%的转化率，原因是加入购物车方便用户批量购买，可以通过运营活动引导用户对商品进行加购，进一步提高最终付费转化率；对于点击-收藏-购买的用户行为路径，收藏到购买的转化率虽然可以达到60%，但是点击到收藏的转化率很低，推测因为商品的价格没有达到用户心理预期，用户希望等商品降价之后进行购买，后续可以对这些用户收藏的商品进行分析，是否价格合理，是否可以通过提供相应优惠券，或者与商家共同推出优惠活动，提高最终购买转化率；对于点击-购买的用户路径，可以对这部分用户的用户标签进行统计，初步形成该类商品对应的用户画像，在之后的活动中对用于这类标签的用户优化商品推荐功能，精准推送商品信息。

-- 商品偏好分析

CREATE TABLE goods_record(`商品id`INT,`商品类目id`INT,`点击数`INT,`收藏数`INT,`加购数`INT,`成交数` INT);
INSERT INTO goods_record
	SELECT `商品id`,`商品类目id`,
		SUM(CASE WHEN `行为类型`='pv' THEN 1 ELSE 0 END)点击数,
		SUM(CASE WHEN `行为类型`='fav' THEN 1 ELSE 0 END)收藏数,
		SUM(CASE WHEN `行为类型`='cart' THEN 1 ELSE 0 END)加购数,
		SUM(CASE WHEN `行为类型`='buy' THEN 1 ELSE 0 END)成交数
	FROM userbehavior0
	GROUP BY `商品类目id`,`商品id`
	ORDER BY `商品类目id`,`商品id`;
#top10点击商品、类别
SELECT `商品id`,`点击数`FROM goods_record ORDER BY `点击数` DESC limit 10;
SELECT `商品类目id`,SUM(`点击数`)点击数 FROM goods_record GROUP BY `商品类目id` ORDER BY SUM(`点击数`) DESC limit 10;
#收藏top10商品、类别
SELECT `商品id`,`收藏数`FROM goods_record ORDER BY `收藏数` DESC limit 10;
SELECT `商品类目id`,SUM(`收藏数`)收藏数 FROM goods_record GROUP BY `商品类目id` ORDER BY SUM(`收藏数`) DESC limit 10;
#top10加购商品、类别
SELECT `商品id`,`加购数`FROM goods_record ORDER BY `加购数` DESC limit 10;
SELECT `商品类目id`,SUM(`加购数`)加购数 FROM goods_record GROUP BY `商品类目id` ORDER BY SUM(`加购数`) DESC limit 10;
#top10成交商品、类别
SELECT `商品id`,`成交数`FROM goods_record ORDER BY `成交数` DESC limit 10;
SELECT `商品类目id`,SUM(`成交数`)成交数 FROM goods_record GROUP BY `商品类目id` ORDER BY SUM(`成交数`) DESC limit 10;

在TOP10商品分布中，点击、收藏、加购TOP10相同的商品种类有9种，说明商品推荐以及商品检索的精准程度能够很好的满足用户的需求，达到用户心理对于搜寻商品的预期，而热销商品TOP10的商品种类与点击、收藏、加购TOP10大相径庭，并且热销排名靠前并不是点击，收藏，加购TOP10商品列表中排名靠前的商品，平台需要加大对热销商品的曝光量，优化热销商品种类商品展示，从而提高销量。

按如下方法添加仪表板操作进行动态展示与进一步比对：点击仪表板选项卡中的操作->添加操作->筛选器\添加操作->突出显示，如下图设置，源工作表、目标工作表都全选。

单击突出显示效果：

单击菜单中筛选1效果：

可以看到筛选出点击最高类目对应的商品在不同指标的表现，再次单击即可恢复全部显示。

筛选成交最多类目和商品其余表均显示空白，表示均不在前十名榜中，用SQL取数查看明细：

SELECT * FROM goods_record WHERE `商品id`=3122135 OR `商品类目id`=1464116 ORDER BY `成交数`DESC;

可以看到商品id3122135成交量遥遥领先，远超热销第一类目的其他22417件商品，而该类目下有44件商品成交量达到百次以上，而有16557件商品成交量为0，后续可以获取更多维更详细的数据进行爆款分析，平台也可以优化推送机制。

-- RFM模型分析用户价值

RFM 模型是一种用于客户细分和行为分析的工具，广泛应用于市场营销、客户关系管理（CRM）和电子商务等领域。它通过三个关键指标（Recency、Frequency、Monetary）对客户进行分类，帮助企业更好地理解客户行为并制定针对性的营销策略。

R反映客户的活跃度，最近购买过的客户更有可能再次购买。F反映客户的忠诚度，购买频率高的客户更可能是忠实客户。M反映客户的消费能力，消费金额高的客户对企业的收入贡献更大。由于没有累计交易金额M，所以选取R，F两个维度进行分析，先根据原数据计算出R,F值，再指定打分标准计算用户R,F的得分r、f

得分	r	f
4	0<=R<=2	F>= 20
3	2<R<=4	11<=F<20
2	4<R<=7	6<= F<11
1	R>7	0<=F<6

CREATE TABLE rf(`用户id`INT,`相差天数R`INT,`成交频次F`INT,r INT,f INT);
INSERT INTO rf 
	SELECT t1.`用户id`,Recency,Frequency,
		CASE WHEN Recency>7 THEN 1#相差天数越少得分越高
			WHEN Recency>4 AND Recency<=7 THEN 2
			WHEN Recency>2 AND Recency<=4 THEN 3
			ELSE 4 END r,
		CASE WHEN Frequency<6 THEN 1#成交数越多得分越高
			WHEN Frequency>=6 AND Frequency<11 THEN 2
			WHEN Frequency>=11 AND Frequency<20 THEN 3
			ELSE 4 END f
	FROM(SELECT ub.`用户id`,`成交数`Frequency FROM ub)t1 JOIN
		(SELECT `用户id`,DATEDIFF('2017-12-3',MAX(`日期`))Recency
		FROM userbehavior0
		WHERE `行为类型`='buy'
		GROUP BY `用户id`)t2 ON t1.`用户id`=t2.`用户id`;
#用户分层
WITH avgs AS(SELECT AVG(r)avg_r, AVG(f)avg_f FROM rf)
SELECT ANY_VALUE(avg_r)avg_r,ANY_VALUE(avg_f),#直接avg_favg_r,avg_f报错
    SUM(CASE WHEN r > avg_r AND f > avg_f THEN 1 ELSE 0 END) AS 价值用户,  
    SUM(CASE WHEN r > avg_r AND f <= avg_f THEN 1 ELSE 0 END) AS 发展用户,  
    SUM(CASE WHEN r <= avg_r AND f > avg_f THEN 1 ELSE 0 END) AS 保持用户,  
    SUM(CASE WHEN r <= avg_r AND f <= avg_f THEN 1 ELSE 0 END) AS 挽留用户  
FROM rf, avgs;

MySQL的sql_mode中启用了ONLY_FULL_GROUP_BY模式。在该模式下，SELECT 列表中的非聚合列必须出现在 GROUP BY 子句中，否则会报错1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'avgs.avg_r'; this is incompatible with sql_mode=only_full_group_by，使用ANY_VALUE函数，声明这些列不需要聚合。

根据rf表可视化，先在左下角参数栏创建阈值参数，如下分别设置两个指标的3个阈值：

然后分别右键显示参数控件，再创建计算字段(以下均在tableau中输入)r_socre：

IF [相差天数R]>[Recency Threshold3] THEN 1
ELSEIF [相差天数R]>[Recency Threshold2] AND [相差天数R]<=[Recency Threshold3] THEN 2
ELSEIF [相差天数R]>[Recency Threshold1] AND [相差天数R]<=[Recency Threshold2] THEN 3
ELSE 4 END

f_socre：

IF [成交频次F]<[Frequency Threshold1] THEN 1
ELSEIF [成交频次F]>=[Frequency Threshold1] AND [成交频次F]<[Frequency Threshold2] THEN 2
ELSEIF [成交频次F]>=[Frequency Threshold2] AND [成交频次F]<[Frequency Threshold3] THEN 3
ELSE 4 END

r_avg：

{FIXED:AVG([r_socre])}

f_avg：

{FIXED:AVG([f_socre])}

用户分层：

IF [r_socre] > [r_avg] AND [f_socre] > [f_avg] THEN "价值用户"
ELSEIF [r_socre] > [r_avg] AND [f_socre] <= [f_avg] THEN "发展用户"
ELSEIF [r_socre] <= [r_avg] AND [f_socre] > [f_avg] THEN "保持用户"
ELSE "挽留用户"END

Tableau不允许在同一个计算字段中混合使用聚合和非聚合字段，因[r_avg]、[f_avg]是聚合字段而报错。在创建[r_avg]、[f_avg]时使用FIXED来计算全局的均值。

把用户分层字段移至维度栏，再拖入标记栏颜色，用户id拖入角度，右键选择度量->计数，最后得到如下可调阈值动态饼图：

对于价值用户，可以提供VIP服务以及定向优惠活动，对于他们所购买的商品种类，可以进行精准营销；对于发展用户，想办法提高他们的购买频率；对于保持用户,可以通过PUSH，短信，站内消息等方式促进用户活跃度，提高他们的付费转化率；对于挽留用户，需弄清楚这部分用户的需求，购物体验为什么没有达到用户预期，分析这部分用户流失的原因，想办法挽留用户。

四、总结

该分析报告选取阿里天池淘宝用户行为数据为数据源进行分析，分析步骤如下：导入数据、数据清洗、数据分析、提出假设及建议。数据分析从以下五个方面入手：数据总览、用户行为习惯、用户行为路径、商品偏好、用户价值，搭建相关数据指标体系，可以将数据归纳为以下两个层面的指标：行为指标：日点击量PV、日活跃人数UV、转化率；商品指标：复购率、商品排行。基于上述两个层面的指标，也会出现一些基于公式的衍生指标，比如日人均浏览次数PV/UV。该分析报告中，运用了数据分析中两个常用的模型，基于用户行为路径分析转化率的漏斗模型和体现用户价值的RFM模型。对于数据分析的整体思路可以按照以下分析步骤：发现问题——归因问题——提出假设——验证假设，本次数据分析报告中，针对发现的问题提出了一些假设，为了验证这些假设，后续可以进一步进行AB测试。

该分析报告让我对数据分析的流程，对于电商平台的数据分析的框架和思路有基本的了解，学会运用常用指标以及数据分析模型、方法，在电商平台是如何利用这些指标数据发现问题以及分析问题。