示例成品 · 平台演示，按左边这组点选真跑出来的
直接按这个顺序查，别从埋点反查。
先默认一个口径：新客=首次下“有效订单”。下面 SQL 默认有效订单为 `PAID/SUCCESS/COMPLETED`，如果你家口径不是这个，把 `order_status` 条件换掉。
默认字段名先对应好：
- 订单表 `orders`：`user_id, order_id, create_time, order_status, is_first_order`
- 支付表 `payment`：`order_id, user_id, pay_time, pay_status`
- 埋点日志 `events`：`user_id, event_name, event_time, order_id`
---
## 1. 先看订单表真实新客曲线：断崖还是平滑跌
```sql
SELECT DATE(first_order_time) AS d,
COUNT(*) AS new_cust
FROM (
SELECT user_id, MIN(create_time) AS first_order_time
FROM orders
WHERE order_status IN ('PAID','SUCCESS','COMPLETED')
GROUP BY user_id
) t
WHERE first_order_time >= CURDATE() - INTERVAL 14 DAY
GROUP BY DATE(first_order_time)
ORDER BY d;
```
判断：
- 某一天直接掉一半：优先查数据管道、口径、埋点上报。
- 平滑往下掉：优先查渠道投放、活动结束、业务下跌。
如果订单表太大，上面的 SQL 慢，换窗口函数版：
```sql
WITH ranked AS (
SELECT user_id, create_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time ASC) AS rn
FROM orders
WHERE order_status IN ('PAID','SUCCESS','COMPLETED')
)
SELECT DATE(create_time) AS d, COUNT(*) AS new_cust
FROM ranked
WHERE rn = 1
AND create_time >= CURDATE() - INTERVAL 14 DAY
GROUP BY DATE(create_time)
ORDER BY d;
```
---
## 2. 三张表对账，找出断点在哪一层
### ① 支付表：每天支付成功用户数
```sql
SELECT DATE(p.pay_time) AS d,
COUNT(DISTINCT o.user_id) AS pay_users
FROM payment p
LEFT JOIN orders o ON o.order_id = p.order_id
WHERE p.pay_status = 'SUCCESS'
AND p.pay_time >= CURDATE() - INTERVAL 14 DAY
GROUP BY DATE(p.pay_time)
ORDER BY d;
```
### ② 埋点日志：每天首次下单事件用户数
```sql
SELECT DATE(event_time) AS d,
COUNT(DISTINCT CASE WHEN event_name = 'first_order' THEN user_id END) AS first_order_event_users,
COUNT(DISTINCT CASE WHEN event_name = 'order_create' THEN user_id END) AS order_create_event_users
FROM events
WHERE event_time >= CURDATE() - INTERVAL 14 DAY
GROUP BY DATE(event_time)
ORDER BY d;
```
把三张表结果放一起看，断点判断表如下：
| 现象 | 结论 |
|---|---|
| 订单表新客掉，支付表没掉 | 订单状态口径/订单写入/首单判断有问题 |
| 订单表新客没掉，埋点掉 | 埋点上报故障，不是业务跌 |
| 订单表新客掉，埋点没掉 | 订单表数据同步/支付状态过滤有问题 |
| 三张表都掉 | 真实业务跌，或渠道/活动停量 |
| 订单表新客没掉，报表掉 | BI 报表口径/过滤条件变了 |
---
## 3. 按你说的三块重点查
### ① 首次行为有没有
不要信订单表的 `is_first_order` 标记，先用真实历史订单算一遍，再和标记字段对比。
```sql
SELECT DATE(create_time) AS d,
COUNT(DISTINCT user_id) AS marked_new
FROM orders
WHERE is_first_order = 1
AND create_time >= CURDATE() - INTERVAL 14 DAY
AND order_status IN ('PAID','SUCCESS','COMPLETED')
GROUP BY DATE(create_time)
ORDER BY d;
```
如果这条和第一步的真实新客曲线从某天开始分叉，就是 `is_first_order` 标记逻辑断了。
另外查一下身份关联有没有断：
```sql
SELECT DATE(create_time) AS d,
SUM(CASE WHEN user_id IS NULL OR user_id = '' OR user_id = 0 THEN 1 ELSE 0 END) AS bad_user
FROM orders
WHERE create_time >= CURDATE() - INTERVAL 14 DAY
GROUP BY DATE(create_time)
ORDER BY d;
```
如果某天 `bad_user` 突然升高，说明用户身份没关联上，新客自然被漏掉。
---
### ② 关键事件漏没漏
先看埋点事件名有没有变化或消失：
```sql
SELECT event_name, COUNT(*) AS cnt
FROM events
WHERE event_time >= CURDATE() - INTERVAL 7 DAY
GROUP BY event_name
ORDER BY cnt DESC;
```
如果 `first_order` 或 `order_create` 事件量突然很少，或事件名变了，就是埋点上报断了。
再对比订单表和埋点订单创建事件：
```sql
SELECT DATE(o.create_time) AS d,
COUNT(DISTINCT o.order_id) AS order_rows,
COUNT(DISTINCT e.order_id) AS matched_event_orders
FROM orders o
LEFT JOIN events e
ON e.order_id = o.order_id
AND e.event_name = 'order_create'
WHERE o.create_time >= CURDATE() - INTERVAL 7 DAY
GROUP BY DATE(o.create_time)
ORDER BY d;
```
如果 `matched_event_orders` 在断点日大幅低于 `order_rows`，说明订单写进表了，但埋点没报上去。
再看订单状态分布有没有突变：
```sql
SELECT order_status, COUNT(*) AS cnt
FROM orders
WHER
点左边「开工 · 直接出成品」，出一份你自己的版本（文字免费）