An end-to-end data analytics project analysing ~95,000 orders placed on the Olist Brazilian e-commerce marketplace between 2016 and 2018. The project moves from raw data through cleaning, SQL analysis, and Python-based customer segmentation, to a stakeholder-ready Power BI dashboard with concrete business recommendations.
Three overarching strategic questions drive the analysis:
- Which regions should receive more commercial investment?
- Which customer groups should be targeted for retention campaigns?
- Which operational areas require the most urgent improvement?
These are broken down into 10 analytical questions covering commercial performance, customer insights, and operational efficiency.
Source: Olist Brazilian E-Commerce Public Dataset (Kaggle)
The dataset covers ~95,000 orders across 9 linked CSV files, including order records, line items, payments, reviews, customer and seller locations, and product metadata.
| Tool | Role |
|---|---|
| DuckDB | SQL querying and aggregation directly on CSV files |
| Python / Pandas | RFM customer segmentation |
| Jupyter Notebooks | Interactive analysis environment |
| PostgreSQL | Persistent storage for cleaned data and segmentation outputs |
| Power BI Desktop | Dashboard and visual reporting |
SQL skills exercised: CTEs, joins, aggregations, GROUP BY, FILTER, DISTINCT, views.
ecommerce_sales_and_customer_insights/
│
├── data/ # Raw CSV files (Olist dataset)
├── docs/ # Project documentation and findings
├── images/ # Dashboard screenshots
├── notebooks/ # Jupyter notebooks (EDA, SQL analysis, segmentation)
├── powerbi/ # Power BI .pbix file
├── sql/ # DuckDB SQL query files
└── README.md
| Phase | Description |
|---|---|
| Phase 1 | Data understanding: schema inspection, row counts, data quality checks |
| Phase 2 | Data cleaning: deduplication, nulls, date standardisation, clean views |
| Phase 3 | SQL analytics: 10 business questions answered across 3 domains |
| Phase 3b | Customer segmentation: RFM quintile model, 6 segments exported to Postgres |
| Phase 3c | NLP sentiment analysis: BERTimbau embeddings and k-means clustering on Portuguese office furniture reviews |
| Phase 4 | Power BI dashboard: 4-page stakeholder-ready report |
| Phase 5 | Findings and recommendations: business write-up |
4-page Power BI report built on cleaned Postgres views and RFM segmentation outputs.
| Page | Content |
|---|---|
| Page 1: Executive Summary | Revenue, orders, customers, AOV, monthly trend |
| Page 2: Regional Performance | Revenue by state, delivery time, review scores by region |
| Page 3: Customer Insights | RFM segment distribution, revenue by category, customer spend distribution, repeat vs one-time buyers |
| Page 4: Operational Performance | Late delivery vs review score scatter, late delivery % over time, late delivery % by category, avg review score by category |
- Revenue concentration: SP, RJ and MG account for 63.5% of total revenue; São Paulo alone contributes 38.4%
- Growth plateau: rapid growth through 2017 (peaking November 2017) levelled off to ~6,000–6,900 orders/month through 2018
- Late delivery drives poor reviews: 37.78% of 1-star reviews involved a late order, vs. 3.0% of 5-star reviews (~12x difference)
- Repeat purchasing is rare: only 2.98% of customers placed more than one order; this is consistent across all states and is a structural platform pattern, not a delivery problem
- Seller quality: a small cluster of sellers has complaint rates above 55%, identifiable and actionable from existing review data
- Office furniture: 21% complaint rate across 1,687 reviews, with an average score of 3.62 against a platform average of 4.04. NLP analysis (Phase 3c) identified two root causes: fulfilment failures (incomplete or partial orders) and structural product defects, with poor seller responsiveness as a cross-cutting aggravator
- Focus commercial investment on mid-tier states (BA, PE, CE, GO): R$1.23M in combined revenue with addressable delivery times of 15–21 days. Improving reliability here lifts review scores and strengthens the acquisition pipeline
- Treat customer retention as a precision problem: use RFM segmentation to target At Risk customers (24% of the base) for re-engagement and Champions/Loyal customers (22%) for retention incentives; broad campaigns across 92,000 customers are unlikely to be cost-effective
- Introduce a seller quality threshold: flag and review any seller with a complaint rate above a defined threshold past a minimum review count
- Investigate office furniture: the complaint rate and sample size both rule out noise; sentiment analysis of review comment text is the recommended next step to identify root causes
- Phase 3d: Multivariate regression to test whether the delivery-to-review relationship holds after controlling for product category, seller, price, and customer state