Most reconciliation projects start with an unexplained variance: retailer sell-out is lower than ERP shipments, and the gap is bigger for some products than others. Teams respond by checking the obvious candidates, and at some point someone says "we also dropped rows whose SKU we do not recognize." That sentence is where the real problem lives, and it usually ends there, because an inner join has already made the dropped rows invisible.
This guide separates the two things that get called "missing SKU," shows why an inner join silently hides valid sales, and sets up the three pieces of a working fix: a detection control that runs before the join, a mapping waterfall that resolves identifiers in order of reliability, and a queue that gives every unresolved product an owner and an age.
Missing and unmapped are two problems
A retailer sell-out file carries a product identifier on every row. When you reconcile that file against your ERP or your sell-out dataset, the join runs on that identifier, through a mapping table that connects retailer codes to internal SKUs. Rows that do not match are "missing." The word hides the distinction that decides the fix.
A missing SKU never appears in the file at all. A store did not report, a category is absent, or a product line is missing from a period. That is a coverage problem: the expected population is incomplete, and no join logic will ever recover it, because the rows were never delivered. The fix is on the retailer side or in the file intake, and it is measured against an expected population, not against your mapping.
An unmapped SKU appears in the file with an identifier your master cannot interpret. The row is physically present; the product was sold; you just cannot say which product it is yet. That is an identity problem, and it is fixable with the mapping table. The two get collapsed into one count because both show up in the same place — the join output — but they fail at different stages. Coverage fails at intake. Identity fails at the mapping.
This is not a terminology exercise. The two problems have different evidence, different owners and different timelines. Coverage is fixed with the retailer or the data provider and may take weeks. Identity is fixed in your own mapping and can take minutes. If the report says "we lost 4% of rows to missing SKUs," nobody knows which kind, so nobody knows who should act, and the number recurs every month with the same ambiguity.
Detection controls before the join
The reason unmapped SKUs survive for months is that the join erases them. An inner join returns only the rows that match, and the output looks clean: every remaining row has a SKU, a value and a name. Nobody can tell from the output that 3% of the retailer's value vanished at the join, and the reconciliation "balances" against a total that was never the real total.
The detection control is a row-and-value count that runs before the join and writes its result to the run log. Split the delivered file into three states: records with no product identifier at all, records whose identifier is not in the mapping, and records that map cleanly. Count rows and value in each state, by retailer and by period. Report matched value as a share of delivered value, not as a share of expected value, so the coverage loss and the identity loss stay separate.
| State | What it means | Where it fails | Owner |
|---|---|---|---|
| No identifier | File rows missing the product column or the field is blank | Intake and validation | Data operations |
| Unmapped | Identifier present but not in the mapping table | Product mapping | Master data |
| Mapped | Identifier resolves to an internal SKU | None | None |
The output of the control is a stable number that moves. When matched value as a share of delivered value rises from month to month, the backlog is shrinking. When it stalls, the queue is not being cleared. Without this number, "we have some missing SKUs" is a mood; with it, the reconciliation has a control that any of the rules in the operating model can own. The control belongs in the same run as the rest of the reconciliation, not in a separate investigation that happens only when someone complains.
The mapping waterfall
An unmapped identifier is resolved through a waterfall, in order of reliability, and each step is recorded so the resolution can be audited and reused. The first attempt is the retailer item code against the mapping table. Most rows resolve here, because the mapping already exists. The second is the retailer product name, brand and pack description against the product master, producing a candidate. The third is the EAN or GTIN printed on the line or resolved through the retailer's item catalogue. The last is human review, which either creates a new mapping entry or classifies the identifier as genuinely unknown.
Every result gets a status, and the status decides whether the row can be used. Matched means the identifier resolves to exactly one internal SKU. Candidate means a probable match that needs a human to confirm before it is treated as sales. Unresolved means nothing plausible was found and the row is routed to the queue. The distinction matters because a candidate promoted to matched without review is how a wrong product attribution becomes permanent — the sales land on the wrong SKU and every trend downstream is quietly wrong.
The waterfall is the same matching discipline the rest of the reconciliation uses, applied one level down. It also feeds the mapping itself: a code that resolves to the same internal SKU three months running should be promoted to a permanent mapping entry with an effective date, so next month it is matched in step one instead of step three.
New listings arrive before your master
The most common source of unmapped SKUs is not an error. It is a new listing. A new product, a retailer-exclusive pack or a seasonal variant enters the retailer's assortment and starts selling, and its rows appear in the sell-out file in the same period the product launches. Your product master was created around your own catalogue, and the mapping between a new retailer code and a new internal SKU has to be created by someone. Until it is, every row for that product is unmapped.
The pattern is predictable enough to schedule. New listings have a known window: the retailer's listing date, the first expected sell-out file, and the day the master or mapping entry is created. The gap between the first and the last of those dates is where valid sales disappear. The fix is not to chase each new code after the fact but to treat a new listing as an event with a deadline: the mapping entry is created before the first file that will contain it, even if the status is "pending master" until the product data is confirmed.
This is also where the GS1 identification rules become relevant. A new product gets a new GTIN, and a change to net content, pack configuration or primary brand requires a new GTIN as well. Those are the product changes that produce new identifiers in retailer files. They are normal commercial events, not data anomalies, and the reconciliation pipeline has to absorb them on a schedule rather than rediscover them each month.
Discontinued items keep selling through
The other recurring source is the opposite of a listing. A SKU is discontinued, retired from your master or filtered out of reporting, but the retailer keeps selling through existing stock, and the file keeps carrying the old code. The rows are valid sales that will end up in the unmapped bucket if the mapping entry was deleted when the product was retired.
Discontinued products should be retired, not deleted. The mapping entry keeps its identifier, the internal SKU, and an end-of-life status, and it stays valid for as long as the retailer continues to report it. The reporting layer decides whether a retired SKU's sales appear in current performance or are flagged as sell-through; the reconciliation layer just needs the mapping to keep working so the rows are not lost. The consequence of deleting the entry is that residual sales of a discontinued product become unmapped, and the residual value of a discontinued line is exactly the value nobody is watching.
The same rule applies to a pack change that reuses a familiar name. When the retailer's code changes because the pack size changed, the old and new codes are two identifiers for adjacent products, not one product with a typo. Pack-level mapping keeps the two distinct so sales history is not mixed across a change that has a real date.
The queue is the process
An unmapped identifier is a queue item, not a meeting topic. The queue holds one row per unresolved identifier, with the source retailer, the first and last period seen, the resolution status, the owner, and the age in periods. Each item is resolved, matched to a SKU and a mapping entry, or explicitly classified as unrecoverable with a documented reason. Items older than a defined threshold are escalated, and the queue is reviewed at the same cadence as the reconciliation run.
The queue changes how the problem is discussed. Instead of "we have some unknown codes again," the run produces: ten new identifiers this month, three resolved by the waterfall, six are new listings awaiting master confirmation, one is genuinely unknown and being escalated to the retailer. Each of those sentences describes a different action and a different owner. That is the difference between an exception process and an inbox.
The queue also catches regression. If an identifier was resolved and mapped three months ago and reappears as unmapped this month, that is not a new problem; it is a mapping that was overwritten, a mapping table that was rebuilt, or a file that switched identifier fields. The exception lifecycle that handles reconciliation differences applies here too, with the same evidence requirements and the same rule: a resolved item that comes back is treated as a defect in the process, not as bad luck.
Why the same codes come back
The product side of retail reconciliation fails for structural reasons, and none of them is exotic. Suppliers and retailers maintain product data separately, and the retailer's item record changes when its own catalogue changes. Product data quality problems in FMCG are well documented on both sides of the relationship: a study of two FMCG distribution and retail companies found product master data quality had a significant effect on logistics process performance, and survey work on the same topic found companies were aware of the importance of product data quality yet rarely used the standardized procedures that exist to maintain it.
The implication is direct. Unmapped SKUs will not stop appearing, because the commercial world keeps producing new identifiers, new listings, relaunches and pack changes. What can be fixed is the response: a detection control that counts them, a waterfall that resolves them, a queue that ages them, and a mapping that treats a retailer code as a durable record rather than a throwaway. When those four exist, the reconciliation number includes everything the retailer reported, and the "missing SKU" discussion stops being about why the file is incomplete and starts being about which product change needs a mapping entry this week.
One limit applies. A well-run mapping cannot recover a file that was never delivered, and it cannot invent products that never shipped. The identity fix protects the rows that arrived; the coverage fix protects the rows that should have arrived. Running one without the other leaves the same hole, just on a different side of the join.
Where unmapped SKUs usually disappearThe rows are dropped by an inner join before anyone counts them, so the variance is attributed to "unknown products" and never decomposed. The first step is a run log that counts matched value against delivered value before the join runs, splitting coverage loss from identity loss.
- SKU-to-EAN and GTIN mapping for FMCG teams
- How to reconcile retailer sell-out data with ERP data
- How to create a reliable FMCG sell-out dataset
- How to design reusable data reconciliation rules
- See an illustrative FMCG data reconciliation case
- Explore data mapping and integration
- Explore data audit and standardization
Frequently asked questions
What is the difference between a missing SKU and an unmapped SKU?
A missing SKU never appears in the retailer file, so the issue is coverage: a store, period or product line is absent. An unmapped SKU appears in the file but its identifier cannot be linked to your product master, so the issue is identity. The two have different causes, different fixes and different owners, and they should never be reported as one number.
How do I detect unmapped SKUs before they are dropped by a join?
Count rows and value in three states before the join: records with no product identifier, records whose identifier is not in the mapping, and records that map cleanly. Report matched value as a share of delivered value by retailer and period. An inner join that drops unmatched rows makes the problem invisible; the detection control has to run before the join.
Why do unmapped SKUs keep coming back every month?
Because the causes are events, not errors: new listings reach the retailer file before your master and mapping update, pack and net-content changes create new identifiers, and discontinued items keep selling through old stock. Each is a scheduled event with a known window. The queue should expect them and age them, instead of treating every reappearance as a new problem.
Sources
- GS1, Global Trade Item Number (GTIN).
- SPS Commerce, Why Supplier Item Data Failures Cascade and What They Cost Retailers.
- MDPI, Impact of the Product Master Data Quality on the Logistics Process Performance.
The GS1 identification rules are cited for when new or changed products receive new identifiers. The SPS Commerce and MDPI material is cited for the documented effects of incomplete product and item data in retail and FMCG supply chains. The guide does not claim a specific share of retailer files affected by unmapped SKUs, because the sources do not provide a verifiable basis for such a figure.