Modeling Same Day Delivery Failure Risk From Event Gaps

The problem I ran into

I got curious about a very specific failure mode in predictive analytics: same-day delivery (SDD) failures caused by “event gaps.”

In our pipeline, every order is supposed to emit a stream of tracking events (e.g., label_created, picked_up, out_for_delivery). But sometimes events arrive late or not at all. Instead of treating “late tracking” as a logging annoyance, I treated it as a signal: an order that hasn’t produced an expected event within a reasonable time window tends to have a higher probability of missing delivery.

So I built a predictive feature set around time-to-next-event and time gaps between required events, then trained a model to predict failure by a cutoff time (e.g., failure if not delivered by 8pm local time).

To keep this concrete, here’s the exact niche I modeled:

Predict SDD failure risk using only event timing, by generating per-order “expected next event” gap features aligned to an order’s local timeline.

That’s random enough to not show up in typical “predictive analytics overview” posts—and it paid off immediately.

---

Data shape I assumed (and how I verified it)

I started with an event table like this:

• order_id
• event_time (UTC timestamp)
• event_type (string)
• timezone (string like America/Los_Angeles)

And a label table (or derived from events):

• order_id
• delivered_time (UTC)
• sdd_cutoff_time_local (time-of-day or computed timestamp)
• failed (boolean): delivered after cutoff or not delivered by cutoff

The tricky part wasn’t training—it was feature engineering and data observability: I needed to understand exactly when events were missing, duplicated, or out of order.

---

Feature idea: “event gap” predictors

I created features that quantify how long the system “stayed quiet” between important stages.

Example stages (you can map them to your own event taxonomy):

1. label_created
2. picked_up
3. out_for_delivery
4. delivered

For each order, at a reference point in time (say, “start of day” or “label_created”), I compute:

• gap_label_to_pickup_min: minutes between label_created and the first picked_up event after it
• gap_pickup_to_out_for_delivery_min: minutes between those two stages
• has_out_for_delivery: whether that event occurred at all before the cutoff
• gap_out_for_delivery_to_cutoff_min: time from out_for_delivery to cutoff (or null -> “missing”)

Missing events are meaningful, so I don’t impute everything blindly. Instead I:

• track missingness explicitly
• cap extreme gaps
• log what fraction of orders had each gap available

---

Step-by-step: generating features in SQL

I used PostgreSQL-style SQL for clarity. The same logic works in most warehouses with minor syntax tweaks.

1) Compute the first timestamp per event type per order

-- First event timestamp of each stage per order
WITH first_events AS (
  SELECT
    order_id,
    event_type,
    MIN(event_time) AS first_event_time
  FROM tracking_events
  WHERE event_type IN (
    'label_created',
    'picked_up',
    'out_for_delivery',
    'delivered'
  )
  GROUP BY order_id, event_type
),
pivoted AS (
  SELECT
    order_id,
    MAX(CASE WHEN event_type = 'label_created' THEN first_event_time END) AS label_created_time,
    MAX(CASE WHEN event_type = 'picked_up' THEN first_event_time END) AS picked_up_time,
    MAX(CASE WHEN event_type = 'out_for_delivery' THEN first_event_time END) AS out_for_delivery_time,
    MAX(CASE WHEN event_type = 'delivered' THEN first_event_time END) AS delivered_time
  FROM first_events
  GROUP BY order_id
)
SELECT * FROM pivoted;

Why this block exists: I wanted the earliest occurrence of each stage because I’m predicting failure based on the earliest signal that something progressed. If duplicates exist (common in real systems), MIN(event_time) prevents “double counting” from distorting gaps.

---

2) Convert to local time and compute cutoff alignment

Assume your cutoff is 20:00 local time for SDD (8pm). I used the order’s timezone to convert.

WITH first_events AS (
  SELECT
    order_id,
    event_type,
    MIN(event_time) AS first_event_time
  FROM tracking_events
  WHERE event_type IN ('label_created','picked_up','out_for_delivery','delivered')
  GROUP BY order_id, event_type
),
pivoted AS (
  SELECT
    order_id,
    MAX(CASE WHEN event_type = 'label_created' THEN first_event_time END) AS label_created_time_utc,
    MAX(CASE WHEN event_type = 'picked_up' THEN first_event_time END) AS picked_up_time_utc,
    MAX(CASE WHEN event_type = 'out_for_delivery' THEN first_event_time END) AS out_for_delivery_time_utc,
    MAX(CASE WHEN event_type = 'delivered' THEN first_event_time END) AS delivered_time_utc
  FROM first_events
  GROUP BY order_id
),
cutoffs AS (
  SELECT
    p.*,
    -- Convert cutoff to UTC based on local date of label_created
    (
      (date_trunc('day', (p.label_created_time_utc AT TIME ZONE tz.timezone)) 
       + INTERVAL '20 hours')
      AT TIME ZONE tz.timezone
    ) AS cutoff_time_utc
  FROM pivoted p
  JOIN order_metadata tz
    ON tz.order_id = p.order_id
)
SELECT * FROM cutoffs;

Why this block exists: Predictive analytics fails silently when features are calculated in inconsistent time zones. Aligning cutoff based on local date gives you a consistent “same-day” definition.

---

3) Create gap features with explicit missingness

WITH first_events AS (
  SELECT
    order_id,
    event_type,
    MIN(event_time) AS first_event_time
  FROM tracking_events
  WHERE event_type IN ('label_created','picked_up','out_for_delivery','delivered')
  GROUP BY order_id, event_type
),
pivoted AS (
  SELECT
    order_id,
    MAX(CASE WHEN event_type = 'label_created' THEN first_event_time END) AS label_created_time_utc,
    MAX(CASE WHEN event_type = 'picked_up' THEN first_event_time END) AS picked_up_time_utc,
    MAX(CASE WHEN event_type = 'out_for_delivery' THEN first_event_time END) AS out_for_delivery_time_utc,
    MAX(CASE WHEN event_type = 'delivered' THEN first_event_time END) AS delivered_time_utc
  FROM first_events
  GROUP BY order_id
),
cutoffs AS (
  SELECT
    p.*,
    (
      (date_trunc('day', (p.label_created_time_utc AT TIME ZONE tz.timezone)) 
       + INTERVAL '20 hours')
      AT TIME ZONE tz.timezone
    ) AS cutoff_time_utc
  FROM pivoted p
  JOIN order_metadata tz
    ON tz.order_id = p.order_id
),
labeled AS (
  SELECT
    *,
    CASE
      WHEN delivered_time_utc IS NULL THEN 1
      WHEN delivered_time_utc > cutoff_time_utc THEN 1
      ELSE 0
    END AS failed
  FROM cutoffs
),
features AS (
  SELECT
    order_id,
    failed,

    -- Gap from label_created -> picked_up (minutes), only if both exist and order is sane
    CASE
      WHEN label_created_time_utc IS NOT NULL
       AND picked_up_time_utc IS NOT NULL
       AND picked_up_time_utc >= label_created_time_utc
      THEN EXTRACT(EPOCH FROM (picked_up_time_utc - label_created_time_utc)) / 60.0
      ELSE NULL
    END AS gap_label_to_pickup_min,

    CASE
      WHEN picked_up_time_utc IS NOT NULL THEN 1 ELSE 0
    END AS has_picked_up,

    CASE
      WHEN picked_up_time_utc IS NOT NULL
       AND out_for_delivery_time_utc IS NOT NULL
       AND out_for_delivery_time_utc >= picked_up_time_utc
      THEN EXTRACT(EPOCH FROM (out_for_delivery_time_utc - picked_up_time_utc)) / 60.0
      ELSE NULL
    END AS gap_pickup_to_out_for_delivery_min,

    CASE
      WHEN out_for_delivery_time_utc IS NOT NULL THEN 1 ELSE 0
    END AS has_out_for_delivery,

    -- Time from out_for_delivery -> cutoff; missing out_for_delivery is meaningful
    CASE
      WHEN out_for_delivery_time_utc IS NOT NULL
      THEN EXTRACT(EPOCH FROM (cutoff_time_utc - out_for_delivery_time_utc)) / 60.0
      ELSE NULL
    END AS minutes_remaining_after_out_for_delivery,

    -- Extra data observability: detect “impossible ordering”
    CASE
      WHEN out_for_delivery_time_utc IS NOT NULL
       AND picked_up_time_utc IS NOT NULL
       AND out_for_delivery_time_utc < picked_up_time_utc
      THEN 1 ELSE 0
    END AS out_of_order_events_flag
  FROM labeled
)
SELECT *
FROM features;

Why this block exists: This is where predictive analytics meets data quality. Instead of forcing everything into numeric imputation early, I preserved nulls and added explicit flags. Those flags became surprisingly predictive.

---

Observability checks I ran (before training)

I computed these distributions:

• % orders where has_picked_up = 0
• % orders where gap_* is NULL
• % where out_of_order_events_flag = 1
• failure rate grouped by each missingness flag

That’s not “nice to have.” When I saw a spike in out_of_order_events_flag, the model performance dropped until I fixed ingestion ordering.

---

Training a model with Python (working end-to-end)

I exported the features to a pandas DataFrame and trained a model.

I used:

• SimpleImputer to handle nulls
• OneHotEncoder only if I had categorical fields (not shown here)
• RandomForestClassifier for a strong baseline and robustness to non-linearities

Python code

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import Pipeline
from sklearn.impute import SimpleImputer
from sklearn.metrics import roc_auc_score, classification_report
from sklearn.preprocessing import StandardScaler
from sklearn.ensemble import RandomForestClassifier

# Example: load your engineered dataset
# df columns:
# - order_id
# - failed (0/1)
# - gap_label_to_pickup_min (float or NaN)
# - has_picked_up (0/1)
# - gap_pickup_to_out_for_delivery_min (float or NaN)
# - has_out_for_delivery (0/1)
# - minutes_remaining_after_out_for_delivery (float or NaN)
# - out_of_order_events_flag (0/1)
df = pd.read_parquet("sdd_event_gap_features.parquet")

target = "failed"
feature_cols = [
    "gap_label_to_pickup_min",
    "has_picked_up",
    "gap_pickup_to_out_for_delivery_min",
    "has_out_for_delivery",
    "minutes_remaining_after_out_for_delivery",
    "out_of_order_events_flag",
]

X = df[feature_cols]
y = df[target]

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42, stratify=y
)

numeric_cols = [
    "gap_label_to_pickup_min",
    "gap_pickup_to_out_for_delivery_min",
    "minutes_remaining_after_out_for_delivery",
    "out_of_order_events_flag",
    "has_picked_up",
    "has_out_for_delivery",
]

numeric_transformer = Pipeline(steps=[
    # Median imputation for continuous gaps; missingness got captured by has_* flags already
    ("imputer", SimpleImputer(strategy="median")),
    ("scaler", StandardScaler()),
])

preprocess = ColumnTransformer(
    transformers=[
        ("num", numeric_transformer, numeric_cols),
    ],
    remainder="drop"
)

clf = RandomForestClassifier(
    n_estimators=400,
    max_depth=None,
    min_samples_leaf=20,
    random_state=42,
    class_weight="balanced"
)

model = Pipeline(steps=[
    ("preprocess", preprocess),
    ("clf", clf)
])

model.fit(X_train, y_train)

proba = model.predict_proba(X_test)[:, 1]
pred = (proba >= 0.5).astype(int)

print("ROC-AUC:", roc_auc_score(y_test, proba))
print(classification_report(y_test, pred, digits=4))

What I watched during training

• ROC-AUC: should be meaningfully above 0.5
• Calibration: if you plan to trigger operational actions, you want probabilities that behave (I validated with reliability curves)
• Feature importance stability: I re-ran on rolling time windows and compared importances

In my case, the model leaned heavily on:

• has_out_for_delivery
• minutes_remaining_after_out_for_delivery
• gaps in the early stages (label → pickup)

---

The “unexpected win” from data observability

The most useful single signal wasn’t even a gap—it was the out-of-order events flag.

When events were ingested in the wrong sequence, the “gap” features became internally inconsistent. The model learned that those ingestion anomalies correlate with downstream delivery failures (or at least with missing critical tracking).

This is a good example of why I prioritize data observability in real-time AI systems: your model ends up using your pipeline’s truthfulness.

---

Conclusion

I built a predictive model for same-day delivery failure risk using a very specific, niche feature family: event gap features aligned to a local cutoff time, plus explicit missingness and ordering anomaly flags. The key lesson from my tinkering was that predictive analytics isn’t just about algorithms—it’s about making event timing trustworthy, observable, and consistent, so the model learns signal instead of pipeline artifacts.