Performance Snapshot
Key Metrics at a Glance
Total Revenue
$14.9M
2009–2012 combined
Total Profit
$1.52M
10.2% overall margin
Loss-Making Orders
50.4%
4,232 of 8,399 order lines
Furniture Margin
2.3%
on $5.18M in sales
Technology Margin
14.8%
highest category margin
Discount Break-Even
20%
above this, avg profit turns negative
Analysis
What the Data Reveals
Revenue Risk
50.4% of Orders Lose Money — The Biggest Operational Risk
More than half of all 8,399 order lines in this dataset are loss-making. Furniture has the highest loss rate at 53.5% — meaning more than 1 in 2 Furniture orders actively destroys margin. This is not an edge case; it is the dominant pattern across the dataset.
01
Margin Alert
Furniture Earns 2.3% Margin on $5.18M in Sales
Furniture is the second-highest revenue category but delivers only 2.3% profit margin — the weakest of all three categories. Technology earns 14.8% on $5.98M and Office Supplies earns 13.8% on $3.75M. Furniture's revenue disguises a near-zero return on every dollar sold.
02
Discount Impact
Discounts Above 20% Flip Average Profit Negative
Orders with no discount average $249 profit. At the 21–30% discount band, average order profit drops to –$249. The break-even point sits between 11–20%. Deep discounting is not a sales lever — it is a systematic profit drain operating across hundreds of orders.
03
Revenue Trend
2009's $4.21M Revenue Peak Has Not Been Recovered
Revenue declined 15.7% from 2009 to 2011 before a partial recovery of +8.2% in 2012. The business generated $3.72M in 2012 — still $496K below the 2009 peak. Three years on, the top line has not returned to its starting point.
04
Seasonal Pattern
December and January Are Peak Months — May–August Is the Low Season
December accounts for 9.87% of annual sales and January 9.66% — the two strongest months. April through June are the weakest at roughly 6% each. Q4 consistently outperforms Q2 and Q3 across all four years in the dataset.
05
Regional Performance
West Leads Revenue but Ontario Leads Profit Margin
The West region generates the most revenue at $3.6M but delivers only an 8.26% margin — below the fleet average. Ontario earns 11.32% margin on $3.06M. Nunavut is the weakest market at 2.44% margin on $117K — the lowest priority region in the portfolio.
06
Critical Finding
The 50.4% loss-order rate is the single most important number in this dataset. It means the business's profitability is being sustained by roughly half its orders while the other half actively erode it. Addressing the discount strategy and Furniture category pricing should be the first priority for any margin improvement initiative.
Business Questions
10 Self-Generated Questions & Answers
BQ-01
How has total revenue trended year-over-year?
Revenue peaked at $4.21M in 2009 and declined for two consecutive years, dropping 15.7% by 2011. A partial recovery of +8.2% in 2012 still left the business $496K below its 2009 peak.
BQ-02
Which product category generates the highest profit margin?
Technology leads at 14.8% ($886K profit). Office Supplies earns 13.8%. Furniture earns only 2.3% despite generating $5.18M in revenue.
BQ-03
Which sub-categories are destroying profit (bottom 5)?
Tables lost –$99.1K on $1.77M in sales (–5.6% margin). Four of the five worst sub-categories are in Furniture — Bookcases, Office Furnishings, and Scissors round out the list.
BQ-04
Does a higher discount always lead to lower profit?
Yes. Orders with 0% discount average $249 profit. At 21–30% discount, average profit is –$249. The profit break-even sits between 11–20% discount.
BQ-05
Which shipping mode is most cost-efficient?
Regular Air handles 88% of orders at only 0.45% shipping cost as a percentage of sales — the most efficient mode. Delivery Truck costs 1.62% of sales per order despite carrying fewer shipments.
BQ-06
Which customer segment is most profitable?
Corporate generates the most total profit at $600K. Home Office leads on margin at 13.2%. Small Business delivers the highest average order value at $4,611 — the most efficient segment per transaction.
BQ-07
Which regions underperform on margin relative to sales?
West leads in revenue ($3.6M) but earns only 8.26% margin — below average. Ontario achieves 11.32% margin. Nunavut is the worst market at 2.44% margin on $117K in sales.
BQ-08
What share of orders result in a net loss, by category?
50.4% of all 8,399 orders lose money. Furniture has the highest loss rate at 53.5% and the largest total loss at –$435K — the single biggest operational risk in the dataset.
BQ-09
Is there a seasonal sales pattern by month?
December (9.87% of annual sales) and January (9.66%) are the two peak months. April through June are consistently the weakest at ~6% each. Q4 outperforms Q2 and Q3 across all four years.
BQ-10
Do returned orders follow a pattern by category or region?
Technology in Ontario has the highest return rate at 13.78%, followed by Furniture in Yukon at 13.64%. Furniture's combination of high returns, 2.3% margin, and 53.5% loss rate makes it the highest-risk category overall.
Business Insights
Structured Analytical Insights
01
Margin Strategy
Furniture Is a Revenue Trap — High Sales, Near-Zero Return
Furniture generates $5.18M in revenue — the second-highest category — but converts this into only $117K in profit at a 2.3% margin. Combined with a 53.5% loss-order rate and a high return rate, the category is absorbing disproportionate operational cost without delivering commensurate return. A full pricing and cost review of Furniture sub-categories — particularly Tables, which lost $99K — is the single highest-leverage intervention available.
02
Discount Policy
The Discount Strategy Is Destroying Value Above 20%
The data shows a clear and consistent relationship: as discounts increase beyond 20%, average order profit turns negative. Orders with no discount average $249 profit; orders at 21–30% average –$249. This is not a volume-offset trade-off — total profit in the deep-discount bands is also negative. A policy cap at 20% discount — with exceptions requiring sign-off — would be the most direct lever to improve overall profitability.
03
Portfolio Prioritisation
Double Down on Technology — It Earns 14.8% Margin at Scale
Technology generates $886K in profit on $5.98M in sales at a 14.8% margin — the strongest category by every measure. It also has the lowest loss-order rate among all three categories. Resourcing, promotional spend, and sales team focus should be weighted toward Technology and high-performing Office Supplies sub-categories, while Furniture volume is allowed to contract in favour of quality over quantity.
04
Seasonal Planning
Inventory and Promotions Should Be Timed Around the Dec–Jan Peak
December and January consistently account for nearly 20% of annual sales combined across all four years. The May–August low season accounts for a similar proportion but at significantly lower average order values. Promotional spend and inventory build-up should be front-loaded ahead of December, while the low season offers an opportunity for targeted retention campaigns to smooth revenue volatility.
Methodology
End-to-End Analytical Pipeline
Phase 0
Database Setup
Created the
superstore MySQL database with a normalised 4-table schema: orders, customers, returns, regional_managers. Five indexes applied on orders for query performance.Phase 1
Python ETL Pipeline
Python/SQLAlchemy ETL pipeline reads all sheets from the Excel source, standardises column names, applies schema alignment transformations, and loads 8,399 orders + 5,496 customers + 572 returns into MySQL.
Phase 2
Exploration & Cleaning
Inspected dtypes, null distribution, and unique values. Engineered year, month, discount bucket, and is_loss columns. Documented 63 null values in product_base_margin as the only data quality gap.
Phase 3
10 Business Questions
Self-generated 10 business questions covering revenue trend, margin analysis, discount impact, shipping efficiency, segment profitability, regional performance, loss-order rate, seasonality, and returns patterns.
Phase 4
SQL Analysis
Each question answered with production-quality SQL: CASE bucketing, LEFT JOINs for returns, ROUND and pct calculations, and documented RATIONALE + ANSWER comments per query.
Phase 5
Python Visualisation
10 Matplotlib/Seaborn charts — dual-axis bar charts, horizontal bar charts, annotated margin comparisons, and a seasonal line chart with fill. Each chart paired with a plain-language key finding.
Full Project Repository
Superstore Database — GitHub
Repository contains the MySQL schema SQL file, the Jupyter Notebook with all executed outputs and charts, and the outputs folder with 10 exported chart PNGs.
View Repository →