mysql 计算近30天总金额_MySQL数分实战：咖啡店精细化运营

前期，我们已经对MySQL的增删改查、以及多种查询方式和窗口、视图进行了解学习。本次我们利用前期所积累的知识进行数据分析实战。

本次实战数据源来自：

Superset​superset.workerindata.com

明确问题

• 如何让用户喜欢我们的产品，进而提升产品销量？

分析思路

我们的产品是销售给用户的，销量好不好就在用户是不是喜欢我们的产品；用户喜欢还是不喜欢怎么判断呢？就要看用户的留存情况、复购情况。

那如何让用户喜欢我们的产品，进而增加销量呢？我们利用5W2H分析方法进行分析：

• why：那为什么用户喜欢我们的产品，那就需要看用户的需求是什么？比如说用户对我们的产品咖啡比较热爱，钟情某款产品；或者我们给优惠券，产品便宜了所以来购买；或者用户需要再我们的咖啡店进行聊天学习等；
• what：我们产品都有哪些？用户喜欢我们的哪些产品？
• where：用户喜欢店内使用我们的产品还是外带？
• when：用户什么时间段来购买产品？
• who：我们的目标用户群体主要有哪些？

那我们根据分析不同特征用户的需求，进而精细化运营，制定运营策略：

• how：我们如何去做才能提升用户喜爱，提高销量？
• how much：我们的产品是否需要进行促销优惠，优惠力度又该如何？

分析指标

• 每天的总收入、总订单量
• 对用户分析：拉新、留存、老用户收入占比
• 了解用户消费习惯：用户消费频次、用户最近次消费日期、用户消费总金额、用户消费时长、用户到店时间分布
• 了解用户偏好：每个用户最喜欢的产品、近30天产品销量排名
• 了解用户的优惠券使用情况：每天赠送出去的不同优惠券数量、不同优惠券被使用消费的数量、过期优惠券未使用数量、优惠券按过期日期汇总
• 了解线下环境对用户影响：最受欢迎的桌子排名、消费金额和次数否在Top10中每个桌子坐过的次数、每个桌子每天空闲总时长
• 探查用户特征，发现用户价值（通过累计高频次、消费时长、连续消费等划分）

数据理解

本次数据集共有7个表：（脑图中的五角星项为Key）

分别理解：

① 用户表 user_info

记录用户编号、性别、年龄以及注册日期；

② 产品表 product

记录产品编号、产品名字、产品价格、每天可销售数量；

③ 桌号表 desk_info；

记录桌号、每桌人数；

④ 订单情况表 order_info

记录用户编号、订单编号、优惠券编号、下单时间、桌子编号；

⑤ 订单数量表 order_detail

记录订单编号、产品偏好、销售数量；

⑥ 优惠券表 coupon_info

记录优惠券种类、满减优惠、打折优惠、新用户无门榄优惠的数量

⑦ 优惠券使用情况表 user_coupon_detail

记录优惠券编号、优惠券种类、用户编号、下单时间戳、优惠券到期日；

数据清洗

本次项目不涉及数据清洗。

数据分析

我们根据上述的分析思路和分析指标，对数据进行分析：

计算每天的总收入、总订单量

1）计算每天的总收入、总订单量

• 每天的总收入

分析：每天收入 = 每天销售数量*售价；售价 = 价格*折扣；但产品不同，产品售价不同，产品销售数量也不同；所以需要对多表进行联结；

select 

我们目前发现，6月1日（周六）的销售情况最佳；次之的为5月27日（周一）；

• 每天总订单量

分析：对订单表order_info按照时间分组，对每天的订单数量统计单数；

select 

我们目前发现，收入高的当天对应销量也较高；

对用户分析：拉新、留存、老用户收入占比

2）计算最近1周(假设今天2019-6-1）注册的用户，首次入店消费的用户数和消费金额

分析：条件是最近一周注册的用户，对符合条件的用户进行分析；统计新注册用户到店数（注意使用distinct）以及本周消费金额；

消费金额 = 每个用户本周购买产品数量*各个产品价格；价格 = 售价*折扣；但产品不同，产品售价不同，产品销售数量也不同；所以需要对多表进行联结；

select 

注意，关于使用date_sub()函数执行日期减操作，比如 date_sub('d',interval7day)，如果求当前日期的七天前，可以使用curdate()。

我们发现，最近一周新注册用户6人，进店消费共计141.68元；

3）新用户次日、次周、次月的复购用户数

-- 每个用户在某天天消费后，其后再次消费的日期

select 

-- 一般业务比较关系次日复购， 7日内复购， 30内复购用户数:

select 

我们发现，5-25的用户次日复购人数1人，7日复购人数有7人；5-27用户新增6人，次日复购1人；

4）每天收入中，老用户收入占比

分析：首先明确每天收入金额（见第一问），其中用户为老用户（定义老用户，购买次数>1均为老用户）的购买金额占每天收入比；

-- 查找老用户

select 

--那每日老用户的收入怎么算呢？

select 

-- 那每天的老用户收入占比 = 老用户收入/当天收入【太粗暴了···期待精进】

select 

我们发现，除了销量增高的5-27和6-1之外，其他时间均为老用户收入，占比100%；5-27和6-1增加大量新用户；

了解用户消费习惯：用户消费频次、用户最近次消费日期、用户消费总金额、用户消费时长、用户到店时间分布

5）每个用户消费频次

分析：对订单表order_info按照用户id分组，统计每个用户的订单数量；

select 

101用户属于咖啡狂热者，购买频次最大；次之的是102、103；

6）每个用户最近消费日期

分析：对订单表order_info按照用户id分组，统计每个用户消费订单日期最大项，显示用户id和消费日期；

select 

101用户102用户都是在19-06-02还有消费的用户，消费日期最近；

7）每个用户消费总金额

分析：每个用户消费金额= 每个用户购买产品数量*各个产品价格；价格 = 售价*折扣；但产品不同，产品售价不同，产品销售数量也不同；所以需要对多表进行联结；

select 

101用户消费金额最高，次之是102、103;

8）用户单次平均消费时长【未完】

分析：每个用户单次平均消费时长 = 每个用户总时长/消费次数

订单表order_info按照用户id、日期分组，按照分组结果计算每日消费时长，对消费时长求和为总时长；按照订单号统计消费次数；

【错误示例：因为timestampdiff(hour,createTime,payTime) 为时间戳，求和需要对】

select 

（待后续补充，有大神会也可以提示一下，感谢）

9）用户到店按小时统计分布, 24小时, 每个小时到店人数【未完】

分析：每小时的用户分布数；对订单表order_info按照时间-时分组，统计每个小时的人数；

（待后续补充，有大神会也可以提示一下，感谢）

了解用户偏好：每个用户最喜欢的产品、近30天产品销量排名

10）每个用户最喜欢的产品

分析：按照用户编号分组，对分组结果查询最多购买产品数量，对每个用户购买的产品数量筛选，等于最大数量的便符合条件；

select 

101用户，购买10 和12 两款数量最大，偏爱这两款；102偏爱10和13；

11）最近30天产品销量

分析：按照订单表order_info和订单情况表order_detail进行交叉联结，查询日期在30天内各产品编号销售数量：

select 

近30天内10款商品销量最好；

了解用户的优惠券使用情况：每天赠送出去的不同优惠券数量、不同优惠券被使用消费的数量、过期优惠券未使用数量、优惠券按过期日期汇总

12）每天赠送出去的不同优惠券数量

分析：按照优惠券情况表user_coupon_detail的createTime分组，对分组结果按照优惠券种类数量统计；

select 

赠出的优惠券均为20001类别的优惠券；

13）不同优惠券被使用消费的数量和过期未消费的数量

• 不同优惠券被使用消费的数量

分析：根据订单中的优惠券编号，并统计使用的数量

select 

我们发现，发放的优惠券均被使用；

• 不同优惠券过期未消费的数量

分析：截止到现在（假设是6月30日），优惠券到期日小于现在，且未被使用（不在订单中）的优惠券数量

select 

我们发现，并无过期未使用的优惠券；看来用户还是很喜欢优惠券的；

这里为了验证代码是否因为写错导致没有数据，我们可以查看一个未到期的优惠券在2019-5-25消费情况：

select 

14）优惠券券按过期日期汇总

分析：按照优惠券情况表user_coupon_detail的过期日分组，对分组结果的数量统计，并按照过期日降序排列；

select 

根据以上了解，我们发现优惠券被广大用户所喜爱，那6-1和5-27销量较大是否是因为优惠券的原因呢？我们检查优惠券的发放日期：

select 

果然，用户购买量和优惠券的发放数量正相关；

了解线下环境对用户影响：最受欢迎的桌子排名、消费金额和次数否在Top10中每个桌子坐过的次数、每个桌子每天空闲总时长

15）做进30天内，最受欢迎的桌子top10

分析：对订单表order_info按照桌号deskid分组，统计近30天内桌号出现次数最多的top10

select 

我们发现，107#桌子最被大家喜欢，次之的是105和106#；再次之的是102和103#；

16）消费金额和次数都在Top10的用户，每个桌子坐过的次数

分析：对每个用户id分组，查询消费金额和消费次数都在top10的用户id；查询符合条件的用户每个桌子坐的次数；我们分以下几步慢慢来：

-- 消费金额top10的用户

select 

-- 消费次数top10的用户

select 

-- 消费次数和消费金额都在top10的用户

select 

-- 消费金额和次数都在Top10的用户，每个桌子坐过的次数

select 

我们发现，top10 的用户，喜欢的桌子并不是我们刚才发现的top桌号，只有101号用户喜欢的是第三名的桌子；

我们检查桌子表：发现，最被喜欢的桌子107是单人桌；次之的105和106就属于大桌的；再次之的102和103属于中桌（2位）；

17）每个桌子每天空闲的总时长【未完】

（待后续补充，有大神会也可以提示一下，感谢）

探查用户特征，发现用户价值（通过累计高频次、消费时长、连续消费等划分）

18）在周末消费的用户

分析：可使用weekday()，返回值为7周六，1周日；

select 

我们发现，忠实用户101和102在周末仍然会消费呢；

19）工作日消费多余周末的用户

select 

我们发现，工作日进行消费多余周末消费的用户top1是102号客户，显然101号客户更多的是在周末消费，属于周末控；而102号客户和103号客户是工作日控；

实施方案

根据以上分析结果，我们可以从以下几方面来提升用户的喜爱，提升销量：

• 通过发放新用户无门槛类的优惠券的方式，吸引新用户；
• 对于沉默用户，我们可以对其采用满减优惠的方式，激活用户；
• 对于忠实用户类似101类的用户，还可以以满一定金额返其最喜欢的产品来留存我们重要用户；
• 对于周末销量较小的情况，建议在周末适当增加优惠券吸引用户；
• 根据用户对桌子的偏爱，适当增加大桌，调整小桌布局；