Supply Chain Data Analyst Interview Case: Explain a Drop in On-Time, In-Full Delivery

An operations leader says on-time, in-full delivery fell six and a half points last week. A weak analyst immediately blames a carrier. A stronger analyst first asks what one counted delivery represents, which promise date was used, and whether a split shipment can count as full. Only then do they locate the segment responsible for the decline. This original interview case gives you a concrete OTIF definition, a synthetic two-week dataset, and a defensible way to move from metric validation to an action memo.

Evidence boundary: public supply-chain references show that on-time/completeness measures may be defined at order or line level. All companies, weeks, values, hypotheses, SQL shapes, and recommendations below are synthetic. The definition in this exercise is deliberate; it is not presented as the universal industry definition of OTIF.

First lock the metric contract

For this case, one observation is an accepted customer order line, not a shipment scan or an order header. Each line has an accepted quantity and an original confirmed delivery deadline frozen at acceptance. A line is on time if the accepted quantity has been delivered by that deadline. It is in full if the cumulative valid delivered quantity reaches the accepted quantity. OTIF requires both conditions by the same deadline. The numerator is accepted lines meeting both tests; the denominator is accepted lines with a deadline in the week being reported, subject to explicitly documented exclusions.

Why choose this grain? An order with three lines can have two complete lines and one short line. An order-level measure may mark the whole order as a miss, while a line-level measure records two successes and one failure. APQC describes a complete-order measure, while Microsoft's supply-risk reporting documentation discusses on-time, in-full, and OTIF performance in an order-line context. Neither source licenses silently mixing grains. In an interview, state the local business contract and show how you would reconcile it to any official dashboard definition.

The deadline is also a decision. If a team edits a promise date after an order is late and later compares delivery to that newer date, apparent performance can improve without any customer receiving a parcel earlier. For this exercise, use the original confirmed promise as the primary metric and retain the latest revised promise as a separate diagnostic. If the customer truly agreed to a new date, report that as an explicit sensitivity or alternative service-level metric; do not overwrite history invisibly. This is a measurement choice, not a claim that all organizations must use the original date.

Outcome at original deadline	On time?	In full?	OTIF?	Diagnostic meaning
Entire accepted quantity delivered by deadline	Yes	Yes	Yes	Promise kept
Entire quantity delivered after deadline	No	Yes eventually	No	Timing miss
Some quantity delivered by deadline, rest later	Partly delivered	No at deadline	No	Split/short at promise
Nothing delivered by deadline	No	No	No	Severe fulfillment or data gap

The words “in full” need a time boundary. If you calculate eventual fullness months later, a short line may appear complete while still having missed the promised service. Duplicate events, replacements, returns, and canceled quantities require explicit handling. Do not sum every positive scan blindly: one parcel may receive several status scans, and a return does not mean the original delivery did not occur. Keep an event-level audit trail and derive one line-level outcome.

The synthetic case: 89.0% becomes 82.5%

Assume two fulfillment lanes, A and B. The following counts are invented for this exercise. Week 1 has 1,000 due order lines and 890 OTIF successes. Week 2 also has 1,000 due lines but only 825 successes. The top-line drop is 82.5% - 89.0% = -6.5 percentage points. Do not call that a 6.5% relative decrease; the relative change would use 89.0% as the denominator and answer a different question.

Due week	Lane	Eligible lines	OTIF lines	Lane OTIF rate	Contribution to total
1	A	600	570	95.0%	57.0 points
1	B	400	320	80.0%	32.0 points
2	A	500	475	95.0%	47.5 points
2	B	500	350	70.0%	35.0 points

Notice two separate effects. The business moved from 40% lane B to 50% lane B; B was already lower-performing, so mix alone lowers the total. Holding Week 1 lane rates constant at Week 2's mix gives 0.5 × 95% + 0.5 × 80% = 87.5%. The mix effect is 87.5% - 89.0% = -1.5 points. At that new mix, B's rate also falls from 80% to 70%, contributing 0.5 × (-10 points) = -5.0 points. Together they explain the observed -6.5 points exactly. This is an arithmetic decomposition under the chosen two-lane partition, not proof of why B deteriorated.

This bridge gives the interviewer a sharp next question: “What happened inside lane B?” It also prevents the classic mistake of attributing the entire decline to lane B's within-lane change when some came from the larger share of B lines. If a lane's volume or product mix changed within the lane, perform a second decomposition by distribution center, carrier, SKU class, geography, or promise window. Keep denominator counts beside every rate; a 20-point fall in a tiny subgroup may explain less of the total than a 3-point fall in a large one.

Reconcile data before telling an operations story

Validate row identity first. One accepted order line should map to one frozen promise and one accepted quantity at the correct version. Split shipments can create multiple delivery events, so aggregate delivered quantity by line and timestamp after deduplicating event IDs. Make sure carrier scans indicate actual customer delivery rather than pickup or departure from a depot. Check time zones around midnight and local cutoff rules. A UTC event at 00:15 can be the prior local calendar day; that difference can move a line across a promise boundary.

Then reconcile denominator movement. Were more B lines accepted, or were due dates shifted into Week 2? Did order ingestion backfill old lines? Were test records or unaccepted orders included? For this exercise, exclude duplicate and test lines, and exclude lines canceled before acceptance because no customer promise existed yet. Keep accepted lines canceled afterward visible in a separate category, with a sensitivity check for customer-initiated versus seller-initiated cancellations. Do not silently discard the hard cases: removing late or short lines from the denominator can manufacture improvement.

A useful quality table has counts for accepted_due_lines, missing_original_promise, missing_quantity, duplicate_line_versions, missing_delivery_event, split_delivery_lines, and post_acceptance_cancellations by week and lane. If any count jumps, the headline OTIF change may be partly a pipeline or policy artifact. A row-level sample of misses should be reviewed with operations before declaring root cause. Data validation is not a delay tactic; it makes the eventual recommendation credible.

A SQL shape that preserves the line grain

The query should first freeze one accepted order-line record, then aggregate delivery events, then classify. Here is intentionally portable pseudocode: functions for timezone conversion, historical snapshots, and timestamp comparison vary by warehouse. It omits all production exclusion policy details, which must be documented rather than guessed.

WITH accepted_lines AS (
  SELECT line_id, lane, accepted_qty, original_promise_at,
         DATE(original_promise_at) AS due_day
  FROM order_line_snapshot
  WHERE is_accepted = TRUE AND is_test = FALSE
), delivered_by_promise AS (
  SELECT l.line_id,
         SUM(CASE WHEN e.event_type = 'DELIVERED'
                   AND e.delivered_at <= l.original_promise_at
                  THEN e.quantity ELSE 0 END) AS qty_by_deadline
  FROM accepted_lines l
  LEFT JOIN delivery_events_deduplicated e USING (line_id)
  GROUP BY l.line_id
), classified AS (
  SELECT l.line_id, l.lane, l.due_day,
         CASE WHEN COALESCE(d.qty_by_deadline, 0) >= l.accepted_qty
              THEN 1 ELSE 0 END AS otif_flag
  FROM accepted_lines l
  LEFT JOIN delivered_by_promise d USING (line_id)
)
SELECT due_day, lane, COUNT(*) AS eligible_lines,
       SUM(otif_flag) AS otif_lines,
       AVG(1.0 * otif_flag) AS otif_rate
FROM classified
GROUP BY due_day, lane;

The simple flag works only because this exercise defines full quantity by the deadline as the combined success condition. In a richer model, retain separate on-time and in-full indicators so you can distinguish late-full from on-time-short. Deduplicate delivery events before the sum, account for negative corrections and returns under a documented policy, and aggregate to weekly cohorts only after classification. If promise dates are stored as revisions, do not select an arbitrary latest row; select the original confirmed version by a stable rule. A warehouse query with correct syntax but the wrong grain is still a wrong answer.

Diagnose B's misses without pretending the aggregates prove cause

Suppose, still synthetically, B had 150 misses in Week 2. A first event-level breakdown finds 90 late-but-eventually-full lines, 40 lines partially delivered by the deadline, and 20 with neither timely nor full delivery. These categories sum to 150 and are mutually exclusive under our deadline rule. A surge of late-full misses points toward transit, dispatch timing, or promise calibration; a surge of shorts points toward stock availability, pick accuracy, or split-fulfillment behavior. But event classifications are symptoms. To infer mechanism, compare planned versus actual pick time, tender time, first carrier scan, hub dwell, and final delivery, while checking whether the underlying event completeness changed.

Look for a plausible operational change: a new carrier on B, a distribution-center cutover, a peak in bulky SKUs, or a promise policy that became more aggressive. For each hypothesis, request a falsifiable comparison. If carrier reassignment is suspected, compare B lines by carrier before and after, controlling for geography and SKU mix; do not infer causation merely because the carrier share changed. If late orders are concentrated at one hub after a cutover, ask whether scan latency changed, not only physical movement. If short lines cluster in a few SKUs, compare inventory availability and substitutions at order acceptance.

The recommendation memo and its guardrails

An interview-ready memo can be short: “OTIF fell from 89.0% to 82.5% on 1,000 due lines per week under a frozen-promise, order-line definition. Mix toward lower-performing lane B explains 1.5 points; B's within-lane rate decline explains 5.0 points. The strongest current symptom is late-full B deliveries, but root cause is unconfirmed. Validate event timeliness and promise revisions, then investigate carrier/hub stage times for B. In parallel, prioritize recovery for due-soon B orders and monitor miss categories daily.” The numbers are synthetic; the memo models reasoning, not a real company finding.

Pair the action with a measurement plan. Track OTIF under both original and any approved revised promise, lane-level eligible counts, late-full and short categories, data-latency indicators, and customer contacts. If an intervention moves orders to another carrier, watch cost and on-time performance together; a metric improvement that is achieved by excluding hard orders or quietly extending promises is not a service improvement. Name a review date and the evidence that would change your recommendation. This avoids the interview trap of ending with an attractive chart but no next decision.

Sources and Further Reading

• APQC: Percentage of orders delivered complete — example of an order-level completeness measure.
• Microsoft Learn: Supply risk assessment reports — on-time, in-full, and OTIF reporting concepts.
• Microsoft Learn: Supply risk assessment configuration — configuration context for performance measures.