MySQL项目实战——用户行为分析

一、项目背景

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

构造推荐系统的关键资源是用户历史行为数据，具体可分为两类：显式反馈和隐式反馈。其中，隐式反馈是指不直接表现用户倾向的历史数据，如浏览网站、购买商品等。

二、分析思路

2.1  分析目的

       通过对数据集中的用户数据、行为数据及商品数据进行分析，找出各个环节存在的问题，为提升销量提供合理的建议。

2.2  明确问题

       1. 了解获客情况和留存情况

       2. 基于漏斗模型分析用户从浏览到购买整个过程的转化率情况

       3. 观察用户四种行为的变化趋势（按天和按小时）

       4. 分析浏览量、购买量前10的商品以及商品类目，优化产品销售

       5. 基于RFM模型对用户价值进行分析

2.3  分析框架

三、数据说明

3.1  数据来源

阿里云天池：数据集-阿里云天池 (aliyun.com) 

3.2  理解数据

        数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集大小说明：用户数量（987,994），商品数量（4,162,024），用户数量（987,994），商品类目数量（9,439），总的淘宝用户行为记录数量为1亿条（100,150,807）。

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

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

四、数据预处理

        由于数据量级达到一亿，考虑到电脑性能问题，故本次选取其中的40万+的数据进行分析。

4.1  选择子集

        数据集中五个字段都具有价值，全部保留。

4.2  列名重命名

        原数据集没有表头，用sql语句创建表及5个字段，把淘宝用户行为数据导入MySQL数据库。

4.3  删除重复值

       不存在重复值。

4.4  缺失值处理

         不存在缺失值。

4.5  一致化处理

        将时间戳整理成日期和小时：

        ①新增字段datetime，用函数from_unixtime 把整数型的timestamps改成日期型的timestamps

        ②新增字段dates，times，hours

4.6  异常值处理

        检查日期是否在规定范围内：2017年11月25日至2017年12月3日。

 

         一共删除异常值184条。

五、数据分析

5.1  获客情况

         按日分析pv、uv、pv/uv：

• pv：页面浏览量
• uv：独立访客数
• pv/uv：浏览深度

#pv  uv  pv/uv
select dates, count(*) 'pv', count(distinct userid) 'uv',
round(count(*)/count(distinct userid),1) 'pv/uv'
from user_behavior
where behaviortype='pv'
group by dates
order by dates;

        ​​​​​​输出结果（代码结果以及Excel图示）：