Profit Leak audit analysis

Four theories, three of them wrong. Contribution margin per order showed the decline was happening below gross margin, in returns on a single jacket.

Added the below in a Custom HTML in WordPress, otherwise it won’t render correctly.

In the Real World · Brief · Data science · Core · 1 to 2 days

Revenue is up. Profit is not keeping pace. The finance director wants to know where the money goes, in pounds, ranked, with a recommendation for each. Work it yourself before reading the walkthrough.

The situation

Fellgate Outdoor sells outdoor gear into the UK, Ireland and the EU. You have twelve months of trading, 2025-08-01 to 2026-07-31. Revenue has grown across the year. Contribution has grown more slowly, and nobody can say why.

Four theories are circulating, one from each corner of the business. Free delivery is the leak. EU orders lose money. Discounting has crept up. The product mix has moved toward low-margin hardware. Your job includes finding out which of them are true.

The data

FileGrainWhat it holds
fellgate-order-lines.csvOne row per line item4,554 rows. Orders, destinations, channels, prices, discounts, delivery charged, and whether the line came back
fellgate-products.csvOne row per SKU12 products with unit cost, actual weight and volumetric weight
fellgate-shipping-rates.csvOne row per zone and weight band15 bands across 3 zones. What the carrier charges, which the shop never sees

The files are as exported. They have not been cleaned or joined for you.

Cost assumptions from finance

CostAssumption
Payment processing1.4% plus 0.20 per order
Pick and pack1.10 per order per order
Return carriage4.20 UK, 7.90 elsewhere
Return handling2.50 per returned line
Stock written off on return12% of returned stock at cost
Free deliveryFree on orders over 50.00 pounds, otherwise 3.95

They are estimates. Treat them as inputs to test, not as facts.

Definition of done

  1. A contribution margin defined precisely enough that another analyst would compute the same number, and a stated reason for the grain you chose.
  2. A verdict on each of the four theories, with the evidence.
  3. The decline in margin split into its causes, sized in pounds.
  4. A sensitivity test on the finance assumptions, showing whether your ranking survives them.
  5. Ranked recommendations, each with what it is worth and how confident you are.
  6. An explicit list of what the business should not do.

Three questions worth asking before you write any code

At what grain are these costs actually incurred, and does your metric see them there? The business is seasonal, so which comparisons are safe and which are measuring the weather? And when you find something expensive, is it recoverable cash or just a sizing of exposure?

If you want to go further

  • Look closely at the two weight columns in the product file and work out which one the carrier bills on. Then work out what using the wrong one does to your model.
  • Plot the distribution of order values between 30 and 70 pounds and explain the shape.
  • Before recommending any delivery charge, work out what it would do to the orders that are already profitable.

When you are done, read the walkthrough. It works the same three files through eight sections to a board-ready memo. Compare your ranking to its ranking. The interesting comparison is not who found more, it is whether you sized them the same way and whether you separated what is measurable from what is merely large.

In the Real World · Data science · Core · 1 to 2 days

Revenue grew 69.2% across the year. Contribution grew 55.4%. Everyone in the building blamed free delivery. Free delivery turned out to cost 483 pounds, and the real leak was somewhere nobody was looking.

The situation. Fellgate Outdoor sells outdoor gear into the UK, Ireland and the EU. Twelve months of trading, 2,674 orders, 346,756 pounds of net revenue. The board can see revenue climbing and profit refusing to follow. The finance director wants to know where the money goes.

What the business is left with. A contribution margin per order, a ranked list of where margin leaks, and three fixes sized in pounds.

Attempt it first. The brief has the question, the three files and the cost assumptions, with none of the answers.

Section 1Problem Definition

No code yet. A margin audit that starts in the data finds whatever is easiest to find, which is almost never the biggest number.

Business objective

Fellgate Outdoor grew revenue this year and did not grow profit at the same rate. The finance director needs to know which parts of the business destroy margin, in pounds, ranked, with a recommendation for each. The answer has to survive a board meeting, which means every number needs an assumption written next to it.

Problem statement

Build a contribution margin for every order between 2025-08-01 and 2026-07-31, then explain the difference between revenue growth and contribution growth.

What the business already believes

Four theories, collected before the analysis started, so they can be tested rather than argued about. Every one of them came from a different person in the room.

  1. H1. Free delivery is the leak. The threshold has not moved in three years while carriage has risen.
  2. H2. EU orders lose money. Carriage is far higher and the prices are the same.
  3. H3. Discounting has crept up. Marketing has been leaning on codes.
  4. H4. The product mix has shifted toward low-margin hardware.

Success criteria

RequirementWhy it matters
A contribution margin per order, not per productShipping, payment fees and pick costs are incurred per order. A product-level margin cannot see them, which is exactly why the leak survived this long
Every cost assumption stated and sensitivity testedHalf of a contribution model is assumptions. If the conclusion flips when the write-off rate moves, the board needs to know that before they act on it
Findings ranked in poundsFour leaks worth different amounts are not four equal problems
A recommendation that does not damage what already worksThe obvious fix to a shipping leak is to stop shipping free. That would be a mistake here, and section 7 shows why

Cost assumptions, supplied by finance

CostAssumption
Payment processing1.4% plus 0.20 per order
Pick and pack1.10 per order per order
Return carriage4.20 UK, 7.90 elsewhere
Return handling2.50 per returned line
Stock written off on return12% of returned stock at cost
Free delivery threshold50.00 pounds on order subtotal

These are estimates, not measurements. That is normal and it is not a reason to skip the analysis, but it is a reason to test them, which section 7c does.

Risks

  • Contribution margin is a modelled number. Change an assumption and it moves.
  • The business is seasonal, so any comparison of one period against another risks measuring the season rather than the change. Section 6 deals with this.
  • Costs incurred per order have to be allocated somewhere. Allocation choices can manufacture findings if they are not stated.

Section 2Data Collection

Three files, from three teams, which is how margin data always arrives. Nobody owns the whole picture, and that is a large part of why nobody had spotted this.

FileOwnerGrainWhat it contributes
fellgate-order-lines.csvEcommerceOne row per line itemWhat sold, to whom, where, and whether it came back
fellgate-products.csvMerchandisingOne row per SKUUnit cost, actual weight and volumetric weight
fellgate-shipping-rates.csvLogisticsOne row per zone and weight bandWhat the carrier charges, which the shop never sees
import numpy as np
import pandas as pd
from scipy import stats

lines = pd.read_csv('data/fellgate-order-lines.csv')   # the transactions
prod  = pd.read_csv('data/fellgate-products.csv')      # cost and weight
rates = pd.read_csv('data/fellgate-shipping-rates.csv')# the carrier rate card

print('order lines :', len(lines))
print('orders      :', lines['order_id'].nunique())
print('products    :', len(prod))
print('rate bands  :', len(rates))
order lines : 4,554
orders      : 2,674
products    : 12
rate bands  : 15

Data dictionary

ColumnFileMeaning
order_id, order_date, customer_idlinesWho ordered what, and when
country, channellinesDestination and acquisition source
sku, quantity, unit_price, line_discountlinesThe line economics as the shop saw them
shipping_chargedlinesWhat the customer paid for delivery, repeated on every line of the order
line_status, return_reasonlinescompleted or returned, and why
unit_costproductsWhat Fellgate pays the supplier
weight_kg, volumetric_kgproductsActual weight, and the carrier’s dimensional measure
zone, weight_from_kg, weight_to_kg, carrier_costratesThe rate card, by destination zone and weight band

The column most people skip

volumetric_kg is in the catalogue because carriers bill on the greater of actual and volumetric weight. A sleeping bag weighs little and fills a van. Ignore that column and every shipping cost in the model is wrong in the same direction, which is the quietest kind of error there is. Section 3f puts a number on it.

Section 3Data Preprocessing

On a margin audit, preprocessing is where the cost model gets built. A mistake here does not produce a messy chart, it produces a confident wrong number in a board pack.

3aDuplicates and schema checks

# exact duplicate rows: the classic symptom of a re-run export
dupes = lines.duplicated(keep='first').sum()
lines = lines.drop_duplicates().reset_index(drop=True)
print('exact duplicate rows dropped :', dupes)
print('lines remaining              :', len(lines))

# before any join, confirm the keys line up. A silent join failure here would
# drop cost from some lines and quietly overstate margin.
print('SKUs in orders not in catalogue :', sorted(set(lines['sku']) - set(prod['sku'])))
print('catalogue rows per SKU          :', prod['sku'].is_unique)
exact duplicate rows dropped : 38
lines remaining              : 4,516
SKUs in orders not in catalogue : []
catalogue rows per SKU          : True

38 duplicated rows out of 4,554. Small, and worth removing before anything is summed: a duplicated line double counts revenue and cost, and because margin is a difference between two large numbers, small duplication moves it more than you expect.

# validate='many_to_one' makes pandas raise if the catalogue is not unique
# on sku. Without it, a duplicated catalogue row silently multiplies your rows.
d = lines.merge(prod, on='sku', how='left', validate='many_to_one')
assert d['unit_cost'].notna().all(), 'a line failed to join to the catalogue'
print('joined rows :', len(d))

The validate argument is the cheapest insurance in pandas. A join that silently fans out is the single most common way a margin model ends up wrong, and it produces numbers that look entirely plausible.

3bHandling categorical mess

The categoricals are clean. What is not clean is that the rate card and the order file do not speak the same language: orders record a country, the carrier prices a zone.

# five countries collapse into three pricing zones
ZONE = {'UK': 'UK', 'Ireland': 'Ireland',
        'Germany': 'EU', 'France': 'EU', 'Netherlands': 'EU'}

d['zone'] = d['country'].map(ZONE)

# a mapping is only safe if you prove nothing fell through it
print('countries not in the map :', sorted(set(d['country']) - set(ZONE)))
print('rows with no zone        :', d['zone'].isna().sum())
countries not in the map : []
rows with no zone        : 0

Always print that second line. A hand-written mapping that misses a value produces nulls, nulls silently drop out of a groupby, and the missing revenue never appears in any total. Nobody notices a number that was never there.

3cDealing with outliers

There are no data-entry errors in this file. There is something more interesting: a spike in the order value distribution that is not an error at all.

d['line_revenue'] = d['quantity'] * d['unit_price'] - d['line_discount']
orders = d.groupby('order_id')['line_revenue'].sum()

# how many orders land just below the free delivery threshold, and just above
bands = pd.cut(orders, [30, 38, 42, 46, 50, 54, 58, 62, 70])
print(bands.value_counts().sort_index().to_string())
(30, 38]       43
(38, 42]        7
(42, 46]       63
(46, 50]        8
(50, 54]      147
(54, 58]       52
(58, 62]      146
(62, 70]       40

7 orders between 46 and 50 pounds. 53 orders between 50 and 54. A ratio of 7.6 to one across a four pound step.

That is not an outlier and it is not noise. It is the free delivery threshold doing exactly what a threshold does: customers sitting just below it add something small to qualify. Hold onto it, because in section 7 it turns out the business is manufacturing the precise orders that cost it money.

3dHandling missing values

d['returned'] = d['line_status'] == 'returned'
blank = d['returned'] & d['return_reason'].isna()

print('returned lines          :', d['returned'].sum())
print('with no reason recorded :', blank.sum(),
      f'({blank.sum() / d["returned"].sum():.1%})')
print('missing cells elsewhere :',
      d.drop(columns=['return_reason']).isna().sum().sum())
returned lines          : 467
with no reason recorded : 64 (13.7%)
missing cells elsewhere : 0

Nothing missing except return reasons, and those are missing on 13.7% of returns. Do not impute them. A reason code is a free-text choice a warehouse operator either made or did not, and inventing one would fabricate evidence for the exact question section 7 uses reason codes to answer. Report the blanks as their own category and let the reader see how much is unknown.

3eHandling skewed data

disc = d[d['line_discount'] > 0]
depth = (disc['line_discount'] / (disc['quantity'] * disc['unit_price'])).round(2)

print('discounted lines :', len(disc), f'({len(disc)/len(d):.2%})')
print('total discount   :', round(d['line_discount'].sum()))
print('depths used      :', sorted(depth.unique()))
discounted lines : 378 (8.37%)
total discount   : 5,386
depths used      : [0.1, 0.15, 0.2]

Three fixed depths and nothing between them, which says the discounts come from a small set of codes rather than from anyone negotiating. Revenue per order is right-skewed, as it is in every retailer, but not so heavily that the mean misleads. We keep the mean and report totals, because the finance director is asking about pounds in aggregate, not about a typical customer.

3fData types and normalisation

The one derived field that decides the whole cost model.

# Carriers bill the GREATER of actual and volumetric weight. Per line, that is
# the heavier measure multiplied by the quantity ordered.
d['chargeable_kg'] = np.maximum(d['weight_kg'], d['volumetric_kg']) * d['quantity']
d['actual_kg']     = d['weight_kg'] * d['quantity']

# the other derived columns everything downstream needs
d['line_cogs'] = d['quantity'] * d['unit_cost']
d['month']     = pd.to_datetime(d['order_date']).dt.to_period('M').astype(str)

print('SKUs where volumetric exceeds actual :',
      (prod['volumetric_kg'] > prod['weight_kg']).sum(), 'of', len(prod))
print('worst offender  :', prod.loc[(prod['volumetric_kg']/prod['weight_kg']).idxmax(),
                                    'product_name'])
print('total actual kg     :', round(d['actual_kg'].sum()))
print('total chargeable kg :', round(d['chargeable_kg'].sum()))
SKUs where volumetric exceeds actual : 12 of 12
worst offender  : Summit Trekking Poles
total actual kg     : 6,033
total chargeable kg : 10,223

Every SKU in the catalogue is bulkier than it is heavy, and the worst is the summit trekking poles at 3.06 times its actual weight. Across the year the business ships 10,223 chargeable kilograms against 6,033 real ones. Section 7b prices that difference.

Section 4Exploratory Data Analysis

4aThe primary metric

Contribution margin, built once, at order level, and then everything else is a cut of it. Section 5 gives the full definition. This is what it produces.

Contribution bridge346,756Netrevenue213,478Costofgoods28,493Carriage3,632Returnshandling5,983Paymentfees2,941Pickandpack92,228Contributionpounds over twelve months

Twelve months, 2,674 orders. Net revenue 346,756 pounds down to contribution 92,228, a rate of 26.6%.

LinePoundsShare of net revenue
Net revenue346,756100.0%
Cost of goods(213,478)61.6%
Carriage(28,493)8.2%
Returns handling(3,632)1.0%
Payment fees(5,983)1.7%
Pick and pack(2,941)0.8%
Contribution92,22826.6%

Two numbers to sit with. Carriage is 8.2% of net revenue, against 1,386 pounds recovered from customers across the whole year. And 372 orders, 13.9% of the book, finish below zero, together losing 5,549 pounds.

4bNumerical variables

The trend is the point of this section, not the distributions.

Contribution rate by month22%24%26%28%30%32%25-0825-1025-1226-0226-0426-06trend -0.33pp per month, p = 0.011

Contribution rate by month. The fitted trend falls 0.3335 percentage points a month, p = 0.01086.

A decline of 0.3335 points a month with a p-value of 0.0109. Small enough that no single month looked alarming, which is precisely why it ran for a year. Nobody saw a bad month, because there was not one.

4cCategorical variables

ZoneOrdersNet revenueContributionRate
UK1,897246,60970,42328.6%
EU47060,12312,33620.5%
Ireland30740,0249,47023.7%
CategoryLinesRevenueGross marginReturn rate
Apparel2,628170,32659.5%14.5%
Camp77157,80621.7%3.6%
Packs33050,78330.0%7.3%
Shelter18344,51023.9%3.8%
Sleep23438,71325.0%3.9%
Hardware37025,65526.2%4.6%

Apparel is the interesting row. It carries the best gross margin in the business at 59.5% and the worst return rate at 14.5%, roughly four times the camping categories. A gross margin view of this business makes apparel look like the hero. It is not obvious yet that it is not.

4dRelationships between variables

The relationship that drives everything later is between what an order weighs and what the courier charges for it. That is a step function, not a line, and steps are where orders fall off a cliff.

# what one parcel costs to send, by zone and chargeable weight
print(rates.pivot(index=['weight_from_kg', 'weight_to_kg'],
                  columns='zone', values='carrier_cost').to_string())

# and how the order book sits across those bands
order_kg = d.groupby('order_id')['chargeable_kg'].sum()
print()
print(pd.cut(order_kg, [0, 1, 2, 5, 100],
             labels=['under 1kg', '1 to 2kg', '2 to 5kg', 'over 5kg'])
        .value_counts().sort_index().to_string())
                             EU  Ireland     UK
weight_from_kg weight_to_kg
0.0            0.5         6.40     5.80   3.10
0.5            1.0         8.70     7.90   4.40
1.0            2.0        11.90    10.60   6.20
2.0            5.0        18.40    16.20   9.80
5.0            30.0       26.80    23.40  14.50

Crossing from 2kg to 2.1kg costs another 3.60 pounds in the UK and 6.50 in the EU, on an order whose price did not change. Now hold that next to the contribution the same orders produce.

           size   mean       sum
under 1kg    515  32.14     16550
1 to 2kg     377  41.49     15642
2 to 5kg     709  33.80     23961
over 5kg     722  45.07     32541

The answer that stops the obvious fix

Heavy orders are the most profitable orders in the business. The over 5kg band averages 45.07 pounds of contribution, the best of any band, because heavy things here are tents and sleeping bags and they cost a lot.

Anyone who reacts to a shipping cost problem by charging for heavy delivery would be taxing 56,502 pounds of the healthiest contribution on the book.

4eTesting our hypotheses

HypothesisVerdictEvidence
H1. Free delivery is the leakMostly wrongOrders shipped free contribute 88,695 pounds. The genuinely loss-making slice is 151 orders worth -483 pounds
H2. EU orders lose moneyWrongEU contributes 12,336 pounds at 20.5%. Lower than the UK, not negative
H3. Discounting has crept upWrongDiscount fell from 1.60% of revenue to 1.24%
H4. Mix has shifted to low marginPartly rightShelter grew 3.02pp of revenue at 23.9% margin, apparel shrank 2.67pp at 59.5%

Three of four theories are wrong and the fourth is a fraction of the answer. That is a normal result and it is worth stating plainly to the room, because the alternative is an audit that quietly confirms whoever spoke loudest.

4fSubgroups

CutOrdersMean contributionShare loss-making
All orders2,67434.4913.9%
Orders with no return2,22340.76
Orders with a return4513.57
Free shipping, under 75 pounds, over 2kg151-3.2056.3%

One of those rows is a different order of magnitude from the others. An order with a return in it contributes 3.57 pounds. An order without one contributes 40.76. Section 7 is mostly about that gap.

Section 5Metric Construction

The definition, in full, because a contribution margin that is not defined precisely is just an opinion with a decimal point.

# the assumptions from section 1, in one place so they can be varied later
FEE_PCT, FEE_FIXED   = 0.014, 0.20
PICK_AND_PACK        = 1.10
RET_CARRIAGE_UK      = 4.20
RET_CARRIAGE_INTL    = 7.90
RET_HANDLING         = 2.50
WRITE_OFF            = 0.12


def carrier_cost(zone, kg):
    """What the courier charges for one parcel of this weight to this zone."""
    band = rates[(rates['zone'] == zone) &
                 (rates['weight_from_kg'] < kg) &
                 (rates['weight_to_kg'] >= kg)]
    if len(band):
        return float(band['carrier_cost'].iloc[0])
    return float(rates[rates['zone'] == zone]['carrier_cost'].max())


o = d.groupby('order_id').agg(
    month         = ('month',            'first'),
    zone          = ('zone',             'first'),
    chargeable_kg = ('chargeable_kg',    'sum'),
    actual_kg     = ('actual_kg',        'sum'),
    subtotal      = ('line_revenue',     'sum'),
    cogs          = ('line_cogs',        'sum'),
    ship_charged  = ('shipping_charged', 'first'),
    returned_lines= ('returned',         'sum'),
)

# what was refunded, and what stock came back, joined on from the returned lines
back = (d[d['returned']].groupby('order_id')
        .agg(refunded_revenue=('line_revenue', 'sum'),
             returned_cogs=('line_cogs', 'sum')))
o = o.join(back).fillna({'refunded_revenue': 0.0, 'returned_cogs': 0.0})

# revenue actually kept: the order, less anything refunded, plus any delivery paid
o['net_revenue'] = o['subtotal'] - o['refunded_revenue'] + o['ship_charged']

# returned stock comes back into inventory, apart from the share written off
o['net_cogs'] = o['cogs'] - o['returned_cogs'] * (1 - WRITE_OFF)

o['carrier_cost'] = [carrier_cost(z, k) for z, k in zip(o['zone'], o['chargeable_kg'])]
o['return_cost']  = o['returned_lines'] * (
    np.where(o['zone'] == 'UK', RET_CARRIAGE_UK, RET_CARRIAGE_INTL) + RET_HANDLING)
o['payment_fee']  = (o['subtotal'] + o['ship_charged']) * FEE_PCT + FEE_FIXED

o['contribution'] = (o['net_revenue'] - o['net_cogs'] - o['carrier_cost']
                     - o['return_cost'] - o['payment_fee'] - PICK_AND_PACK)

print('orders       :', len(o))
print('contribution :', round(o['contribution'].sum()))
orders       : 2,674
contribution : 92,228

Four decisions inside that are worth defending out loud.

  1. Order level, not product level. Carriage, payment fees and pick costs are incurred once per order regardless of how many lines it holds. Allocating them to products would invent precision that does not exist, and it is why a product margin report never found this.
  2. Returned stock comes back at 88 percent. A return is not a lost sale plus a lost product. Most of it goes back on the shelf. Treating returns as total write-offs would overstate the problem by a wide margin, and the section 7c sensitivity tests exactly this.
  3. Outbound carriage is not recovered on a return. The parcel went out and the courier was paid. That cost stays in the order.
  4. Contribution, not net profit. No rent, no salaries, no marketing. Those are real but they are not driven by the order, and mixing them in would bury the thing we are trying to see.

Section 6Method Selection

QuestionMethodWhy not the obvious alternative
Is the margin rate really fallingLinear regression of monthly rate on month indexComparing the first half to the second half compares a winter to a summer in a seasonal business. The trend across all twelve months does not have that problem
Did the return rate change on one productChi-square on a before-and-after contingency tableComparing two percentages by eye cannot tell you whether a move is larger than the sample supports. On smaller SKUs it would be nowhere near significant
Why did gross margin moveMix versus rate decompositionReporting that margin fell says nothing about whether to renegotiate with suppliers or change what gets promoted. The split answers that; the headline does not
What does a policy change recoverStatic recomputation on the observed ordersAn elasticity model would be more sophisticated and completely unfounded here, because there is no price variation to estimate it from. Section 7d states the limitation instead of hiding it inside a model

The seasonality trap, stated once

Fellgate Outdoor sells 69.2% more in 2026-02 to 2026-07 than in 2025-08 to 2026-01. Almost none of that is growth. It is summer. Any analysis that compares those two blocks and attributes the difference to a business change is measuring the weather. Rates and trends are safe to compare across seasons; totals are not.

Section 7Analysis and Validation

7aThe baseline the business believes

Free delivery is the leak, and the fix is to raise the threshold. That belief is held sincerely, it is the first thing anyone says, and it has the great advantage of being actionable in an afternoon.

It is also mostly wrong, and the analysis has to show why rather than assert it. Beating a baseline means explaining the evidence that produced it, not dismissing the person who holds it.

7bThe analysis

Finding one: most of the decline happened below gross margin

h1, h2 = d[d['month'] < '2026-02'], d[d['month'] >= '2026-02']

cat = {}
for c in sorted(d['category'].unique()):
    a, b = h1[h1['category'] == c], h2[h2['category'] == c]
    cat[c] = {
        'h1_rate':  (a['line_revenue'].sum() - a['line_cogs'].sum()) / a['line_revenue'].sum(),
        'h2_rate':  (b['line_revenue'].sum() - b['line_cogs'].sum()) / b['line_revenue'].sum(),
        'h1_share': a['line_revenue'].sum() / h1['line_revenue'].sum(),
        'h2_share': b['line_revenue'].sum() / h2['line_revenue'].sum(),
    }

# the contribution rate in each half, from the order-level P and L
half = o['month'].lt('2026-02')
contrib_move = (100 * o.loc[~half, 'contribution'].sum() / o.loc[~half, 'net_revenue'].sum()
                - 100 * o.loc[half, 'contribution'].sum() / o.loc[half, 'net_revenue'].sum())

# split the gross margin move into mix (what we sold) and rate (what we made on it)
gm_h1 = sum(v['h1_share'] * v['h1_rate'] for v in cat.values())
gm_h2 = sum(v['h2_share'] * v['h2_rate'] for v in cat.values())

mix  = sum((v['h2_share'] - v['h1_share']) * v['h1_rate'] for v in cat.values())
rate_= sum(v['h2_share'] * (v['h2_rate'] - v['h1_rate']) for v in cat.values())

print(f'gross margin {100*gm_h1:.2f}% -> {100*gm_h2:.2f}%  ({100*(gm_h2-gm_h1):+.2f}pp)')
print(f'  of which mix  {100*mix:+.2f}pp')
print(f'  of which rate {100*rate_:+.2f}pp')
print(f'contribution rate move        {contrib_move:+.2f}pp')
print(f'therefore below gross margin  {contrib_move - 100*(gm_h2-gm_h1):+.2f}pp')
gross margin 40.71% -> 40.06%  (-0.66pp)
  of which mix  -0.89pp
  of which rate +0.24pp
contribution rate move        -2.28pp
therefore below gross margin  -1.62pp

Where the margin actually went

Contribution rate fell 2.28 percentage points. Gross margin fell only 0.66, and buying rates actually improved slightly. So 1.62 points, 71% of the whole decline, happened below the gross margin line.

Every report the board sees stops at gross margin. The leak was in the space between gross margin and contribution, which no report covered.

Finding two: returns, and one product inside them

o['has_return'] = o['returned_lines'] > 0
print(o.groupby('has_return')['contribution']
       .agg(['size', 'mean', 'sum']).round(2).to_string())
           size   mean       sum
has_return
False     2,223  40.76  90,617
True       451   3.57   1,611

451 orders, 16.9% of the book, contain a return. They contribute 1,611 pounds between them. Had they behaved like the rest of the book they would have contributed roughly 18,383. The gap is 16,772 pounds, and it is 18% of the entire year’s contribution.

Read that counterfactual carefully

It is not 16,772 pounds of recoverable cash. Some of those orders would never have happened without a generous returns policy, and a customer who returns once often keeps the rest. It is a sizing of the exposure, not a cheque. The honest recoverable number is the excess return cost on the one product below, which is far smaller and far more certain.

Returns are not evenly spread. One line accounts for a large share of them.

ProductCategoryLinesReturnsRate
Ridge Softshell JacketApparel60118330.4%
Fell Runner CapApparel5025611.2%
Merino Base LayerApparel5605810.4%
Trail Socks 2pkApparel492459.2%
Storm OvertrousersApparel473408.5%
Traverse 45L RucksackPacks330247.3%

Finding three: the softshell changed behaviour in January

apparel_baseline = d[(d['category'] == 'Apparel')
                     & (d['sku'] != 'FG-J210')]['returned'].mean()

ss   = d[d['sku'] == 'FG-J210']
pre  = ss[ss['month'] <  '2026-01']
post = ss[ss['month'] >= '2026-01']

table = [[pre['returned'].sum(),  (~pre['returned']).sum()],
         [post['returned'].sum(), (~post['returned']).sum()]]
chi2, p, _, _ = stats.chi2_contingency(table, correction=False)

print(f'before Jan {pre["returned"].mean():.4f}  ({len(pre)} lines)')
print(f'from Jan   {post["returned"].mean():.4f}  ({len(post)} lines)')
print(f'apparel baseline {apparel_baseline:.4f}')
print(f'chi2 {chi2:.2f}  p {p:.2e}')
before Jan 0.1444  (187 lines)
from Jan   0.3768  (414 lines)
apparel baseline 0.0982
chi2 32.86  p 0.00000001
Softshell return rate0%10%20%30%40%9.8%Apparelbaseline14.4%SoftshellbeforeJan37.7%SoftshellfromJan

The Ridge Softshell Jacket against the apparel baseline, before and after January. The step is 32.859 on a chi-square, p = 0.00000001.

The reason codes say what happened.

ReasonRidge Softshell JacketOther apparel
too small7642
too large3624
poor fit3314
(not recorded)2827
arrived late427
faulty320
changed mind226
not as described119

94% of the recorded reasons on this product are about fit. On the rest of the apparel range the reasons spread across late delivery, faults and changes of mind, the ordinary distribution. This is not a returns problem in general. It is a sizing problem on one garment, and it began in January.

Sized against the category baseline: roughly 115 excess returns since January, at 12.33 pounds each in carriage, handling and write-off, which is about 1,422 pounds. That one is a cheque, and it recurs every year until someone fixes the size guide.

Finding four: the shipping cost model is wrong before any policy question

# what carriage would have cost if the model used actual weight, as most do
o['cost_if_actual'] = [carrier_cost(z, k) for z, k in zip(o['zone'], o['actual_kg'])]

gap = o['carrier_cost'].sum() - o['cost_if_actual'].sum()
print(f'modelled on actual weight     : {o["cost_if_actual"].sum():,.0f}')
print(f'billed on chargeable weight   : {o["carrier_cost"].sum():,.0f}')
print(f'understated by                : {gap:,.0f}  ({100*gap/o["cost_if_actual"].sum():.1f}%)')
print(f'orders affected               : {(o["carrier_cost"] > o["cost_if_actual"]).sum():,}')
modelled on actual weight     : 23,189
billed on chargeable weight   : 28,493
understated by                : 5,304  (22.9%)
orders affected               : 1,294

5,304 pounds, 22.9% of the carriage bill, on 1,294 orders, 48.4% of the book. This is not a leak in the business, it is a leak in the reporting: any margin model built on actual weight understates cost by this much and always in the same direction.

Finding five: free delivery, sized honestly

Contribution by chargeable weight-100102030405032.14under1kg515 orders41.491to2kg377 orders33.802to5kg709 orders45.07over5kg722 orders-3.20under 75 poundsand over 2kg151 ordersmean contribution per order, in pounds, orders shipped free

Mean contribution per order for orders shipped free. The first four bars are the whole free-shipping book by weight. The last is the intersection that actually loses money.

151 orders, 5.7% of the book, sit under 75 pounds and over 2kg chargeable. They average -3.20 pounds of contribution, 56.3% of them lose money, and together they cost 483 pounds. Mean carriage on them is 14.68 pounds against a mean order of 60.13.

So H1 was pointing at something real and had the size wrong by two orders of magnitude. 483 pounds is not the reason profit is flat. And section 3c explains where those orders come from: the threshold itself manufactures them, at 7.6 to one either side of the 50 pound line.

Putting the losses in order

Loss-making ordersCountTotal
No return48-196
No return, also in the shipping band67-486
With a return239-4,556
With a return, also in the shipping band18-310

Of 5,549 pounds lost on loss-making orders, 4,866 involves a return and 486 is the shipping band alone. Returns are the story by a distance.

7cSensitivity and robustness

Half of this model is finance’s assumptions. If the conclusion depends on them, that has to be said before the board acts on it.

Assumption variedContributionRateReturns gap per order
write-off rate 5%93,66527.0%34.00
write-off rate 12%92,22826.6%37.19
write-off rate 25%89,56025.8%43.11
return handling 1.5092,69526.7%36.16
return handling 2.5092,22826.6%37.19
return handling 4.0091,52826.4%38.74

Move the write-off rate from 5 percent to 25 percent, a range far wider than anyone believes, and contribution moves from 93,665 to 89,560 pounds. The returns gap per order moves from 34.00 to 43.11. Every version of the assumption produces the same ranking and the same recommendation. The finding is not an artefact of the numbers finance handed over.

The check worth running on any modelled metric

Vary each assumption across a range wider than you can defend, and see whether the ranking of your findings changes. If it does, the ranking is a property of your spreadsheet rather than of the business, and it should not leave your laptop.

7dWhat would change the answer

Three policy options, each recomputed on the actual orders.

OptionRecoversOrders affectedOf which already profitable
Raise the free delivery threshold to 75 pounds2,046518200
Free delivery only under 2kg chargeable8,5141,431906
Charge 5.95 only where the order is under 75 pounds and over 2kg8981510

The blunt option recovers the most, 8,514 pounds, and does it by charging 906 orders that were already healthy. The surgical option recovers 898 and touches nothing that was working.

These are static numbers and that is their limitation. They assume nobody changes behaviour when a delivery charge appears, which is false. Some of those orders would not be placed. There is no price variation in this data to estimate the elasticity from, so the honest position is to present the arithmetic, name the assumption, and test the change rather than model it.

Which is the actual recommendation: the surgical charge is small enough to trial and the trial answers the elasticity question for free.

Before you change the threshold

Sample Size Calculator

A delivery charge on 151 orders a year is a small sample. Put the baseline conversion rate and the effect you would need to see into this and it tells you how long the trial has to run before the result means anything. On a band this size the answer is often longer than people expect.

Open the calculator

Free, no signup. Pairs with the Power and MDE Calculator.

Section 8Decision and Handoff

One page the finance director can take to the board.

Where the money goes

Contribution rate fell 2.28 points across the year, a trend significant at p = 0.0109. Only 0.66 points of that is gross margin, and buying rates improved. 1.62 points sit below the gross margin line, in returns and carriage, which no board report covers.

Free delivery is not the problem. It costs 483 pounds a year in a narrow band of 151 orders. Charging for heavy delivery generally would tax 56,502 pounds of the best contribution on the book.

Returns are the problem. Orders containing a return contribute 3.57 pounds against 40.76 for everything else. One product, the Ridge Softshell Jacket, went from 14.4% to 37.7% returns in January against a category baseline of 9.8%, and 94% of its recorded reasons are about fit.

The three actions, in order of certainty

ActionWorthCertaintyOwner
Fix the Ridge Softshell Jacket sizing. Remeasure the batch, correct the size guide, add fit guidance to the pageabout 1,422 a yearHigh. Excess returns against a category baseline, with reason codes agreeingBuying and Ecommerce
Rebuild the shipping cost model on chargeable weight5,304 of cost currently unreportedCertain. It is arithmetic against the rate cardFinance
Trial a 5.95 delivery charge on orders under 75 pounds and over 2kgup to 898 a yearMedium. Static estimate, no elasticity in the dataEcommerce

What not to do

  • Do not raise the free delivery threshold across the board. It recovers 2,046 pounds and charges 200 already-profitable orders to get it.
  • Do not stop free delivery on heavy items. Heavy orders are the most profitable in the business, averaging 45.07 pounds.
  • Do not tighten the returns policy. The evidence points at one garment, not at customer behaviour. A blanket policy change would cost apparel demand, and apparel carries the best gross margin in the business.
  • Do not treat the 16,772 pound returns gap as recoverable. It sizes an exposure. The recoverable figure is the excess on the softshell.

What to measure from now on

  1. Contribution margin per order, monthly, on the definition in section 5. Gross margin alone would have hidden 71% of this decline.
  2. Return rate by SKU against its category baseline, with an alert when a line doubles. The softshell ran for six months before anyone noticed.
  3. Carriage recovery: what customers pay for delivery against what the carrier charges. This year that was 1,386 against 28,493.
  4. The share of orders landing within 5 pounds of the free delivery threshold.

Put these measures somewhere permanent

Ecommerce Dashboard: A Free Excel Template

The four measures above want a home that is not a notebook. This template takes an order export and gives you the sales and margin views back, ready to point at your own data, so the monitoring survives after the audit is filed.

Download the dashboard

Free Excel template, LAD branded. No signup.

Reproducibility

ItemValue
Filesfellgate-order-lines.csv, fellgate-products.csv, fellgate-shipping-rates.csv
Rows4,554 lines, 2,674 orders
Window2025-08-01 to 2026-07-31
Payment fee1.4% plus 0.20 per order
Pick and pack1.10 per order per order
Return carriage4.20 UK, 7.90 elsewhere
Return handling2.50 per returned line
Write-off12% of returned stock at cost
MethodsLinear regression on monthly rate, chi-square contingency, mix and rate decomposition
Librariespandas, numpy, scipy.stats

What to take from this

  • Build the metric at the grain the costs occur. Carriage, fees and pick costs happen per order. A product margin report cannot see them, which is exactly why this survived a year.
  • Test the theories in the room, in writing. Three of the four here were wrong. Without writing them down first, the audit would have quietly confirmed whichever one its author preferred.
  • Split mix from rate before recommending anything. They call for completely different actions and the headline number contains both.
  • The obvious fix is often a tax on what works. Heavy orders looked like the villain and were the most profitable thing in the business.
  • Separate exposure from recoverable cash. 16,772 pounds sizes a problem. 1,422 pounds is what you can actually go and get.

The finance director asked where the money goes. The answer is that it goes out below the line every report stops at, mostly through one jacket that stopped fitting in January. That is a smaller and less dramatic answer than the room expected, and it is the one that can be acted on this week.

Hope this is useful, Andrei.

View Comments (2)

Leave a Reply

Subscribe to My Newsletter

Subscribe to my email newsletter to get the latest posts delivered right to your email. Pure inspiration, zero spam.

Discover more from Discuss Data Science, Machine Learning and Analytics

Subscribe now to keep reading and get access to the full archive.

Continue reading