Payments Fraud Pattern & Issue Analysis Using SQL

One thing that comes up every so often is how to effectively analyse a large selection of transaction data for the purpose of understanding how fraudulent transactions are still occurring.

How to find the causes of fraud

The starting point for any good financial investigation as well noted in the popular movie The Big Short is to go into the transaction row-level data.

No amount of general reporting from an analyst or payments system is going to get to the level of granularity you will need to really find the issues at play.

Common reasons you might have payments fraud

There are usually a few reasons payment fraud might be happening. Most of the time nobody believes it could possibly be any of the below and instead it must be a particularly intelligent adversary. It’s probably not.

Often it can be one or more of the following issues:

• Your data is wrong: You think think some checks are happening but they’re not, someone has broken something;
• You have switched something off: Whether you’ve reduced the detection threshold or simply someone has hit the wrong switch the flood gates are now open;
• Human error: The number of times we’ve seen transactions that were approved as a result of human error is terrifying, more often than not after a fraudster getting in touch with some sort of customer support team;
• Cascade failure: You had an average set-up and now as a result of several combinations of wrong data and switching things off things have gone wrong;
• Clever adversary: They do exist and they are persistent if you have things that make it ‘easy’ for your normal customer then fraudsters will take advantage;

How to analyse the data

It took me weeks and weeks to analyse my first batch of fraud data before I knew what I was looking for or how to approach it. However, once I cracked it the writing was on the wall at how easy and obvious it was.

I’ll give a simplified example below of how to analyse payments data for fraud with enough info that you could take it to a more advanced level if you felt confident.