Audited joins, duplicate keys, status logic and pre-computed KPIs
VERIFIED CASE STUDY · E-COMMERCE ANALYTICS
Revenue Leakage & Customer Retention
An end-to-end SQL, Python and Power BI audit of 138,116 online-retail orders, tracing booked revenue to collected cash and identifying discount, delivery and retention risks.
SQLPythonPower BIDAX
01 · CONTEXT
From a broad concern to testable questions.
Business problem
Where is booked revenue leaking before it becomes cash, and which pricing, operational and retention actions should leadership prioritise?
Questions explored
- How much booked revenue is actually collected?
- Are deep discounts producing profitable sales?
- Which customer groups are lapsing, and how much annual revenue is at risk?
- Can the source data and pre-computed KPIs be trusted?
Dataset
Synthetic multi-country e-commerce dataset: 138,116 orders, 397,569 order lines, 25,000 customer records and 1,175 products from January 2021 to December 2025.
02 · ANALYSIS
A transparent path from preparation to interpretation.
Rebuilt revenue, profit and customer metrics from transaction-level data
Tested discount, delivery and retention patterns in SQL and Python
Designed a four-page Power BI report on a star schema with decision-focused recommendations
03 · DASHBOARD
POWER BI REPORT GALLERY4 decision views and the data model
Select any view to inspect the dashboard at full size.
04 · FINDINGS
What the analysis surfaced
01Only $129.9M of $177.1M booked revenue was collected; $14.3M sits in completed-but-unpaid orders and a further $32.9M is cancelled, returned or pending.
02Every one of the 1,229 loss-making completed orders had a discount above 35%; margin fell from 50.4% at 0–5% discount to 18.4% above 35%.
03New customer acquisition fell 97% from 14,913 completed-order customers in 2021 to 399 in 2025, leaving the business dependent on existing buyers.
04A total of 4,237 high-value lapsed customers represent $12.2M in annual revenue at risk.
Recommended next moves
01Fix order-to-cash controls: investigate $14.3M of unpaid completed orders and resolve 1,997 paid-but-pending orders.
02Cap discounts at 30% and require approval above the threshold; the model estimates a $1.44M five-year profit uplift under stated assumptions.
03Launch a September–October win-back campaign for high-value At Risk and Cannot Lose Them customers before the Q4 peak.
04Reconcile order headers with line items, rebuild customer metrics from transactions and collect marketing-spend data before calculating ROI.
LIMITATIONS
What the analysis does not establish.
- The dataset is synthetic, so uniform warehouse, campaign and return patterns may not generalise to a real retailer.
- Impact estimates are directional and depend on stated assumptions; a live rollout should be tested experimentally.
- 8.1% of orders do not reconcile with their line items, so order-level and category-level analysis use different source grains.
- The apparent fall in acquisition is partly structural because the customer population is fixed and sign-up dates are unavailable.
NEXT CASE STUDY
Continue exploring