SQL patterns I use to catch transaction fraud

Coffee usually _is_ a round number in my experience, and I know of people who aim for round numbers when filling their car, and of fuel stations which require a pre-set value, often 10, 20, 50€ etc

I don't usually buy gas, coffee or snacks at 2am. But on the very rare occasion that I do, I'm dealing with some kind of personal emergency and don't also want to have to call my bank.

I get that that's also a time opportunistic thieves, etc, might be operating. But the cost of false positives is also a thing.

a) trivial to bypass by adding dither to the test transactions and

b) trivial to improve upon with proper statistical analysis and

c) shouldn't this kind of heuristic pattern recognition with no expectation of near-100% accuracy be what AI is good at?

Card got blocked as they thought it was fraud. Annoying! And not something inebriated me wanted to deal with at 2am.

Ok. Maybe they protected me from myself, but still!

> When a skimmer compromises a card reader at, say, a gas pump, you don’t get one fraud case. You get dozens. Every card swiped at that pump for the next few weeks is now in someone’s database. So the symptom from the merchant side is: an unusual number of unrelated cards spending more than usual, in a short window.

So he checks for hour-bucketed increases in high-value transactions originating from that merchant.

Seems to me like a good way to catch a sale, an opening, a launch event, or a product “drop,” a single high-value sale that somebody spreads across several cards… less so a good way to detect a steady trickle of stolen card data that’s inexplicably used back at the same merchant.

If you’re installing a card skimmer, why would you charge the stolen cards at the same business where you’re stealing them? And why would you concentrate your spending into bursts if the skimmer’s harvesting all day every day?

If you’re the merchant doing the skimming in order to spend at your own store, wouldn’t it be easier to punch a higher amount into the terminal? If you’re a skimming ring, wouldn’t you prefer to have purchasing power rather than this $5000 threshold (?!) of extra gas (plus a giant neon sign advertising where you placed your skimmer)?

Wouldn’t a more sensible approach involve something like looking for merchant clusters in the combined transaction histories of known-stolen accounts?

The LLM runs so strong in this whole enterprise… I want to give the person the benefit of the doubt, but I can’t resist the sneaking suspicion that LLM fabulism to push a slop novel just wasted 15 minutes of my life.

Kind of fascinating, though it could still be a person doing this using AI as opposed to an entirely generated persona. Thanks for bringing it up.

Makes me wonder if this AI flood uncovers the unflattering truth about this community acuteness, or it's only a failure of existing guardrails and we just need to change them.

It's definitely a real post. Yes it's obviously LLM-written, but if the worst thing you can say about an article is that it looks LLM-written, then maybe you don't have any real criticisms.

Whether the contents are made up or not is unclear, but you can criticise the content of the article without needing to speculate on whether it was written by an LLM or whether it is a work of fiction. It has plenty of much more concrete flaws.

And my favourite most hated pattern, the no no no:

> Not machine learning, not graph databases, not whatever Gartner is hyping this year.

This is Claude talking isn’t it.

For Prevention, you're always going to be constrained by latency requirements, available data and an incomplete picture of user behaviour. You make a quick decision using ML and rules that deals with the majority of cases. But those constraints make it impossible to precisely prevent all fraud.

Detection deals with the downstream consequences of this. A team of analysts will typically analyse the accepted transactions for signs of fraud. This is particularly important for fraud types where you don't get an external signal like a chargeback or customer complaint. Platform integrity is one such example. But Fintechs will also see this building anti-money laundering systems - you need to go looking for the fraud. This is the process the article is describing.

I say they're complementary because the detected transactions become the labels for training and evaluating the next iteration of prevention models.

This rings home so true, as a Canadian company I am SO TIRED of US banks flagging our transactions as fraud. We have done so much to try to prevent it too. We have a mail forwarding office address in the US. A bank account in USD in the US registered to that address, the merchant account tied to that charging in USD, and still we get these fraud flags. And we’re over the 10 year mark now, I think almost 15. You would thing we would have built up some trust at these banks, but nope.

My next biggest hassle lately is we are a “tokenize and bill later” type service, and we don’t charge monthly recurring exact same amount, depends on the users incurred charges in that period. And lately it seems most Americans leave their cards on a permanently lock, and only unlock to allow a charge, this means most of our charges decline initially until the user unlocks their card and retries the payment. A real support headache if any has a fix to either of these problems I would pay good money for it.

Fraudulent transactions will eventually cost the bank (when they would have to reverse/reimburse it and eat the loss). A denied transaction only results in an angry customer who will quickly forget after they complained - so the customer bears the brunt of the externalized cost.

Therefore, the bank's incentive is to err on the side of more caution, and deny transactions when finding false positives.

Human analysts need to be able to explain to compliance in a single 5 minute email why a specific transaction was declined, and most importantly, what could have been done differently to avoid the adverse decision.

Fixing one problem with ML often creates two new problems that aren't quite obvious yet. SQL tends to have fewer surprises with regard to regressions and unexpected side effects as things change over time.

> What works is running them all and scoring each transaction across the signals. A transaction that fails on three or four of them is almost always fraud. A transaction that fails on one might be your grandma being weird with her debit card on vacation.

The DPAN is translated into the CPAN by software at the issuing bank, so it's not identifiable by the merchants.

Merchants get the "last 4" digits, but that's not enough to identify specific CPANs.

Basically it's not just banks and formal financial institutions doing this, and how they do it depends on the company size. Size tends to correlate not only with how many resources you have for a risk team, but also with whether fraud rings are targeting you.

Usually what I've seen is that companies start with some kind of batch SQL/simple logic process that runs daily and tends to flag accounts for manual review and block automatic events like settlement or trading (or whatever the platform does) until that review has been done. Then over time the company will transition to an ML-based approach that still mostly flags things for manual review. The goal of the ML is to improve the precision of the flagging without hurting dollar recall or fraud event recall too much. Depending on the payment system companies may be sensitive to both (for example, in ACH if you get too many returns, even very low dollar payment returns, you're going to get a hard time from your partner bank and you risk not being able to use ACH anymore).