淘宝用户行为数据分析（Mysql）

作者: 对三zzzzzzz | 来源:发表于2020-03-27 11:45 被阅读0次

本文结构：

本文结构.png

一、理解数据

1、总体介绍

①、数据来源：

本数据集数据来源于阿里巴巴天池：https://tianchi.aliyun.com/dataset/dataDetail?dataId=46

②、数据时间：

2014年11月18日至2014年12月18日

③、数据简介：

数据集由8477名随机用户共1048575条行为数据，数据集的每一行表示一条用户行为，由用户ID、商品ID、行为类型、地理位置、商品类目ID和时间组成。

总记录1000万条,因电脑性能不足，难以处理这么多数据，故取100万条作为样本进行分析。

2、字段介绍

该数据集包括6个字段，分别为用户ID，商品ID，行为类型、地理位置、商品类目ID和时间，其中行为类型包括浏览，收藏，加入购物车，购买四种类型，分别用数字1/2/3/4代替，具体如下：

字段说明

行为类型说明

二、分析思路

1、提出问题：

本次分析的目的是想通过对淘宝用户行为数据的分析，为以下问题提供解释或改进建议：

① 、分析用户使用APP过程中的常见电商指标，了解运营现状，查看各环节的流失率，并找到需要改进的环节；

② 、研究用户在不同维度下的行为规律，了解用户行为特征，优化运营策略；

③ 、研究用户价值，针对不同价值用户进行精细化的运营，；

④ 、研究用户生命周期，针对不同周期用户采取不同的运营策略。

2、数据探索：

该数据集为电商领域的数据，包括用户，商品，商品类别，用户行为，行为地址、时间六个维度。因为是电商数据，所以得先了解相关的电商运营基础指标，指标如下：

电商数据分析基本指标体系.png

了解了电商基础运营指标后，结合本数据集看看数据能做的分析包含哪些方面：

①、基于user_id、behavior_type等字段数据可以分析PV、UV、PV/UV、跳失率、总订单量等整体运营指标；

②、基于time字段可以和behavior_type等字段，可以分析用户行为特征；

③、基于time字段极其计算字段等，结合RFM模型可以给用户价值打标签；

④、基于AARRR模型，可以分析用户生命周期，划分不同用户所处周期阶段。

3、分析思路：

可查看本文结构中的模型建立及可视化部分

三、清洗数据

导入数据：
通过数据库管理工具Navicat将数据导入Mysql本地数据库。如果觉得工具太慢，可以直接CMD命令将数据导入数据库或者其他办法。
可参考方法：
https://blog.csdn.net/L_up_up_up/article/details/89514877）#查看导入方法
https://blog.csdn.net/dvd_sun/article/details/87778577 # 解决导入太慢的问题
https://blog.csdn.net/weixin_44166997/article/details/94736499 # 查看python环境导入方法

原始数据
备注：为保护源数据，建议复制表，后续操作都在副本表里进行操作为宜。

1、选择子集

由于位置字段user_geohash存在大量空值且加密处理过，无法对该字段进行分析，故直接删除该字段，后续也不对此字段进行分析，此处直接利用Navicat工具进行删除。

删除位置后的数据集

2、列名重命名

数据集列名完整可用，故不做修改

查看字段名

3、重复值处理

SELECT *,count(*)
FROM userbehavior
GROUP BY user_id
        ,item_id
        ,behavior_type
        ,item_id
        ,time
HAVING COUNT(user_id)>1;

存在重复记录

查处的数据有重复数据，但因为行为记录最小粒度为小时，而同一个用户的同一个行为在同一个小时内是可能存在多次的，所以这里不对数据进行去重处理。

4、异常值处理

本数据集的异常值只可能存在于time字段和behavior_type，所以检查这两个字段有无异常值，具体代码如下：

#异常值检查
#行为类型
SELECT DISTINCT behavior_type
FROM userbehavior；
#时间周期
SELECT MIN(time)
      ,MAX(time)
FROM userbehavior；

行为类型

时间周期

查询结果：
没有异常值需要处理

5、缺失值处理

因为导入数据的时候都设置了NOT NULL，所以是没有缺失值的，这里可以用代码验证一下：

# 验证是否缺失值
SELECT COUNT(user_id)
      ,COUNT(item_id)
      ,COUNT(behavior_type)
      ,COUNT(item_category)
      ,COUNT(time)
FROM userbehavior;

查询验证无缺失值

确认无NULL值。

6、数据一致化处理

(1)、time列包含日期和时间，为方便后续分别针对日期和小时维度的分析，将此字段拆分成两个字段，一个记录是日期(date)，一个是时间(time)，代码实现过程如下：

# 1.新增一列date，用于储存日期值
ALTER TABLE userbehavior ADD COLUMN date VARCHAR(20) NOT NULL AFTER time;

#2.复制time列到date列
UPDATE userbehavior
SET date = time

#3.截取日期
UPDATE userbehavior
SET date = DATE_FORMAT(date,'%Y-%m-%d')

#4.截取时间
UPDATE userbehavior
SET time = DATE_FORMAT(time,'%H')
#备注：可以实现截取操作的方法很多，可以用时间函数也可以用字符串函数

(2)、behavior_type 列的四种行为类型分别用 1，2，3，4 表示点击、收藏、加购物车、购买四种行为，为了便于查看数据，将1，2，3，4替换为 ‘pv'、’fav‘，’cart'，‘buy' ，代码实现过程如下：

# 修改behavior_type值，方便查看
#1.修改数据
UPDATE userbehavior
SET behavior_type = (CASE 
                     WHEN behavior_type =1 THEN 'pv'
                     WHEN behavior_type =2 THEN 'fav'
                     WHEN behavior_type =3 THEN 'cart'
                     WHEN behavior_type =4 THEN 'buy'
                     ELSE '其他'
                     END )；
# 2.验证修改数据
SELECT DISTINCT behavior_type
FROM userbehavior

验证修改数据

验证结果：修改无误。

7、排序

这里的数据排序与否意义不大，故此环节可忽略，若后续有需要再进行排序即可。

最终清洗完后的数据为：

清洗后的最终数据

四、构建模型

1、总体运营指标：

(1)、流量指标：

①、计算页面访客数(pv)、独立访客数(uv)、人均点击数(pv/uv):

查询代码：

 SELECT COUNT(*) AS pv
       ,COUNT(DISTINCT user_id) AS uv
       ,COUNT(*)/COUNT(DISTINCT user_id) AS 'pv/uv'
 FROM userbehavior
 WHERE behavior_type = 'pv'

查询结果：

月维度流量指标
现象描述/分析：
页面访客：987911次，独立访客8477，人均点击次数117次/人；
117/30= 3.9，也就是说日人均点击次数大概为4次/人/天；
孤立数据，无法展开进一步分析。

②、每日流量指标变化趋势：

代码查询

SELECT date AS 日期
      ,COUNT(*) AS pv
      ,COUNT(DISTINCT user_id) AS uv
      ,COUNT(*)/COUNT(DISTINCT user_id) AS 人均页面访问数
FROM userbehavior
WHERE behavior_type = 'pv'
GROUP BY date 
ORDER BY date ASC;

日维度流量指标变化

用图表表示，观察走势：

流量指标随日期的变化
现象描述/分析：
pv/uv指标呈正相关关系；
指标大部分时间走势稳定，2014-12-11开始上升，至2014-12-12达到峰值，2014-12-13结束回归正常水平；
双十二活动的影响无疑是明显的。

(2)、AARRR漏斗转化率

①、按照页面访客计算漏斗转化率

查询代码：

SELECT behavior_type
      ,COUNT(*)
FROM userbehavior
GROUP BY behavior_type;

查询结果：

image.png
计算：
因为在购物环节中收藏和加入购物车两个环节没有先后之分，所以将这两个环节可以放在一起作为购物环节的一步，最终得到用户购物行为各环节转化率，如下：

image.png

注释：转化率是以pv为基数计算；占比是指到达下一环节的人数占上一环节人数的比例。

可视化展示：

漏斗转化
现象描述/分析：
从转化率来看：浏览- 收藏/加购转化率仅为5%，总体购买转化率为1%，这说明有非常多的用户在浏览后未进行下一步操作，有非常多的无效点击；
从占比来看：浏览-加购/收藏环节转化率很低，收藏/加购-购买的转化率也只有20%，说明有很多用户加入了购物车/收藏，但并未真正购买。

②、按照独立访客计算漏斗转化率

查询代码：

 SELECT behavior_type
       ,COUNT(DISTINCT user_id)
FROM userbehavior
GROUP BY behavior_type
ORDER BY behavior_type DESC;

查询结果：

image.png
计算

image.png
可视化展示：

image.png
现象描述/分析：
由上图可知，基于独立访客数的漏斗转化，用户有浏览后收藏/加购的习惯，用户流失主要发生购物车环节。
备注：
为什么收藏/加购人数会大于浏览人数？
答：从数据集结构来看，是因为同一个浏览行为，可能既收藏了，也加购了。

下面查询一下页面跳失率，作为初步验证：

(3)、跳失率

①、浏览页跳失率

浏览页跳失率= 只访问一次就离开的人数/总用户数（已知UV = 8477）

查询代码：

SELECT COUNT(DISTINCT user_id) AS '只浏览无购买'
FROM userbehavior
WHERE user_id IN (SELECT DISTINCT user_id
                  FROM userbehavior 
                  WHERE behavior_type = 'pv')
AND user_id NOT IN (SELECT DISTINCT user_id
                    FROM userbehavior
                    WHERE behavior_type = 'fav')
AND user_id NOT IN (SELECT DISTINCT user_id
                    FROM userbehavior
                    WHERE behavior_type = 'cart')
AND user_id NOT IN (SELECT DISTINCT user_id
                    FROM userbehavior
                    WHERE behavior_type = 'buy');

查询结果：

image.png
计算
浏览页跳失率 = 1588/8477 = 18.7%

②、关键页跳失率

有收藏、加购行为但无购买

查询代码：

SELECT COUNT(DISTINCT user_id) AS '有意向无购买'
FROM userbehavior
WHERE user_id IN (SELECT DISTINCT user_id
                  FROM userbehavior
                  WHERE behavior_type = 'fav')
  AND user_id IN (SELECT DISTINCT user_id
                  FROM userbehavior
                  WHERE behavior_type = 'cart')
AND user_id NOT IN (SELECT DISTINCT user_id
                    FROM userbehavior
                    WHERE behavior_type = 'buy');

查询结果：

关键页面跳失人数
计算：
关键页跳失率 = 636/8477 = 7.5%

现象描述/分析：
浏览页跳失率为18.7%，关键页跳失率为7.5%，跳失率都不高，说明用户号停留时间较久，商品对用户比较有吸引力，这也验证了前面基于独立访客计算的用户行为漏斗转化率。但是基于浏览量的漏斗转化效率却不高，是因为什么呢，这里由于缺失数据验证，不再做进一步分析。

(4)、订单指标：

(1)、成交量

①、先看总体成交量：

用户行为类型为buy即视为一个订单

查询代码：

SELECT COUNT(behavior_type) AS 总成交量
FROM userbehavior
WHERE behavior_type = 'buy';

查询结果

image.png

②、再看每日成交量走势

查询代码：

SELECT date
      ,COUNT(behavior_type) AS 总成交量
FROM userbehavior
WHERE behavior_type = 'buy'
GROUP BY date
ORDER BY date;

查询结果：

每日成交量
可视化展示：

image.png
现象描述/分析：
订单量在大部分时间都保持平稳，而2014-12-12这天有爆发式的增长，造成这一现象的原因是双十二电商大促销活动所导致，与前面的流量指标走势分析，可以进行相互验证。

(2)、人均购买次数

①、先看总体

查询代码：

SELECT COUNT(behavior_type) AS 总订单量
      ,COUNT(DISTINCT user_id) AS 总用户数
      ,COUNT(behavior_type)/COUNT(DISTINCT user_id) 人均购买次数
FROM userbehavior
WHERE behavior_type = 'buy';

查询结果：

人均购买次数

②、再看每日

查询代码：

SELECT date
      ,COUNT(behavior_type) AS 总订单量
      ,COUNT(DISTINCT user_id) AS 总用户数
      ,COUNT(behavior_type)/COUNT(DISTINCT user_id) 人均购买次数
FROM userbehavior
WHERE behavior_type = 'buy'
GROUP BY date
ORDER BY date;

查询结果：

每日订单量
可视化展示：

每日订单量
现象描述/分析：
在2014-11-18到2014-12-18这一个月的时间内，平均一个用户购买了2.3次
每天的订单量与流量指标的日期走势也是一致的。

(3)、复购率

①、总体复购率

查询代码：

#复购率计算
# 至少成交一单的人数 
SELECT COUNT(DISTINCT user_id)  AS 成交人数
FROM userbehavior
WHERE behavior_type = 'buy';
# 查询结果：4330

# 购买大于1次的人数 
SELECT COUNT(DISTINCT user_id) AS 复购人数
FROM(SELECT user_id
           ,COUNT(behavior_type) AS 购买次数
     FROM userbehavior
     WHERE behavior_type = 'buy'
     GROUP BY user_id) AS a
WHERE 购买次数 > 1;
# 查询结果：2295

查询结果：
如上
计算：
复购率 = 购买大于1次的人数/至少成交一单的人数 = 2295/4330 = 53%

那么是哪些商品被重复购买的次数最多？哪些用户进行了更多的重复购买呢？

②、商品品类复购排行榜：

按商品品类分组统计被购买次数，降序排列取前十观察

查询代码：

# 商品品类复购排行榜
SELECT item_category
      ,COUNT(behavior_type) AS 购买次数
FROM userbehavior
WHERE behavior_type = 'buy'
GROUP BY item_category
ORDER BY COUNT(behavior_type) DESC
LIMIT 10；

查询结果：

商品品类复购排行榜
可视化展示：

商品品类复购排行榜
现象描述/分析：
可根据商品品类属性进行研究和下钻，优化商品结构。但这里因为商品品类商品id均为数值型，所以这里无法对商品品类进行分析，也无法下钻。

③、用户复购排行榜：

按用户id分组统计购买行为为buy的次数，降序排列取前十观察

查询代码：

SELECT user_id
      ,COUNT(behavior_type) AS 购买次数
FROM userbehavior
WHERE behavior_type = 'buy'
GROUP BY user_id
ORDER BY COUNT(behavior_type) DESC
LIMIT 10

查询结果：

用户购买排行榜

-可视化展示：

用户购买排行榜

现象描述/分析：
显然以上用户对于平台的忠诚度是比较高的，对于平台价值贡献度也是最高的，可以开发用户信息库，收集详实的用户资料，追踪记录顾客的交易情况，收集用户画像，或者线上组建VIP客户微信群、短信通知系统等，针对这些用户的购买偏好进行更为精准的运营营销。

2、用户行为特征分析：

(1)、用户行为时间的特征：

①、按日期粒度：

查询代码：

SELECT date
      ,COUNT(*) AS '行为总数'
      ,SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) AS '点击次数'
      ,SUM(CASE WHEN behavior_type = 'fav' THEN 1 ELSE 0 END) AS '收藏次数'
      ,SUM(CASE WHEN behavior_type = 'cart' THEN 1 ELSE 0 END) AS '加购次数'
      ,SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END) AS '购买次数'
      ,COUNT(DISTINCT user_id) AS '总用户数'
      ,COUNT(*)/COUNT(DISTINCT user_id) AS '人均行为次数'
      ,CONCAT(ROUND(SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') AS '点击数占比'
      ,CONCAT(ROUND(SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') AS '成交数占比'
FROM userbehavior
GROUP BY date
ORDER BY date;

查询结果

日期维度用户行为查询结果
可视化展示

日期维度用户行为走势
现象描述/分析：
按日期粒度来看，用户活跃度与总行为属是正相关的关系，大部分走势平稳，双十二电商大促销活动这天各项指标暴增。
进一步观察一下浏览量和成交量的关系，如下图：

日期维度用户点击占比和成交占比的关系
现象描述/分析：
可以明显看到大部分日期里点击占比和成交占比保持稳定且正相关，但双十二这天却相反，这是因为经过前面的筛选，当天的购买行为十分有针对性，从而导致指标形成这样的变化。

②、按周粒度

检查样本时间范围：

查询代码：

# 查看日期所对应的星期数
SELECT date,DATE_FORMAT(date,'%W')
FROM userbehavior
GROUP BY date
ORDER BY date

查询结果

查看日期对应的星期

查询结果发现：
数据集中2014-11-18到2014-11-23，2014-12-15到2014-12-18均不满一周，所以周粒度的样本采集截取2018-11-24到2014-12-14中间的三个完整周进行统计分析，具体过程如下：

查询代码

SELECT DATE_FORMAT(date,'%W') AS '星期'
      ,COUNT(*) AS '行为总数'
      ,SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) AS '点击次数'
      ,SUM(CASE WHEN behavior_type = 'fav' THEN 1 ELSE 0 END) AS '收藏次数'
      ,SUM(CASE WHEN behavior_type = 'cart' THEN 1 ELSE 0 END) AS '加购次数'
      ,SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END) AS '购买次数'
      ,COUNT(DISTINCT user_id) AS '总用户数'
      ,COUNT(*)/COUNT(DISTINCT user_id) AS '人均行为次数'
      ,CONCAT(ROUND(SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') AS '点击数占比'
      ,CONCAT(ROUND(SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') AS '成交数占比'
FROM userbehavior
WHERE date BETWEEN '2014-11-24' AND '2014-12-14'
GROUP BY DATE_FORMAT(date,'%W')
ORDER BY DATE_FORMAT(date,'%W');

查询结果

image.png
可视化展示

周维度用户行为走势
现象描述/分析：周一至周日
一周中大部分时候用户活跃度较为平稳，周五开始上升。查看双十二当天也是周五，所以，以周维度的统计算是异常值，数据源有限，所以对此不再展开深入分析。
同样我们也看一下浏览率与成交率的关系，如下图：

点击占比和成交占比的关系

可以看到整体走势跟日期粒度的是一致的，不同的地方发生在周五这一天，这是因为双十二这天也是周五。

③、按小时粒度

查询代码

SELECT time
      ,COUNT(*) AS '行为总数'
      ,SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) AS '点击次数'
      ,SUM(CASE WHEN behavior_type = 'fav' THEN 1 ELSE 0 END) AS '收藏次数'
      ,SUM(CASE WHEN behavior_type = 'car' THEN 1 ELSE 0 END) AS '加购次数'
      ,SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END) AS '购买次数'
      ,COUNT(DISTINCT user_id) AS '总用户数'
      ,COUNT(*)/COUNT(DISTINCT user_id) AS '人均行为次数'
      ,CONCAT(ROUND(SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') AS '点击数占比'
      ,CONCAT(ROUND(SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') AS '成交数占比'
FROM userbehavior
GROUP BY time
ORDER BY time;

查询结果

时间维度用户行为查询结果
可视化展示

时间维度用户行为走势
现象描述/分析：
-各指标之间呈正相关的关系；
每日0点到5点用户活跃度快速降低，降到一天中的活跃量最低值，6点到10点用户活跃度快速上升，10点到18点用户活跃度较平稳，18点到23点用户活跃度快速上升，达到一天中的最高值。
同样也从浏览率和成交率的关系对以上结论进行验证

点击率和成交率的关系

分析：
现象描述：0-5点点击率下降，成交率跟着下降；9-16点，点击率下降而成家率上升；17-23点，点击率上升而成家率下降。
结论：晚间用户使用淘宝更偏向于浏览，白天尤其是中午左右的时段，购买行为的比率相对一天中最高，用户使用淘宝用于购买的目的性最强。

(2)、用户商品偏好特征：

查询代码

SELECT item_id
      ,SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) AS '点击次数'
      ,SUM(CASE WHEN behavior_type = 'fav' THEN 1 ELSE 0 END) AS '收藏次数'
      ,SUM(CASE WHEN behavior_type = 'cart' THEN 1 ELSE 0 END) AS '加购次数'
      ,SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END) AS '购买次数'
      ,CONCAT(ROUND(SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END)/COUNT(behavior_type)*100,2),'%') AS '成交占比'
FROM userbehavior
GROUP BY item_category
ORDER BY SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) DESC ;

查询结果：

按商品类目查询用户行为
可视化展示
按照商品品类区分，根据点击次数和购买次数两个维度将所有商品划分到四个象限：

image.png
现象描述/分析
第一象限类：
点击数高购买数高。说明此类产品的刚需性强，品牌和商品种类丰富，用户在较高的需求下也有较多的选择空间。
第二象限类：
点击数低购买数高。用户购买决策非常迅速，用户对于该类产品的需求量大，说明该类产品选择性少、或者某几个品牌或产品已经形成垄断态势，或者产品的差异性小等的原因，用户不愿花过多精力去重复挑选。
第三象限类：
点击数低购买数低。绝大部分种类产品都集中在该象限内，商品存在相对的替代性，用户很难集中在某个子类精心挑选大量购买，而是跳跃式选购。
第四象限类：
点击数高购买数低。这类产品需求弹性较大，用户购买存在随机性

(3)、用户行为路径上的特征：

从AARRR漏斗转化模型来看，用户购买商品可分为如下几类过程：
直接购买
浏览后购买
浏览加购购买
浏览收藏购买
浏览收藏加购购买
下面通过建立‘购买路径’指标对购买路径特征进行分析，具体过程如下：

# 1、创建用户购买路径表视图(表结构：user_id,pv,fav,cart,buy)
CREATE VIEW 用户购买路径表 AS
SELECT *
FROM (SELECT user_id
            ,SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) AS '点击'
            ,SUM(CASE WHEN behavior_type = 'fav' THEN 1 ELSE  0 END) AS '收藏'
            ,SUM(CASE WHEN behavior_type = 'cart' THEN 1 ELSE  0 END) AS '加购'
            ,SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE  0 END) AS '购买'
        FROM userbehavior
        GROUP BY user_id
                   ,item_id
                   ,date) AS 购买路径
WHERE 购买 >=1; # 大于0的原因是必须要有成交

# 2、将所有用户行为标准化(存在该类行为则标记为1，否则为0)
CREATE VIEW 用户行为标准化表 AS
SELECT user_id
      ,(CASE WHEN 点击 >=1 THEN 1 ELSE 0 END) AS 点击
      ,(CASE WHEN 收藏 >=1 THEN 1 ELSE 0 END) AS 收藏
      ,(CASE WHEN 加购 >=1 THEN 1 ELSE 0 END) AS 加购
      ,(CASE WHEN 购买 >=1 THEN 1 ELSE 0 END) AS 购买
FROM 用户购买路径表

#3、建立标准化指标表(user_id,购买路径类型)
CREATE VIEW 标准化指标表 AS
SELECT user_id
    ,CONCAT(点击,收藏,加购,购买) AS 购买路径类型
FROM (SELECT user_id
            ,(CASE WHEN 点击 >=1 THEN 1 ELSE 0 END) AS 点击
            ,(CASE WHEN 收藏 >=1 THEN 1 ELSE 0 END) AS 收藏
            ,(CASE WHEN 加购 >=1 THEN 1 ELSE 0 END) AS 加购
            ,(CASE WHEN 购买 >=1 THEN 1 ELSE 0 END) AS 购买
      FROM 用户购买路径表) AS 标准化表
GROUP BY user_id;

# 4、统计各指标用户数
SELECT 购买路径类型
      ,COUNT(DISTINCT user_id) AS 用户数
FROM 标准化指标表
GROUP BY 购买路径类型

查询结果（最终）

最终查询结果
整理计算：

购买路径分布
可视化展示：

占比
现象描述/分析：
现象：直接购买的购买行为最多，占比58%，而浏览购买的行为第二，占比35%；其他几种行为则共有7%的比例。
分析：大部分购买者都喜欢直接购买商品，而非仔细挑选商品。

(4)、用户复购率特征：

查询代码

# 1.复购率分析
#创建视图，计算不同用户的购买次数
CREATE VIEW 用户复购次数 AS
SELECT user_id
      ,COUNT(behavior_type) AS 购买次数
FROM userbehavior
WHERE behavior_type ='buy'
GROUP BY user_id
ORDER BY 购买次数 DESC;

# 2.计算不同购买次数的用户数
SELECT 购买次数
      ,COUNT(DISTINCT user_id) AS 用户数
FROM 用户复购次数
GROUP BY 购买次数
ORDER BY 购买次数;

查询结果

购买次数视图
按购买次数分组统计用户数
可视化展示：
看分布：

image.png

看占比：
由上图可知用户购买次数大部分集中在10次以内，将用户数按购买1次，购买2-5次，购买6-10次，购买11次以上将数据分组，结果如下：

分组
各购买次数所占比例如图所示：
image.png
现象描述/分析：
现象：仅购买一次的客户占比为47%，2-5次的客户占比为46.07%，其余客户占比约为7%，整体复购率为53%。
分析：绝大多数客户在淘宝上购买次数少于5次，开发空间较大。

3、基于RFM模型分析用户价值：

(1)、RFM模型简介

image.png

(2)、分类维度及标准

image.png

(2)、实现过程

参考RFM模型，结合该数据集的字段，通过R（Recency）和F（Frequency）两个维度把客户分成四大类：

①、建立视图，统计用户R值和F值：

R统计基准日期为：2014-12-18

查询代码

# 用户价值分析
# 用户id、R/F统计视图
CREATE VIEW 用户RF统计视图 AS
SELECT user_id
      ,DATEDIFF('2014-12-18',MAX(date)) AS R
      ,COUNT(behavior_type) AS F
FROM userbehavior
WHERE behavior_type = 'buy'
GROUP BY user_id；

查询结果

用户id、R/F统计视图

②、给RF分组，给用户打分

A、数值分组
查看最大值和平均值，确定分组标准

查询代码

SELECT MAX(R)
      ,MAX(F)
FROM 用户RF统计视图

查询结果

MAX
将R/F按四组：
R值：0-7,8-15,16-23,24-30分别对应4分、3分、2分、1分
F值：1-5,6-10,11-20,21-82分别对应1分、2分、3分、4分

B、为用户价值打分
将R、F分组后按照上面的标准进行打分

查询代码：

CREATE VIEW 用户价值打分 AS
SELECT user_id
      ,(CASE WHEN R BETWEEN 0 AND 7 THEN 4
             WHEN R BETWEEN 8 AND 15 THEN 3
             WHEN R BETWEEN 16 AND 23 THEN 2
             WHEN R BETWEEN 24 AND 30 THEN 1
             ELSE 0
             END) AS R_score
        ,(CASE WHEN F BETWEEN 1 AND 5 THEN 1
               WHEN F BETWEEN 6 AND 10 THEN 2
               WHEN F BETWEEN 11 AND 20 THEN 3
               WHEN F BETWEEN 21 AND 82 THEN 4
               ELSE 0
               END) AS F_score
FROM 用户RF统计视图;

查询结果

用户价值打分

C、计算R_score和F_score的平均值，确定评分标准
按RF的平均值打标签，然后将R/F的评分跟平均值比较，比如重要价值客户两项评分必须都高于平均分：

查询代码：

SELECT AVG(R_score),AVG(F_score)
FROM 用户价值打分

查询结果

AVG

③、给不同价值用户贴上价值标签：

代码过程

CREATE VIEW 用户标签表 AS
SELECT user_id
      ,(CASE WHEN  R_score  > 3.09 AND F_score >1.07 THEN '重要价值客户'
              WHEN R_score  > 3.09 AND F_score <= 1.07 THEN '重要保持客户'
              WHEN R_score  <= 3.09 AND F_score > 1.07 THEN '重要发展客户'
              WHEN R_score  <= 3.09 AND F_score <= 1.07 THEN '一般价值客户'
              ELSE 0
              END) AS 客户类型
FROM 用户价值打分;

查询结果

标签

④、查看不同价值用户占比情况

客户类型计数：

查询代码

# 用户类型计数
SELECT 客户类型
       ,COUNT(DISTINCT user_id) AS 数量
FROM 用户标签表
GROUP BY 客户类型

查询结果

客户类型计数
可视化展示：

客户类型占比

⑤、现象描述/分析

对不同类型的用户实行不同的营销策略：

重要价值用户：
他们是最优质的用户，需要重点关注并保持，应该提高满意度，增加留存，显然6%的比例很低；
重要保持用户：
他们最近有购买，但购买频率不高，可以通过活动等提高其购买频率，显然44%较高；
重要发展/挽留用户：
他们虽然最近没有购买，但以往购买频率高，可以做触达，以防止流失，1%比例很低；
一般价值用户：
他们最近没有购买，以往购买频率也不高，特别容易流失，所以应该赠送优惠券或推送活动信息，唤醒购买意愿，他们的比例是49%。

4、用户生命周期分析

(1)、用户生命周期简介

用户生命周期是指用户从第一次进入平台到最后彻底离开平台所经历的每一个阶段，主要可以分为五个阶段，不同阶段的用户对平台创造的价值是不一样的，不同阶段的用户特征也是不一样的。在进行精细化运营的过程中，为使用户更快的进入成熟期并且维持，用户生命周期是一个重要的课题。

用户生命周期

（2）、实现过程

结合AARRR模型对用户生命周期阶段进行划分，因为数据集没有金额，所以这里也只从R、F两个维度进行分析，具体实现过程如下：

①、指标选择

查询代码

SELECT behavior_type
      ,COUNT(behavior_type)
FROM userbehavior
GROUP BY behavior_type;

查询结果

用户行为指标

②、权重计算

按AARRR漏斗计算各指标权重

③、得分计算排序

查询代码

# 按用户分组计算得分和排名
CREATE VIEW 用户生命得分表 AS # 4.创建视图，作为打标签的基表
SELECT user_id
      ,得分
      ,RANK() OVER (ORDER BY 得分 DESC ) AS '排名'  #3.对分数进行排名
FROM (SELECT * 
            ,(点击次数 *0.1 +收藏次数*0.2 +加购次数*0.2 +购买次数*1  ) AS 得分  #2.计算用户活跃度得分
      FROM (SELECT user_id # 1.按用户id分组，计算每个人的行为数
                    ,SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) AS '点击次数'
                    ,SUM(CASE WHEN behavior_type = 'fav' THEN 1 ELSE 0 END) AS '收藏次数'
                    ,SUM(CASE WHEN behavior_type = 'cart' THEN 1 ELSE 0 END) AS '加购次数'
                    ,SUM(CASE WHEN behavior_type = 'buy' THEN 1 ELSE 0 END) AS '购买次数'
              FROM userbehavior
              GROUP BY user_id) AS 按权重计算得分
        ORDER BY 得分 DESC) AS 得分表;

查询结果

用户活跃度得分

④、周期划分

查询代码

#给每一位用户打上周期标签
#已知总用户数8477人，按比例划分:
#成熟期：排名小于8477*20%
#成长期：排名介于8477*20%到8477*50%
#休眠期：排名介于8477*50%到8477*75%
#流失期：排名大于8477*75%
CREATE VIEW 用户生命周期最终表 AS
SELECT *
      ,(CASE WHEN 排名 < 1695 THEN '成熟期'
             WHEN 排名 >= 1695 AND 排名 < 4239 THEN '成长期'
             WHEN 排名 >= 4239 AND 排名 < 6358 THEN  '休眠期'
             WHEN 排名 >= 6358 THEN  '流失期'
                 ELSE '怎么回事'
                 END) AS 标签
FROM 用户生命得分表;

查询结果

用户生命所处阶段

⑤、现象描述/分析

给每个用户打上标签之后，就可以根据用户所属的不同阶段，进行个性化的运营策略：
引入期：找到合适的渠道进行拉新
成长期：刺激用户快读进入成熟期
成熟期：建立积分体系维护用户
休眠期：通过运营策略刺激用户重新回到成熟期
流失期：流失用户召回

五、总结建议

通过对运营指标、用户行为、用户价值和用户生命周期的分析，可得出如下结论：

(1)、总体运营指标方面：

①、从对流量指标和订单产生效率指标分析可知，促销活动对用户各项指标的影响非常大。

建议：要充分利用好节假日的机会，策划相关营销活动，提升用户活跃度，提高销售转化。

②、用户行为转化率很低但跳失率却不高，这说明用户在首页进行多次点击后并未找到中意的商品，有意向-购买转化率50%，说明有过半数的加购最终也没有成交。

建议：优化产品详情页、收藏页、购物车页面，可以通过活动、优惠券、倒计时购物车等方式增加客户购买紧近感，促进用户下单的利益“诱导”。

③、页面访客基准的转化率仅为5%和1%，而基于独立访客转化率为100%和51%，跳失率也不高，这说明商品还是符合用户需求的，只是“道路不够通畅”。

做页面优化，与第②点基本相同

(2)、用户行为特征：

①、时间上的活跃度特征：活动日活跃平日平稳；周末活跃平日平稳；晚间活跃白天一般。

建议：营销活动时间节点选择可根据用户以上活跃规律进行，实现活动效果最大化。

②、商品上的活跃度特征：仅购买一次的用户占47%，购买5次以内占比90%以上，说明留存率很低，结合基于独立访客的转化率来看，运营效果不好。

建议：针对活跃商品和用户提炼其画像，分析其背后的需求逻辑，优化商品结构，建立用户详细信息库，通过合适的渠道，进行精准营销活动推送，提高用户复购率。

③、购买路径上的特征：直接购买占比58%、浏览购占比47%，用户并不会走完每一个步骤，商品品质和首页引导对销量转化非常关键。

建议：优化商品结构，确保商品品质，提升用户满意度；优化首页界面，减少用户购物筛选难度，提升下单概率，提升转化效率。

④、复购率上的特征：总体复购率53%，可针对复购率高的商品和用户，分析其画像，提升复购，增加留存。

建议：建议同②

(3)、用户价值：

①、重要价值客户仅为6%，就是最近有消费且频次高的用户，这说明高价值用户比例少/留存低。

建议：提高中药价值客户比例，可通过提升重要保持客户的购买频次等方式实现

②、重要保持客户44%，就是那些最近买过但频率不高，比例较高，需丰富运营活动，刺激消费，让更多的人成为重要价值客户。

建议：通过丰富多彩的活动，刺激消费，转化为重要价值客户

③、重要发展/挽回客户仅为1%，就是那些以前消费频率高但已经很久没有消费了，这说明平台流失率低，用户忠诚度较好。

建议：分析历史购买记录，分析用户画像，挖掘需求点，尽可能挽回这部分用户。

(4)、用户生命周期

①、引入期：找到合适的渠道进行拉新

②、成长期：刺激用户快读进入成熟期

③、成熟期：建立积分体系维护用户

④、休眠期：通过运营策略刺激用户重新回到成熟期

⑤、流失期：流失用户召回

淘宝用户行为数据分析（Mysql）

本文结构：

一、理解数据

1、总体介绍

①、数据来源：

②、数据时间：

③、数据简介：

2、字段介绍

二、分析思路

1、提出问题：

2、数据探索：

3、分析思路：

三、清洗数据

1、选择子集

2、列名重命名

3、重复值处理

4、异常值处理

5、缺失值处理

6、数据一致化处理

7、排序

四、构建模型

1、总体运营指标：

(1)、流量指标：

①、计算页面访客数(pv)、独立访客数(uv)、人均点击数(pv/uv):

②、每日流量指标变化趋势：

(2)、AARRR漏斗转化率

①、按照页面访客计算漏斗转化率

②、按照独立访客计算漏斗转化率

(3)、跳失率

①、浏览页跳失率

②、关键页跳失率

(4)、订单指标：

(1)、成交量

①、先看总体成交量：

②、再看每日成交量走势

(2)、人均购买次数

②、再看每日

(3)、复购率

①、总体复购率

那么是哪些商品被重复购买的次数最多？哪些用户进行了更多的重复购买呢？

②、商品品类复购排行榜：

③、用户复购排行榜：

2、用户行为特征分析：

(1)、用户行为时间的特征：

①、按日期粒度：

②、按周粒度

③、按小时粒度

(2)、用户商品偏好特征：

(3)、用户行为路径上的特征：

(4)、用户复购率特征：

3、基于RFM模型分析用户价值：

(1)、RFM模型简介

(2)、分类维度及标准

(2)、实现过程

①、建立视图，统计用户R值和F值：

②、给RF分组，给用户打分

③、给不同价值用户贴上价值标签：

④、查看不同价值用户占比情况

⑤、现象描述/分析

4、用户生命周期分析

(1)、用户生命周期简介

（2）、实现过程

①、指标选择

②、权重计算

③、得分计算排序

④、周期划分

⑤、现象描述/分析

五、总结建议

(1)、总体运营指标方面：

(2)、用户行为特征：

(3)、用户价值：

(4)、用户生命周期

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

画图