Cómo hacer un análisis RFM en Excel con COUNTIFS, SUMIFS y PERCENTRANK.

¿Qué es el análisis RFM?

El análisis RFM es un método clásico de segmentación de clientes que parte de una idea muy simple: no todos tus clientes valen lo mismo, y la mejor forma de medir su valor es mirar tres cosas concretas de su comportamiento de compra.

• Recencia (R): ¿hace cuánto compró por última vez? Un cliente que compró ayer es mucho más valioso que uno que no compra hace un año, aunque ambos hayan gastado lo mismo.
• Frecuencia (F): ¿con qué frecuencia compra? Los clientes que vuelven una y otra vez son la base de cualquier negocio sano.
• Valor Monetario (M): ¿cuánto ha gastado en total? Mide la contribución real de cada cliente a tus ingresos.

Al puntuar a cada cliente en estas tres dimensiones obtienes una foto muy precisa de tu base. Identificas a tus Campeones (compran seguido, recientemente y gastan mucho), detectas a quienes están en riesgo de abandono antes de perderlos y descubres a clientes nuevos con potencial. El RFM convierte una lista plana de transacciones en un mapa accionable para tus campañas de marketing, fidelización y retención.

Lo mejor es que no necesitas software caro para empezar: Excel tiene todo lo que hace falta. A continuación construimos el análisis completo, columna por columna.

Paso 1 — Prepara tu tabla de transacciones

Todo RFM empieza con una tabla limpia de transacciones. Necesitas, como mínimo, tres columnas: el ID del cliente, la fecha de la compra y el importe. Cada fila representa una transacción individual.

A          B            C
Cliente    Fecha        Importe
C001       2026-01-12   1200
C001       2026-03-04   850
C002       2025-11-20   430
C003       2026-05-30   2100
...

Antes de seguir, revisa dos cosas importantes. Primero, que las fechas sean fechas reales y no texto: selecciona la columna y confirma que Excel las reconoce (se alinean a la derecha). Si están como texto, conviértelas con =FECHANUMERO(B2) o ajustando el formato regional. Segundo, que no haya filas vacías ni importes en blanco, porque distorsionan los conteos y las sumas.

Por comodidad, te recomendamos crear una hoja aparte con la lista única de clientes. Puedes obtenerla con Quitar duplicados o con la función =UNICOS(Clientes) en Microsoft 365. En esa hoja construiremos las columnas R, F y M, una fila por cliente.

Finalmente, define una fecha de referencia en una celda fija, por ejemplo $F$1. Suele ser la fecha de hoy =HOY() o el último día del periodo que estás analizando. La recencia se mide siempre respecto a esa fecha.

Paso 2 — Calcula la Recencia con MAXIFS

La recencia es el número de días transcurridos desde la última compra de cada cliente. Primero obtenemos la fecha de su última compra con MAXIFS y luego la restamos a la fecha de referencia.

Donde A2 es el ID del cliente en tu hoja de resumen, $F$1 es la fecha de referencia, y los rangos apuntan a las fechas e IDs de la hoja de transacciones. El resultado es un número de días: cuanto menor, más reciente y, por tanto, más valioso el cliente.

Si usas una versión de Excel anterior a 2016 sin MAXIFS, puedes lograr lo mismo con una fórmula matricial: =$F$1-MAX(SI(Transacciones!$A:$A=A2;Transacciones!$B:$B)) confirmada con Ctrl+Mayús+Enter.

Paso 3 — Calcula la Frecuencia con COUNTIFS

La frecuencia cuenta cuántas veces ha comprado cada cliente. La fórmula es directa con COUNTIFS (CONTAR.SI.CONJUNTO en Excel en español):

Esto cuenta cuántas filas de la tabla de transacciones corresponden al cliente A2. Aquí hay una decisión importante: ¿quieres contar líneas de pedido o pedidos únicos? Si tu tabla tiene una fila por producto comprado, un solo pedido con cinco productos contaría como cinco. Para contar pedidos reales, añade una columna de número de pedido y cuenta valores únicos, o trabaja sobre una tabla previamente agregada a nivel de pedido.

Para la mayoría de los negocios de ecommerce, contar pedidos únicos refleja mejor el comportamiento de compra que contar líneas individuales.

Paso 4 — Calcula el Valor Monetario con SUMIFS

El componente monetario suma todo lo que ha gastado cada cliente. Usamos SUMIFS (SUMAR.SI.CONJUNTO):

Esto suma la columna de importes (C) para todas las filas del cliente A2. Algunos analistas prefieren usar el ticket promedio (valor monetario dividido entre frecuencia) en lugar del gasto total, para no penalizar a clientes nuevos que aún no han acumulado muchas compras. Ambas variantes son válidas; el gasto total es la versión clásica del RFM y la más usada.

Con estas tres columnas — R, F y M — ya tienes el comportamiento de cada cliente resumido en tres números. El siguiente paso es convertirlos en puntajes comparables.

Paso 5 — Asigna puntajes del 1 al 5 con PERCENTRANK

Los valores brutos de R, F y M no son comparables entre sí (días vs. número de compras vs. dinero). La solución estándar es convertir cada métrica en un puntaje de 1 a 5 basado en su posición relativa dentro de toda tu base de clientes (quintiles).

Para la frecuencia y el valor monetario, donde más es mejor, usa:

Aquí $D$2:$D$500 es toda la columna de frecuencia (o monetario) y D2 el valor del cliente actual. RANGO.PERCENTIL (PERCENTRANK) devuelve un número entre 0 y 1; lo multiplicamos por 5 y redondeamos hacia arriba para obtener un puntaje de 1 a 5.

Para la recencia hay que invertir la escala, porque menos días es mejor. El cliente más reciente debe recibir un 5, no un 1:

Al restar de 6, el cliente con menor recencia (compra más reciente) obtiene el puntaje más alto. Repite el proceso para las tres columnas y tendrás los puntajes R, F y M de cada cliente, todos en la misma escala de 1 a 5.

Paso 6 — Crea el código RFM combinado

Ahora une los tres puntajes en un único código de tres dígitos, en el orden R-F-M:

Esto produce códigos como 555, 144 o 313. La lectura es intuitiva: el primer dígito es la recencia, el segundo la frecuencia y el tercero el valor monetario. Un 555 es tu cliente ideal; un 111, prácticamente perdido. Un 155 es un cliente que gastaba mucho y compraba seguido pero lleva tiempo sin volver: justo el tipo de cliente al que conviene lanzar una campaña de reactivación urgente.

Algunos equipos prefieren trabajar con un puntaje numérico promedio (=(R+F+M)/3) para ordenar rápidamente, pero el código de tres dígitos conserva mucha más información para definir segmentos precisos.

Paso 7 — Segmenta a tus clientes

Los 125 códigos posibles son demasiados para actuar sobre ellos. El paso final es agruparlos en segmentos accionables. Un esquema muy usado es el siguiente:

Para asignar el segmento automáticamente, crea una tabla de equivalencias en una hoja aparte y usa BUSCARV (VLOOKUP) o una serie de SI anidados sobre los puntajes R, F y M. Por ejemplo:

Ajusta los umbrales según tu negocio. No existe una segmentación universal: lo importante es que cada segmento tenga una acción de marketing clara asociada.

Paso 8 — Visualiza y activa los segmentos

Con la columna de segmento lista, crea una tabla dinámica para ver de un vistazo cuántos clientes y cuántos ingresos representa cada grupo. Coloca el segmento en filas, una cuenta de clientes en valores y la suma del valor monetario en otra columna. Casi siempre descubrirás que un puñado de segmentos (Campeones y Leales) concentra la mayor parte de tus ingresos — el clásico principio de Pareto.

El último paso es activar los segmentos: exporta la lista de clientes de cada grupo y úsala en tus campañas. Los Campeones reciben un trato VIP; los clientes En riesgo, una oferta de reactivación; los Prometedores, un incentivo para su segunda compra. Aquí es donde el análisis RFM deja de ser un ejercicio de Excel y empieza a generar ingresos reales.

Si quieres comparar este flujo manual con un dashboard automático, revisa cómo se construye un dashboard desde Excel con IA en DataHub Pro.

Comparativa: RFM manual en Excel vs DataHub Pro

¿Quieres profundizar en otras alternativas de análisis? Mira nuestras guías sobre la alternativa a Power BI y la alternativa a Tableau para equipos en LATAM.

¿Por qué automatizar tu RFM con DataHub Pro?

Preguntas frecuentes sobre el análisis RFM en Excel