An operations team needs to know where deliveries lose time and which missed promises deserve attention first.
Where are orders losing time, and what should the business investigate to make delivery more reliable?
I use Olist’s real, anonymised order records to connect delivery performance with customer experience, then translate the findings into a proposed operations review and exception queue.
Independent case study using historical Olist records. The data describes 2016–2018.
THE PROCESS, IN PLAIN LANGUAGE
01 / PurchaseThe customer places an order.
02 / ApprovalThe order is approved.
03 / HandoverThe carrier receives it.
04 / DeliveryIt reaches the customer.
What does “late” mean here?
Delivery occurred after the estimated calendar date. An arrival during the promised day counts as on time. A long journey can still be on time if the promise allowed for it.
The main rate covers delivered orders with valid core dates. Cancelled, shipped and other non-delivered records are a separate population, not successful deliveries.
01 / WHY THE CUSTOMER MATTERS
Late delivery comes with a different review profile.
Nearly two in three reviewed late orders received only one or two stars. For on-time orders, it was fewer than one in ten.
What this changes
Delivery exceptions deserve a customer-support response as well as an operations review. A proactive-contact pilot is worth considering.
Ratings cover the whole purchase experience. This comparison does not prove lateness caused a poor review or predict a pilot’s impact.
Orders receiving 1–2 stars Share of reviewed orders in each delivery group
On-time deliveries9.3%
Late deliveries62.4%
0100%
On time: 8,289 / 89,443 Late: 3,983 / 6,381 646 eligible orders had no review.
02 / WHERE TIME ACCUMULATES
The largest time gap appears after handover.
Late orders averaged 27.9 days from handover to delivery, compared with 8.0 days for on-time orders. Approval-to-handover was also longer: 5.6 versus 2.7 days.
Ask for tracking scans, failed-attempt records and route information before deciding what to change. The timestamp interval can include transport, sorting and waiting.
Average elapsed days, using the same valid-stage orders throughout. Each panel uses a 0–30 day scale.
Purchase → approval
On time0.4 days
Late0.5 days
030 days
Approval → handover
On time2.7 days
Late5.6 days
030 days
Handover → delivery
On time8.0 days
Late27.9 days
030 days
88,573 on-time and 6,509 late orders. A further 1,388 eligible orders have missing or out-of-sequence stage dates and are excluded here. Groups are defined by their delivery outcome; this is a descriptive comparison.
03 / WHERE TO INVESTIGATE
Volume and risk point to different priorities.
São Paulo has more late orders. But its 4.5% late rate is below the overall 6.8% rate. Rio de Janeiro’s rate is 12.1%, with nearly as many late orders from a much smaller order base.
Rio accounts for 12.8% of eligible orders but 22.9% of late orders. It is a sensible place to start a diagnostic sample; São Paulo remains important because of volume. These are unadjusted destination comparisons, not evidence that location itself causes delays.
Five destinations with the most late orders · all eligible delivered orders
Customer state
Eligible orders
Late orders
Late rate
São Paulo
40,494
1,820
4.5%
Rio de Janeiro
12,350
1,495
12.1%
Minas Gerais
11,354
519
4.6%
Bahia
3,256
396
12.2%
Rio Grande do Sul
5,344
325
6.1%
Choose a destination to see its order count and late rate together.
All states are included. Smaller groups are less stable; these figures are not adjusted for month, seller or category mix. This control changes this destination readout only.
FROM EVIDENCE TO A BUSINESS DECISION
Start with a focused investigation. Design a better handoff.
01
Review late-order journeys in Rio.
Compare late and on-time orders from similar months, sellers and categories. Join carrier tracking events to locate repeated waiting points. Retain São Paulo on the high-volume watchlist.
02
Propose one shared overdue-order queue.
Give operations and support one record per order, showing the promise date, latest verified stage, days overdue and proposed owner. Missing or impossible dates should be flagged explicitly.
03
Test whether earlier communication helps.
Run a measured pilot with a suitable comparison group. Track customer contacts, review outcomes, delivery outcomes and handling time. Set targets after a baseline is agreed.
The proposed decision: approve a diagnostic review and define a small pilot. No savings, delivery improvement or operational rollout has been demonstrated by this portfolio project.
THE BUSINESS ANALYSIS WORK
A finding needs a usable requirement.
Keep the order whole.
Need: an analyst should not see three exceptions just because an order contains three items.
Acceptance: each eligible overdue order appears once, regardless of item count.
Make uncertainty visible.
Need: the team should know whether a parcel is waiting or a timestamp is missing.
Acceptance: unknown dates display “unknown”; impossible sequences are flagged and excluded from stage averages.
Keep comparisons fair.
Need: a large destination should not look unreliable solely because it has more orders.
Acceptance: show counts and rates together, updating both numerator and denominator when filtering.
Proposed requirements for a future solution. The interactive readout above demonstrates the count-versus-rate principle; it is not a live exception-management system.
FROM RAW RECORDS TO A TRUSTWORTHY COMPARISON
One order. One selected review.
Review rows are not the same as reviewed orders. The Olist review file contains 99,224 rows for 98,673 orders; 547 orders have multiple reviews. A direct join would give some orders more influence in the result.
The issue
Within the eligible delivered-order cohort, an unfiltered join produces 96,353 review rows.
The rule
Select one review per order using the latest answer timestamp, then creation timestamp, review ID and score as deterministic tie-breakers. This is an analytical choice, not an Olist business rule.
The check
The selected dataset contains 95,824 reviewed orders, with one review per order. The rule removes 529 extra joined rows. Another 646 eligible delivered orders have no review and are excluded from rating percentages.
Eligible orders are delivered with valid purchase, actual-delivery and estimated-delivery dates and consistent date ordering. “Late” means the actual calendar date is after the estimate. Same-day arrivals count as on time.
SQL: compare low ratings after selecting one review
The query runs against the eligible cohort described above. The downloadable script includes the full eligibility view.
WITH ranked_reviews AS (
SELECT order_id, review_score,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY review_answer_timestamp DESC,
review_creation_date DESC, review_id DESC, review_score DESC
) AS review_rank
FROM reviews
)
SELECT e.late, COUNT(*) AS reviewed_orders,
SUM(CAST(r.review_score AS INTEGER) <= 2) AS low_reviews,
ROUND(100.0 * SUM(CAST(r.review_score AS INTEGER) <= 2)
/ COUNT(*), 2) AS low_review_percent
FROM eligible e
JOIN ranked_reviews r USING (order_id)
WHERE r.review_rank = 1
GROUP BY e.late
ORDER BY e.late;
Late deliveries have a much higher share of low ratings in this cohort. This identifies a priority for investigation; the comparison does not establish that delivery delay caused every low rating.
BEHIND THE WORK
Follow the evidence. Question the interpretation.
The work includes a reproducible analysis, a source and quality record, a business brief and proposed acceptance criteria. The public site shows aggregates; raw customer identifiers and review text are not republished.
A PAGE FROM THE ANALYSIS
A different review profile.
On time9.3%
Late62.4%
1–2-star reviews among reviewed orders in each group · Olist 2016–2018 · Association, not causation.
99,441 source orders → exclude 2,963 non-delivered statuses and 8 delivered orders missing core dates → 96,470 eligible orders. The stage comparison additionally excludes 1,388 missing or inconsistent timestamp sequences.
Recent and non-delivered orders may have incomplete outcomes. The delivered-only rate does not describe eventual reliability for every purchase. Historical patterns should be rechecked with current operational data.
What can this dataset not tell us?
It cannot establish carrier fault, stockouts, warehouse performance, reasons for failed delivery, the effect of an intervention or financial savings. Destination and review comparisons are observational. Review timing and who chooses to respond can influence the results.
Attribution: Olist, Brazilian E-Commerce Public Dataset. Retrieved 20 September 2026. Source and this analytical adaptation: CC BY-NC-SA 4.0. Changes include filtering, joining, aggregation and interpretation. No Olist endorsement is implied.
WHAT THIS PROJECT IS REALLY ABOUT
Give the delay a context. Give the team a next step.
The story is not just that some orders were late. It is how to turn that observation into a fair comparison, a focused investigation and a requirement someone can use.