Skip to main content

PnL Metrics

PnL is computed at totals grain (sums of sales, fees, COGS), with MB commission resolved per-ASIN and aggregated up. The model intentionally departs from the older per-unit attribute-based approach — actuals from Amazon's SKU Economics report are now the canonical source.

Source of truth​

MetricSourceType
pnl_salesrpt_pnl_sku_economics.salesL1, SUM, required
pnl_unit_pricerpt_pnl_sku_economics.average_sales_priceL1, SUM (per-row; see caveat)
pnl_landed_cogs_amtrpt_pnl_sku_economics.cost_of_goods_sold_per_unit * units_sold (SQL expr:)L1, SUM, required
pnl_referral_fee_amtrpt_pnl_sku_economics.referral_fee_totalL1, SUM
pnl_fba_feerpt_pnl_sku_economics.fba_fulfillment_fees_totalL1, SUM
pnl_sponsor_products_charge_totalrpt_pnl_sku_economics.sponsored_products_charge_totalL1, SUM
pnl_mb_commission_amtMbCommissionEnricher (see below)Computed, SUM, required
pnl_pre_ad_marginformulaL2, Recalculate
pnl_pre_ad_margin_pctformulaL2, Recalculate
pnl_contribution_marginformulaL3, Recalculate
pnl_contribution_margin_pctformulaL3, Recalculate
pnl_breakeven_tacosformula (= pnl_pre_ad_margin_pct)L3, Recalculate

rpt_pnl_sku_economics is the Amazon "Selling Economics and Fees" report — uploaded per (seller, marketplace, period), keyed by (seller_id, marketplace, period_start_date, period_end_date, period_granularity, msku, asin). Supported granularities: weekly, monthly, quarterly.

Formulas​

pnl_landed_cogs_amt   = SUM(cost_of_goods_sold_per_unit * units_sold)   ← SQL expr, per-row
pnl_mb_commission_amt = SUM( compute_mb_fee(config_per_asin, sales) )    ← enricher

pnl_pre_ad_margin     = pnl_sales
                      - pnl_landed_cogs_amt
                      - pnl_referral_fee_amt
                      - pnl_fba_fee
                      - pnl_mb_commission_amt

pnl_pre_ad_margin_pct = pnl_pre_ad_margin / pnl_sales * 100

pnl_contribution_margin     = pnl_pre_ad_margin - pnl_sponsor_products_charge_total
pnl_contribution_margin_pct = pnl_contribution_margin / pnl_sales * 100

pnl_breakeven_tacos   = pnl_pre_ad_margin_pct

All L2/L3 metrics use Recalculate: their source components are SUMmed first during aggregation, then the formula re-evaluates on the summed components. This is what makes ratios correct across grains (you can't average pre-ad-margin-percent across ASINs; you must sum margin and sales separately and divide at the requested grain).

COGS via SQL expr:​

pnl_landed_cogs_amt cannot be SUM(cogs_per_unit) * SUM(units_sold) — that double-counts. It must be SUM(cogs_per_unit * units_sold) row-by-row, then summed. We can't express this as a polars post-fetch formula because formulas evaluate after aggregation; we'd lose row-level pairing.

Solution: the expr: source field passes a raw SQL expression that the report-table strategy wraps in SUM(...) in the SELECT clause. See Seller Metrics Engine — expr.

sources:
- alias: pnl_landed_cogs_amt
  table: rpt_pnl_sku_economics
  expr: cost_of_goods_sold_per_unit * units_sold

Emitted SQL: SUM(cost_of_goods_sold_per_unit * units_sold) AS pnl_landed_cogs_amt.

MB commission​

Commission is a JSON config (StaticCommission | SalesRangeCommission) stored in dynamic_attributes_values.value_json at two keys:

  • asin_mb_commission_config (entity_type='asin') — per-ASIN override
  • brand_mb_commission_config (entity_type='brand') — per-brand default

Resolution chain: ASIN override → brand default → None.

Static config​

{"type": "static", "rate": 10}

Fee = sales * rate / 100, applied to every row.

Sales-range config​

{
  "type": "sales-range",
  "tiers": [
    {"min_sales": 0,    "rate": 5},
    {"min_sales": 1000, "rate": 12}
  ]
}

Tier-pick depends on query granularity:

  • Monthly / quarterly → the enricher computes the brand-level period sales total (SUM of sales across all ASINs of that brand for the period) and picks the tier from that total. Mirrors how Amazon bills the brand — every ASIN under the same (brand, period) gets the same effective rate.
  • Weekly → the enricher applies the highest tier's rate to every row regardless of sales. Weekly brand totals rarely cross higher thresholds even though monthly billing would; using the top tier is the conservative-margin proxy. See the sales-range caveat below.

In both cases the per-row commission is row_sales × rate / 100. The rate is the only thing that varies with granularity.

The compute_mb_fee helper itself implements full bracket-pick on whatever total you pass — used by the brands API for projections. The enricher only swaps in granularity-aware totals.

Math helper​

compute_mb_fee(config, total_sales) in app/schemas/mb_commission.py is the single source of truth. The enricher reuses it; the brands API reuses it. Don't reimplement.

Config-resolution semantics (V1: latest wins)​

The metrics engine ignores effective_from / effective_to and uses the most-recently-set config for each ASIN (override) or brand (default), regardless of when the query period falls. Helper: get_latest_mb_commission_batch in app/services/brands/service.py.

  • Setting a 10% config today, then querying April PnL, applies 10% to April.
  • If you later set a 12% config (newer effective_from), every PnL query — historical and current — switches to 12%.

This trades historical accuracy for not requiring explicit backdating when a brand's first config is created. Acceptable approximation for analytical PnL; if you need true historical rates (e.g. for invoice reconciliation), use get_effective_mb_commission_batch(as_of=…) directly — it still does strict temporal matching and is what the brands API calls for projections.

Known trade-off: changing the rate mid-period (or after-the-fact) silently re-prices historical PnL. We accept this until it bites; flag it as a follow-up if it does.

Required metrics + null cascade prevention​

pnl_sales, pnl_landed_cogs_amt, and pnl_mb_commission_amt are marked required: true. If any of them returns null for any row, the engine's required-metric validation step (run post-join, pre-collapse) raises RequiredMetricMissingError naming the affected ASINs:

RequiredMetricMissingError:
  Required metric 'pnl_landed_cogs_amt' is missing source data for:
  B0D8C2KQG3, B0DPJBHB23, B0D5YWMW61, B0D8BTQLJ4, B0DDV8XD3V (+43 more).
  Cannot compute downstream margins.

MCP layer surfaces this as a structured required_metric_missing error with metric + missing_entities fields. The LLM can then tell the user precisely what needs to be configured (COGS uploaded for those ASINs; MB commission set on the brand or per-ASIN; etc.) rather than returning a result with silent nulls in every margin field.

See Seller Metrics Engine — required-metric validation.

Conservation of mass​

PnL should hold the same totals across any dimension grain. With a Beauticone test seed (static 10% MB commission, monthly Apr 2026):

dimensions=rowspnl_salespnl_mb_commission_amtpnl_pre_ad_margin
[]112,809.591,280.964,327.65
[seller_id]112,809.591,280.964,327.65
[parent_asin]512,809.591,280.964,327.65
[child_asin]912,809.591,280.964,327.65
[sku]1512,809.591,280.964,327.65

Sales-range tiers ([{0,5%},{1000,12%}]): per-ASIN math holds (each ASIN's fee = its sales × its tier rate), and totals sum cleanly.

Querying​

# Seller-level monthly PnL
QueryRequest(
    metrics=[
        "pnl_sales", "pnl_landed_cogs_amt", "pnl_referral_fee_amt",
        "pnl_fba_fee", "pnl_mb_commission_amt", "pnl_pre_ad_margin",
        "pnl_pre_ad_margin_pct", "pnl_contribution_margin"
    ],
    dimensions=["seller_id"],
    granularity="monthly",
    date_range_start="2026-04-01", date_range_end="2026-04-30",
    seller_id="AGBDRQ8IA12WY", marketplace="US",
)

For SKU/ASIN slicing add dimensions=["sku"] or dimensions=["child_asin"]. For named-ASIN filtering, call metrics_list_seller_asins first to resolve normalized names → ASINs, then pass filters={"child_asin": [...]}.

Caveats​

MB commission sales-range: weekly uses highest tier​

Bracket-based sales-range configs are problematic at sub-monthly granularity. Example: a brand with tiers <10k = 3%, >=10k = 6%.

  • A weekly PnL query rarely sees any single (brand, week) cross $10k — even though monthly billing crosses the threshold and bills at 6%. Picking the tier from weekly totals would always apply 3%, under-counting the commission the seller actually pays.
  • The "right" model is to know the seller's projected monthly total and apply the corresponding tier rate proportionally across weeks. We don't have projection signals.

Current behavior:

  • Monthly / quarterly → tier picked from the brand-level period sales total (matches billing semantics). This is the accurate case.
  • Weekly → fall back to the highest tier's rate applied to every row. Conservative-margin proxy; over-counts when the seller is genuinely below the top tier monthly.

Static configs are unaffected by granularity in either case.

Halo effect on SKU-level ad cost​

Amazon's ad reports attribute ad spend to the advertised SKU, but a customer who clicks an ad for SKU xyz may end up buying a related SKU abc (different variant, different size). The SKU Economics report shows xyz's ad cost as $X and abc's ad cost as $0, even though abc's sale was driven by xyz's ad.

At SKU grain this distorts PnL: abc looks artificially profitable (real sales, zero ad attribution) while xyz looks artificially unprofitable (ad cost but no purchase). pnl_contribution_margin at SKU level is not reliable until halo attribution is modeled (cross-SKU ad-spend redistribution by family/parent ASIN).

Workaround until then: aggregate to parent_asin or child_asin grain when looking at contribution margin — the halo attribution issue cancels out within a family.

Other caveats​

  • pnl_unit_price rolls up as SUM-of-AVG when crossing periods/SKUs. Acceptable for V1; weighted-avg is the correct long-term fix.
  • pnl_contribution_margin sign: the registry literal pre_ad_margin + sp_charge was identified as a typo (SP charge is a cost). Implementation uses subtraction. Re-verify if registry changes.
  • MB commission effective_to semantics: currently picks latest config as of period_end_date. Intra-period config changes aren't prorated — querying at weekly grain tightens precision.