Data & BI · SQL Server · T-SQL · Python · Tableau

Olist Marketplace — Delivery SLA & Seller Sizing (SQL case study)

Which marketplace deliveries miss the promised date, which sellers carry that late volume, and how big is the problem? An analysis of the public Olist Brazilian e-commerce dataset in SQL Server (T-SQL), cross-checked in Python and visualised in Tableau.

ToolsSQL Server · T-SQL · Python · Tableau
DataPublic Olist dataset (orders, items, sellers, reviews)
Denominator96,470 delivered orders with both dates
ValidationSQL and Python agree: 96,470 / 6,534 / 0.0677
Tableau Delivery SLA dashboard for Olist showing 96,470 delivered orders, 6,534 late orders, a 6.77% late rate and late orders by delay bucket
Tableau Delivery SLA view (screenshot from the repository).

Definitions

  • Denominator: orders with order_status = 'delivered' that have both a delivered date and an estimated date (96,470).
  • Late: delivered on a calendar day after the estimated day. Same-day delivery counts as on time.

The core rule in T-SQL compares dates, not timestamps:

case when cast(order_delivered_customer_date as date)
        > cast(order_estimated_delivery_date as date)
     then 1 else 0 end as is_late

Results

Delivery SLA and seller sizing
KPIValue
Delivered orders with dates96,470
Late orders6,534 (6.77%)
8+ days late2,862 (44% of late orders)
High-rate sellers (20+ orders, late rate ≥ 20%)23 sellers, 3.8% of late lines
Top 15 sellers by late lines1,517 lines (~21% of late lines)
Average review, on-time vs late4.29 vs 2.27

Recommendations

  • Coach the top 15 sellers by late volume, not the 23 sellers with the highest late rate, because volume is where most late lines sit.
  • Treat 8+ day delays separately from 1–3 day slips.
  • Do not penalise sellers with fewer than 20 orders.

What this project demonstrates

  • SQL Server / T-SQL: explicit denominator and lateness rules, date-level comparison, seller aggregation.
  • Cross-validation: the same KPI reproduced in Python (96,470 / 6,534 / 0.0677).
  • Tableau: delivery SLA and seller views.
  • Prioritisation: sizing sellers by late volume rather than late rate, so effort goes where most late orders are.

Limits

Lateness is an order-level fact copied onto every item line. Reviews are not one per order. The dataset contains no cost or CSAT money impact, so none is claimed.

Written by Emmanuel Abolade, Data Analyst, Business Intelligence Developer and Full-Stack Software Developer based in Hull, UK. Every figure on this page comes from the linked public repository or live demo.