You’re the analytics lead for Uber’s last‑mile delivery product in the UK (think: grocery + convenience delivery). The London market averages ~450k weekly active consumers, ~35k weekly active couriers, and processes ~1.8M delivery tasks/week (pickup, in-store substitution, dropoff, photo proof, etc.). Task completion is a critical operational KPI because it drives on-time delivery, refunds, courier earnings, and repeat purchase.
Last week (Mon–Sun), the task completion rate in London dropped from 96.2% to 93.4% (−2.8pp). Other UK cities (Manchester, Birmingham, Glasgow) were flat. Operations leadership is asking within 48 hours:
Assume there was a mobile app release mid-week that changed the “Confirm Pickup” screen for couriers, and London also experienced two days of heavy rain.
Provide a structured investigation plan and the initial metric framework you would use.
You have access to a Snowflake warehouse with the following sources:
| Table / Log | What it contains | Grain |
|---|---|---|
delivery_tasks | task_id, order_id, city, zone, task_type, created_at, scheduled_at, merchant_id, courier_id | task |
task_events | task_id, event_type (created/accepted/started/completed/failed/canceled/reassigned), event_ts, failure_code | event |
courier_app_sessions | courier_id, session_start, session_end, app_version, os, device_model, network_type, crash_count | session |
orders | order_id, basket_value, promised_eta, delivered_at, cancel_reason, refund_amount | order |
merchant_dim | merchant_id, vertical (grocery/convenience), store_size, avg_prep_time | merchant |
courier_supply_hourly | city, zone, hour, online_couriers, active_deliveries, surge_multiplier | hour-zone |
incident_log | service_name, start_ts, end_ts, severity, impacted_region | incident |
weather_hourly | city, hour, precipitation_mm, temperature_c | hour |
Constraints: you have 2 days to deliver a readout. You can request one additional dataset if necessary, but assume engineering bandwidth is limited.