Project 5 — E-commerce Analytics¶
Analyse a real multi-table e-commerce database — orders, customers, products, payments, reviews — and answer the questions that drive an online retail business.
Overview¶
This is the most realistic database project in the course. Unlike single-CSV projects, e-commerce data is genuinely relational: orders link to customers, products, payments, and reviews across multiple tables. This project teaches you to navigate that complexity, write multi-table joins, and answer business questions that span the whole customer journey — from first visit to repeat purchase.
Business Objective¶
Build an analytics layer for an e-commerce business that surfaces customer lifetime value, product and category performance, delivery efficiency, and the drivers of customer satisfaction — enabling data-driven decisions on inventory, marketing, and operations.
Tools and Skills¶
| Tool | Used for |
|---|---|
| SQL | Multi-table joins, cohort analysis, RFM segmentation |
| Python (Pandas) | Cross-table merging, feature engineering |
| Tableau | Interactive multi-page analytics dashboard |
Difficulty¶
Intermediate-Advanced — multiple related tables require careful joins. This is the project that most resembles a real analyst's day-to-day database work.
Dataset¶
- Source: Brazilian E-Commerce Public Dataset by Olist
- Download: Kaggle Olist Dataset
- Tables: orders, order_items, customers, products, sellers, payments, reviews, geolocation (8 files)
- Size: ~100k orders, realistic and clean
- Schema details: dataset-guide
Project Files¶
| File | Description |
|---|---|
| business-problem | Business context and questions |
| dataset-guide | Multi-table schema and relationships |
| data-cleaning | Joining and cleaning across tables |
| sql-analysis | Multi-table SQL analysis |
| dashboard-design | Tableau dashboard design |
| insights-and-recommendations | Findings and recommendations |
| interview-questions | Project interview questions |
Learning Outcomes¶
- Navigate and join a realistic relational database
- Calculate customer lifetime value across multiple tables
- Build RFM customer segments
- Analyse the relationship between delivery time and review scores
- Perform cohort retention analysis
- Build a multi-page analytics dashboard