Nivel 1 · Retención de clientes en SQL

Cómo lee un analista este ticket

«¿Los clientes vuelven?» suena como un solo número. Se vuelve un número útil solo después de tres decisiones:

1. Qué cuenta como pedido. Aquí, solo completed. Un pedido cancelado no es una compra, y un cliente cuyo único pedido fue reembolsado en realidad no empezó una relación con la tienda.
2. Quién está en el denominador. Los clientes que compraron al menos una vez. Los ~230 registrados que nunca pidieron son un problema de adquisición, no de retención. Los 18 registros de clientes duplicados no tienen pedidos, así que quedan fuera por sí solos.
3. Cuánto tiempo ha tenido cada cliente para volver. Esta es la decisión que la mayoría de los primeros intentos pasa por alto.

Paso 1: cohortes por primer pedido

Una cohorte es un grupo de clientes que empezaron al mismo tiempo. El primer paso es siempre el mismo: reducir orders a una fila por cliente con su primer pedido y su cantidad de pedidos. Todo lo que sigue es un GROUP BY sobre esta tabla pequeña. COUNT(*) FILTER (WHERE n_orders >= 2) cuenta los clientes recurrentes dentro de cada cohorte.

Mira el resultado antes de seguir. La cohorte de 2025 tiene una tasa de recompra mucho más baja, y es tentador informar que la retención se está desplomando. No necesariamente: alguien que compró por primera vez en noviembre de 2025 ha tenido unas semanas para volver a pedir, mientras que un cliente de 2023 ha tenido tres años. Eso es censura por la derecha, y hace que cualquier tasa de recompra de toda la historia sea injusta con las cohortes recientes.

Paso 2: una ventana justa de 90 días

La solución estándar es una ventana fija: «recompra dentro de los 90 días siguientes al primer pedido», contada solo para los clientes que llevan al menos 90 días. Los datos terminan el 2025-12-31, así que los clientes cuyo primer pedido es anterior al 2025-10-01 son elegibles.

Para encontrar el segundo pedido de cada cliente, numera sus pedidos completados con ROW_NUMBER(), ordenados por order_date y luego por order_id para que los empates se resuelvan siempre igual. Un MAX condicional pivota rn = 1 y rn = 2 en dos columnas. Un comprador de una sola vez tiene NULL en second_order, y NULL <= cualquier cosa no es verdadero, así que correctamente no pasa la prueba sin ningún tratamiento especial.

Compara tiempos con first_order + INTERVAL 90 DAY en lugar de date_diff('day', ...). date_diff cuenta límites de días calendario, mientras que la aritmética de intervalos mide exactamente 90 × 24 horas. Ambas son defendibles; usar el intervalo hace que las versiones de SQL y pandas coincidan hasta el último cliente.

Paso 3: consumidores frente a empresas

Une la tabla por cliente con customers para obtener el segmento. El JOIN es uno a uno, porque ambos lados tienen una fila por cliente, así que nada se cuenta doble. Las cuentas de empresa son pocas, pero piden con mucha más frecuencia, y por eso la junta debería verlas por separado: una tasa de recompra combinada describe sobre todo a los consumidores.

SQL y pandas lado a lado

Idea	SQL	pandas
una fila por cliente	GROUP BY customer_id en una CTE	groupby("customer_id").agg(...)
n-ésimo pedido	ROW_NUMBER() OVER (PARTITION BY ...)	sort_values + groupby().cumcount()
más 90 días	+ INTERVAL 90 DAY	+ pd.Timedelta(days=90)
año de una fecha	year(first_order)	.dt.year

Errores comunes

• Contar pedidos no completados. Un primer pedido cancelado pondría a un cliente en la cohorte equivocada.
• Unir primero customers con orders y luego contar. COUNT(*) sobre ese JOIN cuenta pedidos, no clientes; agrega primero a una fila por cliente.
• Comparar cohortes con distinta exposición. Informa la tasa de 90 días junto a la tasa de toda la historia, y explica por qué.