Added the below in a Custom HTML in WordPress, otherwise it won’t render correctly.
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
| File | Grain | What it holds |
|---|---|---|
| fellgate-order-lines.csv | One row per line item | 4,554 rows. Orders, destinations, channels, prices, discounts, delivery charged, and whether the line came back |
| fellgate-products.csv | One row per SKU | 12 products with unit cost, actual weight and volumetric weight |
| fellgate-shipping-rates.csv | One row per zone and weight band | 15 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
| Cost | Assumption |
|---|---|
| Payment processing | 1.4% plus 0.20 per order |
| Pick and pack | 1.10 per order per order |
| Return carriage | 4.20 UK, 7.90 elsewhere |
| Return handling | 2.50 per returned line |
| Stock written off on return | 12% of returned stock at cost |
| Free delivery | Free on orders over 50.00 pounds, otherwise 3.95 |
They are estimates. Treat them as inputs to test, not as facts.
Definition of done
- A contribution margin defined precisely enough that another analyst would compute the same number, and a stated reason for the grain you chose.
- A verdict on each of the four theories, with the evidence.
- The decline in margin split into its causes, sized in pounds.
- A sensitivity test on the finance assumptions, showing whether your ranking survives them.
- Ranked recommendations, each with what it is worth and how confident you are.
- 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.
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.
Contents
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.
- H1. Free delivery is the leak. The threshold has not moved in three years while carriage has risen.
- H2. EU orders lose money. Carriage is far higher and the prices are the same.
- H3. Discounting has crept up. Marketing has been leaning on codes.
- H4. The product mix has shifted toward low-margin hardware.
Success criteria
| Requirement | Why it matters |
|---|---|
| A contribution margin per order, not per product | Shipping, 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 tested | Half 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 pounds | Four leaks worth different amounts are not four equal problems |
| A recommendation that does not damage what already works | The 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
| Cost | Assumption |
|---|---|
| Payment processing | 1.4% plus 0.20 per order |
| Pick and pack | 1.10 per order per order |
| Return carriage | 4.20 UK, 7.90 elsewhere |
| Return handling | 2.50 per returned line |
| Stock written off on return | 12% of returned stock at cost |
| Free delivery threshold | 50.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.
| File | Owner | Grain | What it contributes |
|---|---|---|---|
| fellgate-order-lines.csv | Ecommerce | One row per line item | What sold, to whom, where, and whether it came back |
| fellgate-products.csv | Merchandising | One row per SKU | Unit cost, actual weight and volumetric weight |
| fellgate-shipping-rates.csv | Logistics | One row per zone and weight band | What 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
| Column | File | Meaning |
|---|---|---|
| order_id, order_date, customer_id | lines | Who ordered what, and when |
| country, channel | lines | Destination and acquisition source |
| sku, quantity, unit_price, line_discount | lines | The line economics as the shop saw them |
| shipping_charged | lines | What the customer paid for delivery, repeated on every line of the order |
| line_status, return_reason | lines | completed or returned, and why |
| unit_cost | products | What Fellgate pays the supplier |
| weight_kg, volumetric_kg | products | Actual weight, and the carrier’s dimensional measure |
| zone, weight_from_kg, weight_to_kg, carrier_cost | rates | The 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.
Twelve months, 2,674 orders. Net revenue 346,756 pounds down to contribution 92,228, a rate of 26.6%.
| Line | Pounds | Share of net revenue |
|---|---|---|
| Net revenue | 346,756 | 100.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% |
| Contribution | 92,228 | 26.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 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
| Zone | Orders | Net revenue | Contribution | Rate |
|---|---|---|---|---|
| UK | 1,897 | 246,609 | 70,423 | 28.6% |
| EU | 470 | 60,123 | 12,336 | 20.5% |
| Ireland | 307 | 40,024 | 9,470 | 23.7% |
| Category | Lines | Revenue | Gross margin | Return rate |
|---|---|---|---|---|
| Apparel | 2,628 | 170,326 | 59.5% | 14.5% |
| Camp | 771 | 57,806 | 21.7% | 3.6% |
| Packs | 330 | 50,783 | 30.0% | 7.3% |
| Shelter | 183 | 44,510 | 23.9% | 3.8% |
| Sleep | 234 | 38,713 | 25.0% | 3.9% |
| Hardware | 370 | 25,655 | 26.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
| Hypothesis | Verdict | Evidence |
|---|---|---|
| H1. Free delivery is the leak | Mostly wrong | Orders shipped free contribute 88,695 pounds. The genuinely loss-making slice is 151 orders worth -483 pounds |
| H2. EU orders lose money | Wrong | EU contributes 12,336 pounds at 20.5%. Lower than the UK, not negative |
| H3. Discounting has crept up | Wrong | Discount fell from 1.60% of revenue to 1.24% |
| H4. Mix has shifted to low margin | Partly right | Shelter 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
| Cut | Orders | Mean contribution | Share loss-making |
|---|---|---|---|
| All orders | 2,674 | 34.49 | 13.9% |
| Orders with no return | 2,223 | 40.76 | |
| Orders with a return | 451 | 3.57 | |
| Free shipping, under 75 pounds, over 2kg | 151 | -3.20 | 56.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.
- 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.
- 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.
- Outbound carriage is not recovered on a return. The parcel went out and the courier was paid. That cost stays in the order.
- 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
| Question | Method | Why not the obvious alternative |
|---|---|---|
| Is the margin rate really falling | Linear regression of monthly rate on month index | Comparing 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 product | Chi-square on a before-and-after contingency table | Comparing 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 move | Mix versus rate decomposition | Reporting 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 recover | Static recomputation on the observed orders | An 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.
| Product | Category | Lines | Returns | Rate |
|---|---|---|---|---|
| Ridge Softshell Jacket | Apparel | 601 | 183 | 30.4% |
| Fell Runner Cap | Apparel | 502 | 56 | 11.2% |
| Merino Base Layer | Apparel | 560 | 58 | 10.4% |
| Trail Socks 2pk | Apparel | 492 | 45 | 9.2% |
| Storm Overtrousers | Apparel | 473 | 40 | 8.5% |
| Traverse 45L Rucksack | Packs | 330 | 24 | 7.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
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.
| Reason | Ridge Softshell Jacket | Other apparel |
|---|---|---|
| too small | 76 | 42 |
| too large | 36 | 24 |
| poor fit | 33 | 14 |
| (not recorded) | 28 | 27 |
| arrived late | 4 | 27 |
| faulty | 3 | 20 |
| changed mind | 2 | 26 |
| not as described | 1 | 19 |
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
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 orders | Count | Total |
|---|---|---|
| No return | 48 | -196 |
| No return, also in the shipping band | 67 | -486 |
| With a return | 239 | -4,556 |
| With a return, also in the shipping band | 18 | -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 varied | Contribution | Rate | Returns gap per order |
|---|---|---|---|
| write-off rate 5% | 93,665 | 27.0% | 34.00 |
| write-off rate 12% | 92,228 | 26.6% | 37.19 |
| write-off rate 25% | 89,560 | 25.8% | 43.11 |
| return handling 1.50 | 92,695 | 26.7% | 36.16 |
| return handling 2.50 | 92,228 | 26.6% | 37.19 |
| return handling 4.00 | 91,528 | 26.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.
| Option | Recovers | Orders affected | Of which already profitable |
|---|---|---|---|
| Raise the free delivery threshold to 75 pounds | 2,046 | 518 | 200 |
| Free delivery only under 2kg chargeable | 8,514 | 1,431 | 906 |
| Charge 5.95 only where the order is under 75 pounds and over 2kg | 898 | 151 | 0 |
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.
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
| Action | Worth | Certainty | Owner |
|---|---|---|---|
| Fix the Ridge Softshell Jacket sizing. Remeasure the batch, correct the size guide, add fit guidance to the page | about 1,422 a year | High. Excess returns against a category baseline, with reason codes agreeing | Buying and Ecommerce |
| Rebuild the shipping cost model on chargeable weight | 5,304 of cost currently unreported | Certain. It is arithmetic against the rate card | Finance |
| Trial a 5.95 delivery charge on orders under 75 pounds and over 2kg | up to 898 a year | Medium. Static estimate, no elasticity in the data | Ecommerce |
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
- Contribution margin per order, monthly, on the definition in section 5. Gross margin alone would have hidden 71% of this decline.
- Return rate by SKU against its category baseline, with an alert when a line doubles. The softshell ran for six months before anyone noticed.
- Carriage recovery: what customers pay for delivery against what the carrier charges. This year that was 1,386 against 28,493.
- 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.
Free Excel template, LAD branded. No signup.
Reproducibility
| Item | Value |
|---|---|
| Files | fellgate-order-lines.csv, fellgate-products.csv, fellgate-shipping-rates.csv |
| Rows | 4,554 lines, 2,674 orders |
| Window | 2025-08-01 to 2026-07-31 |
| Payment fee | 1.4% plus 0.20 per order |
| Pick and pack | 1.10 per order per order |
| Return carriage | 4.20 UK, 7.90 elsewhere |
| Return handling | 2.50 per returned line |
| Write-off | 12% of returned stock at cost |
| Methods | Linear regression on monthly rate, chi-square contingency, mix and rate decomposition |
| Libraries | pandas, 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.
[…] Profit Leak audit analysis […]
[…] Profit Leak audit analysis […]