Every market, retailer and distributor submits sell-out differently: different files, product codes, calendars, units and levels of detail. Combining them into one spreadsheet produces totals that look clean but cannot be explained. A reliable dataset is the opposite: every row traces to a source, every correction keeps its history, and every gap is visible as a flag or an exception rather than a silently blank cell.
This guide defines the eight layers of that dataset: the source contract, the canonical model, mappings, calendars and measures, quality flags, versioning, exceptions and output layers.
The source contract
Start with a written contract for each source: which file or feed arrives, how often, in what format, at what level of detail, with which columns and units, and who owns it. The contract states the expected coverage, such as the stores or channels the file claims to represent. A source that delivers outside its contract is a data-quality event, not an unexpected surprise.
The contract makes the dataset possible to audit. When a total changes between cycles, the source contract identifies whether the change is a real movement, a new file version or a coverage shift.
The canonical model
Define one canonical structure that every source converts into at intake. A sell-out record should carry the source, the retailer or distributor, the product identifier, the location, the period, the units and value, and the metadata that makes it traceable. Every source lands in the same shape, so comparisons and aggregation run on one consistent model instead of reworked copies.
The canonical model is the contract between the sources and every report. When a new partner joins, the model does not change; the partner's file is mapped into it.
Canonical row template
The following illustrative row separates what arrived from the source from what the dataset standardized or decided. The fields are useful because they let a published number be traced back to a file, a mapping, a period rule and the controls that allowed it forward.
| Field | Illustrative value | Role in the dataset |
|---|---|---|
| Source system or file | Retailer ES-042 / sellout_2026-05_v3.csv | Source-preserving identity |
| Source file version | v3 | Source-preserving revision marker |
| Market | ES | Standardized reporting dimension |
| Distributor or retailer | Retailer ES-042 | Source-preserving party mapped to the reporting hierarchy |
| Product source code | 883221 | Source-preserving partner identifier |
| Canonical product ID | MX-2041 | Standardized product reference |
| Location or customer | Store ES-042 / customer CUST-042 | Source and standardized location relationship |
| Source period or date | Retail week W14 | Source-preserving reporting period |
| Canonical period | 2026-04-01 to 2026-04-07 | Standardized period from the controlled calendar bridge |
| Unit of measure | Each | Standardized measure after conversion rules |
| Quantity | 1,240 | Standardized numeric value with source quantity retained in lineage |
| Value and currency | €8,928 net / EUR | Measure and currency definition for reporting |
| Source lineage | File v3, rows 182–183 | Evidence for reconstruction |
| Coverage flag | Expected store set included | Whether the source scope is represented |
| Completeness flag | Period received; product coverage partial | Whether expected dimensions are present |
| Validation status | Passed structural and business checks | Gate before the row enters reporting |
| Reconciliation status | Compared at product-store-week | Whether the row has entered the relevant comparison |
| Exception status | Open: product coverage review | Visible unresolved decision |
| Record/version timestamp | 2026-06-03T09:15Z | When this version entered the controlled dataset |
Mappings
Map every source identifier to the shared references: product code to SKU and EAN or GTIN, store or outlet to a location master, and market to a geography. Keep the mappings as versioned tables with effective dates and owners, and make every unmapped record visible instead of dropping it. An unmapped product is not an absence of sales; it is an exception waiting for a decision.
The same discipline applies to store and market mapping. If two sources call the same store different things, the mapping table is the single place where that relationship is recorded.
Calendars and measures
State the reporting calendar and the measure definitions before aggregation. A retailer may report by retail week while a distributor reports by calendar month; the dataset needs a calendar bridge that assigns every source period to the shared reporting period without double counting. State the units each source reports in and convert them explicitly, including pack, case and each conversions.
Document which measure each column represents, such as sell-out units, sell-out value net of returns, or gross. Two sources calling a column value may mean different things; the measure rule decides the shared meaning and the conversion.
Quality flags
Carry a quality assessment on every record or block of records instead of only keeping rows that look correct. Flag coverage, such as which stores or channels a file represents; completeness, such as missing products or periods; and validity, such as implausible prices or duplicate keys. The flags travel with the data so a report can show totals and the caveats that qualify them.
A total without its flags is misleading. Reports can then choose to show the full total, the fully verified total, or both, and say which one they are using.
| Completeness dimension | Illustrative control record | What it tells the owner |
|---|---|---|
| Expected files | 5 expected / 5 received | Whether every source submission arrived |
| Expected stores or locations | 420 expected / 408 received | Whether the observed scope covers the intended network |
| Expected product coverage | 1,180 active products / 1,146 observed | Whether missing product rows may affect the total |
| Expected periods or dates | 4 retail weeks / 4 received | Whether the reporting window is complete |
| Expected volume or value coverage | Source contract baseline recorded; no universal threshold applied | Whether a valid source baseline exists for interpreting the total |
These fields are a control model, not universal acceptance thresholds. The source contract and reporting use decide whether a partial store set, product set or value baseline can be released, quarantined or shown with a caveat.
Versioning
Version every submission and keep every version. When a corrected file arrives, record the new version, what changed and why, and rerun only the affected periods. The dataset keeps the audit trail of what the source originally reported and what it was corrected to. Reports state which version produced their numbers.
Do not overwrite history. The difference between the original and the corrected version is often the most useful diagnostic the dataset can provide.
Late and corrected submissions
The right response depends on the source contract, materiality and whether the period has already been published. The following is a defensible control model, not a mandatory policy: retain the earlier version, make the decision visible and reopen sign-off when the published result is affected.
| Submission case | Accept and retain | Stages to rerun | Downstream output | Sign-off and evidence |
|---|---|---|---|---|
| On-time new file | Accept as the current source version | Validate, map and publish | Current period output | Receipt, version and validation result |
| Late file for an open period | Accept if the source contract permits it; retain receipt time | Validate and rerun affected period | Publish or hold according to completeness rule | Late reason, coverage impact and owner |
| Corrected file before publication | Supersede the earlier input but retain both versions | Rerun affected validation, mapping and outputs | Only corrected version is released | Correction reason and version comparison |
| Duplicate file | Quarantine unless it is explicitly a new version | Do not rerun downstream stages until classified | No duplicate contribution to totals | Duplicate key, file hash or source reference |
| Superseding file after publication | Retain the published version and the superseding version | Rerun affected stages and reconcile the delta | Versioned restatement or annotated current view | Restatement decision and approval record |
| Correction to a published period | Retain original and corrected records | Rerun affected period, downstream metrics and reconciliation where relevant | Restate only where policy and impact require it | Before/after totals, impact scope and reopened sign-off |
Exceptions
Send anything the dataset cannot classify into a visible exception queue: unmapped products, missing periods, implausible records, late files and coverage gaps. Each exception carries a category, an owner, a status and an aging rule. The queue makes the dataset honest, because every known gap is named and owned instead of absorbed into a blank cell.
Recurring exceptions become improvement work: a new mapping, a corrected source, a contract change or a new validation rule.
Output layers
Separate the dataset into layers so each consumer reads the right one. The raw layer keeps source files as received. The standard layer holds the canonical model with quality flags and mappings applied. The reconciled layer contains the version that has passed checks and cleared its exceptions. And the reporting layer presents the totals with their flags. Consumers who need audited figures use the reconciled layer; analysts who need to see raw detail use the standard layer.
Keeping the layers separate prevents reports from silently mixing sources, versions and levels of quality.
Practical takeaway
Build the sell-out dataset around traceability, not cleanliness. Enforce a source contract, land every source in one canonical model, map identifiers with versioned tables, define calendars and measures explicitly, carry quality flags, version corrections, run a visible exception queue and separate output layers. The dataset then gives your team one version of sell-out that can be defended, audited and improved.
Marksyte's data standardization service can design the intake and validation layer. Data mapping and integration support connects source identities and feeds, data reconciliation and controls tests the published comparisons, and managed data operations can run the versioned dataset cycle across retailers and distributors.
- Consolidate sell-out data from multiple distributors
- Compare retail sales across countries carefully
- FMCG data reconciliation operating model
- Manage monthly reconciliation exceptions
- Standardize retailer and distributor Excel files
- Validate retailer and distributor files before release
- See an illustrative FMCG data reconciliation case
- Explore managed data operations and analysis
Frequently asked questions
What makes a FMCG sell-out dataset reliable?
It has one canonical model, a documented source contract, explicit mappings, calendar and measure rules, quality flags, versioning for corrected files and an exception queue. It preserves source differences instead of hiding them.
How do you combine sell-out data from retailers and distributors?
Standardize every source into one canonical schema at intake, map products, stores and periods to shared references, apply calendar and measure rules, then keep source lineage and quality flags on every row.
How do you handle corrected sell-out files?
Version each submission, keep the earlier versions, rerun only the affected periods and flag which version produced each output. The dataset keeps a full audit trail instead of silently overwriting history.