Segmentación RFM: recencia, frecuencia, valor

Imprimir y enviar un catálogo costaba dinero real: papel, tinta y franqueo, por cada casa. En los años 60 y 70, las grandes empresas de venta por correo en Estados Unidos —Sears, Montgomery Ward, Reader's Digest— tenían que decidir a quién se lo mandaban y a quién no, porque mandárselo a todo el mundo era ruinoso. La respuesta que encontraron era sorprendentemente simple: mirar tres cosas de cada cliente — hace cuánto compró por última vez, cuántas veces ha comprado, y cuánto ha gastado en total — y quedarse con los que puntuaban alto. Bob Stone recogió esas reglas en un manual en 1975 y Arthur Hughes las bautizó como modelo RFM (Recency, Frequency, Monetary) en 1994. Sigue siendo una de las segmentaciones de clientes más usadas en retail y e-commerce, y la vas a construir entera con SQL.

### De donde viene RFM: historia breve

La idea no nacio en Silicon Valley ni la invento un algoritmo. En los anos 60, las empresas de venta por catalogo en Estados Unidos tenian un problema muy concreto: imprimir y enviar un catalogo costaba dinero real — papel, tinta, franqueo. Enviarlo a todo el mundo era ruinoso, asi que necesitaban un metodo para elegir a quien mandarselo. La respuesta fue sorprendentemente simple: mira tres cosas de cada cliente. Primera, hace cuanto tiempo compro por última vez (Recency). Segunda, cuantas veces ha comprado en total (Frequency). Tercera, cuanto dinero ha gastado (Monetary). Un cliente que compro ayer, que compra cada mes y que gasta mucho es oro puro: le mandas el catalogo sin dudarlo. Un cliente que compro hace dos anos, una sola vez, y gasto el minimo, probablemente ni se acuerde de tu marca.

Sesenta anos despues, el principio sigue siendo el mismo. Solo ha cambiado el canal: donde antes era un catalogo de papel, hoy es un email, un push, un descuento personalizado o un mensaje de WhatsApp. Pero la logica es identica: no trates igual a quien compra cada semana que a quien desaparecio hace seis meses. RFM es el marco de segmentación de clientes más usado en retail, e-commerce y suscripciones porque funciona con datos que CUALQUIER negocio ya tiene — una tabla de compras con fecha, cliente e importe. No necesitas un modelo de machine learning ni un equipo de ciencia de datos. Necesitas SQL.

### Las tres dimensiones: R, F y M

Vamos a desglosar cada dimension con la analogia de un bar de barrio. El dueno no tiene un CRM, pero sabe perfectamente quien es un buen cliente. El tipo que viene cada viernes, pide dos rondas y trae a sus amigos: recencia alta (estuvo el viernes pasado), frecuencia alta (viene cada semana) y valor monetario alto (gasta generosamente). La senora que vino una vez en diciembre, tomo un cafe y no ha vuelto: recencia baja, frecuencia baja, valor bajo. El dueno del bar trata distinto a cada uno — y tu vas a hacer lo mismo con datos.

1. 01.Recency (R): cuántos días han pasado desde la última compra del cliente hasta hoy. Menos días = mejor. Un cliente que compro ayer probablemente sigue enganchado a tu marca; uno que compro hace 8 meses puede haberte olvidado.
2. 02.Frequency (F): cuantas compras ha hecho el cliente en el periodo de análisis. Mas compras = mejor. Distingue al comprador habitual del que solo aparecio una vez por una oferta de Black Friday.
3. 03.Monetary (M): cuanto dinero ha gastado en total. Mas gasto = mejor. No todos los compradores frecuentes gastan igual: alguien que compra cada semana pero siempre el producto más barato no vale lo mismo que quien compra menos pero elige siempre lo premium.

### El dataset de trabajo

Para esta leccion vamos a trabajar con una tabla de compras sencilla. En un caso real tendrias millones de filas; aqui usamos 20 compras de 8 clientes para que puedas verificar cada calculo a mano. La estructura es la que encontraras en cualquier e-commerce: un identificador de cliente, la fecha de la compra y el importe.

1-- Dataset de trabajo: tabla de compras
2CREATE OR REPLACE TABLE compras AS
3SELECT * FROM (VALUES
4  ('C001', '2024-01-15'::DATE, 120.00),
5  ('C001', '2024-03-22'::DATE,  85.50),
6  ('C001', '2024-06-10'::DATE, 200.00),
7  ('C001', '2024-09-05'::DATE,  95.00),
8  ('C002', '2024-02-01'::DATE,  45.00),
9  ('C002', '2024-02-20'::DATE,  60.00),
10  ('C003', '2024-01-10'::DATE, 300.00),
11  ('C003', '2024-04-18'::DATE, 275.00),
12  ('C003', '2024-07-25'::DATE, 310.00),
13  ('C003', '2024-09-30'::DATE, 290.00),
14  ('C003', '2024-10-15'::DATE, 150.00),
15  ('C004', '2024-08-01'::DATE,  30.00),
16  ('C005', '2024-03-05'::DATE,  55.00),
17  ('C005', '2024-05-12'::DATE,  70.00),
18  ('C005', '2024-10-28'::DATE,  65.00),
19  ('C006', '2024-10-01'::DATE, 500.00),
20  ('C007', '2024-09-15'::DATE,  40.00),
21  ('C007', '2024-10-20'::DATE,  42.00),
22  ('C007', '2024-11-01'::DATE,  38.00),
23  ('C008', '2024-04-10'::DATE,  25.00)
24) AS t(customer_id, purchase_date, amount);

### Paso 1: Calcular las tres métricas en crudo

El primer paso es puramente mecanico: para cada cliente, calcular sus tres números. Recency es la diferencia en días entre una fecha de referencia y su última compra. Frequency es el conteo de compras. Monetary es la suma de importes. Un GROUP BY, tres agregaciones, y ya tienes el esqueleto.

1-- Paso 1: Métricas RFM en crudo
2-- Usamos una fecha fija como "hoy" para que el resultado sea reproducible
3SELECT
4    customer_id,
5    DATEDIFF('day', MAX(purchase_date), DATE '2024-11-15') AS recency_dias,
6    COUNT(*)                                               AS frequency,
7    ROUND(SUM(amount), 2)                                  AS monetary
8FROM compras
9GROUP BY customer_id
10ORDER BY customer_id;

### Paso 2: Asignar scores con NTILE

Los números en crudo son utiles para ti, pero no para el equipo de marketing. Que 71 días de recencia sea bueno o malo depende de tu negocio: en un supermercado es una barbaridad, en una tienda de muebles es normal. Lo que necesitamos es una escala relativa: dividir a los clientes en grupos iguales y asignarles una puntuacion del 1 al 5. Eso son los quintiles (quintile en ingles, que son los percentiles cortados en cinco trozos), y en SQL se calculan con NTILE(5).

NTILE(5) divide las filas ordenadas en 5 buckets de tamaño lo más igual posible. Si tienes 100 clientes, cada grupo tiene 20. Si tienes 8 (nuestro caso), se reparten como puede: grupos de 2 o de 1. La funcion asigna el número del bucket — del 1 al 5. La convencion más comun es que 5 sea el mejor score.

1-- Paso 2: Asignar scores 1-5 con NTILE (5 = mejor)
2WITH metricas AS (
3    SELECT
4        customer_id,
5        DATEDIFF('day', MAX(purchase_date), DATE '2024-11-15') AS recency_dias,
6        COUNT(*)        AS frequency,
7        SUM(amount)     AS monetary
8    FROM compras
9    GROUP BY customer_id
10)
11SELECT
12    customer_id,
13    recency_dias,
14    frequency,
15    ROUND(monetary, 2) AS monetary,
16    -- Recency: muchos días primero (peor), NTILE 5 = pocos días (mejor)
17    NTILE(5) OVER (ORDER BY recency_dias DESC) AS r_score,
18    -- Frequency: pocas compras primero (peor), NTILE 5 = muchas (mejor)
19    NTILE(5) OVER (ORDER BY frequency ASC)     AS f_score,
20    -- Monetary: poco gasto primero (peor), NTILE 5 = mucho (mejor)
21    NTILE(5) OVER (ORDER BY monetary ASC)      AS m_score
22FROM metricas
23ORDER BY customer_id;

### Paso 3: Interpretar los segmentos

Ya tienes tres scores por cliente. Ahora la pregunta es: ¿qué significan las combinaciones? Un cliente con R=5, F=5, M=5 es obvio: tu mejor cliente, un campeón. Pero ¿qué pasa con R=1, F=5, M=4? Ese cliente SOLÍA comprar mucho y gastar bastante, pero lleva meses sin aparecer. Está en riesgo de perderse para siempre. ¿Y R=5, F=1, M=5? Compró hace muy poco, una sola vez, pero gastó mucho: es un nuevo prometedor al que vale la pena cuidar.

La clasificacion en segmentos con nombre es una decision de negocio, no un calculo. No hay una formula universal. Cada empresa adapta los umbrales y los nombres a su realidad. Pero hay una clasificacion estándar que funciona como punto de partida y que encontraras en la mayoria de implementaciones:

La tabla anterior no es una formula: es un mapa de decisiones. La responsable de CRM la mira y dice: «Vale, los de "en riesgo" reciben la campaña de reactivación con un 20% de descuento. Los "campeones" reciben acceso anticipado a la nueva coleccion. Los "nuevos prometedores" reciben un email de bienvenida premium con un regalo en su segunda compra.» Eso es lo que hace util a RFM: cada segmento se traduce directamente en una accion distinta.

### Paso 4: La query completa con CASE WHEN

Ahora juntamos todo en una sola query que va desde la tabla de compras hasta los segmentos con nombre. La clasificacion usa CASE WHEN con condiciones que priorizan los segmentos más especificos primero (un campeon cumple tambien la condición de fiel, asi que va antes en el CASE).

1-- Query completa: de compras a segmentos RFM
2WITH metricas AS (
3    SELECT
4        customer_id,
5        DATEDIFF('day', MAX(purchase_date), DATE '2024-11-15') AS recency_dias,
6        COUNT(*)        AS frequency,
7        SUM(amount)     AS monetary
8    FROM compras
9    GROUP BY customer_id
10),
11scores AS (
12    SELECT
13        customer_id,
14        recency_dias,
15        frequency,
16        ROUND(monetary, 2) AS monetary,
17        NTILE(5) OVER (ORDER BY recency_dias DESC) AS r_score,
18        NTILE(5) OVER (ORDER BY frequency ASC)     AS f_score,
19        NTILE(5) OVER (ORDER BY monetary ASC)      AS m_score
20    FROM metricas
21)
22SELECT
23    customer_id,
24    r_score,
25    f_score,
26    m_score,
27    r_score || '-' || f_score || '-' || m_score AS rfm_code,
28    CASE
29        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Campeon'
30        WHEN r_score >= 3 AND f_score >= 4                  THEN 'Fiel'
31        WHEN r_score >= 4 AND f_score <= 2 AND m_score >= 4 THEN 'Nuevo prometedor'
32        WHEN r_score <= 2 AND f_score >= 4                  THEN 'En riesgo'
33        WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Hibernando'
34        WHEN r_score >= 4 AND f_score <= 2                  THEN 'Nuevo reciente'
35        WHEN r_score <= 2 AND f_score >= 3                  THEN 'A punto de irse'
36        ELSE 'Necesita atencion'
37    END AS segmento
38FROM scores
39ORDER BY r_score DESC, f_score DESC, m_score DESC;

### NTILE vs rangos manuales: cuando usar cada uno

NTILE es rapido y justo: reparte los clientes en grupos del mismo tamaño, sin que tengas que decidir umbrales. Pero tiene una limitación importante: si tu distribución esta muy sesgada (un 5% de clientes concentra el 60% del gasto), NTILE mete en el mismo bucket a gente muy distinta. En ese caso, rangos manuales con CASE WHEN te dan más control:

1-- Alternativa: scores con rangos manuales (umbrales de negocio)
2WITH metricas AS (
3    SELECT
4        customer_id,
5        DATEDIFF('day', MAX(purchase_date), DATE '2024-11-15') AS recency_dias,
6        COUNT(*)        AS frequency,
7        SUM(amount)     AS monetary
8    FROM compras
9    GROUP BY customer_id
10)
11SELECT
12    customer_id,
13    recency_dias,
14    frequency,
15    ROUND(monetary, 2) AS monetary,
16    CASE
17        WHEN recency_dias <= 30  THEN 5
18        WHEN recency_dias <= 60  THEN 4
19        WHEN recency_dias <= 90  THEN 3
20        WHEN recency_dias <= 180 THEN 2
21        ELSE 1
22    END AS r_score,
23    CASE
24        WHEN frequency >= 5 THEN 5
25        WHEN frequency >= 4 THEN 4
26        WHEN frequency >= 3 THEN 3
27        WHEN frequency >= 2 THEN 2
28        ELSE 1
29    END AS f_score,
30    CASE
31        WHEN monetary >= 500 THEN 5
32        WHEN monetary >= 200 THEN 4
33        WHEN monetary >= 100 THEN 3
34        WHEN monetary >= 50  THEN 2
35        ELSE 1
36    END AS m_score
37FROM metricas
38ORDER BY customer_id;

### Presentar RFM al equipo de CRM

El análisis no termina cuando la query devuelve filas. Termina cuando alguien toma una decision distinta gracias a el. Y para que eso pase, tienes que hablar el idioma de CRM, no el de SQL. Aqui va lo que funciona en la reunión:

1. 01.Empieza por el resumen: «Tenemos X clientes repartidos en 6 segmentos. Los tres que necesitan accion inmediata son...»
2. 02.Pon números de negocio, no scores: «El segmento En Riesgo son 2.400 clientes que antes compraban cada mes y llevan una media de 95 días sin aparecer. Juntos representan 180.000 euros de facturación anual que estamos perdiendo.»
3. 03.Propón la accion concreta: «Si les mandamos un email con un 15% de descuento y recuperamos un 20% de ellos, son 36.000 euros al trimestre.»
4. 04.Deja claro que la segmentación se recalcula: «Esto no es una foto fija. Lo ejecuto cada semana y os paso la lista actualizada. Un cliente que compre mañana ya no esta "en riesgo", pasa automáticamente a otro segmento.»

### Resumen del flujo completo

### Errores comunes al implementar RFM

• ▹No filtrar el periodo: si tomas todas las compras desde el inicio de los tiempos, un cliente que compro 50 veces hace 3 anos pero no ha vuelto sale como "frecuente". Usa una ventana razonable (12-24 meses tipicamente).
• ▹Confundir la dirección de Recency: que NTILE asigne 5 al mejor, no al que lleva más días. Comprueba siempre con un caso conocido.
• ▹NTILE con pocos clientes: con 8 clientes y NTILE(5), los buckets tienen 1-2 personas y los scores no son significativos. En producción necesitas al menos 100-200 clientes para que los quintiles tengan sentido estadístico.
• ▹No excluir pedidos cancelados o devueltos: un pedido devuelto no deberia contar como compra ni sumar al monetary. Filtra por estado completado.
• ▹Usar solo el score concatenado (555, 443...): el codigo RFM es util para depurar, pero 125 combinaciones posibles son demasiadas para que marketing actue. Agrupa en 5-8 segmentos con nombre y accion clara.