Case 032026 · Supply chain risk

Where are shipments being lost, and which stage is responsible?

Two years of shipment, transport, storage and delivery records from a Kenyan third-party logistics operation — six tables, joined and attributed stage by stage.

PythonDeepnoteRisk analysis

At a glance

Shipments (2024–2025): 9,600
Transport events: 48,391
Cities served: 6
Connected tables: 6

The verdict

Storage is the single largest loss stage — 53.9% of total perishable loss. Routine transit and delivery with no breakdown accounts for ~34.5%, and breakdown-related transit for only ~11.6%.

And the safeguard PHL already pays for isn't working: on identical perishable cargo, refrigerated vehicles lost 5.14% against ambient's 4.68% — marginally worse, with vehicle assignment and fleet age both ruled out as explanations.

Stage 1

Business context

Executive summary

PHL Agri-Logistics is a third-party logistics provider operating in Kenya, contracted by other businesses to move and temporarily store agricultural and food products. PHL does not buy, sell, or own the goods it handles — it is hired for the movement and short-term custody of shipments between origin and destination. Over the past two years PHL has scaled up its client base and shipment volume, but delivery delays and product losses have become frequent enough that clients are starting to question whether PHL's operations are reliable. This analysis was commissioned to determine where in PHL's handling process the failures are actually occurring, because the business could describe the symptom (late or spoiled shipments) but not the cause.

Company overview

PHL operates a pickup, temporary storage, and delivery service across six Kenyan cities: Nairobi, Mombasa, Kisumu, Meru, Nakuru, and Eldoret. Clients hand off goods to PHL at an origin point, PHL may hold the shipment briefly at a handling facility, and PHL is responsible for delivering it to the client's destination within an agreed window. The company runs a mixed fleet, some vehicles temperature-controlled and some ambient, and handles both perishable products (vegetables, dairy, fruit) and non-perishable products (maize, beans, flour, cereals).

Industry context

Kenya's agricultural logistics sector sits between producers and the point of sale, and it is where a large share of value is lost before goods ever reach a buyer. Poor road infrastructure, inconsistent cold-chain access, and fragmented handling between multiple parties are well-documented contributors to spoilage and waste in the sector. PHL operates as a specialist mover rather than a producer, meaning its commercial reputation depends entirely on reliability metrics: on-time delivery and product condition on arrival.

Business model

PHL earns revenue by contract, charging clients per shipment or under standing agreements, priced by distance, volume, and whether temperature control is required. Its customers are businesses that need agricultural goods moved but do not want to run their own transport and storage operations.

Stakeholders

Customers: FoodMarts, wholesalers, restaurants, institutional buyers, cooperatives, and individuals who contract PHL to move goods.

Operations team: manages pickup scheduling, vehicle assignment, and storage facility use.

PHL leadership: accountable to clients for service-level performance; commissioned this analysis.

Clients' end customers: not PHL's direct stakeholder, but late or spoiled deliveries damage PHL's clients' relationships with them, which is why PHL is under pressure.

Current situation & problem statement

PHL has been operating for two years with growing shipment volume. Client complaints have increased around two issues: shipments arriving later than the agreed window, and shipments arriving with product loss, primarily on perishable goods. Leadership did not have a clear answer to which part of the process was responsible — route, vehicle, storage, or a combination that depends on product type. Treating every late or spoiled shipment as the same kind of failure was preventing any targeted fix.

Why analysis matters

Fixing the wrong part of the operation wastes money and does not reduce complaints. Data analysis was needed to separate correlation from cause across the stages PHL actually controls, and to give leadership a defensible basis for where to act first.

Who commissioned this analysis

PHL's operations leadership, not sales or marketing. The mandate was internal and operational: identify where product is failing inside PHL's own process, not how to sell the service better.

Assumption

Assumption: PHL's operational logs are assumed to be captured consistently across shipment, transport, storage, and delivery stages, even though in reality this kind of data is often incomplete. Any gaps or inconsistencies uncovered during data understanding are documented as limitations rather than treated as errors.

Expected business value

A clear, evidence-based picture of which routes, vehicle types, and storage practices are driving delays and losses, and which single stage of the handling chain accounts for the largest share of the problem — enough for leadership to prioritize one or two operational fixes instead of investing broadly.

Stage 2

Data understanding

Data overview

PHL does not run a database. Operationally, the business is held together by Excel workbooks and Google Sheets maintained by different teams: dispatch keeps a shipment log, each storage facility keeps its own handling sheet, delivery reconciliation happens separately and often weeks behind, and vehicle records sit in a static file rarely updated by the fleet coordinator. None of these systems communicate with each other.

Data collection & consolidation

Each source was pulled into Power Query, standardized, and merged by shipment reference before export. Shipment references that appeared in one file but not another were flagged rather than dropped silently. Proper surrogate keys were assigned once merged, since the original sheet references were not reliably unique. Six clean CSVs were exported, matching the six-table relational structure below.

Assumption: Chronologically impossible timestamps (e.g., delivery logged before departure) were flagged and excluded from time-based calculations rather than corrected, since there was no reliable way to determine which timestamp was actually wrong.

Data dictionary

Table	Size	Fields
customers	220 rows	customer_id, customer_name, customer_type (FoodMart, Wholesaler, Restaurant, Institutional Buyer, Cooperative, Individual), city, county.
vehicles	52 rows	vehicle_id, vehicle_type (Refrigerated Truck, Box Truck, Flatbed Truck, Pickup), capacity_kg, temperature_controlled (Yes/No), acquisition_year.
shipments	9,600 rows	shipment_id, customer_id, shipment_date, origin, destination, product_category, product, perishable_flag, quantity_kg, planned_delivery, actual_delivery, status (Delivered, Delivered Late, Partially Rejected). Central fact table, Jan 2024–Dec 2025.
transport_events	48,391 rows	event_id, shipment_id, vehicle_id, event_type (Pickup, Loading, Departure, Arrival, Delivery, Breakdown), event_time, location, notes.
storage_events	5,876 rows	storage_id, shipment_id, storage_in, storage_out, storage_condition (Cold/Ambient), avg_temp_c, acceptable_min, acceptable_max, temp_excursion_flag, damage_kg. At most one row per shipment; absence means direct transit, not a missing record.
delivery_events	9,600 rows	delivery_id, shipment_id, planned_delivery, actual_delivery, received_quantity_kg, rejected_quantity_kg, rejection_reason.

Data quality assessment

Assumption: avg_temp_c is expected to be null for essentially all Ambient storage rows (not applicable) and for a small residual share of Cold rows, reflecting a genuine monitoring gap in PHL's process rather than a data defect.

Assumption: A shipment with no storage_events row is a valid direct-transit shipment (~39% of the network), not a gap in the data.

Limitations

Temperature monitoring coverage is incomplete even for Cold-storage shipments, which likely understates the true rate of cold-chain risk rather than representing its actual absence. This limitation is carried into the analysis journey and stated plainly in the final recommendations rather than treated as a settled null result.

Analytical readiness

After normalization and keying, the six tables join cleanly on shipment_id and support all four analytical sub-questions defined in Stage 3.

Stage 3

Business objectives & KPIs

Business goal

Give PHL's operations leadership an evidence-based basis for prioritizing investment against delivery delays and product loss, replacing a single undifferentiated complaint ("shipments are unreliable") with a specific, stage-by-stage account of where loss actually accumulates.

Central business question

Where and why are PHL's agricultural shipments being delayed or lost, and which stage of handling is actually responsible?

Analytical sub-questions

1Route performance — which origin-destination pairs perform worst, and does distance explain it?
2Transport conditions — do delays and breakdowns concentrate in specific vehicle types, and does temperature-controlled assignment reduce loss on perishables?
3Storage and cold chain — does storage duration predict spoilage independently of temperature excursion, or are they confounded?
4Stage attribution — which single stage of the handling process accounts for the largest share of total losses?

KPI definitions & results

KPI	Why it matters	How it's calculated	Result
On-time delivery rate	Primary reliability metric clients judge PHL on	% of shipments with status ≠ 'Delivered Late'	66–75% on non-Mombasa routes; 57–65% on Mombasa-linked routes
Late rate by route	Isolates where the delay problem concentrates geographically	% of shipments with status = 'Delivered Late', by origin-destination	19–27% non-Mombasa; 28–34% Mombasa-linked
Loss rate (kg)	Measures actual product loss, weighted by shipment volume	SUM(rejected_quantity_kg) / SUM(quantity_kg)	0.06–0.07% non-perishables (network-wide); 5.49–8.64% perishables on Mombasa routes
Vehicle-type loss rate (perishables only, fair comparison)	Tests whether temperature-controlled vehicles actually protect cargo	pct_kg_lost, perishable shipments only, split by temperature_controlled	Refrigerated: 5.14% vs Ambient: 4.68% — refrigerated performed marginally worse
Breakdown rate by route	Tests whether mechanical failure is route-concentrated	% of shipments with a Breakdown transport_event, by route	Highest: Eldoret-Kisumu 7.06%, Eldoret-Meru 6.38%; Mombasa routes below average
Avg. storage damage by duration band	Establishes the duration-damage relationship	AVG(damage_kg), perishables, binned by storage duration	0–12h: 21.8kg; 12–24h: 44.3kg; 24–48h: 108.5kg; 48h+: 238.1kg
Temperature excursion probability by duration band	Tests whether excursion risk itself rises with duration	% of Cold storage_events with temp_excursion_flag = True, by duration band	0–12h: 9.2%; 12–24h: 12.1%; 24–48h: 23.7%; 48h+: 46.7%
Stage attribution of total loss	The payoff KPI — tells leadership where to invest	storage_damage_kg vs (rejected_quantity_kg − storage_damage_kg), by breakdown status and route	Storage: 53.9%; Breakdown-related transit: ~11.6%; Routine transit/delivery: ~34.5%

Success criteria

The analysis is considered successful if it produces a single, ranked, evidence-backed answer to which stage of PHL's process contributes the most total loss, and whether that answer holds up after testing (not assuming) the most obvious alternative explanations — distance, vehicle assignment, and fleet age.

Stage 4

Analysis journey

Step 0 — Data validation

Before any business question was touched, twelve checks confirmed the dataset could be trusted: row counts across all six tables, primary-key uniqueness (customers, shipments, delivery_events, storage_events), orphaned foreign-key checks, missing-value patterns in shipments and storage_events, and baseline aggregations (date range, status distribution, transport event-type counts, storage duration by condition).

Step 0 — validation checks

-- Row counts across all six tables
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM 'customers.csv'
UNION ALL SELECT 'vehicles', COUNT(*) FROM 'vehicles.csv'
UNION ALL SELECT 'shipments', COUNT(*) FROM 'shipments.csv'
UNION ALL SELECT 'transport_events', COUNT(*) FROM 'transport_events.csv'
UNION ALL SELECT 'storage_events', COUNT(*) FROM 'storage_events.csv'
UNION ALL SELECT 'delivery_events', COUNT(*) FROM 'delivery_events.csv'

-- Duplicate check: delivery_events should be exactly one row per shipment
SELECT shipment_id, COUNT(*) AS n
FROM 'delivery_events.csv'
GROUP BY shipment_id
HAVING COUNT(*) > 1

-- Missing-value check: temperature nulls should track storage_condition, not be random
SELECT storage_condition, COUNT(*) AS total_rows,
  SUM(CASE WHEN avg_temp_c IS NULL THEN 1 ELSE 0 END) AS missing_temp
FROM 'storage_events.csv'
GROUP BY storage_condition

Result: all twelve checks passed cleanly — no duplicate keys, no orphaned shipments, temperature nulls landing exactly where the data dictionary predicted.

Route performance

Which origin-destination pairs perform worst, and does distance explain it?

Query 1 — route baseline

Establish volume, on-time rate, late rate, and a first-pass average delay hours per route.

Sub-Q1, Query 1 — route baseline

SELECT
  origin, destination,
  COUNT(*) AS total_shipments,
  ROUND(100.0 * SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_on_time,
  ROUND(100.0 * SUM(CASE WHEN status = 'Delivered Late' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_late,
  ROUND(AVG(DATE_DIFF('hour', planned_delivery, actual_delivery)), 1) AS avg_delay_hours
FROM 'shipments.csv'
GROUP BY origin, destination
ORDER BY total_shipments DESC

Result: Every Mombasa-linked route underperformed every non-Mombasa route on every metric, but avg_delay_hours came back negative for most routes — an artifact of averaging in on-time and early deliveries (see Mistakes & Corrections below).

Query 2 — corrected delay severity + distance

Isolate delay hours to late shipments only, and test distance as an explanation.

Assumption: Route distance is not a field in the dataset. Distances were introduced as an external reference value (approximate real road distances) for this analysis only, not derived from the data itself.

Sub-Q1, Query 2 — corrected delay severity + distance

WITH route_distance AS (
  SELECT origin, destination,
    CASE
      WHEN (origin='Nairobi' AND destination='Mombasa') OR (origin='Mombasa' AND destination='Nairobi') THEN 480
      WHEN (origin='Mombasa' AND destination='Kisumu') OR (origin='Kisumu' AND destination='Mombasa') THEN 800
      WHEN (origin='Nairobi' AND destination='Kisumu') OR (origin='Kisumu' AND destination='Nairobi') THEN 340
      WHEN (origin='Nairobi' AND destination='Meru') OR (origin='Meru' AND destination='Nairobi') THEN 225
      WHEN (origin='Nairobi' AND destination='Nakuru') OR (origin='Nakuru' AND destination='Nairobi') THEN 160
      WHEN (origin='Nairobi' AND destination='Eldoret') OR (origin='Eldoret' AND destination='Nairobi') THEN 310
      WHEN (origin='Mombasa' AND destination='Meru') OR (origin='Meru' AND destination='Mombasa') THEN 700
      WHEN (origin='Mombasa' AND destination='Nakuru') OR (origin='Nakuru' AND destination='Mombasa') THEN 630
      WHEN (origin='Mombasa' AND destination='Eldoret') OR (origin='Eldoret' AND destination='Mombasa') THEN 780
      WHEN (origin='Kisumu' AND destination='Meru') OR (origin='Meru' AND destination='Kisumu') THEN 460
      WHEN (origin='Kisumu' AND destination='Nakuru') OR (origin='Nakuru' AND destination='Kisumu') THEN 210
      WHEN (origin='Kisumu' AND destination='Eldoret') OR (origin='Eldoret' AND destination='Kisumu') THEN 200
      WHEN (origin='Meru' AND destination='Nakuru') OR (origin='Nakuru' AND destination='Meru') THEN 260
      WHEN (origin='Meru' AND destination='Eldoret') OR (origin='Eldoret' AND destination='Meru') THEN 400
      WHEN (origin='Nakuru' AND destination='Eldoret') OR (origin='Eldoret' AND destination='Nakuru') THEN 160
    END AS distance_km
  FROM 'shipments.csv'
  GROUP BY origin, destination
)
SELECT s.origin, s.destination, rd.distance_km,
  COUNT(*) AS total_shipments,
  ROUND(100.0 * SUM(CASE WHEN s.status='Delivered Late' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_late,
  ROUND(AVG(CASE WHEN s.status='Delivered Late'
      THEN DATE_DIFF('hour', s.planned_delivery, s.actual_delivery) END), 1) AS avg_delay_hours_when_late
FROM 'shipments.csv' s
JOIN route_distance rd ON s.origin = rd.origin AND s.destination = rd.destination
GROUP BY s.origin, s.destination, rd.distance_km
ORDER BY rd.distance_km DESC

Result: Distance did not explain the pattern: an 800km Mombasa route and a 160km non-Mombasa route showed comparable delay severity once a shipment was actually late.

Query 3 — loss rate by route

Rank routes on weighted kg-loss, since a route could be moderately late but severely lossy or vice versa.

Sub-Q1, Query 3 — loss rate by route

WITH route_loss AS (
  SELECT s.origin, s.destination,
    SUM(s.quantity_kg) AS total_kg_shipped,
    SUM(d.rejected_quantity_kg) AS total_kg_rejected,
    ROUND(100.0 * SUM(d.rejected_quantity_kg) / SUM(s.quantity_kg), 2) AS pct_kg_lost,
    ROUND(100.0 * SUM(CASE WHEN s.status='Delivered Late' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_late
  FROM 'shipments.csv' s
  JOIN 'delivery_events.csv' d ON s.shipment_id = d.shipment_id
  GROUP BY s.origin, s.destination
)
SELECT * FROM route_loss ORDER BY pct_kg_lost DESC

Result: Mombasa-Meru showed a low late rate (20.8%) but a high loss rate (2.25%) — proof that lateness and loss are not the same failure.

Query 4 — perishable split

Test whether the loss concentrated in perishable products specifically, on the four routes already flagged.

Sub-Q1, Query 4 — perishable split

SELECT s.origin, s.destination, s.perishable_flag,
  SUM(s.quantity_kg) AS total_kg_shipped,
  SUM(d.rejected_quantity_kg) AS total_kg_rejected,
  ROUND(100.0 * SUM(d.rejected_quantity_kg) / SUM(s.quantity_kg), 2) AS pct_kg_lost
FROM 'shipments.csv' s
JOIN 'delivery_events.csv' d ON s.shipment_id = d.shipment_id
WHERE (origin='Mombasa' AND destination='Meru') OR (origin='Meru' AND destination='Mombasa')
   OR (origin='Kisumu' AND destination='Mombasa') OR (origin='Mombasa' AND destination='Kisumu')
GROUP BY s.origin, s.destination, s.perishable_flag

Result: Non-perishable loss: 0.06–0.07% (noise). Perishable loss on the same routes: 5.49–8.64%.

Sub-question finding

Mombasa-linked routes carry a structurally higher late rate and loss rate, independent of distance. Lateness and loss are partially independent. Loss is almost entirely a perishables phenomenon.

Transport conditions

Do delays and breakdowns concentrate in specific vehicle types, and does temperature-controlled assignment reduce loss on perishables?

Query 1 — vehicle baseline

Establish breakdown, late, and loss rate by vehicle type network-wide.

Assumption: Each shipment is assumed to be carried by a single vehicle for its entire journey, joined via its Pickup transport_event.

Sub-Q2, Query 1 — vehicle baseline

SELECT v.vehicle_type, v.temperature_controlled,
  COUNT(*) AS total_shipments,
  ROUND(100.0 * SUM(COALESCE(bf.had_breakdown,0)) / COUNT(*), 2) AS pct_breakdown,
  ROUND(100.0 * SUM(CASE WHEN s.status='Delivered Late' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_late,
  ROUND(100.0 * SUM(d.rejected_quantity_kg) / SUM(s.quantity_kg), 2) AS pct_kg_lost
FROM shipment_vehicle sv
JOIN 'vehicles.csv' v ON sv.vehicle_id = v.vehicle_id
JOIN 'shipments.csv' s ON sv.shipment_id = s.shipment_id
JOIN 'delivery_events.csv' d ON sv.shipment_id = d.shipment_id
LEFT JOIN breakdown_flag bf ON sv.shipment_id = bf.shipment_id
GROUP BY v.vehicle_type, v.temperature_controlled
ORDER BY pct_breakdown DESC

Result: Refrigerated Trucks showed the highest loss rate (4.31% vs 0.75–0.80%) — but this comparison is confounded by cargo assignment (see Mistakes & Corrections below).

Query 2 — corrected, perishable-only comparison

Fair test of temperature control, isolating cargo type.

Sub-Q2, Query 2 — perishable-only comparison

SELECT v.temperature_controlled,
  COUNT(*) AS total_shipments,
  ROUND(100.0 * SUM(CASE WHEN s.status='Delivered Late' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_late,
  ROUND(100.0 * SUM(d.rejected_quantity_kg) / SUM(s.quantity_kg), 2) AS pct_kg_lost
FROM shipment_vehicle sv
JOIN 'vehicles.csv' v ON sv.vehicle_id = v.vehicle_id
JOIN 'shipments.csv' s ON sv.shipment_id = s.shipment_id
JOIN 'delivery_events.csv' d ON sv.shipment_id = d.shipment_id
WHERE s.perishable_flag = 'Yes'
GROUP BY v.temperature_controlled

Result: Result flipped: Refrigerated 5.14% loss vs Ambient 4.68%, on identical cargo.

Query 3 — selection-effect test

Check whether refrigerated trucks are disproportionately assigned to Mombasa routes, which would explain the flip as harder cargo rather than equipment failure.

Sub-Q2, Query 3 — selection-effect test

SELECT mf.route_group, v.temperature_controlled,
  COUNT(*) AS total_shipments,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY mf.route_group), 1) AS pct_within_route_group
FROM shipment_vehicle sv
JOIN 'vehicles.csv' v ON sv.vehicle_id = v.vehicle_id
JOIN mombasa_flag mf ON sv.shipment_id = mf.shipment_id
JOIN 'shipments.csv' s ON sv.shipment_id = s.shipment_id
WHERE s.perishable_flag = 'Yes'
GROUP BY mf.route_group, v.temperature_controlled

Result: 69% vs 68.2% — assignment ratios nearly identical. Selection effect ruled out.

Query 4 — breakdown by route

Sub-Q2, Query 4 — breakdown by route

SELECT s.origin, s.destination,
  COUNT(*) AS total_shipments,
  SUM(COALESCE(bf.had_breakdown,0)) AS breakdown_count,
  ROUND(100.0 * SUM(COALESCE(bf.had_breakdown,0)) / COUNT(*), 2) AS pct_breakdown
FROM 'shipments.csv' s
LEFT JOIN breakdown_flag bf ON s.shipment_id = bf.shipment_id
GROUP BY s.origin, s.destination
ORDER BY pct_breakdown DESC

Result: Top three: Eldoret-Kisumu 7.06%, Eldoret-Meru 6.38%, Nakuru-Meru 5.56%. No Mombasa routes in the top tier.

Queries 5 & 6 — Eldoret fleet checks

Sub-Q2, Queries 5 & 6 — Eldoret fleet checks

SELECT ef.route_group, ROUND(AVG(v.acquisition_year),1) AS avg_acquisition_year, COUNT(*) AS total_shipments
FROM shipment_vehicle sv
JOIN 'vehicles.csv' v ON sv.vehicle_id = v.vehicle_id
JOIN eldoret_flag ef ON sv.shipment_id = ef.shipment_id
GROUP BY ef.route_group

SELECT ef.route_group, v.vehicle_type,
  COUNT(*) AS total_shipments,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY ef.route_group), 1) AS pct_within_route_group
FROM shipment_vehicle sv
JOIN 'vehicles.csv' v ON sv.vehicle_id = v.vehicle_id
JOIN eldoret_flag ef ON sv.shipment_id = ef.shipment_id
GROUP BY ef.route_group, v.vehicle_type

Result: Average acquisition year: 2020.8 both groups. Refrigerated-truck share: 41.3% vs 41.0%. Fleet assignment ruled out.

Sub-question finding

Vehicle-level temperature control is not currently protecting perishable cargo — likely an equipment/monitoring reliability gap rather than vehicle assignment. Breakdowns are a second, independent pattern concentrated on Eldoret-linked routes, unexplained by fleet age or type.

Storage and cold chain

Does storage duration predict spoilage independently of temperature excursion, or are they confounded?

Query 1 — duration-damage baseline

Sub-Q3, Query 1 — duration-damage baseline

SELECT
  CASE WHEN duration_hours<12 THEN '0-12h' WHEN duration_hours<24 THEN '12-24h'
       WHEN duration_hours<48 THEN '24-48h' ELSE '48h+' END AS duration_band,
  COUNT(*) AS storage_events_count,
  ROUND(AVG(damage_kg),1) AS avg_damage_kg,
  ROUND(SUM(damage_kg),0) AS total_damage_kg
FROM storage_duration sd
JOIN 'shipments.csv' s ON sd.shipment_id = s.shipment_id
WHERE s.perishable_flag = 'Yes'
GROUP BY duration_band
ORDER BY AVG(duration_hours)

Result: Avg damage: 21.8kg (0–12h) → 238.1kg (48h+), an 11x increase.

Query 2 — duration × excursion cross-tab

Assumption: temp_excursion_flag = False includes both “within acceptable range” and Ambient-storage shipments where excursion isn't a meaningful concept, per the data dictionary definition.

Sub-Q3, Query 2 — duration × excursion cross-tab

SELECT
  CASE WHEN duration_hours<12 THEN '0-12h' WHEN duration_hours<24 THEN '12-24h'
       WHEN duration_hours<48 THEN '24-48h' ELSE '48h+' END AS duration_band,
  temp_excursion_flag,
  COUNT(*) AS storage_events_count,
  ROUND(AVG(damage_kg),1) AS avg_damage_kg
FROM storage_duration sd
JOIN 'shipments.csv' s ON sd.shipment_id = s.shipment_id
WHERE s.perishable_flag = 'Yes'
GROUP BY duration_band, temp_excursion_flag
ORDER BY duration_band, temp_excursion_flag

Result: Within every duration band, excursion multiplied damage 5–16x. Both factors independently real.

Query 3 — excursion probability by duration

Sub-Q3, Query 3 — excursion probability by duration

SELECT
  CASE WHEN duration_hours<12 THEN '0-12h' WHEN duration_hours<24 THEN '12-24h'
       WHEN duration_hours<48 THEN '24-48h' ELSE '48h+' END AS duration_band,
  COUNT(*) AS cold_storage_events,
  ROUND(100.0 * SUM(CASE WHEN temp_excursion_flag THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_excursion
FROM storage_duration sd
JOIN 'shipments.csv' s ON sd.shipment_id = s.shipment_id
WHERE sd.storage_condition = 'Cold' AND s.perishable_flag = 'Yes'
GROUP BY duration_band

Result: Excursion probability: 9.2% (0–12h) → 46.7% (48h+).

Sub-question finding

Storage duration and temperature excursion are distinct but compounding mechanisms. Duration directly increases damage and independently raises excursion probability. Reducing hold time is the more efficient single fix, since it addresses both pathways.

Stage attribution

Which single stage of the handling process accounts for the largest share of total losses?

Query 1 — storage vs. other loss split

Sub-Q4, Query 1 — storage vs other loss

WITH storage_damage AS (
  SELECT shipment_id, SUM(damage_kg) AS storage_damage_kg
  FROM 'storage_events.csv' GROUP BY shipment_id
)
SELECT
  ROUND(SUM(d.rejected_quantity_kg),0) AS total_kg_rejected,
  ROUND(SUM(COALESCE(sd.storage_damage_kg,0)),0) AS total_storage_damage_kg,
  ROUND(100.0 * SUM(COALESCE(sd.storage_damage_kg,0)) / SUM(d.rejected_quantity_kg),1) AS pct_loss_from_storage
FROM 'shipments.csv' s
JOIN 'delivery_events.csv' d ON s.shipment_id = d.shipment_id
LEFT JOIN storage_damage sd ON s.shipment_id = sd.shipment_id
WHERE s.perishable_flag = 'Yes'

Result: Storage: 53.9% of total perishable loss.

Query 2 — breakdown vs. no-breakdown split of remaining loss

Sub-Q4, Query 2 — breakdown vs no-breakdown

SELECT
  CASE WHEN bf.had_breakdown=1 THEN 'Breakdown occurred' ELSE 'No breakdown' END AS breakdown_status,
  COUNT(*) AS total_shipments,
  ROUND(SUM(d.rejected_quantity_kg - COALESCE(sd.storage_damage_kg,0)),0) AS other_damage_kg,
  ROUND(AVG(d.rejected_quantity_kg - COALESCE(sd.storage_damage_kg,0)),1) AS avg_other_damage_kg_per_shipment
FROM 'shipments.csv' s
JOIN 'delivery_events.csv' d ON s.shipment_id = d.shipment_id
LEFT JOIN storage_damage sd ON s.shipment_id = sd.shipment_id
LEFT JOIN breakdown_flag bf ON s.shipment_id = bf.shipment_id
WHERE s.perishable_flag = 'Yes'
GROUP BY breakdown_status

Result: Breakdown shipments (4.5% of population): 152.1kg avg damage each (~7x worse per incident), but only ~25% of remaining loss due to rarity. Non-breakdown shipments account for ~75% of remaining loss.

Query 3 — Mombasa binary check (first pass)

Sub-Q4, Query 3 — Mombasa binary check

SELECT rg.route_group,
  COUNT(*) AS total_shipments,
  ROUND(SUM(d.rejected_quantity_kg - COALESCE(sd.storage_damage_kg,0)),0) AS routine_other_damage_kg,
  ROUND(AVG(d.rejected_quantity_kg - COALESCE(sd.storage_damage_kg,0)),1) AS avg_routine_other_damage_kg,
  ROUND(100.0 * SUM(d.rejected_quantity_kg - COALESCE(sd.storage_damage_kg,0))
    / SUM(SUM(d.rejected_quantity_kg - COALESCE(sd.storage_damage_kg,0))) OVER (),1) AS pct_of_routine_total
FROM 'shipments.csv' s
JOIN 'delivery_events.csv' d ON s.shipment_id = d.shipment_id
LEFT JOIN storage_damage sd ON s.shipment_id = sd.shipment_id
LEFT JOIN breakdown_flag bf ON s.shipment_id = bf.shipment_id
JOIN route_group rg ON s.shipment_id = rg.shipment_id
WHERE s.perishable_flag = 'Yes' AND bf.had_breakdown IS NULL
GROUP BY rg.route_group

Result: Held back from interpretation until the binary Mombasa / non-Mombasa framing itself had been sanity-checked across all 30 routes.

Query 4 — full 30-route sanity check

Run before trusting Query 3's binary framing, given Eldoret's earlier unrelated breakdown pattern.

Sub-Q4, Query 4 — full 30-route sanity check

SELECT s.origin, s.destination,
  COUNT(*) AS total_shipments,
  ROUND(SUM(d.rejected_quantity_kg - COALESCE(sd.storage_damage_kg,0)),0) AS routine_other_damage_kg,
  ROUND(AVG(d.rejected_quantity_kg - COALESCE(sd.storage_damage_kg,0)),1) AS avg_routine_other_damage_kg
FROM 'shipments.csv' s
JOIN 'delivery_events.csv' d ON s.shipment_id = d.shipment_id
LEFT JOIN storage_damage sd ON s.shipment_id = sd.shipment_id
LEFT JOIN breakdown_flag bf ON s.shipment_id = bf.shipment_id
WHERE s.perishable_flag = 'Yes' AND bf.had_breakdown IS NULL
GROUP BY s.origin, s.destination
ORDER BY avg_routine_other_damage_kg DESC

Result: Every top-eight route involved Mombasa; Eldoret sat unremarkably in the lower half — confirmed this loss category was genuinely Mombasa-specific, not a second hidden pattern.

Sub-question finding

Query 5 re-ran Query 3, now trusted: Mombasa 42.4kg avg per shipment vs 14.7kg elsewhere (~3x); 48.3% of category total despite ~25% of shipment volume.

Mistakes & corrections

Two analytical errors changed the direction of a finding, not just its precision, and are documented here rather than folded silently into the final numbers.

Mistake 1

Flattened delay average

The first version averaged delay hours across all shipments, including on-time and early ones — producing misleading negative averages.

Sub-Q1, Query 1 (incorrect)

-- WRONG: includes on-time/early deliveries, producing misleading negative averages
SELECT origin, destination,
  ROUND(AVG(DATE_DIFF('hour', planned_delivery, actual_delivery)), 1) AS avg_delay_hours
FROM 'shipments.csv'
GROUP BY origin, destination

Corrected version

-- CORRECTED: only averages delay among shipments that were actually late
SELECT origin, destination,
  ROUND(AVG(CASE WHEN status='Delivered Late'
      THEN DATE_DIFF('hour', planned_delivery, actual_delivery) END), 1) AS avg_delay_hours_when_late
FROM 'shipments.csv'
GROUP BY origin, destination

Isolating to late shipments only made delay severity comparable across routes, which is what allowed distance to be tested as an explanation at all.

Mistake 2

Confounded vehicle-loss comparison

The first version compared loss rate by vehicle type without controlling for cargo type, since refrigerated trucks are preferentially assigned to perishables.

Sub-Q2, Query 1 (incorrect)

-- WRONG: not controlled for cargo type; refrigerated trucks disproportionately carry perishables
SELECT v.vehicle_type, v.temperature_controlled,
  ROUND(100.0 * SUM(d.rejected_quantity_kg) / SUM(s.quantity_kg), 2) AS pct_kg_lost
FROM shipment_vehicle sv
JOIN 'vehicles.csv' v ON sv.vehicle_id = v.vehicle_id
JOIN 'shipments.csv' s ON sv.shipment_id = s.shipment_id
JOIN 'delivery_events.csv' d ON sv.shipment_id = d.shipment_id
GROUP BY v.vehicle_type, v.temperature_controlled

Corrected version

-- CORRECTED: perishable-only, so temperature-controlled vs ambient is a fair test
SELECT v.temperature_controlled,
  ROUND(100.0 * SUM(d.rejected_quantity_kg) / SUM(s.quantity_kg), 2) AS pct_kg_lost
FROM shipment_vehicle sv
JOIN 'vehicles.csv' v ON sv.vehicle_id = v.vehicle_id
JOIN 'shipments.csv' s ON sv.shipment_id = s.shipment_id
JOIN 'delivery_events.csv' d ON sv.shipment_id = d.shipment_id
WHERE s.perishable_flag = 'Yes'
GROUP BY v.temperature_controlled

The correction reversed the apparent conclusion: the wrong version made refrigerated trucks look like the highest-loss vehicle type; the corrected version showed refrigerated trucks performing marginally worse than ambient trucks on identical cargo, a materially different and more useful finding.

Mistake 3

Synthetic data generation bug (pre-analysis)

During data generation, the planned-delivery SLA formula did not account for expected storage time, producing an unrealistic 69.3% network-wide late rate.

Generator (incorrect)

# WRONG: buffer doesn't account for time the shipment is expected to spend in storage
planned_delivery = shipment_date + timedelta(hours=float(rng.uniform(6, 14)) + base_transit_hours + 4)

Corrected version

# CORRECTED: adds a storage allowance when the shipment is routed through storage
storage_allowance = 16.0 if has_storage else 0.0
planned_delivery = shipment_date + timedelta(
    hours=float(rng.uniform(6, 14)) + base_transit_hours + storage_allowance + 7
)

This dropped the late rate to a more operationally realistic 24.2%, caught via a sanity check before the dataset was finalized for analysis.

Results

Final stage attribution

Stage	Share of total loss
Storage	53.9%
Breakdown-related transit	~11.6%
Routine transit/delivery (no breakdown)	~34.5%

Stage 5

Business recommendations

Executive summary

PHL's agricultural shipment losses are not random. They concentrate in three specific, separable patterns: storage duration, Mombasa-linked route handling, and a narrow mechanical issue on Eldoret-linked routes. Storage duration is the single highest-leverage fix, accounting for 53.9% of total perishable loss and independently driving up the probability of the temperature failures that compound it further. Refrigerated vehicles, PHL's current cold-chain safeguard, are not currently delivering a measurable protective benefit — that finding alone should reshape any planned fleet investment before it's made.

Key insights

1Storage accounts for the largest share of loss (53.9%), and duration inside storage doesn't just extend exposure — it increases the odds of a temperature excursion occurring at all.
2Mombasa-linked routes underperform on late rate, loss rate, and routine non-breakdown loss, in both directions, regardless of paired city, with no elevation in breakdowns — ruling out road quality/distance as a full explanation.
3Refrigerated vehicles show no measurable protective benefit for perishable cargo once cargo type is fairly controlled for, and this isn't explained by refrigerated trucks disproportionately serving harder routes.
4Eldoret's breakdown rate is elevated and isolated to mechanical failure alone — unrelated to vehicle age, type, or any other loss metric tested.
5Roughly a third of all perishable loss (34.5%) comes from routine transit/delivery handling with no dramatic single cause — the least visible category, and the easiest to underinvest in.

Business recommendations

Prioritize reducing average storage duration for perishable shipments

The largest single loss category and the only lever that reduces both direct damage and excursion probability. Target: hold shipments under 24 hours wherever routing allows.

Open a focused operational review of Mombasa-specific handling practices

Before approving further fleet or infrastructure investment tied to that corridor — examining scheduling, refrigeration verification, and storage handoff procedures specifically.

Commission an audit of refrigerated vehicle cooling performance in transit

Not just at storage checkpoints, and before expanding the refrigerated fleet.

Treat Eldoret's breakdown rate as a separate, narrower maintenance issue

A maintenance/route-conditions problem, not part of the broader loss-reduction effort.

Give routine non-breakdown transit/delivery loss explicit visibility

In operational reporting, rather than letting it stay folded into an undifferentiated “other” bucket.

Expected business impact

Reducing storage duration into the 0–24h range targets the two bands responsible for a disproportionate share of both damage severity and rising excursion probability, without requiring capital investment. The Mombasa review and refrigeration audit are prerequisites for spending confidently on fleet/infrastructure investment, avoiding money spent on a fix (more refrigerated trucks) this analysis suggests would not resolve the underlying problem.

Risks

This dataset confirms temperature conditions during storage but not continuously during transit, so the refrigeration-reliability finding is well-supported by elimination but not directly observed. The Eldoret and Mombasa explanations rest partly on ruling out testable alternatives (vehicle age, type, route selection); real-world causes outside this dataset (road conditions, staffing, facility-specific practices) remain plausible and would need direct operational investigation before large investment decisions are finalized.

Next steps

Route Mombasa-linked storage and refrigeration handling for a focused two-to-four week operational review before capital decisions. Set a storage-duration target and track compliance against the duration bands identified here. Add in-transit temperature logging, even periodic rather than continuous, as a follow-up data capability.

Future analysis opportunities

A facility-level breakdown of storage_events would clarify whether long storage durations are a scheduling problem or a specific-facility throughput problem. Driver- or shift-level analysis of the routine non-breakdown transit loss category could reveal further concentration. Continuous in-transit temperature logging would convert the refrigeration-reliability finding from a strong inference into a directly measurable one.

Losing product and not sure where?

Attribution beats intuition — every alternative explanation tested before a recommendation is made.

Book a call WhatsApp me

← All case files View the notebook