Fraud Detection Rules: Velocity Checks, Rule Scoring & Alert Tuning

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn raw checkout attempts into the three classic fraud features: amount relative to the card's history, a new-device flag from a device list and a billing-vs-IP country mismatch.
• Build rolling time windows with COUNTIFS and SUMIFS, count attempts and dollars per card in the last 24 hours and the last hour, and flag cards that break a velocity limit.
• Turn five fraud rules into a 0/1 hit matrix, weight the hits into one risk score with SUMPRODUCT and route each attempt to Approve, Review or Decline.
• Back-test a risk score against chargeback labels: alerts, true positives, precision and recall per threshold, then dollar recall and the highest threshold that still catches 90% of the fraud dollars.
• Convert analyst hours into a daily review capacity, turn alert rates into alerts and head-count, pick the lowest threshold the team can handle and watch a sale day build a backlog.
• Price every threshold: fraud dollars and chargeback fees saved against review costs and the friction false alarms cause, find the threshold with the highest net benefit and chart the curve.
• Read an alert log where each attempt lists the rules it fired, count hits and fraud hits per rule with wildcard COUNTIFS, compare precision and coverage, and chart which rules earn their place.
• Compare the live rule set with a challenger that ran in shadow mode on the same four weeks: precision, net benefit, a two-proportion z-test and a promote-or-keep decision.