E-commerce Revenue Leakage Analysis: Python, SQL & Power BI by Samuel MainaE-commerce Revenue Leakage Analysis: Python, SQL & Power BI by Samuel Maina

E-commerce Revenue Leakage Analysis: Python, SQL & Power BI

Portfolio project
Samuel Maina
Where is an e-commerce seller losing money?
THE PROBLEM A seller can have thousands of orders coming in and still not keep the revenue. Cancelled, returned, rejected and lost orders quietly drain income, and most sellers can't see how much is leaking, where it comes from, or what to fix first.
WHAT I DID I took a raw Amazon sales dataset of 128,975 order lines (120,378 distinct orders) and built a complete pipeline from messy data to decision-ready dashboard.
Data cleaning (Python / Pandas)
Handled missing values based on what each blank actually meant. A blank promotion means no promotion was used. A blank amount most likely means a cancelled order.
Standardized column names and fixed casing, typos and old names in categories, states and cities. For example, Bangalore and Bengaluru were being counted as two different cities, and the state list shrank from 48 to 37 unique values.
Converted dates to a proper date type and postal codes to text.
No rows were removed.
Analysis (MySQL)
Loaded the cleaned data into a MySQL database and ran queries on order outcomes, revenue leakage, and cancellation rates by category, fulfilment type and state, plus revenue by category and city.
Dashboard (Power BI)
Built an interactive dashboard with KPI cards (revenue, orders, cancellation rate, revenue leaked, leakage rate) and charts showing where the leakage comes from.
Added slicers for fulfilment type, category and date, plus an INR/USD currency switch with an adjustable exchange rate.
Findings
14.2% of order lines were cancelled. Counting returned, rejected and lost or damaged items, total leakage was 15.9%.
About $106,500 of $1.01M in order value belonged to leaked orders, roughly 10.6%.
Merchant-fulfilled orders were cancelled more often than Amazon-fulfilled ones.
"Set" was both the top revenue category and the most cancelled, so the biggest earner is also the biggest exposure.
Bengaluru and Hyderabad were the top revenue cities.
RECOMMENDATION Investigate merchant-fulfilment cancellations first, then the Set category.
LIMITATIONS
The data covers March to June 2022, and March is a partial month.
Original amounts are in rupees. Dollar figures use a fixed rate of 78 INR per USD.
Cancelled orders with no recorded amount were set to zero, so the revenue lost to cancellations is a minimum, not an exact figure.
TOOLS Python, Pandas, MySQL, Power BI

1 like · 2 views · Nov 21, 2026

This project analyzes 120,378 Amazon orders to show an e-commerce seller where revenue is lost to cancelled, returned and lost orders. I cleaned the raw data in

Tags