E-Commerce Delivery & Retention Analysis
Cross-validated 100K+ orders in Python and SQL to trace a 3.12% repeat-purchase rate back to a cause someone can actually fix.
Review score by delivery outcome
Scale 0–5
- Delivered on time4.29
- Delivered 4+ days late1.85
- 3.12%
- Repeat-purchase rateThe retention problem being traced
- 67.6%
- Revenue from the top 10% of sellers310 of 3,095 sellers
- 3.5x
- Late-delivery rate, worst statesAgainst the national rate
- Role
- Solo project / independent build
- Stack
- Excel, Python (pandas), PostgreSQL, SQL, Power BI, DAX
- Scope
- 100K+ orders · 9 relational tables · 3,095 sellers
100K+
Orders modelled across 9 tables
3.12%
Repeat-purchase rate
67.6%
Revenue from the top 10% of sellers
15+
DAX measures, each cross-validated
How it came together.
- 01
The model
This one started as a modelling problem before it was ever an analysis problem. I modelled 100K+ orders across 9 relational tables in PostgreSQL, because the questions worth asking here all live across table boundaries rather than inside any single one of them: does delivery affect whether someone comes back, and where is the revenue actually concentrated.
- 02
Checking my own work
Then I did the part I now do by reflex: I cross-validated six analyses in Python and SQL, running each one twice by two different routes and comparing the answers. That caught two methodology errors, a revenue-denominator bug and a naive freight-ratio calculation, and it caught them before either reached a dashboard. Both would have looked completely plausible on a chart, which is exactly the problem with them.
- 03
The finding
The headline number was a 3.12% repeat-purchase rate, which is low, and low numbers are only useful once you can say what to do about them. So I traced it to a specific, addressable cause: orders delivered 4+ days late scored 1.85/5, against 4.29/5 for on-time orders. Those delays weren't spread evenly either. They concentrated in specific states, at up to 3.5x the national rate, which turns a vague retention problem into a shortlist of places to fix.
- 04
The concentration risk
The second finding was one the raw category data hides completely: the top 10% of sellers (310 of 3,095) drive 67.6% of revenue. That is a concentration risk you cannot see by looking at categories, only by looking at the distribution. I surfaced all of it through a 3-page Power BI dashboard carrying 15+ DAX measures, and validated every one of those measures against Python and SQL before it went on a page.
Have a similar problem to solve?Let's talk it through.
I'm looking for a data analyst role in Sydney, and I have full working rights here. If something here looks like the work you need doing, get in touch.
Open to opportunitiesSydney, Australia