Patrones SQL usados para detectar fraude en transacciones

 (analytics.fixelsmith.com)
1 puntos por GN⁺ 2 시간 전 | 1 comentarios | Compartir por WhatsApp
  • La detección de fraude muchas veces empieza, antes que con machine learning, por definir bien las tablas y joins y encontrar con SQL patrones anómalos de velocidad, ubicación, monto, comercio y franja horaria
  • Velocity busca períodos cortos en los que se concentran transacciones del mismo titular de tarjeta, y requiere ajustar la ventana de tiempo, los umbrales y una whitelist de falsos positivos
  • Impossible travel usa LAG() y cálculo de distancia para detectar como una fuerte señal de tarjeta clonada casos físicamente imposibles, como un pago en Chicago y otro 7 minutos después en Los Ángeles
  • Las anomalías de monto buscan importes como $1.00, $99.99 o $499.99 que sugieren pruebas de tarjeta o evasión de reglas, aunque no encajan bien en transacciones de beneficios
  • Si se combinan picos por comercio, operaciones fuera del horario habitual y columnas derivadas con funciones de ventana, se pueden puntuar transacciones con múltiples señales y reducir el ciclo de iteración de semanas a horas

Patrones SQL para encontrar señales de fraude en datos de transacciones

  • La detección de fraude muchas veces comienza, antes que con machine learning o bases de datos de grafos, con las tablas y joins correctos y con SQL para encontrar transacciones extrañas
  • Se puede aplicar a datos donde se mueve dinero y quedan logs, como tarjetas de crédito, reclamos médicos, comercio electrónico, POS y programas gubernamentales de beneficios
  • En un dataset nuevo, normalmente se van acumulando patrones en este orden: velocidad, viaje imposible, anomalías de monto, concentración por comercio, horarios anómalos y señales basadas en funciones de ventana

1. Velocity: demasiadas transacciones en poco tiempo

  • Cuando intentan agotar rápido una tarjeta o cuenta robada, aparece un patrón de concentración de transacciones en poco tiempo para el mismo titular de tarjeta
  • La consulta base agrupa las transacciones de los últimos 30 días por hora y busca períodos en los que la cantidad de transacciones por cardholder_id supera un umbral
  • Los principales parámetros de ajuste son el tamaño de la ventana de tiempo y el umbral de cantidad de transacciones
    • Se pueden ejecutar en paralelo versiones de 1 minuto, 5 minutos y 1 hora para compararlas
    • Las redes de prueba de tarjetas pueden concentrar transacciones en segundos, mientras que las redes de fraude de beneficios pueden moverse a lo largo de medio día, así que la escala cambia
  • Los usuarios normales también pueden superar el umbral
    • Operadores que administran máquinas expendedoras
    • Personas que cargan muchas tarjetas prepago
    • Después de la primera exploración, hace falta una whitelist de falsos positivos para estos casos
  • El enfoque de ventana deslizante calcula el número de transacciones en los últimos 5 minutos con COUNT(*) OVER (...) RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
  • QUALIFY funciona en Snowflake, BigQuery, Databricks y Teradata
    • En Postgres hay que envolver toda la consulta en un CTE y filtrar afuera

2. Impossible travel: desplazamientos físicamente imposibles

  • Si una tarjeta se usa en Chicago y 7 minutos después en Los Ángeles, es muy probable que una de las dos transacciones sea falsa
  • Este patrón es una señal fuerte para detectar tarjetas clonadas, porque casi no hay razones legítimas para que una misma tarjeta esté en dos lugares lejanos en minutos
  • La consulta usa LAG() para traer la hora y la ubicación de la transacción anterior, y calcula la distancia y el tiempo entre la ubicación actual y la previa
  • haversine es una función para calcular la distancia de círculo máximo (great-circle distance)
    • La mayoría de los data warehouses la ofrecen
    • Si no, es una función que se puede implementar sin mucha dificultad
  • El umbral de ejemplo es 600mph
    • Como la velocidad de crucero de un jet comercial es de unas 575mph, eso implica una velocidad imposible incluso viajando en avión
    • Si se baja a 100mph, también se pueden detectar desplazamientos terrestres rápidos, pero empiezan a aparecer transacciones legítimas de viajeros o de padres que llevan a sus hijos
  • En la misma familia hay otras señales útiles
    • Si hay transacciones en dos ciudades lejanas del mismo estado en 5 minutos, puede sugerir una red local de clonación
    • Si hay transacciones en varios códigos postales en una hora, puede sugerir una red de skimmers moviéndose dentro de una zona
    • Si hay transacciones que cruzan fronteras en 10 minutos, puede ser una señal de una red internacional

3. Amount anomalies: transacciones anómalas en ciertos montos

  • En el fraude hay patrones de monto que aparecen seguido, pero son raros en el uso normal
  • Las condiciones de ejemplo buscan estos rangos
    • $1.00, $5.00, $10.00
    • Desde $99.50 hasta menos de $100.00
    • Desde $499.50 hasta menos de $500.00
  • Los montos pequeños en dólares enteros suelen ser una señal de prueba de tarjeta
    • Es el flujo donde verifican si un número obtenido de un volcado de tarjetas realmente funciona antes de revenderlo
    • Es poco común que el titular real compre algo de exactamente $1.00
    • Es más probable que un café cueste $4.73 y una carga de gasolina $52.81, no un monto redondo exacto
  • Los montos justo por debajo de un umbral tienen otro significado
    • $99.99 puede ser una forma de evitar reglas que exigen verificación de identidad desde los $100 en muchos lugares
    • $499.99 puede ser una forma de evitar un límite diario de ATM de $500
    • Es una señal de que quien transa conoce la regla y se mantiene justo por debajo
  • En transacciones de beneficios, los patrones de montos redondos no ayudan tanto
    • Los beneficios no suelen probarse como se hace con las tarjetas
    • Normalmente la señal más importante son los beneficiarios duplicados

4. Suspicious merchants: concentración anómala a nivel de comercio

  • Si un lector de tarjetas en un surtidor de gasolina queda infectado con un skimmer, eso puede terminar no en un solo caso sino en decenas de fraudes
  • Todas las tarjetas que usaron ese lector durante semanas pueden terminar en la base de datos de alguien
  • Desde la perspectiva del comercio, esto aparece como un aumento inusual, en un período corto, de tarjetas no relacionadas entre sí, junto con montos de transacción más altos de lo normal
  • Un criterio simple de ejemplo agrupa por comercio y por hora en los últimos 7 días para calcular
    • Cantidad de tarjetas únicas
    • Cantidad total de transacciones
    • Monto total transado
    • Luego busca franjas horarias en las que haya más de 20 tarjetas únicas y un total superior a $5000
  • Los umbrales estáticos tienen el problema de ajustar por tamaño
    • Costco puede superar ese criterio en 90 segundos
    • Una librería de usados casi nunca lo hará
  • Una mejor forma es comparar cada comercio contra su propia línea base histórica
    • Agrupar por hora las transacciones de los últimos 60 días
    • Calcular para cada comercio el promedio histórico de tarjetas únicas tomando como referencia los 168 buckets horarios previos
    • Buscar períodos en los que la cantidad actual de tarjetas únicas supere 3 veces ese promedio histórico
  • Los 168 buckets horarios representan los intervalos por hora de los últimos 7 días
    • Porque la estacionalidad diaria y semanal importa
    • Incluso la misma cafetería tiene una línea base distinta un martes a las 2 p. m. que un sábado a las 9 a. m.
  • Como punto de partida se puede usar 3 veces lo normal
    • Es lo bastante laxo para que no lluevan alertas
    • Pero lo bastante estricto como para detectar franjas realmente anómalas

5. Off-hours: transacciones fuera del horario habitual de una persona

  • La mayoría de las personas tiene hábitos de gasto
  • Si alguien que trabaja de 9 a 5 de repente empieza a cargar gasolina a las 3 de la mañana, puede ser que otra persona esté usando la tarjeta o que esté de viaje
  • Si está de viaje, eso se puede confirmar con otras señales
  • La consulta calcula primero la cantidad de transacciones por titular y por franja horaria durante los últimos 90 días, y solo reconoce como horario habitual las franjas en las que hubo al menos 2 transacciones
  • Después detecta transacciones nuevas cuya hora quede fuera del rango entre earliest_hour y latest_hour de ese titular
  • La condición de “2 o más transacciones en esa franja” dentro de la consulta interna es importante
    • Evita que una sola carga nocturna casual de hace 3 meses quede incluida dentro del horario habitual
    • Ajusta la referencia no a “algo que pasó una vez”, sino al hábito real
  • La desventaja es que se necesitan datos históricos
    • Las cuentas nuevas no tienen línea base
    • Para ellas se pueden usar patrones horarios del conjunto total de usuarios o simplemente omitir este patrón hasta acumular algunos meses

6. Combinar señales con funciones de ventana

  • Los patrones con funciones de ventana no son tanto un tipo de fraude separado como una forma de preparar los cinco patrones anteriores para convertirlos en señales combinables
  • Se pueden crear por transacción columnas derivadas como estas
    • Tiempo transcurrido desde la transacción anterior: timestamp - LAG(timestamp)
    • Si cambió el comercio: comparar el merchant_id anterior con el merchant_id actual
    • Monto acumulado en las últimas 24 horas: SUM(amount) OVER (...)
    • Qué número de transacción del día es: ROW_NUMBER()
  • Si estas columnas se materializan, las reglas antifraude se reducen a simples expresiones de filtro
  • Una red de prueba de tarjetas se puede detectar con condiciones como estas
    • Quinta transacción del día o posterior
    • Menos de 60 segundos desde la transacción previa
    • El comercio es distinto al de la transacción anterior
  • Si una nueva hipótesis de fraude se puede expresar como un filtro SQL en lugar de un ticket de ingeniería, el ciclo de iteración baja de semanas a horas
  • Como resultado, se puede detectar más fraude y más rápido

Cómo usar los patrones en conjunto

  • Ningún patrón por sí solo es suficiente
  • Cada patrón tiene límites claros
    • Velocity tiene falsos positivos, como operadores de máquinas expendedoras
    • Los desplazamientos geográficamente imposibles no detectan fraudes que ocurren dentro de una misma gran área metropolitana
    • Las anomalías de monto no funcionan bien fuera del contexto de prueba de tarjetas
    • Las reglas de horarios anómalos requieren historial
  • En la práctica, lo que funciona es correr todos los patrones y puntuar cada transacción en varias señales
  • Una transacción que activa tres o cuatro señales casi siempre es fraude
  • Una que activa solo una señal puede ser un uso inusual pero legítimo de un titular que está viajando
  • Si estás empezando con detección de fraude, conviene comenzar por Velocity
    • Revela una cantidad útil de fraude
    • Captura relativamente poca actividad normal
    • Y además cuesta poco ejecutarlo
  • Si ya tienes cubiertos del 1 al 5, la siguiente inversión debería ser en columnas base con funciones de ventana
    • Una vez creadas, las puede usar todo el equipo de analistas
    • Agregar el siguiente patrón de fraude deja de ser un proyecto aparte

Puntos de atención

  • Manejo de NULL

    • En tablas reales de transacciones, muchas veces no se usa NULL como en los manuales introductorios de SQL
    • Muchos sistemas legacy usan valores centinela como 9999-12-31 para “sin fecha de fin” y 0001-01-01 para “sin fecha de inicio”
    • Si filtras con IS NULL, puedes omitir estas filas sin darte cuenta
    • Hay que revisar las convenciones de cada tabla antes de escribir la cláusula WHERE

  • Falsos positivos

    • Toda regla puede atrapar a titulares reales con comportamientos inusuales pero legítimos
    • Los casos marcados necesitan revisión humana
    • Hace falta un ciclo de retroalimentación para ajustar los umbrales según qué era fraude real y qué no
    • Si bloqueas automáticamente con una sola regla, puedes perder clientes

  • Privacidad

    • Si los datos tienen PII, hay que respetar las políticas de uso de datos que correspondan
    • Primero se debe trabajar con datos desidentificados o de muestra, y usar datos de producción solo con aprobación

  • Costo

    • Las funciones de ventana sobre particiones grandes no son baratas
    • Primero hay que filtrar el rango de fechas y luego aplicar las funciones de ventana
    • Si ejecutas LAG() primero sobre 2 años de transacciones de todo el dataset y recién después agregas el WHERE, puedes consumir una gran parte del presupuesto de créditos del warehouse

Lecturas relacionadas

1 comentarios

 
GN⁺ 2 시간 전
Comentarios de Hacker News

  • Me parece que el criterio de que el verdadero titular de la tarjeta casi nunca compra algo de $1.00 depende de cómo el comercio fija el precio
    Al comprar algo en un sitio web para probar una tarjeta de crédito robada, el comprador no puede elegir libremente el precio
    Además, parece estar pensado demasiado desde contextos como Estados Unidos, donde los impuestos no están incluidos en el precio, y en otras regiones los precios redondos son muy comunes
    También dudo que los otros criterios del artículo funcionen bien. Por ejemplo, si marcas a alguien por haber hecho una transacción fuera de su horario habitual dentro de los últimos 90 días, ¿no terminarías atrapando como a la mitad de la gente?
    No queda claro si es un artículo que simplifica en exceso conocimiento especializado complejo en consultas SQL demasiado simples, o si todo son conjeturas e invención. La frase “seis patrones SQL usados para detectar fraude en transacciones” entra en conflicto con “aquí no hay nada en lo que yo realmente haya trabajado o visto”

    • “Transacción fuera del horario habitual” parece un criterio bastante básico
      Normalmente uno no compra gasolina, café y snacks a las 2 de la mañana, pero cuando eso pasa muy de vez en cuando, es muy probable que se trate de una emergencia personal y no quieras estar llamando al banco en medio de eso
      Sé que un ladrón oportunista también puede operar a esa hora, pero claramente también existe el costo de los falsos positivos
    • Es aún peor. En mi experiencia, el café muchas veces cuesta un monto redondo, y hay gente que al cargar gasolina pone a propósito una cantidad exacta
      Además, hay gasolineras que piden montos predefinidos como 10, 20 o 50 euros
    • Una noche en un bar me dio hambre y quise comprar una bolsa de papas fritas, pero el consumo mínimo con tarjeta era de £5, así que pedí que me cobraran £5 exactos
      Entonces bloquearon la tarjeta por sospecha de fraude y fue bastante molesto. No era algo que quisiera resolver borracho a las 2 de la mañana
      Tal vez me protegieron de mí mismo, pero igual fue inconveniente
    • No sé si todavía se use, pero antes en hoteles o rentadoras de autos a veces hacían una transacción de $1.00 para verificar que la tarjeta de crédito fuera válida antes de reservar una habitación o rentar un vehículo
    • Este método se puede evadir fácilmente agregando un poco de ruido a la transacción de prueba, y también se podría mejorar fácilmente con un análisis estadístico decente
      Y este tipo de reconocimiento heurístico de patrones, donde no esperas una precisión cercana al 100%, ¿no es justamente el tipo de área en la que la IA debería destacar?

  • El criterio de “cruzar una frontera en 10 minutos significa crimen organizado internacional” también podría aplicar a personas completamente normales que viven en zonas fronterizas de Europa
    Incluso si se excluyen las transacciones sin tarjeta presente, parece asumir erróneamente que todas las ubicaciones de los comercios están configuradas con precisión, que todas las ventas ocurren en tiendas físicas, que no existe la venta móvil y que todas las transacciones se procesan en línea

    • Hace unas semanas, cuando crucé de Estados Unidos a Canadá, probablemente también fueron como 10 minutos

  • Si lo lees hasta el final, se nota que el contenido está vacío y que los consejos se contradicen entre sí. Casi seguro parece un texto generado por un LLM
    Dice que “tu equipo” no debería depender de ningún patrón individual, pero también que el patrón 1 por sí solo puede revelar “una cantidad útil de fraude”
    También hay frases raras como “todos los analistas de tu equipo las usarán, es decir, las funciones de ventana, y agregar el siguiente patrón de fraude deja de ser un proyecto”
    Además, casi todos los ejemplos no usan IS NULL, pero aparece una discusión poco relacionada sobre que el filtrado IS NULL podría no aplicarse, y el único ejemplo que sí lo usa está en otro contexto
    En general es un texto de baja calidad y demasiado largo

  • Hacker News, esto hay que señalarlo
    “Fixel Smith” es una persona creada por IA, y el artículo casi no tiene relación con análisis de fraude. Ese nombre se usa para casi cualquier identidad imaginable: músico (1), novelista (2), analista de fraude (3), influencer (4), etc.
    Tiene más de 220 puntos y más de 70 comentarios, pero casi nadie notó que este artículo es bastante falso y nadie vio que se trata de una persona generada por IA

    1. https://www.amazon.it/Forged-Soundtrack-Explicit-Fixel-Smith...

    2. https://fixelsmith.com

    3. https://analytics.fixelsmith.com/

    4. https://www.instagram.com/fixeltales/

    • Parece que últimamente Hacker News ha agarrado la frustrante costumbre de subir este tipo de publicaciones de IA de baja calidad
      Me hace preguntarme si esta inundación de IA revela una verdad incómoda sobre el criterio de la comunidad, o si solo muestra una falla de los mecanismos de defensa existentes que bastaría con cambiar
    • Revisé el artículo desde el celular y solo vi un poco los comentarios. No siempre es fácil decidir si un texto fue generado por IA o editado, pero aquí bastaba ver las citas para que fuera obvio desde el primer momento
      Si asumimos que todos los comentarios fueron escritos de buena fe, entonces es bastante preocupante que incluso aquí haya tan poca alfabetización en IA
    • Incluso a simple vista parece una persona extremadamente prolífica o un bot
      La novela no tiene casi nada que ver con los textos analíticos, y los textos analíticos parecen tener estilo de LLM, así que todo resulta sospechoso. Dado que el tema original es fraude, es irónico
    • Me sorprendería más si la mayoría de la gente investigara por costumbre al autor de lo que lee
      Sinceramente, yo normalmente ni veo la firma del artículo, y mucho menos otras partes del sitio
    • Esto claramente es un artículo que existe de verdad. Por supuesto, parece escrito por un LLM, pero si la peor crítica que se le puede hacer es que parece de LLM, entonces quizá en realidad no haya una crítica sustancial
      No está claro si el contenido es inventado o no, pero se puede criticar el contenido del artículo sin especular si lo escribió un LLM o si es ficción. Tiene defectos mucho más concretos

  • Estamos desarrollando el framework de seguridad open source tirreno
    Tengo dudas sobre el enfoque que se describe aquí. Por ejemplo, el viaje imposible es una técnica legítima y muy usada, pero está relacionada con el comportamiento de usuarios en línea basado en direcciones IP
    En tirreno hay reglas separadas para casos en que está claro que la IP viene de Apple Relay o de VPN/Tor, y esas son banderas aparte
    Creo que algunos o todos los ejemplos fueron generados por un LLM. El contexto está mezclado y, además, en pagos con tarjeta no existe en la práctica ningún lugar que recolecte ubicaciones GPS a gran escala

    1. https://github.com/tirrenotechnologies/tirreno

  • Esto se parece más a lógica basada en reglas codificada en consultas SQL “sin datos que la respalden”
    Hay montones de umbrales, pero no hay datos que demuestren que esos umbrales tengan sentido

  • Afirmaciones tajantes como “la detección de fraude en datos transaccionales es mayormente SQL, no machine learning ni bases de datos de grafos ni lo que sea que Gartner esté promoviendo este año” solo se justifican si estás hablando del trabajo completo de integridad programática
    Si resuelven el problema del dominio, un enfoque más simple y más tosco puede ser mejor
    Los clientes fintech normalmente quieren saber si la transacción que está ocurriendo ahora es fraudulenta, y quieren una respuesta en milisegundos sobre datos de alta dimensionalidad. Este es un tipo de trabajo a una escala donde una base de datos relacional tiene dificultades para cumplir esas restricciones en tiempo real, y en cambio se usa para otras cosas, como cargar datos históricos
    Por eso aparecen bases de datos en memoria, motores de procesamiento de streams e incluso machine learning
    Aun así, algunos puntos del autor son válidos, y en particular el problema de lidiar con alertas ruidosas va más allá de la ingeniería de rendimiento, así que espero el siguiente artículo

    • En mi experiencia, lo que se describe se llama más precisamente prevención de fraude que detección de fraude. En configuraciones maduras, ambas coexisten y se complementan
      En prevención siempre estás limitado por los requisitos de latencia, los datos disponibles y una imagen incompleta del comportamiento del usuario. Tomas decisiones rápidas con machine learning y reglas para cubrir la mayoría de los casos, pero por esas limitaciones no puedes bloquear con precisión todo el fraude
      La detección trata las consecuencias posteriores. Es común que un equipo de analistas revise transacciones aprobadas para buscar señales de fraude. Esto es especialmente importante en tipos de fraude donde no hay señales externas como contracargos o quejas de clientes. La integridad de plataformas es un ejemplo, y los sistemas antilavado en fintech también tienen que salir a buscar fraude
      Se complementan porque las transacciones detectadas se convierten en etiquetas para entrenar y evaluar el siguiente modelo de prevención

  • Dice que si la tarjeta se usa en Chicago y 7 minutos después se vuelve a usar en Los Ángeles, una de las dos debe ser falsa, pero me pregunto cómo funciona eso con las compras en línea
    Si estoy sentado en el sofá y compro algo en Amazon, ¿qué dirección se registra?
    También parece posible un caso límite donde una pareja comparte una cuenta en línea y una de las personas compra mientras viaja usando la tarjeta guardada

    • Deslizar, insertar o acercar la tarjeta es una transacción con tarjeta presente. Ingresar el número de la tarjeta, como en compras en línea, es una transacción sin tarjeta presente
      Los comercios y los bancos pueden distinguir esa diferencia
    • Se puede distinguir por los metadatos de la transacción. Trabajé en una empresa de tarjetas de crédito
    • Entiendo que el sistema distingue entre tarjeta presente y tarjeta no presente

  • “Este enfoque no funciona hasta que se acumula historial, y las cuentas nuevas no tienen línea base” es un factor de experiencia del cliente poco valorado
    Si eres un cliente nuevo o muestras un patrón nuevo y te rechazan la tarjeta, sientes que el software está haciendo bien su trabajo
    Pero si tengo un historial previo autenticado y aun así me rechazan una transacción, me molesta por culpa de un algoritmo ingenuo y paranoico

    • El incentivo del banco es reducir el fraude
      Las transacciones fraudulentas al final terminan en anulaciones o reembolsos, y el banco absorbe esa pérdida. Una transacción rechazada solo crea un cliente enojado, y el cliente se queja y luego se le pasa. Así que el peso de ese costo externalizado recae en el cliente
      Por lo tanto, los bancos tienen incentivos para equivocarse del lado de ser más cautelosos y rechazar transacciones aunque haya falsos positivos

  • Me parece que la esencia del machine learning es precisamente aprender este tipo de reglas a partir de los datos
    Creo que el enfoque correcto sería usar un modelo de machine learning para encontrar patrones asociados al fraude y luego evaluar si alguno tiene sentido. Así incluso podrías descubrir nuevas hipótesis

    • Cualquier cosa que no sea explicable y que no pueda mejorarse iterativamente de forma determinista es demasiado riesgosa para el trabajo de rechazar transacciones financieras
      Un analista humano tiene que poder explicarle al equipo de cumplimiento en un correo de 5 minutos por qué se rechazó una transacción concreta y qué se habría tenido que hacer distinto para evitar esa decisión adversa
      Muchas veces, cuando arreglas un problema con machine learning, aparecen dos problemas nuevos que todavía no son evidentes. A medida que cambia con el tiempo, SQL en general da menos sorpresas en términos de regresiones o efectos secundarios inesperados