Olist Marketplace — E-commerce Analytics

Sales, customer, delivery and seller report for Olist, a Brazilian marketplace: about 100k orders from 2016 to 2018 across 9 linked tables, turned into a star schema and a five-page Power BI report.

Preview

Highlights

Revenue Jan–Aug 2018 is +138% year on year, with Black Friday 2017 the biggest month
Built and validated as code; not yet opened in Power BI Desktop

Problem

The marketplace was growing fast, but it needed to know what drives bad reviews, whether customers come back, how dependent it is on a few sellers, and what distance costs buyers in freight.

Approach

DuckDB star schema from 1.55M raw rows: customer RFM scores, seller Pareto tiers, product category groups and distance per item from ZIP-code coordinates; 12 data tests block the export if any fail
63 DAX measures in display folders: sales, time intelligence (prior year, YoY, YTD, MoM, rolling 3 months), customers, delivery, satisfaction, payments, sellers and logistics
Five report pages sharing a sidebar and year / region / category slicers; measure-driven colours instead of hard-coded ones, sort-by columns for every ordered label
Logistic regression in Python to size how much late delivery raises the odds of a bad review

Stack

Power BIDAXTMDLPBIRDuckDBSQLPython

Outcome

Orders more than a week late average 1.7★ vs 4.3★ when early; late delivery is ×9.0 the odds of a 1–2★ review
97% of customers buy only once; just 0.48% of a monthly cohort orders again the next month
4.2% of sellers (129) generate half of all revenue; freight takes 26% of the basket past 2,000 km vs 15% locally
Model: 10 tables, 63 measures; 5 report pages; both validators pass