Seller Metrics Engine

YAML-declared metrics and dimensions, executed as a planner/fetcher/joiner/aggregator pipeline over rpt_* report tables and dynamic_attributes_values. Surfaced to the LLM via metrics-engine-local-claude-code (and prod) MCP servers.

Where things live

Path	Purpose
mcp_server/seller_metrics_engine/definitions/metrics.yaml	Every metric — source/formula/grain/category
mcp_server/seller_metrics_engine/definitions/dimensions.yaml	Every dimension — table_column_map, optional classification or enricher binding
mcp_server/seller_metrics_engine/definitions/__init__.py	YAML loader — loaded once at import
mcp_server/seller_metrics_engine/tools/query.py	MCP tool entrypoints (query_metrics, compare_periods)
app/services/seller_metrics_engine/engine/	Resolver, planner, joiner, aggregator, executor
app/services/seller_metrics_engine/engine/enricher/	Stage-typed enricher framework — see Enrichers
app/services/seller_metrics_engine/strategies/	report_table.py (rpt_* fetch) + dynamic_attribute.py (EAV pivot)

Pipeline at a glance

Every stage transition triggers run_enrichers_at(stage, ctx) — see Enrichers for hooks.

Joining tables (FULL OUTER)

A query that spans tables (e.g. PnL economics + Ads + Business-Report) fetches one DataFrame per table, each at a per-(entity, period) grain, and merges them on the shared dimension + temporal columns. The merge is a FULL OUTER join, so the result is the union of every table's keys — no source row is ever dropped.

This matters because the merge must be symmetric. A LEFT join is anchored on one table and silently drops rows from the others; the anchor was historically "the table with the most SELECT columns" (planner.py). In an ad-heavy PnL query the Ads table (which only has rows for advertised SKU-weeks) won that tiebreak and became the anchor — so every PnL row for a SKU that wasn't advertised was dropped, under-reporting Sales, COGS and margin. With FULL OUTER the anchor choice no longer affects the row set: PnL sales survive the Ads join, and ad spend on an unsold SKU survives the PnL join.

Mechanics (joiner.py):

• Compound key on all shared dimensions — a table pair joins on every dimension both tables carry, not just the coarsest. PnL (per msku), Ads (per advertised_sku) and the Business-Report (per sku) all share both child_asin and sku; keying only on child_asin would cross-multiply each table's SKU rows under a shared ASIN (a multi-SKU-per-ASIN fan-out that doubled/tripled every no-breakdown aggregate). Joining on (child_asin, sku, period) keeps the merge 1:1. The planner emits the full key list (JoinStep.key_dims); the joiner canonicalises and renames each key to its dimension name.
• Key coalescing — joins use Polars how="full", coalesce=True so the shared dimension/temporal keys merge into one column (an outer row keeps its key from whichever side it came from). Non-key overlapping columns (e.g. weekly_end) come back with a _right suffix and are coalesced so outer rows keep them too.
• Presence markers — before joining, each table's rows are tagged with a __present_<table>__ = true column. After the outer join this is true on rows that table contributed and null on outer-introduced rows. Required-metric flagging (below) uses it so a PnL metric isn't falsely flagged "missing" on a row that simply never existed in the PnL table (e.g. an ad-only SKU-week). The markers are dropped immediately after flagging, before collapse.

Metrics from a table that didn't contribute a given row are null there; SUM aggregation skips nulls (so they count as 0) and handle_null fills optional components — a SKU with sales but no ads correctly reads ad_spend = 0.

Canonical output naming

A single final presentation step (executor.py) renames every output column to its canonical key, applied identically to the per-period frame and the range-summary rows:

• Metrics → their metric key (e.g. source alias spend → ad_spend).
• Dimensions → their dimension key, regardless of the physical report column or whether a join ran (asin/advertised_asin → child_asin, msku/advertised_sku → sku). The joiner canonicalises multi-table queries as a side-effect of its rename, but a single-table query never hits the joiner — without this step its output would leak the physical column name (asin, msku). Normalizing here guarantees a stable output contract for every query shape; it's a presentation-only rename (never touches values), and it's collision-safe (skips a dimension whose canonical column already exists).

Required-metric validation

A universal pipeline step (between post_join enrichers and the internal-dim collapse). It scans resolved.all_metrics for any with required: true and checks the joined DataFrame for null rows in the metric's column. Affected rows are flagged in an error_rows manifest naming the entity and the missing metric(s) — the row is not dropped and the query is not aborted, so the FE can still render the row with the missing cell highlighted. The entity is named under canonical dim keys (sku / child_asin / parent_asin / seller_id — whichever the DF carries), mapped from the physical report columns (msku/asin) so the manifest has one stable shape whether the query was single- or multi-table.

The step runs at source-row grain — before the collapse step drops the SKU/asin columns — so the manifest can identify which specific entities are missing data. It is presence-aware: a required metric is only flagged on rows where its source table actually contributed (via the __present_<table>__ markers from the join). An outer-join-introduced row — e.g. ad spend on a SKU with no sale that week — has null PnL metrics but is not flagged, because that SKU-week simply never existed in the PnL table (it's not missing data).

# In the executor pipeline, after post_join enrichers:
error_rows = self._flag_required_metrics(ctx.df, resolved)

The MCP layer surfaces the manifest as a structured missing_metrics list, so the LLM can tell the user precisely what needs to be configured (COGS or FBM shipping fees entered for those SKUs, MB commission configured, etc.) instead of returning a silent null cascade through margin formulas. Cost lines that also carry handle_null: {replace: 0} are simultaneously flagged (gap visible) and treated as 0 in the margin (formula still computes).

To make a metric required, add required: true to its YAML entry. No registration step — the validator is part of the engine, not an enricher.

Defining a metric

Two flavors:

L1 — sourced directly from a column

- key: pnl_sales
  display_name: 'PNL: Sales'
  description: Gross sales totals from SKU Economics.
  sources:
  - alias: pnl_sales
    table: rpt_pnl_sku_economics
    column: sales
  metric_refs: null
  formula: null
  supported_granularities: [weekly, monthly, quarterly]
  aggregation_rule: SUM
  required: true
  format: $
  category: PNL
  hierarchy_level: L1

L1 — SQL expression (computed per source row)

For per-row arithmetic that must aggregate as SUM(a*b) rather than SUM(a)*SUM(b):

sources:
- alias: some_per_unit_total
  table: rpt_pnl_sku_economics
  expr: some_value_per_unit * units_sold

Engine emits SUM(some_value_per_unit * units_sold) in the SELECT, so per-row arithmetic is preserved before aggregation. YAML is trusted — same blast radius as Python formula: strings. (Illustrative — no live metric uses expr: today; the per-unit cost lines like COGS and FBM fees are enricher-computed from dynamic attributes, see PnL Metrics.)

L2/L3 — computed from other metrics

- key: pnl_pre_ad_margin
  sources: null
  metric_refs: [pnl_sales, pnl_landed_cogs_amt, pnl_referral_fee_amt, pnl_fba_fee, pnl_fbm_shipping_fees_amt, pnl_mb_commission_amt]
  formula: pnl_sales - pnl_landed_cogs_amt - pnl_referral_fee_amt - pnl_fba_fee - pnl_fbm_shipping_fees_amt - pnl_mb_commission_amt
  aggregation_rule: Recalculate

Recalculate means: SUM the source components first during aggregation, then re-evaluate the formula. Use it for any ratio/total whose pieces must be summed before division (ACoS, margin %, etc.).

Computed by enricher (no formula, no source)

- key: pnl_mb_commission_amt
  sources: null
  metric_refs: [pnl_sales]      # forces rpt_pnl_sku_economics fetch
  formula: null
  enricher: mb_commission        # ← Python-implemented
  aggregation_rule: SUM
  required: true

See Enrichers for how this is wired.

Field reference

Field	Purpose
key	Unique identifier; what the LLM passes in metrics: [...]
display_name, description	LLM-facing
sources	List of {alias, table, column} or {alias, table, expr}
metric_refs	Transitive deps — pulled into the plan even if not user-requested
formula	Python expression evaluated on the polars DF (column refs by alias)
enricher	Name of a registered Python enricher that fills this metric
aggregation_rule	SUM / Average / Recalculate / null
required	If true, rows with a null value are flagged in the error_rows manifest (not dropped/aborted) — see Required-metric validation
supported_granularities	Which granularity query values are valid
format	$ / % / int — output formatting
category, hierarchy_level	Browsing/grouping in list_metrics

Defining a dimension

- key: sku
  display_name: SKU
  description: Seller-assigned SKU. Column name varies per report — engine resolves the right one for each table.
  table_column_map:
  - {table: rpt_pnl_sku_economics, column: msku}
  - {table: rpt_restock_inventory, column: msku}
  - {table: rpt_br_detail_page_sales_traffic, column: sku}
  - {table: rpt_all_listings, column: seller_sku}
  - {table: rpt_sponsored_products_advertised_product, column: advertised_sku}
  data_type: string
  category: Product

One dim, N tables. The planner picks the right column per table.

Three resolution paths (in order)

1. Explicit YAML — table_column_map entry matches a queried table.
2. Enricher — dim is registered with an Enricher subclass that adds the column post-fetch (e.g. parent_asin looked up via new_asins).
3. Native column fallback — dim key is not in YAML at all, but a queried table has a column with that exact name. Engine accepts it implicitly. Example: dimensions=["seller_id"] works on every rpt_* table without a YAML entry.

If none match, the planner raises DimensionTableMismatchError with the queried table list.

Classification dimensions (derived from a formula)

- key: campaign_type
  description: Auto vs Manual based on targeting type.
  table_column_map:
  - {table: rpt_sponsored_products_targeting, column: targeting}
  classification_formula: "'Auto' if targeting in ('close-match','loose-match','substitutes','complements') else 'Manual'"

Aggregator evaluates the formula on the raw column post-fetch, then re-aggregates by the derived value.

Query flow

QueryRequest(
    metrics=["pnl_sales", "pnl_pre_ad_margin", "pnl_pre_ad_margin_pct"],
    dimensions=["sku"],
    filters={"child_asin": ["B0X...", "B0Y..."]},
    granularity="monthly",
    date_range_start="2026-04-01", date_range_end="2026-04-30",
    seller_id="AGBDRQ8IA12WY",
    marketplace="US",
)

The MCP wrapper validates seller authorization, parses the date range (date strings, never inferred), and hands off to QueryExecutor.execute(request). Result envelope:

{
  "results":    {"format": "csv", "schema": {...}, "data": "<csv>"},
  "summary":    {"format": "csv", "schema": {...}, "data": "<csv>"},
  "error_rows": {"format": "csv", "schema": {...}, "data": "<csv>"},
  "trace": {
    "tables_queried": "...",
    "sql_queries":      {csv-envelope},
    "rows_scanned":     {csv-envelope},
    "aggregation_rules": {csv-envelope},
    "formulas_applied":  {csv-envelope},
    "warnings":          "pipe-joined"
  }
}

summary is the always-on range rollup: one row per non-temporal dimension group — a single whole-query row when no dimensions, one row per group (e.g. per seller) otherwise — with SUM metrics summed and ratios recomputed from the summed components. Each summary row also carries its own missing_periods (the expected periods that group has no data for), so per-entity coverage never needs client-side reconstruction. There is no include_aggregate flag — the rollup is always returned. (The REST endpoint returns the same data as nested JSON: each row is {period, dimensions, metrics} and summary is a top-level array of {dimensions, metrics, missing_periods}.)

Conservation of mass

Every dimension grain should return the same total for additive metrics. Quick sanity check when adding metrics or enrichers:

dimensions=	rows	total pnl_sales
[]	1	12,809.59
[seller_id]	1	12,809.59
[parent_asin]	5	12,809.59
[child_asin]	9	12,809.59
[sku]	15	12,809.59

If a new metric breaks this, suspect the aggregator: the enricher is probably running post-collapse instead of pre-collapse, or a formula is summing a non-additive value. For a deeper walkthrough of how enrichers preserve this property — including a worked example through SKU/ASIN/seller grain — see Enrichers — How dimensionality is handled.

Adding a metric — checklist

1. Pick the right hierarchy level (L1 source-backed / L2 formula / L3 with ad spend).
2. Decide aggregation rule: raw SUM for L1 totals, Recalculate for L2/L3 ratios.
3. Mark required: true if downstream margins would silently break with null inputs.
4. Add to metrics.yaml; restart the MCP server (YAML loads once at import).
5. Smoke-test conservation across at least three grains.

Adding a dimension — checklist

1. Identify the column name on every relevant report table.
2. If the column name is identical across tables, consider relying on native-column fallback instead of YAML.
3. Otherwise add a table_column_map entry per table.
4. If the dim needs a lookup (e.g. parent ASIN from child), add an enricher and bind it with enricher: <name>.

Related

• Metrics Engine Enrichers — stage-typed framework for custom column computation
• PnL Metrics — PnL-specific source mapping, formulas, and required-data semantics
• Reporting Structure — upstream rpt_* table architecture