HomeDashboardOrders stuck

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?

The time from purchase to delivery splits into three stages at the timestamps in the data: purchase to approval, approval to handoff to the carrier, and handoff to the customer. Late orders took 34.0 days in all against 11.1 for on-time orders, 22.9 extra days. The carrier stage holds 19.9 of them (86.8% of the extra days), the seller stage 2.9 (12.8%) and approval 0.1 (0.5%); the shares add to 100% to rounding. Medians say the same: 26.2 days with the carrier against 7.0. The promise made at purchase was nearly the same, 22.6 days for late orders and 23.8 for on-time orders. The carrier stage is 72.4% of on-time orders' average time (8.0 of 11.05 days), so its 86.8% share of the extra days is more than its usual share. For 3,916 of the 6,509 late orders (60.2%), the carrier stage alone ran past the estimated delivery date (see Method).

Olist marketplace orders · public data

Download CSV
Stages: 3 rows. 95,082 delivered orders with all three timestamps in order, 6,509 of them late. Late means delivered 1 or more calendar days after the estimated date shown at purchase. Days are exact time differences. The Late deliveries page counts 6,381 late among the 95,824 delivered orders with a review; Method explains the difference.
StageOn-time orders, average daysLate orders, average daysExtra days of late ordersShare of the extra days (%)On-time orders, median daysLate orders, median days
Purchase to approval0.390.500.110.50.010.02
Approval to carrier handoff2.655.572.9212.81.793.07
Carrier handoff to customer8.0027.8819.8786.86.9626.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?

Every item has a deadline for the seller to hand it to the carrier; for an order I use the earliest. Sellers met it for 90.9% of orders and missed it for 9.1%. The missed ones arrived late 20.9% of the time, against 5.4%. Even so, 4,695 of the 6,509 late orders (72.1%) were handed over by the deadline. If the missed-deadline orders had been late as rarely as the others, about 1,341 fewer orders would have been late (20.6% of all late orders); that is what the gap would be worth if the late handoff caused all of it, which the data cannot show.

Olist marketplace orders · public data

Download CSV
Seller deadline: 2 rows. Each bar is the share of the orders in that group that arrived late. The handoff time is one date for the whole order. Orders with more than one seller use their earliest deadline. 95,082 delivered orders.
Seller handoffOrders that arrived late (% of these orders)OrdersLate ordersShare of all orders (%)Share of all late orders (%)
Handed to the carrier by the deadline5.486,3834,69590.972.1
Handed to the carrier after the deadline20.98,6991,8149.127.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?

Each bar is the share of the orders bought that month that arrived late, for months with 300 or more orders. Orders bought in Nov 2017 arrived late 12.4% of the time, in Feb 2018 14.2% and in Mar 2018 19.0%; every other month was 1.2% to 7.4%. The carrier stage averaged 11.1, 13.4 and 13.0 days in those three months, against 8.8 days across all orders bought Mar to Oct 2017, the eight months before the Nov 2017 rise. Sellers missed the handoff deadline more often in Nov 2017 and Feb 2018 (12.9% and 12.3% of the month's orders) than in Mar to Oct 2017 (6.8% to 8.0%); in Mar 2018 it was 8.5%.

Olist marketplace orders · public data

Download CSV
By month: 20 rows. Months with fewer than 300 orders are left out (3 months, 263 orders, all in late 2016). Highlighted bars are above 10%. Month of purchase, not of delivery.
Purchase monthOrdersOrders that arrived late (% of the month's orders)Carrier stage, average daysSeller missed the deadline (% of the month's orders)
2017-017472.99.222.1
2017-021,6383.09.617.3
2017-032,5454.69.86.8
2017-042,2796.611.27.4
2017-053,5303.08.37.3
2017-063,1303.08.98.0
2017-073,8412.88.57.7
2017-084,1902.98.16.8
2017-094,1364.48.67.2
2017-104,4784.28.47.9
2017-117,28712.411.112.9
2017-125,5087.411.710.9
2018-017,0455.710.510.2
2018-026,54314.213.412.3
2018-037,00119.013.08.5
2018-046,4094.78.86.4
2018-056,6816.68.76.6
2018-065,9731.26.76.9
2018-075,5933.66.19.8
2018-086,2656.25.29.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?

Seller state is where an item shipped from, not where it was delivered: the files used here have no customer location, so distance is not measured. 12 of the 22 states that ship these orders have 300 or more orders and are shown. In Maranhão the seller missed the handoff deadline on 32.8% of orders, against 9.2% in São Paulo, which ships 66,556 orders.

Olist marketplace orders · public data

Download CSV
Seller state: 12 rows. Orders with exactly one seller (93,859 orders). States with fewer than 300 orders are left out. A state's rate rests on its own orders only, so small states move a lot.
Seller stateOrdersOrders that arrived late (% of the state's orders)Seller missed the deadline (% of the state's orders)Carrier stage, average days
MA Maranhão36920.132.813.1
SP São Paulo66,5567.59.29.2
RJ Rio de Janeiro4,0837.38.59.3
ES Espírito Santo3066.25.210.6
PR Paraná7,2115.511.310.0
DF Distrito Federal7885.38.99.6
SC Santa Catarina3,4495.18.110.8
BA Bahia5415.07.810.5
MG Minas Gerais7,4444.97.89.7
PE Pernambuco3943.84.110.7
RS Rio Grande do Sul1,8943.28.68.1
GO Goiás4422.96.810.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?

The ten categories with the most orders, each between 3,685 and 8,749 orders. Their late rates sit within 2.1 points of each other, around the overall 6.8%. Across all 31 categories with 300 or more orders the range is 4.3% to 12.1%, but the small ones move a lot on a few orders.

Olist marketplace orders · public data

Download CSV
Product category: 10 rows. Orders with one seller and one product category (92,355 orders). Category names are the English names from the dataset's translation file. Orders with more than one category, or an item with no category, are left out.
Product categoryOrdersOrders that arrived late (% of the category's orders)Seller missed the deadline (% of the category's orders)Carrier stage, average days
Bed bath table8,7497.78.210.0
Health beauty8,4007.79.19.1
Watches gifts5,3227.67.210.0
Auto3,6927.57.49.5
Furniture decor5,9637.412.09.5
Telephony3,9977.39.89.5
Sports leisure7,3296.76.99.6
Computers accessories6,3276.610.49.6
Toys3,6856.59.39.0
Housewares5,4835.69.68.1

Limits · Read with care

What this data can't show.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.

  1. 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).

    Chart 1

  2. 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.

    Chart 1

  3. 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.

    Chart 2

  4. 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.

    Chart 2

  5. 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.

    Chart 3

  6. 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.

    Chart 4

  7. Product category does not either: the ten biggest categories ran 5.6% to 7.7% late.

    Chart 5

Method

How the figures were built.

Steps

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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.
  9. 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

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.