Retail Profitability: The Discount Cliff
pandas profitability autopsy of a national retailer: which sub-categories quietly lose money, and the exact discount threshold where orders flip from profit to loss.
View code & data on GitHub ↗The Problem
Topline sales at this retailer grew every year, yet total profit barely moved. Somewhere inside the category mix, discounting behavior was converting revenue growth into margin erosion, the classic retail failure mode that aggregate dashboards are structurally unable to show.
The analysis had one goal: decompose profit at the order-line level to find exactly where money is lost, by sub-category, region, and most importantly by discount depth, and produce rules a category manager could apply on Monday morning.
Approach
Margin decomposition base table
Computed line-level margin percentage and profit-per-unit, then built a sub-category league table separating volume winners from margin winners.
Discount-band profitability curve
Bucketed all lines into discount bands (0%, 0-10%, 10-20%, 20-30%, 30%+) with pd.cut and traced average profit per line across the curve to find the break-even threshold.
Sub-category and region loss map
pivot_table heatmap of profit by sub-category and region, isolating where the loss-makers concentrate geographically.
Loss-order forensics
Filtered the roughly 1,870 negative-profit lines and profiled them by discount depth, category mix, and customer segment to test whether losses are strategic (loyal customers) or just leakage.
What-if repricing model
Simulated capping discounts at 20% with conservative demand elasticity assumptions to size the recoverable margin.
The Work
Representative excerpts from the analysis: the queries and transformations doing the heavy lifting. The full runnable project lives in the GitHub repo.
import pandas as pd
df = pd.read_csv("superstore.csv", encoding="latin-1",
parse_dates=["Order Date"])
league = (
df.groupby(["Category", "Sub-Category"])
.agg(sales=("Sales", "sum"),
profit=("Profit", "sum"),
orders=("Order ID", "nunique"),
avg_discount=("Discount", "mean"))
.assign(margin_pct=lambda x:
(100 * x.profit / x.sales).round(1),
avg_discount=lambda x:
(100 * x.avg_discount).round(1))
.sort_values("profit")
)
print(league.head(6)) # the loss-makers
print(league.tail(6)) # the profit engines
# Where do losing lines live?
losses = df[df["Profit"] < 0]
print(f"{len(losses):,} loss lines "
f"({len(losses)/len(df):.1%} of all lines), "
f"totaling {losses['Profit'].sum():,.0f}")bands = [-0.001, 0.0, 0.10, 0.20, 0.30, 1.0]
labels = ["0%", "0-10%", "10-20%", "20-30%", "30%+"]
df["disc_band"] = pd.cut(df["Discount"], bins=bands,
labels=labels)
cliff = (
df.groupby("disc_band", observed=True)
.agg(lines=("Profit", "size"),
avg_profit_per_line=("Profit", "mean"),
total_profit=("Profit", "sum"),
pct_loss_lines=("Profit",
lambda s: 100 * (s < 0).mean()))
.round(2)
)
print(cliff)
# Regional view of the worst sub-categories
heat = df[df["Sub-Category"].isin(
["Tables", "Bookcases", "Supplies"])].pivot_table(
index="Sub-Category", columns="Region",
values="Profit", aggfunc="sum").round(0)
print(heat)What the Data Shows
Average profit per order line by discount band
US$ per line
Below 20% discount, lines stay profitable on average. Past 20% the average line loses money, and at 30%+ discounts 97.8% of lines are sold at a loss.
Key Findings
Three sub-categories are structural money-losers: Tables (-$17.7k), Bookcases (-$3.5k), and Supplies (-$1.2k), hidden inside a Furniture category that looks acceptable in aggregate.
The discount cliff is sharp: lines discounted 0-20% remain profitable on average, but average line profit turns negative past 20%, and at 30%+ discounts 97.8% of lines lose money.
17.3% of all order lines are sold at a loss, carrying an average 33.5% discount vs 6.5% on profitable lines.
Technology delivers a 17.4% margin vs Furniture's 2.5%, yet both receive similar discounting behavior, meaning discounts are set by habit, not by margin headroom.
Loss lines skew toward one-off customers, not high-lifetime-value accounts. The losses are leakage, not strategic relationship pricing.
Capping discounts at 20% (with a conservative elasticity haircut) models out to roughly $125k of recoverable margin, about 44% of current total profit.
Recommendations
Hard 20% discount ceiling without approval
The cliff is empirical: past 20%, average line profit is negative. Exceptions above the line should require a margin-aware sign-off.
Fix or exit Tables
A persistent -$17.7k sub-category needs repricing, renegotiated freight and supplier costs, or a managed exit. Continuing unchanged is a decision, not a default.
Regional discount audit for Central
Central's Furniture losses point to localized discounting practice. Compare rep-level discount distributions against other regions.
Margin-weighted discount guidance
Give Technology (17.4% margin) more promotional headroom than Furniture (2.5%). One blanket discount policy across both is exactly how the erosion happened.