Capstone 4 — E-commerce Business Dashboard¶
A self-directed capstone building a comprehensive analytics layer for an online retail business. The most technically demanding capstone — it involves multiple related tables and the full customer journey.
The Brief¶
An e-commerce business has rich data trapped across multiple tables — orders, customers, products, payments, reviews — that nobody has connected. Build the analytics layer: revenue, customer LTV, delivery/satisfaction, and a dashboard leadership can use.
Tools¶
SQL · Python (Pandas) · Tableau/Power BI
Difficulty¶
Intermediate-Advanced — multi-table joins, customer-level aggregation, the most realistic data work.
What You Must Deliver¶
- A joined, cleaned dataset across the order journey
- Revenue, customer, delivery, and satisfaction analysis
- Customer LTV and repeat-rate calculation
- A relationship analysis (e.g., delivery speed vs review score)
- A multi-page dashboard
- Recommendations to improve retention/satisfaction
Suggested Datasets¶
- Brazilian E-Commerce (Olist) — 8 related tables, the ideal choice
- Any multi-table e-commerce dataset
Business Questions¶
- What is total revenue and how does it trend?
- What is the repeat purchase rate and customer LTV?
- Which categories drive revenue and which have poor reviews?
- Is there a relationship between delivery time and satisfaction?
- Which customer segments are most valuable (RFM)?
The Key Technical Challenges¶
Two traps to handle
- Customer ID granularity — if the dataset assigns a new ID per order (like Olist), you MUST use the true customer identifier for repeat/LTV analysis. See Projects/E-commerce-Analytics/dataset-guide.
- Row multiplication on joins — aggregate the "many" side (order items) before joining to avoid double-counting. See Projects/E-commerce-Analytics/data-cleaning.
Requirements Checklist¶
- [ ] Multiple tables joined correctly (verified no row multiplication)
- [ ] Customer-level metrics using the correct customer ID
- [ ] Repeat rate and LTV calculated
- [ ] A relationship analysis with interpretation
- [ ] Multi-page dashboard
- [ ] Recommendations
- [ ] Portfolio README
Evaluation Criteria¶
| Criterion | Weight |
|---|---|
| Correct multi-table joins | 25% |
| Customer-level analysis (LTV, repeat) | 20% |
| Relationship/satisfaction analysis | 20% |
| Dashboard design | 20% |
| Recommendations | 15% |
Stretch Goals¶
- Cohort retention analysis
- Seller/vendor performance scorecard
- Geographic delivery bottleneck analysis
- A churn or repeat-purchase prediction model