Nivel 11 · Desafío final: primeros pedidos y recompras

Las preguntas de retención empiezan todas igual: encuentra el primer evento de cada cliente y luego mira lo que pasa después. Los CTE están hechos para esto, porque cada paso tiene un grano claro.

Paso 1: primer pedido por cliente

SELECT customer_id, CAST(MIN(order_date) AS DATE) AS first_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

Una fila por cliente. Si un pedido cancelado cuenta como “captar” a un cliente es una definición de negocio. El ticket de Megan dice solo completados, así que el filtro va antes del MIN. Si lo pones después, un cliente cuyo primer pedido fue cancelado se captaría en la fecha equivocada.

Paso 2: clientes nuevos por mes

Con los primeros pedidos en un CTE, “clientes nuevos por mes” es un DATE_TRUNC y un conteo. El filtro de año se aplica a first_order_at, no a todos los pedidos: un cliente que compró por primera vez en 2023 y de nuevo en 2025 no es nuevo en 2025. Confundir esos dos filtros es uno de los errores más comunes en los informes de captación.

Paso 3: tasa de recompra a 90 días

Define la cohorte (primer pedido completado en 2024) y luego marca a cada miembro que volvió:

CASE WHEN EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'completed'
    AND o.order_date >  c.first_order_at
    AND o.order_date <= c.first_order_at + INTERVAL '90 days'
) THEN 1 ELSE 0 END AS repeated

EXISTS busca al menos un pedido que califique, así que un cliente con cinco pedidos de recompra se cuenta una sola vez. Un JOIN aquí generaría fan-out e inflaría el numerador. El > estricto excluye el primer pedido en sí. La tasa es la suma de las banderas sobre el tamaño de la cohorte.

Usar una ventana fija (90 días desde el primer pedido de cada cliente) es deliberado. “Volvió a pedir en 2024” daría a los clientes de diciembre casi nada de tiempo para volver y a los de enero un año entero, lo que hace que la tasa dependa de cuándo se unió la gente y no de lo leal que es.

INTERVAL '90 days' funciona en DuckDB y Postgres. MySQL y BigQuery escriben INTERVAL 90 DAY (BigQuery dentro de TIMESTAMP_ADD).

Convertirlo en una historia

Una sola tasa de recompra es útil; una tendencia es mejor. Agrupa el paso 3 por el mes del primer pedido y tendrás una tabla de cohortes: ¿los clientes captados en primavera volvieron más que los de la temporada navideña? Ese es el Desafío final del próximo capítulo, construido con funciones de ventana.

Errores comunes

• Filtrar el estado después de tomar el MIN. Si los pedidos cancelados siguen dentro cuando se ejecuta MIN(order_date), un cliente cuyo primer pedido fue cancelado obtiene la fecha de primera compra equivocada. Pon WHERE status = 'completed' en la misma consulta que el MIN.
• Filtrar el año sobre todos los pedidos. WHERE order_date >= '2025-01-01' antes del MIN hace que un cliente de 2023 parezca nuevo en 2025. Filtra por first_order_at una vez calculado.
• Usar >= en la ventana de recompra. o.order_date >= c.first_order_at coincide con el propio primer pedido, así que todos los clientes cuentan como recompra. Usa >.
• Unir con los pedidos de recompra en lugar de usar EXISTS. Un cliente con cinco pedidos de recompra se cuenta cinco veces en el numerador.

En SQL Server

Reemplaza c.first_order_at + INTERVAL '90 days' por DATEADD(day, 90, c.first_order_at). Los CTE y MIN(order_date) no cambian, y la clave de mes es DATETRUNC(month, first_order_at) (SQL Server 2022) o DATEFROMPARTS(YEAR(first_order_at), MONTH(first_order_at), 1).