How to Prepare Data for an 80/20 Analysis

Start with the decision and period

Data preparation is easier when the intended decision is explicit. A customer service question may require order and line counts. A product simplification question may require SKU, family, quantity, and changeover indicators. A profitability question requires reliable revenue and cost.

Choose a period that represents current operations. A trailing twelve months is a common starting point because it captures seasonality without stretching across too many structural changes. Use a different window when the buying cycle, project cycle, acquisition history, or business change makes twelve months misleading.

Record the start date, end date, source system, extraction date, currency, and organizational scope before transforming the file.

Use the lowest practical transaction grain

An invoice-line or order-line dataset is usually the most flexible starting point. It preserves the relationship between customer, product, date, revenue, cost, and quantity.

Summary files can answer a narrow question, but they often remove the detail required to investigate mix, returns, order patterns, or customer-product intersections. Once detail is aggregated away, it cannot be recovered from the analysis output.

Each row should represent one consistent type of event. Do not mix invoice lines, monthly summaries, open orders, and budget records without a clear field identifying the record type and an intentional reason to combine them.

Required fields

Use stable identifiers wherever possible. Names are useful for presentation but less reliable for joins and grouping.

Field	Purpose	Preparation check
Transaction date	Defines period and trend	Valid date; consistent timezone if relevant
Customer ID	Groups customer activity	Stable across spelling and location changes
Customer name	Human-readable output	Mapped to the intended parent, bill-to, or ship-to level
Product ID or SKU	Groups product activity	Preserves leading zeros and meaningful punctuation
Product description	Human-readable output	One current description per product where possible
Revenue or net sales	Primary contribution measure	Net of the discounts and credits intended for the analysis
Direct cost or COGS	Supports later opportunity sizing	Preserved when reliable, without changing the initial revenue rank
Quantity	Adds volume and unit context	Units of measure are consistent or explicitly mapped

Optional fields can include customer parent, product family, channel, market, salesperson, site, order number, invoice number, freight, returns, or other complexity measures.

Map customers deliberately

Decide whether the analysis should rank parent accounts, bill-to accounts, ship-to locations, or another level. That selected entity will be ranked by revenue. Preserve both the source ID and the analysis-level ID when records are rolled up.

Common customer issues include:

• one customer represented by several spellings or IDs
• acquisitions or reorganizations that changed account structures
• internal, intercompany, or employee accounts mixed with external sales
• cash customers grouped under a generic record
• parent and location-level records combined inconsistently

Create a mapping table rather than overwriting source values. The mapping should include the original ID, analysis ID, analysis name, reason for the mapping, and review owner.

Map products without hiding complexity

SKU-level analysis preserves detail, but product families can improve interpretation. Keep both. Do not replace all SKU identifiers with family names before the first analysis.

Check for:

• superseded or renamed SKUs
• make-to-order records that create a new code for each job
• miscellaneous or non-stock items
• services, freight, and surcharges represented as products
• inconsistent units of measure
• kits or bundles whose component economics are recorded elsewhere

Document exclusions and special treatment. A freight line may be noise for one question and essential cost-to-serve evidence for another.

Clean amounts and signs

Convert numeric fields to a consistent data type and currency. Define how returns, credits, rebates, samples, and zero-value transactions should appear.

Do not remove negative values merely because they complicate the cumulative revenue ranking. Negative revenue or margin can be economically meaningful. If a particular calculation requires a separate treatment, preserve the records and disclose the rule.

Calculate gross margin consistently:

Gross margin dollars = revenue − included direct cost

Gross margin percentage = gross margin dollars ÷ revenue

Treat zero or negative revenue carefully when calculating percentages. A blank or separately classified rate is often more honest than an extreme or undefined value.

Reconcile before ranking

Aggregate the prepared file and compare it with the accepted source for the same period and scope. At minimum, reconcile revenue because it is the initial ranking measure. Reconcile direct cost separately when it will support later opportunity sizing. If quantity, orders, or invoices are used in the analysis, reconcile those counts where a trusted benchmark exists.

Investigate differences rather than forcing a tie. Common causes include timing, currency conversion, excluded business units, open versus invoiced sales, unposted credits, and differences between financial and operational definitions.

Record the reconciliation result and material unresolved differences in the analysis notes.

Validate the prepared dataset

Run these checks before producing customer or product rankings:

• every row has an in-scope date
• customer and product identifiers are populated or intentionally classified
• source row counts and prepared row counts reconcile after documented exclusions
• revenue and cost totals tie within an accepted tolerance
• duplicates are identified using a defensible transaction key
• units and currencies are consistent
• mappings do not create unintended many-to-many joins
• extreme and negative values have been reviewed
• excluded records can be reproduced from explicit rules
• the final file retains traceability to the source transaction

Preserve a repeatable data contract

Save the field definitions, mapping tables, exclusions, formulas, and reconciliation results with the analysis. When the work is refreshed, compare the new extract with that contract before reusing the workflow.

A repeatable 80/20 analysis depends less on a perfect one-time cleanup than on stable definitions. The team should be able to explain what each field means, how each entity is grouped, and why the totals are trustworthy.

Once the data is ready, continue with the practical 80/20 analysis workflow or build the customer-product intersections in Quad & Quartile analysis.

Frequently asked questions

How much history should an 80/20 analysis use?

A trailing twelve-month period is a common starting point because it captures seasonality without mixing too many structural changes. Adjust the window when the business cycle or decision requires it.

Can I run an 80/20 analysis from summarized data?

Yes for a narrow ranking, but transaction-level data preserves customer-product intersections, dates, quantities, and diagnostic detail that summarized files often remove.

How should returns and credits be handled?

Keep them visible and apply a documented sign convention. Removing negative transactions can overstate revenue and margin and hide important service or quality patterns.