SQL 用户运营分析案例（PostgreSQL）

基于模拟业务数据，完成用户分层、活动漏斗、留存率、优惠券核销与新老用户消费对比分析。

场景通用于网约车、电商等以订单为核心的用户运营业务。

---

表结构

表名	说明
users	用户基础信息（注册日期、城市、新老用户标记）
orders	订单记录（金额、城市、是否使用优惠券）
events	用户行为事件流（用于漏斗分析）

建表与样本数据：schema.sql

---

分析案例

01 用户分层

业务问题：按最近30天下单频次，把用户分成高频、中频、低频、沉默四层，指导差异化运营策略。

查询文件 →

输出样例：

用户层级	用户数	占比百分比
高频用户	0	0.0%
中频用户	1	14.3%
低频用户	3	42.8%
沉默用户	3	42.9%

结论：沉默用户占比超40%，是召回活动的核心目标群体；低频用户占比高，需要设计提频激励（如消费满N次送权益）；高频用户当前为空，说明样本期较短，实际业务中应重点维护这部分用户体验而非用大额折扣拉低利润。

---

02 活动漏斗转化分析

业务问题：3月10日推送活动中，从收到推送到最终下单，每一步流失了多少人？哪个环节是最大断点？

查询文件 →

输出样例：

收到推送	点击推送	进入活动页	领取优惠券	完成下单	整体转化率
5	4	3	2	1	20.0%

各步转化率：

环节	转化率	流失判断
收到 → 点击	80%	正常
点击 → 进入页面	75%	可接受
进入页面 → 领券	67%	需关注，落地页是否过于复杂？
领券 → 下单	50%	最大流失点，优惠券门槛或有效期问题

结论：领券到下单流失50%是最大断点，优先排查优惠券门槛是否过高、有效期是否过短。整体转化率20%在推送活动中属于正常区间，后续可A/B测试不同券面额对转化率的影响。

---

03 留存率分析

业务问题：3月1日下单的用户，第2天和第7天还有多少人回来下单？留存率是判断活动质量的核心指标。

查询文件 →

输出样例：

基准日用户数	次日回访用户数	次日留存率	七日回访用户数	七日留存率
2	1	50.0%	1	50.0%

结论：样本数据期间次日留存50%，高于电商平台行业均值（5-15%）。需注意：样本量极小（2人），实际业务中需要千人以上样本才具统计意义。若活动结束后留存率明显回落，说明活动带来的是"借来的增长"而非真实习惯养成。

---

04 优惠券核销率与ROI分析

业务问题：这次活动发出的优惠券，各城市核销情况如何？每花1块补贴带来多少GMV？

查询文件 →

输出样例：

城市	总订单数	用券订单数	核销率	优惠券总成本	用券订单GMV	简化版ROI
杭州	5	3	60.0%	18.00	73.50	4.08
南京	2	1	50.0%	10.00	38.00	3.80
上海	3	1	33.3%	5.00	18.50	3.70

结论：杭州核销率最高（60%）且ROI最优（4.08），是本次活动效果最好的城市，可加大下次活动在杭州的券发放量。上海核销率偏低（33%），需排查是否因券面额不够吸引或本地竞争对手活动干扰。注意：此处ROI为简化版（用券GMV/券成本），未排除"本来也会下单"的用户，实际增量ROI会低于此数值。

---

05 新老用户消费行为对比

业务问题：新用户和老用户在消费金额、下单频次、用券行为上有什么差异？差异如何指导运营策略？

查询文件 →

输出样例：

用户类型	用户总数	总订单数	人均订单数	平均客单价	用券率	人均消费总额
new	4	4	1.00	25.38	75.0%	25.38
returning	3	6	2.00	30.58	33.3%	61.17

结论：老用户人均订单数是新用户的2倍，客单价高25%，是核心收入来源，运营重点应放在体验维护而非折扣补贴。新用户用券率高达75%，价格敏感，需要优惠驱动首次消费习惯，但应设计"阶梯式退出"避免长期依赖补贴。新用户人均消费总额仅为老用户的41%，提升新用户LTV是增长的核心命题。

---

关键语法索引

语法	出现在	用途
WITH（CTE）	01、03	把复杂查询拆成可读的步骤
CASE WHEN	01、02、04、05	条件分类与条件计数
LEFT JOIN	01、03、05	保留左表全量，右表无匹配填NULL
COUNT(DISTINCT)	02、05	去重计数，避免同一用户被重复计算
COALESCE	01	NULL值兜底，沉默用户订单数补0
NULLIF	02、03、04、05	防止除以0报错
窗口函数 OVER()	01	计算各分层占比时保留总数作分母