A retail business generated ~$13M in revenue but only ~$1M in profit, indicating significant margin leakage.
This project investigates the drivers of profitability erosion and evaluates pricing policy changes that could improve margins.
Key findings show that aggressive discounting is the primary driver of margin loss, with discounts above 20% generating negative profit margins. Simulation analysis suggests that reducing deep discounts could increase profit significantly if demand remains stable.
The analysis combines SQL, Python, statistical modeling, and Power BI to identify profit drivers and simulate policy scenarios.
Despite strong revenue performance, the company’s profitability remained low. The key analytical questions were:
- Which factors are driving low profitability?
- How do discount policies affect profit margins?
- Which product categories contribute most to losses?
- What pricing strategy could improve profitability?
Total Shipping Cost ≈ $1M, nearly equal to total profit.
This indicates that logistics costs play a major role in margin erosion.
Orders with discounts above 20% generate negative margins.
| Discount Bucket | Avg Margin |
|---|---|
| No Discount | ~26% |
| Low (0–10%) | ~18% |
| Medium (10–20%) | ~15% |
| High (>20%) | -29% |
Furniture category drives most losses, while Technology and Office Supplies remain profitable.
A simulation model tested profit sensitivity to discount reduction.
If deep discounts are limited:
Assumption: customer demand remains constant.
A Pearson correlation test was used to evaluate the relationship between discount levels and profit margins.
Results:
- Correlation coefficient ≈ -0.63
- P-value < 0.001
This indicates a strong and statistically significant negative relationship between discount levels and profitability.
In practical terms, higher discounts are strongly associated with lower profit margins.
A linear regression model was used to quantify the impact of discount levels and shipping costs on profit margins.
Model:
Key findings:
- Discount levels have a strong negative effect on profit margins.
- Shipping costs have a smaller impact compared to discounting.
- The model confirms that aggressive discounting is the primary driver of margin erosion.
Note: Regression identifies statistical relationships but does not prove causation.
The dashboard enables decision-makers to explore:
- Profit drivers
- Discount behavior
- Category profitability
- Pricing policy scenarios
- Profit sensitivity to discount changes
High discounting (>20%) produces negative profit margins.
Furniture category contributes disproportionately to losses.
Reducing deep discounts significantly increases profit.
- Python
- Pandas
- SQL (SQLite)
- Statsmodels
- Scipy
- Power BI
- Jupyter Notebook
This notebook performs the core analytical workflow:
- Loading the retail dataset
- SQL-based aggregation using SQLite
- Order-level financial feature engineering
- Discount bucket segmentation
- Category-level profitability analysis
- Statistical testing
- Regression modeling
- Dataset preparation for dashboard visualization
Limit discounts above 20%, particularly in loss-making categories.
A pricing discipline policy could significantly improve profitability without requiring revenue growth.
- Data Cleaning
- SQL Analytics
- Feature Engineering
- Profitability Modeling
- Statistical Testing
- Regression Modeling
- Scenario Simulation
- Business Intelligence Dashboarding
- Data Storytelling