基于mysql的淘宝用户、商品、平台价值分析

---

一、项目背景和需求

1.项目背景及分析目的

随着互联网和大数据的发展，电商的数量已经呈现井喷式增长，到了一定的瓶颈期，那么通过数据分析挖掘消费者潜在需求、消费偏好成为平台运营过程中的重要环节。
所以本项目基于淘宝平台用户行为数据，在mysql关系型数据库，探索如何对用户行为规律，挖掘隐式反馈信息，寻找高价值用户、高贡献商品，并分析产品功能，优化产品路径。

关于隐式反馈：
推荐系统中用户对物品的反馈分为显式和隐式反馈。
显式反馈 （如评分、评级) 用户明确表示对物品喜好的行为。
隐式反馈 (如浏览、点击、加入购物车）不能明确反映用户喜好的行为。
显性反馈数值代表偏好程度，隐性反馈数值代表置信度。

二、数据集摸底并找出问题

2.1数据来源

阿里云天池
[https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1]

2.2数据理解

User Behavior 是来自淘宝的用户行为数据集，用于隐式反馈的推荐问题。数据集由阿里巴巴提供。随机选取了大约 200 万用户在 2017 年 11 月 25 日至 12 月 3 日期间具有点击、购买、添加商品到购物车和商品偏好等行为。
数据集包含了2017年11月25日至2017年12月3日之间，有行为的约两百万随机用户的所有行为，包括点击、购买、加购、喜欢。

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

---

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

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

---

用户行为类型共有四种，它们分别是:

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

2.3分析思路梳理

2.3.1 分析流程

---

指标体系的构建设计应该提前设计，本案例指标体系的构建基于“人货场”理论

关于“人货场”三要素：
人货场，是指影响销售的三个重要因素（不是三个指标）
1.人：来自销售人员、顾客的因素
销售人员：人员是否足够，素质是否满意，执行是否到位？
顾客：是否有足够客户到来，是否有成交，成交消费力如何？
2.货：商品因素
商品质量：种类是否丰富、款式是否能够吸引、有没有爆款
商品数量：商品备货是否充足，畅销品是否短缺，滞销品有多少
3.场：卖场/门店/销售渠道因素
卖场数量：线下门店数量/位置，线上引流渠道数量/类型
卖场质量：线下门店装修、面积、陈列，线上引流渠道的转化路径，页面设计

2.3.2使用人货场拆解方式建立指标体系

最终结果：评价“用户、商品、平台”三者质量
人：（用户）是整个运营的核心，所有的举动都围绕着如何让更多的人有购买行为，让他们买的更多、买的更贵，所以对人的洞察是一切行为的基础。目前平台上的主力消费人群有哪些特征，他们对货物有哪些需求，他们活跃在哪一些平台，还有哪些消费力的人目前不在平台上，对这些问题的回答指向了接下来的行动。

指标	细化指标	说明
浏览	PV	(pageview)是页面浏览量
	UV	(Unique Visitor)是一定时间内访问网页的人数，正式名称独立访客数（一个网站或者一个页面），也可以说是终端数
流量质量	PV/UV	浏览深度
	ROI	投资回报率（ROI）=年利润或年均利润/投资总额 *100%
成交用户	新客数	（一般当天的算新用户，当日激活或者新增）
	老客数
	客单价	当日消费总价/顾客数（新+老），客单价应该是稳的，突然多/少，购买力特别强的，疫情影响客单价下降
	DAU	日活跃用户
	MAU	月活跃用户

---

货： 对应供给，涉及到了货品分层，哪一些是红海（指的销量高、利润少、竞争多的商品，属于常规商品），哪一些是蓝海（销量少、利润高、竞争少的商品），如何进行动态调整，是要做自营还是平台，以满足消费者的需求

---

场： 就是消费者在什么场景下，以什么样的方式接触了这个商品。早期导购比较简单，目前场就比较丰富，但也暴露了一些电商平台（eg：淘宝or京东）在导购方面的一些问题，比如内容营销，目前最好的可能是微博KOL生态和小红书，甚至微信，而不在电商自己的场。如何做一个全域的打通，和消费者进行多触点的接触，比如社交和电商的联动，来完成销售转化，这也就是所谓的「全域营销」
平台可以理解为电商网站，但是现在这个场的概念已经前后有所延伸说，往前延伸指的是引流，往后延伸指的是后期的售后、物流等。

---

2.3.3 确认问题

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

• 从场的角度：基于漏斗模型的用户购买流程各环节分析指标，确定各个环节的转换率，便于找到需要改进的环节；
• 从商品角度：商品分析：找出热销商品，研究热销商品特点；
• 从用户的角度：基于RFM模型找出核心付费用户群，对这部分用户进行精准营销

---

三、数据清洗

3.1 导入数据

点开Mysql workbench
①新建一个库

DROP DATABASE IF EXISTS `用户行为分析`;
CREATE DATABASE `用户行为分析`;
USE `用户行为分析`;

②导入外部数据，使用Navicat连接Mysql，选择导入200万行数据。
ps:在开始分析一份数据的时候，第一时间先进行备份。（因为吃过一次数据不小心被删的亏，所以养成了拿到一份数据先备份）：

CREATE TABLE USERBEHAVIOR_BACKUPS AS
SELECT *
FROM UserBehavior

③查看数据，对应2.2数据理解，第一列为用户ID，第二列为商品ID，第三列为商品类目ID，第四列为行为类型，第五列为时间戳，从数据面板发现列名和一些字段的数据类型需要修改。

3.2 选择子集

因为导入原始数据共有一亿多条，这里导入了200w条数据，为了查看数据是否已成功导入，查看表结构是否正确：

#查看数据是否为200w条
SELECT COUNT(*) FROM userbehavior;
#查看表结构，只看前20行即可
SELECT * FROM userbehavior  LIMIT 20;

结果：
这里多出60w的数据，所以对200w以后的数据进行删除
但是发现导入的数据中第一个字段有重复值，所以增加一个ID标识列并设置为主键，便于删除：

ALTER TABLE userbehavior ADD COLUMN 
id INT(8) PRIMARY KEY AUTO_INCREMENT;

处理后的表格如下：
可以进行对200w以后的数据进行删除：

DELETE FROM userbehavior WHERE id > 2000000;

3.2 列名重命名

可以直接在类型面板上双击Column直接进行更改字段名

3.3 查找重复值并删除

①检查是否存在重复值

SELECT * FROM userbehavior 
GROUP BY User_ID, Item_ID, Category_ID, Behavior_type, Time_date, id
HAVING COUNT(*)>1;

结果如下：

以上步骤完成后可以将表示id列删掉:

alter table userbehavior drop column id;

②修改数据类型：行为分析原来为枚举值，由于不再进行数据增加，因此设为VARCHAR。

3.4 处理错误值-以类别字段为主-‘行为类型’

select behavior_type, count(behavior_type)
from userbehavior
group by behavior_type with rollup

结果：行为数据和枚举出的四个类型保持一致，该列没有问题

3.5 查找缺失值

SELECT 
COUNT(User_ID),
COUNT(Item_ID),
COUNT(Category_ID),
COUNT(Behavior_type),
COUNT(Time_date)
FROM userbehavior;

结果各字段的数量相等，所以不存在缺失值：

3.6 处理与时间相关的问题（一致化处理）

目前时间列可读性较差，不利于我们要做的时间分析，所以需要进行转化，变成我们常见常用的时间格式。

专门处理时间戳的函数：From_unixtime；
作用：将MYSQL中以INT存储的时间以"YYYY-MM-DD"格式来显示。
语法：FROM_UNIXTIME(时间戳，时间格式)

---

常见的时间格式：
%Y年，%m月，%d日，eg：2022-01-01
%H时，%I分，%s秒，eg：11:59:30

---

因此需要将现有的时间列分为日期时间、日期、小时三列

①添加新列Date_time，根据Time_date返回日期时间（%Y年，%m月，%d日 %H时，%I分，%s秒 ）:

ALTER TABLE userbehavior
add Date_time VARCHAR(255);
UPDATE userbehavior
set Date_time = from_unixtime(Time_date, '%Y-%m-%d %H:%i:%s')

②添加新列Date，根据Time_date返回日期

ALTER TABLE userbehavior
add Date VARCHAR(255);
UPDATE userbehavior
set Date = from_unixtime(Time_date, '%Y-%m-%d')

③添加新列Time，根据Time_date列返回时间

ALTER TABLE userbehavior
add Time VARCHAR(255);
UPDATE userbehavior
set Time = from_unixtime(Time_date, '%H:%i:%s')

根据以上得出的结果如下：

3.7 异常值处理（超出目标分析范围的数据）

需要检查日期是否都在需要分析的时间范围内，即2017年11月25日至2017年12月3日之间

select max(Date), min(Date)
from userbehavior

结果：

最大值日期超过2017-12-3，最小日期超过2017-11-25，因此需要删除2017年11月25日至2017年12月3日之外的数据

DELETE FROM USERBEHAVIOR
WHERE DATE < '2017-11-25' OR DATE > '2017-12-03';

共删除了991条时间异常数据

3.8 最终数据范围（维度和数量）

维度	数量
用户数量	19544
商品数量	628886
商品类目数量	6492
PV	1790224
Buy	40243
Cart	111015
Fav	57526
所有行为数量	1999008

---

四、指标体系建设及分析问题

基于人-货-场指标体系
使用MySQL进行分析，Excel进行可视化展示

4.1 用户指标体系

基础指标体系（UV/PV/留存率）+RFM模型分析

4.1.1 基础指标

4.1.1.1 UV、PV、浏览深度（PV/UV）的每日变化趋势

UV:独立访客数,统计distinct user_id的数量，需要按日统计（分组）；
PV：页面流量次数,统计behavior_type = 1的记录数，需要按日统计（分组）；
PV/UV：平均一个独立访问者所浏览的页面访问量

代码如下：

select date, count(distinct user_id) 'uv', 
             count(if(behavior_type = 'pv', 1, null)) 'pv'，
             count(if(behavior_type = 'pv', 1, null))/count(distinct user_id) 'pv/uv'
             from userbehavior
             group by date
             

结果如下：

将以上数据导入excel中，绘制PV、UV每日变化趋势图。