Nivel 11 · Desafío final: retención por cohortes

El análisis de cohortes es la prueba clásica para llevar a casa de los analistas: necesita CTE, funciones de ventana, lógica de fechas y una definición clara, todo a la vez. Este desafío final te lleva hasta ahí paso a paso.

Paso 1: participación sobre el total

Agrega por categoría y luego divide cada fila entre el total general:

ROUND(100.0 * net_revenue / SUM(net_revenue) OVER (), 1) AS pct_of_total

Un OVER () vacío hace que la ventana sea todo el resultado, así que cada fila ve el mismo total. Sin subconsulta, sin join. Las participaciones deben sumar cerca de 100; si suman mucho más, un join multiplicó filas.

Paso 2: tiempo hasta el segundo pedido

ROW_NUMBER numera los pedidos de cada cliente en orden cronológico. LAG da entonces el intervalo de cada pedido respecto al anterior, y conservar order_number = 2 deja exactamente una fila por cliente que vuelve: el intervalo entre su primer y su segundo pedido. El desempate por order_id protege contra dos pedidos con la misma marca de tiempo.

El starter muestra los pedidos numerados de un solo cliente. Inspeccionar una sola entidad así es como se comprueba que una ventana tiene sentido antes de agregarla.

Los clientes que nunca hicieron un segundo pedido no entran en el promedio. Eso es correcto para “cuánto tardan en volver”, pero preséntalo junto a cuántos no volvieron nunca, o el número se ve mejor que la realidad.

Paso 3: retención por cohortes

Tres definiciones forman una tabla de cohortes:

• Cohorte: el mes del primer pedido completado de un cliente. MIN(order_month) OVER (PARTITION BY customer_id) la adjunta a cada una de sus filas sin colapsarlas.
• Actividad: un cliente está activo en un mes si hizo al menos un pedido completado. La CTE customer_months usa SELECT DISTINCT a propósito, para obtener una fila por cliente por mes, el grano correcto para “activo o no”.
• Retenido en el mes 1: activo en el mes calendario inmediatamente posterior al mes de la cohorte.

COUNT(DISTINCT CASE WHEN order_month = CAST(cohort_month + INTERVAL '1 month' AS DATE)
                    THEN customer_id END) AS retained_m1

COUNT(DISTINCT CASE ...) cuenta los clientes que cumplen la condición. El CASE devuelve NULL en los demás casos, y COUNT ignora los NULL.

El filtro de cohortes se aplica después de la ventana, en la consulta externa. Filtrar los pedidos a 2025 antes de calcular el MIN asignaría a una cohorte de 2025 a los clientes que compraron por primera vez en 2024, la misma trampa que con LAG y las medias móviles.

Notas de dialecto

Sumar un mes: DuckDB y Postgres aceptan date + INTERVAL '1 month' (el resultado es un timestamp, de ahí el cast). BigQuery usa DATE_ADD(cohort_month, INTERVAL 1 MONTH), MySQL DATE_ADD(cohort_month, INTERVAL 1 MONTH), Snowflake DATEADD(month, 1, cohort_month).

Ir más allá

La tabla de cohortes completa tiene una columna para cada desfase de mes (m1, m2, m3, …), que suele calcularse con DATEDIFF en meses entre order_month y cohort_month, agrupando por cohorte y desfase y luego pivotando. En una entrevista, acertar el mes 1, con definiciones claras dichas en voz alta, vale más que una cuadrícula completa hecha a toda prisa.

Errores comunes

• Filtrar los pedidos a 2025 antes del MIN. Los clientes que compraron por primera vez en 2024 quedan entonces en una cohorte de 2025. Calcula el mes de la cohorte sobre todos los pedidos y filtra por fuera.
• Contar filas en lugar de clientes. COUNT(*) sobre filas de pedidos cuenta pedidos, no clientes activos. Usa COUNT(DISTINCT customer_id), o una CTE de una fila por cliente por mes.
• Reportar solo el intervalo promedio hasta un segundo pedido. Cubre únicamente a los clientes que volvieron; muestra a su lado cuántos nunca hicieron un segundo pedido.
• Numerar pedidos sin un criterio de desempate. Dos pedidos con la misma marca de tiempo pueden intercambiar el primer y el segundo puesto. Añade order_id al ORDER BY del ROW_NUMBER.

En SQL Server

Suma un mes con DATEADD(month, 1, cohort_month); DATEDIFF(month, cohort_month, order_month) da el desfase de mes para una cuadrícula completa de cohortes. Los intervalos en días necesitan DATEDIFF(day, prev_day, order_day), porque - entre fechas da error. Las funciones de ventana en sí no cambian.