DataHub Pro › ES › Análisis de cohortes en Excel

Cómo hacer un análisis de cohortes en Excel — mide la retención paso a paso

Tus ventas totales pueden subir mientras tu negocio se desangra por debajo: si pierdes clientes tan rápido como los captas, el crecimiento es una ilusión. El análisis de cohortes es la herramienta que destapa esa verdad. Agrupa a tus clientes por el mes en que llegaron y sigue cuántos siguen activos mes a mes. En esta guía construirás una tabla de cohortes de retención completa en Excel —con fórmulas reales y un mapa de calor— y verás cómo automatizar todo el proceso con IA.

Dr Waqas Rafique Dr Waqas Rafique · Fundador y CTO · PhD en Aprendizaje Automático Estadístico
· Acerca de

Resumen rápido

Halla el mes de adquisición de cada cliente con MIN.SI.CONJUNTO + FIN.MES, calcula los meses transcurridos de cada actividad con SIFECHA, cuenta los clientes activos por cohorte y período con SUMAR.SI.CONJUNTO, divide entre el tamaño de la cohorte para el % de retención y aplica formato condicional de escala de color. ¿Sin armar fórmulas? Sube tu archivo a DataHub Pro y la IA arma la tabla de cohortes por ti.

Contenido

  1. ¿Qué es un análisis de cohortes?
  2. Antes de empezar
  3. Paso 1 — Prepara los datos
  4. Paso 2 — Mes de adquisición
  5. Paso 3 — Período relativo
  6. Paso 4 — Cuenta clientes por celda
  7. Paso 5 — Porcentaje de retención
  8. Paso 6 — Mapa de calor
  9. Cómo leer la tabla de cohortes
  10. Errores comunes y soluciones
  11. Preguntas frecuentes

¿Qué es un análisis de cohortes?

Una cohorte es un grupo de clientes que comparten un evento de origen común. En el análisis de retención más habitual, ese evento es el mes de la primera compra: todos los clientes que hicieron su primer pedido en enero forman la cohorte de enero, los de febrero forman la de febrero, y así sucesivamente. El análisis de cohortes consiste en seguir a cada uno de esos grupos a lo largo del tiempo para ver qué porcentaje sigue activo un mes después, dos meses después, tres meses después.

La razón por la que esto es tan poderoso es que separa dos cosas que un promedio global mezcla: cuántos clientes nuevos entran y cuánto tiempo se quedan. Si solo miras la cantidad total de clientes activos, una avalancha de altas nuevas puede ocultar que los clientes antiguos te están abandonando en masa. La tabla de cohortes desenreda eso: cada fila te muestra el destino de un grupo concreto, independientemente de cuántos llegaron después.

El resultado típico es una matriz triangular: las cohortes en las filas (enero, febrero, marzo...) y los meses transcurridos desde la adquisición en las columnas (mes 0, mes 1, mes 2...). Cada celda dice qué porcentaje de esa cohorte seguía activo en ese mes. Leída en diagonal, revela si la retención está mejorando con el tiempo; leída por filas, muestra la "curva de vida" de cada grupo de clientes.

Antes de empezar

El análisis de cohortes es exigente con la calidad de los datos. Vale la pena revisar estos puntos antes de escribir la primera fórmula.

Necesitas dos columnas mínimas: cliente y fecha

Cada fila debe representar una actividad relevante —una compra, un pedido, un inicio de sesión, un pago de suscripción— y contener, como mínimo, un identificador único del cliente y la fecha en que ocurrió. Si quieres además cohortes de ingresos, incluye también el importe.

El identificador de cliente debe ser estable

El mismo cliente debe aparecer siempre con el mismo identificador. Si en una fila es "cliente@correo.com" y en otra "Cliente@Correo.com", Excel los tratará como personas distintas y la retención saldrá inflada. Normaliza los identificadores (por ejemplo, todo en minúsculas) antes de empezar.

Las fechas deben ser fechas reales

Una fecha guardada como texto rompe las funciones FIN.MES y SIFECHA. Comprueba que tus fechas se alineen a la derecha (señal de que Excel las reconoce como números de fecha) y, si no, conviértelas con Datos → Texto en columnas o con la función VALOR.FECHA.

💡 Consejo: Convierte tus datos en una tabla con Ctrl+T y dale un nombre como tActividad. Así las fórmulas que escribas más adelante se actualizarán solas cuando agregues nuevas filas de actividad.
1
2
3
4
5
6

1.Paso 1 — Prepara los datos de clientes

Empieza con tu tabla de actividad. Supongamos que tienes el identificador de cliente en la columna A (ClienteID) y la fecha de cada compra en la columna B (Fecha), desde la fila 2. Conviértelo en tabla con Ctrl+T y nómbrala tActividad.

Si tu archivo tiene una fila por cliente con varias fechas en columnas separadas, primero necesitas "despivotarlo" para que cada actividad ocupe su propia fila. Power Query (Datos → Obtener datos) hace esto fácilmente, pero para empezar basta con tener una fila por compra.

2.Paso 2 — Calcula el mes de adquisición de cada cliente

La cohorte de un cliente la define el mes de su primera compra. Para hallar esa primera fecha, usa MIN.SI.CONJUNTO, que devuelve la fecha mínima para un identificador dado. En una columna nueva (C, "Primera compra"):

=MIN.SI.CONJUNTO(tActividad[Fecha], tActividad[ClienteID], [@ClienteID])

Después normaliza esa fecha al primer día de su mes para definir la cohorte. FIN.MES con argumento -1 y sumando 1 te da el primer día del mes:

=FIN.MES([@[Primera compra]], -1) + 1

El resultado es el "mes de cohorte" de cada cliente. Todos los clientes con el mismo mes de cohorte pertenecen al mismo grupo.

💡 Consejo: Si tu versión de Excel no tiene MIN.SI.CONJUNTO (disponible desde Office 2019 / Microsoft 365), puedes lograr lo mismo con una fórmula matricial: =MIN(SI(tActividad[ClienteID]=[@ClienteID], tActividad[Fecha])) confirmada con Ctrl+Mayús+Entrar en versiones antiguas.

3.Paso 3 — Calcula el período relativo de cada actividad

Ahora necesitas saber cuántos meses pasaron entre la adquisición del cliente y cada actividad suya. Primero normaliza la fecha de la actividad a su mes (igual que antes) en una columna "Mes actividad", y luego cuenta los meses transcurridos con SIFECHA (en algunas versiones, DATEDIF), usando la unidad "m" de meses:

=SIFECHA([@[Mes cohorte]], [@[Mes actividad]], "m")

Este número es el "período relativo": 0 para la actividad del mes en que el cliente fue adquirido, 1 para el mes siguiente, 2 para dos meses después, y así. Es el eje horizontal de tu tabla de cohortes.

⚠ Importante: Si te aparece un período relativo negativo, casi siempre significa que confundiste el mes de cohorte con el mes de actividad en la resta. El mes de cohorte (la primera compra) debe ir siempre primero en SIFECHA, porque ninguna actividad puede ser anterior a la primera compra del propio cliente.

4.Paso 4 — Cuenta clientes activos por cohorte y período

Construye la matriz de cohortes en una zona nueva: las cohortes (meses de adquisición) en las filas y los períodos relativos (0, 1, 2, 3...) en las columnas. En cada celda quieres contar cuántos clientes de esa cohorte estuvieron activos en ese período. Con CONTAR.SI.CONJUNTO sobre las columnas auxiliares que creaste:

=CONTAR.SI.CONJUNTO(MesCohorte, $A2, PeriodoRelativo, B$1)

Aquí $A2 es el mes de cohorte de la fila (referencia con columna fija) y B$1 es el número de período de la columna (referencia con fila fija). Esa combinación de $ permite arrastrar la fórmula por toda la matriz sin que se descuadren las referencias.

💡 Consejo: Si un mismo cliente compra varias veces en el mismo período, CONTAR.SI.CONJUNTO sobre las filas de actividad lo contaría más de una vez. Para contar clientes únicos, trabaja sobre una tabla con una sola fila por cliente y período (puedes generarla con una tabla dinámica con "Recuento distinto") antes de aplicar la fórmula.

5.Paso 5 — Convierte los conteos en porcentaje de retención

Los conteos absolutos no se comparan bien entre cohortes de distinto tamaño. Conviértelos a porcentaje dividiendo cada celda entre el tamaño de su cohorte, que es el valor de la columna del período 0 (la primera columna). Si tus conteos están desde B2 y el tamaño de la cohorte vive en la columna B:

=B2 / $B2

Fija la columna del divisor con $B2 para que, al arrastrar a la derecha, siempre divida entre el tamaño original de esa cohorte. La columna del período 0 dará siempre 100%, y las siguientes mostrarán qué porcentaje de aquellos clientes seguía activo. Da formato de porcentaje a toda la matriz.

CohorteMes 0Mes 1Mes 2Mes 3
Enero100%48%32%27%
Febrero100%52%38%31%
Marzo100%55%41%—

En este ejemplo, la retención del mes 1 mejora de 48% (enero) a 55% (marzo): señal de que algo en tu producto o adquisición está funcionando mejor con cada cohorte nueva.

6.Paso 6 — Crea un mapa de calor de la tabla de cohortes

Una matriz de números es difícil de leer; un mapa de calor la vuelve obvia. Selecciona toda la zona de porcentajes y ve a Inicio → Formato condicional → Escalas de color. Elige una escala de tres colores (por ejemplo, rojo para los valores bajos, amarillo para los medios y verde para los altos).

Excel colorea cada celda según su valor. Al instante verás franjas: las primeras columnas suelen ser verdes (alta retención inicial) y, a medida que avanzas a la derecha, el color se enfría hacia el rojo conforme los clientes se van. Cualquier cohorte que rompa el patrón —una fila inesperadamente roja o verde— salta a la vista.

Cómo leer la tabla de cohortes

Una vez construida, la tabla responde tres preguntas clave que ningún promedio global puede contestar.

¿Dónde está la mayor fuga? Mira la caída entre el mes 0 y el mes 1 en cualquier fila. Suele ser la más grande: es el momento en que decides si un cliente se quedó o no. Si pierdes la mitad de cada cohorte en el primer mes, tu prioridad es la activación temprana, no captar más clientes.

¿La retención mejora con el tiempo? Compara la misma columna (por ejemplo, el mes 1) entre cohortes sucesivas. Si los números suben de enero a marzo, tus cambios recientes están funcionando. Si bajan, algo se está deteriorando, aunque las ventas totales sigan creciendo.

¿Cuál es tu "suelo" de retención? En cohortes maduras, la retención suele estabilizarse en un porcentaje que ya no baja: ese es tu núcleo de clientes leales. Conocer ese suelo te dice cuánto valor de largo plazo genera realmente cada cliente captado.

Cohortes automáticas, sin armar la matriz a mano

Construir cohortes en Excel es potente, pero laborioso y frágil. Sube tu archivo de clientes a DataHub Pro y la IA genera la tabla de cohortes con su mapa de calor, la curva de retención y más, lista para exportar a Word o PowerPoint. Nivel gratuito permanente con 3 consultas de IA al día. Plan de pago desde $14.99/mes.

Prueba DataHub Pro gratis →

Errores comunes y soluciones

La retención sale por encima del 100%

Estás contando actividades en lugar de clientes únicos: un cliente que compra tres veces en un mes cuenta como tres. Trabaja sobre una tabla con una fila por cliente y período (usa "Recuento distinto" en una tabla dinámica) antes de contar.

Períodos relativos negativos

Confundiste el orden en SIFECHA: el mes de cohorte (primera compra) debe ir antes que el mes de actividad. Revisa también que el "mes de cohorte" use la fecha mínima del cliente, no una fecha cualquiera.

FIN.MES o SIFECHA devuelven #¡VALOR!

Tus fechas están guardadas como texto. Conviértelas a fechas reales con Texto en columnas o VALOR.FECHA antes de calcular cohortes.

Las fórmulas se descuadran al arrastrar por la matriz

Faltan los anclajes $. La cohorte de la fila se fija con columna absoluta ($A2) y el período de la columna con fila absoluta (B$1). Sin esos anclajes, las referencias se desplazan mal al copiar.

Preguntas frecuentes

¿Qué es un análisis de cohortes?
Un análisis de cohortes agrupa a tus clientes según un evento común —normalmente el mes en que hicieron su primera compra— y sigue su comportamiento a lo largo del tiempo. En lugar de mirar la retención global, ves cómo se comporta cada grupo (cohorte) mes a mes desde que entró. Esto revela si los clientes nuevos se quedan más o menos que los antiguos y en qué momento exacto los pierdes, algo que un promedio general oculta.
¿Cómo hago un análisis de cohortes en Excel?
Primero, prepara los datos con una fila por compra (cliente y fecha). Calcula el mes de adquisición de cada cliente con MIN.SI.CONJUNTO y FIN.MES, y el período relativo de cada actividad con SIFECHA. Luego construye una matriz con las cohortes en las filas y los meses transcurridos en las columnas, cuenta los clientes activos en cada celda con SUMAR.SI.CONJUNTO, divide entre el tamaño de la cohorte para obtener el porcentaje de retención y aplica formato condicional para crear un mapa de calor.
¿Qué fórmulas necesito para el análisis de cohortes en Excel?
Las principales son: MIN.SI.CONJUNTO para hallar la primera compra de cada cliente, FIN.MES para normalizar fechas al mes, SIFECHA (o DATEDIF) para calcular los meses transcurridos entre la adquisición y cada actividad, y SUMAR.SI.CONJUNTO o CONTAR.SI.CONJUNTO para contar clientes por cohorte y período. El porcentaje de retención se obtiene dividiendo cada conteo entre el tamaño de la cohorte (la columna del mes 0).
¿Qué es la retención de cohortes y por qué importa?
La retención de cohortes es el porcentaje de clientes de un grupo que siguen activos N meses después de su primera compra. Importa porque mide la salud real de tu negocio mejor que el total de ventas: puedes estar creciendo en ingresos mientras pierdes clientes a gran velocidad. La curva de retención por cohorte te dice si tu producto realmente fideliza y en qué mes se produce la mayor fuga, para que actúes justo ahí.
¿Cómo creo un mapa de calor de cohortes en Excel?
Selecciona la matriz con los porcentajes de retención, ve a Inicio > Formato condicional > Escalas de color y elige una escala (por ejemplo, de rojo a verde). Excel colorea cada celda según su valor, de modo que las zonas de alta retención resaltan en verde y las de baja fuga en rojo. Así detectas de un vistazo qué cohortes y qué meses tienen mejor o peor comportamiento.
¿Sirve un análisis de cohortes para ecommerce y SaaS?
Sí, es una de las herramientas más útiles en ambos casos. En ecommerce, las cohortes muestran qué porcentaje de los compradores de cada mes vuelve a comprar en los meses siguientes, lo que mide la fidelidad real. En SaaS, las cohortes de suscriptores revelan a qué ritmo cancelan (churn) y si las mejoras del producto realmente reducen la fuga. En ambos casos, el análisis por cohorte es más revelador que cualquier promedio global.
¿Por qué mi tabla de cohortes en Excel tiene errores o celdas vacías?
Las causas más comunes son fechas guardadas como texto en lugar de fechas reales (lo que rompe FIN.MES y SIFECHA), un período relativo negativo por confundir la fecha de adquisición con la de actividad, o referencias que no son absolutas al copiar las fórmulas por la matriz. Verifica que las fechas sean fechas, fija las referencias con el símbolo $ donde corresponda y asegúrate de que el mes 0 corresponda siempre a la primera compra de cada cliente.
¿Cuál es la diferencia entre cohortes de retención y de ingresos?
Una cohorte de retención cuenta cuántos clientes siguen activos en cada período, mientras que una cohorte de ingresos suma cuánto dinero gasta cada grupo a lo largo del tiempo. La de retención mide fidelidad; la de ingresos mide valor. Ambas se construyen igual en Excel —la diferencia está en si cuentas clientes con CONTAR.SI.CONJUNTO o sumas importes con SUMAR.SI.CONJUNTO— y juntas dan una imagen completa del comportamiento del cliente.
¿Puedo automatizar el análisis de cohortes sin armar las fórmulas en Excel?
Sí. Construir cohortes a mano en Excel es potente pero laborioso y propenso a errores. DataHub Pro automatiza el análisis: subes tu archivo de clientes con identificador y fecha, y la IA genera la tabla de cohortes con su mapa de calor, además de la curva de retención y otros análisis. Tienes un nivel gratuito permanente con 3 consultas de IA al día, y el plan de pago parte de $14.99/mes, con exportación a Word y PowerPoint.

Sigue aprendiendo