Retailer sell-out data mapping: align SKU, store and period data

A retailer reports 1,240 units for a product. The ERP shows 1,198. The first reaction is to search for a commercial explanation: stock-outs, returns, late invoices or a pricing issue. Those may matter, but the difference may have been created earlier, when the two files were joined on incompatible product codes, locations or reporting periods.

This guide owns the pre-join workflow: align product, location or store, reporting period and grain before comparing sell-out with ERP, distributor or POS data. It is not a general product-identity guide; for the underlying SKU, EAN and GTIN model, use the SKU-to-EAN and GTIN mapping guide.

Sell-out mapping is the work of making those dimensions comparable without erasing what each source originally said. The aim is not to force every row into a match. It is to make matched rows trustworthy and unmatched rows visible.

What does retailer sell-out data mapping mean?

Retailer sell-out data mapping connects the codes and definitions in a retailer or distributor file to the product, customer and calendar references used by the brand or ERP. It is also called SKU mapping, outlet mapping, secondary-sales mapping or data harmonization, depending on the team and market.

The mapping is not a dashboard and it is not a simple column rename. It is the documented relationship that lets a POS or EPOS record, distributor submission and ERP record refer to the same product, commercial location and period. If those relationships are not explicit, a later reconciliation can produce a precise-looking answer built on the wrong rows.

Start with the grain, not the join

Before mapping anything, define what one row represents. A retailer file may contain one row per product, store and retail week. An ERP extract may contain one row per product, customer and invoice date. These are not interchangeable grains, even when both contain a units column.

Write the grain as a sentence: “one row equals one product, one location and one reporting period.” Then identify which source fields support each part. If a file does not contain the necessary detail, mark that as a limitation instead of inventing precision during the join.

Align product codes before the join

Product mapping connects the identifiers used by each source to one canonical product record. The retailer may use an item code, the distributor an article number and the ERP an internal SKU. A barcode such as a GTIN can provide another reference, but it does not remove the need to document pack level, market and effective dates. For the full identifier model and governance rules, see the SKU-to-EAN and GTIN mapping guide. Here the focus is narrower: make sure the product key is ready to be compared alongside location, period and grain.

Keep the mapping in a versioned table with the source system, source code, canonical product, pack description, conversion factor, valid-from date, valid-to date, status and owner. A retired code must remain available for historical rows. Replacing it with the current code can make an old period appear to have changed.

Map retailer, outlet and store codes

“Customer” and “store” are often different dimensions. One ERP customer may own several outlets; a retailer file may report each outlet separately; a distributor may report a depot rather than the store where the product was sold. Decide whether the comparison is at account, outlet, warehouse or market level.

Use a location master that records the source identifier, canonical location, parent account, country or market, location type and active dates. Do not aggregate two stores merely because their names look similar. A location change, closure or account transfer can otherwise look like a sales movement.

Bridge the reporting period

A retail week, an accounting month and an invoice date describe time differently. The period map should associate source dates or periods with a canonical calendar and record the rule used. For example, a retail week that crosses two financial months needs an explicit allocation rule, not a silent choice based on the file name.

Keep both the source period and the canonical period. The source period preserves what the partner submitted; the canonical period makes comparison possible. If a source is late or corrected, store the submission version and received date as separate metadata rather than changing the sale date.

Keep exceptions visible

An unmapped product is not zero sales. A missing store relationship is not a valid total-level match. A period outside the calendar is not an ordinary variance. Each should enter an exception queue with the source, key, period, affected measure, reason, owner, status and next action.

This distinction matters because a join that drops unmatched rows can make the final comparison look cleaner while reducing coverage. Report coverage alongside the totals: mapped rows, unmapped rows, duplicate keys, rejected records and the value or units affected.

Run controls before comparing totals

1. Profile the source. Check columns, data types, duplicate keys, blank identifiers, date range and units before transformation.
2. Test mapping uniqueness. An active source product should resolve to one canonical product at the stated grain. If not, stop and resolve the ambiguity.
3. Check coverage. Measure the share of rows and units that resolve for product, location and period independently.
4. Reconcile at the same grain. Aggregate only after both sources use the same product, location, period and measure definitions.
5. Preserve lineage. Keep source file, submission version, source row or record key and mapping version on the output.

These controls do not explain every difference. They make the remaining differences interpretable. Once identity and time are aligned, the investigation can focus on real causes such as returns, stock timing, cut-off, unit conversion or missing coverage.

Practical takeaway

Map product, location and period as separate, owned dimensions. Keep source values beside canonical values, version every change and send unresolved relationships to an exception queue. The result is not a cleaner-looking file; it is a comparison that can be reproduced and defended.

Marksyte’s data mapping and integration service helps align source-to-target structures, transformations and refresh workflows across retailer, distributor and internal data. Reconciliation and controls can then be applied to records that actually describe the same thing.

Frequently asked questions

Sources and methodology

1. GS1, GTIN Management Standard: Introduction
2. GS1, Global Location Number

The mapping model and operating guidance in this article are Marksyte’s practical interpretation for multi-source sell-out reporting. GS1 sources support the definitions of product and location identifiers; they do not prescribe the specific reconciliation workflow described here.