Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL patterns I use to catch transaction fraud

analytics.fixelsmith.com

Post reply on HN
Print view View original

91–100 of 138 posts

Re: SQL patterns I use to catch transaction fraud

#91

Post by VladVladikoff »

jwr wrote: post #88

After 35 years of building software systems I've learned to temper my hubris. These days I rarely assume things to be "definitely true". 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…

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

Re: SQL patterns I use to catch transaction fraud

#92

Post by [deleted] »

LandR wrote: post #77
jstanley wrote: 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!

[deleted]

Re: SQL patterns I use to catch transaction fraud

#93

Post by adrian_b »

jstanley wrote: 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…

I do not know if this is still used anywhere, but in the past there were places, e.g. hotels or car rental services, where the validity of a credit card was tested by a $1.00 transaction, before booking a room or renting a car.

Re: SQL patterns I use to catch transaction fraud

#94

Post by baranazal »

enoent wrote: post #41

> Fraud detection in transaction data is mostly SQL. Not machine learning, not graph databases, not whatever Gartner is hyping this year. SQL, run against the right tables, with the right joins, looking for the right shapes. 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 i…

[flagged]

Re: SQL patterns I use to catch transaction fraud

#95

Post by adrian_b »

nswango wrote: post #44
jstanley wrote: 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.

On the other hand, for online transactions I frequently do them outside the usual hour range.

However, before going to a distant country, which was also in very different time zone, I warned the bank that issued the card that I intended to use, so that they would not consider suspicious either the place or the time of the transactions.

Re: SQL patterns I use to catch transaction fraud

#96

Post by layer8 »

reconnecting wrote: post #61

Hacker News, we need to talk! "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-Expl…

The soundtrack album for the novel is so cringe. Also warning: The music starts autoplaying on [2].

Re: SQL patterns I use to catch transaction fraud

#97

Post by gosub100 »

time4tea wrote: post #54
themafia wrote: post #12

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.

> Just

the point of these drives is to get more people to give to charity. Then you use a lullaby-word as if setting up a charitable donation is as easy as saying "yes" when the checker asks if you want to give a small donation.

Re: SQL patterns I use to catch transaction fraud

#98

Post by gosub100 »

jwr wrote: post #88

After 35 years of building software systems I've learned to temper my hubris. These days I rarely assume things to be "definitely true". 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…

The card processor collects a lot of data, presumably they would have a flag whether the card was used via a phone or real plastic. I suspect the "card used in 2 locations" thing is pretty old. Cards are supposed to have switched to chip for many years now. AFAIK magstripes are the only ones that can be cloned.

Re: SQL patterns I use to catch transaction fraud

#99

Post by gosub100 »

dogscatstrees wrote: post #16

The main problem with these SQL calculations is that they are deterministic shortcuts for a probabilistic problem. Fraud is not usually a “true because rule X matched.” It is more like "what is the probability this is fraudulent"? SQL patterns are useful, but they are blunt instruments. I really don't think banks use deterministic heuristics but more data science stuff.

what is "data science stuff" ? It's simply adding weights to indicators to come up with a score, and determining a threshold. It's not magic.

Re: SQL patterns I use to catch transaction fraud

#100

Post by jason1cho »

People here provide counterexamples to show this article is bullshit. Don't forget, fraud detection is about statistics. Outliers always exist.

Machine learning systems also learn your pattern. The article gives simple SQL rules. Don't dismiss this article as worthless.

Post reply on HN