analytics.fixelsmith.com

How I backtest a fraud rule before it ships

Most fraud rules ship on intuition. Somebody sees a pattern in last week’s confirmed cases, writes a threshold that would have caught them, and pushes it live. Then the team spends the next month discovering what else the rule catches, one false positive at a time.

If you keep alert history and disposition labels, you already have most of what you need to test a rule against the past before it touches production.

The backtest is four queries: replay the rule, compare it with known outcomes, check how much it overlaps with existing rules, and sweep the threshold.

I run all four on anything I propose. The overlap query kills more rule ideas than the other three combined.

What you need before any of this works

Three tables:

Without disposition data, you can estimate volume. You can’t estimate whether the alerts would be useful. Disposition discipline was the first of the five wins in the noisy-alerts post, and this is where it pays off a second time: the labels you recorded to tune old rules are what let you test new ones for free.

Chargebacks, customer reports, and manual referrals make the test better because they include fraud that didn’t come through the current rules. Pull those outcomes in too. Otherwise a new rule gets evaluated almost entirely against cases the old rules already found.

I usually combine those sources into a known_outcomes view with one row per transaction and an outcome of fraud or legitimate. The one-row rule matters. A transaction confirmed through two different channels shouldn’t get counted twice.

Query 1: replay the rule against history

Write the candidate rule as a plain SELECT over historical transactions. No deployment, no rules engine, no feature branch.

If the rule is “flag transactions over $200 where the account used a new device in the last 24 hours,” the replay is:

-- Candidate rule replayed over the last 6 months
SELECT
  t.transaction_id,
  t.account_id,
  t.amount,
  t.created_at
FROM transactions t
WHERE t.amount > 200
  AND EXISTS (
    SELECT 1
    FROM device_events d
    WHERE d.account_id = t.account_id
      AND d.first_seen_at >= t.created_at - INTERVAL '24 hours'
      AND d.first_seen_at <= t.created_at
  )
  AND t.created_at >= CURRENT_DATE - INTERVAL '6 months';

Six months is my starting point, not a requirement.

If the business is seasonal, the window needs to include the relevant season. If the checkout flow or device-tracking logic changed three months ago, six months of history may be worse than three because half the test no longer represents production.

The row count is the first sanity check. If the replay returns 40,000 transactions over roughly six months, the rule would generate about 220 alerts a day. If the team clears 500 alerts a day across the entire rule set, this one rule would take almost half its capacity.

There’s no reason to debate precision until the volume problem has an answer.

Query 2: compare the replay with known outcomes

I report two rates here because they answer different questions:

WITH replay AS (
  -- Query 1 goes here
)
SELECT
  COUNT(*) AS would_fire,

  COUNT(*) FILTER (
    WHERE o.outcome = 'fraud'
  ) AS known_fraud,

  COUNT(*) FILTER (
    WHERE o.outcome IN ('fraud', 'legitimate')
  ) AS labeled_events,

  ROUND(
    100.0 * COUNT(*) FILTER (WHERE o.outcome = 'fraud')
    / NULLIF(
        COUNT(*) FILTER (
          WHERE o.outcome IN ('fraud', 'legitimate')
        ),
        0
      ),
    2
  ) AS precision_among_labeled_pct,

  ROUND(
    100.0 * COUNT(*) FILTER (WHERE o.outcome = 'fraud')
    / NULLIF(COUNT(*), 0),
    2
  ) AS known_fraud_rate_pct

FROM replay r
LEFT JOIN known_outcomes o
  ON o.transaction_id = r.transaction_id;

There’s a problem with both numbers.

Most labels exist because an old rule fired and an analyst looked at the transaction. Transactions the old rules missed are much less likely to have a disposition at all.

precision_among_labeled_pct ignores transactions with no outcome. It’s the cleaner precision calculation, but it inherits the bias in the labeling process.

known_fraud_rate_pct includes every replayed transaction in the denominator, even the unlabeled ones. I treat it as a conservative known-fraud rate, not as measured production precision.

Unlabeled doesn’t mean legitimate. It means you don’t know.

I still use 2 percent as a first-pass bar, applied to precision_among_labeled_pct, when I’m comparing rules evaluated through the same label pipeline. A candidate that can’t clear that bar against known outcomes usually doesn’t get better after it starts creating work. But the percentage only means something when the rules were measured the same way.

Query 3: incremental catch, the query that kills most rules

A candidate can match plenty of known fraud and still add almost no coverage.

The cleanest overlap check uses the transaction or case that caused the alert:

WITH replay AS (
  -- Query 1 again
),
candidate_fraud AS (
  SELECT
    r.transaction_id,
    r.account_id,
    r.created_at
  FROM replay r
  JOIN known_outcomes o
    ON o.transaction_id = r.transaction_id
   AND o.outcome = 'fraud'
),
coverage AS (
  SELECT
    c.*,
    EXISTS (
      SELECT 1
      FROM alerts a
      WHERE a.transaction_id = c.transaction_id
    ) AS already_alerted
  FROM candidate_fraud c
)
SELECT
  COUNT(*) AS known_fraud_matched,
  COUNT(*) FILTER (
    WHERE NOT already_alerted
  ) AS known_fraud_not_previously_alerted
FROM coverage;

If alerts are linked at the case level, use case_id instead. That’s often closer to the operational question: did another rule already bring this case to the team’s attention?

An account-and-time-window comparison is a workable fallback when neither identifier exists. It’s still a proxy. An unrelated alert on the same account can make the candidate look redundant when it wasn’t.

This is where a lot of proposed rules fall apart. They catch fraud, but almost every case was already surfaced by velocity, geography, device risk, or another existing signal.

A candidate that matches 60 known-fraud transactions sounds useful. If 57 of those transactions already generated an alert, the actual proposal is all of the candidate’s alert volume in exchange for three additional historical matches.

That may still be worthwhile. It’s a different decision from the one suggested by the original count.

When overlap is high, I look at the existing rule before adding another one. The better change may be adjusting its threshold or its place in the scoring model.

Query 4: sweep the threshold

The original proposal used $200. There’s nothing inherently correct about $200.

Remove the amount threshold from the replay and group the results into buckets:

WITH replay AS (
  -- Query 1 with the amount threshold removed
),
bucketed AS (
  SELECT
    FLOOR(r.amount / 100) * 100 AS threshold,
    COUNT(*) AS bucket_volume,
    COUNT(*) FILTER (
      WHERE o.outcome = 'fraud'
    ) AS bucket_known_fraud
  FROM replay r
  LEFT JOIN known_outcomes o
    ON o.transaction_id = r.transaction_id
  GROUP BY 1
),
sweep AS (
  SELECT
    threshold,
    SUM(bucket_volume) OVER (
      ORDER BY threshold DESC
    ) AS cumulative_volume,
    SUM(bucket_known_fraud) OVER (
      ORDER BY threshold DESC
    ) AS cumulative_known_fraud
  FROM bucketed
)
SELECT
  threshold,
  cumulative_volume,
  cumulative_known_fraud,
  ROUND(
    100.0 * cumulative_known_fraud
    / NULLIF(cumulative_volume, 0),
    2
  ) AS cumulative_known_fraud_rate_pct
FROM sweep
ORDER BY threshold DESC;

Each row approximates the results of setting the rule at that threshold.

Moving the threshold downward should increase both volume and known-fraud matches. The useful question is how quickly each one increases. Moving from $300 to $200 might add 8,000 alerts and one known-fraud match. Moving from $400 to $300 might add 200 alerts and 20 matches.

That’s the threshold decision. Not which number looks clean in a meeting, but how much analyst work the next bucket buys.

There usually isn’t one mathematically perfect cutoff. The threshold has to fit the team’s capacity, the cost of the fraud being detected, and the confidence you have in the labels.

Then run it in shadow mode

Passing the backtest doesn’t mean the rule should begin creating analyst work immediately.

Historical SQL can’t reproduce everything that happens in production. Fields arrive late. Streaming logic behaves differently from a batch query. Device identifiers change. A rule that looks manageable against six months of data can behave very differently during a promotion or a fraud spike.

I start new rules in log-only mode. They evaluate production traffic and write would-be alerts to a table, but they create no analyst work.

Two weeks is usually enough to compare volume and catch obvious production-data problems. It may not be enough to measure quality. That depends on how quickly analyst dispositions, chargebacks, and other outcomes arrive.

Once enough outcomes have matured, run the same volume, outcome, and overlap checks against the shadow results. If they look materially different from the backtest, find out why before turning on the queue.

The habit

None of this is sophisticated. Four queries, then shadow mode.

The point is to make a proposed rule prove that it deserves analyst time before it gets any.

Find the problem in SQL, not in the analyst queue.

Next post: matching accounts that belong to the same person when that person would rather you didn’t. Entity resolution in plain SQL.