淘宝用户行为分析--基于MySQL和Excel

最新推荐文章于 2023-07-20 17:46:14 发布

拨一朵云便见光明

最新推荐文章于 2023-07-20 17:46:14 发布

阅读量1.2k

点赞数

分类专栏：数据分析 MySQL 文章标签：数据分析 mysql

本文链接：https://blog.csdn.net/weixin_42072835/article/details/105126971

版权

数据分析同时被 2 个专栏收录

10 篇文章 1 订阅

订阅专栏

MySQL

7 篇文章 0 订阅

订阅专栏

淘宝用户行为分析–基于MySQL和Excel

1、分析背景

1.1 分析背景

电子商务自诞生以来,一直在以骇人的速度在发展着,加上政策的扶持和自身不拘束于时间和空间的限制的优势,各个电商平台都发展地很不错，尤其是今年的疫情,让人们更加意识到了电商平台的优越性。无论是哪个电商平台,他们的服务对象永远是用户群体,因此对用户的行为进行分析,是十分重要且必要的,越来越多的商家选择通过数据分析的方式获取用户购买行为背后的意义，以不断提高店铺盈利。
1.2 获取数据

本数据集(UserBehavior.csv)包含了约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成。

数据来源：
链接:阿里天池https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1.
列名称

用户ID，字符串类型，用户ID

商品ID，字符串类型，商品ID

商品类目ID，字符串类型，商品所属类目ID

行为类型，字符串，枚举类型，包括(‘pv’, ‘buy’, ‘cart’, ‘fav’)

行为类型包括:

pv，商品详情页pv，等价于点击

buy，商品购买

cart，将商品加入购物车

fav，收藏商品

时间戳，整型，行为发生的时间戳
2、分析目的

获取用户行为规律，从点击到够买之间的购买行为过程中的用户留存规律，用户复购行为以及对产品的喜爱等问题，为后续的营销推广及功能优化改进提供数据支持以及思路。

3、分析思路

3.1 思维导图
在这里插入图片描述
3.2 提出问题

1）基于AARRR漏斗模型用户转化率如何；

2）用户的活跃高峰时什么时间；

3）用户的复购率是怎么样的；

4）哪种产品销量最高,哪种产品受欢迎；

3.3 数据清洗

1.导入表格

从阿里巴巴天池下载数据后保存为CSV格式，在Navicat可视化客户端导入数据，因为下载数据过大，因为此次我们只是演示运用SQL进行数据分析的过程，因此我们导入400000条数据。导入时数据类型均选择varchar(255)；
在这里插入图片描述
2.列名重命名

这个可以在导入数据后设置自己使用更为方便理解的字段，也可提前在mysql中创建表格，然后在导入数据时选择目标表格即可,本次导入是先创建了指定的名为"用户行为"的表格;因为每个用户都可以发生重复购买行为，所以这次我们暂时不设置主键。
创建表格代码如下:

Create table 用户行为(
用户ID varchar(255) not null,
商品ID varchar(255) not null,
商品类目ID varchar(255) not null,
行为类型 varchar(255) not null,
时间戳 varchar(255) not null);

3.选择子集

因为我们研究的是用户的转化率，热销产品，购买行为，活跃高峰等问题，因此在数据筛选时，所有的字段都是需要的，因此暂时不选择子集，选择全集数据；

4.删除重复项

如果用户ID、商品ID，时间戳相同时，我们可以认为是数据发生了重复

因此我们将以上三个字段设置为联合主键，检测是否存在需要删除的重复数据。

在这里插入图片描述
5.缺失值处理
代码如下:

SELECT * FROM 用户行为  WHERE
	用户ID IS NULL
OR 商品ID IS NULL
OR 商品类目ID IS NULL
OR 行为类型 IS NULL
OR 时间戳 IS NULL;

结果显示无缺失值
在这里插入图片描述
6.一致化处理

数据集中时间戳显示为无法看懂的字符串，使用SQL语句转化为可识别的日期及时间，过程及结果如下：

alter table 用户行为 add 日期 varchar(255);
alter table 用户行为 add 时间 varchar(255);
update 用户行为
set 日期=from_unixtime(时间戳,'%y-%m-%d');
update 用户行为
set 时间=from_unixtime(时间戳,'%H:%I:%S');
/*注意,这里转换时间戳的时候,'%H:%I:%S'大写的方式转换为24小时制,'%h:%i:%s' 小写的转化为12小时制*/

7.异常值处理

本次主要考虑是否会出现异常时间:
查找日期及时间是否存在异常值，经查询无异常值，查询过程及结果如下：

select max(日期),min(日期),max(时间),min(时间)
from 用户行为;

在这里插入图片描述
4、分析内容

1.基于AARRR漏斗模型用户转化率如何；

在这个数据集中，用户一共发生了pv（商品详情页pv，等价于点击），cart（将商品加入购物车），fav（收藏商品），buy （商品购买）四个购买行为，四个行为之间是逐渐递进的关系，浏览后加购或者收藏然后发生购买行为。

总体行为数据查询过程及结果如下：

select 行为类型,count(*)
from 用户行为
group by 行为类型;

在这里插入图片描述

用户行为包括点击、放进购物车、收藏以及购买，点击占总行为数的89.5%，而放进购物车只占5.5%，最后实际购买只占2%，夹点在放进购物车这一环节上，可能出现的原因是用户花了大量时间寻找合适的产品，针对这个可以优化电商平台的筛选功能，让用户可以更容易找到合适产品。
单个用户查询过程及结果如下：

select 行为类型,count(distinct 用户ID)as 次数
from 用户行为
group by 行为类型;

在这里插入图片描述

从上图可以看出浏览的用户里发生购买行为的用户达到了69.55%，因此从这个角度来看，用户的转化是非常高的。
人均数据查询过程及结果如下：

select 行为类型,count(DISTINCT 用户ID)as 人数,
                count(行为类型)as 浏览数,
                count(行为类型)/count(DISTINCT 用户ID) as 人均次数
from 用户行为
group by 行为类型
order by 人均次数 desc;

在这里插入图片描述
上图是人均浏览量到人均购买次数的数据，其中人均浏览量高达92次，但是人均购买次数只有3次，因此在各个环节之间存在用户的流失，用户的流失主要发生在哪个环节呢，现在回去单个用户的购买行为都是在哪个环节终止的。
用户流失查询过程及结果如下：

// An highlighted block
CREATE VIEW  用户流失 as select 用户ID,sum(case when 行为类型='pv' then 1 else 0 end) as PV,
              sum(case when 行为类型='fav' then 1 else 0 end) as fav,
              sum(case when 行为类型='cart' then 1 else 0 end) as cart,
              sum(case when 行为类型='buy' then 1 else 0 end) as buy
from 用户行为
group by 用户ID;
/*将这个语句单独保存为视图并命名为用户流失；*/

在这里插入图片描述
上图可以看出有的用户浏览了商品但是没发生购买行为，因此我们将流失分为几种类型，浏览后流失，收藏后流失，加购后流失，加购收藏后流失。
流失环节比率查询过程及结果如下：

select count(用户ID)as 用户总数,
       sum(case when pv<>0 and fav=0 and cart=0 and buy=0 then 1 else 0 end)as 浏览后流失,
       sum(case when fav>0 and cart=0 and buy=0 then 1 else 0 end)as 收藏后流失,
       sum(case when fav=0 and cart>0 and buy=0 then 1 else 0 end)as 加购后流失,
       sum(case when fav>0 and cart>0 and buy=0 then 1 else 0 end)as 加购收藏后流失,
       sum(case when buy<>0 then 1 else 0 end)as 购买人数
from 用户流失;

在这里插入图片描述

上图可以看出在各个环节的用户流失比例，其中加购后流失在购买环节中是流失比例最高的一环，占总流失数的46%

加购后流失的原因是什么

第一步：提出假设

假设一：售前服务不满意

假设二：竞品的促销活动

假设三：产品自身的规格属性

假设四：是否包邮

第二步：数据收集及结论

暂时没有相关数据，因此我们暂时不做深入分析

结论：
1.单个用户从浏览到购买的转化率是比较高的；

2.在购买行为发生初期，用户花费了大量时间在搜索行为上，因此考虑优化页面详情，更好的展现产品优势及特点，减少用户在产品选择上的比较次数，缩短购买时间；

3.人均浏览量达到91，人均购买只有3，因此单个用户花费大量时间在商品的搜索上，因此可以优化搜索词也展示详情页，使用户可以在较少次数的浏览下完成购买。

4.用户在加购后流失比例远高于其他环节的流失率，可以后续分析是由于什么原因导致用户在加购后没有完成付款，比如售前服务，竞品的价格质量优势，产品本身不符合顾客需求，邮费问题等。

2.1用户的活跃高峰时什么时间(一天中的哪些时间段)；

计算在一天中的不同时段的活跃用户

select hour(时间) as 小时,
       sum(case when 行为类型='pv' then 1 else 0 end)as 点击数,
       sum(case when 行为类型='fav' then 1 else 0 end)as 收藏数,
       sum(case when 行为类型='cart' then 1 else 0 end)as 加购数,
       sum(case when 行为类型='buy' then 1 else 0 end)as 购买数
from 用户行为
group by 小时
order BY 小时 asc;

在这里插入图片描述
每小时浏览量

在这里插入图片描述
每小时收藏数/加购数/购买数

上图分别是每小时用户发生的点击行为随时间的变化图，可以看出用户从凌晨1点到上午8点之间的点击数是一天中最为平缓的时候，浏览数量及加购数在晚上22点达到高峰，但是其他点击数从上午10点到晚23点的点击数量一直相对平缓。因此用户在晚上的时候活跃率以及加购率较高，可以适当在晚上增加商品的曝光度。

2.2用户的活跃高峰时什么时间(一周中的哪些天)

select DAYOFWEEK(日期) as 周几,
count(*)as 点击数,count(distinct(用户ID))
from 用户行为 
group by 周几
order by 周几;

在这里插入图片描述
上图可以看出用户数及点击数都在周六有一个小幅度的上升，因此周末的点击量及用户数都是偏高的，可以在周末的时候加大对推广及曝光力度以获取更多的盈利。
结论
1.用户上午9点到晚上23点之间用户的数量是比较高的，同时是比较稳定的，而在凌晨1点到8点之间用户数较低，刚好这段时间是休息及在早起上班的时间，因此用户数量偏低；

2.每小时浏览量及加购量在晚上21时到22时之间达到顶峰，因此在此之间是用户活跃度最高的时候，考虑在此时间段加大退关及曝光力度，考虑到上文分析的加购用户的流失率是最高的，因此考虑用户流失和时间存在一定的关系，这里我们暂时不做数据的展开分析；

3.用户的日活整体是比较稳定的，只有在周末的时候略有上浮，因此可以考虑周末和工作日不同的营销方案

3.用户的复购率是怎么样的；

统计发生重复购买行为的用户占比

SELECT 购买次数, COUNT(*) AS '人数'
FROM
(SELECT COUNT(用户ID) AS 购买次数
FROM 用户行为
WHERE 行为类型 = 'buy'
GROUP BY 用户ID) AS 用户复购
GROUP BY 购买次数 ASC;

在这里插入图片描述
整体复购率为(2707-883)/2707= 67.38%，有购买行为的用户中，大概有 67.38%的用户会重复购买。

下面是复购情况的可视图，可以看出大部分买家只购买一次。

在这里插入图片描述

复购用户的交易数占比

SELECT sum(购买次数*人数) as 购买总数 from (SELECT 购买次数, COUNT(*) AS '人数'
FROM
(SELECT COUNT(用户ID) AS 购买次数
FROM 用户行为
WHERE 行为类型 = 'buy'
GROUP BY 用户ID) AS buy_pool
GROUP BY 购买次数 ASC) as 购买情况 ;
SELECT (购买次数*人数) as 非复购用户 from (SELECT 购买次数, COUNT(*) AS '人数'
FROM
(SELECT COUNT(用户ID) AS 购买次数
FROM 用户行为
WHERE 行为类型 = 'buy'
GROUP BY 用户ID) AS buy_pool
GROUP BY 购买次数 ASC) as 购买情况 where 购买次数<=1;

在这里插入图片描述

在这里插入图片描述
由上图可以到发生重复购买的用户占总用户数的67%，复购用户的交易总数占总交易数的89.4%。
结论
复购用户占比及其交易次数的占比都是非常高的，因此用户的黏度较高，同时新用户的占比较低，交易数占比也较低，因此应加大商品的渠道推广力度，增加店铺及商品曝光，增加用户流量，获取更多的激活用户。

4.哪种产品销量最高,哪种产品受欢迎；

SELECT 商品购买次数, COUNT(*) AS 商品类型数量
FROM
(SELECT COUNT(用户ID) AS 商品购买次数
FROM 用户行为
WHERE 行为类型 = 'buy'
GROUP BY 商品类目ID) AS 商品购买情况
GROUP BY 商品购买次数
ORDER BY 商品购买次数 ASC;

产品的购买次数统计结果如下：
在这里插入图片描述
只购买一次的产品有6904种，购买两次的产品有502种，本次分析的4847种商品中并没有找到销售非常集中的产品，为了看清楚这一部分我们来看看产品累计的销售情况。

产品种类累计销售情况
在这里插入图片描述
可以看出33.73的产品贡献了80%的销售量，虽然接近二八法则,但是并不完全一致,通过查阅资料发现,电商平台依靠长尾理论累计销售。
长尾理论:

“长尾理论是网络时代兴起的一种新理论，由于成本和效率的因素，当商品储存、流通、展示的场地和渠道足够宽广，商品生产成本急剧下降以至于个人都可以进行生产，并且商品的销售成本急剧降低时，几乎任何以前看似需求极低的产品，只要有卖，都会有人买。这些需求和销量不高的产品所占据的共同市场份额，可以和主流产品的市场份额相比，甚至更大。

销量前30的点击数与购买数之间对比

在这里插入图片描述

由上图可以看出浏览最高的产品与销量最高的产品类别是不相同的，同时浏览量很高的商品类别的购买转化率也是比较低的，商品类别4756105、3607361、4145813、2355072、982926是点击量最高五类产品类别，是用户搜索频率较高的产品，但是转化率都比较低，因此后续可以对这五类产品进行优化，提高环化率。

产品3607361，2520377，3002561，4181361，4170419是销量最高的五类产品热销产品，因此可以在后续的推广过程中可以增加曝光获取更多的用户。

其中3607361是浏览量及销量都比较高的产品，因此可以着重推广此类产品；