E-commerce Sales EDA in SQL: Finding Where Revenue Leaks by Samuel MainaE-commerce Sales EDA in SQL: Finding Where Revenue Leaks by Samuel Maina

E-commerce Sales EDA in SQL: Finding Where Revenue Leaks

Portfolio project
Samuel Maina
The Problem I get lots of orders, but I'm not keeping all the money. Where am I losing it?" Every query answers a piece of that question.
WHAT I DID I loaded the cleaned dataset into a MySQL table and ran exploratory queries in order, each one leading to the next.
The big picture: total rows, distinct orders (120,378), date range and total revenue.
A check on my own assumption: do the blank amounts really belong to cancelled orders?
Order outcomes: the share of order lines by status (delivered, cancelled, returned, lost and so on).
Revenue leakage: I grouped 13 status values into four buckets (Fulfilled / In progress, Cancelled, Returned / Rejected, Lost / Damaged) and totaled revenue for each.
Where it leaks: cancellation rates by category, fulfilment type (Amazon vs Merchant) and state, with a minimum order count so tiny states don't distort the ranking.
What's working: revenue by category and by city.
Over time: monthly orders, revenue and cancellations.
SQL USED Aggregations, GROUP BY, CASE WHEN for grouping statuses, window functions for percentage shares, HAVING filters, DISTINCT counts and date formatting.
WHAT THE ANALYSIS SHOWED
14.2% of order lines were cancelled, and total leakage (cancelled, returned, lost or damaged) was 15.9%.
Merchant-fulfilled orders were cancelled more often than Amazon-fulfilled ones.
"Set" was the top revenue category and also had the highest cancellation rate.
LIMITATION Cancelled orders with no recorded amount were set to zero, so revenue lost to cancellations is a minimum, not an exact figure.
TOOLS MySQL, DBeaver, SQL

1 like · 0 views · Nov 22, 2026

This project is an exploratory SQL analysis of 128,975 Amazon order lines, framed as a client question: where is the seller losing money? Using MySQL, I measure

Tags