销售额下降了，客户到底少买了什么？

foggyprojects
0 阅读1分钟

副标题：从客户汇总追到商品结构，不把相关变化说成原因

上一张结果表告诉我们：2016 年 1～5 月相比 2015 年同期，Svetlana Todorovic 的开票含税销售额下降了 74,889.85 元。

业务人员很自然会继续问一句：

那她到底少买了什么？

这句话和“哪些客户拖累了销售额”不是同一个粒度的问题。前一个问题要继续按商品拆开，后一个问题按客户汇总；如果查询只换了一个展示字段，却没有重新设计分组、关联和核对方式，结果很容易看起来像下钻，实际上已经换了口径。

本文继续使用上一遍已经确认过的发票 QueryModel，选择下降最多的客户 947 / Svetlana Todorovic，比较她在两个同期区间内的商品销售额、购买数量和发票明细行数。

先说明证据边界：本例的商品结果已经在本地 dev/test Runtime 上通过 compose validate、preview 和只读 execute；商品粒度的金额、数量和明细行数回加后，也与上一遍客户汇总一致。它能证明“这位客户在这两个期间少买了哪些商品”，不能单独证明库存不足、价格变化、客户流失或销售人员跟进等原因。

先给结果：下降不是一个抽象百分比

本例固定了两个时间范围：

  • 2015-01-01（含）至 2015-06-01（不含）；
  • 2016-01-01（含）至 2016-06-01（不含）。

这两个范围都使用发票日期，2016 年仍然只代表样例数据中的 1～5 月，不是全年。

客户 947 的客户级汇总是：

指标2015 年同期2016 年同期变化
开票含税销售额93,921.2019,031.35-74,889.85
购买数量2,6431,126-1,517
发票明细行数6426-38

销售额变化比例约为 -79.74%。接着按商品 ID 对齐两期结果，并按销售额变化金额从小到大排序，前 10 项如下：

商品 ID商品2015 年同期2016 年同期变化金额数量变化明细行变化
215Air cushion machine (Blue)21,838.500.00-21,838.50-10-2
16710 mm Anti static bubble wrap (Blue) 50m18,216.000.00-18,216.00-160-2
16432 mm Double sided bubble wrap 50m12,880.000.00-12,880.00-100-1
17020 mm Anti static bubble wrap (Blue) 50m10,557.000.00-10,557.00-90-1
93"The Gu" red shirt XML tag t-shirt (Black) M3,229.200.00-3,229.20-156-2
75Ride on big wheel monster truck (Black) 1/12 scale2,380.500.00-2,380.50-6-1
146Halloween skull mask (Gray) S1,738.800.00-1,738.80-84-1
16232 mm Double sided bubble wrap 10m1,518.000.00-1,518.00-60-1
79"The Gu" red shirt XML tag t-shirt (White) S1,490.400.00-1,490.40-72-1
142Halloween zombie mask (Light Brown) S1,490.400.00-1,490.40-72-1

从这张表可以得到一个比“下降了 79.74%”更具体的事实：这个客户在 2015 年同期购买过的若干包装材料、设备和商品，在 2016 年同期没有对应的发票明细行。

但“没有发票明细行”仍然要加上时间范围限定。它表示在本次查询的 2016 年 1～5 月区间内没有开票记录，不表示这个客户从此永远不买，也不表示这些商品一定没有库存。

下钻时，最重要的是不要换掉事实口径

上一遍客户排行使用的 QM 是：

wwi_oltp_invoice_lines

如果你还不熟悉这几个缩写，可以先把它们理解成三层：

  • QM（QueryModel）是给查询和 AI 使用的业务模型，字段叫“客户”“商品”“开票含税销售额”；
  • TM（TableModel）负责说明这些字段如何从业务表和维度表组织起来；
  • SQL 是 Runtime 根据前两层和本次条件，最终发给数据库执行的查询。

这次没有为了做商品分析而换成另一张表，也没有把销售额改成当前商品价格。使用的仍然是同一个发票 QM：

语义字段本次用途物理来源
customer$id固定客户 947Sales.Invoices.CustomerID
stockItem$id两期商品结果的关联键Sales.InvoiceLines.StockItemID / 商品维度
stockItem$caption商品名称展示Warehouse.StockItems.StockItemName
invoiceDate$id固定两个比较区间Sales.Invoices.InvoiceDate
totalIncludingTax商品销售额汇总Sales.InvoiceLines.ExtendedPrice
quantity商品购买数量汇总Sales.InvoiceLines.Quantity
invoiceLineId发票明细行数Sales.InvoiceLines.InvoiceLineID

因此，“客户汇总 → 商品下钻”真正变化的是分析粒度，不是指标定义：

客户粒度：customer$id + SUM(totalIncludingTax)
商品粒度：customer$id + stockItem$id + SUM(totalIncludingTax)

本例的商品名称来自当前商品维度，事实关联使用的仍然是 stock_item_id。如果生产系统要求还原历史时点的商品名称、品牌或规格，就不能只依赖当前主数据，还需要商品快照或缓慢变化维度；这属于历史维度建模问题，不应由 AI 在查询时临时猜测。

如果第一张表用的是开票含税销售额，第二张表却换成订单金额或当前零售价，即使商品名称看起来对，也不能说这是客户销售额的商品拆解。

两期商品对比，不能只写一条日期过滤

一个期间的商品汇总可以写成下面这样：

{
  "model": "wwi_oltp_invoice_lines",
  "columns": [
    "stockItem$id",
    "stockItem$caption",
    "sum(totalIncludingTax) as revenue"
  ],
  "groupBy": [
    "stockItem$id",
    "stockItem$caption"
  ],
  "slice": [
    {
      "field": "customer$id",
      "op": "=",
      "value": 947
    },
    {
      "field": "invoiceDate$id",
      "op": "[)",
      "value": ["2015-01-01", "2015-06-01"]
    }
  ]
}

两期比较需要分别运行两份这样的商品聚合：一份输出 revenue_2015，另一份输出 revenue_2016。如果把两个日期条件简单地放在同一个查询里，得到的只是同一张结果中的合并金额，不能直接得到两列可比较的期间值。

本次使用的 Compose 脚本把过程分成几层：

const prior = dsl({
  model: "wwi_oltp_invoice_lines",
  columns: [
    "stockItem$id as stock_item_id",
    "stockItem$caption as stock_item_name",
    "sum(totalIncludingTax) as revenue_2015",
    "sum(quantity) as quantity_2015",
    "count(invoiceLineId) as line_count_2015"
  ],
  groupBy: ["stockItem$id", "stockItem$caption"],
  slice: [
    { field: "customer$id", op: "=", value: 947 },
    { field: "invoiceDate$id", op: "[)", value: ["2015-01-01", "2015-06-01"] }
  ]
});

const current = dsl({
  model: "wwi_oltp_invoice_lines",
  columns: [
    "stockItem$id as stock_item_id_2016",
    "stockItem$caption as stock_item_name_2016",
    "sum(totalIncludingTax) as revenue_2016",
    "sum(quantity) as quantity_2016",
    "count(invoiceLineId) as line_count_2016"
  ],
  groupBy: ["stockItem$id", "stockItem$caption"],
  slice: [
    { field: "customer$id", op: "=", value: 947 },
    { field: "invoiceDate$id", op: "[)", value: ["2016-01-01", "2016-06-01"] }
  ]
});

const joined = prior.join(current, "full", [
  { left: "stock_item_id", op: "=", right: "stock_item_id_2016" }
]);

const aligned = joined.query({
  columns: [
    "COALESCE(stock_item_id, stock_item_id_2016) as stock_item_id",
    "COALESCE(stock_item_name, stock_item_name_2016) as stock_item_name",
    "COALESCE(revenue_2015, 0) as revenue_2015",
    "COALESCE(revenue_2016, 0) as revenue_2016",
    "COALESCE(quantity_2015, 0) as quantity_2015",
    "COALESCE(quantity_2016, 0) as quantity_2016",
    "COALESCE(line_count_2015, 0) as line_count_2015",
    "COALESCE(line_count_2016, 0) as line_count_2016"
  ]
});

const metrics = aligned.query({
  columns: [
    "stock_item_id",
    "stock_item_name",
    "revenue_2015",
    "revenue_2016",
    "quantity_2015",
    "quantity_2016",
    "line_count_2015",
    "line_count_2016",
    "revenue_2016 - revenue_2015 as delta",
    "(revenue_2016 - revenue_2015) / NULLIF(revenue_2015, 0) as delta_rate",
    "quantity_2016 - quantity_2015 as quantity_delta",
    "line_count_2016 - line_count_2015 as line_count_delta"
  ]
});

const products = metrics.query({
  slice: [{ field: "delta", op: "<", value: 0 }],
  columns: [
    "stock_item_id", "stock_item_name",
    "revenue_2015", "revenue_2016", "delta", "delta_rate",
    "quantity_2015", "quantity_2016", "quantity_delta",
    "line_count_2015", "line_count_2016", "line_count_delta"
  ],
  orderBy: [
    { field: "delta", dir: "ASC" },
    { field: "stock_item_id", dir: "ASC" }
  ],
  limit: 10
});

Runtime 的 preview 会把这段计划编译成 SQL Server 方言。省略底层视图展开后，外层结构大致是：

SELECT TOP (10)
    stock_item_id,
    stock_item_name,
    revenue_2015,
    revenue_2016,
    delta,
    quantity_delta,
    line_count_delta
FROM (
    SELECT
        stock_item_id,
        stock_item_name,
        revenue_2015,
        revenue_2016,
        revenue_2016 - revenue_2015 AS delta,
        quantity_2016 - quantity_2015 AS quantity_delta,
        line_count_2016 - line_count_2015 AS line_count_delta
    FROM (
        SELECT
            COALESCE(stock_item_id, stock_item_id_2016) AS stock_item_id,
            COALESCE(stock_item_name, stock_item_name_2016) AS stock_item_name,
            COALESCE(revenue_2015, 0) AS revenue_2015,
            COALESCE(revenue_2016, 0) AS revenue_2016,
            COALESCE(quantity_2015, 0) AS quantity_2015,
            COALESCE(quantity_2016, 0) AS quantity_2016,
            COALESCE(line_count_2015, 0) AS line_count_2015,
            COALESCE(line_count_2016, 0) AS line_count_2016
        FROM prior_aggregate
        FULL OUTER JOIN current_aggregate
          ON prior_aggregate.stock_item_id = current_aggregate.stock_item_id_2016
    ) aligned
) metrics
WHERE delta < 0
ORDER BY delta ASC, stock_item_id ASC;

这里的 prior_aggregate 和 current_aggregate 不是数据库中的实体表，而是 Runtime 根据两份 QM DSL 生成的子查询。子查询内部会展开 TM 的视图定义：发票明细和发票头关联，商品 ID 再关联商品维度；totalIncludingTax 最终落到 Sales.InvoiceLines.ExtendedPrice。这就是“同一个业务问题”从 QM 经过 TM，最后变成 SQL 的实际路径。

这里有四个容易被忽略的细节。

1. 两期先各自聚合，再按商品 ID 关联

商品关联必须使用 stock_item_id，不能使用商品名称。名称只是展示字段，可能被修改、重复或包含规格文字；稳定 ID 才适合作为两期的关联键。

而且必须在关联前先聚合到商品粒度。如果把两期的发票明细行直接互相连接，再进行求和，同一个商品的多行明细可能互相相乘，金额会被放大。

2. 用 full join 保留只出现在一侧的商品

一个商品可能只在 2015 年出现，也可能只在 2016 年出现。full join 会把两侧的商品都保留下来，COALESCE 再把缺失的一侧金额、数量和行数补成 0。

所以像上表中的 Air cushion machine，在 2015 年有 21,838.50 元、2016 年没有发票行时，系统才能明确得到：

revenue_2015 = 21838.50
revenue_2016 = 0
delta = -21838.50

如果使用普通内连接，这个商品会因为 2016 年没有对应行而直接消失，恰恰无法回答“少买了什么”。

3. 派生指标单独成层

delta 是在查询中刚刚计算出来的别名。把 delta < 0 放在下一层，查询计划更清楚，也避免不同数据库对同层别名引用的处理差异。

delta_rate 使用 NULLIF(revenue_2015, 0) 保护。一个商品如果 2015 年没有销售、2016 年才出现，就没有合理的同比下降比例，应该显示为空或“无对比基期”，而不是制造一个百分比。

4. 排序要有稳定的第二键

首要排序是 delta ASC，最负的商品排在前面。两个商品变化金额相同时，再按 stock_item_id ASC 排序，这样同一份数据重复执行时顺序稳定，前端分页和 AI 解释也更容易复现。

回加是下钻查询的验收条件

商品表能返回结果，不代表它就是正确的商品下钻。最简单、最有价值的验收方式，是把商品明细重新加回客户粒度，检查是否回到上一张表的数字。

本次 Compose 脚本额外返回了一个 reconciliation 计划，对 full join 后的完整商品结果求和：

指标2015 年同期2016 年同期变化
商品销售额回加93,921.2019,031.35-74,889.85
商品数量回加2,6431,126-1,517
商品明细行数回加6426-38

它与客户汇总完全一致。这一步看似只是加法，实际是在验证三件事：

  1. 客户过滤条件在两个期间都生效；
  2. 商品粒度没有重复计算发票明细；
  3. 下钻使用的度量仍然是同一个 totalIncludingTax。

还要注意，前 10 个下降商品的差值合计是 -75,338.80，比客户总差值更负。因为排行只展示前 10 项，其他商品可能有增长，或者下降幅度较小，对总额形成了抵消。不能把 Top N 明细直接当成总额变化的完整分解。

这也是为什么结果接口最好同时保留两类数据：

products：给业务人员看的下降商品排行
reconciliation：给系统和实施人员做口径验收的完整回加

前者服务阅读和探查，后者服务可信度。两者不应该混成一张只返回前 10 行的表。

回加计划的核心写法如下：

const reconciliation = metrics.query({
  columns: [
    "sum(revenue_2015) as product_revenue_2015",
    "sum(revenue_2016) as product_revenue_2016",
    "sum(revenue_2016 - revenue_2015) as product_delta",
    "sum(quantity_2015) as product_quantity_2015",
    "sum(quantity_2016) as product_quantity_2016",
    "sum(line_count_2015) as product_line_count_2015",
    "sum(line_count_2016) as product_line_count_2016"
  ],
  limit: 1
});

return { plans: { products, reconciliation } };

两个期间的查询还必须使用同一个 namespace、调用者和模型权限上下文。否则，即使 QM、商品 ID 和计算公式都相同，2015 年和 2016 年看到的客户范围不同，回加也只能证明一个被权限切开的子集，不能证明全量客户结果。

“少买了”仍然不等于“为什么少买”

现在可以确认的事实是：

  • 这位客户在两个同期区间内的开票含税销售额减少了；
  • 若干商品在 2015 年同期有发票明细，在 2016 年同期没有对应明细；
  • 客户购买数量和发票明细行数也同时减少；
  • 商品明细可以回加到客户汇总。

但以下说法仍然没有证据：

  • “因为仓库缺货，所以没有买”；
  • “因为涨价，所以销量下降”；
  • “客户已经流失”；
  • “销售人员没有维护这个客户”；
  • “这些商品被另一批商品替代了”。

要继续分析这些原因，至少要补充相应事实：库存快照、可售库存、价格与折扣、订单状态、客户状态、销售跟进记录等。并且每个原因都要先写出可验证的规则，例如“库存不足”不能只看当前库存，而要检查相同期间和商品粒度下的可售库存与需求记录。

查询引擎可以把这些事实按明确的键和时间范围组合起来，但它不会因为两个字段同时下降，就自动把它们解释成因果关系。

从聊天结果回到业务系统表格

业务人员不需要这样提问：

请使用 wwi_oltp_invoice_lines，按 stockItem$id 分组，计算 sum(totalIncludingTax)，再做 full join。

更自然的问法是：

Svetlana Todorovic 去年同期销售额下降最多，具体少买了哪些商品？

系统内部仍然需要保存完整上下文：

客户：947 / Svetlana Todorovic
事实：发票明细
指标：开票含税销售额
日期：发票日期
对比期：2015-01-01 至 2015-06-01
当前期：2016-01-01 至 2016-06-01
下钻粒度：商品
关联键：stock_item_id
结果：下降商品排行 + 完整商品回加

如果业务系统已经有商品分析表格，客户排行这一行可以把 customer_id=947 和两个日期范围带入同一个 QM 的商品查询；BI 也可以使用同一事实来源或经过明确对齐的模型。这样聊天窗口、业务表格和 BI 图表至少能对同一个“开票含税销售额”做解释，而不是各自猜一套商品结果。

不过，本文样例只验证了查询计划、SQL 和数据库结果，没有接通真实的客户详情或商品详情页面。因此这里说的是“结果可以继续按同一语义模型查询”，不是宣称已经完成页面跳转。

对实施和架构的一个提醒

很多系统把“下钻”理解成在结果上再加一个字段。真正可维护的下钻至少需要同时固定：

上一层的业务主键
   ↓
下一层的事实粒度
   ↓
两期或多期的关联键
   ↓
缺失一侧的补零规则
   ↓
回加到上一层的验收规则

这几项中只要少一项，AI 就可能返回一张看似合理、却无法解释的明细表：

  • 没有稳定 ID，就可能按名称错误合并；
  • 没有先聚合，就可能发生明细行相乘；
  • 没有 full join，就会漏掉只在一个期间出现的商品；
  • 没有回加，就没人知道下钻是否换了销售额口径；
  • 没有权限上下文，两期比较的可能不是同一批数据。

所以，业务系统、BI 和 AI 要真正共享一套可核对的分析基础，重点不只是“能不能问出商品名称”，而是每次从客户跳到商品时，粒度、键、度量、日期和权限是否仍然可追溯。

最后

“销售额下降了，客户到底少买了什么？”是一个很适合交给 AI 的问题，但它不应该由 AI 凭经验猜答案。

一个可靠的回答应该经过这条链路：

自然问题
   ↓
锁定客户、时间范围和销售额口径
   ↓
同一 QM 上分别聚合两期商品结果
   ↓
按商品 ID 对齐并计算差值
   ↓
用回加验证商品明细没有换口径
   ↓
把下降事实交给库存、价格和订单事实继续验证

在这个例子里，我们可以很明确地说：Svetlana Todorovic 在 2016 年同期没有开票购买若干 2015 年同期购买过的商品，而客户销售额的净变化为 -74,889.85 元。

但我们还不能说她为什么少买。把事实变化和原因假设分开，正是 AI 分析在企业场景中需要建立的基本边界。

后续仍回到本专栏主线：继续围绕业务系统表格、BI 和 AI 问数如何共享可核对的语义层，下一篇选题不在本文预先展开。

avatar
文章
阅读
粉丝