How to consolidate sell-out data from multiple distributors

A region sells through five distributors and receives five different files. Columns change, product codes change, units change, currencies change. If you join them as they arrive, the total looks plausible and the detail is wrong. This guide builds a consolidation you can defend: inventory the sources, define a canonical schema, map the dimensions, apply calendar, currency and unit rules, and run a workflow that keeps exceptions visible.

You will see the fields a canonical schema needs, how product and customer mappings attach to it, the rules that make files comparable, and the controls that let you trust the resulting market total.

Source inventory: know every file before you build anything

Start with a written inventory of every distributor file that arrives: who sends it, how often, at what grain, in which units and currency, and which codes it uses. Without this list, the consolidation schema is guesswork and every exception later becomes a surprise.

Distributor	File	Grain	Units	Product key
MX-Norte	Sell-out monthly	Store	Units	Own code + EAN
MX-Centro	Sell-out monthly	Market	Cases	EAN-13
MX-Sur	Sell-out monthly	Store	Units	Own code

Record the grain precisely. A file at store level supports different questions than one at market level, and a consolidation that hides the difference will mislead later analysis.

The canonical schema: one structure for every source

Define one output structure that every distributor file is transformed into, and keep the source files untouched. The canonical schema is the contract between raw partner data and every downstream report, so it must be explicit and stable.

Canonical rowSell-out · Market MXPeriod · Market · Distributor · Product · Qty · Value

MX-NorteStore-level unitsOwn code + EAN

MX-CentroMarket-level casesEAN-13

MX-SurStore-level unitsOwn code

Minimal canonical fields: reporting period, market, distributor, product key, quantity in a standard unit, value in a standard currency, and a lineage reference back to the source file and row. Lineage is what makes a consolidated number explainable when someone challenges it.

Mapping dimensions: products, customers and periods

Every distributor identifies the same product differently. Build reference mappings once and let every file join through them, rather than repairing lookups in each file.

Keep each mapping versioned and effective-dated. When a distributor renames a code or a store closes, the mapping changes without breaking historical periods.

Calendar, currency and unit rules

Files become comparable only when the same conversion rules apply to all of them. Write the rules once, with a reference date, so the consolidation does not depend on whoever pressed recalculate.

1. Calendar. Convert each file's reporting weeks or cutoffs to the consolidated period with an explicit boundary rule.
2. Currency. Apply a fixed exchange rate with a stated reference date, and keep the original currency in the raw layer.
3. Units. Convert cases, trays and eaches to one standard unit using effective-dated conversion factors.
4. Coverage. Record how much of the expected market each file represents, so totals carry a coverage note.
5. Rounding. Fix where rounding happens, at source or at consolidation, and record it.

Document every rule with an owner and an effective date. When a distributor changes its template or a new market joins, the rule set is the first thing to review.

A consolidation workflow you can run every cycle

The consolidation should run as a repeatable sequence, not a series of manual steps. Each stage has a defined input, output and check so a failure is caught where it happens.

1. Intake. Register each file with its distributor, version and receipt date.
2. Validate. Check schema, required fields, duplicates and totals against the source file.
3. Map. Resolve product, customer and period keys through the reference mappings.
4. Convert. Apply calendar, currency and unit rules to build canonical rows.
5. Consolidate. Aggregate canonical rows into the market-level dataset.
6. Check. Compare consolidated totals against the sum of validated source totals.
7. Publish. Release the dataset with a version, lineage and coverage note.

Each stage writes a log entry. A year later you should be able to say which file version, which mapping version and which exchange rate produced any published number.

When a distributor corrects a published file

This illustrative scenario shows the control decision without prescribing one universal restatement policy. The question is whether the correction changes a published output and which downstream stages depend on it.

Worked example: consolidating three distributor files

Three distributors report the same sparkling water product in May 2026. Each file uses a different key, grain and unit; the canonical rows make them one number.

Consolidation dossierOwner · Regional commercial team

Canonical productMX-2041Sparkling water · 500 ml

MarketMXPeriod 2026-05

MX-Norte + MX-Centro + MX-Sur → MX-2041

MX-Norte1,200 cases → 86,400 unitsConverted

MX-Centro54,300 unitsMapped

MX-Sur39,600 unitsMapped

Conversion72 units per caseApplied

Coverage3 of 3 filesComplete

The number is usable because every component is traceable: MX-Norte's cases were converted with a recorded factor, MX-Centro and MX-Sur mapped through the product reference, and the total reconciles to the three validated source files.

Exceptions: keep them visible, never merge them silently

Not every row consolidates cleanly. Unmapped codes, missing periods and negative quantities are normal in real files. The discipline is to surface them as a controlled queue with an owner and a status, not to drop them into the total.

Each exception keeps its amount visible in the output as an explicit adjustment line. Excluded volume is a fact to explain, and the reconciliation only closes when the exception has an owner and an agreed resolution.

Controls before the total is used

The consolidated total should pass a fixed control set before it reaches commercial reporting. These checks are cheap and catch the differences that break a month-end number.

1. Totals. Consolidated volume equals the sum of validated source files after conversion.
2. Coverage. All expected files and periods are present, and gaps are flagged.
3. Mappings. No canonical row is built from an expired mapping version.
4. Rules. Currency and unit factors carry an effective date and a recorded source.
5. Exceptions. Open exceptions are listed with amount, owner and target resolution date.
6. Sign-off. A named owner accepts the dataset before release.

Run the same controls every cycle so a deviation stands out. A consolidation that cannot pass its own checks has not failed the process; it has correctly refused to publish.

Coverage is a visibility measure, not a universal pass threshold. The source contract and the reporting question decide whether a partial store or product set can be published, held or shown with a caveat.

Practical takeaway

Consolidating sell-out from multiple distributors works when the sources are inventoried, one canonical schema holds the output, dimensions map through controlled references and the same calendar, currency and unit rules apply to every file. Exceptions stay visible, and a fixed control set signs off the total.

Marksyte's data mapping and integration service can design the canonical schema and reference mappings, while managed data operations runs the monthly consolidation with lineage and exception control. Related work includes standardizing distributor templates and reconciling consolidated totals.

Frequently asked questions