The business question

Every revenue number a B2B software company reports was cleaned by somebody. This is that job: a month of invoices, and the question of which rows are wrong.

It is a judgment problem disguised as a detection problem. Flag too little and one mis-keyed invoice poisons every average downstream. Flag too much and you delete your best deal of the year because it looked unusual. This worked example plants four known anomalies in an invoice ledger and shows which detectors find which — and, more usefully, which detector hides the others.

The data

One row per invoice, as a billing export delivers it.

column meaning
invoice_id invoice number
account_id the account billed
segment SMB / Mid-Market / Enterprise
seats licensed seats on the invoice
price_per_seat contracted per-seat rate, US dollars
amount_usd invoiced amount, US dollars
invoice_month billing month

Four anomalies are planted deliberately, and we know exactly what they are:

  1. A mis-keyed invoice — a real Enterprise invoice typed with one extra zero.
  2. A genuine large deal — a real, correct, unusually big new contract.
  3. A genuine near-zero month — a real credit issued after a service outage.
  4. A duplicate — one ordinary invoice recorded twice, same invoice number.
set.seed(20260824)

n <- 240
segment <- sample(c("SMB", "Mid-Market", "Enterprise"), n, replace = TRUE,
                  prob = c(0.78, 0.16, 0.06))
seats <- ifelse(segment == "SMB",        sample(22:38,   n, replace = TRUE),
         ifelse(segment == "Mid-Market", sample(55:95,   n, replace = TRUE),
                                         sample(110:150, n, replace = TRUE)))
price_per_seat <- round(rnorm(n, 78, 6), 2)
amount_usd     <- round(seats * price_per_seat, 2)

ledger <- data.frame(
  invoice_id     = sprintf("INV-%05d", seq_len(n)),
  account_id     = sprintf("ACC-%04d", sample(1000:1999, n, replace = TRUE)),
  segment        = segment,
  seats          = seats,
  price_per_seat = price_per_seat,
  amount_usd     = amount_usd,
  invoice_month  = "2026-05",
  stringsAsFactors = FALSE
)

# --- plant the four known anomalies -------------------------------------------------
ent_rows <- which(ledger$segment == "Enterprise")
smb_rows <- which(ledger$segment == "SMB")

i_typo  <- ent_rows[which.max(ledger$amount_usd[ent_rows])]   # a real Enterprise invoice
true_amount_of_typo <- ledger$amount_usd[i_typo]
ledger$amount_usd[i_typo] <- round(true_amount_of_typo * 10, 2)   # one extra zero

i_big <- ent_rows[order(ledger$amount_usd[ent_rows])][2]      # becomes the genuine big deal
ledger$seats[i_big]      <- 320L
ledger$amount_usd[i_big] <- round(320 * ledger$price_per_seat[i_big], 2)

i_zero <- smb_rows[10]                                        # outage credit month
ledger$amount_usd[i_zero] <- 150.00

i_dup <- smb_rows[40]                                         # recorded twice, verbatim
ledger <- rbind(ledger, ledger[i_dup, ])
rownames(ledger) <- NULL

write.csv(ledger, "data.csv", row.names = FALSE)

planted <- data.frame(
  anomaly = c("mis-keyed invoice", "genuine large deal", "genuine near-zero (credit)",
              "duplicate row"),
  invoice_id = c(ledger$invoice_id[i_typo], ledger$invoice_id[i_big],
                 ledger$invoice_id[i_zero], ledger$invoice_id[i_dup]),
  amount_usd = c(ledger$amount_usd[i_typo], ledger$amount_usd[i_big],
                 ledger$amount_usd[i_zero], ledger$amount_usd[i_dup]),
  note = c(sprintf("true amount was %.2f", true_amount_of_typo),
           "320 seats, correctly billed", "service credit after an outage",
           "same invoice number appears twice")
)
kable(planted, caption = "The four planted anomalies — the answer key")
The four planted anomalies — the answer key
anomaly invoice_id amount_usd note
mis-keyed invoice INV-00136 112800.0 true amount was 11280.00
genuine large deal INV-00018 25068.8 320 seats, correctly billed
genuine near-zero (credit) INV-00013 150.0 service credit after an outage
duplicate row INV-00046 2910.6 same invoice number appears twice 
c(rows = nrow(ledger), unique_invoice_ids = length(unique(ledger$invoice_id)))
##               rows unique_invoice_ids 
##                241                240

Detector 1 — the classic rule: mean ± 3 standard deviations

x  <- ledger$amount_usd
mu <- mean(x); sdev <- sd(x)
classic_z <- (x - mu) / sdev
classic_flag <- abs(classic_z) > 3
c(mean = round(mu, 2), sd = round(sdev, 2),
  upper_fence = round(mu + 3 * sdev, 2), lower_fence = round(mu - 3 * sdev, 2),
  n_flagged = sum(classic_flag))
##        mean          sd upper_fence lower_fence   n_flagged 
##     3507.42     7396.35    25696.48   -18681.64        1.00
kable(ledger[classic_flag, c("invoice_id","segment","seats","amount_usd")],
      row.names = FALSE, caption = "Everything the classic rule flags")
Everything the classic rule flags
invoice_id segment seats amount_usd
INV-00136 Enterprise 150 112800

It finds the mis-key, loudly — and then it stops. The genuine large deal and the outage credit both sit inside the fences.

Why: the mis-key is inside its own yardstick

x_fixed <- x; x_fixed[i_typo] <- true_amount_of_typo
mu2 <- mean(x_fixed); sd2 <- sd(x_fixed)
comparison <- data.frame(
  ledger      = c("as delivered (mis-key present)", "after correcting the mis-key"),
  mean        = round(c(mu, mu2), 2),
  sd          = round(c(sdev, sd2), 2),
  upper_fence = round(c(mu + 3 * sdev, mu2 + 3 * sd2), 2),
  big_deal_z  = round(c((ledger$amount_usd[i_big] - mu) / sdev,
                        (ledger$amount_usd[i_big] - mu2) / sd2), 3),
  big_deal_flagged = c(abs((ledger$amount_usd[i_big] - mu) / sdev) > 3,
                       abs((ledger$amount_usd[i_big] - mu2) / sd2) > 3)
)
kable(comparison, caption = "Masking: one bad row inflates the ruler that judges every other row")
Masking: one bad row inflates the ruler that judges every other row
ledger mean sd upper_fence big_deal_z big_deal_flagged
as delivered (mis-key present) 3507.42 7396.35 25696.48 2.915 FALSE
after correcting the mis-key 3086.17 2238.14 9800.61 9.822 TRUE 
c(sd_inflation_factor = round(sdev / sd2, 2))
## sd_inflation_factor 
##                 3.3

The mis-key inflates the standard deviation, the fences move outward, and the genuine large deal is swallowed. Correct the one bad row and the same detector finds it — it was detectable the whole time.

The outage credit is a harder case: the lower fence is far below zero either way, so the classic rule can never flag a low outlier in right-skewed revenue data. Not a tuning problem — a shape problem.

Detector 2 — the robust rule: median and MAD

med <- median(x); madv <- mad(x)   # mad() applies the 1.4826 consistency constant
robust_z <- (x - med) / madv
robust_flag <- abs(robust_z) > 3
c(median = round(med, 2), mad = round(madv, 2), n_flagged = sum(robust_flag))
##    median       mad n_flagged 
##   2459.97    592.40     38.00
robust_hits <- ledger[robust_flag, c("invoice_id","segment","seats","amount_usd")]
robust_hits$robust_z <- round(robust_z[robust_flag], 2)
kable(head(robust_hits[order(-abs(robust_hits$robust_z)), ], 12), row.names = FALSE,
      caption = "Top robust-rule flags (of all flagged)")
Top robust-rule flags (of all flagged)
invoice_id segment seats amount_usd robust_z
INV-00136 Enterprise 150 112800.00 186.26
INV-00018 Enterprise 320 25068.80 38.16
INV-00191 Enterprise 135 10993.05 14.40
INV-00139 Enterprise 144 10775.52 14.04
INV-00184 Enterprise 137 10505.16 13.58
INV-00177 Enterprise 131 10046.39 12.81
INV-00014 Mid-Market 95 8207.05 9.70
INV-00158 Mid-Market 92 8083.12 9.49
INV-00160 Enterprise 113 7997.01 9.35
INV-00070 Mid-Market 82 7023.30 7.70
INV-00005 Mid-Market 86 6849.04 7.41
INV-00216 Mid-Market 89 6793.37 7.31 
table(ledger$segment[robust_flag])
## 
## Enterprise Mid-Market        SMB 
##          7         30          1

The robust yardstick cannot be inflated by one bad row, so it finds all three value anomalies. But it now flags a crowd of perfectly ordinary invoices, because most of the book is SMB and every Mid-Market and Enterprise invoice looks extreme against an SMB-shaped median.

Detector 3 — compare like with like

ledger$robust_z_within <- ave(ledger$amount_usd, ledger$segment,
                              FUN = function(v) (v - median(v)) / mad(v))
strat_flag <- abs(ledger$robust_z_within) > 3
c(n_flagged = sum(strat_flag))
## n_flagged 
##         3
strat_hits <- ledger[strat_flag, c("invoice_id","segment","seats","amount_usd","robust_z_within")]
strat_hits$robust_z_within <- round(strat_hits$robust_z_within, 2)
kable(strat_hits[order(-abs(strat_hits$robust_z_within)), ], row.names = FALSE,
      caption = "Judged against its own segment, only the plants survive")
Judged against its own segment, only the plants survive
invoice_id segment seats amount_usd robust_z_within
INV-00136 Enterprise 150 112800.0 94.38
INV-00018 Enterprise 320 25068.8 13.22
INV-00013 SMB 24 150.0 -4.72

Detector scoreboard

planted_idx <- c(typo = i_typo, big_deal = i_big, near_zero = i_zero)
score <- data.frame(
  detector = c("classic mean +/- 3 sd", "robust median/MAD", "robust within segment"),
  mis_key    = c(classic_flag[i_typo],  robust_flag[i_typo],  strat_flag[i_typo]),
  large_deal = c(classic_flag[i_big],   robust_flag[i_big],   strat_flag[i_big]),
  near_zero  = c(classic_flag[i_zero],  robust_flag[i_zero],  strat_flag[i_zero]),
  total_flagged  = c(sum(classic_flag), sum(robust_flag), sum(strat_flag)),
  false_alarms   = c(sum(classic_flag) - sum(classic_flag[planted_idx]),
                     sum(robust_flag)  - sum(robust_flag[planted_idx]),
                     sum(strat_flag)   - sum(strat_flag[planted_idx]))
)
kable(score, caption = "Which detector found which anomaly, and what it cost in false alarms")
Which detector found which anomaly, and what it cost in false alarms
detector mis_key large_deal near_zero total_flagged false_alarms
classic mean +/- 3 sd TRUE FALSE FALSE 1 0
robust median/MAD TRUE TRUE TRUE 38 35
robust within segment TRUE TRUE TRUE 3 0

The anomaly no value-based scan can see

dup_ids <- ledger$invoice_id[duplicated(ledger$invoice_id)]
dup_rows <- ledger[ledger$invoice_id %in% dup_ids,
                   c("invoice_id","account_id","segment","seats","amount_usd")]
kable(dup_rows, row.names = FALSE, caption = "Found by checking invoice numbers, not amounts")
Found by checking invoice numbers, not amounts
invoice_id account_id segment seats amount_usd
INV-00046 ACC-1397 SMB 35 2910.6
INV-00046 ACC-1397 SMB 35 2910.6 
c(duplicate_flagged_by_classic = any(classic_flag[ledger$invoice_id %in% dup_ids]),
  duplicate_flagged_by_robust  = any(robust_flag[ledger$invoice_id %in% dup_ids]),
  duplicate_flagged_by_strat   = any(strat_flag[ledger$invoice_id %in% dup_ids]))
## duplicate_flagged_by_classic  duplicate_flagged_by_robust 
##                        FALSE                        FALSE 
##   duplicate_flagged_by_strat 
##                        FALSE

Its amount is perfectly ordinary — that is the point. Duplicates are a provenance problem, and only a provenance check finds them.

The charts a practitioner reads

cols <- ifelse(strat_flag, "#d94141", ifelse(robust_flag, "#F97316", "#5fa9dd"))
plot(ledger$seats, ledger$amount_usd, log = "y", pch = 19, col = cols, cex = 1.1,
     xlab = "seats on the invoice", ylab = "amount invoiced (US$, log scale)",
     main = "Invoice ledger: amount against seats")
abline(h = mu + 3 * sdev, lty = 2, col = "#888888")
text(max(ledger$seats), mu + 3 * sdev, "classic upper fence", pos = 2, cex = 0.8, col = "#666666")
legend("topleft", bty = "n", pch = 19, col = c("#d94141", "#F97316", "#5fa9dd"),
       legend = c("flagged within its own segment (the real anomalies)",
                  "flagged only by the pooled robust rule (false alarms)",
                  "not flagged"))

bars <- c(`as delivered` = sdev, `mis-key corrected` = sd2)
bp <- barplot(bars, col = c("#d94141", "#5fa9dd"), ylab = "standard deviation of amount (US$)",
              main = sprintf("One mis-keyed invoice inflates the yardstick %.1fx", sdev / sd2))
text(bp, bars * 0.5, sprintf("%.0f", bars), font = 2, col = "white")

What the lesson teaches

  1. Measure strangeness with a yardstick one bad row cannot move. Mean and standard deviation are computed from the data they are judging; the median and MAD are not.
  2. Masking is the failure mode to fear — the loudest anomaly hides the others by inflating the ruler. Correcting one row changed the large deal from invisible to a clear flag.
  3. Compare like with like. Most false alarms are not statistical failures, they are segment failures: an Enterprise invoice is not a big SMB invoice.
  4. Some anomalies have no value signature at all. The duplicate is found by checking invoice numbers, never by scanning amounts.
  5. A flag is a question, not a verdict. Three real anomalies here needed three different actions: correct the mis-key, keep the large deal, keep the credit — and log every change. Deleting the inconvenient ones is not cleaning, it is rewriting history.
  6. The tool needs at least two numeric columns: it scans them per-column with a robust score and jointly with Mahalanobis distance, so amount_usd and seats together catch rows where the relationship breaks, not just the size.