MySQL项目1- 咖啡店经营分析

练习网站链接：Superset

开发者写的教程：

  1. 极简教程：助小白入坑数据分析(二)
  2. 数据分析师SQL刷题, 体验完爆牛客网

“记录一切能记录的，量化一切可量化的，利用一切能用的数据。一般老板就看看收入总和，这些对我来说远远不够, 我希望清楚每个用户消费的过程，每个订单的相关数据最好都记录下来。”

商业流程

进店 -> 扫描桌上小程序 -> 点餐 -> 享用 -> 支付离店 -> 领券 ---> 进店

开店前的调研

  1. 选址：人流量、房租 or 房贷
  2. 客户：新客户、老用户
  3. 商品：餐饮类型与定价、环境/服务
  4. 运营：客户分析、活动营销

数据在每一步里，从选址到经营。

比如选址，这个很重要。咖啡馆提供的是一种下线实体场景服务，如约会、商业谈判、看书学习，尤其在都市里，看书学习这个场景，貌似除了咖啡馆，很难想到其他合适的场所，没毕业的学生是不是很难理解？明确咖啡馆的定位后，对应的目标人群自然就是对这类场景有需求的人群。地址应该选择在目标人群可能集中的地方，或者在一个时间周期内，目标人群出现频次较多的位置。一般咖啡馆选择在写字楼或者商业中心内，正是这个逻辑。

数据一定不是硬生生的数字，而是数字背后的价值和对应场景。上面说的选址好像没看到数据，但内在的逻辑肯定是一种商业数据模型, 选址主要依赖的光临咖啡馆的客户数，取决于转化：商圈每周人流量 x 目标人群占比 x 每周被路过频次 x 进店率

数据收集

店铺运营是个精细活，正是数据能起到作用的地方。数据精细化运营适用于任何商业运营。

把店铺经营过程抽象成下面几个模块，每个模块会有对应产生的数据信息。客户+员工+产品+订单+座位数。店铺是半自动运营，重点打造线下学习环境体验，不雇佣大量员工的模式，所以员工表可能会被省去。

image

  1. 用户表 user_info：假设来店销售的用户都有首次登录日期，即注册日期，这个通过微信小程序就能轻松解决。用户编号-性别-年龄-注册日期

  2. 产品表 product：产品编号-名称-单价-库存数

  3. 桌子表 desk_info：桌子编号-桌子容纳人数

  4. 订单表 order_info: 记录下单时间和支付离店时间，且假定用户都是扫描桌子对应二维码点餐的，然后是在离店时付款的。用户-订单-优惠券表-下单时间戳-支付时间戳-桌子编号

  5. 订单详情 order_detail: 订单-产品-数量

  6. 优惠券表 coupon_info：优惠券种类很多，如满减优惠、打折优惠、新用户无门榄优惠等

  7. 用户优惠券使用详情 user_coupon_detail：优惠券编号-种类编号-用户-获得时间-核销时间

数据分析

“因为我们记录了用户的消费信息，而付费用户是我们店铺活下来的关键。所以，对于用户，我们需要做细致的分析。目的是为了尽可能多的了解我们的用户，从已知数据各个方面进行分析，再反过来优化我们的用户体验，产生更多的价值。”

“数据计算不一定要多复杂，真正的是这些数据能反映的价值。”

“数据分析，思维先行，技术辅助。”

1 宏观分析

  • 每日订单量

  • 每日总收入

用订单表 GROUP BY day 即可

SELECT DATE(t1.createTime) AS day, COUNT(DISTINCT t2.orderid) as orderNum, SUM(t3.price * t2.num) AS income
 FROM order_info t1
  JOIN order_detail t2 ON t1.orderid = t2.orderid
  JOIN product t3 ON t2.productid = t3.productid
 GROUP BY day

2 用户分析

2.1 基础分析

  • 拉新：最近一周注册的用户，首次入店消费的用户数和消费金额
    • 对于实体店，拉一名新用户实属不易，这个一定要多多呵护。结账时，优惠券奉上，为了提高用户下次来消费的可能性
/* 因为不确定是否所有下单的用户都需要注册，我们先筛选看看是否有订单没有对应的 userid */
SELECT id
FROM order_info
WHERE userid IS NULL

筛选结果（The query returned no data）说明所有用户在第一次下单之前需要注册。

/* 求每日新增用户数和总用户数 */
SELECT u1.registerDay as Day, COUNT(DISTINCT u1.userId) AS newNum, COUNT(DISTINCT u2.userId) AS userNum
FROM user_info u1, user_info u2
WHERE u2.registerDay <= u1.registerDay
GROUP BY day
ORDER BY day

/* 求每日新增用户的消费情况 */
SELECT t1.registerDay AS day, COUNT(DISTINCT t1.userId) AS newUser, SUM(t3.price * t2.num) AS cost
 FROM user_info t1 LEFT JOIN order_detail t2 USING(id)
                   LEFT JOIN product t3 ON t2.productid = t3.productId /* 注意连接表不能用 JOIN */
 GROUP BY day
 ORDER BY day

  • 留存：新用户次日、次周、次月的复购用户数

    • 难度级别: 简单
    • 重要级别: ★★★★★
    • 场景: 衡量店铺的服务认可度、筛选目标客户。假设店铺提供的服务很没有吸引力，那客户下次肯定不来了，但即使我们的咖啡店环境和服务可能都不错，我们也不能奢求留下全部新用户，甚至是只有一小部分，因为不是所有用户都喜欢在咖啡店看书，但留下来的用户就可能就有这种需求或者偏好。他们就是我们的目标客户。

  • 老用户收入占比：每天收入中，老用户收入占比

    • 难度级别: 简单
    • 重要级别: ★★★★★
    • 场景: 衡量店铺收入结构是否健康。如果很偏重新用户或者很偏重老用户，可能都不太利于店铺长久发展。前者就是要求店铺不停的拉新，而拉新的用户却在不断流失，这样店铺周围的用户就被透支完的时候就是店铺关门的时候；后者说明店铺太依赖老用户，但老用户因为各种原因，是会慢慢流失的，等老用户慢慢流失完的时候，就是店铺倒闭的时候

用到的表: user_info, order_info, order_detail

2.2 用户分层（用户行为消费研究RFM）

难度级别: 简单
重要级别: ★★★★★

  • 用户消费频次：来光顾次数越多，自然用户价值越高
  • 用户最近一次消费日期（相比去年最后一次消费的用户，昨天来店的客户我们更关注）
  • 用户消费总金额
SELECT t1.userId, COUNT(DISTINCT DATE(t1.createTime)) AS days, COUNT(t1.orderId) AS orderTimes, SUM(t2.num * t3.price) AS cost
 FROM order_info t1 LEFT JOIN order_detail t2 ON t1.orderId = t2.orderid
                    LEFT JOIN product t3 ON t2.productid = t3.productId
 GROUP BY t1.userId
 ORDER BY cost DESC
  • （用户到店按小时统计分布，24小时）每个小时到店人数：店铺营销高峰期，可以用于高峰期的价格调节和空闲期的活动促销
SELECT HOUR(createTime) AS arriveHour, COUNT(userid) AS customerNum
 FROM order_info
 GROUP BY arriveHour
  • 用户单次消费时长: 判断用户到店目的
/* 单日消费 */
SELECT t1.userid, DATE(t1.createtime) AS day, (MAX(t1.payTime) - MIN(t1.createTime)) / 10000 AS spendTime, SUM(t2.num * t3.price) AS cost
 FROM order_info t1 LEFT JOIN order_detail t2 ON t1.orderId = t2.orderid
                    LEFT JOIN product t3 ON t2.productid = t3.productId
group by t1.userId, day

用到的表: order_info, order_detail

2.3 根据用户消费习惯进行产品分析

难度级别: 简单
重要级别: 5星

  • 每个用户最喜欢的产品：以后是不是可以送给ta？
SELECT t1.userId, t2.productid, COUNT(t2.productid) AS ordertimes, SUM(t2.num * t3.price) AS cost
 FROM order_info t1 LEFT JOIN order_detail t2 on t1.orderId = t2.orderid
                    LEFT JOIN product AS t3 ON t2.productid  = t3.productId
 GROUP BY t1.userId, t2.productid
  • 最近30天产品销量排名：为更新库存做准备

用到的表: user_info, order_info, order_detail, product

2.4 根据用户消费习惯进行营销分析

难度级别: 简单
重要级别: 5星

  • 每天赠送出去的不同优惠券数量：优惠券要坚持送，而且我们赠送优惠券是需要有赠送场景的，比如消费，拉新等，优惠券种类也不同
  • 不同优惠券被使用消费的数量 & 过期未消费的数量：哪类优惠券相对更受欢迎
  • 消费券按过期日期汇总: 即将过期的优惠券可以push进行提醒

以上计算相关表: user_coupon_detail

2.5 根据用户消费习惯进行布局分析

难度级别: 中等
重要级别: 5星

  • 最受欢迎的桌子 Top10，按最近 30 天的订单量排列。 我们再从实际情况分析下受欢迎的原因，比如光线、大小、舒适度
SELECT t1.userId, t1.deskId, COUNT(t1.deskId) AS deskTimes, SUM(t2.num * t3.price) AS cost
 FROM order_info t1 LEFT JOIN order_detail t2 ON t1.orderId = t2.orderid
                    LEFT JOIN product t3 ON t2.productid = t3.productId
 GROUP BY t1.userId, t1.deskId
 ORDER BY t1.userId
  • 消费金额和次数都在 Top10 的用户，每个桌子坐过的次数: 看高价值用户是否有座位偏好，作为店老板在以后运营时，是否能够尽量满足这种偏好，进一步提高留存。
  • 每个桌子每天空闲的总时长

相关表: desk_info, order_info, order_detail, product

2.6 用户特征

难度级别: 中等

  • 周末控: 只在周末消费的用户

  • 工作日控: 工作日消费多余周末的用户

  • 高频控: 最近30天，累计消费15次，且一周消费不低于3次

  • 学习控: 平均单次消费时长超过4小时的用户

  • 连续3天/7天消费的用户

3 问题思考

  • 拉新的活动策划
  • 留存的活动策划
  • 尝试激活沉默用户
  • 优惠券的折扣力度
  • 不同时段如何个性化定价
  • 关于桌子布局优化有什么建议

练习题

  1. 用户消费频次
    image

  2. 消费频次用户数分布（freq, userNum）
    image
    要注意的是必须将派生表重新命名（如t1）。

  3. 用户最后一次消费日期
    image
    为什么一定要用GROUP BY才成功？

  4. 每天首次付费用户数（firstPayDay, userNum）
    image
    先 SELECT 每个用户首次付费日期，并按用户分组；再按首次付费日期集成。

  5. 每天消费小时数的用户分布（hourNum, userNum）
    image
    MySQL 中用函数 TIMESTAMPDIFF 计算时间差：TIMESTAMPDIFF(interval, time_start, time_end)
    按小时数 group by

  6. 每个桌子累计订单量和用户量（deskId, orderNum, deskNum）
    image
    在一个表里就能得到结果，看了答案之后发现出题意图不是这样，需要连接两个表：
    image
    注意下单的用户会有重复，所以要用到DISTINCT。
    用的是LEFT JOIN而不是JOIN很严谨

  7. 桌子座位数对应的订单量（seatNum, orderNum）
    image

  8. 优惠券过期日期对应用户数（date, userNum）
    image

  9. 每个用户实际消费金额
    因为有折扣(coupon)，所以求实际消费金额需要连接表
    image

posted @ 2022-09-23 22:14  Vonos  阅读(280)  评论(0)  编辑  收藏  举报