SQL数据分析-淘宝用户行为

写在前面的话
为了巩固这段时间SQL的学习成果,同时方便以后回忆和二次学习,我决定把自己做过的项目 淘宝用户行为分析 整理出来,和各位网友分享。同时欢迎大家提出不同意见和建议,我们一起讨论,共同学习和进步。

处理数据用到的软件
数据导入和导出:Navicat Premium 12
可视化:Tableau 2019.4 、Excel 2019

一、数据收集

1.1 数据获取

数据源:阿里云-天池-淘宝数据分析

1.2 数据说明

UserBehavior.csv 是阿里巴巴提供的一个淘宝用户行为数据集,用于隐式反馈推荐问题的研究。

文件名称 说明 包含特征
UserBehavior.csv 包含所有的用户行为数据 用户ID,商品ID,商品类目ID,行为类型,时间戳

该数据集包含了2017年11月25日至2017年12月3日之间,有行为的约一百万随机用户的所有行为(行为包括点击、购买、加购、喜欢)。数据集的组织形式和MovieLens-20M类似,即数据集的每一行表示一条用户行为,由用户ID、商品ID、商品类目ID、行为类型和时间戳组成,并以逗号分隔。关于数据集中每一列的详细描述如下:

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

1.3 数据导入

由于数据量庞大,无法使用Excel直接处理,故本次使用数据库管理软件Navicat,导入了10W条数据集,进行分析处理。接下来是导入流程:

1.3.1 新建数据库

在这里插入图片描述

1.3.2 导入数据源

表–右键单击–导入向导–选择数据源–选择分隔符–选择导入记录条数与时间格式–接下来默认即可-开始耐心等待导入
在这里插入图片描述
在这里插入图片描述
这部分就不赘述啦,csv文件导入网上教程很多,大家可以搜索关键词 Navicat 导入csv

Nacivat使用心得:这是我自己在使用中遇到的问题,对表做操作以后刷新,居然没反应,后来我发现可以这样解决:打开表-随便选中某一列-右键-刷新,屡试不爽啊。

二、数据清洗

2.1 去除重复值

我们可以通过这三个字段:userid, itemid, timestamps 是否完全相同,来检查是否有相同的记录。也就是说,我们认为userid, itemid, timestamps这三个字段相当于这个表的主键,三者联合起来,完全定义一条独立的记录。
SQL查询语句如下:

SELECT userid FROM userbehavior
GROUP BY userid,itemid,timestamps
HAVING COUNT(userid) > 1;

SQL查询结果如下:


结果显示没有重复记录

2.2 检查缺失值

我们使用计数,既可以得到每一列的非空值数目
SQL查询语句如下:

-- 1.检查是否有缺失值
SELECT COUNT(userid),COUNT(itemid),COUNT(categoryid),count(behaviortype),COUNT(timestamps) 
FROM userbehavior

SQL查询结果如下:
每一列的记录条数都相同,所以没有缺失值
每一列的记录条数都相同,所以没有缺失值。

2.3 日期格式转换

在mysql中因为timestamp无法支持到毫秒,所以很多时候采用毫秒进行存储。那么如何将存储在数据库中Int类型的时间,如: 1344954515 ,转换成我们正常可以肉眼能看懂的时间格式呢?

知识点1:MySQL格式化时间戳函数:FROM_UNIXTIME()
知识点2:Mysql字符串截取总结:left()、right()、substring()、substring_index()

因为源数据格式不符合我们接下来的分析需求,所以需要进行数据转换:

2.3.1 处理timestamp时间列

SQL查询语句如下:

-- 2.1 处理timestamp时间列
-- 添加新列dates_time,返回日期和时间
ALTER TABLE userbehavior 
ADD COLUMN dates_time TIMESTAMP(0) NULL;
UPDATE userbehavior SET dates_time = FROM_UNIXTIME(timestamps);

2.3.2 添加新列dates,返回日期

SQL查询语句如下:

-- 2.2 添加新列dates,返回日期
-- 注意指定格式的大小写
ALTER TABLE userbehavior 
ADD COLUMN dates CHAR(10) NULL;
UPDATE userbehavior SET dates = FROM_UNIXTIME(timestamps,'%Y-%m-%d');

2.3.3 添加新列time,返回时间

SQL查询语句如下:

-- 2.3 添加新列time,返回时间
ALTER TABLE userbehavior 
ADD COLUMN time CHAR(10) NULL;
UPDATE userbehavior SET time = FROM_UNIXTIME(timestamps,'%H:%i:%S');-- 注意指定格式的大小写

2.3.4 添加新列time_hour,返回hour

SQL查询语句如下:

-- 2.4 添加新列time_hour,返回hour
-- 为了方便后面按照小时对用户习惯进行分析
ALTER TABLE userbehavior 
ADD COLUMN time_hour CHAR(10) NULL;
UPDATE userbehavior SET time_hour = LEFT(time,2);

2.3.5 数据展示

经过上面的数据清洗过程,源数据如下图所示:
阶段性清洗后的数据

2.4 剔除异常值

因为我们分析的时间范围是2017-11-25至2017-12-3 (9天),所以需要剔除不在这9天的数据:

SQL查询语句如下:

-- 3.剔除异常值
-- 排除日期不在2017-11-25至2017-12-3这9天的数据
DELETE FROM userbehavior
WHERE dates < '2017-11-25' or dates > '2017-12-03';

然后我们再次检查一下源数据:

-- 4.再次检查
SELECT MIN(dates),MAX(dates) FROM userbehavior;

SQL查询结果如下:
最小和最大日期均在范围内
结果显示,目前的日期范围已经符合我们的分析需求。

三、数据分析

3.1 分析思路

在这里插入图片描述

3.2 电商数据分析

知识点1:如何清楚易懂的解释“UV和PV"的定义? - 知乎
知识点2:跳出率
知识点3:重复购买率
知识点4:SEM流量四象限分析法

3.2.1 总体流量 – uv / pv / 收藏 / 加购 / 下单数量

SQL查询语句如下:

-- 1.总体 uv/点击/收藏/加购/下单数量
select 
COUNT(DISTINCT userid) AS 总用户数,
SUM(CASE WHEN behaviortype = 'pv'   THEN 1 ELSE 0 END ) AS 总点击数量,
SUM(CASE WHEN behaviortype = 'fav'  THEN 1 ELSE 0 END ) AS 总收藏数量,
SUM(CASE WHEN behaviortype = 'cart' THEN 1 ELSE 0 END ) AS 总加购数量,
SUM(CASE WHEN behaviortype = 'buy'  THEN 1 ELSE 0 END ) AS 总下单数量,
ROUND(SUM(CASE WHEN behaviortype = 'pv' THEN 1 ELSE 0 END )/COUNT(DISTINCT userid),0) AS 人均总点击数量
FROM userbehavior;

SQL查询结果如下:
在这里插入图片描述

3.2.2 每日流量 – uv / pv / 收藏 / 加购 / 下单数量

SQL查询语句如下:

-- 每日流量,按照日期分组
CREATE VIEW  web_traffic_day AS
select dates,
COUNT(DISTINCT userid) AS 每日用户数,
SUM(CASE WHEN behaviortype ='pv'   THEN 1 ELSE 0 END ) AS 点击数量,
SUM(CASE WHEN behaviortype ='fav'  THEN 1 ELSE 0 END)  AS 收藏数量,
SUM(CASE WHEN behaviortype ='cart' THEN 1 ELSE 0 END)  AS 加购数量,
SUM(CASE WHEN behaviortype ='buy'  THEN 1 ELSE 0 END)  AS 购买数量,
ROUND(SUM(CASE WHEN behaviortype = 'pv' THEN 1 ELSE 0 END )/COUNT(DISTINCT userid),0) AS 人均点击数量
FROM userbehavior
GROUP BY dates
ORDER BY dates;

SQL查询结果如下:
在这里插入图片描述
可视化:使用Excel展示
在这里插入图片描述

分析说明:11/25-12/01这七天网站的流量没有太大波动,但是,12/02 和12/03两天的各项日均流量指标显著大于前7天,推测可能是因为淘宝双12预热活动,使得浏览量大幅增加。

当然,还有一个可能:这两天刚好是周末。但是周末的影响可能并不大,比如同样是周末的11/25和1/26 ,各项流量指标和工作日无太大差异。

3.2.3 每时流量 – uv / pv / 收藏 / 加购 / 下单数量

SQL查询语句如下:

-- 每小时流量,按时刻分组
CREATE VIEW  web_traffic_hour AS
select time_hour,
COUNT(DISTINCT userid) AS 每日用户数,
SUM(CASE WHEN behaviortype ='pv'   THEN 1 ELSE 0 END ) AS 点击数量,
SUM(CASE WHEN behaviortype ='fav'  THEN 1 ELSE 0 END)  AS 收藏数量,
SUM(CASE WHEN behaviortype ='cart' THEN 1 ELSE 0 END)  AS 加购数量,
SUM(CASE WHEN behaviortype ='buy'  THEN 1 ELSE 0 END)  AS 购买数量,
ROUND(SUM(CASE WHEN behaviortype = 'pv' THEN 1 ELSE 0 END )/COUNT(DISTINCT userid),0) AS 人均点击数量
FROM userbehavior
GROUP BY time_hour
ORDER BY time_hour;

SQL查询结果如下:
在这里插入图片描述

可视化:使用Excel进行展示
在这里插入图片描述

分析说明:以点击数为例进行分析,上图显示,每天零点过后到2:00,网站点击量逐渐下降,在2-4点出现一天中点击量最低谷。应该是用户进入休息状态,直到凌晨5点,网站的点击量开始逐渐和上升,这符合一般用户的作息习惯。网站的点击量在10点左右产生一个小高峰,11-12点略有下降,这应该是用户午餐时间。下午13点-16点网站的点击量相对高于上午,其中在15点出现小高峰。17-18点网站点击量下降,可能是用户下班回家高峰导致。19点以后,网站点击量再次逐渐上升,在21点到达一天中的巅峰,22-23点逐渐缓慢下降,用户开始准备入睡,但是这段时间的平均点击量仍然较高。由此我们可以推断:一天当中,用户最活跃的时间段是下班以后。

3.2.4 行为流量转化漏斗

这部分分析的是用户点击 – 收藏和加购 - - 下单的购物环节中,流量的转化率。其中,因为 收藏和加购 两个行为没有先后之分,所以将这两个行为放在一起,二者合并为购物环节的一个路径进行分析。

SQL查询语句如下:

-- 用户行为流量转化
SELECT 
SUM(CASE WHEN behaviortype = 'pv'   THEN 1 ELSE 0 END) AS 总点击数量,
SUM(CASE WHEN behaviortype = 'fav'  OR behaviortype = 'cart' THEN 1 ELSE 0 END ) AS 总收藏和加购数量,
SUM(CASE WHEN behaviortype = 'buy'  THEN 1 ELSE 0 END) AS 总下单数量,

-- 计算加购收藏转化率
CONCAT(ROUND(SUM(CASE WHEN behaviortype = 'fav'  OR behaviortype = 'cart' THEN 1 ELSE 0 END )/SUM(CASE WHEN behaviortype = 'pv' THEN 1 ELSE 0 END) * 100,2),'%') AS pv2cart_or_fav,

-- 计算下单转化率
CONCAT(ROUND(SUM(CASE WHEN behaviortype = 'buy' THEN 1 ELSE 0 END)/SUM(CASE WHEN behaviortype = 'pv' THEN 1 ELSE 0 END)*100,2),'%') AS pv2buy

FROM userbehavior; 

最终得到用户购物行为各环节转化率,如下:
在这里插入图片描述
可视化:使用Excel展示
在这里插入图片描述
分析说明:《精益数据分析》一书指出,据2012年的一项研究表明,在整个互联网范围内,平均转化率为2.13%。

图中所示购买行为的转化率(pv2buy)为2.34%,高于行业平均值,说明淘宝用户行为的转化率较好。

3.2.5 复购率

复购率,指消费者对该品牌产品或者服务的重复购买次数,重复购买率越多,则反应出消费者对品牌的忠诚度就越高,反之则越低。

复购率 = 重复购买客户数量 / 客户样本数量

SQL查询语句如下:

-- 5.复购率
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 user_behavior_data;

SQL查询结果如下:
在这里插入图片描述
分析说明:计算得到复购率高达65.87%。

由此看来,顾客对淘宝店铺商品的喜爱程度较高,且满意度较高,所以愿意回购。也说明社会对产品本身的认可度比较高,说白了就是我们所卖的产品没有错,这个产品会让我们挣钱。

复购率高,我们就不用去花费高额的钱去拉新客户,因为获取一个新客户的成本比让一个老客户来购买的成本来要多。

3.2.6 跳失率

跳失率,显示顾客通过相应入口进入,只访问了一个页面就离开的访问次数占该入口总访问次数的比例。

SQL查询语句如下:

-- 6.跳失率=只浏览了一个页面的访客数/总访客数
-- Bounce Rate = Single Page Visitors / Total Visitors
select COUNT(*) from 
(
	SELECT userid from userbehavior 
	GROUP BY userid 
	HAVING COUNT(behaviortype)=1
)as t;

SQL查询结果如下:
在这里插入图片描述
分析说明:就这9天内而言,跳失率为零。说明没有用户只浏览了一个界面以后就离开,就像我们看短视频一样,这个视频对我们有着足够强烈的吸引力,让我们停留下来看完。由此可见淘宝的商品广告足够有创意或者吸引力。

但是此处缺乏用户在每个网页的停留时间,所以我们不进行网页驻留时间分析。

3.3 用户分析

3.3.1 用户行为user_behavior列转行数据

SQL查询语句如下:

-- 按照用户ID分组,用户行为user_behavior行转列数据
CREATE VIEW user_behavior_data AS
SELECT userid,
COUNT(behaviortype) AS 用户行为数总计,
sum(CASE WHEN behaviortype ='pv'   THEN 1 ELSE 0 END)  AS 点击,
sum(CASE WHEN behaviortype ='fav'  THEN 1 ELSE 0 END)  AS 收藏,
sum(CASE WHEN behaviortype ='cart' THEN 1 ELSE 0 END)  AS 加购,
sum(CASE WHEN behaviortype ='buy'  THEN 1 ELSE 0 END)  AS 购买
FROM userbehavior
GROUP BY userid
ORDER BY 用户行为数总计 DESC;

SQL查询结果如下:
视图user_behavior_data是按照列存储的数据格式
按照用户ID进行分组,得到视图user_behavior_data。将源数据user_behavior列转为行存储的数据格式,我们通过这个视图可以清楚地看到每个用户的行为(pv 、fav 、cart、buy)以及对应的行为数量,便于后面进行用户的RFM价值分类。

3.3.2 不同行为的用户数量

SQL查询语句如下:

-- 不同行为的用户数量
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 user_behavior_data;

SQL查询结果如下:
在这里插入图片描述
分析说明:产生点击的用户数有980名,最终购买的用户是671名,由此计算出购买转化率是68.4%

那么,这671名用户从点击到购买发生了什么?用户到底更偏向于哪一种购买方式呢?我们如何才能提高最终的购买转化率呢?接下来我们对用户购买的流程进行分析。

3.3.3 用户漏斗转化分析

从点击到下单购买,中间用户可能会收藏该商品,加购该商品,将这些可能性组合一下,我们认为,一共有四种购买路径:

(1)点击–直接购买

SQL查询语句如下:

-- 路径1:点击--直接购买
SELECT 
SUM(CASE WHEN 购买>0 THEN 1 ELSE 0 END) AS 购买用户数
FROM user_behavior_data
WHERE 加购=0 AND 收藏=0;

SQL查询结果如下:
在这里插入图片描述
可视化:使用Excel展示
在这里插入图片描述
分析说明:可以看出有7.45%的用户是直接购买的。

(2)点击–加购不收藏–购买

SQL查询语句如下:

-- 路径2:点击--加购不收藏--购买
SELECT 
SUM(CASE WHEN 加购>0 THEN 1 ELSE 0 END) AS 加购不收藏用户数,
SUM(CASE WHEN 购买>0 THEN 1 ELSE 0 END) AS 购买用户数
FROM user_behavior_data
WHERE 加购>0 AND 收藏=0;

SQL查询结果如下:
在这里插入图片描述
可视化:使用Excel展示
在这里插入图片描述

(3)点击–收藏不加购–购买

SQL查询语句如下:

-- 路径3:点击--收藏不加购--购买
SELECT 
SUM(CASE WHEN 收藏>0 THEN 1 ELSE 0 END) AS 收藏不加购用户数,
SUM(CASE WHEN 购买>0 THEN 1 ELSE 0 END) AS 购买用户数
FROM user_behavior_data
WHERE 加购=0 AND 收藏>0;

SQL查询结果如下:
在这里插入图片描述
可视化:使用Excel展示
在这里插入图片描述

(4)点击–加购且收藏–购买

SQL查询语句如下:

-- 路径4:点击--加购且收藏--购买
SELECT 
SUM(CASE WHEN 加购>0 THEN 1 ELSE 0 END) AS 加购且收藏用户数,
SUM(CASE WHEN 购买>0 THEN 1 ELSE 0 END) AS 购买用户数
FROM user_behavior_data
WHERE 加购>0 AND 收藏>0;

SQL查询结果如下:
在这里插入图片描述
可视化:使用Excel展示
在这里插入图片描述

分析说明:从上面的漏斗图中可以看出,用户浏览页面以后,仅有7.45%的人选择直接购买;有48.78%的人选择加购物车,然后下单购买;有12.04%的人选择收藏,然后下单购买;有25%的人选择收藏并且加购,然后下单购买。

这四种方式比较说明,加购物车购买是用户最喜欢的一种方式,其次喜欢的就是加购且收藏。推断原因可能是因为加购物车,用户可以享受满减等优惠或者折扣。所以,我们可以引导用户将商品加购物车,提高中间转化率。

3.3.4 用户RFM价值分析

RFM模型是衡量客户价值和客户创利能力的重要工具和手段。这三个要素构成了数据分析最好的指标:

  • 最近一次消费(Recency)
  • 消费频率(Frequency)
  • 消费金额(Monetary)

本数据集中不包括M数据,本次不考虑该指标。

(1)分析购买频率F

SQL查询语句如下:

-- 1.分析购买频率F
SELECT MAX(购买次数F),MIN(购买次数F) ,AVG(购买次数F)
FROM
(
	SELECT userid,COUNT(behaviortype) as 购买次数F
	FROM userbehavior
	WHERE behaviortype = 'buy'
	GROUP BY userid
	ORDER BY 购买次数F DESC
) AS t

SQL查询结果如下:
在这里插入图片描述
分析说明:用户购买次数,最多达到43次,哇真是疯狂的购物者;最少的是1次;平均每人购买了3次。

(2)分析购买间隔R

SQL查询语句如下:

-- 2.分析购买间隔R
SELECT MAX(购买间隔R),MIN(购买间隔R) ,AVG(购买间隔R)
FROM
(
	SELECT userid,DATEDIFF('2017-12-04',MAX(dates)) as 购买间隔R
	FROM userbehavior
	WHERE behaviortype = 'buy'
	GROUP BY userid
	ORDER BY 购买间隔R DESC
) AS t

SQL查询结果如下:
在这里插入图片描述
分析说明:用户购买间隔,也就是用户最后一次购买距今的时间间隔,可以看到R最大是9天,最小是1天,平均值是3.45天。

(3)整合R 和 F,创建视图

SQL查询语句如下:

-- 3.整合创建视图
CREATE VIEW user_RFM_model AS
SELECT 
	userid,
	COUNT(behaviortype) as 购买次数F,
	DATEDIFF('2017-12-04',MAX(dates)) as 购买间隔R
FROM userbehavior
WHERE behaviortype = 'buy'
GROUP BY userid
ORDER BY 购买次数F DESC,购买间隔R ASC

SQL查询结果如下:
在这里插入图片描述
可视化结果:使用Tableau可视化
在这里插入图片描述
使用 R=4F=4两条参考线,我们把散点图分为四个象限:

R F 用户类型
重要价值用户
重要发展用户
重要保持用户
重要挽留用户

注:高和低都是和平均值比较。
在这里插入图片描述

分析说明:利用RFM模型对用户进行分群,我们找出有价值的用户。这样我们就能对用户进行精确化运营:

对于第一象限的用户,即 消费频率高 且 近期有消费 的高价值用户,应该保持当前的情况;

对于第二象限的用户,即 消费频率不高 但 近期有消费的重要发展用户,应该用会员权益或者发放优惠券的形式促进消费次数;

对于第三象限的用户,即 消费频率不高 且 近期没有消费 的潜在用户,我们应该双管齐下,想办法争取;

对于第四象限的用户,即 消费频率高 但 近期没有消费 的重要保持用户,使用邮件推送、APP push的方法、活动短信提醒的方法召回。

3.4 商品流量分析

3.4.1 商品SKU 下单量 的帕累托分析

(1) 按照商品种类分组

按照商品种类分组,分析下单次数:

SQL查询语句如下:

-- 按照商品种类分组,分析下单次数
SELECT itemid, COUNT(itemid) as 下单次数
FROM userbehavior 
WHERE behaviortype='buy'
GROUP BY itemid 
ORDER BY 下单次数 DESC

SQL查询结果如下:
在这里插入图片描述
在Navicat右下角得到总记录数:
在这里插入图片描述
分析说明:如上表所示,我们发现,在被下单购买的1984件商品中,单个商品被购买的次数最多不超过4次,也就是说,其实没有出现卖得特别火爆的商品。

(2) 按照下单次数分组

按照下单次数分组,分析每种下单次数对应的商品种类(也就是下单次数的直方图):

SQL查询语句如下:

-- 按照下单次数分组进行分析
-- 做出直方图
create view item_buy AS
select t.下单次数,COUNT(t.itemid) AS 商品数量
FROM
(
	SELECT itemid, COUNT(itemid) AS 下单次数
	FROM userbehavior 
	WHERE behaviortype='buy'
	GROUP BY itemid ORDER BY 下单次数 DESC
) AS t
GROUP BY t.下单次数 
ORDER BY t.下单次数 DESC;

SQL查询结果如下:
在这里插入图片描述
分析说明:可以看到下单量最大为4,最小为1。下单量为1的商品高达1881种,占比达到98.8%(销售记录大于零商品总共有1984种)。

(3)帕累托结果可视化

使用Tableau,可视化展示商品的下单量:
在这里插入图片描述
分析说明:上图显示的结果并不符合二八定律,80%的下单量并不是由20%的商品带动的。这也说明了,互联网环境下,以淘宝为代表的电商平台,其商品售卖主要是依靠商品的长尾效应,并非爆款商品的带动。

3.4.2 商品SKU 浏览量 的帕累托分析

(1) 按照商品种类分组

按照商品种类分组,分析浏览次数:

SQL查询语句如下:

-- 按照商品种类分组,分析浏览次数
SELECT itemid, COUNT(itemid) as 浏览次数
FROM userbehavior 
WHERE behaviortype='buy'
GROUP BY itemid 
ORDER BY 浏览次数 DESC 

SQL查询结果如下:
在这里插入图片描述
在Navicat右下角得到总记录数:
在这里插入图片描述
分析说明:如上表所示,我们发现,在所有被浏览过的60592件商品中,单个商品的浏览次数最多39次,最少1次。

那大部分的商品都被浏览了多少次呢?接下来我们绘制浏览次数的直方图进行分析。

(2) 按照浏览次数分组

按照浏览次数分组,分析每种浏览次数对应的商品种类(也就是浏览次数的直方图):

SQL查询语句如下:

-- 按照浏览次数分组,分析商品种类
create view item_pv AS
select t.浏览次数,COUNT(t.itemid) AS 商品数量
FROM
(
	SELECT itemid, COUNT(itemid) as 浏览次数
	FROM userbehavior 
	WHERE behaviortype='PV'
	GROUP BY itemid 
	ORDER BY 浏览次数 DESC
) AS t
GROUP BY t.浏览次数 
ORDER BY t.浏览次数 DESC;

SQL查询结果如下:
在这里插入图片描述
续上图
在这里插入图片描述
分析说明:上面我们看到,只有1件商品被浏览了39次,高达45718件商品(占比75%)都只被顾客浏览了一次。

(3)帕累托结果可视化

使用Tableau,可视化展示商品的浏览量:
在这里插入图片描述
分析说明:上图显示的结果并不符合二八定律,80%的浏览量是由66%的商品带动的。这还是表现了互联网环境下商品的长尾效应。

3.2.3.3 商品流量四象限分析

通过象限法,对浏览量下单量两个维度指标进行分析,分析出推广环节中可能存在的问题。

使用Tableau,可视化展示商品的浏览量 和 下单量 散点图:
在这里插入图片描述
使用浏览次数=15 和 下单次数 =3 两条参考线,我们把商品流量的散点图分为四个象限:

第Ⅰ象限:浏览次数高,购买量高。满意消费者。说明网民比较容易看到相关商品的广告,也比较愿意点购买该商品,即“对的信息传递给了对的人,对的人找到了他要的信息”。

第Ⅱ象限:购买量高,浏览量低。糟糕的广告。即很少人点击广告,但是点击广告的人中,有不少人购买了。说明广告信息传播准确,网站层级也不会有多大问题,主要问题应该是出现在广告投放层级,例如排名不好或创意不够吸引人。

第Ⅲ象限:购买量低,浏览量低。不精准人群。可以简单理解为很少人点广告,就算点了广告,也很少人购买。说明广告没有很好的展现到目标人群面前,或者广告创意吸引力不够。另外在网站层级也可能出现问题。

第Ⅳ象限:浏览量高,购买量差。失望的消费者。可以简单理解为点击广告的人很多,但都不产生购买。说明可能是广告创意夸大诱导网民点击;另外,购买量差的矛盾应该指向网站和产品(服务)本身,例如网站打开速度慢,着陆页建设差,自身产品毫无优势等。

分析说明:上图我们可以发现,大部分商品集中分布在第三象限。说明针对这部分商品,我们应该提高广告的创意吸引力 和 改进算法,精准广告投放人群。

其他象限的商品此处不作赘述。

四、数据报告

4.1 结论

通过对这份淘宝用户行为数据的分析,我们可以得到以下结论:

1.每日流量:用户在周末和工作日的点击/收藏/加购/购买行为没有太大波动;
2.每时流量:晚上7点以后是用户活跃度最高的时间段,这段时间内点击/收藏/加购/购买行为都明显高于其他时间段;
3.转化率:浏览到购买的用户转化率(pv2buy)是68.4%,由此看出愿意购买的用户数量很多;但是,浏览到购买的行为仅为转化率2.34%
4.购买方式:用户最喜欢的购买方式是加购物车购买,其次喜欢的就是加购且收藏。
5.用户客群:利用RFM模型对用户进行分群,我们找出有价值的用户;
6.商品种类:无论是点击量还是销售量,主要依靠商品的长尾效应。

4.2 建议

1.周末相对于工作日而言,用户的空闲时间增多,这是一个突破口。在这个前提下,可以考虑在周末开展促销活动,挖掘部分用户,引导用户消费;
2.在晚上7点以后开展营销活动,活动比较容易触达用户,可以提高网站流量;
3.针对提高用户浏览-购买行为转化率(仅为2.3%),建议两步走:

  • 第一步,提高中间行为 收藏和加购 的流量,建议卖家主动引导用户收藏加购,比如在商品 主图 或者 详情页 推出“收藏加购有小礼品赠送 或 收藏加购会提前发货”等吸引新用户,还可以让客服人员主动告知顾客;当然也可以通过短信营销唤醒老顾客,鼓励他们收藏。
  • 第二步,提高最终行为 购买 的流量,前面有了收藏加购率,商家就要增添宝贝的亮点,搞搞促销活动或者提升售前售后服务,满足不同用户的需求,直击用户的痛点,刺激他们的购买欲望,这样水到渠成,成交自然就上来了。

4.使用RFM模型对客户群进行划分,对不同的客群采用差异化策略管理,达到精准营销:

  • 对于重要价值用户,关注并保持, j继续提升这部分客群满意度,增加留存;
  • 对于重要发展客户和保持用户,及时通过邮件、短信或者客户端提醒,召回客户;或者,用会员权益或者发放优惠券的形式促进消费次数;
  • 对于重要挽留客户,一方面应该优化推荐算法,分析其购物习性和商品偏好,精准投放广告,让用户看到喜欢的商品;另一方面,应该提升广告创意和吸引力,以唤醒他们的购买意愿。

5.长尾效应的确能带来一部分收益,但是,繁多的种类对于商家来说其实是一种经营负担。二八定律告诉我们,商家其实可以通过打造爆款商品来获利。打造爆款商品具体的建议是:品控上提高产品质量,宣传上增大力度(直播、微淘、引流到其他平台),展现上突出产品优势等(主图、详情页)。

发布了8 篇原创文章 · 获赞 4 · 访问量 2382
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览