Olist E-Commerce Business Analysis
Analysed ~99,000 orders from Brazil's largest marketplace aggregator to test four pre-formed business hypotheses — delivery performance, geographic revenue concentration, seller GMV distribution, and freight cost burden — using Excel, Tableau, and structured BA frameworks.
1. The Problem
Olist is a Brazilian marketplace aggregator connecting small and medium sellers to platforms like Mercado Livre, B2W, and Via Varejo. The dataset covers approximately 99,000 orders placed between 2016 and 2018 across Brazil's 27 states.
Using the 6P Framework (Product, Price, Place, Promotion, People, Process) as a structured diagnostic lens — before opening the dataset — four problem areas emerged: freight costs potentially making purchases economically unviable in remote regions, seller supply concentrated far from customer demand, delivery timing as a likely driver of satisfaction scores, and platform revenue likely following a power law across sellers. These were formalised into four testable hypotheses and documented before any data was accessed, to prevent retrofitting conclusions to data.
2. The Approach
I structured the analysis using a standard BA workflow across four phases:
- Business Question Formation: Applied the 6P Framework to pre-diagnose problems and form four testable hypotheses (H1–H4) before accessing the dataset. Predictions were documented upfront to ensure analytical integrity.
- Stakeholder Mapping: Built a Power/Interest Matrix for six stakeholder groups — CEO, Operations & Logistics, Sales & Marketing, Investors, Sellers, and Customers — with tailored engagement strategies for each.
- Process Mapping: Modelled the full order-to-delivery lifecycle using BPMN swimlane diagrams in Draw.io across four actors: Customer, Olist System, Seller, and Carrier — identifying three critical handoff points where delays compound.
- Data Analysis & Visualisation: Consolidated 9 raw CSV files (99,441 rows) into a MASTER sheet in Excel using XLOOKUP joins, built 6 pivot tables, then created a 7-visualisation Tableau dashboard including a Lorenz curve for seller GMV concentration.
3. The Process
I traced the analytical workflow step-by-step:
Imported 9 raw CSV files via Power Query into separate worksheets. Built a MASTER sheet (99,441 rows, 23 columns) using XLOOKUP to join orders, customers, items, products, sellers, payments, reviews, and geolocation on foreign keys. Filtered to 96,478 delivered orders and created 8 calculated columns: delivery_delay_days, delivery_status, delay_band, order_purchase_month_year, freight_pct, and three additional fields for Lorenz curve inputs and concentration metrics.
Built 6 pivot tables to test H1–H4: delivery status vs. average review score; delay severity vs. average review score; revenue by customer state; monthly order volume trend; seller revenue concentration (Lorenz curve inputs); and average freight percentage by customer state — all confirming the pre-formed hypotheses.
Connected Tableau Desktop to the DELIVERED_ONLY sheet. Built 7 charts: delay severity vs. review score (line), revenue by top 10 states (bar), freight % by state (bar), monthly growth trend (line), customers by state (map), sellers by state (map), and a GMV Pareto Lorenz curve using 8 custom calculated fields (including LOD expressions like True GMV, Concentration Ratio, and Top 20% Sellers GMV) to deduplicate revenue and compute the seller concentration ratio.
4. The Analysis
All four hypotheses were confirmed. The quantitative evidence revealed that delivery failures, revenue concentration, seller imbalance, and freight inequality are not four separate problems — they are three dimensions of a single structural root cause: seller geographic concentration in São Paulo forces long-haul fulfilment across Brazil.
Late deliveries (6,534 orders, ~6.8% of delivered orders) averaged a 2.27 review score versus 4.03 for on-time orders — a 1.76-point gap on a 5-point scale. Orders 7+ days late dropped further to 1.70, functionally destroying customer retention. Delay severity was progressive: every additional band produced a measurable score collapse.
São Paulo generated 42% of total platform revenue (R$4,622,373 of R$12.09M). The top 3 states — SP, RJ, MG — accounted for ~70% of GMV. Platform orders peaked at 7,289/month in Nov 2017 then plateaued, indicating Olist had saturated established markets and needed geographic expansion to sustain growth.
Monthly order volume grew rapidly from near-zero in late 2016 to a peak of 7,289 orders in November 2017 — then flatlined between 6,000–7,000 orders/month through 2018. This plateau, combined with the geographic concentration above, suggests Olist exhausted growth in its core SP/RJ/MG markets without unlocking demand in underserved states.
Of 2,960 sellers, the top 592 (20%) generated 82.54% of GMV — stronger than the classic 80/20 rule. Platform health depends disproportionately on retaining a small, identifiable cohort of highly successful sellers.
Northern-state customers paid freight exceeding 50% of product price: Roraima (60.07%), Rondônia (57.83%), Maranhão (53.67%) — more than double São Paulo's 25.24% baseline. Despite these costs, northern customers were still placing orders, confirming suppressed latent demand.
All four findings trace back to one structural problem: sellers are concentrated where customers already are, not where unmet demand exists. This forces long-haul fulfilment across Brazil, driving up both freight costs and delivery times for remote regions.
fulfilment
Fig 6 — Seller concentration in SP forces cross-country shipping to northern customers, creating the freight burden (H4) and delivery delays (H1) simultaneously. Solving seller distribution addresses all three business gaps.
5. Business Recommendations
These recommendations are based on analysis of Olist's 2016–2018 order, delivery, seller, and customer data. Each targets a specific gap identified in the analysis. They are sequenced by dependency — logistics infrastructure must be in place before seller acquisition becomes effective. Seller tiering runs in parallel as a quick win.
Customers in northern states are still placing orders despite paying freight costs that can exceed half the product price. That is not a market with no demand — it is a market with a logistics cost problem. Regional carrier partnerships would bring freight costs to a level where northern orders become economically viable at scale.
A 1.76-point review score gap between late and on-time orders is large enough to affect seller rankings, repeat purchase rates, and customer trust. The root cause is route complexity — northern states have longer distances, fewer carrier options, and infrastructure constraints the fulfilment workflow was never designed to handle.
592 sellers generate the majority of platform revenue with no differentiated treatment. If a meaningful portion churn, the impact on GMV is immediate. Segmenting sellers into tiers and assigning dedicated support to the top tier protects the revenue base. Retention is cheaper than replacement.
When 42% of revenue depends on one state, any disruption disproportionately affects the platform. A targeted seller acquisition programme in tier 2 and tier 3 states would reduce concentration risk, shorten fulfilment distances, and open a growth channel competitors have not yet saturated.
- Building warehouses or shifting to a stock-holding model — outside Olist's operating model
- Abandoning the SP market — concentration is a risk, not a reason to pull back from a high-performing region
- Category-level changes — category analysis was out of scope for this project