Using SQL and Power BI to understand what is driving revenue, customer behaviour, delivery performance and operational risk.
An e-commerce business can have thousands of orders and still not know what is actually driving its performance.
Are customers coming back? Which products and regions generate the most revenue? Are delivery delays hurting customer experience? Which sellers may need attention? And when revenue suddenly jumps, what actually caused it?
This project explores those questions using the Brazilian Olist E-Commerce Dataset.
I used PostgreSQL and SQL to audit the data and investigate 10 business questions, then brought the most important insights together in an interactive Power BI dashboard.
The analysis was built around 10 questions that an e-commerce business could actually use:
01. 💰 Growth - How did delivered-order revenue and order volume change over time?
02. 🛍️ Products - Which product categories generate the most revenue?
03. 👥 Customers - How valuable and loyal are Olist customers?
04. 🚚 Delivery - How do delivery delays affect customer review scores?
05.
06. 📍 Markets - Which customer states generate the most orders and revenue?
07. 💳 Payments - How do payment methods and installments differ in customer spending?
08. 📈 Anomalies - Which months showed unusual revenue movements?
09. 🔎 Root Cause - What drove the November 2017 revenue anomaly?
10. ⭐ Customer Experience - How much customer dissatisfaction is associated with late delivery?
The project uses the Brazilian E-Commerce Public Dataset by Olist, containing anonymized e-commerce data from Brazil.
🔗 Brazilian E-Commerce Public Dataset by Olist — Kaggle
The analysis used 9 connected CSV files covering customers, orders, order items, payments, reviews, products, sellers, geolocation and product-category translation.
Together, the PostgreSQL database contains:
✦ 99K+ orders
✦ 112K+ order-item records
✦ 103K+ payment records
✦ 99K+ reviews
✦ 32K+ products
✦ 3K+ sellers
✦ 1M+ geolocation records
📌 One CSV is included in this repository as a sample. The complete public dataset can be downloaded from Kaggle.
Before answering the business questions, I performed a separate SQL data-quality audit.
The checks covered duplicates, missing values, dates, payment values, review scores, product information and delivery inconsistencies.
This helped separate genuine business patterns from possible data-quality problems before moving into the analysis.
Delivered-order revenue increased substantially as the platform expanded.
One month stood out in particular: November 2017 generated about 1.15M from 7,289 delivered orders, representing a major increase from the previous month.
Instead of simply reporting the spike, I used a rolling 3-month revenue baseline and z-score anomaly detection to identify unusual monthly movements and then investigated the November increase further.
After detecting the unusual revenue movement, I compared October vs November 2017 performance by product category.
This turned a simple observation —
“Revenue suddenly increased.”
into a more useful business question —
“Which categories actually contributed to that increase?”
Customers were segmented into:
One-Time • Repeat • High Repeat
The customer base was heavily dominated by one-time buyers, showing that acquiring customers was not the same as retaining them.
This makes repeat purchasing an important area for the business to investigate.
Orders were grouped based on whether they arrived early, on time or late, and their review behaviour was compared.
Late deliveries showed a much higher level of poor customer reviews.
In simple terms:
Late delivery → greater risk of customer dissatisfaction.
Seller performance was not judged only by the number of late deliveries.
The analysis combined order volume, late-delivery rate and average review score to identify high-volume sellers where operational problems could affect more customers.
This created a practical seller risk watchlist rather than a simple seller ranking.
Revenue and order volume were compared across customer states.
São Paulo (SP) clearly stood out as the strongest market, showing that a substantial part of Olist's business activity was concentrated geographically.
Revenue was also compared across product categories.
Categories including health & beauty, watches & gifts, bed/bath/table and sports & leisure appeared among the strongest revenue contributors.
The analysis also compared payment methods and installment behaviour to understand how customers chose to pay and how spending differed across those payment patterns.
The most important business insights were brought together in an interactive Power BI dashboard.
💰 15.42M Total Revenue
📦 ~96K Delivered Orders
🧾 159.83 Average Order Value
The dashboard brings together revenue trends, customer purchase behaviour, geographic performance, product categories, delivery-related reviews and high-volume seller risk in one view.
9 Connected CSV Files
↓
PostgreSQL Database
↓
Data Quality Audit
↓
10 Business Questions
↓
SQL Analysis
↓
Trend • Customer • Product • Delivery • Risk Analysis
↓
Anomaly Detection & Root-Cause Investigation
↓
Power BI Dashboard
↓
Business Insights
PostgreSQL • SQL • Power BI • Power Query • DAX
SQL techniques used include CTEs • Window Functions • Aggregations • CASE Statements • Date Analysis • Multi-Table Joins • Rolling Statistics • Z-Score Anomaly Detection
📁 Datasets
└── olist_customers_dataset.csv
📄 01_data_quality_audit.sql
📄 02_business_analysis.sql
📊 Olist_Ecommerce_Analytics_Dashboard.pbix
🖼️ Dashboard.png
📖 README.md
📌 Dataset Note: The complete analysis used all 9 original Olist dataset files. Due to file-size limitations, the full raw dataset is not duplicated in this repository and can be downloaded from the original Kaggle source.
This project goes beyond creating charts from an e-commerce dataset.
It starts with business questions, checks whether the underlying data can be trusted, uses SQL to investigate performance and risk, drills deeper when unusual behaviour appears, and finally turns the findings into an interactive dashboard.
The result is a complete analytics workflow:
Raw Data → Business Questions → SQL Investigation → Findings → Power BI Dashboard
Manogna
Data Analytics • SQL • Power BI
⭑✮ If you found this project interesting or have suggestions, feel free to connect. Always happy to learn and collaborate ₊˚⊹