Audit records before summarizing
5 min read
Lesson objectives
- The learner creates an exception log that distinguishes a true duplicate from a repeated event, a missing value from zero, and a change of unit from a change of meaning. The resulting table remains traceable to the raw rows, and its summary is bounded by the records actually available. The connection to the preceding lesson is practical: even a carefully defined denominator becomes wrong when duplicates or mixed units change its rows.
Please sign in to use all the tools
Once you sign in, the reading tools are yours: larger or smaller text, typeface, line spacing, focus mode, highlighting and bold, and notes saved to your account and gathered in My study.
When a small extract arrives, the first question is not “What is its average?” Ask what each row represents and whether the rows are comparable. A row might be a service ticket, an update to a ticket, a reopening event, or a duplicate export. Use a key that identifies the entity and event, such as ticket ID plus event number and update timestamp, instead of deleting every repeated name. Define the variable to summarize, its unit, and the event that starts and stops measurement. “Completion duration” could mean active work time or elapsed clock time. Do not mix those meanings in one column without explanation.
Test quality against the intended decision. Completeness asks whether each included ticket has a duration. Uniqueness asks whether the summary table represents each ticket once. Consistency asks whether the duration agrees with another source record and whether units are clear. Validity asks whether the format and range are plausible. Accuracy means agreement with reality; a populated cell alone cannot establish it. A sheet can have no blanks yet contain a wrong duration because the start time was captured after the end time. These dimensions organize checks; checking boxes does not itself certify the data.
The reported result must include its base: “73 minutes on average among five tickets with documented duration out of six unique closed tickets.” Duration-field completeness is 5 ÷ 6 ≈ 83.3%. Filling the blank with zero would produce 365 ÷ 6 ≈ 60.8 minutes, suggesting speed without evidence. Keeping the duplicate T103 in the known-value set gives a sum of 440 over six rows, or about 73.3 minutes. The closeness of these results is coincidental; it does not validate the wrong procedure. In another dataset, duplicated rows could change the mean substantially or in a biased direction.
An exception log should show the original value, reason for flagging, action, effect on counts, and reviewer of the decision. Here the second T103 is an identical event export copy, so remove it from the analytic table while preserving its trace in the raw extract. T104 has missing duration; do not guess a replacement, and request source review. T102 and T105 use hours; convert to minutes and record the conversion. Do not erase raw rows or overwrite their original 1.5 and 2 hour values without a trace. A reviewer needs to know whether a later discrepancy came from the source or the transformation. If two similar rows really were different events, deletion would be a mistake. Return to the unit of analysis defined on the metric card.
Before reporting the mean, reconcile counts: seven raw rows less one duplicate yield six unique tickets; five durations are known and one is missing. Independently check the sum: 50+90=140, while 75+120+30=225, totaling 365. Put “minutes” in the final column label rather than leaving the unit implicit. Sorting may reveal an implausible value, but do not delete a large observation solely because it is large. Inspect its source and event. A 120-minute duration is possible in this hypothetical case.
The inference has a boundary. The mean of the five observed durations need not equal the mean of all six. If T104 is missing because a difficult case took unusually long, the observed mean may understate the true mean. If it was very short, the reverse may hold. Report the missing count and seek recovery before a sensitive decision, or give a clearly conditional range only if defensible duration bounds exist. Do not invent a value to fill a cell. If management compares months, apply the same duplicate, unit, and missingness checks to both. Different source quality can create an artificial difference in their means.
Separate a demonstrable correction from one that needs investigation. Converting a clearly labeled 1.5 hours to 90 minutes is deterministic and can be recorded immediately. Removing the second T103 is justified only after its event key matches, not merely because the duration happens to match. T104 cannot be reconstructed from the other rows, so keep it unresolved rather than replacing it with the sample mean; mean imputation would suppress variation and conceal a collection problem. For a quick management update, publish the provisional summary together with its completeness and assign a source check. Urgency does not turn a guessed value into an observation.
If start and end timestamps arrive instead of a duration, verify date format, time zone, and day rollover before subtraction. “23:50 to 00:20” can mean 30 minutes when the end is on the next day, yet look negative if dates are dropped. If the system captures active work rather than waiting time, an elapsed-clock calculation cannot be silently relabeled as work duration. State that methodological difference in the column name and metric definition so another analyst can repeat or challenge a specific transformation.
Lesson summary
The learner creates an exception log that distinguishes a true duplicate from a repeated event, a missing value from zero, and a change of unit from a change of meaning. The resulting table remains traceable to the raw rows, and its summary is bounded by the records actually available. The connection to the preceding lesson is practical: even a carefully defined denominator becomes wrong when duplicates or mixed units change its rows.