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

blhbd

于 2023-09-06 20:39:50 发布

阅读量1.1k

点赞数

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

本文链接：https://blog.csdn.net/blhbd/article/details/132641142

版权

一、项目介绍

1.1 项目背景

随着电商行业近几年的迅猛发展，电⼦商务从早些年的粗放式经营，逐步转化为精细化运营。随着
平台数据量的不断积累，通过数据分析挖掘消费者的潜在需求，消费偏好成为平台运营过程中的重要环节。本项目基于淘宝电商平台用户行为数据，在MySQL关系型数据库，探索用户行为规律，寻找高价值用户；分析商品特征，寻找高贡献商品；分析产品功能，优化产品路径。

1.2 项目目的

想通过对用户行为数据进行分析，为以下问题提供解释和改进建议:
（1）基于漏斗模型的用户购买流程各环节分析指标，确定各个环节的转换率，便于找到需要改进的环节;
（2）基于RFM模型找出核心付费用户群，对这部分用户进行精准营销；
（3）商品分析: 找出热销商品，研究热销商品特点。

1.3 数据集介绍

数据来源：数据集—阿里云天池

数据说明：该数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。

数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：

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

四种用户类型包括：

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

二、分析思路

2.1 分析框架

2.2 分析维度

平台维度

通过对电商流量维度的分析，可以了解到用户流量的相关信息，比如用户活跃时间、用户流量漏斗转化率。

需要指标：uv、pv、人均访问量、跳出率、复购率、用户流量漏斗转化率…

用户维度

通过对用户行为的分析，可以了解用户四种行为数据，用户购买路径偏好，用户留存率等。

通过对用户价值进行分析，使用RFM模型，对用户进行分类，找出有价值的用户，对用户进行精细化运营。

需要指标：行为数据、购买路径、用户留存率、RFM

商品维度

通过对商品品类、商品成交量、商品点击次数、购买转化率等指标的分析，探索用户对商品及商品品类的偏好，了解商品销售的规律。

三、数据导入

导入方式：Navicat

在MySQL中新建一个taobao数据库，导入向导，保留前100w条数据

四、数据清洗

4.1 检查是否存在重复值

select * from userbehavior 
group by user_id,item_id,category_id,behavior_type,time
having count(*)>1;

结果都是空，不存在重复值

4.2 查看缺失值

select
	count(user_id),
	count(item_id),
	count(category_id),
	count(behavior_type),
	count(time) 
from
	userbehavior;

统计每一个字段下有多少行，查询结果行数是相等的，说明没有缺失值。

4.3 时间格式转换

-- 新增date,hour时间列
alter table userbehavior add date VARCHAR(20),add hour VARCHAR(20);
-- 将hour列的时间转换为-年-月-日
update userbehavior set date = FROM_UNIXTIME(time,'%Y-%m-%d');
-- 将hour列的时间转换为-时
update userbehavior set hour = FROM_UNIXTIME(time,'%H');
-- 将time列的时间转换为-年-月-日 -时-分-秒
UPDATE userbehavior SET time = FROM_UNIXTIME(time);

#调整time字段数据的样式
UPDATE userbehavior SET time = SUBSTRING_INDEX(time,'.',1);

最终处理结果如下：

4.4 过滤异常值

过滤时间范围不在2017-11-25至2017-12-3之间的数据

delete from userbehavior
where date < '2017-11-25' or date > '2017-12-3';

检查下是否将异常值剔除干净

select max(time),min(time) from userbehavior;

五、数据分析

5.1 平台维度

5.1.1 流量分析

总体流量分析

select count(DISTINCT user_id) as uv,count(user_id) as '网站总访问量',
       sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,
       sum(case when beha