Case studies / Discount profitability and margin erosion

Discounting costs $13.8M: a standing practice, not a targeted tool

Retail Discount Profitability Audit. Synthetic dataset (Microsoft Contoso): 223,974 order lines across 93,470 orders (2016 to 2025). PostgreSQL, Python, and Power BI.

By Zeeshan Akram, founder of MarginLens.

The question

Discounting is one of the most common pricing levers and one of the least measured. This audit asks whether discounting is a controlled pricing mechanism or a standing practice, what it costs in dollars, and where its cost sits across categories, customers, locations, and time.

The numbers

• Order lines sold below list priceObserved in the data61%
• Total discount cost6.3% of revenue, 11.3% of profit$13.8M
• Break-even share for discounted salesRequired volume to retain1 in 6

Observed figures are measured directly in the dataset. Modeled figures are what-ifs built from it, not savings or forecasts.

Findings

• 61.1% of order lines (136,913 of 223,974) are sold below list price, remaining steady between 60.9% and 61.9% in every year from 2016 to 2025.
• Discounts total $13,787,101 (about $101 per discounted line), representing 6.3% of revenue and 11.3% of profit.
• Blended margin falls from 58.4% at full price to 53.1% in the 10% to 20% discount tier, matching precisely what a price cut alone predicts with unchanged costs.
• Every product category and sales location is discounted the same way; category share of discount dollars follows category revenue size.
• Among customers with 10 or more order lines, discount rates cluster tightly around the 61% average, showing that heavy buyers are not a separate discount-dependent group.
• The break-even test shows that discounts pay for themselves only if removing them would lose more than 16.5% (about 1 in 6) of discounted sales.

What the audit concluded

RecommendationDo not change pricing policy yet. Put a budget, an owner, and reason codes on discounts, then test a lower discount cap on the 10% to 20% tier.

Methodology

• Grain: one row per product line within an order, across seven related tables (order rows, orders, customers, products, stores, dates, and currency rates).
• Margin basis: blended margin calculated as `SUM(profit) / SUM(revenue)` at order-line level.
• Sequence: staged SQL files answering baseline performance, discount impact, tier depth, category drivers, customer behavior, trends over time, and store behavior.
• Validation: Python checks and a Power BI executive dashboard built around audit conclusions rather than exploratory slicing.
• Tiers: discount depth bands set at under 5%, 5% to 10%, and 10% to 20% for pattern detection.

Limitations

• The dataset is synthetic (Microsoft Contoso), reflecting generated patterns rather than real-world commercial behavior.
• There is no demand data or sales history from before discounts were introduced, so the exact revenue contribution of discounting cannot be measured directly.
• Margin is line-level only; overheads, returns, shipping, and marketing costs are excluded.
• Currency conversion was not required as list prices share a common currency base across orders.

Report and source files

• Read the executive report (PDF)
• Code, queries, and dashboard (GitHub)

Other case studies

• Product profit concentration and SKU risk
• Customer lifetime value and retention
• E-commerce delivery risk and churn