Failed Delivery Rate
Also known as: Failed First Delivery Attempt Rate, First Attempt Delivery Failure Rate, Failed Delivery Attempt Rate
Failed Delivery Rate is the percentage of shipments out for delivery in the period whose first delivery attempt failed because the recipient was not available, the address was incorrect or undeliverable, the driver could not get access, or the recipient refused the parcel. Unlike Delivery Accuracy Rate (right items reaching the right address intact) and Shipment On-Time Delivery Rate (delivery date against promise date), it isolates failure at the last-mile handover, the main cause of redelivery surcharges, return-to-sender cost and 'where is my order' contacts.
Business Use Case
1️⃣ Carrier Management (Logistics): runs a weekly carrier-by-region scorecard. When a carrier's rate in a delivery region is more than 1.5 points above its trailing 8-week baseline, the team files a performance claim at the next carrier business review and changes the rate-shopping rules to move volume in that region to the next-best carrier. 2️⃣ Checkout Product team: when address_issue accounts for more than 30% of failed first attempts, the team adds address validation and a mandatory apartment/unit prompt at the shipping-information step, then checks the effect in the next monthly readout. 3️⃣ Customer Service: when the daily rate for a region jumps above its control limit, the team triggers a proactive SMS and email with self-service reschedule and redirect-to-pickup-point options. It also increases staffing in the 'where is my order' queue for the next 48 hours. 4️⃣ Finance: multiplies failed first attempts by the contracted redelivery surcharge and return-to-sender cost per carrier each month. The result sets the accrual for last-mile cost overruns and supports surcharge disputes at contract renewal.
Business Criticality
High
Journey / Navigation Area
Generic
Owning Function
Operations
Scope
Global
Formula
Calculation Notes
1️⃣ Grain and denominator: one row per outbound tracking number. A shipment enters the denominator on its first carrier scan normalised to out_for_delivery, dated in the destination's local time zone rather than the warehouse or UTC date. Each package of a split shipment counts separately. Excluded: return and exchange labels, store pickup (BOPIS), ship-to-store orders, and parcels addressed at checkout to a locker or pickup point (none of these has a doorstep attempt). 2️⃣ Numerator: the first attempt outcome is the first attempt-terminal scan that follows the first out-for-delivery scan. The attempt is failed when its normalised reason is recipient_unavailable, address_issue (incorrect, incomplete or undeliverable), access_issue (gated community, secured building, business closed) or refused. Deliveries to a safe place, a neighbour or a mailroom count as successful. A redirect to a pickup point after an attempt counts as a failed first attempt. 3️⃣ Carrier-caused non-attempts are not failures in this KPI: 'delivery not attempted', vehicle breakdown, weather, missort and 'ran out of time'. These shipments stay in the denominator. They are reported through the delivery-exception-occurrence event and Shipment On-Time Delivery Rate, so a carrier's capacity problems are not blamed on customer address quality. 4️⃣ Exception code normalisation: raw UPS, FedEx, USPS, DHL and regional carrier status and exception codes are mapped to the governed Delivery Exception Type taxonomy through a version-controlled mapping table. A first-attempt outcome with an unmapped code counts as a posted outcome, so it stays in the denominator but is excluded from the numerator, and it is flagged. When unmapped codes exceed 2% of first-attempt outcomes for a carrier, that carrier's figures are withheld until the mapping is updated. 5️⃣ Late and duplicate tracking data: carrier webhooks and aggregator feeds can arrive late, duplicated or out of order. Sequence scans by the carrier event timestamp, not the ingestion timestamp. Deduplicate on tracking number + status code + event timestamp. Each day is restated for 72 hours. A shipment with an out-for-delivery scan but no attempt-terminal scan yet is in-flight while that scan is less than 48 hours old relative to the run time, and becomes outcome-pending once 48 hours have passed. Both are left out of the numerator and the denominator until an outcome posts. The pending share (outcome-pending shipments over shipments whose out-for-delivery scan is at least 48 hours old) is published alongside the rate; in-flight shipments do not count towards it. 6️⃣ How it relates to other KPIs: the complement (100 − rate) is the First Attempt Delivery Success Rate. Do not combine this KPI with, or subtract it from, Delivery Accuracy Rate or Shipment On-Time Delivery Rate. A failed first attempt can still be re-delivered inside the promise window, and a late shipment may never have had a failed attempt. 7️⃣ Mix effects: shipments that need an adult signature or age verification, and high-value shipments, fail far more often at the first attempt. Commercial addresses delivered outside business hours map to access_issue. When comparing carriers, regions or periods, break the rate down by signature requirement and by residential versus commercial destination, because changes in shipment mix can hide real changes in carrier performance.
Technical Details
Events
SQL Queries
Google Analytics 4 (BigQuery)
-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Source: carrier tracking events (backend) + OMS shipments. Rerun daily over the trailing window; the last 72 hours are restated.
WITH scans_dedup AS (
SELECT tracking_number, carrier, raw_status_code, event_timestamp
FROM `your-project.logistics.carrier_tracking_events`
WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 100 DAY)
QUALIFY ROW_NUMBER() OVER (PARTITION BY tracking_number, raw_status_code, event_timestamp ORDER BY ingested_at) = 1
),
scans AS (
-- Sequence by carrier event time; raw codes normalised through the governed Delivery Exception Type mapping
SELECT s.tracking_number, s.carrier, s.raw_status_code, s.event_timestamp,
m.normalized_status, m.exception_type, m.is_attempt_terminal,
m.normalized_status IS NULL AS is_unmapped
FROM scans_dedup s
LEFT JOIN `your-project.logistics.carrier_status_code_map` m
ON m.carrier = s.carrier AND m.raw_status_code = s.raw_status_code AND m.is_current_version = TRUE
),
eligible_shipments AS (
-- Outbound doorstep deliveries only: no returns, BOPIS, ship-to-store, lockers or pickup points
SELECT tracking_number, order_id, carrier, carrier_service_level, delivery_region,
destination_type, signature_required, destination_timezone
FROM `your-project.oms.shipments`
WHERE shipment_direction = 'outbound'
AND is_return_label = FALSE
AND fulfillment_method = 'ship_to_address'
AND destination_type IN ('residential', 'commercial')
),
first_ofd AS (
SELECT tracking_number, MIN(event_timestamp) AS first_ofd_ts
FROM scans
WHERE normalized_status = 'out_for_delivery'
GROUP BY tracking_number
),
first_outcome AS (
-- First attempt-terminal (or unmapped) scan after the first out-for-delivery scan
SELECT sc.tracking_number, sc.exception_type, sc.normalized_status, sc.is_unmapped
FROM scans sc
JOIN first_ofd f ON f.tracking_number = sc.tracking_number
WHERE sc.event_timestamp > f.first_ofd_ts
AND (sc.is_attempt_terminal = TRUE OR sc.is_unmapped)
QUALIFY ROW_NUMBER() OVER (PARTITION BY sc.tracking_number ORDER BY sc.event_timestamp) = 1
),
shipment_outcomes AS (
SELECT e.tracking_number, e.carrier, e.carrier_service_level, e.delivery_region,
e.destination_type, e.signature_required,
DATE(f.first_ofd_ts, e.destination_timezone) AS ofd_local_date,
CASE
-- No outcome yet: in_flight while the out-for-delivery scan is under 48h old at run time, pending from 48h on
WHEN o.tracking_number IS NULL AND f.first_ofd_ts > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR) THEN 'in_flight'
WHEN o.tracking_number IS NULL THEN 'pending'
WHEN o.is_unmapped THEN 'unmapped' -- posted outcome: stays in the denominator, never in the numerator
WHEN o.exception_type IN ('recipient_unavailable', 'address_issue', 'access_issue', 'refused', 'redirected_to_pickup_point') THEN 'failed'
ELSE 'not_failed' -- delivered (incl. safe place / neighbour / mailroom) or carrier-caused non-attempt
END AS first_attempt_outcome
FROM eligible_shipments e
JOIN first_ofd f ON f.tracking_number = e.tracking_number
LEFT JOIN first_outcome o ON o.tracking_number = e.tracking_number
),
agg AS (
SELECT ofd_local_date, carrier, carrier_service_level, delivery_region, destination_type, signature_required,
COUNT(*) AS shipments_with_ofd_scan,
COUNTIF(first_attempt_outcome != 'in_flight') AS shipments_ofd_48h_elapsed,
COUNTIF(first_attempt_outcome NOT IN ('pending', 'in_flight')) AS shipments_out_for_delivery,
COUNTIF(first_attempt_outcome = 'failed') AS failed_first_attempts,
COUNTIF(first_attempt_outcome = 'unmapped') AS unmapped_first_outcomes,
COUNTIF(first_attempt_outcome = 'pending') AS outcome_pending_shipments,
COUNTIF(first_attempt_outcome = 'in_flight') AS in_flight_shipments
FROM shipment_outcomes
GROUP BY ofd_local_date, carrier, carrier_service_level, delivery_region, destination_type, signature_required
),
carrier_mapping_health AS (
SELECT ofd_local_date, carrier,
SAFE_DIVIDE(SUM(unmapped_first_outcomes), SUM(shipments_out_for_delivery)) AS carrier_unmapped_share
FROM agg
GROUP BY ofd_local_date, carrier
)
SELECT a.ofd_local_date, a.carrier, a.carrier_service_level, a.delivery_region,
a.destination_type, a.signature_required,
a.shipments_out_for_delivery,
a.failed_first_attempts,
a.unmapped_first_outcomes,
a.outcome_pending_shipments,
a.in_flight_shipments,
ROUND(SAFE_DIVIDE(a.outcome_pending_shipments, a.shipments_ofd_48h_elapsed) * 100, 2) AS outcome_pending_share_pct,
ROUND(c.carrier_unmapped_share * 100, 2) AS carrier_unmapped_share_pct,
IF(c.carrier_unmapped_share > 0.02, NULL,
ROUND(SAFE_DIVIDE(a.failed_first_attempts, a.shipments_out_for_delivery) * 100, 2)) AS failed_delivery_rate_pct
FROM agg a
JOIN carrier_mapping_health c
ON c.ofd_local_date = a.ofd_local_date AND c.carrier = a.carrier
WHERE a.ofd_local_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
ORDER BY a.ofd_local_date DESC, a.carrier, a.delivery_region;Adobe Experience Platform (AEP Query Service)
-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Source: backend record datasets ingested into AEP (carrier tracking events + OMS shipments). Rerun daily; the last 72 hours are restated.
WITH scans_ranked AS (
SELECT tracking_number, carrier, raw_status_code, event_timestamp,
ROW_NUMBER() OVER (PARTITION BY tracking_number, raw_status_code, event_timestamp ORDER BY ingested_at) AS dup_rank
FROM aep_backend_carrier_tracking_events
WHERE event_timestamp >= current_timestamp() - INTERVAL 100 DAYS
),
scans AS (
SELECT s.tracking_number, s.carrier, s.raw_status_code, s.event_timestamp,
m.normalized_status, m.exception_type, m.is_attempt_terminal,
CASE WHEN m.normalized_status IS NULL THEN TRUE ELSE FALSE END AS is_unmapped
FROM scans_ranked s
LEFT JOIN aep_backend_carrier_status_code_map m
ON m.carrier = s.carrier AND m.raw_status_code = s.raw_status_code AND m.is_current_version = TRUE
WHERE s.dup_rank = 1
),
eligible_shipments AS (
SELECT tracking_number, order_id, carrier, carrier_service_level, delivery_region,
destination_type, signature_required, destination_timezone
FROM aep_backend_oms_shipments
WHERE shipment_direction = 'outbound'
AND is_return_label = FALSE
AND fulfillment_method = 'ship_to_address'
AND destination_type IN ('residential', 'commercial')
),
first_ofd AS (
SELECT tracking_number, MIN(event_timestamp) AS first_ofd_ts
FROM scans
WHERE normalized_status = 'out_for_delivery'
GROUP BY tracking_number
),
post_ofd_scans AS (
SELECT sc.tracking_number, sc.exception_type, sc.is_unmapped,
ROW_NUMBER() OVER (PARTITION BY sc.tracking_number ORDER BY sc.event_timestamp) AS outcome_rank
FROM scans sc
JOIN first_ofd f ON f.tracking_number = sc.tracking_number
WHERE sc.event_timestamp > f.first_ofd_ts
AND (sc.is_attempt_terminal = TRUE OR sc.is_unmapped = TRUE)
),
first_outcome AS (
SELECT tracking_number, exception_type, is_unmapped
FROM post_ofd_scans
WHERE outcome_rank = 1
),
shipment_outcomes AS (
SELECT e.tracking_number, e.carrier, e.carrier_service_level, e.delivery_region,
e.destination_type, e.signature_required,
to_date(from_utc_timestamp(f.first_ofd_ts, e.destination_timezone)) AS ofd_local_date,
CASE
-- No outcome yet: in_flight while the out-for-delivery scan is under 48h old at run time, pending from 48h on
WHEN o.tracking_number IS NULL AND f.first_ofd_ts > current_timestamp() - INTERVAL 48 HOURS THEN 'in_flight'
WHEN o.tracking_number IS NULL THEN 'pending'
WHEN o.is_unmapped = TRUE THEN 'unmapped' -- posted outcome: stays in the denominator, never in the numerator
WHEN o.exception_type IN ('recipient_unavailable', 'address_issue', 'access_issue', 'refused', 'redirected_to_pickup_point') THEN 'failed'
ELSE 'not_failed'
END AS first_attempt_outcome
FROM eligible_shipments e
JOIN first_ofd f ON f.tracking_number = e.tracking_number
LEFT JOIN first_outcome o ON o.tracking_number = e.tracking_number
),
agg AS (
SELECT ofd_local_date, carrier, carrier_service_level, delivery_region, destination_type, signature_required,
COUNT(*) AS shipments_with_ofd_scan,
SUM(CASE WHEN first_attempt_outcome <> 'in_flight' THEN 1 ELSE 0 END) AS shipments_ofd_48h_elapsed,
SUM(CASE WHEN first_attempt_outcome NOT IN ('pending', 'in_flight') THEN 1 ELSE 0 END) AS shipments_out_for_delivery,
SUM(CASE WHEN first_attempt_outcome = 'failed' THEN 1 ELSE 0 END) AS failed_first_attempts,
SUM(CASE WHEN first_attempt_outcome = 'unmapped' THEN 1 ELSE 0 END) AS unmapped_first_outcomes,
SUM(CASE WHEN first_attempt_outcome = 'pending' THEN 1 ELSE 0 END) AS outcome_pending_shipments,
SUM(CASE WHEN first_attempt_outcome = 'in_flight' THEN 1 ELSE 0 END) AS in_flight_shipments
FROM shipment_outcomes
GROUP BY ofd_local_date, carrier, carrier_service_level, delivery_region, destination_type, signature_required
),
carrier_mapping_health AS (
SELECT ofd_local_date, carrier,
CAST(SUM(unmapped_first_outcomes) AS DOUBLE) / NULLIF(SUM(shipments_out_for_delivery), 0) AS carrier_unmapped_share
FROM agg
GROUP BY ofd_local_date, carrier
)
SELECT a.ofd_local_date, a.carrier, a.carrier_service_level, a.delivery_region,
a.destination_type, a.signature_required,
a.shipments_out_for_delivery,
a.failed_first_attempts,
a.unmapped_first_outcomes,
a.outcome_pending_shipments,
a.in_flight_shipments,
ROUND(CAST(a.outcome_pending_shipments AS DOUBLE) / NULLIF(a.shipments_ofd_48h_elapsed, 0) * 100, 2) AS outcome_pending_share_pct,
ROUND(c.carrier_unmapped_share * 100, 2) AS carrier_unmapped_share_pct,
CASE WHEN c.carrier_unmapped_share > 0.02 THEN NULL
ELSE ROUND(CAST(a.failed_first_attempts AS DOUBLE) / NULLIF(a.shipments_out_for_delivery, 0) * 100, 2)
END AS failed_delivery_rate_pct
FROM agg a
JOIN carrier_mapping_health c
ON c.ofd_local_date = a.ofd_local_date AND c.carrier = a.carrier
WHERE a.ofd_local_date BETWEEN date_sub(current_date(), 90) AND current_date()
ORDER BY a.ofd_local_date DESC, a.carrier, a.delivery_region;Source Data
Carrier Tracking Events (Backend) + Order Management (OMS)
Dependencies
Prerequisite: List the dependencies required for this KPI
Events:
Dimensions:
Report Attributes
Shipping Carrier Carrier Service Level Delivery Exception Type (failure reason) Delivery Region Destination Type (Residential / Commercial) Fulfillment Node Shipping Tier Signature Requirement Attempt Day of Week
Recommended Reporting Views
Segment Eligibility
false
Governance
Governance
GitHub
catalog/kpis/failed-delivery-rate.yml