Si tienes una tabla de órdenes, ya tienes todo lo necesario para empezar a hacer análisis de clientes.

El modelo RFM (Recency, Frequency, Monetary) es una técnica sencilla que te permite clasificar usuarios según su comportamiento de compra. Es ideal para comenzar en temas de Business Intelligence sin necesidad de herramientas complejas.

¿Qué es el análisis RFM?

RFM significa:

• Recency (R): ¿Qué tan reciente fue la última compra?
• Frequency (F): ¿Con qué frecuencia compra?
• Monetary (M): ¿Cuánto dinero gasta?

La magia está en combinar estos tres factores para segmentar clientes y tomar decisiones más inteligentes.

¿Para qué sirve en un ecommerce?

Con RFM puedes responder cosas como:

• ¿Quiénes son mis mejores clientes?
• ¿Quiénes están a punto de abandonarme?
• ¿A quién le mando descuentos vs a quién le vendo premium?

Esto conecta directo con CRM (campañas segmentadas), BI (dashboards accionables) y estrategias de retención y revenue growth.

Suposiciones básicas de tu base de datos

Un ecommerce típico tiene una tabla orders con esta estructura:

• id — identificador único de la orden
• user_id — cliente que realizó la compra
• total_amount — monto total de la orden
• created_at — fecha y hora de creación

Datos de ejemplo: tabla orders

Usaremos este dataset para ilustrar cada paso del análisis:

id	user_id	total_amount	created_at
1001	U001	$120.50	2024-03-15
1002	U002	$45.00	2024-01-03
1003	U001	$89.90	2024-04-01
1004	U003	$310.00	2024-04-10
1005	U002	$60.00	2024-02-20
1006	U004	$15.00	2023-11-05
1007	U001	$200.00	2024-04-18
1008	U003	$95.00	2024-03-30

Paso 1: Obtener métricas base

Primero, calculamos R, F y M por usuario agrupando las órdenes:

SELECT
    user_id,
    MAX(created_at) AS last_order_date,
    COUNT(*)        AS frequency,
    SUM(total_amount) AS monetary
FROM orders
GROUP BY user_id;

Resultado esperado

Asumiendo que hoy es 2024-04-30, el query anterior devuelve:

user_id	last_order_date	frequency	monetary
U001	2024-04-18	3	$410.40
U002	2024-02-20	2	$105.00
U003	2024-04-10	2	$405.00
U004	2023-11-05	1	$15.00

💡 Nota: last_order_date aún no es «Recency». En el siguiente paso la convertimos a días.

Paso 2: Calcular Recency

SELECT
    user_id,
    DATE_PART('day', NOW() - MAX(created_at)) AS recency,
    COUNT(*)          AS frequency,
    SUM(total_amount) AS monetary
FROM orders
GROUP BY user_id;

Recency representa los días transcurridos desde la última compra. Usamos una fecha de referencia (normalmente hoy):

Resultado esperado (referencia: 2024-04-30)

user_id	recency (días)	frequency	monetary
U001	12	3	$410.40
U002	69	2	$105.00
U003	20	2	$405.00
U004	176	1	$15.00

💡 A menor valor de recency, más reciente fue la compra — y más valioso es el cliente desde esa perspectiva.

Paso 3: Convertir métricas en scores (1–5)

WITH rfm AS (
  SELECT
      user_id,
      DATE_PART('day', NOW() - MAX(created_at)) AS recency,
      COUNT(*)          AS frequency,
      SUM(total_amount) AS monetary
  FROM orders
  GROUP BY user_id
)
SELECT
    user_id,
    NTILE(5) OVER (ORDER BY recency DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency)    AS f_score,
    NTILE(5) OVER (ORDER BY monetary)     AS m_score
FROM rfm;

Normalizamos los valores usando NTILE para dividir cada métrica en quintiles (1 = peor, 5 = mejor):

Resultado esperado

Recency se ordena DESC porque menor número de días = mejor score. Frequency y Monetary se ordenan ASC.

user_id	recency	frequency	monetary	r_score	f_score	m_score
U001	12	3	$410.40	5	5	5
U002	69	2	$105.00	3	3	2
U003	20	2	$405.00	4	3	4
U004	176	1	$15.00	1	1	1

💡 Con pocos usuarios los quintiles colapsan. NTILE funciona mejor con cientos o miles de registros. En datasets pequeños considera usar PERCENT_RANK o rangos manuales.

Paso 4: Crear el RFM Score combinado

SELECT *,
    CONCAT(r_score, f_score, m_score) AS rfm_score
FROM ...

Concatenamos los tres scores para obtener un identificador compacto por cliente:

Resultado esperado

user_id	r_score	f_score	m_score	rfm_score
U001	5	5	5	555
U002	3	3	2	332
U003	4	3	4	434
U004	1	1	1	111

Un score 555 representa el cliente ideal: compró recientemente, compra con frecuencia y gasta mucho. Un score 111 es una señal de alarma.

Paso 5: Clasificación útil para el negocio

CASE
    WHEN r_score >= 4 AND f_score >= 4 THEN 'VIP'
    WHEN r_score >= 4                  THEN 'Recent Customer'
    WHEN f_score >= 4                  THEN 'Loyal Customer'
    WHEN r_score <= 2                  THEN 'At Risk'
    ELSE 'Regular'
END AS segment

Aquí es donde pasas de datos a decisiones. Aplicamos reglas de negocio sobre los scores:

Resultado esperado: clientes segmentados

user_id	rfm_score	r_score	f_score	segmento
U001	555	5	5	VIP
U002	332	3	3	Recent Customer
U003	434	4	3	Loyal Customer
U004	111	1	1	At Risk

Guía de segmentos y acciones

Segmento	Criterio	Acción recomendada
VIP	r_score ≥ 4 AND f_score ≥ 4	Early access, beneficios exclusivos
Recent Customer	r_score ≥ 4	Bienvenida, cross-sell, upsell
Loyal Customer	f_score ≥ 4	Programa de fidelidad, recompensas
At Risk	r_score ≤ 2	Descuentos agresivos, encuesta de retención
Regular	Resto	Campañas genéricas, nurturing

¿Cómo usar esto en la vida real?

CRM

• VIP → early access, promociones exclusivas
• At Risk → descuentos agresivos, campañas de reactivación
• Nuevos → onboarding personalizado + recomendaciones

BI / Dashboards

• Distribución de segmentos por período
• Revenue generado por tipo de cliente
• Evolución mensual del RFM Score promedio

Automatización

• Triggers para campañas de email según cambio de segmento
• Motores de recomendación basados en perfil RFM
• Lógica de pricing dinámico por segmento

Cosas que suelen salir mal

• Usar datos sucios — órdenes canceladas o devueltas incluidas en el cálculo.
• No actualizar RFM periódicamente — un análisis estático se vuelve obsoleto rápido.
• Usar rangos fijos en lugar de percentiles — los percentiles se adaptan a tu distribución real.
• Ignorar el contexto del negocio — no todo es e-commerce estándar; ajusta los criterios de segmento a tu industria.

Conclusión

RFM no es solo una técnica… es una forma de traducir comportamiento humano en decisiones técnicas.

Con unas cuantas queries puedes pasar de:

«tenemos usuarios» → «estos 200 clientes generan el 60% del revenue y están a punto de irse»

Y ahí, justo ahí, es donde SQL deja de ser solo consultas… y se convierte en estrategia.