指标体系与口径

本章目标：能区分原子指标、派生指标、复合指标，能写出 GMV、AOV、复购率、日活买家、迟到率的 SQL，并解释含运费、取消单、支付差异等口径问题。 前置知识：数据仓库入门、SQL 核心语法、窗口函数 预计用时：90 分钟

数仓里最难的不是写 SQL，而是让十个人对同一个指标算出同一个数。本章解决的就是“口径”问题。

1. 为什么口径会打架

同一个数据集里，两个人都能叫“GMV”，但数可能不一样：

差额 259.00 元，全部来自运费。两个人如果没在指标定义里写清楚含不含运费，报表就一定会打架。

除了运费，还有四类常见分歧：

• 取消/不可用订单算不算：示例里有 3 个这类订单，默认不算。
• 没有明细的订单怎么算：真实数据里可能存在，AOV 分母要排除。
• 分析窗口算哪个：全量和某个时间窗口的 GMV 本来就会不同，必须写清范围。
• 支付金额能不能当 GMV：不能。支付表可能含取消订单和无明细订单的付款，与有效明细口径存在差额。

2. 指标体系的三个层次

2.1 原子指标

原子指标 = 业务过程 + 度量 + 聚合方式，不可再拆。

原子指标只描述“算什么”，不带时间和业务限定。

2.2 修饰词与派生指标

修饰词是限定条件：时间（2024 年 1 月）、城市（北京）、品类（数码）、订单状态（已签收）。

派生指标 = 原子指标 + 修饰词 + 时间周期。

例如“2024 年 1 月北京数码品类的含运费 GMV”：

原子指标：含运费 GMV
修饰词：city = '北京'，category = '数码'
时间周期：2024 年 1 月

2.3 复合指标

复合指标由多个指标运算得出：

复合指标不能直接加总或求平均。把 20 个月的复购率相加再除以 20，得到的是“比率的平均”，不是整体复购率。

2.4 三层关系

原子指标：SUM(item_amount_with_freight)
    + 修饰词：city = '北京'，时间 = 2024 年 1 月
    = 派生指标：2024 年 1 月北京 GMV

派生指标：GMV、订单数
    + 运算：GMV / 订单数
    = 复合指标：客单价

3. 指标字典怎么写

指标字典是口径的唯一来源。没有它，每个人都在自己的 SQL 里“发明”指标。

提示：指标字典要版本化。口径一改，必须记录变更原因、生效日期、影响范围，并通知所有下游。不要悄悄改 SQL。

4. 核心指标的定义与 SQL

以下 SQL 都基于清洗后的 dwd_order_detail 和 dwd_order。前者一行一件商品，后者一行一个有效订单，已排除取消/不可用订单。

4.1 GMV

定义：有效订单中，商品金额加运费的总额。

SELECT
    ROUND(SUM(item_amount_with_freight), 2) AS gmv_with_freight,
    ROUND(SUM(item_amount), 2)              AS gmv_no_freight
FROM dwd_order_detail;

结果：

gmv_with_freight = 1509.00
gmv_no_freight   = 1250.00

如果业务要“不含运费 GMV”，就取第二列。两个指标可以同时存在，但报表标题必须写清楚。

4.2 AOV 客单价

定义：GMV 除以订单数。分母要选“有明细的有效订单数”，示例里是 9。

SELECT ROUND(SUM(item_amount_with_freight) / COUNT(DISTINCT order_id), 2) AS aov
FROM dwd_order_detail;

结果 167.67。

分母换成 12（原始订单）或 10（只排除取消单），结果都会变。AOV 必须和 GMV 使用同一批订单，否则分子分母不匹配。

4.3 复购率

定义：统计周期内有效订单数大于等于 2 的买家，占所有下单买家的比例。

WITH buyer AS (
    SELECT buyer_id,
           COUNT(DISTINCT order_id) AS order_cnt
    FROM dwd_order
    WHERE buyer_id IS NOT NULL
    GROUP BY buyer_id
)
SELECT
    COUNT(*) AS buyer_cnt,
    COUNT(*) FILTER (WHERE order_cnt >= 2) AS repeat_buyer_cnt,
    ROUND(COUNT(*) FILTER (WHERE order_cnt >= 2) * 100.0 / COUNT(*), 2) AS repeat_rate_pct
FROM buyer;

示例数据没有买家 ID，代入真实项目的 buyer_id 后就能算出具体人数和复购率。

三个必须写进字典的细节：统计周期（全部历史还是最近 90 天）、用户口径（订单级客户 ID 还是稳定的自然人 ID）、订单口径（是否含取消）。换任何一个，结果都会变。同一个人可能有多个收货地址，对应多个订单级客户 ID，复购必须用稳定的自然人 ID。

4.4 首购月 cohort 回购率

定义：按买家首次下单月份分组，看在后续月份是否再次下单。这是没有行为日志时，能做的最接近“留存”的指标。

WITH first_order AS (
    SELECT buyer_id,
           MIN(date_trunc('month', order_date)) AS first_month
    FROM dwd_order
    GROUP BY buyer_id
),
buyer_month AS (
    SELECT DISTINCT
           o.buyer_id,
           f.first_month,
           date_trunc('month', o.order_date) AS order_month
    FROM dwd_order o
    JOIN first_order f USING (buyer_id)
)
SELECT
    first_month,
    COUNT(DISTINCT buyer_id) AS cohort_buyers,
    COUNT(DISTINCT buyer_id) FILTER (
        WHERE order_month > first_month
    ) AS repeat_buyers,
    ROUND(COUNT(DISTINCT buyer_id) FILTER (
        WHERE order_month > first_month
    ) * 100.0 / COUNT(DISTINCT buyer_id), 2) AS repeat_rate_pct
FROM buyer_month
GROUP BY first_month
ORDER BY first_month;

注意：这是“首购后是否回购”，不是“次日留存”。只有订单数据、没有登录和浏览事件时，不能算 D1/D7 留存。

4.5 订单状态分布

订单状态是快照，不是漏斗。同一时刻各状态订单数如下：

canceled + unavailable = 3，统一视为无效订单。shipped 仍在履约中，保留在有效订单里，但不进入履约时长和迟到率分母。真实业务还会有 delivered、invoiced、processing 等状态，判断规则相同。

4.6 日活买家

定义：一天内有有效下单的去重买家数。不是 DAU，项目没有登录和浏览日志。

WITH daily AS (
    SELECT order_date,
           COUNT(DISTINCT buyer_id) AS buyer_cnt
    FROM dwd_order
    GROUP BY order_date
)
SELECT
    order_date,
    buyer_cnt,
    ROUND(AVG(buyer_cnt) OVER (ORDER BY order_date
                               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS buyer_ma7
FROM daily
ORDER BY order_date;

实际计算时，注意分母是“有订单的活跃日”，不是日历天数；移动平均用于抹平周末波动，不能替代原始值。大促、节假日等峰值要单独标注，否则会被误判成异常。

注意：口径要写清是“当天下单的去重买家”，不是“当天登录用户”。两者不能混用。

4.7 迟到率

定义：签收时间晚于预计送达时间的有效订单，占“签收和预计时间都不为空”的有效订单比例。

SELECT
    COUNT(*) FILTER (
        WHERE delivered_at IS NOT NULL AND estimated_at IS NOT NULL
    ) AS late_base,
    COUNT(*) FILTER (WHERE is_late) AS late_order_cnt,
    ROUND(COUNT(*) FILTER (WHERE is_late) * 100.0
          / NULLIF(COUNT(*) FILTER (
              WHERE delivered_at IS NOT NULL AND estimated_at IS NOT NULL
          ), 0), 2) AS late_rate_pct
FROM dwd_order;

结果的分子是签收时间晚于预计送达的订单，分母是签收和预计时间都不为空的订单。取消单和未签收单不进入分母，否则会人为拉低迟到率。

5. 口径差异：同一指标的不同算法

5.1 含运费与不含运费

差额 259.00 元。两个都不算错，错的是不写明用了哪个。

5.2 取消、不可用、无明细、支付差异

支付总额和有效明细 GMV 的差，通常来自取消订单付款、无明细订单付款、分期或多笔支付；逐单差额检查的是明细口径内部是否对得上。两者回答不同问题，不要混用。

5.3 退款

取消订单和退款不是一回事：

• 取消订单：订单没有成交，不计入 GMV。
• 退款：订单成交后发生退款，GMV 可以计、也可以设“净 GMV = GMV - 退款金额”。

如果数据里没有退款表，就只能算“成交口径”。取消/不可用订单即使仍有支付，也不能判断是否已退款。业务问退款率时，必须在指标字典里标注“当前数据不支持”，而不是硬编一个公式。

5.4 下单日期与时间窗口

GMV 按 order_date 统计。至少要有两套范围：

• 全量有效订单：用于总览、复购、履约；
• 分析窗口：用于月度趋势和品类/卖家分析，通常避开冷启动期和数据尾部。

两个数不一样不是错误，是范围不同。报表和指标字典必须写明截止日期和是否包含当天。

5.5 用户口径

订单级客户 ID 通常跟着收货地址走，稳定的自然人 ID 才代表同一个人。同一个人换地址会产生多个订单级客户 ID。用错 ID 会把复购率算低，所有买家、复购口径必须统一使用稳定的自然人 ID。

5.6 口径对齐流程

1. 业务、产品、数据三方共同确认定义，写进指标字典。
2. 数据开发在 DWD 层固化清洗规则，不在 ADS 各写各的。
3. 新老口径双跑一段时间，逐日对账差异。
4. 口径变更走版本记录，通知下游并评估历史报表。
5. 在 ADS 表字段注释里写清过滤条件和时间口径。

6. 动手示例：一张总览表

CREATE OR REPLACE TABLE ads_gmv_overview AS
SELECT
    COUNT(DISTINCT order_id) AS orders_with_items,
    ROUND(SUM(item_amount_with_freight), 2) AS gmv,
    ROUND(SUM(item_amount), 2) AS goods_gmv,
    ROUND(SUM(item_amount_with_freight) - SUM(item_amount), 2) AS freight_amount,
    ROUND(SUM(item_amount_with_freight) / COUNT(DISTINCT order_id), 2) AS aov
FROM dwd_order_detail;

运行结果应能对上：

orders_with_items = 9
gmv               = 1509.00
goods_gmv         = 1250.00
freight_amount    = 259.00
aov               = 167.67

这张总览基于明细表，所以订单数是“有明细的有效订单”。真正的有效订单数要从 dwd_order 取，两者可能相差几单：差额就是没有明细的订单。

再写一条对账 SQL，确认订单事实和明细事实一致：

SELECT
    (SELECT ROUND(SUM(order_amount), 2) FROM dwd_order) AS order_amt,
    (SELECT ROUND(SUM(item_amount_with_freight), 2) FROM dwd_order_detail) AS detail_amt;

两列应该相等。不等时先查订单汇总是否按订单粒度去重，再查明细是否重复。

7. 常见坑

1. 只写 SQL 不写口径：数字对了，但没人知道怎么算的。
2. 对复合指标求平均：AVG(客单价) 不是整体客单价。
3. 分子分母口径不一致：GMV 排除取消单，订单数却没排除。
4. 用支付金额代替 GMV：支付表含取消订单，且与明细有差额。
5. 复购率不写用户口径：用订单级客户 ID 会把同一个人拆成多个用户。
6. 把订单状态当漏斗：状态是快照，不能直接算转化。
7. 把日活买家叫 DAU：没有登录和浏览日志，不能叫 DAU。

8. 在线练习

自测：

• 能写出 GMV 含运费和不含运费两种口径，并说明各自用途。
• 能解释 AOV 的分母为什么是“有明细的有效订单数”，而不是原始订单数。
• 能写出复购率 SQL，并说清订单级客户 ID 和稳定自然人 ID 的差异。
• 能解释同比、环比、累计占比分别用什么窗口函数。

9. 面试延伸

面试题：两个同事的 GMV 对不上，你怎么排查？

是什么：GMV 口径差异通常来自时间范围、订单状态、运费、去重、退款和日期字段。

解决什么问题：先对齐指标定义，再逐项比对：是否含运费、是否排除 cancel/unavailable、是否按明细去重、全量还是分析窗口、是否扣退款。然后用同一张 DWD 表分别跑两种口径，定位差异来自哪一步。

我的实操边界：项目线里我核对过含运费与不含运费的差额、取消/不可用订单数量和支付与明细的差异；没有在生产环境处理过跨部门口径争议。

面试题：GMV 要不要含运费、要不要扣退款？

是什么：GMV 是成交总额指标，含不含运费、扣不扣退款没有绝对标准，取决于业务定义。

解决什么问题：含运费更接近用户实付，不含运费更接近商品销售额；退款可以单独看退款率，也可以计算净 GMV。关键是定义唯一、写进指标字典、全站统一。

我的实操边界：项目默认 GMV 含运费、不扣退款（数据里没有退款表），另外提供不含运费口径。我理解真实电商通常还会区分下单 GMV、支付 GMV、成交 GMV。

面试题：复购率和留存率分别怎么定义？

是什么：复购率是购买用户中重复购买的比例；留存率是某批用户在第 N 天仍活跃的比例。

解决什么问题：复购率衡量用户重复消费，留存率衡量用户是否回来。两者都要明确时间窗口、用户口径和行为定义，否则数字不可比。

我的实操边界：项目线的复购率按稳定自然人 ID 和全部有效订单计算；数据里没有行为日志，做不了 D1/D7 留存，只能做首购月 cohort 回购率。

10. 本章验收

• 能区分原子指标、派生指标、复合指标，并各举两个例子
• 能默写 GMV 含运费和不含运费的 SQL，并说出两者差额的来源
• 能解释 AOV 的分母为什么是“有明细的有效订单数”，而不是原始订单数
• 能独立写出复购率、首购月 cohort、日活买家和迟到率的 SQL
• 能说清取消、无明细、支付差异、退款、时间窗口五类口径差异
• 能写出一份包含定义、公式、来源、过滤条件、时间口径的指标字典