Olist Brazilian E-Commerce Analytics
A complete end-to-end analytics build — from 9 fragmented raw CSV files to a 3-page interactive Power BI dashboard answering the questions a CEO would ask every week.
Dataset
Olist (Kaggle)
Records
100,000+ orders
Period
2016 – 2018
Tools
Power Query, MySQL, Power BI
$13.77M
Total revenue (2017–2018)
93.21%
On-time delivery rate
4.08/5.0
Avg review score
112K+
Rows in master fact table
The problem
Olist is Brazil's largest e-commerce marketplace, connecting thousands of small sellers to millions of customers across all 26 states. Between 2016 and 2018 the platform processed over 100,000 orders — but the raw data wasn't a single clean spreadsheet. It was 9 separate relational CSV files, each covering a different slice of the business: orders, customers, sellers, products, payments, reviews, and geography.
No single file could answer a business question on its own. Before this project, Olist management had no unified view of revenue, delivery performance, customer satisfaction, and geography simultaneously.
"Which states are delivering on time — and which states are destroying customer satisfaction through late deliveries? Is the platform growing? What's driving the negative reviews?"
Phase 1 — Data cleaning in Power Query
All 9 raw CSV files were loaded, cleaned individually, and merged into one master fact table. This was the most critical phase — bad data in means bad insights out.
- Filtered orders to delivered-only, removed rows with null delivery dates, and created delivery_days and delivery_status ("On Time" / "Late") derived columns
- Standardised customer_city and customer_state to uppercase — raw data had "sao paulo", "Sao Paulo", and "SAO PAULO" all meaning the same city
- Translated all product categories from Portuguese to English using a dedicated translation table
- Deduplicated reviews to one per order and created a review_sentiment column (Positive / Neutral / Negative)
- Kept only primary payment rows to avoid double-counting order totals from instalment splits
All 8 cleaned tables were merged using left outer joins on order_id, producing a Fact_Olist master table of approximately 112,000–115,000 rows — one row per product line within one delivered order.
9 raw relational tables merged into a single 112K-row Fact_Olist master table.
Phase 2 — SQL analysis in MySQL
With the clean fact table exported and imported into MySQL, 8 analytical queries plus a window function query answered specific business questions:
Revenue & growth
2018 revenue dramatically outpaced 2017 in every month. A LAG() window function measured exact month-over-month growth rates.
Delivery performance
São Paulo delivers in 8 days. Roraima averages 27 days. Alagoas has a 21.3% late delivery rate — a stark north-south logistics gap.
Customer sentiment
78.43% positive sentiment platform-wide. Negative reviews correlate directly with late deliveries — not product quality.
Phase 3 — the Power BI dashboard
Four SQL views (vw_fact_main, vw_monthly, vw_category, vw_state) were connected to Power BI so the dashboard refreshes automatically as source data changes. A dynamic Power Query date table and 10 custom DAX measures — including Revenue YoY % using SAMEPERIODLASTYEAR and a Running Revenue cumulative measure — powered a 3-page interactive dashboard.
Page 1 — Executive summary
Total Revenue, Total Orders, On-Time Delivery %, and Avg Review Score KPI cards give a CEO a health check in under 30 seconds. A monthly revenue trend line chart and cumulative revenue area chart make the 2018-over-2017 growth story immediately visible.
Page 2 — Geographic performance
A filled map of revenue by state, late delivery rate bar chart, and avg delivery days column chart expose the north-south logistics divide — São Paulo represents roughly 42% of all orders and delivers fastest, while five northern states average 25+ days.
Page 3 — Product & sentiment
A review score distribution reveals a bimodal pattern — 57K five-star reviews vs 9K one-star reviews, with little neutral middle ground. A price-vs-satisfaction scatter chart confirms there's no correlation between what customers pay and how happy they are — fulfilment quality matters more than price.
What the CEO takeaway looks like
The platform generated $13.77M in revenue across 2017–2018 with 93.21% on-time delivery and a 4.08/5.0 average review score — all healthy headline numbers. But 12.7% negative sentiment across 96,000+ orders means roughly 12,000 customers had a bad experience, and the data shows this is a logistics problem, not a product problem. States with the worst delivery times also have the lowest review scores.
The five highest-value decisions this dashboard enables: fix northern logistics partnerships, protect São Paulo operations from concentration risk (42% of all orders), audit the Bed & Bath category for seller quality, invest in flexible instalment payment products (75.31% of orders use credit card), and expand seller recruitment in high-satisfaction niche categories like Fashion Children's Clothes (5.0 avg review).
