This project takes 100,000+ real online orders from a Brazilian e-commerce marketplace (Olist) and turns raw, messy order data into answers to the kind of questions a business actually asks: Is revenue growing? Which regions have delivery problems? Do customers who get their orders late leave worse reviews? How much of our revenue comes from our most loyal customers?
The data is stored in a properly structured database (PostgreSQL), analyzed using SQL, and the results are shown visually in a Power BI dashboard — so the findings are easy to read even without touching a single line of code.
Most e-commerce, retail, and logistics companies collect exactly this kind of data — orders, payments, deliveries, customer reviews — but raw data sitting in a database doesn't help anyone make a decision on its own. Someone has to clean it, organize it, ask the right questions of it, and present the answer clearly. That's the skill this project demonstrates: not just writing queries, but going from a messy dataset to a business insight someone could actually act on — including being upfront when an initial idea turned out to be wrong and adjusting the analysis instead of forcing a misleading conclusion.
This mirrors a real task an analytics or business intelligence role would be given at a company that sells things online:
- Track growth — is the business making more money month over month, and when did it spike or slow down?
- Find operational problems — which regions are getting orders delivered late, and is that actually hurting the customer experience?
- Understand the customer base — how much of total revenue depends on a small group of repeat customers, versus one-time buyers?
Each of these is answered below with a specific number, not just a general trend — and shown in a dashboard a manager or stakeholder could open and understand in under a minute.
PostgreSQL 18 · SQL (CTEs, Window Functions) · Relational Schema Design · Power BI
Olist Brazilian E-Commerce Public Dataset — 100k+ orders (2016–2018) across 9 relational CSVs (customers, orders, order items, payments, reviews, products, sellers, geolocation, category translations).
9 interconnected tables with enforced primary and foreign key constraints (orders as the
central fact table, joined to customers, order_items, payments, reviews, products, and sellers).
See schema.sql for full DDL.
Data quality issues found and resolved during load (see fix_data_issues.sql):
- 2 product categories missing from the translation lookup table — backfilled via a staging table
- Duplicate
review_idvalues in the raw reviews file — deduplicated usingROW_NUMBER() - Load-order dependency chain (products must load before order_items, due to FK constraints)
Restricted to the platform's stable period (Jan 2017–Aug 2018), excluding the 2016 soft-launch ramp-up and an incomplete final month. Revenue grew fairly consistently, with a standout +52% spike in November 2017 (Black Friday). Growth decelerated through 2018 as the platform matured.
Ranked all 26 states by % of orders delivered after their estimated delivery date. Northeastern states (AL, MA, PI, CE) show 15–24% late-delivery rates, compared to just 5.89% in São Paulo (Olist's home base and largest market by volume) — pointing to a real logistics gap for regions farther from the primary distribution hub.
Ranked top 3 revenue-generating categories within each of Brazil's 27 states using
DENSE_RANK() PARTITION BY. health_beauty and watches_gifts dominate nationally, with a
few regional exceptions (RS/MG favor bed_bath_table; AC/RR favor sports_leisure).
Initially hypothesized that regions with worse delivery delays would show higher order
cancellation rates. Tested it with a state-level NTILE(2) split and a CORR() check —
result was a near-zero correlation (cancellation rates of 0.44% vs. 0.39% between worst- and
best-delay states). Cancellations mostly happen pre-fulfillment (payment/stock issues),
not because of late shipping, so the hypothesis didn't hold.
Pivoted to test delay against review score instead, since a customer has to actually receive an order before reviewing it. This held up clearly:
- Late-delivered orders average 2.57★, vs. 4.30★ for on-time orders
- 54.0% of late deliveries receive a 1–2 star review, vs. 9.2% on-time — a ~5.9x spike
- Delay length correlates with review score at -0.262 (moderate, real signal)
Grouped by customer_unique_id (not customer_id, which resets per order in this dataset)
to isolate customers with more than one order, then split into revenue quintiles with
NTILE(5). The top 20% of repeat customers (578 people) generated 49.7% of total
repeat-customer revenue ($372.7K of $750.5K).
Connected Power BI Desktop directly to PostgreSQL (Import mode, Npgsql driver) using the SQL-statement connector option to pull query1, query4, and query5 results straight from Postgres. Three visuals:
- Revenue trend line chart (MoM growth + Nov 2017 spike)
- Review score comparison, late vs. on-time deliveries
- Pareto bar chart, repeat customer revenue by quintile
createdb olist_ecommerce
psql -U postgres -d olist_ecommerce -f schema.sql
psql -U postgres -d olist_ecommerce -f load_data.sql # loads clean tables
psql -U postgres -d olist_ecommerce -f fix_data_issues.sql # resolves data-quality issues
psql -U postgres -d olist_ecommerce -f query1_mom_revenue_growth.sql
psql -U postgres -d olist_ecommerce -f query2_regional_bottlenecks.sql
psql -U postgres -d olist_ecommerce -f query3_top_categories_by_region.sql
psql -U postgres -d olist_ecommerce -f query4_delay_review_score.sql
psql -U postgres -d olist_ecommerce -f query5_pareto_repeat_customers.sqlRequires the 9 Olist CSVs placed in a data/ folder (not included in this repo due to size —
download from the Kaggle link above).