Reconciling warehouse, industry, and API fitment counts

2024-06-01 · Ben Williamson

The ticket that started this

Support escalated a fitment-count mismatch on an automotive B2B catalog SKU. The warehouse PIM export showed 29 vehicles. The curated industry fitment file showed 13. The storefront REST response expanded to 54 when null submodels mapped to "all subs." Merchandising trusted the highest number. QA trusted the lowest. Nobody was wrong about their source. The sources disagreed.

Symptom pattern

The same SKU, three different counts, usually means:

  • Warehouse export includes year ranges the curated file trimmed.

  • Industry fitment data omits submodels the warehouse still carries.

  • API layer expands null submodel rows into every submodel for that year/make/model, inflating totals.

Magento indexes and garage filters amplify whatever upstream sends. Patching indexes first feels fast. It re-breaks on the next sync.

Root causes to check first

  1. Null submodel expansion. A null submodel mapped to every submodel inflates counts versus curated rows that list explicit subs only.

  2. Year/make drift. A year present in warehouse data but missing in the industry file (or vice versa).

  3. Configurable vs simple SKU. Fitment attached at the parent while the sellable child SKU differs, so exports keyed on different identifiers never align.

Reconciliation workflow

One SKU, three CSVs, a normalized diff, fix upstream:

  1. Pick one reporting SKU stakeholders agree on.

  2. Dump all three sources to CSV with normalized keys: Year, Make, Model, Submodel (blank submodel = explicit empty string, not null).

  3. Diff on those keys. Tag rows as warehouse-only, industry-only, or API-only.

  4. Fix upstream mapping rules. Patch Magento only after the source agreement holds.

In practice, null submodel expansion caused most of the inflation. Warehouse and industry disagreements on year ranges accounted for the rest. Configurable parent/child mismatches showed up on legacy catalog structures.

What to publish

Share methodology and normalization rules, not customer data. Keep SKUs, internal URLs, warehouse credentials, and hostnames out of public writeups. The reusable artifact is the diff key shape and the rule that Magento is downstream, not authoritative, for fitment truth.

Quick review before closing a mismatch

  • Are all three exports keyed on the same sellable SKU?

  • Is null submodel handled consistently in normalization?

  • Did we fix upstream before reindexing?

  • Does QA retest with the same SKU after the next scheduled sync?