From 100,000 Orders to Business Intelligence: How I Built an End-to-End Analytics System on Brazil’s Biggest E-Commerce Dataset

What if you could see every sale, every delivery failure, every unhappy customer, and turn that chaos into a strategy?

São Paulo at night

I’m a data analyst, and like many others, I’ve spent hours on toy datasets that feel clean, tidy, and fake. So when I found the Olist Brazilian E-Commerce dataset on Kaggle. 100,000 real anonymized orders spanning 2016 to 2018, across nine messy, relational CSV files, I knew this was the real thing.

This blog is the story of how I turned that raw data jungle into a production-style analytics system using SQL and Power BI, and what I actually learned about Brazilian e-commerce along the way.

The Dataset: A Messy, Beautiful Slice of Real Commerce

Nine datasets, one story to tell

Olist is Brazil’s largest department store marketplace, a platform that connects small businesses to customers across the country. Their dataset is a goldmine:

• 97,000+ orders placed across multiple Brazilian marketplaces
• 9 relational tables covering customers, sellers, products, payments, reviews, and geolocation
• A complete customer journey, from the first click to the delivery rating
• Real review text (anonymized, partner names replaced with Game of Thrones houses, which is delightfully absurd)

But “real data” means real problems. Missing product dimensions. Duplicate geolocations. Inconsistent timestamps. Null review comments. This wasn’t a project where you could just run SELECT * and start making charts.

The Architecture: Building It Like a Production System

SQL Pipeline

Most tutorials skip the unsexy parts, the infrastructure, the data contracts, the reusability. I deliberately built this project the way a professional BI team would:

CSV raw data
  ↓
MySQL Staging Tables
  ↓
ETL.sql (structure, integrity, indexing)
  ↓
views.sql (semantic layer: dim_ and fact_ tables)
  ↓
analysis.sql (10 executive business questions)
  ↓
Power BI Dashboard

Why does this matter? Because repeatability is everything in analytics. If your pipeline breaks when someone adds a new column to a CSV, it was never really a pipeline, it was a script held together with hope.

The Three SQL Scripts

I separated SQL responsibilities intentionally:

**ETL.sql** handles the boring-but-critical stuff: converting text timestamps to proper DATETIME columns, enforcing primary and foreign keys, creating indexes for fast reporting queries, and writing idempotent logic so re-running the script never breaks anything.

**views.sql** is the semantic layer, the bridge between raw tables and business meaning. Instead of letting Power BI join seven tables on every refresh, I published clean, reusable views:

• dim_customer — who bought
• dim_product — what they bought
• fact_orders — what happened
• fact_order_items_enriched — the full enriched transaction record

**analysis.sql** is where the business questions live, 10 repeatable SQL analyses that answer real executive questions. More on those in a moment.

The Questions That Actually Matter

Executive KPI Overview

I hate analytics projects that answer questions nobody asked. So before writing a single query, I framed three strategic questions that a marketplace leadership team would actually lose sleep over:

1. Where is growth accelerating or stalling? (Revenue trends, category performance, regional concentration)
2. Where are margin and service quality leaking? (Freight burden, late deliveries, low review scores)
3. Which levers create sustainable performance? (Payment mix, repeat customers, retention)

Then I turned each of those into specific, executable SQL analyses.

What the Data Actually Revealed

Monthly revenue trend

1. Revenue Has a Seasonality Story

Monthly revenue didn’t just grow, it spiked dramatically in certain periods, then pulled back. Understanding why those peaks happen (promotional campaigns? holidays? a single viral product category?) is where real business value lives. The trend line alone is a conversation starter for any leadership meeting.

Top-category bar chart

2. A Handful of Categories Drive Everything

E-commerce follows the Pareto principle brutally. In the Olist data, the top revenue-driving categories are heavily concentrated. This creates both a risk (over-reliance) and an opportunity (double down on what’s working). Knowing which categories carry the business, and which are dragging down average order value, is the difference between strategy and guesswork.

Payment method breakdown

3. Credit Cards Dominate, But Installments Tell a Different Story

One of the most fascinating findings in Brazilian e-commerce data is the installment payment culture. Brazilians routinely split purchases into 3, 6, even 12 installments, even for small amounts. The average order value varies dramatically by payment type, and understanding that mix shapes everything from pricing strategy to working capital management.

Revenue concentration by state

4. São Paulo Is a Planet Unto Itself

When you map revenue by state, the concentration in São Paulo (SP) is almost jarring. Brazil is a massive, economically diverse country, but Olist’s order distribution mirrors Brazil’s broader income inequality. This raises a strategic question: is the Southeast concentration a ceiling or an opportunity? Expanding into underserved northern states could unlock an entirely different growth curve.

Delivery performance chart

5. Logistics Is the Hidden Margin Killer

Late delivery is the silent destroyer of customer satisfaction. In the Olist dataset, delivery punctuality varies wildly by product category and region. The freight cost burden is also not evenly distributed, some categories carry freight costs that eat heavily into seller margins.

A product that sells well but ships expensively to remote states might be a net-negative contribution to marketplace health. You can’t see that without a properly engineered analytics layer.

6. The Review Score Distribution Has a Brutal Tail

The majority of Olist reviews are 5 stars. But the 1-star tail is disproportionately damaging, and it clusters in specific categories. When you cross-reference low review scores with late deliveries and high freight costs, a pattern emerges: logistics failure is the primary driver of negative reviews, not product quality.

That’s an actionable finding. It means customer satisfaction isn’t a product problem, it’s a supply chain problem.

The Dashboard: From Data to Decision

The Power BI dashboard was designed around a clear philosophy: separate leading indicators from diagnostic indicators.

• The Executive KPI Overview page shows the headline numbers: total revenue, AOV, repeat customer rate, late delivery rate.
• The Sales & Category page lets you drill into what’s driving revenue.
• The Logistics & Service Quality page surfaces where things are breaking.
• The Customer & Payment Behavior page maps retention and payment patterns.

Every page supports slicer-driven drilldowns, so a business stakeholder can go from “revenue is down” to “specifically in the Southeast, in the electronics category, with orders using boleto payments” in under 60 seconds.

That’s the difference between a dashboard and a decision support system.

What I Learned About Building BI Projects That Last

After completing this project, here are the things I’d tell my past self:

Separate your concerns. ETL, semantic modeling, and business logic are three different jobs. Mixing them in one script creates technical debt that compounds fast.

Index aggressively. Power BI connecting to a MySQL database with no indexes on frequently joined columns is a slow, painful experience. Index your foreign keys. Index your date columns. Future you will be grateful.

Name your views like you mean it. dim_customer and fact_orders communicate intent. temp_table_v3_final communicates chaos.

Build for reruns. Every script should be safe to run twice. Idempotent ETL isn’t optional in production, it’s the floor.

Ask the business question first. The best analytical code is code that answers a question someone actually asked. Start with the question, work backwards to the query.

Try It Yourself

The full project — SQL scripts, Power BI templates, and data documentation — is available on my GitHub:

🔗 **github.com/gabrielnguyen2603/Olist-Ecommerce-Project**

The Olist dataset itself is free on Kaggle:

🔗 **Brazilian E-Commerce Public Dataset by Olist**

To run it yourself:

1. Spin up a MySQL 8+ instance
2. Load the CSVs from the /data folder
3. Run ETL.sql → views.sql → analysis.sql in order
4. Connect Power BI to your MySQL ecommerce database and import the semantic views

Final Thought

There’s something special about working with real data. The Olist dataset isn’t simulated; it’s 100,000 actual transactions from actual small business owners selling to actual people across a continent-sized country.

Every late delivery in the dataset is a customer who waited too long. Every 1-star review is a relationship that broke down. Every repeat purchase is a testament to a seller who earned someone’s trust.

Analytics at its best doesn’t just describe what happened; it creates the clarity to make things better.

That’s why I build these systems. And that’s why, if you’re on the fence about starting a project like this, you should just start.

Thousands of developers share what they’re building, learning, and discovering across our publications every month. One account connects you to our entire network of publications and communities. **Explore more at plainenglish.io.**