Ask a question the data can answer

Northstar Dispatch is a fictional delivery operation. Its manager asks, “Which hub is worse, and should we introduce a checklist?” It sounds like one question. It actually contains two different decisions. The first compares existing operations; the second asks what would happen after an intervention. A table of past deliveries can describe existing operations. It cannot, by itself, reveal the outcome of a change that has not been tried.

Our job is to make the question precise before producing an attractive answer. Throughout this course you will investigate 240 invented shipments across eight invented weeks. Later you will examine a separate simulated randomized checklist trial. These are teaching fixtures with known generating rules, not evidence about any real business or person. The distinction between the two files is part of the lesson: the way data was obtained limits what a calculation can establish.

Begin with the decision

Suppose the manager can investigate one source of delays next week. An answer such as “average time is 42 minutes” leaves important questions unanswered. Average across which deliveries? Is 42 good relative to the promise? Did longer routes take longer because they were longer? A useful starting question is: “Among the 240 recorded shipments in weeks 1–8, how often did actual delivery time exceed the promise, and how did that observed rate differ by hub and service?” This asks for a description of a defined file. It does not yet ask for a forecast or a causal explanation.

Write a short analysis contract: decision, population, unit, outcome, comparison, period and limitations. The population is the collection you want to say something about. The unit is the thing measured. An outcome is the measurement used to answer the question. In the shipment file, the unit is a shipment and the outcome might be actual delivery minutes or late/not late. These are different outcomes. A service can be fast in absolute minutes and still miss its tighter promise more frequently.

“Improve service” is too broad for a metric. “Reduce the fraction arriving after the promised time without an unacceptable cost or safety trade-off” is a decision that could be measured. This course has no cost or safety outcomes. Do not treat their absence as proof that a change has no downside.

Understand one row before counting rows

The grain of data/shipments_clean.csv is one recorded shipment. shipment_id identifies that unit. A shipment with two tracking updates is still one shipment. If you join an updates table and obtain two rows for it, a raw row count now counts updates rather than shipments. This is the same grain problem taught in the SQL course, but the statistical consequence is new: apparent sample size and precision can both be overstated.

Read the data dictionary before looking at a chart. distance_km is a numerical measurement with meaningful differences and a meaningful zero. hub and service are categories. The rating is ordered from 1 to 5; a gap from 1 to 2 need not represent the same psychological change as a gap from 4 to 5. A numerical storage type does not automatically make arithmetic meaningful. IDs are labels even if they contain digits.

The late indicator equals 1 when actual time is strictly greater than promised time, and 0 otherwise. A shipment taking exactly its promised 40 minutes is on time under this definition. That boundary matters when you reproduce a metric. A missing rating is unknown, not zero and not a neutral rating of 3.

Work a rate all the way through

Use this tiny, separate teaching table before opening the main file:

Two of four shipments are late: 2 divided by 4 equals 0.50, or 50%. The numerator counts late shipments; the denominator counts shipments with usable actual and promised times in this table. Now imagine a fifth row with an unknown actual time. Reporting 2/5 as the measured late rate assumes the unknown shipment was on time. Reporting 2/4 without mentioning the missing row hides incomplete coverage. A transparent report says “2 of 4 evaluable shipments were late; 1 of 5 shipments had unknown timing.”

Percentages are ratios, not self-explanatory facts. Always keep their count and denominator close. In this course, all 240 clean shipment records have valid timing. Rating denominators will be smaller because some ratings are missing.

Preserve the evidence while cleaning

The supplied raw export contains deliberately introduced duplicate and invalid rows. The companion cleaner keeps an audit of what it accepted, rejected and deduplicated. Open the dictionary for the exact policy. A duplicated valid record does not create a second shipment. An impossible negative duration must be investigated or rejected under an explicit rule; a plausible long duration must not be removed merely because it worsens the average.

Keep the raw CSV unchanged. Make a cleaned copy and an issue log. Check that the accounting reconciles: raw records equal accepted records plus rejected records plus duplicate records under the stated, mutually exclusive disposition policy. If the counts do not reconcile, stop before telling a story. A neat chart cannot repair untracked data loss.

From a question to a defensible sentence

A strong first result names its scope: “In the recorded eight-week fixture, X of 240 shipments were late.” A weak result says “The company has a lateness problem” without defining a comparison or target. A stronger next step asks whether the observed pattern persists within comparable service types and routes. A causal recommendation needs a design that supports it, which is why the separate randomized trial appears later.

Before moving on, write your own analysis contract and explain the difference between shipment grain and update grain. Then attempt Practice 1A–1C in the workbook. You are ready when another learner can identify exactly which records belong in your denominator.

Further reading: NIST on exploratory analysis. The SQL prerequisite crosswalk is in the syllabus; repeating the SQL course is unnecessary.