Surely this depends on how the vendor sets their prices? If you're going to buy something from a website to test a stolen credit card you don't just get to make up your own prices.
And I think you may be over-indexing on the US "prices don't include tax" thing. Elsewhere, round-number prices are extremely common.
In fact a lot of the rest of the stuff in the post seems like it wouldn't work very well either. (E.g. you're flagging anyone who has done a transaction in the last 90 days outside the range of hours at which they have 2+ transactions? Wouldn't that be like 50% of people?).
It's unclear to me whether this article is an attempt at breaking down complex expertise into over-simplified SQL queries, or whether it is all speculative and made up.
There is a conflict between "Six SQL patterns I use to catch transaction fraud" and "Nothing here comes from anything I’ve actually worked on or seen".
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.
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
> Real cardholders almost never buy something for exactly $1.00. Coffee is $4.73, gas is $52.81. The roundness is the signal.
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!
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?
Just set up a direct debit to your favourite charity.
We learn simultaneously that 'your team' shouldn't rely on any one of those patterns ('none of them is enough'), but that pattern 1 'alone will surface a useful amount of fraud'.
We also read strange sentences like "Every analyst on your team will use them (ie window functions) once they exist, and adding the next fraud pattern stops being a project. [end of paragraph]"
Or irrelevant discussions about how filtering by "IS NULL" might be not applicable when almost none of the provided examples uses it (and the one which does uses it in different context).
This is low quality and too long.
Or normal people living in Europe in border-adjacent areas.
Also, I guess you don't include card-not-present transactions in this, but you incorrectly assume that every merchant has their location set correctly. And that every sale happens in a brick-and-mortar establishment, not from travelling salespeople or whatever. And that all transactions happen online.
"Fixel Smith" is an AI-generated person, with an article that has very little to do with fraud analysis. 'This' is also a music artist (1), novelist (2), fraud analyst (3), influencer (4), and whatever else you can imagine.
220+ points and 70 comments, and very few notice it's quite a fake post — and no one that it's an AI generated person?
1. https://www.amazon.it/Forged-Soundtrack-Explicit-Fixel-Smith...
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.
Well,sure. But some people come here just for the comments and don't read the articles
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.
I question the described approaches. For example, while impossible travel is a legitimate and widely used technique, it's related to online user behaviour based on IP address. Moreover, tirreno, for example, has separate rules for cases where the IP clearly comes from Apple Relay or VPN/Tor — those are separate flags. I assume some or all examples are LLM-generated, as the context is mixed up and no one actually collects GPS location in bulk for card swipes.
For example "Impossible travel": these days you can add your credit card to your phone and use Apple Pay. Well, this is useful for many things, one of them being adding your credit card to your kid's (teenager) phone, so that your kid can use your card in case of need/emergency when they are away from you. I did exactly that recently and actually worried about fraud control systems when my child paid using my card in Boston while I was in Europe.
Many things which you think are true might not be.
Anecdotally, US banks are terrible at building fraud control systems. It seems US banks assume any transaction that is charged by an entity outside the US is fraud. In my 10-year history of running a SaaS, the US banks and their "fraud control" systems have been one of the biggest billing problems.
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.
It's also not all program-integrity, which is the only work that could justify such blanket statements. Worse is better as long as it addresses the problem domain.
Fintech clients are generally interested in knowing whether a transaction happening _right now_ is fraud. They want to know that in a few milliseconds, for high-dimensional data. It's work done at a scale where relational databases cannot meet these real-time constraints, and instead find other uses like historical data loading. That's how you end up with in-memory databases, stream-processing engines, and yes, even machine learning.
Having said that, some of the author's points are valid, and I'm looking forward for their next writings, in particular dealing with noisy alerts is a general problem beyond performance engineering.
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.
If a card swipes in Chicago and seven minutes later swipes in Los Angeles, one of those swipes is fake.
Can also imagine an edge case: couple shares an online account, one is traveling and purchases with the saved card details.
This is an underrated CX factor: If my card gets denied when i’m a new customer or exhibiting a new pattern, i’m impressed with their software.
However if they deny a transaction where there is any previous history of me authenticating, then I’m frustrated by their naive paranoid algorithm.
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.
So this is really just surfacing cases, but with not enough context to be useful to prioritise. I would expect a score to be included.
Apart from that it misses a lot of signals like refunds, declines, disputes etc [1].
1) https://stripe.com/gb/guides/improve-fraud-management-with-r...
What about the tables?
> 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.
It isn't though, so balancing it with other rules is fine.
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).
Bunch of thresholds, no data proving those thresholds are meaningful.
Lagging window functions and/or lateral joins probably would have reduced it to 1/4 the size but definitely increased the cost versus just narrowing the sets into smaller tables first.
> The roundness is the signal.
> Slight pain, same result.
to point at a few.
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.
How do you deal with vacations and online shopping. You could be in another country or two in a few hours and purchase from across the world
Machine learning systems also learn your pattern. The article gives simple SQL rules. Don't dismiss this article as worthless.
Signal's he can check? So some random dude is looking at my credit card purchase history while playing around with his SQL queries?
But I must admit there was a point where I suddenly lost my love for SQL and it was pretty much when the OVER PARTITION BY syntax appeared.
It never clicks. I always have to look up how it works, I always find it unintuitive. I've never understood why I hate it so much.
Or, the cardholder is trying to do the cannonball run:
> Most people are creatures of habit when they spend money. A nine-to-fiver doesn’t suddenly start buying gas at 3am.
Breaking out of a habit once in a while is what keeps one's mind sharp.
A big "fuck you" to financial analysts with those groundhog-day mindsets for making my life much more miserable than it needs to be and for adding a chilling effect to those little getaways that make life interesting and worthwhile. I despise you for this.
chargeback-mcp
or would you turn it all into a markdown file and call it a skill?