Live data from Hacker News

SQL patterns I use to catch transaction fraud

analytics.fixelsmith.com

121–130 of 138 posts

Re: SQL patterns I use to catch transaction fraud

#121

Earlier quoted context omitted.

pushing slop is "productive means" for many people. they can sell the accounts/identities to election-manipulators or advertisers. bots age like fine wine.

So what? They can do whatever they want, that still doesn't mean we need to allow these posts.

That's fine. Ban AI research posts too.

Re: SQL patterns I use to catch transaction fraud

#122
post #2

> Real cardholders almost never buy something for exactly $1.00. Coffee is $4.73, gas is $52.81. The roundness is the signal. 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 a…

Their approach to “Suspicious merchants” also confuses me: the description doesn’t make logical sense to me and doesn’t match the abuse pattern as far as I understand it.

> 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.

Re: SQL patterns I use to catch transaction fraud

#123

I agree the post looks a little AI written but generally this kind of analysis is quite common. Leaving aside human heuristics that are generally too well known to catch real scammers (like time travel or "7 days", which is bad because often weekly patterns are important so at the very least look at 10 days) and actually have low precision, what I find odd it that all results just return a user ID. So this is really…

[flagged]

Thanks for clarifying the rule - I wasn't aware of that and follow it next time. But I also don't think it's a marketing link. The post explains why specifically in the cases of transaction fraud other signals are important, with specific SQL examples, and that should translate to any other payment provider.

Re: SQL patterns I use to catch transaction fraud

#124

Earlier quoted context omitted.

I'm seeing a few stores here and there which have a "round up to donate" option. I guess I'm a bit of a sucker and I always use that option. My groceries are always a round number as a result.

Ive always suspected that this is all of a tax dodge, a money spinner, and a pr exercise "we gave xxx to charity" - no, your customers did. Just set up a direct debit to your favourite charity.

ah, thanks for introducing me to this business model! :-)

Re: SQL patterns I use to catch transaction fraud

#126
post #112

Earlier quoted context omitted.

You're overthinking this. There's a publicity element to it, but the money just gets given to charity, like they say it does. It's not a conspiracy or a tax accounting trick.

How can you tell? And is this definitely universally true or just true of the 1 instance you happen to have personal experience with?

The risk with charity money is not the companies collecting a few cents but the charity itself.

Re: SQL patterns I use to catch transaction fraud

#127

Earlier quoted context omitted.

[flagged]

Thanks for clarifying the rule - I wasn't aware of that and follow it next time. But I also don't think it's a marketing link. The post explains why specifically in the cases of transaction fraud other signals are important, with specific SQL examples, and that should translate to any other payment provider.

[flagged]

Re: SQL patterns I use to catch transaction fraud

#128
post #38
post #3

> Drawback: this doesn’t work until you have history. New accounts have no baseline. 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.

The incentives of the bank is to cut fraud. 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 whe…

Spend retention is by far the highest priority. This is why they overnight fedex replacement cards.

The worst scenario for a credit card issuer is when a customer, for whatever reason, starts using another bank’s card in their wallet as their daily driver.

Re: SQL patterns I use to catch transaction fraud

#129
post #44
post #2

> Real cardholders almost never buy something for exactly $1.00. Coffee is $4.73, gas is $52.81. The roundness is the signal. 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 a…

The "transaction outside usual hour range" seems pretty basic. 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.

Two things I have found that absolutely positively freak the shit out of my bank:

Buying a full tank of gas and then a full tank of petrol (dual-fuel vehicle) in two separate card transactions one after the other. Can't use the same pump, annoyingly, at least with the old system in Morrisons. Don't know if it's different since their petrol stations have been bought over by Motor Fuel Group.

Similarly, buying a full tank of gas at Morrisons in Bradford where the supermarket chain's headquarters are, then driving five hours north and refuelling again in a different Morrisons which show the transaction as coming from their banking systems in Bradford but tagged as a city in Scotland. This is apparently because it's implausible to drive from the central England to central Scotland in a few hours, and then need to refuel.

They are (or was at least, last checked a year ago) 100% repeatable.

Re: SQL patterns I use to catch transaction fraud

#130
post #77
post #2

> Real cardholders almost never buy something for exactly $1.00. Coffee is $4.73, gas is $52.81. The roundness is the signal. 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 a…

Yeah I was in a bar one night and was peckish, so tried to buy a packet of crisps. They said minimum spend on card was £5, so I said just charge me the £5 it's fine. 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!

Why didn't you just buy five quid's worth of crisps?
Post reply on HN