Modèles SQL utilisés pour détecter les fraudes transactionnelles
(analytics.fixelsmith.com)- La détection de fraude commence souvent, avant le machine learning, par une bonne modélisation des tables et jointures, puis par la recherche en SQL de schémas anormaux liés à la vitesse, au lieu, au montant, au commerçant et à l’horaire
- La velocity consiste à repérer des rafales de transactions sur une courte période pour un même titulaire de carte, avec des ajustements nécessaires sur la fenêtre temporelle, les seuils et les listes blanches de faux positifs
- L’impossible travel utilise
LAG()et un calcul de distance pour détecter comme signal fort de carte clonée des cas comme un paiement à Chicago suivi 7 minutes plus tard d’un paiement à Los Angeles - Les anomalies de montant repèrent des sommes comme
$1.00,$99.99,$499.99, qui peuvent suggérer des tests de carte ou des contournements de règles, mais sont moins pertinentes pour les transactions liées aux prestations - En combinant hausse soudaine chez un commerçant, transactions hors horaires habituels et colonnes dérivées par fonctions de fenêtre, on peut attribuer un score à partir de plusieurs signaux et réduire les cycles d’itération de plusieurs semaines à quelques heures
Modèles SQL pour repérer les signes de fraude dans les données de transaction
- La détection de fraude commence souvent, avant le machine learning ou les bases de données graphe, par les bonnes tables et jointures et par du SQL capable de repérer des formes de transactions inhabituelles
- Cela s’applique aux données où de l’argent circule et où des logs sont conservés, comme les cartes de crédit, les demandes de remboursement de santé, l’e-commerce, les POS ou les programmes publics d’aide sociale
- Sur un nouveau dataset, on construit généralement les motifs dans cet ordre : velocity, impossible travel, anomalies de montant, concentration par commerçant, horaires anormaux, puis signaux basés sur les fonctions de fenêtre
1. Velocity : trop de transactions en peu de temps
- Quand quelqu’un essaie d’épuiser rapidement une carte ou un compte volé, on observe souvent un regroupement de transactions sur une courte période pour un même titulaire
- La requête de base regroupe les transactions des 30 derniers jours par heure et cherche les plages où le nombre de transactions par
cardholder_iddépasse un seuil - Les paramètres clés à ajuster sont la taille de la fenêtre temporelle et le seuil de nombre de transactions
- On peut exécuter en parallèle des versions sur 1 minute, 5 minutes et 1 heure pour les comparer
- Les groupes qui testent des cartes concentrent les transactions en quelques secondes, tandis que les groupes qui fraudent des prestations peuvent opérer sur une demi-journée ; l’échelle n’est donc pas la même
- Des utilisateurs légitimes peuvent aussi dépasser le seuil
- opérateurs qui gèrent des distributeurs automatiques
- personnes rechargeant en masse des cartes prépayées
- après une première exploration, il faut donc une liste blanche de faux positifs
- Une fenêtre glissante calcule le nombre de transactions sur les 5 dernières minutes avec
COUNT(*) OVER (...) RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW QUALIFYfonctionne sur Snowflake, BigQuery, Databricks, Teradata- sur Postgres, il faut encapsuler toute la requête dans une CTE puis filtrer à l’extérieur
2. Impossible travel : déplacement physiquement impossible
- Si une carte est utilisée à Chicago puis 7 minutes plus tard à Los Angeles, il y a de fortes chances que l’une des deux transactions soit frauduleuse
- Ce motif est un signal fort de carte clonée, car il existe très peu de raisons légitimes pour qu’une même carte se retrouve en deux lieux éloignés en quelques minutes
- La requête utilise
LAG()pour récupérer l’heure et la position de la transaction précédente, puis calcule la distance et le temps écoulé entre l’ancienne et la nouvelle position haversineest une fonction qui calcule la distance orthodromique (great-circle distance)- la plupart des data warehouses la proposent
- sinon, c’est une fonction assez simple à écrire soi-même
- Le seuil d’exemple est 600mph
- la vitesse de croisière d’un avion de ligne est d’environ 575mph, donc cela signifie une vitesse impossible même en avion
- si on descend à 100mph, on peut aussi capter des déplacements terrestres rapides, mais on commence à inclure des voyageurs légitimes ou des parents transportant leurs enfants
- On peut aussi examiner des signaux proches
- des transactions dans deux villes éloignées d’un même État en moins de 5 minutes peuvent suggérer un réseau local de clonage
- des transactions dans plusieurs codes ZIP en moins d’une heure peuvent suggérer un réseau de skimmers opérant dans une même zone
- des transactions qui franchissent une frontière en moins de 10 minutes peuvent signaler un réseau international
3. Amount anomalies : montants suspects
- Certaines formes de montant apparaissent souvent dans la fraude, mais rarement dans l’usage normal
- Les conditions d’exemple recherchent les plages suivantes
$1.00,$5.00,$10.00- de
$99.50inclus à moins de$100.00 - de
$499.50inclus à moins de$500.00
- Les petits montants ronds en dollars sont généralement un signal de test de carte
- il s’agit de vérifier si un numéro issu d’un dump de cartes fonctionne réellement avant de le revendre
- il est rare qu’un vrai titulaire achète exactement un article à
$1.00 - un café sera plus probablement à
$4.73, et de l’essence à$52.81, plutôt qu’à un montant parfaitement arrondi
- Les montants juste sous un seuil ont une autre signification
$99.99peut chercher à éviter une vérification d’identité exigée à partir de$100dans de nombreux endroits$499.99peut chercher à éviter une limite quotidienne de retrait ATM à$500- c’est un signal que la personne connaît les règles et reste juste en dessous
- Dans les transactions liées aux prestations, les montants arrondis sont beaucoup moins utiles
- les prestations ne sont pas testées de la même façon qu’une carte
- dans ce cas, les bénéficiaires en double sont souvent un signal plus important
4. Suspicious merchants : concentration anormale par commerçant
- Si un lecteur de carte, par exemple sur une pompe à essence, est infecté par un skimmer, cela peut produire non pas un cas, mais des dizaines de fraudes
- Toutes les cartes passées sur ce lecteur pendant plusieurs semaines peuvent se retrouver dans la base de données de quelqu’un
- Vu du côté commerçant, cela se manifeste souvent par une hausse anormale du nombre de cartes sans lien entre elles sur une courte période, avec aussi des montants plus élevés
- Un exemple simple consiste à regrouper les 7 derniers jours par commerçant et par heure, puis à calculer
- le nombre de cartes uniques
- le nombre total de transactions
- le montant total des transactions
- et à rechercher les heures où le nombre de cartes uniques dépasse 20 et le total dépasse
$5000
- Les seuils statiques posent un problème d’ajustement à la taille
- Costco peut dépasser ce seuil en 90 secondes
- une librairie d’occasion ne le dépassera presque jamais
- Une meilleure approche consiste à comparer chaque commerçant à sa propre baseline historique
- regrouper les 60 derniers jours par heure
- calculer, pour chaque commerçant, le nombre moyen de cartes uniques sur les 168 derniers buckets horaires
- repérer les périodes où le nombre actuel de cartes uniques dépasse 3 fois cette moyenne
- Les 168 buckets horaires correspondent aux créneaux horaires des 7 derniers jours
- parce que la saisonnalité quotidienne et hebdomadaire compte
- le même café n’a pas la même baseline le mardi à 14h et le samedi à 9h
- On peut commencer avec un seuil de 3 fois la normale
- suffisamment souple pour éviter un déluge d’alertes
- suffisamment strict pour faire ressortir les heures réellement anormales
5. Off-hours : transactions en dehors des horaires habituels d’une personne
- La plupart des gens ont des habitudes de dépense
- Si une personne qui travaille de 9h à 17h commence soudainement à faire le plein à 3h du matin, il est possible que sa carte soit utilisée par quelqu’un d’autre, ou qu’elle soit en voyage
- Le fait qu’elle voyage peut être vérifié ensuite avec d’autres signaux
- La requête calcule sur les 90 derniers jours le nombre de transactions par titulaire et par heure, puis ne considère comme horaires habituels que les heures où il y a eu au moins 2 transactions
- Ensuite, elle détecte les nouvelles transactions dont l’heure est en dehors de la plage
earliest_houràlatest_hourpour ce titulaire - La condition « au moins 2 transactions dans cette tranche horaire » dans la sous-requête interne est importante
- elle évite qu’un seul plein nocturne fortuit, survenu il y a 3 mois, soit intégré aux horaires habituels
- elle aligne la règle sur une véritable habitude, pas sur un événement ponctuel
- L’inconvénient est qu’il faut des données historiques
- un nouveau compte n’a pas encore de baseline
- pour ces comptes, on peut utiliser les modèles horaires globaux de l’ensemble des utilisateurs ou ignorer ce motif jusqu’à avoir quelques mois d’historique
6. Combiner les signaux avec les fonctions de fenêtre
- Les motifs basés sur les fonctions de fenêtre ne constituent pas un type de fraude séparé ; ils servent plutôt à transformer les cinq motifs précédents en signaux combinables
- Pour chaque transaction, on peut préparer les colonnes dérivées suivantes
- temps écoulé depuis la transaction précédente :
timestamp - LAG(timestamp) - changement de commerçant : comparaison entre le
merchant_idprécédent et lemerchant_idcourant - montant cumulé sur les 24 dernières heures :
SUM(amount) OVER (...) - rang de la transaction dans la journée :
ROW_NUMBER()
- temps écoulé depuis la transaction précédente :
- Une fois matérialisées, ces colonnes permettent de réduire les règles de fraude à de simples expressions de filtre
- On peut repérer un groupe de test de cartes avec les conditions suivantes
- transaction au moins la 5e de la journée
- moins de 60 secondes depuis la transaction précédente
- commerçant différent de celui de la transaction précédente
- Si une nouvelle hypothèse de fraude peut s’exprimer comme un filtre SQL plutôt que comme un ticket d’ingénierie, le cycle d’itération passe de plusieurs semaines à quelques heures
- Au final, cela permet d’attraper plus de fraudes, plus rapidement
Comment utiliser ces motifs ensemble
- Aucun motif pris isolément n’est suffisant
- Chacun a des limites claires
- la velocity produit des faux positifs comme les opérateurs de distributeurs automatiques
- les déplacements géographiquement impossibles ratent les fraudes qui se déroulent dans une même grande zone urbaine
- les anomalies de montant fonctionnent mal hors du contexte des tests de cartes
- les règles sur les horaires anormaux nécessitent de l’historique
- En pratique, on exécute tous les motifs et on attribue un score à chaque transaction à partir de plusieurs signaux
- Une transaction qui déclenche trois ou quatre signaux est presque toujours frauduleuse
- Une transaction qui n’en déclenche qu’un seul peut correspondre à un usage inhabituel mais légitime d’un titulaire en voyage
- Si vous débutez dans la détection de fraude, mieux vaut commencer par la velocity
- elle fait ressortir un volume utile de fraude
- elle capte relativement peu d’activité normale
- son coût d’exécution est faible
- Si vous avez déjà mis en place les points 1 à 5, le prochain investissement utile concerne les colonnes brutes basées sur les fonctions de fenêtre
- une fois créées, tous les analystes de l’équipe peuvent les utiliser
- ajouter un nouveau motif de fraude ne devient plus un projet à part entière
Points de vigilance
-
Gestion des
NULL- dans les vraies tables de transactions, on n’utilise souvent pas
NULLcomme dans les tutoriels SQL - beaucoup de systèmes legacy utilisent des valeurs sentinelles comme
9999-12-31pour « pas de date de fin » ou0001-01-01pour « pas de date de début » - un filtre avec
IS NULLpeut donc rater silencieusement ce type de lignes - il faut vérifier les conventions de la table concernée avant d’écrire la clause
WHERE
- dans les vraies tables de transactions, on n’utilise souvent pas
-
Faux positifs
- toute règle peut faire remonter un vrai titulaire qui a simplement un comportement inhabituel mais légitime
- les cas signalés nécessitent une revue humaine
- il faut une boucle de feedback pour ajuster les seuils en fonction de ce qui est réellement frauduleux ou non
- bloquer automatiquement sur une seule règle peut faire perdre des clients
-
Données personnelles
- si les données contiennent des PII, il faut respecter les politiques d’usage des données applicables
- mieux vaut commencer sur des données anonymisées ou d’échantillon, puis utiliser les données de production après validation
-
Coût
- les fonctions de fenêtre ne sont pas bon marché sur de grandes partitions
- il faut d’abord filtrer la plage de dates, puis appliquer les fonctions de fenêtre
- lancer
LAG()d’abord sur deux ans de transactions de tout le dataset, puis ajouter leWHEREensuite, peut consommer une grosse part du budget de crédits du data warehouse
4 commentaires
C’est une méthode qui avait été empruntée par le passé dans les systèmes centraux bancaires et les systèmes de canaux.
Commentaires Hacker News
Le critère selon lequel le véritable titulaire de la carte n’achète presque jamais quelque chose à 1,00 $ dépend surtout de la manière dont le vendeur fixe ses prix, non ?
Quand quelqu’un achète quelque chose sur un site web pour tester une carte bancaire volée, il ne peut pas choisir librement le prix.
En plus, cela semble trop pensé pour des situations comme aux États-Unis, où les taxes ne sont pas incluses dans le prix ; dans d’autres régions, les prix ronds sont très courants.
Je doute aussi que les autres critères fonctionnent bien. Par exemple, si l’on signale les personnes qui ont effectué une transaction au cours des 90 derniers jours en dehors d’une tranche horaire où elles avaient d’habitude au moins deux transactions, j’ai l’impression qu’on attraperait facilement la moitié des gens.
On ne sait pas bien si c’est un article qui réduit à l’excès une expertise complexe à de simples requêtes SQL, ou si tout est inventé et spéculatif. Les phrases « six motifs SQL utilisés pour repérer la fraude transactionnelle » et « il n’y a rien ici sur lequel j’ai réellement travaillé ou que j’ai réellement vu » se contredisent.
En général, on n’achète pas de l’essence, du café ou des snacks à 2 heures du matin, mais les rares fois où cela arrive, il y a de fortes chances qu’il s’agisse d’une urgence personnelle, et dans ce cas on n’a pas forcément envie d’appeler sa banque.
Je sais bien qu’un voleur opportuniste peut aussi agir à cette heure-là, mais le coût des faux positifs existe bel et bien.
Il existe aussi des stations-service qui demandent des montants prédéfinis, comme 10, 20 ou 50 euros.
Ma carte a alors été bloquée pour suspicion de fraude, ce qui m’a bien agacé. Ce n’est pas le genre de chose qu’on veut gérer à 2 heures du matin en étant ivre.
Ça m’a peut-être protégé de moi-même, mais c’était quand même pénible.
Et puis la reconnaissance heuristique de motifs, quand on n’attend pas une précision proche de 100 %, n’est-ce pas justement le genre de domaine où l’IA est censée être bonne ?
Le critère « franchissement de frontière en moins de 10 minutes = organisation internationale » pourrait aussi s’appliquer à des gens tout à fait ordinaires qui vivent près des frontières en Europe.
Même si on exclut les transactions sans présence physique de la carte, cela semble supposer à tort que tous les emplacements des commerçants sont correctement configurés, que toutes les ventes ont lieu dans des magasins physiques, qu’il n’existe pas de commerce mobile, et que toutes les transactions sont correctement traitées en ligne.
Si on lit jusqu’au bout, on découvre un contenu creux et des conseils contradictoires. Cela ressemble presque certainement à un texte généré par LLM.
L’auteur dit que « votre équipe » ne devrait dépendre d’aucun motif en particulier, tout en affirmant que le seul motif 1 peut déjà révéler « une quantité utile de fraude ».
Il y a aussi des phrases bizarres comme « tous les analystes de votre équipe les utiliseront, c’est-à-dire les window functions, et ajouter le prochain motif de fraude cessera d’être un projet ».
Presque tous les exemples n’utilisent pas
IS NULL, et pourtant il y a une digression peu pertinente sur le fait que le filtrageIS NULLpourrait ne pas être appliqué ; le seul exemple qui l’utilise est dans un autre contexte.Globalement, c’est un article de mauvaise qualité et beaucoup trop long.
Hacker News, il faut qu’on parle de ça.
« Fixel Smith » est un personnage créé par IA, et l’article n’a presque rien à voir avec l’analyse de fraude. Ce nom est utilisé pour presque toutes les identités imaginables : musicien (1), romancier (2), analyste fraude (3), influenceur (4), etc.
Le post a obtenu plus de 220 points et plus de 70 commentaires, et presque personne n’a remarqué qu’il était assez faux ; personne non plus n’a relevé qu’il s’agissait d’un personnage généré par IA.
https://www.amazon.it/Forged-Soundtrack-Explicit-Fixel-Smith...
https://fixelsmith.com
https://analytics.fixelsmith.com/
https://www.instagram.com/fixeltales/
Je me demande si ce déferlement d’IA révèle une vérité dérangeante sur le discernement de la communauté, ou s’il s’agit simplement d’un échec des défenses existantes qu’il suffirait de corriger.
Si l’on suppose que tous les commentaires ont été rédigés de bonne foi, le faible niveau de littératie IA même ici est assez inquiétant.
Les romans n’ont presque aucun rapport avec les articles d’analyse, et les articles d’analyse semblent avoir un style de LLM, donc l’ensemble paraît suspect. C’est ironique quand on pense que le sujet initial est justement la fraude.
Honnêtement, je ne regarde généralement même pas la signature, et encore moins les autres parties du site.
On ne sait pas si le contenu est inventé ou non, mais on peut critiquer l’article pour ce qu’il dit sans spéculer sur le fait qu’il ait été écrit par un LLM ou que ce soit de la fiction. Il présente beaucoup de défauts bien plus concrets.
Nous développons le framework open source de sécurité tirreno.
L’approche décrite ici nous paraît discutable. Par exemple, le déplacement impossible est une technique légitime et largement utilisée, mais elle concerne le comportement d’utilisateurs en ligne basé sur les adresses IP.
Dans tirreno, il existe des règles distinctes pour les cas où l’IP provient manifestement d’Apple Relay ou de VPN/Tor, et ce sont des indicateurs séparés.
Je pense qu’une partie, voire la totalité, des exemples est générée par LLM. Le contexte est mélangé, et personne ne collecte réellement à grande échelle des positions GPS pour les paiements par carte.
Cela ressemble davantage à une logique fondée sur des règles encodée dans des requêtes SQL, sans données probantes derrière.
Il y a des seuils partout, mais aucune donnée ne montre que ces seuils ont du sens.
L’affirmation du type « la détection de fraude dans les données transactionnelles, c’est surtout du SQL, pas du machine learning, pas des bases de données graphe, ni quoi que ce soit que Gartner pousse cette année » ne peut se défendre que si l’on parle de l’ensemble du travail d’intégrité programmatique.
Si une approche plus simple et plus grossière résout le problème, elle peut être préférable.
Les clients fintech veulent généralement savoir si une transaction en cours est frauduleuse, et ils veulent une réponse en quelques millisecondes sur des données de grande dimension. C’est un type de charge où une base de données relationnelle a du mal à tenir ces contraintes de temps réel à l’échelle ; elle sert plutôt à d’autres usages, comme le chargement de données historiques.
C’est pour cela qu’on voit apparaître des bases de données en mémoire, des moteurs de stream processing, et aussi du machine learning.
Cela dit, certains points de l’auteur sont valables, en particulier la gestion des alertes bruyantes, qui est un problème général dépassant la seule ingénierie de performance ; j’attends donc la suite avec intérêt.
En prévention, on est toujours limité par les exigences de latence, les données disponibles et une vision incomplète du comportement utilisateur. On prend des décisions rapides avec du machine learning et des règles pour traiter la plupart des cas, mais à cause de ces contraintes, on ne peut pas bloquer toute la fraude avec précision.
La détection, elle, s’occupe de ce qui se passe ensuite. Il est courant qu’une équipe d’analystes examine des transactions approuvées pour y repérer des signes de fraude. C’est particulièrement important pour les types de fraude qui ne s’accompagnent pas de signaux externes comme des chargebacks ou des plaintes clients. L’intégrité de plateforme en est un exemple, et les systèmes anti-blanchiment en fintech doivent eux aussi aller chercher la fraude.
Les deux sont complémentaires, car les transactions détectées deviennent des labels qui servent à entraîner et évaluer les futurs modèles de prévention.
Il est dit que si une carte est utilisée à Chicago puis à Los Angeles 7 minutes plus tard, l’une des deux transactions est forcément frauduleuse ; je me demande comment cela fonctionne pour le shopping en ligne.
Si je suis assis sur mon canapé et que j’achète quelque chose sur Amazon, quelle adresse est enregistrée ?
Et il y a aussi des cas limites, par exemple un couple qui partage un compte en ligne, pendant que l’un des deux voyage et effectue un achat avec une carte enregistrée.
Les commerçants et les banques savent faire cette distinction.
Le fait que « cette méthode ne fonctionne pas tant qu’un historique n’a pas été constitué, et qu’il n’y a pas de référence pour les nouveaux comptes » est un aspect expérience client sous-estimé.
Quand ma carte est refusée parce que je suis un nouveau client ou parce que j’ai un nouveau comportement, j’ai l’impression que le logiciel fait bien son travail.
Mais si j’ai déjà un historique vérifié et qu’une transaction est quand même refusée, je suis agacé par ce qui ressemble à un algorithme naïf et paranoïaque.
Au bout du compte, les transactions frauduleuses finissent souvent annulées ou remboursées, et c’est la banque qui absorbe la perte. Une transaction refusée ne crée qu’un client mécontent, qui se plaindra puis oubliera vite. Le coût externalisé retombe donc sur le client.
Les banques ont donc intérêt à se tromper du côté de la prudence, et à refuser des transactions même s’il y a des faux positifs.
J’ai l’impression que l’idée même du machine learning est justement d’apprendre ce genre de règles à partir des données.
La bonne approche serait, à mon avis, d’utiliser un modèle de machine learning pour trouver les motifs corrélés à la fraude, puis d’évaluer lesquels ont du sens. Cela pourrait même permettre de découvrir de nouvelles hypothèses.
Un analyste humain doit pouvoir expliquer à l’équipe conformité, dans un e-mail de 5 minutes, pourquoi une transaction donnée a été refusée et ce qu’il aurait fallu faire différemment pour éviter cette décision défavorable.
Avec le machine learning, corriger un problème en fait souvent apparaître deux autres, encore mal visibles. Quand les choses évoluent dans le temps, le SQL réserve généralement moins de surprises en termes de régressions ou d’effets de bord inattendus.
Je me demandais ce que c’était, ces montants ronds… en fait, c’était
rounded.Pourquoi cela a-t-il été traduit ainsi ? T_T Je l’ai corrigé.