An online retailer's order file is usually the first place a revenue question goes. It contains every line item, every discount and every status, so it looks like a complete record of what happened. It also contains cancellations, refunds, payment failures and blank rows — which means a sum of the order column is not revenue, and a count of rows is not orders.
The decision
The commercial team needed a trustworthy view of realised sales by category, payment method and month: what was ordered, what was cancelled, what was refunded, and which categories carried the highest rate of failed transactions. Before that view could exist, the file had to be reduced to a clean order-line table with a documented status taxonomy.
What we worked with
An order-line extract covering July 2016 to August 2018:
- Transaction: item identifier, order number, date, quantity, unit price, line total and discount amount.
- Status: complete, cancelled, received, refunded, pending, payment review and a small number of fraud flags.
- Commercial context: category, payment method and customer identifier.
After removing blank rows, the usable extract contained roughly 585,000 line items across about 409,000 orders and 115,000 customers.
The traps we designed around
- Almost half the file was empty. The raw extract had about 1.05 million rows, but 464,000 of them carried no item, order, customer or amount — they were blank rows at the end of the sheet. Counting rows would have overstated the business by 44%. Removing them was the first step, not a footnote.
- Line items are not orders. Orders contain multiple lines; the average is about 1.4 lines, with some orders much larger. Counting lines as orders inflates conversion and average order value. The correct denominator has to be stated for every metric.
- Status is the outcome. Only a minority of lines are complete. Cancelled, refunded and refund-requested lines together account for around 46% of the real lines. Summing the line total across all statuses produces a gross figure that no one should call revenue.
- Gross, net and collected are different measures. A cancelled order may never have been paid; a refunded order may have been paid and reversed; cash-on-delivery may fail at the door. The file supports a gross measure and a status-adjusted measure, but not a cash-collected measure without payment reconciliation.
- Zero and negative values. Thousands of lines have zero or negative totals, and some have zero price. These can be free items, data errors, adjustments or reversals. They need a rule, not silent inclusion.
- Mixed date formats. Dates are stored as text in a locally formatted string. Sorting or filtering them as strings produces the wrong order and the wrong monthly aggregates. Parsing with an explicit format was required.
- Fraud is too rare to analyse. Only ten lines are flagged as fraud. That is enough to know the flag exists, not enough to build or validate a fraud model from this file.
How the analysis was built
- Remove non-data rows. Blank rows were dropped and the row count reconciled against the item identifier.
- Fix the grain. The base table was defined as one line per item per order, with order-level metrics calculated from a separate aggregation.
- Define a status taxonomy. Statuses were mapped to three groups: realised, reversed and open. The mapping was reviewed with the commercial owner because "received" and "complete" do not mean the same thing operationally.
- Build gross and net measures. Gross line value was kept for reconciliation; net value excluded cancelled and refunded lines. Both were reported, never mixed.
- Compare categories and payment methods on rates. Cancellation and refund rates were reported with order counts, so a small category could not lead the ranking on a handful of failures.
- Reconcile the totals. Category totals were checked against the order-level total and against the raw file after status filtering.
What this changes
- Revenue reporting starts from net. Gross line value is a reconciliation number, not the headline.
- Cancellation and refund rates become first-class metrics. They are reviewed by category and payment method alongside sales.
- Cash-on-delivery risk is visible. COD is the dominant payment method, and its failure pattern differs from prepaid methods, which changes the collection conversation.
- Category comparisons are fair. Order counts and volume thresholds sit next to every rate, so small categories do not win by noise.
What you receive
- A cleaned order-line table with documented row and grain rules.
- A status taxonomy and the resulting gross, net and open measures.
- Monthly and category views with cancellation and refund rates.
- A payment-method comparison with volume and uncertainty shown.
- A reconciliation note that ties every dashboard total back to the source file.
Where this applies
The method fits online retailers, marketplaces and any business whose transaction file mixes realised and reversed orders. The minimum inputs are order and line identifiers, status, date, amount, discount and payment method. The first deliverable is usually the grain and status note, because most revenue disagreements are definition disagreements.
This is an illustrative case study using an anonymized public e-commerce dataset; it does not report results from a specific client engagement. It reflects our standard method for order and refund analysis.
