Chapter 2 of 8

Data Quality and the Audit Before the Analysis

3 sections · ~7 min read · 3 practice questions · part of Business Analytics

Learning objectives

  • Run a structured quality audit across completeness, uniqueness, validity, consistency, and timeliness
  • Quantify how duplicates, test records, and missing rows distort a headline ratio
  • Recognise that partial cleaning can move a metric further from the truth than no cleaning at all

Five questions to ask of any extract

Analysts inherit data; they rarely collect it. That means the first substantive task is an audit, and the audit is far more productive when it follows a fixed checklist rather than intuition. Completeness asks whether every event that happened in the real world produced a row, and it is the hardest to check because missing rows are invisible by construction. Uniqueness asks whether any real event produced more than one row, which retry logic, late-arriving webhooks, and repeated batch loads all cause routinely. Validity asks whether each field obeys its own rules: dates in range, amounts non-negative where they must be, currency codes from the permitted set, identifiers matching the expected format. Consistency asks whether the extract agrees with an independent source, most usefully a financial system that someone reconciles for other reasons. Timeliness asks what period the extract actually covers, since a table pulled at nine in the morning typically has a partial final day that will make the last bar of every chart look like a collapse. The audit is cheap relative to the cost of retracting a recommendation, and it should be written down with counts, not adjectives, so that the next analyst can reproduce it.

Missingness has a mechanism and the mechanism matters

The important question about a missing value is not how many there are but why they are missing, because the answer determines whether any repair is safe. If values are missing completely at random, for example because a sensor dropped packets on a schedule unrelated to anything of interest, then the observed rows remain a fair sample and dropping them costs precision but not accuracy. If values are missing at random conditional on observed variables, for example if income is more often blank for younger customers whose ages you do record, then you can adjust using the variables you have. If values are missing for reasons tied to the unobserved value itself, for example if the largest customers are the ones who refuse to disclose contract size, then no amount of imputation recovers the truth and any mean computed from the remainder is biased in a known direction. The practical rule is to treat imputation as an assumption you must state, not a technical step you may hide. Filling a blank with the column mean is not neutral; it shrinks variance, weakens correlations, and manufactures confidence intervals that are narrower than the evidence supports.

Cleaning is directional, so clean in both directions

Data errors do not conveniently offset. Duplicates inflate counts and, if the duplicated records are systematically different from the rest, they also drag ratios. Missing rows deflate counts and drag ratios the other way. Because these are separate mechanisms with separate causes, correcting only the one that is easy to find can move a metric further from the truth than leaving everything alone. Deduplication is easy: it needs only the extract in hand. Recovering missing rows is hard: it needs a second source. The temptation to stop after the easy half is strong and should be resisted, or at minimum flagged loudly in the writeup. Keep a reconciliation ledger showing the starting row count and value, every adjustment as a signed line item with its reason, and the ending figures. This ledger is what makes an analysis defensible when a finance colleague asks why your revenue number differs from theirs, and it usually reveals a real process bug worth fixing at the source, which is worth more than the analysis itself.

Worked example

Problem

A hypothetical online retailer extracts one month of orders: 10,000 rows totalling 1,250,000 dollars, giving a reported average order value of 125.00 dollars. An audit finds three problems. First, a payment retry bug wrote 400 exact duplicate rows worth 52,000 dollars in total. Second, 150 internal test orders worth 3,000 dollars in total are included. Third, one store's feed failed for a day, so 200 genuine orders worth 28,000 dollars in total are missing and have been recovered from a backup. Compute the corrected average order value, the completeness and duplicate rates, and the AOV that would have been reported had only the duplicates been removed.

Step by step

  1. Start: 10,000 rows, 1,250,000 dollars. Reported AOV = 1,250,000 / 10,000 = 125.00 dollars per order.
  2. Remove duplicates: 10,000 - 400 = 9,600 rows. 1,250,000 - 52,000 = 1,198,000 dollars.
  3. Remove internal test orders: 9,600 - 150 = 9,450 rows. 1,198,000 - 3,000 = 1,195,000 dollars.
  4. Add the recovered missing orders: 9,450 + 200 = 9,650 rows. 1,195,000 + 28,000 = 1,223,000 dollars.
  5. Corrected AOV = 1,223,000 / 9,650. Since 9,650 x 126 = 1,215,900, the remainder is 7,100, and 7,100 / 9,650 = 0.7358. Corrected AOV = 126.74 dollars per order.
  6. Change versus reported = 126.74 - 125.00 = 1.74 dollars, or 1.74 / 125.00 = 1.39 percent higher.
  7. Duplicate rate = 400 / 10,000 = 4.00 percent of delivered rows.
  8. Completeness rate = genuine orders present before the backup recovery, divided by genuine orders that occurred = 9,450 / 9,650 = 97.93 percent.
  9. Partial cleaning check. Removing only the duplicates gives 1,198,000 / 9,600. Since 9,600 x 124 = 1,190,400, the remainder is 7,600, and 7,600 / 9,600 = 0.7917, so AOV = 124.79 dollars.
  10. Compare distances from the truth. Doing nothing was off by 126.74 - 125.00 = 1.74 dollars. Removing duplicates only was off by 126.7358 - 124.7917 = 1.94 dollars, which is further from the truth than doing nothing. Carry the unrounded figures through this last comparison: subtracting the two rounded values instead gives 126.74 - 124.79 = 1.95, and a one-cent artefact of rounding has no business sitting in a conclusion.

Answer. The corrected figures are 9,650 orders and 1,223,000 dollars, giving a true AOV of 126.74 dollars per order, 1.39 percent above the reported 125.00. The duplicate rate was 4.00 percent of rows and completeness was 97.93 percent. Deduplicating alone would have produced 124.79 dollars, which is 1.94 dollars from the truth, worse than the 1.74 dollars of error in the untouched extract. The business interpretation is that the errors were partly offsetting: duplicates and test orders pulled AOV down while the missing high-value store pulled it up, so the only safe procedure is to correct in both directions and publish the reconciliation ledger. All figures are for one month in dollars per order and none of them are seasonally adjusted.

Practice

Work each question before opening the solution.

  1. A customer survey reports mean satisfaction of 4.3 out of 5 with a 12 percent response rate. Under what missingness mechanism is 4.3 an unbiased estimate of population satisfaction, and which mechanism is most plausible here?

    Show solution for question 1

    It is unbiased only if response is missing completely at random, or missing at random conditional on variables you have measured and adjusted for. The plausible mechanism is missingness tied to the unmeasured satisfaction itself: highly satisfied and highly annoyed customers respond more than indifferent ones, and in most product surveys the satisfied group dominates. The estimate is then biased and 4.3 should be read as the mean among responders, not among customers.

  2. A daily active user table contains 3 percent duplicate rows, spread evenly across all users. State the effect on the reported total DAU count and on the reported mean sessions per user, and explain why the two effects differ.

    Show solution for question 2

    Total DAU is inflated by about 3 percent if the count is of rows rather than of distinct user identifiers. Mean sessions per user is roughly unaffected, because duplicates spread evenly inflate the numerator of total sessions and the denominator of distinct users by the same proportion, so the ratio cancels. Counts are vulnerable to duplication; evenly spread duplication mostly cancels out of a ratio, which is why a metric can look fine while the underlying table is broken.

  3. A dashboard shows a sharp drop in orders on the most recent day, every day. Give the most likely explanation and one fix that does not require changing the data pipeline.

    Show solution for question 3

    The extract is taken partway through the current day, so the final bucket covers only a few hours and is not comparable to full days. This is a timeliness defect, not a business event. The presentation fix is to exclude the incomplete period from the chart, or to shade it and label it as partial, so the eye does not read a partial day as a decline.