Discount & Profitability Study
Discounts can make revenue look healthy while profit drains away. Where exactly does that start?
Public dataset. Two earlier repositories are merged into this one case study.
Problem
Problem
Discounting lifts revenue, so a business can look like it's growing while its margins erode. The commercial question is specific: at what discount level does an order stop making money, and which parts of the catalogue carry the losses?
Constraints
Constraints
- Public data
- The Sample Superstore retail dataset, with profit given rather than built up from costs.
- Observational
- Historical orders, not an experiment, so this shows association rather than cause.
- Two overlapping studies
- Two earlier repositories asked nearly the same question; they are merged here and their numbers reconciled.
Architecture
Architecture
Public datasetretail order lines
Input The Sample Superstore dataset: US retail order lines with sales, discount and profit.
Clean · derivepandas → SQLite
Deterministic step Column clean-up, profit margin derived, loaded into SQLite for querying.
SQL questions7 queries
Deterministic step Loss-making products, discount buckets, category thresholds, revenue from loss-making lines.
Sensitivityprofit per level
Deterministic step Mean profit and margin at every discrete discount level, overall and per category.
DashboardTableau
Stored data Findings summarised in one Tableau dashboard.
- Input
- Deterministic step
- Stored data
The analysis as built: SQL for the questions, pandas for sensitivity, Tableau for communication.
Key decisions
Key decisions
Revisited: a decision I changed or corrected after the evidence moved.
Exact discount levels, not buckets
The dataset uses discrete discount values, so profit is reported at each one. The disagreement disappears: average profit is still positive at 20% and negative from 30%. The 20–30% bucket simply contained the 30% orders.
Show reasoning
- Context
- The two earlier write-ups disagreed: one said losses start “beyond 20–30%”, the other “around 30%”.
- Options considered
- Keep the bucket chart · report per discrete discount level
- Trade-off accepted
- Some levels have few orders, so their averages are noisy.
State the denominator
The figure reported here is a share of revenue: about 20% of sales came from order lines with negative profit. The 35% figure could not be traced to any saved analysis, so it is not used.
Show reasoning
- Context
- “About 20% loss-making” and “about 35% loss-making” had both been quoted in earlier materials.
- Options considered
- Pick the more striking number · name what's being counted
- Trade-off accepted
- A less dramatic headline, but a defensible one.
No invented upside
That scenario rested on an assumed rule (halving profit on lines discounted above 20%), not a model of customer behaviour, so it is not reported here.
Show reasoning
- Context
- An earlier version reported a “~25% simulated profit improvement”.
- Options considered
- Keep it, labelled simulated · drop it
- Trade-off accepted
- No headline improvement figure.
Implementation
Implementation
- pandas clean-up and derived margin, loaded into SQLite.
- Seven SQL queries: loss-making products, discount buckets, category thresholds and revenue from loss-making lines.
- A pandas sensitivity analysis of mean profit and margin per discount level, overall and per category.
- A Tableau dashboard summarising the findings.
Validation
Validation
- Cross-checked
- Every number on this page appears in a saved notebook output or the published dashboard.
- Reconciled
- Conflicting figures in earlier materials were traced to buckets and denominators, or dropped.
Results
Results
- 20% → 30%average order profit is positive at 20% discount and negative from 30%Dataset finding
- ≈20%of revenue came from loss-making order linesDataset finding
- Furnitureweakest category margin; average margin turns negative at 30% discountDataset finding
- Dataset finding Mean profit per order line: +24.7 at 20% discount and −45.7 at 30%, falling to −310.7 at 50%.
- Dataset finding About 20% of revenue (19.86%) came from loss-making order lines.
- Dataset finding Furniture has the weakest margin. Its average margin goes from +7.2% at 20% discount to −12.1% at 30%.
30% discount mean profit −45.7
Source: discount_analysis.ipynb, cell 2 output. Positive bars in blue, losses in red, signed values in the table.
Show the data as a table
| Discount | Mean profit per order line |
|---|---|
| 0% | +66.9 |
| 10% | +96.1 |
| 15% | +27.3 |
| 20% | +24.7 |
| 30% | −45.7 |
| 32% | −88.6 |
| 40% | −111.9 |
| 45% | −226.6 |
| 50% | −310.7 |
| 60% | −43.1 |
| 70% | −95.9 |
| 80% | −101.8 |
Limitations
Limitations
- Correlation, not causation: discounts may be applied to products that were already struggling.
- A single public sample dataset, not a real business's books.
- Sparse discount levels make some averages unstable.
Lessons
Lessons
- Bucket edges can manufacture disagreements. Look at the raw levels.
- A number without its denominator isn't a finding.
What I'd do differently
- Estimate the discount effect within each product, so product mix doesn't masquerade as a discount effect.
Where the numbers come from
- 20% → 30% average order profit is positive at 20% discount and negative from 30%. Mean profit per order line by discount level: +24.7 at 20%, −45.7 at 30%. Source: discount-profit-sensitivity-analyzer/notebooks/discount_analysis.ipynb (cell 2)
- ≈20% of revenue came from loss-making order lines. Share of total sales where line profit < 0 (19.86% in the Tableau dashboard). Source: ecommerce-profit-optimizer/reports/dashboard.png; notebooks/eda.ipynb (cell 16)
- Furniture weakest category margin; average margin turns negative at 30% discount. Furniture mean margin +7.2% at 20% discount, −12.1% at 30%. Source: discount_analysis.ipynb (cell 6)