Delivery Reliability & Bottleneck Analysis

Orders analyzed

94.48K

Unique orders after normalizing the item-level source to order grain.

Unique orders after normalizing the item-level source to order grain.Source: Delivery summary metricsTable: dashboard_summary

Order-level delivery and review totals after item-level normalization.

SELECT * FROM dashboard_summary

Late-delivery rate

Share delivered after the estimated delivery timestamp.

Over 24h late 6.7%

Share delivered after the estimated delivery timestamp.Source: Delivery summary metricsTable: dashboard_summary

Order-level delivery and review totals after item-level normalization.

SELECT * FROM dashboard_summary

Median late days

5.79

Delay duration among late orders only.

90th percentile 21.04

Delay duration among late orders only.Source: Delivery summary metricsTable: dashboard_summary

Order-level delivery and review totals after item-level normalization.

SELECT * FROM dashboard_summary

Review-score gap

-1.73

Late-order average rating minus on-time average rating; association, not causation.

Late-order average 2.57

Late-order average rating minus on-time average rating; association, not causation.Source: Delivery summary metricsTable: dashboard_summary

Order-level delivery and review totals after item-level normalization.

SELECT * FROM dashboard_summary

Monthly late-delivery rate

Source: Monthly delivery reliabilityTable: monthly

Monthly purchase cohorts used to inspect the timing and scale of reliability spikes.

SELECT * FROM monthly ORDER BY month

Monthly late-delivery rate data
Purchase month	Late-delivery rate	Orders	Late orders	Median handoff days	Average review
2017-01	3%	731	22	2.47	4.2
2017-02	2.8%	1.59K	44	2.34	4.2
2017-03	5.5%	2.48K	135	1.98	4.19
2017-04	7.7%	2.23K	172	2.69	4.14
2017-05	3.5%	3.44K	119	1.98	4.24
2017-06	3.8%	3.06K	116	2.19	4.23
2017-07	3.4%	3.8K	129	2.28	4.26
2017-08	3.3%	4.1K	135	2.19	4.32
2017-09	5.1%	4.06K	206	2.35	4.27
2017-10	5.2%	4.4K	227	2.29	4.2
2017-11	14%	7.14K	1K	3.17	3.99
2017-12	8.2%	5.33K	436	2.83	4.1
2018-01	6.4%	6.85K	437	2.42	4.12
2018-02	16%	6.4K	1.02K	2.52	3.88
2018-03	21.1%	6.83K	1.44K	2.38	3.81
2018-04	5.2%	6.69K	349	2.06	4.21
2018-05	8.2%	6.69K	548	1.8	4.24
2018-06	1.4%	6.05K	83	1.76	4.31
2018-07	4.4%	6.07K	265	1.79	4.32
2018-08	10.3%	6.3K	648	1.76	4.31

Late-delivery rate by purchase-to-carrier time

Source: Purchase-to-carrier speed bandsTable: ship_speed

Order-level late rates grouped by elapsed time from purchase to carrier handoff.

SELECT * FROM ship_speed ORDER BY band_order

Late-delivery rate by purchase-to-carrier time data
Purchase-to-carrier time	Late-delivery rate	Orders	Average review	Low-review rate
Within 2 days	5%	42.36K	4.28	10.1%
2-4 days	7.3%	27.74K	4.18	12.4%
4-7 days	9.8%	16.64K	4.07	14.4%
Over 7 days	22.8%	7.74K	3.64	24.6%

Low-review rate by delivery status

Source: Delivery status and review outcomesTable: review_status

Review outcomes compared between late and on-time orders.

SELECT * FROM review_status ORDER BY sort_order

Low-review rate by delivery status data
Delivery status	Reviews scored 1-2	Review-eligible orders	Average review
On time	9.2%	86.77K	4.3
Late	54%	7.52K	2.57

Excess late orders by high-volume seller

Source: Seller opportunity rankingTable: seller_opportunity

High-volume sellers ranked by late orders above the network-rate expectation.

SELECT * FROM seller_opportunity ORDER BY excess_late_orders DESC LIMIT 12

Excess late orders by high-volume seller data
Anonymized seller	Excess late orders	Seller state	Orders	Late-delivery rate	Median handoff days	Over four-day handoff
Seller 06A2C3	56.34	MA	384	22.7%	2.87	40.9%
Seller 4A3CA9	55.79	SP	1.66K	11.4%	2.18	23.6%
Seller 4869F7	39.94	SP	1.1K	11.6%	2.39	19.2%
Seller 1F50F9	36.5	SP	1.36K	10.7%	3.96	49.4%
Seller 816025	30.98	SP	351	16.8%	2.8	38.8%
Seller 88460E	28.96	PR	226	20.8%	6.95	65.9%
Seller EA8482	28.38	SP	1.11K	10.5%	3.24	37.3%
Seller 7D13FC	24.49	SP	545	12.5%	3.44	41.3%
Seller 8B321B	23.11	SP	913	10.5%	1.97	16.7%
Seller 1025F0	21.38	SP	872	10.4%	3.45	41.1%
Seller E5A343	19.9	SP	139	22.3%	3.87	46.8%
Seller 7C67E1	19.6	SP	957	10%	11.35	97.6%

Excess late orders by seller-to-customer route

Source: Seller-to-customer route rankingTable: route_opportunity

Seller-state to customer-state routes ranked by excess late volume.

SELECT * FROM route_opportunity ORDER BY excess_late_orders DESC LIMIT 12

Excess late orders by seller-to-customer route data
Seller state to customer state	Excess late orders	Orders	Late-delivery rate	Median handoff days	Over four-day handoff
SP to RJ	587.28	7.85K	15.5%	2.31	27.9%
SP to BA	154.72	2.23K	14.9%	2.34	27%
SP to ES	81.71	1.42K	13.7%	2.41	26.8%
SP to CE	66.55	945	15%	2.2	27.7%
SP to SC	62.93	2.24K	10.8%	2.26	27.7%
SP to MA	62.84	478	21.1%	2.48	26.6%
PR to RJ	54.59	932	13.8%	2.26	26.6%
SP to AL	44.12	249	25.7%	2.71	30.1%
SP to PI	32.61	318	18.2%	2.29	28.9%
SP to PA	31.95	652	12.9%	2.49	26.2%
SP to MS	31.04	463	14.7%	2.26	28.5%
SP to PE	29.18	1.1K	10.6%	2.28	26.5%

Excess late orders by customer state

Source: State opportunity rankingTable: state_opportunity

Customer states ranked by excess late volume.

SELECT * FROM state_opportunity ORDER BY excess_late_orders DESC LIMIT 12

Excess late orders by customer state data
Customer state	Excess late orders	Orders	Late-delivery rate	Average review
RJ	640.4	12.03K	13.3%	3.97
BA	183.48	3.19K	13.7%	3.93
CE	89.41	1.26K	15.1%	3.95
MA	80.79	704	19.5%	3.83
ES	74.16	1.95K	11.8%	4.08
AL	59.86	390	23.3%	3.85
SC	52.44	3.46K	9.5%	4.14
PE	40.37	1.56K	10.6%	4.09
PI	36.96	464	16%	3.99
PA	36.95	915	12%	3.91
MS	24.91	690	11.6%	4.16
SE	23.49	332	15.1%	3.9

Excess late orders by product category

Source: Category opportunity rankingTable: category_opportunity

Product categories ranked by excess late volume.

SELECT * FROM category_opportunity ORDER BY excess_late_orders DESC LIMIT 12

Excess late orders by product category data
Product category	Excess late orders	Orders	Late-delivery rate	Average review
Health Beauty	71.32	8.6K	8.8%	4.23
Bed Bath Table	55.34	9.18K	8.6%	4
Electronics	45.57	2.5K	9.8%	4.12
Baby	28.49	2.79K	9%	4.1
Furniture Decor	25.3	6.26K	8.4%	4.06
Watches Gifts	20.73	5.45K	8.4%	4.12
Auto	20.34	3.79K	8.5%	4.15
Telephony	20.14	4.07K	8.5%	4.05
Office Furniture	12.68	1.24K	9%	3.64
Construction Tools Construction	6.64	731	8.9%	4.12
Musical Instruments	5.7	605	8.9%	4.23
Consoles Games	-0.56	1.01K	7.9%	4.18

High-volume seller delivery detail

Source: Seller delivery-risk detailTable: seller_detail

Auditable seller-level delivery metrics for complete single-seller orders.

SELECT * FROM seller_detail ORDER BY late_rate DESC, orders DESC

High-volume seller delivery detail
Seller	State	Orders	Late orders	Late rate	Excess late orders	Median handoff days	Over four-day handoff	Average review
Seller 06A2C3	MA	384	87	22.7%	+56.34	2.87	40.9%	4.02
Seller 4A3CA9	SP	1.66K	188	11.4%	+55.79	2.18	23.6%	3.91
Seller 4869F7	SP	1.1K	128	11.6%	+39.94	2.39	19.2%	4.18
Seller 1F50F9	SP	1.36K	145	10.7%	+36.5	3.96	49.4%	4.18
Seller 816025	SP	351	59	16.8%	+30.98	2.8	38.8%	4.03
Seller 88460E	PR	226	47	20.8%	+28.96	6.95	65.9%	3.53
Seller EA8482	SP	1.11K	117	10.5%	+28.38	3.24	37.3%	4.05
Seller 7D13FC	SP	545	68	12.5%	+24.49	3.44	41.3%	4.08
Seller 8B321B	SP	913	96	10.5%	+23.11	1.97	16.7%	4.12
Seller 1025F0	SP	872	91	10.4%	+21.38	3.45	41.1%	4.05
Seller E5A343	SP	139	31	22.3%	+19.9	3.87	46.8%	3.88
Seller 7C67E1	SP	957	96	10%	+19.6	11.35	97.6%	3.51
Seller 1835B5	SP	372	49	13.2%	+19.3	1.71	11.3%	3.77
Seller F7BA60	SP	213	34	16%	+16.99	2.43	18.3%	4.26
Seller 855668	SP	292	40	13.7%	+16.69	3.85	47.3%	3.88

197 results · Showing first 15

State delivery-risk detail

Source: State delivery-risk detailTable: state_detail

Auditable customer-state delivery metrics.

SELECT * FROM state_detail ORDER BY late_rate DESC, orders DESC

State delivery-risk detail
State	Orders	Late orders	Late rate	Excess late orders	Average review	Median ship days
AL	390	91	23.3%	+59.86	3.85	2.69
MA	704	137	19.5%	+80.79	3.83	2.5
PI	464	74	16%	+36.96	3.99	2.25
CE	1.26K	190	15.1%	+89.41	3.95	2.2
SE	332	50	15.1%	+23.49	3.9	2.61
BA	3.19K	438	13.7%	+183.48	3.93	2.31
RJ	12.03K	1.6K	13.3%	+640.4	3.97	2.21
TO	270	35	13%	+13.44	4.15	2.48
PA	915	110	12%	+36.95	3.91	2.47
ES	1.95K	230	11.8%	+74.16	4.08	2.4
MS	690	80	11.6%	+24.91	4.16	2.22
PB	504	55	10.9%	+14.76	4.06	2.73
RN	465	50	10.8%	+12.88	4.15	2.38
PE	1.56K	165	10.6%	+40.37	4.09	2.22
SC	3.46K	329	9.5%	+52.44	4.14	2.27

23 results · Showing first 15

What the evidence supports

Late delivery was episodic: the rate rose to 14.0% in Nov 2017, 16.0% in Feb 2018, and 21.1% in Mar 2018. Monthly median handoff time stayed within 1.8-3.2 days and was 2.4 days in the worst month. At order level, handoff after seven days flagged a 22.8% late rate versus 5.0% within two days. That makes handoff age useful for escalation, but insufficient to explain the time-series spikes.

Three actions

Apply seller-neutral handoff alerts at four days and escalation at seven, then rank the queue by excess late-order volume.
Investigate the Nov 2017 and Feb-Mar 2018 spikes with promised-service windows, carrier scans, warehouse events, and seller deadlines before assigning root cause.
Cross-audit the SP to RJ route, RJ deliveries, and the Health Beauty category, then contact customers before likely promise-date misses.

Limits

This is descriptive, not causal. The anonymized source covers Brazilian orders from 2016-2018 and lacks promised-service tiers, carrier scans, warehouse events, and last-mile checkpoints. The handoff bands are therefore operating thresholds, not discovered causes. Seller analysis excludes multi-seller orders for unambiguous attribution, and conflicting review scores are excluded from review comparisons.

Sources

Delivery summary metricsoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Order-level delivery and review totals after item-level normalization.
SQL query
```
SELECT * FROM dashboard_summary
```
Monthly delivery reliabilityoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Monthly purchase cohorts used to inspect the timing and scale of reliability spikes.
SQL query
```
SELECT * FROM monthly ORDER BY month
```
Purchase-to-carrier speed bandsoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Order-level late rates grouped by elapsed time from purchase to carrier handoff.
SQL query
```
SELECT * FROM ship_speed ORDER BY band_order
```
Delivery status and review outcomesoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Review outcomes compared between late and on-time orders.
SQL query
```
SELECT * FROM review_status ORDER BY sort_order
```
Seller opportunity rankingoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
High-volume sellers ranked by late orders above the network-rate expectation.
SQL query
```
SELECT * FROM seller_opportunity ORDER BY excess_late_orders DESC LIMIT 12
```
Seller delivery-risk detailoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Auditable seller-level delivery metrics for complete single-seller orders.
SQL query
```
SELECT * FROM seller_detail ORDER BY late_rate DESC, orders DESC
```
Seller-to-customer route rankingoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Seller-state to customer-state routes ranked by excess late volume.
SQL query
```
SELECT * FROM route_opportunity ORDER BY excess_late_orders DESC LIMIT 12
```
State opportunity rankingoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Customer states ranked by excess late volume.
SQL query
```
SELECT * FROM state_opportunity ORDER BY excess_late_orders DESC LIMIT 12
```
Category opportunity rankingoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Product categories ranked by excess late volume.
SQL query
```
SELECT * FROM category_opportunity ORDER BY excess_late_orders DESC LIMIT 12
```
State delivery-risk detailoutputs/analysis.sqlite · SQLite · 2026-08-31T06:53:31Z
Auditable customer-state delivery metrics.
SQL query
```
SELECT * FROM state_detail ORDER BY late_rate DESC, orders DESC
```
Brazilian E-Commerce Public Dataset by Olist