Definir cohortes de clientes con CTEs

Llegaste al cuarto hito del proyecto. Hasta aquí modelaste el esquema, mediste tendencias de ingresos y construiste rankings top-N por categoría. Todas esas preguntas miran el negocio desde el lado de los productos y los ingresos. En este hito giramos la cámara hacia el otro lado igual de importante: los clientes. ¿Los que compraron una vez vuelven? ¿Cuánto tarda un cliente en abandonar? ¿Una camada de clientes nuevos se comporta mejor que la anterior?

La herramienta estándar para responder esto se llama análisis de cohortes, y es el corazón de cualquier informe de retención. En esta lección sentamos su primera pieza: asignar a cada cliente la cohorte de su primera compra y registrar en qué periodos siguió activo. En la siguiente lección convertiremos eso en una matriz de retención, y luego la visualizaremos como un heatmap.

Refresco rápido (CTEs). Una CTE (WITH nombre AS (...)) es una consulta con nombre que vive solo durante la consulta principal. Las viste en SQL Avanzado; aquí las usamos para lo que mejor hacen: encadenar pasos legibles —primera compra → etiqueta de cohorte → actividad— sin anidar subconsultas ilegibles.

¿Qué es una cohorte y por qué importa?

Una cohorte es un grupo de clientes que comparten un evento de inicio en el mismo periodo. La definición más común —y la que usaremos— es la cohorte de adquisición: todos los clientes cuya primera compra ocurrió en el mismo mes.

El truco mental clave: en vez de mirar a todos los clientes mezclados, los separamos por cuándo entraron y seguimos a cada grupo a lo largo del tiempo. Eso permite comparar manzanas con manzanas.

Un ejemplo concreto de por qué importa. Imagina que tu total de clientes activos sube mes a mes y todo parece sano. Pero si lo descompones por cohortes, podrías descubrir que cada cohorte nueva retiene peor que la anterior: el crecimiento solo se sostiene porque entran más clientes nuevos, no porque los retengas. Esa es exactamente la clase de hallazgo que un promedio global esconde y un análisis de cohortes revela.

Para construir una cohorte necesitamos dos coordenadas por cada actividad del cliente:

1. A qué cohorte pertenece → el mes de su primera compra (fijo, no cambia nunca).
2. En qué periodo está activo → el mes de cada compra, medido como distancia desde su cohorte (mes 0, mes 1, mes 2…).

Vamos a calcular ambas, paso a paso, con una cadena de CTEs.

El plan: una cadena de tres CTEs

Resolveremos el problema en tres pasos encadenados. Cada CTE toma el resultado de la anterior y le añade una pieza, de modo que la consulta final se lee casi como una frase en español.

Antes de escribir la consulta, conozcamos los datos. Trabajaremos con una tabla pedidos minimalista —solo lo necesario para cohortes: quién compró, cuándo y por cuánto— poblada con seis clientes a lo largo de cuatro meses de 2024. Ejecuta este primer bloque para ver el dataset crudo:

Paso 1 — La primera compra de cada cliente

La cohorte de un cliente está anclada a un único momento: su primera compra. En SQL eso es exactamente MIN(fecha_pedido) agrupando por cliente. Este es el cimiento de todo lo demás, así que lo aislamos en su propia CTE.

Fíjate en algo importante: la fecha de primera compra es fija por cliente. No importa cuántas veces vuelva después; su cohorte se decide aquí y no cambia.

Paso 2 — Etiquetar la cohorte (el mes de la primera compra)

Una fecha exacta como 2024-01-17 es demasiado granular: queremos agrupar clientes por mes, no por día. Para eso usamos date_trunc('month', fecha), que "redondea" cualquier fecha al primer día de su mes (2024-01-17 → 2024-01-01). Lo convertimos a date para que la etiqueta quede limpia.

Encadenamos una segunda CTE, cohortes, que consume la salida de primera_compra. Aquí se ve la elegancia de las CTEs: cada paso es una idea, con nombre, leíble de arriba abajo.

Ese resultado ya es un hallazgo en sí mismo: el tamaño de cada cohorte, es decir, cuántos clientes nuevos captaste cada mes. Es el denominador con el que más adelante calcularemos los porcentajes de retención (mes 0 = 100 %).

Paso 3 — Cruzar cohorte con actividad y medir el periodo

Ahora la pieza final. Por cada cliente necesitamos los meses en los que tuvo actividad (no solo el primero). Un cliente puede comprar varias veces en el mismo mes, así que usamos DISTINCT para quedarnos con un registro por cliente y mes activo: nos interesa si estuvo activo, no cuántos pedidos hizo.

Luego unimos cada cliente con su cohorte y calculamos el número de periodo: cuántos meses pasaron entre su cohorte y cada mes de actividad. El mes de la primera compra es el periodo 0, el siguiente es el 1, y así sucesivamente. La fórmula es la diferencia en meses entre ambas fechas:

$\text{periodo} = (\text{año}_{act} - \text{año}_{coh}) \times 12 + (\text{mes}_{act} - \text{mes}_{coh})$

Que en SQL escribimos con EXTRACT:

¡Esa tabla es el resultado central de la lección! Cada fila dice: de la cohorte X, en el periodo N había Y clientes activos. Léela con atención:

• En periodo 0, clientes_activos coincide con el tamaño de la cohorte (por definición, todos están activos el mes en que entraron).
• A medida que crece el periodo, el número suele caer: eso es exactamente la retención (o su pérdida) que queremos medir.
• La cohorte de enero llega hasta periodos más altos simplemente porque ha tenido más meses de vida que la de marzo.

Esta tabla cohorte / periodo / clientes_activos es justo el insumo que en la próxima lección pivotaremos en una matriz de retención (cohortes en las filas, periodos en las columnas) y convertiremos a porcentajes.

Visualicemos la caída de actividad

Para dar intuición antes del heatmap de la próxima lección, este gráfico muestra los clientes_activos por periodo de cada cohorte del resultado anterior. Cada línea es una cohorte; fíjate cómo todas arrancan en su tamaño inicial y descienden:

Tu turno

Antes de pasar a la matriz de retención, experimenta sobre el bloque del Paso 3:

1. Cohortes trimestrales. Cambia date_trunc('month', ...) por date_trunc('quarter', ...) en cohortes y en actividad. ¿Cómo se agrupan ahora los clientes? (Pista: con trimestres y la fórmula de meses, los periodos saltarán de 3 en 3.)
2. Retención por ingresos, no por cabezas. En la consulta final añade SUM(...) del monto. Necesitarás traer el monto hasta la CTE actividad (agregándolo por cliente y mes). ¿Las cohortes que retienen más clientes son también las que retienen más ingresos?
3. Predice y comprueba. Sin ejecutar, ¿cuántos clientes activos esperas en la cohorte 2024-02, periodo 1? Corre la consulta y verifica.

Errores comunes a evitar

• Confundir cohorte con mes de actividad. La cohorte se fija con MIN(fecha) una sola vez; el mes de actividad cambia con cada compra. Si etiquetas la cohorte con la fecha de cada pedido, un cliente "saltaría" de cohorte y el análisis pierde sentido.
• Olvidar DISTINCT en la actividad. Sin él, un cliente con 3 pedidos en un mes contaría como 3 "activos". Para retención cuentas personas, no pedidos.
• Calcular el periodo restando fechas directamente. fecha_a - fecha_b en Postgres da días, no meses. Usa la diferencia de EXTRACT(YEAR)/EXTRACT(MONTH) (o age()), como hicimos.
• Subconsultas anidadas en vez de CTEs. Funcionaría, pero sería ilegible. Las CTEs encadenadas hacen el razonamiento explícito y depurable paso a paso.

Para recordar

• Una cohorte de adquisición agrupa clientes por el mes de su primera compra (MIN(fecha) + date_trunc).
• El análisis de cohortes responde lo que un promedio global esconde: ¿los clientes que entraron juntos siguen volviendo?
• Necesitas dos coordenadas: la etiqueta de cohorte (fija) y el número de periodo (meses desde la primera compra).
• Una cadena de CTEs —primera_compra → cohortes → actividad → consulta final— mantiene el problema legible y depurable.
• El resultado cohorte / periodo / clientes_activos es el insumo directo de la matriz de retención que construirás en la próxima lección.