Analysis 11 of 31 · Operations
Where do orders get stuck?
The short answer
60.2% of late orders: the carrier leg alone took longer than the whole delivery time promised at purchase
A different base: each stage's share of the extra days late orders took compared with on-time orders
In 60.2% of late orders, the carrier leg alone took longer than the whole delivery time promised. Late orders spent 27.9 days with the carrier, on-time ones 8.0; 72.1% of late orders reached the carrier by the seller's deadline.
Where do late orders lose their time: before approval, before the carrier handoff, or after it?
- Public data
- Anonymized marketplace orders
- Delivered orders only
- Correlation, not cause
Data as of · fixed dataset
Key figures
- 80.0%of late orders had a carrier leg longer than the carrier legs of 9 in 10 on-time orders (over 15.3 days)5,205 of 6,509 late orders; the carrier leg is handoff to customer. 95,082 delivered orders
- 20.9%of orders handed to the carrier after the seller's deadline arrived lateAgainst 5.4% of orders handed over by the deadline. 8,699 and 86,383 orders
- 72.1%of late orders were handed to the carrier by the seller's deadline4,695 of 6,509 late orders; the seller was late on the other 27.9%
- 19.0%of orders bought in Mar 2018 arrived late, the worst month7,001 orders that month; the carrier stage averaged 13.0 days, against 8.8 across orders bought Mar to Oct 2017
The Late deliveries page counts 6,381 late of 95,824 delivered orders because it needs a review; this page counts 6,509 late of 95,082 because it needs all three timestamps in order.
Chart 1 of 5
Lead chart · average days in each stage
Late orders spent 27.9 days between the carrier handoff and the customer, against 8.0 for on-time orders.
Which stage do late orders spend their extra days in?
Olist marketplace orders · public data
Download CSV| Stage | On-time orders, average days | Late orders, average days | Extra days of late orders | Share of the extra days (%) | On-time orders, median days | Late orders, median days |
|---|---|---|---|---|---|---|
| Purchase to approval | 0.39 | 0.50 | 0.11 | 0.5 | 0.01 | 0.02 |
| Approval to carrier handoff | 2.65 | 5.57 | 2.92 | 12.8 | 1.79 | 3.07 |
| Carrier handoff to customer | 8.00 | 27.88 | 19.87 | 86.8 | 6.96 | 26.20 |
Chart 2 of 5
Handoff against the seller's deadline
Orders handed to the carrier after the seller's deadline arrived late 20.9% of the time, against 5.4% when the deadline was met.
Does a late handoff by the seller explain the late orders?
Olist marketplace orders · public data
Download CSV| Seller handoff | Orders that arrived late (% of these orders) | Orders | Late orders | Share of all orders (%) | Share of all late orders (%) |
|---|---|---|---|---|---|
| Handed to the carrier by the deadline | 5.4 | 86,383 | 4,695 | 90.9 | 72.1 |
| Handed to the carrier after the deadline | 20.9 | 8,699 | 1,814 | 9.1 | 27.9 |
Chart 3 of 5
When orders got stuck
Lateness spiked in Nov 2017 and Feb to Mar 2018, and the carrier stage ran longer in those months too.
When did orders get stuck most?
Olist marketplace orders · public data
Download CSV| Purchase month | Orders | Orders that arrived late (% of the month's orders) | Carrier stage, average days | Seller missed the deadline (% of the month's orders) |
|---|---|---|---|---|
| 2017-01 | 747 | 2.9 | 9.2 | 22.1 |
| 2017-02 | 1,638 | 3.0 | 9.6 | 17.3 |
| 2017-03 | 2,545 | 4.6 | 9.8 | 6.8 |
| 2017-04 | 2,279 | 6.6 | 11.2 | 7.4 |
| 2017-05 | 3,530 | 3.0 | 8.3 | 7.3 |
| 2017-06 | 3,130 | 3.0 | 8.9 | 8.0 |
| 2017-07 | 3,841 | 2.8 | 8.5 | 7.7 |
| 2017-08 | 4,190 | 2.9 | 8.1 | 6.8 |
| 2017-09 | 4,136 | 4.4 | 8.6 | 7.2 |
| 2017-10 | 4,478 | 4.2 | 8.4 | 7.9 |
| 2017-11 | 7,287 | 12.4 | 11.1 | 12.9 |
| 2017-12 | 5,508 | 7.4 | 11.7 | 10.9 |
| 2018-01 | 7,045 | 5.7 | 10.5 | 10.2 |
| 2018-02 | 6,543 | 14.2 | 13.4 | 12.3 |
| 2018-03 | 7,001 | 19.0 | 13.0 | 8.5 |
| 2018-04 | 6,409 | 4.7 | 8.8 | 6.4 |
| 2018-05 | 6,681 | 6.6 | 8.7 | 6.6 |
| 2018-06 | 5,973 | 1.2 | 6.7 | 6.9 |
| 2018-07 | 5,593 | 3.6 | 6.1 | 9.8 |
| 2018-08 | 6,265 | 6.2 | 5.2 | 9.3 |
Chart 4 of 5
Where the item shipped from
Eleven of twelve seller states ran 2.9% to 7.5% late; Maranhão stood out at 20.1%, on 369 orders.
Do orders from some seller states get stuck more?
Olist marketplace orders · public data
Download CSV| Seller state | Orders | Orders that arrived late (% of the state's orders) | Seller missed the deadline (% of the state's orders) | Carrier stage, average days |
|---|---|---|---|---|
| MA Maranhão | 369 | 20.1 | 32.8 | 13.1 |
| SP São Paulo | 66,556 | 7.5 | 9.2 | 9.2 |
| RJ Rio de Janeiro | 4,083 | 7.3 | 8.5 | 9.3 |
| ES Espírito Santo | 306 | 6.2 | 5.2 | 10.6 |
| PR Paraná | 7,211 | 5.5 | 11.3 | 10.0 |
| DF Distrito Federal | 788 | 5.3 | 8.9 | 9.6 |
| SC Santa Catarina | 3,449 | 5.1 | 8.1 | 10.8 |
| BA Bahia | 541 | 5.0 | 7.8 | 10.5 |
| MG Minas Gerais | 7,444 | 4.9 | 7.8 | 9.7 |
| PE Pernambuco | 394 | 3.8 | 4.1 | 10.7 |
| RS Rio Grande do Sul | 1,894 | 3.2 | 8.6 | 8.1 |
| GO Goiás | 442 | 2.9 | 6.8 | 10.3 |
Chart 5 of 5
The ten biggest categories
Among the ten biggest product categories, late rates sit between 5.6% and 7.7%.
Do some kinds of product get stuck more?
Olist marketplace orders · public data
Download CSV| Product category | Orders | Orders that arrived late (% of the category's orders) | Seller missed the deadline (% of the category's orders) | Carrier stage, average days |
|---|---|---|---|---|
| Bed bath table | 8,749 | 7.7 | 8.2 | 10.0 |
| Health beauty | 8,400 | 7.7 | 9.1 | 9.1 |
| Watches gifts | 5,322 | 7.6 | 7.2 | 10.0 |
| Auto | 3,692 | 7.5 | 7.4 | 9.5 |
| Furniture decor | 5,963 | 7.4 | 12.0 | 9.5 |
| Telephony | 3,997 | 7.3 | 9.8 | 9.5 |
| Sports leisure | 7,329 | 6.7 | 6.9 | 9.6 |
| Computers accessories | 6,327 | 6.6 | 10.4 | 9.6 |
| Toys | 3,685 | 6.5 | 9.3 | 9.0 |
| Housewares | 5,483 | 5.6 | 9.6 | 8.1 |
Limits · Read with care
What this data can't show.
- Why an order was late. The data records when each step happened, not why. A busy week, a remote address, a slow carrier and a late handoff all look alike here.
- Who is responsible for each stage. The files do not say who recorded each date, so "seller stage" and "carrier stage" name the step between two timestamps, not a verdict on either party. There are no carrier names, so the carrier leg may cover several carriers.
- How far each order travelled. There is no customer location in the files used, so the length of the carrier stage cannot be set against distance. Seller state is where an item shipped from.
- Whether a faster stage would have saved the order. These are averages and rates side by side, not a controlled test. They show where the days sit, not what would have changed them.
- What a late order costs. Costs, refunds and repeat orders are not in the data. For what lateness does to the review score, see Late deliveries, which this page does not repeat.
Findings
What the charts say.
For 3,916 of the 6,509 late orders (60.2%), the carrier leg alone took longer than the whole delivery time promised at purchase. For 80.0% of late orders it was longer than 9 in 10 on-time orders' carrier legs (over 15.3 days).
Late orders took 34.0 days from purchase to delivery against 11.1 for on-time orders. Of the 22.9 extra days, 86.8% were between the carrier handoff and the customer, 12.8% between approval and handoff, and 0.5% before approval. Both groups were promised about the same time: 22.6 days for late orders, 23.8 for on-time orders.
Orders handed to the carrier after the seller's deadline arrived late 20.9% of the time, against 5.4% when the deadline was met. Sellers missed the deadline on 9.1% of orders.
Most late orders were handed to the carrier by the seller's deadline: 4,695 of 6,509 (72.1%). If a missed deadline caused the whole gap, about 1,341 late orders (20.6% of all late orders) would trace to it; the data cannot show that it did.
The share of orders that arrived late was 12.4% for Nov 2017, 14.2% for Feb 2018 and 19.0% for Mar 2018, against 1.2% to 7.4% in every other month with 300 or more orders. The carrier stage was longer in those months (11.1 to 13.4 days, against 8.8 across orders bought Mar to Oct 2017), though Dec 2017 also ran 11.7 days with 7.4% late.
Seller state does not separate much: eleven of twelve states ran 2.9% to 7.5% late. Maranhão ran 20.1% on 369 orders, with the seller missing the handoff deadline on 32.8% of them.
Product category does not either: the ten biggest categories ran 5.6% to 7.7% late.
Method
How the figures were built.
Steps
- The file has 99,441 orders. 96,478 are marked delivered; 8 of those have no delivery date and are left out, which leaves 96,470 delivered orders. Orders that were canceled, unavailable, shipped but never marked delivered or not yet shipped are not part of this page.
- Late means delivered 1 or more calendar days after the estimated delivery date shown at purchase, using the same rule as the Late deliveries page: 6,534 of 96,470 delivered orders (6.8%). That page shows 6,381 of 95,824 (6.7%) because it counts only delivered orders that have a review.
- Three stages, from three timestamps: purchase to approval (order_approved_at), approval to handoff to the carrier (order_delivered_carrier_date), and handoff to the customer (order_delivered_customer_date). Days are the exact time difference divided by 86,400 seconds.
- Stage timestamps: I leave 1,388 delivered orders out of the stage tables because a timestamp is missing or out of order (14 with no approval time, 1 with no handoff time, 1,350 with the handoff dated before the approval, 23 delivered before the handoff). 95,082 orders remain, 6,509 of them late (6.8%). Every chart and table below starts from these 95,082 orders.
- The seller's handoff deadline is the order's earliest shipping_limit_date across its items. The seller was late when the carrier handoff time is after that deadline. The handoff time is one date for the whole order.
- 1,223 of the 95,082 orders have more than one seller. I keep them in the stage and deadline views (their earliest deadline is used) and leave them out of the seller state and category views, which need one seller and one category.
- Seller state and product category: groups with fewer than 300 orders are not shown. Product category comes from the products file, with English names from the translation file. 1,504 single-seller orders are left out of the category view: 181 with more than one category or a mix of named and unnamed items, and 1,323 with no category at all.
- Two more figures about the carrier stage of late orders. First, the order's purchase time plus its carrier stage (handoff to customer), as a calendar date, is compared with the estimated delivery date: when it is already later, the carrier stage alone took longer than the whole delivery time promised at purchase (3,916 of 6,509 late orders, 60.2%). Second, the 90th percentile of the on-time orders' carrier stage is 15.3 days (nearest rank, 88,573 on-time orders); 5,205 late orders (80.0%) had a longer carrier stage.
- A rate is the share of the orders in that group. The group is named in every label.
99,441 orders in the file, 96,470 delivered with a delivery date, 95,082 with all three stage timestamps in order. Every figure on this page comes from data/analyses/orders_stuck.json, built from the Olist CSV files; the table under each chart and the Method steps say how.
Checks
- Orders, order items, sellers, products and the category translation match the file sizes Kaggle lists (99,441 orders).
- Late deliveries page reconciled: the 95,824 delivered orders that have a review (using the reviews file credited on the Late deliveries page), counted the way that page counts them, have 6,381 late (6.7%). This page counts all 96,470 delivered orders, reviewed or not, so its late rate is 6.8%.
- Sensitivity: merging approval into the handoff stage (purchase to handoff) keeps the 96,281 delivered orders whose dates are in order from purchase, including those with the handoff dated before the approval; the carrier stage is still 86.8% of the extra days, the same as in the stage tables.
- Every number on this page was recomputed by separate code (verify_stuck.py: plain Python reading the CSV files with the csv module, with no DuckDB and no intermediate files) and matches.
- The four queries below were run in DuckDB 1.5.5 on the Olist files loaded as tables named like the files; each returns the figures shown under it.
- Query 1 returns, for purchase to approval, approval to handoff and handoff to customer, on time or early: 88,573 orders, 0.39, 2.65 and 8.00 days; late: 6,509 orders, 0.50, 5.57 and 27.88 days.
- Query 2 returns by the deadline: 86,383 orders, 4,695 late, 5.4%; after the deadline: 8,699 orders, 1,814 late, 20.9%.
- Query 3 returns 20 months, from Jan 2017 (747 orders, 2.9% late) to Aug 2018 (6,265 orders, 6.2% late).
- Query 4 returns 6,509 late orders, 3,916 (60.2%) with a carrier stage longer than the promise, and 5,205 (80.0%) above the on-time 90th percentile of 15.3 days.
The queries
This series is not in Live SQL. The data file behind every chart is data/analyses/orders_stuck.json, built by data/analyses/scripts/build_stuck.py (prep, DuckDB) and data/analyses/scripts/verify_stuck.py (independent check, plain Python) from the source downloads.
Where the days go: average days per stage, late against on time
-- Where the days go: average days in each stage, late orders against on-time orders
WITH deadline AS ( -- one handoff deadline per order: the earliest across its items
SELECT order_id, min(shipping_limit_date) AS handoff_deadline
FROM olist_order_items
GROUP BY order_id
),
stages AS ( -- delivered orders with all three stage timestamps in order
SELECT o.order_id,
o.order_purchase_timestamp AS purchased,
date_diff('day', o.order_estimated_delivery_date::DATE,
o.order_delivered_customer_date::DATE) > 0 AS is_late,
o.order_delivered_carrier_date > d.handoff_deadline AS seller_late,
epoch(o.order_approved_at - o.order_purchase_timestamp) / 86400 AS days_to_approval,
epoch(o.order_delivered_carrier_date - o.order_approved_at) / 86400 AS days_to_handoff,
epoch(o.order_delivered_customer_date - o.order_delivered_carrier_date) / 86400 AS days_with_carrier
FROM olist_orders o
JOIN deadline d USING (order_id)
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
AND o.order_approved_at IS NOT NULL
AND o.order_delivered_carrier_date >= o.order_approved_at -- also drops a missing handoff date
AND o.order_delivered_customer_date >= o.order_delivered_carrier_date
)
SELECT CASE WHEN is_late THEN 'Late' ELSE 'On time or early' END AS delivery,
count(*) AS orders,
round(avg(days_to_approval), 2) AS purchase_to_approval,
round(avg(days_to_handoff), 2) AS approval_to_handoff,
round(avg(days_with_carrier), 2) AS handoff_to_customer
FROM stages
GROUP BY is_late
ORDER BY is_late
Did the seller meet the handoff deadline, and did the order arrive late?
-- Did the seller meet the handoff deadline, and did the order arrive late?
WITH deadline AS ( -- one handoff deadline per order: the earliest across its items
SELECT order_id, min(shipping_limit_date) AS handoff_deadline
FROM olist_order_items
GROUP BY order_id
),
stages AS ( -- delivered orders with all three stage timestamps in order
SELECT o.order_id,
o.order_purchase_timestamp AS purchased,
date_diff('day', o.order_estimated_delivery_date::DATE,
o.order_delivered_customer_date::DATE) > 0 AS is_late,
o.order_delivered_carrier_date > d.handoff_deadline AS seller_late,
epoch(o.order_approved_at - o.order_purchase_timestamp) / 86400 AS days_to_approval,
epoch(o.order_delivered_carrier_date - o.order_approved_at) / 86400 AS days_to_handoff,
epoch(o.order_delivered_customer_date - o.order_delivered_carrier_date) / 86400 AS days_with_carrier
FROM olist_orders o
JOIN deadline d USING (order_id)
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
AND o.order_approved_at IS NOT NULL
AND o.order_delivered_carrier_date >= o.order_approved_at -- also drops a missing handoff date
AND o.order_delivered_customer_date >= o.order_delivered_carrier_date
)
SELECT CASE WHEN seller_late THEN 'After the deadline' ELSE 'By the deadline' END AS handoff,
count(*) AS orders,
count(*) FILTER (WHERE is_late) AS late_orders,
round(100.0 * count(*) FILTER (WHERE is_late) / count(*), 1) AS pct_of_these_orders_late
FROM stages
GROUP BY seller_late
ORDER BY seller_late
Late orders by purchase month
-- Late orders by purchase month (months with 300 or more orders)
WITH deadline AS ( -- one handoff deadline per order: the earliest across its items
SELECT order_id, min(shipping_limit_date) AS handoff_deadline
FROM olist_order_items
GROUP BY order_id
),
stages AS ( -- delivered orders with all three stage timestamps in order
SELECT o.order_id,
o.order_purchase_timestamp AS purchased,
date_diff('day', o.order_estimated_delivery_date::DATE,
o.order_delivered_customer_date::DATE) > 0 AS is_late,
o.order_delivered_carrier_date > d.handoff_deadline AS seller_late,
epoch(o.order_approved_at - o.order_purchase_timestamp) / 86400 AS days_to_approval,
epoch(o.order_delivered_carrier_date - o.order_approved_at) / 86400 AS days_to_handoff,
epoch(o.order_delivered_customer_date - o.order_delivered_carrier_date) / 86400 AS days_with_carrier
FROM olist_orders o
JOIN deadline d USING (order_id)
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
AND o.order_approved_at IS NOT NULL
AND o.order_delivered_carrier_date >= o.order_approved_at -- also drops a missing handoff date
AND o.order_delivered_customer_date >= o.order_delivered_carrier_date
)
SELECT strftime(purchased, '%Y-%m') AS purchase_month,
count(*) AS orders,
round(100.0 * count(*) FILTER (WHERE is_late) / count(*), 1) AS pct_of_orders_late,
round(avg(days_with_carrier), 1) AS avg_days_with_carrier
FROM stages
GROUP BY 1
HAVING count(*) >= 300
ORDER BY 1
Late orders: the carrier stage against the promise and against on-time orders
-- Late orders: the carrier stage against the promise and against on-time orders
WITH deadline AS ( -- one handoff deadline per order: the earliest across its items
SELECT order_id, min(shipping_limit_date) AS handoff_deadline
FROM olist_order_items
GROUP BY order_id
),
stages AS ( -- delivered orders with all three stage timestamps in order
SELECT o.order_id,
o.order_purchase_timestamp AS purchased,
(o.order_purchase_timestamp + (o.order_delivered_customer_date - o.order_delivered_carrier_date))::DATE
> o.order_estimated_delivery_date::DATE AS carrier_alone_late,
date_diff('day', o.order_estimated_delivery_date::DATE,
o.order_delivered_customer_date::DATE) > 0 AS is_late,
o.order_delivered_carrier_date > d.handoff_deadline AS seller_late,
epoch(o.order_approved_at - o.order_purchase_timestamp) / 86400 AS days_to_approval,
epoch(o.order_delivered_carrier_date - o.order_approved_at) / 86400 AS days_to_handoff,
epoch(o.order_delivered_customer_date - o.order_delivered_carrier_date) / 86400 AS days_with_carrier
FROM olist_orders o
JOIN deadline d USING (order_id)
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
AND o.order_approved_at IS NOT NULL
AND o.order_delivered_carrier_date >= o.order_approved_at -- also drops a missing handoff date
AND o.order_delivered_customer_date >= o.order_delivered_carrier_date
),
p90 AS ( -- 90th percentile of the on-time orders' carrier stage
SELECT quantile_disc(days_with_carrier, 0.9) AS days FROM stages WHERE NOT is_late
)
SELECT count(*) AS late_orders,
count(*) FILTER (WHERE carrier_alone_late) AS carrier_alone_over_promise,
round(100.0 * count(*) FILTER (WHERE carrier_alone_late) / count(*), 1) AS pct_of_late_orders,
count(*) FILTER (WHERE days_with_carrier > (SELECT days FROM p90)) AS over_p90,
round(100.0 * count(*) FILTER (WHERE days_with_carrier > (SELECT days FROM p90)) / count(*), 1) AS pct_over_p90,
round((SELECT days FROM p90), 1) AS p90_days
FROM stages
WHERE is_late
Caveats
- Anonymized orders from a Brazilian marketplace; the delivered orders were bought Sep 2016 to Aug 2018. Timestamps are taken as recorded; the data does not say who recorded each one, and it has no carrier names and no reasons for delay. The dataset's customers file (customer state) is not used here.
- The estimated delivery date is the date Olist showed the customer at purchase. On-time orders arrived on average about 12.8 days before it (23.83 promised, 11.05 taken), so late means later than a cautious promise.
- The handoff and approval timestamps are taken as recorded; the data does not say who recorded each one. 1,350 delivered orders have a handoff dated before the approval (165 of them before the purchase), and I leave them out of the stage tables with 38 others. Only 1.8% of those 1,388 were late, against 6.8% of the orders kept, so leaving them out raises the late rate a little.
- The late rate here is 6.8% of all 96,470 delivered orders. The Late deliveries page says 6.7% because it counts only the 95,824 delivered orders that have a review. The stage tables here use 6,509 late of 95,082, after the timestamp exclusions in Method.
- No customer location is in the files used, so the carrier stage cannot be set against distance. Orders from far away are likely to take longer and to be late more often, and the data cannot separate that from slow carriers.
Releases and revisions
- A fixed public release; it is not revised.
Sources
Data and licence
- Source
- Brazilian E-Commerce Public Dataset by Olist (orders, order items, sellers, products)
- Period
- Orders Sep 2016 to Aug 2018
- Licence
- CC BY-NC-SA 4.0
- Charts
- 5, each with its data table and CSV
- Data file
- data/analyses/orders_stuck.json
- Brazilian E-Commerce Public Dataset by Olist Olist, on Kaggle. Orders, order items, sellers, products and the category translation. CC BY-NC-SA 4.0.
Licence: CC BY-NC-SA 4.0. Credit: Olist. Non-commercial and share-alike. Public data.
Download the chart data
Each CSV is made in your browser from the rows in that chart's table.
04Contact
Want this kind of analysis on your own data?
Open to e-commerce, data and operations roles. Los Angeles or remote.