Olist E-Commerce Business Performance Analysis

Business problem

A marketplace wants to know where its revenue really comes from, whether it delivers what it promises, and whether delivery performance affects how customers rate the experience. It also needs to know whether the underlying data is clean enough to trust those numbers.

Dataset

The public Olist dataset covers orders, order items, products, customers, sellers, payments and reviews for a Brazilian marketplace. The analysis excludes cancelled and unavailable orders and works with 98,199 completed orders. Delivery analysis uses 96,470 delivered orders.

Limitations:

• The dataset is historical and anonymised, and covers one marketplace.
• The report states monetary values in dollars. The source is a Brazilian marketplace, so the currency label is worth confirming before any figure is quoted outside this project.
• It contains no browsing or checkout data, so it cannot measure conversion or cart abandonment.

Analytical workflow

1. Schema setup (01_schema_setup.sql): load the relational tables into PostgreSQL.
2. Cleaning and exploratory checks (02_data_cleaning_eda.sql): test missing values by order status and trace anomalies to their cause before computing any KPI.
3. Business KPIs (03_business_kpis.sql): revenue, order value, category concentration and delivery metrics.
4. Report: findings, recommendations and a phase-two roadmap written for a business reader.

Business insights

Revenue is concentrated. 18 of 74 categories (24%) produce 81.26% of gross revenue (about $10.96M). The other 56 categories share the remaining 18.74%.

The company delivers faster than it promises. Average actual delivery is 12.6 days against 23.7 days shown at checkout, so orders arrive about 11.2 days early on average.

Delivery time lines up with review scores. Five-star orders averaged 10.7 days to deliver and one-star orders averaged 21.3 days. Satisfaction drops more steeply once delivery passes roughly 14 days.

What a stakeholder could consider: focus marketing and warehouse layout on the leading categories, review whether a 23.7-day promise is too conservative, and set up alerts for orders approaching 14 days in transit. The report presents these as proposals.

Methodology and technical decisions

• Clean first. Eight orders marked delivered had no delivery date, clustered in June-July 2018, which suggests a system sync problem. They were excluded from delivery-time calculations and the exclusion was documented.
• Make missing categories visible. 610 products had no category. Rather than dropping them, they were labelled “Unassigned” so about $178,572.55 of revenue stays visible in category reports.
• Use a Pareto cutoff. Ranking categories by revenue and tracking cumulative share gives a clear, checkable definition of “concentrated”.

Results and limitations

• The relationship between delivery time and review score is an association. Slow deliveries may differ from fast ones in product type or region, so the data does not prove that faster delivery alone would raise scores.
• The report suggests that long delivery estimates may reduce conversion. The dataset has no checkout data, so this is a hypothesis and not a finding.
• The “expected benefits” listed in the report are goals, not measured results.