A full end-to-end data analytics project analyzing 100,000+ real e-commerce orders from Olist, Brazil's largest e-commerce platform. This project simulates the work of a data analyst at Amazon, Google, or Microsoft — from raw data to executive dashboard.
Business Problem: An e-commerce company is losing revenue and doesn't know why. Using data, we identify the root causes and provide actionable recommendations.
👉 View Interactive Tableau Dashboard
| Metric | Value |
|---|---|
| Total Orders Analyzed | 96,470+ |
| Total Revenue | R$ 19.7 Million |
| Overall Churn Rate | ~68% |
| Average Review Score | 4.09 / 5 |
| Late Delivery Rate | ~10-24% by state |
| Top Category | Bed Bath Table |
ecommerce-intelligence-platform/ │ ├── data/ │ ├── raw/ ← Original Olist dataset (9 CSV files) │ ├── processed/ ← Cleaned & engineered datasets │ └── final/ ← Final Tableau-ready dataset │ ├── notebooks/ │ ├── 01_data_exploration.ipynb │ ├── 02_data_cleaning.ipynb │ ├── 03_sql_analysis.ipynb │ ├── 04_rfm_segmentation.ipynb │ ├── 05_churn_analysis.ipynb │ └── 06_tableau_prep.ipynb │ ├── dashboard/ ← Tableau workbook ├── reports/ ← Charts & findings └── README.md
- Loaded and explored all 9 datasets
- Analyzed 100K+ orders across customers, products, sellers
- Identified key patterns in order volume and status distribution
- Fixed missing values, data types, duplicates
- Engineered new features: delivery days, is_late, days_diff
- Built master dataset merging all 9 tables
- Answered 7 key business questions using SQLite
- Monthly revenue trend, top categories, late delivery by state
- Customer repeat rate, review score impact, seller performance
- Built RFM model (Recency, Frequency, Monetary)
- Segmented 93,000+ customers into 9 behavioral groups
- Identified Champions, At Risk, Lost and more
- Defined churn as no purchase in 180 days
- Identified 3 root causes: late deliveries, low reviews, low spend
- Used statistical hypothesis testing (t-test) to validate findings
- Built 6-sheet interactive executive dashboard
- Revenue trends, category performance, delivery heatmap
- Customer segmentation and churn overview
| Tool | Purpose |
|---|---|
| Python 3.11 | Data cleaning, analysis, modeling |
| Pandas & NumPy | Data manipulation |
| Matplotlib & Seaborn | Data visualization |
| Scikit-learn | RFM scoring |
| SQLite + SQL | Business queries |
| Tableau Public | Executive dashboard |
| Git & GitHub | Version control |
- Improve last-mile delivery in AL, MA, SE states which have 20%+ late rates
- Immediately follow up with customers who give 1-2 star reviews
- Create loyalty programs targeting low-spend first-time customers
- Reactivation campaigns for the 15,000+ At Risk customers
- Double down on Bed Bath Table and Health Beauty categories
Source: Brazilian E-Commerce (Olist) — Kaggle
Size: 100,000+ orders | 9 tables | 2016–2018
Shivansh Pandey
Aspiring Data Analyst | Python • SQL • Tableau
GitHub | Tableau Public