Nivel 2 · Retención de clientes en pandas

La misma pregunta, las mismas definiciones

La revisión de retención de Catherine en pandas, con las mismas definiciones que el nivel de SQL de este caso: solo pedidos completados, cohortes por el año del primer pedido completado, «recurrente» significa dos o más pedidos completados, y la tasa de 90 días solo cuenta a los clientes que tuvieron 90 días para volver.

Paso 1: una fila por cliente

Una fila por cliente es un groupby sin as_index=False, así que customer_id queda como índice:

completed = orders[orders["status"] == "completed"]
per_customer = completed.groupby("customer_id").agg(
    first_order=("order_date", "min"), n_orders=("order_id", "count")
)

A partir de aquí, cada pregunta es un segundo groupby sobre esta tabla pequeña. Una columna booleana is_repeat hace legible el conteo: "size" cuenta a todos los clientes de la cohorte, "sum" cuenta los valores True. .dt.year da un año entero, igual que year() en SQL.

Mira la cohorte de 2025 antes de seguir: su tasa de recompra de toda la historia es mucho más baja. Eso es censura por la derecha, no necesariamente un desplome. Alguien que compró por primera vez en noviembre de 2025 ha tenido unas semanas para volver, un cliente de 2023 tres años, así que cualquier tasa de toda la historia es injusta con las cohortes recientes. El paso 2 lo arregla con una ventana fija de 90 días.

Paso 2: el segundo pedido con cumcount

Para encontrar el segundo pedido de cada cliente, numera sus pedidos. groupby(...).cumcount() numera las filas dentro de cada grupo como 0, 1, 2, … en el orden en que están en ese momento, así que primero ordena y luego numera:

completed = orders[orders["status"] == "completed"].sort_values(["customer_id", "order_date", "order_id"])
completed["n"] = completed.groupby("customer_id").cumcount()   # 0, 1, 2, ...

cumcount empieza en 0, así que el primer pedido es n == 0 y el segundo es n == 1. Ordenar también por order_id además de la fecha resuelve los empates exactamente como lo hace la versión en SQL.

pivot(index="customer_id", columns="n", values="order_date") convierte las dos filas por cliente en dos columnas. Es pivot_table sin agregación, para cuando cada par índice/columna tiene un solo valor. Las columnas nuevas se llaman como los valores de n, 0 y 1, así que .rename(columns={0: "first_order", 1: "second_order"}) les da nombres legibles (el nombre viejo a la izquierda, el nuevo a la derecha). Los compradores de una sola vez reciben NaT (marca de tiempo faltante) como segundo pedido. Las comparaciones con NaT devuelven False, así que no pasan la prueba de 90 días sin código adicional.

pd.Timedelta(days=90) es una duración, exactamente 90 × 24 horas, y sumarla a un datetime lo adelanta: pd.Timestamp("2025-01-10 14:00") + pd.Timedelta(days=90) es 2025-04-10 14:00. Es la forma en pandas del INTERVAL 90 DAY de SQL. Usar (second - first).dt.days <= 90 también sería razonable, pero puede diferir en un día del intervalo de SQL cerca de la medianoche; usar la misma aritmética en ambos es lo que hace que los números coincidan.

Paso 3: merge para el segmento

customers tiene una fila por cliente y per_customer también, así que validate="one_to_one" debería pasar, y pasa. Si los registros de clientes duplicados (ids 2001–2018) tuvieran pedidos, aquí es donde aparecerían; no los tienen, pero la comprobación no cuesta nada.

("n_orders", "mean") da los pedidos por cliente, igual que el AVG(n_orders) de SQL. Selecciona las columnas en el orden que lista el ejercicio antes de devolver el resultado.

SQL y pandas lado a lado

Idea	SQL	pandas
clientes de una cohorte	COUNT(*)	("is_repeat", "size")
recurrentes	COUNT(*) FILTER (WHERE n_orders >= 2)	("is_repeat", "sum")
n-ésimo pedido	ROW_NUMBER()	sort_values + cumcount()
filas a columnas	MAX(CASE WHEN rn = 2 ...)	pivot(columns="n")
segundo pedido ausente	NULL	NaT

Errores comunes

• Olvidar ordenar antes de cumcount. Numera las filas en el orden en que están, que después de un filtro o un merge no está garantizado que sea por fecha.
• count frente a size. "count" omite los valores faltantes, "size" no. En una columna sin valores faltantes coinciden; sabe cuál quieres decir.
• Elegibilidad sobre la fecha equivocada. Filtra por el primer pedido, no por la fecha de registro: los clientes se registran antes de comprar, a veces meses antes.