【实战】淘宝电商用户行为分析——SQL

最新推荐文章于 2025-03-08 21:33:31 发布

Stella屿

最新推荐文章于 2025-03-08 21:33:31 发布

阅读量1.6k

点赞数 3

分类专栏：实战文章标签： sql

本文链接：https://blog.youkuaiyun.com/Bsienwuw/article/details/125104744

版权

本文通过SQL分析了淘宝用户行为，包括数据清洗、整体数据分析、用户消费行为转化、时间维度分析、商品维度分析及RFM模型用户价值。目标是提高用户忠诚度和转化率，发现用户在20-22点最活跃，商品销量与浏览量无直接关联，RFM模型揭示了用户价值分布，有助于精准营销策略制定。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2.2.3 一致化处理（对timestamp）

6.1.3 浏览量前十&销量前十商品有无重合

一、项目背景

UserBehavior是阿里巴巴提供的一个淘宝用户行为数据集。

数据集包含了2017年11月25日-2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢），用以进行隐式反馈推荐问题的研究。

推荐系统中用户对物品的反馈分为显式和隐式反馈：

——显式反馈 (如评分、评级) 或单一的隐式反馈 (如浏览、点击、加入购物车)

——隐式反馈推荐是推荐系统通过对内容和用户行为的分析,建立适当的模型,帮助用户从海量的数据中找到自己感兴趣的内容

1.1分析目的

为客户提供更精准的隐式反馈推荐。

从用户角度：提高用户忠诚度，帮助用户快速找到商品

从网站角度：提高网站交叉销售能力，提高成交转化率

1.2分析思路

主要从以下五个维度进行分析和建议

整体行为数据分析：PV，UV，平均访客量，复购率和跳失率
用户消费行为分析：从总体行为（PV）和独立访客行为（UV）两方面进行从商品的点击、收藏、加购、购买各环节转化率分析，提出改善转化率的意见
时间维度分析：用户在哪些时间、时段活跃，包括用户行为时段分析和用户量(UV，交易用户数)时段分析
商品角度分析——用户对哪些产品感兴趣，查询浏览量top20以及销量top20商品，进行浏览量和销量相关性分析

二、数据来源&清洗

阿里巴巴天池：数据集-阿里云天池

2.1数据集介绍

2.2数据清洗

2.2.1 检查是否存在重复值

select *
from userbehavior u 
group by user_id,item_id,cate_id,behav_type,timestamp
having count(*)>1

发现均不存在重复值

2.2.2 检查是否存在缺失值

SELECT count(user_id),count(item_id),count(cate_id),
       count(behav_type),count(timestamp) 
FROM userbehavior

均不存在缺失值

2.2.3 一致化处理（对timestamp）

将‘timestamp’列转化为三列，分别为时间，日期，小时

#新增一列Date_time
ALTER TABLE userbehavior ADD COLUMN Date_time TIMESTAMP(0)  NULL;
UPDATE userbehavior
SET Date_time = FROM_UNIXTIME(`timestamp`);

#新增列Date
ALTER TABLE userbehavior ADD COLUMN Date char(10) NULL;
UPDATE userbehavior
SET Date = substring(Date_time from 1 for 10);

#新增列Hours
ALTER TABLE userbehavior ADD COLUMN Hours char(10) null;
update userbehavior
set Hours = SUBSTRING(Date_time FROM 12 FOR 2);

2.2.4异常值处理（时间不在范围内）

#检查时间始末
select MAX(Date_time),Min(Date_time)
from userbehavior
#时间最大值为2017-12-04 00:00:06 
#时间最小值为2017-09-11 16:16:39 不符合 要删除异常值

delete from userbehavior 
where Date_time>'2017-12-04 00:00:00' or Date_time<'2017-11-25 00:00:00';

#再次验证日期时间的准确性
select MAX(Date_time),Min(Date_time)
from userbehavior  
#符合要求

三、整体数据分析

3.1.UV,PV,UV/PV

select count(distinct user_id) as '用户数UV',
	(select count(behav_type) from userbehavior where behav_type='pv') as '总访问量PV',
	count(distinct cate_id) as '商品类目数量',
	count(behav_type) as '行为数量',
	sum(case when behav_type='pv' then 1 else 0 end) as '点击',
	sum(case when behav_type='buy' then 1 else 0 end) as '购买',
	sum(case when behav_type='cart' then 1 else 0 end) as '加购',
		sum(case when behav_type='fav' then 1 else 0 end) as '收藏',
		round(sum(
                case when behav_type='pv' then 1 else 0 end)/count(distinct user_id))
         as '人均浏览次数'  
from userbehavior
#人均浏览次数=点击浏览量/用户数=PV/UV

3.2 复购率+跳失率

复购率=消费两次及以上用户数/总消费用户数

跳失率=只有点击行为的用户/总用户数即该用户的行为数=点击数

create view 用户行为数据 as
select user_id,count(behav_type) as '用户行为数',
	sum(case when behav_type ='pv' then 1 else 0 end ) as '点击',
sum(case when behav_type ='fav' then 1 else 0 end) as '收藏',
sum(case when behav_type ='cart' then 1 else 0 end) as '加购',
sum(case when behav_type ='buy' then 1 else 0 end) as '购买'
from userbehavior
GROUP BY user_ID
ORDER BY 用户行为数 DESC;

#计算复购率=消费两次及以上用户数/总消费用户数
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/ sum(case when 购买>0 then 1 else 0 end),2),'%') as 复购率
from 用户行为数据

#跳失率=只有点击行为的用户/总用户数 即该用户的行为数=点击数
select * from 用户行为数据
where '用户行为数'='点击'
#均为空值，说明跳失率为0 
#复购率为62.31%，跳失率为0，可看出淘宝用户的忠诚度较高，可进一步培养用户忠诚度，鼓励更高频次的消费。