A SKU rationalization effort rises or falls on the quality of its fact base. Most programs fail not because leaders oppose simplification, but because the underlying data is fragmented, inconsistent, or too weak to support confident choices. Sales has one view of volume, finance another of margin, supply chain a third of inventory, and no one can connect them cleanly at the SKU level. When that happens, the team debates the numbers instead of deciding what to do.
Decision-grade fact base: data that is not perfect, but is reliable enough to show demand, economics, operational burden, and customer relevance. This chapter explains how to build that foundation.
4.1 Defining the Data Required
The first step is to define the data from the decisions backward. Many teams begin by asking every function for every field they can export. That creates volume, not insight. A better approach starts with the questions the business must answer. Which SKUs create meaningful demand? Which ones generate acceptable profit? Which ones create disproportionate complexity? Which ones matter to customers, channels, or strategy? If the fact base cannot answer those questions, it is not fit for purpose.
Most programs need four data lenses. The first is demand. At minimum, that means sales volume and revenue by SKU over time, ideally with customer, channel, region, and month included. This makes it possible to see velocity, concentration, seasonality, and account dependence. The second lens is profitability. That typically includes standard cost, gross margin, and, where possible, contribution margin or cost-to-serve elements such as freight, rebates, or promotional deductions. Revenue alone often protects SKUs that look attractive on paper but are weak economically.
The third lens is operational complexity. This usually includes inventory levels, order frequency, lead times, minimum order quantities, run sizes, changeovers, supplier counts, component complexity, and warehouse handling requirements. A low-volume SKU is not automatically a bad SKU. Some are easy to make and easy to hold. Others create a burden at every step.
The fourth lens is commercial and strategic context. Rationalization decisions go wrong when SKUs are treated as isolated data points. The fact base should therefore capture major-account dependence, channel-specific listings, exclusives, contract flags, regulatory restrictions, lifecycle stage, and known substitutes where they exist. A small item may still matter if it defends a channel, completes a bundle, or supports a required offer architecture.
A practical structure is to organize the data into five blocks:
- SKU master data: codes, descriptions, hierarchy, attributes, pack sizes, units of measure, and status.
- Commercial data: volume, revenue, price realization, customers, channels, geographies, and promotions.
- Financial data: cost, gross margin, contribution margin, rebates, allowances, and other serving costs where available.
- Operational data: inventory, service levels, lead times, run lengths, changeovers, sourcing inputs, and planning parameters.
- Strategic context: lifecycle stage, substitution relationships, customer commitments, and constraint flags.
The right level of detail is important. Data that is too aggregated hides variation. Data that is too detailed slows the work and creates noise. In most cases, twelve to twenty-four months of history at SKU-by-month level is enough, with customer or channel detail added only where it materially affects decisions.
4.2 Identifying Data Sources Across Functions
Once the required fields are defined, the next problem is locating them across the business. No single function owns the full picture. ERP may hold item masters, standard costs, and inventory. Demand planning may hold forecast error and service metrics. Manufacturing systems may hold setup times and bill-of-material details. CRM may hold account-specific information. Finance may maintain rebates or adjusted margin views outside the core systems. A credible fact base depends on pulling these pieces together without losing meaning.
This is why source mapping matters. For every field, the team should identify the system of record, the business owner, the technical owner, the extraction format, the time range, and the identifier that links it to other data. These details sound administrative, but they are where many projects go off track. If two functions send “net sales” with different definitions, or if inventory is pulled as of a different date than the sales period being analyzed, the result will look precise but be misleading.
Item-level data is often especially fragmented. One system may define the commercial SKU, another the production material, another the shipper or case code, and another the customer-specific listing. Acquisitions and system migrations make this worse by leaving historical identifiers in place. Unless the team understands these relationships early, the portfolio may appear larger or smaller than it really is, and analysis may double-count or miss items.
The best way to avoid this is to pair data collection with short interviews of the functional owners. Those discussions often reveal what the system fields do not. Supply chain may explain that one lead-time field is rarely updated. Finance may warn that a margin file excludes rebates for certain channels. Sales may point out retailer-specific variants that look generic in the master data. These nuances determine whether a field can be used directly, adjusted, or treated with caution.
Data request pack: a document that defines each required field, its business meaning, the date range, the preferred format, and the key identifiers that must be included. It should also specify units of measure. Many reconciliation problems come not from missing data, but from mixing eaches, cases, pounds, liters, and pallets without a clear conversion rule.
It also helps to assign one integration owner. Someone must control versioning, track which extracts are current, and confirm that files can be joined consistently.
4.3 Cleaning, Reconciling, and Standardizing SKU Data
Raw extracts are almost never analysis-ready. Systems use different names for the same SKU, different customer codes for the same account, different calendars, different units, and different definitions of active status. Cleaning and standardization are therefore not tasks at the edge of the work. They are the work that turns disconnected files into a usable fact base.
The first priority is key alignment. Every file that will be joined must connect through a consistent identifier structure. Sometimes a single SKU code is enough. Often it is not. The team may need a crosswalk that links commercial item number, material number, case code, legacy code, and customer item number into one common key. The same issue often applies to customers, channels, and plants. Without these bridges, even correct data cannot be combined reliably.
The next step is definition reconciliation. Similar labels can hide different meanings. One sales file may show gross sales before deductions, while another shows net invoice value. One inventory extract may include blocked stock, another may not. One cost file may reflect current standards, another last quarter’s standards. Before the data is merged, these definitions must be made explicit. If the team chooses one view over another, that choice should be documented so the analysis is auditable and repeatable.
Standardization comes next. Dates should follow one calendar. Units should be converted into a primary reporting basis. Currency should be consistent. Status codes such as active, obsolete, seasonal, or discontinued should follow one rule set. Product hierarchies should be normalized so that attributes are treated consistently across categories. These decisions reduce false differences and make SKU comparisons more meaningful.
This is also the point to remove obvious noise. Duplicate records, blank item codes, internal-use materials, temporary test items, and distorted transactions caused by returns handling should not flow unexamined into the analysis. Not every anomaly should be removed, but every anomaly should be understood. Cleaning does not mean forcing the data to look tidy. It means making sure odd patterns are visible and explainable.
Data dictionary: a reference file that lists every field, its source, business meaning, unit, and any transformation applied.
4.4 Handling Missing, Inconsistent, and Low-Quality Data
No company has perfect SKU data. Waiting for perfect data is often a disguised form of delay. The right response is disciplined pragmatism: improve what matters, document what is weak, and judge whether the remaining uncertainty is acceptable for the decision at hand.
The first step is to classify the gaps. Some missing fields are inconvenient but noncritical. Others undermine the core logic of the program. If a minor product attribute is absent for a small number of SKUs, the analysis can often proceed. If customer-level sales are missing for a major channel, or if the cost structure is unreliable for a meaningful share of items, the team has a material problem. Not all data gaps deserve the same effort or concern.
Where possible, important missing fields should be recovered from the source owner. When that is not possible, proxy logic may be used, but only transparently. Missing costs may sometimes be estimated from similar items within the same family. Missing lifecycle flags may be filled through category-manager review. Missing substitution mappings may be built through structured commercial input. These are acceptable methods when they are documented and when the affected SKUs are visible to reviewers.
Inconsistent data should also be handled openly. If one system says a SKU is active and another says it is obsolete, the answer may depend on the business question. A commercially inactive item that still has stock on hand remains relevant to rationalization because it consumes working capital and space. In these cases, it is often better to preserve multiple signals and apply a business rule than to force a false single truth.
The same principle applies to low-quality fields. If an input is too unreliable for direct use, the team should make an explicit choice: repair it, replace it with a proxy, or exclude it from the quantitative core and address it qualitatively in decision meetings. This protects the integrity of the analysis and reduces late-stage disputes.
A strong fact base is not defined by perfection. It is defined by fitness for purpose. If the data is good enough to identify low-value complexity, estimate transfer risk, quantify likely savings, and support defensible decisions, the program can move forward. The discipline is to know where the data is strong, where it is weak, and how that uncertainty will be managed. That is what turns messy operational data into a reliable foundation for SKU decisions.
