sql 不同年份数据结果显示在同一行_SQL实战项目：国外某化妆品电商平台用户行为数据分析...

终于终于是要给自己的SQL学习来一个小小的总结啦~献上人生第一个SQL的实战项目，项目名字是自己乱起的，但是数据是真的~~~哈哈~加油~

数据来自Kaggle平台较为新鲜的近期数据（2020February,本年为闰年所以二月有29天），链接如下：国外某化妆品电商平台消费数据。里面有具体的数据描述，这里仅贴几张会在项目中用到的数据进行稍加解释。

首先是数据的导入，mac端下的MySQL的csv文件操作与Windows下类似，需要注意的是因为原始文件中不同指标数目过多以及一些没有分析价值的指标例如category_code，对于SQL小白的我选择性地导入一部分指标数据进行分析（主要是我懒~）。

选择性导入部分指标数据

数据的简单介绍:

事件发生时间，事件类型

商品id,商品类别id，商品类别代码

剩下的就是用户账户名称user_id,不再赘述。

项目步骤：提出问题→理解数据→数据清洗→分析数据&结论

一 提出问题

• 基于AARRR漏斗模型进行分析。所以提出以下几个问题：
• 夹点在哪里：用户从浏览到最终购买整个过程的流失情况，确定夹点位置，提出改善转化率的意见
• 活跃时间在什么时候：在研究的时间段里找出用户最活跃的日期以及每天活跃时间段，了解用户的行为时间模式
• 什么产品受欢迎：什么产品以及产品类目的购买率最高，找出最受欢迎的产品，优化产品销售
• 高频购买会员分析：哪些用户购买次数最多，找出最核心的付费用户群，并且统计出这些用户购买的产品以及类目，针对这些用户的购买偏好推送个性化的产品销售方案

二 理解数据

数据集包含了2020年2月1日至2020年2月29日之间，有行为的约40万随机用户的所有行（行为包括点击、购买、加购物车、取关）。数据集大小情况为：用户数量约40万（391055），商品数量约4万（48579），商品类目数量487以及总的用户行为记录数量为400多万条（4156682）。

数据集大小介绍

三 清洗数据

1. 选择子集： 参照文章开头
2. 列名重命名：原数据集有表头，数据导入Mysql数据库即可。
3. 删除重复值：表格数据导入时保证没有重复数据。
4. 缺失值处理：经查询没有缺失值。

查询缺失值

5.异常值处理：表中无异常值数据

四 用户行为分析

建立视图便于统计各种行为发生的次数：

4.1 用户转化率

用户转化率=产生购买行为的访客人数/所有到达店铺的访客人数，本文将所有产生行为的用户人数视为到达店铺的访客人数；根据查询结果计算，用户转化率为5.82%，转化率偏低。

4.2 用户流失环节分析

从购买流程的角度来分析用户在哪个环节流失较多

加购环节：部分用户点击后未加入购物车，直接购买，这部分用户未流失，因此查询加入购物车环节的流失人数应该排除这部分用户，流失率为53.44%.

付款环节：加入购物车后却最终没有付款，流失率为52.71%.

加购后失去兴趣移出购物车:流失率为34%

Question:

用假设检验的方法分析上述环节流失率高的原因

假设1：推送商品不符合用户偏好客户购买量排行.

分别查询购买前100和点击前100 的商品

内连接查询公共部分的商品ID

结果显示，有31个商品是购买前100与点击前100共同的商品ID，说明推送的商品不太符合用户偏好，假设一成立。

假设2：商品或服务质量不能满足用户需求，用户回购率低。

结果显示，商品接近一半都有被回购，但是顾客只有10%产生回购行为，原因可能是商品质量并不理想，入手后就被拔草了~~~

4.3 用户行为模式分析——找出用户最活跃的日期以及每天活跃时间段，了解用户的行为时间模式

A. 查询用户点击量最活跃的日期

导出数据python 可视化结果：

点击率逐渐下降可能是圣诞节后的回归，大家步入新一年的工作，无心专注于买买买~

B. 查询用户点击量最活跃的时间段

导出数据python 可视化结果：

可以看到中午12点和下午19时点击量最大，大概是因为中午12点是lunch time ,晚上7 点左右大家是下班后的free time，不受工作事务影响所以尽情滴购起了物~

4.4 产品销售分析——什么产品以及产品类目的购买率最高，找出最受欢迎的产品，并优化产品销售

A. 查询购买率最高的产品

结果显示product_id 为5809910 和5808664的销量遥遥领先，和第三名5809912有较大的差距，但是随后的商品销量均匀递减。

B. 查询不同购买次数的商品数量累计情况

结果显示大多数人的回购次数只有个位数，反而购买次数多的人却很少，有点符合长尾效应，让我们导出结果用python 可视化验证一下吧~Bingo!!!

C. 查询不同购买次数的商品类别累计情况

结果显示大多数人的购物对象只集中于某一种产品，反而多种产品只有一小部分人买，也有点符合长尾效应，让我们导出结果用python 可视化验证一下吧~Bingo!!!

4.5 核心用户分析——哪些用户购买次数最多，找出最核心的付费用户群，并且统计出这些用户购买的产品以及类目，针对这些用户的购买偏好推送个性化的产品销售方案.

A. 查询购买率最高的顾客

我们得到了二月最佳买买买小能手前三名~对于下单量超多的客户应该多推送给他们喜欢的类似或更高价的产品，做到cross-sale 和 up-sale双管齐下~

B. 统计出高价值用户购买的商品以及类目

用户购买行为是个性化的行为，不同用户的购买商品可能完全不同，应单独分析。这里pick up 下单量第八位的客户进行分析，为什么选他呢？因为我最喜欢数字8~~~

首先分析8号喜欢的商品：

但是很不幸，8号的购买倾向分布地很平均。。。。。。

接着分析8号喜欢的商品类别：

看来8号也有自己喜欢的商品类别~~~

来一个小小的总结，经过自己的学习算是掌握了SQL的入门知识，在做这个项目的时候也顺便搜了学习了不少以前没有遇到过的语法，过程开心又充实~马上就要毕业啦，多刷题多锻炼自己的思维~加油~~~