How to standardize retailer and distributor Excel files

An analyst spends two days every month renaming columns, fixing dates and copying tabs from five retailer and distributor files. The numbers land in the same workbook, but nobody can explain how one cell travelled there. Standardization is the difference between that and a single canonical structure every partner file is converted into at intake, with the original kept for lineage.

This guide shows how to build that structure: profile the files, define one canonical schema, map every field and unit with an explicit rule, validate before data lands, and keep the raw files so any number can be traced back to its source.

Profile the files before designing anything

Start with the files you actually receive, not the ones you wish you received. For each partner file record the template, the column names and their meaning, the product and location identifiers, the units, the calendar and the volume of rows. Do this across several periods because templates change: a column that appears in March and disappears in May is a design constraint, not an anomaly.

Keep the profile as a written inventory, one row per source file per period. It becomes the input to the schema design and the checklist for intake later. A source that cannot be described on a single line is not ready to be standardized.

Define one canonical schema

The canonical schema is the single structure every partner file is converted into. It fixes the field names, the data types, the grain and the units so downstream joins and reports never have to interpret a column again. A practical sell-out schema holds the partner or banner, store, product identifier, period, units and value, plus the source file and row reference for lineage.

Decide the grain explicitly: one row per product, store and period. If a retailer sends subtotals, decide whether they map to the grain or stay in an exceptions view. The schema should be stable and versioned; changing it is a deliberate act with a migration, not a habit.

Map every field from source to canonical

For each source column, write the mapping to the canonical field: the source name, the target field, the transformation and the confidence. Store the mapping as a table, not in analyst memory, because it is what makes the process repeatable by anyone. Distinguish fields that map directly, fields that need a lookup or conversion, and fields that are informational and carried along without a target.

Product and location identifiers deserve their own mapping path. A retailer product code, an EAN and an internal SKU usually need a master-data lookup, as covered in SKU-to-EAN and GTIN mapping for FMCG teams. The mapping layer keeps those references versioned so a changed code does not silently rejoin differently.

Fix data types and units before they become your problem

Dates are the most common source of silent variance. One partner writes DD/MM/YYYY, another MM/DD/YYYY, a third sends month-end serial numbers, and the ERP exports ISO dates. The canonical schema states one date format and an explicit calendar, and the intake converts everything to it. The same applies to numbers: thousands and decimal separators, currency and whether value includes tax.

Units need the same discipline. A file in cases, another in eaches and a third in packs means the comparison is wrong before any join. Record the conversion factor and its effective date in the mapping, because pack sizes change. Convert at the edge, once, rather than carrying raw units into every report.

Validate at intake, not after the joins

Validation is part of standardization. Check required fields, unique keys, reference validity against masters, dates in range, units present and totals that reconcile to the file's own subtotals. A file that fails structural checks should be rejected with a clear reason, not partially loaded and fixed later. Quarantine it and notify the partner so the same defect does not recur.

Intake validation turns the process from "we discovered it in the report" to "we prevented it at the door". The checks are cheap compared with a wrong market total that reaches management.

Keep source lineage and corrections

Every converted row should be able to answer the question: which source file, which row, which mapping version, loaded when? Store the raw file, the load identifier and the mapping version alongside the canonical output. Standardization never means discarding the evidence; it means making the evidence structured.

Corrections need the same discipline. A retailer resends a corrected file. Decide whether it replaces the original, appends an adjustment or creates a new version, and record which one was used. Without lineage, a corrected file becomes an unexplained overwrite that later reconciliations cannot audit.

Treat template changes as events

A partner changes a column name, adds a field or switches units. The instinct is to fix it in place; the standard approach is to treat it as a controlled event. Compare the new template against the profiled one, identify what changed, update the mapping with the new effective version, validate against the previous periods and record the change in the lineage.

This is what keeps standardization alive. Templates will keep changing; the mapping table and the version trail are what absorb those changes without breaking history or the next cycle.

A worked standardization example

Suppose a brand receives sell-out from one retailer and one distributor plus its own ERP extract. The three files describe the same product in different ways.

Each source reaches the same canonical row through a documented mapping: the retailer code joins through the product cross-reference, the case count converts with the pack factor, and the calendar maps retail weeks to the month. The analysis reads one structure, and each row carries the source file and row reference behind it.

Frequently asked questions