lección 3
Día 2 (mañana) — El cruce para Jorge
Escribes tu primera query SQL de segmentación real, descubres que 6.852 clientes no tienen pedidos, y comunicas el hallazgo a un stakeholder.
⏱ 45 min
### 9:30 AM — Standup async
Hoy es martes. El standup es async por Slack — Elena prefiere no hacer videollamada cuando todo el mundo tiene claro en qué está:
1#data-team23Elena Torres 9:31 AM4Standup:5- Yo: revisando el modelo de dbt para el Q4, PR abierta6- Raúl: sigue con el pipeline de inventario (¿para cuándo?)7- tú: ayer subió los datos de campaña a raw. Hoy: limpieza +8 lo de Jorge9¿Algo bloqueado?1011Raúl Vega 9:38 AM12lo del inventario espero hoy, me falta un edge case con los productos13descatalogados1415Tú 9:42 AM16Nada bloqueado. Voy con el cruce para Jorge y la limpieza de los17datos de campaña.
Standup async — nadie pierde tiempo si no hay bloqueos
### 10:00 AM — Planteando el cruce de segmentación
Jorge quiere saber: de los 15K clientes de la campaña, ¿cuántos son premium (>6 pedidos en 3 meses), regulares (2-6 pedidos) o nuevos (<2 pedidos)? Para responder vamos a usar tres datasets: campana_verano_clientes.csv (la lista de la campaña, con el cliente_id), pedidos.csv (el historial de pedidos del warehouse) y clientes.csv (la tabla maestra del CRM, de donde sacaremos el nombre y la fecha_registro, porque el archivo de campaña solo trae IDs y engagement). Esto es un par de LEFT JOIN con una agregación.
Piénsalo como un problema de la vida real: tienes dos listas. La primera es "personas invitadas a la fiesta" (clientes de la campaña). La segunda es "registro de compras del supermercado en los últimos 3 meses" (pedidos). Quieres saber, para cada invitado, cuántas veces ha comprado. Algunos invitados puede que NUNCA hayan comprado — y esos son los interesantes porque representan captación fallida.
La query se construye en 3 pasos lógicos: (1) contar pedidos por cliente en los últimos 3 meses, (2) cruzar esa cuenta con la lista de clientes de la campaña, (3) clasificar en segmentos según el número de pedidos. Vamos a usar CTEs (Common Table Expressions) para que cada paso sea legible:
1-- segmentacion_campana.sql2-- Cruce para Jorge: segmentación de clientes de la campaña de verano34-- Paso 1: Contar pedidos recientes por cliente5WITH pedidos_recientes AS (6 SELECT7 cliente_id,8 COUNT(*) AS pedidos_3m9 FROM pedidos10 WHERE fecha_pedido >= '2024-06-16' -- últimos 3 meses desde sept 202411 AND estado NOT IN ('cancelado')12 GROUP BY cliente_id13),1415-- Paso 2: Cruzar con los clientes de la campaña y la tabla maestra16-- LEFT JOIN: queremos TODOS los de la campaña, tengan pedidos o no17-- JOIN con clientes (maestra) porque nombre/email/fecha_registro NO están18-- en campana_verano_clientes.csv (ahí solo está el cliente_id de la campaña)19segmentacion AS (20 SELECT21 c.cliente_id,22 m.nombre,23 m.email,24 m.fecha_registro,25 COALESCE(p.pedidos_3m, 0) AS pedidos_ultimos_3m,26 CASE27 WHEN COALESCE(p.pedidos_3m, 0) > 6 THEN 'premium'28 WHEN COALESCE(p.pedidos_3m, 0) >= 2 THEN 'regular'29 ELSE 'nuevo'30 END AS segmento31 FROM campana_verano_clientes c32 LEFT JOIN clientes m ON c.cliente_id = m.cliente_id33 LEFT JOIN pedidos_recientes p ON c.cliente_id = p.cliente_id34)3536-- Paso 3: Resultado ordenado por actividad37SELECT * FROM segmentacion38ORDER BY pedidos_ultimos_3m DESC;
La query completa en 3 CTEs — clara, legible, explicable en una servilleta
### Por qué LEFT JOIN y no INNER JOIN
Este es un punto donde muchos juniors se equivocan. Si usas INNER JOIN, solo obtienes los clientes que TIENEN pedidos. Pero Jorge quiere saber TAMBIÉN cuántos NO tienen pedidos — esos son los "nuevos" que captó la campaña pero no convirtieron. Con LEFT JOIN, todos los clientes de la campaña aparecen en el resultado; los que no tienen pedidos muestran NULL en la columna de conteo, que convertimos a 0 con COALESCE.
### 11:00 AM — Comunicar el hallazgo a Jorge
Tienes el resultado. Pero antes de mandarlo sin más, haces algo que diferencia a un ingeniero bueno de uno mediocre: comunicas el HALLAZGO, no solo el archivo. Los 6.852 clientes sin pedidos son un dato relevante para negocio.
1Tú 11:05 AM2Jorge, te dejo el CSV en el canal #mkt-data.3Un dato: hay 6.852 clientes de la campaña que no tienen ningún4pedido en los últimos 3 meses. ¿Es normal? ¿Pueden ser cuentas5nuevas que se registraron pero no compraron?67Jorge Ruiz 11:12 AM8Buena observación. Sí, son registros que vinieron por la campaña9de captación de junio. Se registraron pero no convirtieron. De hecho,10eso es justo lo que quiero analizar: ¿cuántos de esos "captados en11junio" compraron durante la promo de verano?12¿Me puedes añadir la fecha de registro al CSV?13Así distingo "captados pre-campaña" de "ya eran clientes".1415Tú 11:15 AM16Claro, te lo actualizo en 20 min.
Jorge valida tu hallazgo Y pide un campo adicional — la iteración es normal
Fíjate en lo que pasó: comunicaste un dato anómalo, Jorge te confirmó que era esperado (no es un error), Y además te pidió un campo extra. Esto es iteración normal — nunca aciertas el 100% de los requisitos a la primera. Actualizas la query añadiendo c.fecha_registro al SELECT y reexportas.
Consejo de senior: cuando encuentres un dato que "no cuadra", SIEMPRE comunícalo al stakeholder antes de asumir que es un error y "corregirlo". He visto juniors que eliminaron filas con cliente_id sin pedidos pensando que eran errores — y resultó que esos eran EXACTAMENTE los clientes que el negocio quería analizar. Los datos raros no son bugs hasta que alguien del negocio te confirma que lo son.
### Ejecutar la query con Python (Pandas + SQL)
Como estamos trabajando en local con CSVs descargados, vamos a usar Pandas para simular el cruce SQL. En un entorno real harías esta query directamente contra PostgreSQL con sqlalchemy o psycopg2. Pero la lógica es exactamente la misma:
1# cruce_segmentacion.py — Lo que Jorge necesita2import pandas as pd3from datetime import datetime, timedelta45# Cargar datos6clientes_campana = pd.read_csv('datos/campana_verano_clientes.csv')7clientes_maestra = pd.read_csv('datos/clientes.csv') # nombre, fecha_registro, email...8pedidos = pd.read_csv('datos/pedidos.csv')910# Convertir fechas (pedidos puede tener formatos mixtos)11pedidos['fecha_pedido'] = pd.to_datetime(12 pedidos['fecha_pedido'], format='mixed', dayfirst=True13)1415# Paso 1: Filtrar pedidos últimos 3 meses (desde 2024-06-16)16fecha_corte = pd.Timestamp('2024-06-16')17pedidos_recientes = pedidos[18 (pedidos['fecha_pedido'] >= fecha_corte) &19 (pedidos['estado'] != 'cancelado')20]2122# Paso 2: Contar pedidos por cliente23conteo = (24 pedidos_recientes25 .groupby('cliente_id')26 .size()27 .reset_index(name='pedidos_3m')28)2930# Paso 3: LEFT JOIN con clientes de la campaña31resultado = clientes_campana.merge(32 conteo, on='cliente_id', how='left'33)34resultado['pedidos_3m'] = resultado['pedidos_3m'].fillna(0).astype(int)3536# Paso 4: Clasificar en segmentos37def clasificar(pedidos):38 if pedidos > 6:39 return 'premium'40 elif pedidos >= 2:41 return 'regular'42 else:43 return 'nuevo'4445resultado['segmento_calculado'] = resultado['pedidos_3m'].apply(clasificar)4647# Paso 5: Enriquecer con datos de la tabla maestra (nombre, fecha_registro)48# OJO: campana_verano_clientes.csv NO trae nombre ni fecha_registro,49# solo el cliente_id. Hay que cruzarlo con clientes.csv.50resultado = resultado.merge(51 clientes_maestra[['cliente_id', 'nombre', 'fecha_registro']],52 on='cliente_id', how='left'53)5455# Resumen56print("Distribución de segmentos:")57print(resultado['segmento_calculado'].value_counts())58print(f"\nClientes sin pedidos: {(resultado['pedidos_3m'] == 0).sum()}")5960# Exportar para Jorge (con fecha_registro incluida)61cols_jorge = ['cliente_id', 'nombre', 'fecha_registro',62 'segmento_calculado', 'pedidos_3m']63resultado[cols_jorge].to_csv('output/segmentacion_jorge.csv', index=False)64print("\n✅ Archivo exportado: output/segmentacion_jorge.csv")
El mismo cruce SQL pero con Pandas — misma lógica, mismo resultado
### Reflexión: SQL vs Pandas para cruces
¿Por qué mostramos tanto la query SQL como el código Pandas? Porque en la vida real usarás AMBOS. Cuando los datos están en una base de datos (PostgreSQL, Redshift, BigQuery), el cruce lo haces en SQL — es más rápido, el motor optimiza por ti. Cuando los datos son archivos locales (CSVs que te manda María), usas Pandas. La lógica es idéntica: LEFT JOIN + GROUP BY + CASE. Solo cambia la sintaxis.
Cuidado con hacer cruces grandes en Pandas cuando podrías hacerlos en SQL. Un merge de dos DataFrames de 1M filas puede consumir toda tu RAM y tardar minutos. La misma query en PostgreSQL tarda segundos porque usa índices y un optimizador sofisticado. Regla general: si los datos ya están en una base de datos, hazlo en SQL. Si son archivos locales pequeños (<100K filas), Pandas está bien.
### El COALESCE: tu mejor amigo con LEFT JOINs
Cuando haces un LEFT JOIN, las filas de la tabla izquierda que NO tienen match en la derecha aparecen con NULL en todas las columnas del lado derecho. Si estás contando pedidos, esos NULLs significan "cero pedidos". COALESCE(valor, 0) convierte NULL a 0. En Pandas el equivalente es .fillna(0). Parece un detalle menor, pero si no lo haces, tu CASE WHEN no clasificará bien esos registros (NULL > 6 es NULL, no False).
### Resumen de la mañana
- Escribiste una query SQL de segmentación real con CTEs y LEFT JOIN
- Descubriste que 6.852 clientes no tienen pedidos — dato relevante para negocio
- Comunicaste el hallazgo a Jorge ANTES de asumir que era un error
- Jorge iteró pidiendo un campo adicional — y respondiste en 20 minutos
- Ejecutaste la misma lógica en Pandas para trabajar con archivos locales
Consejo de senior: una buena query SQL se lee como una historia. Las CTEs son los capítulos. Si tu query tiene 100 líneas sin una sola CTE, probablemente es ilegible. Divide, nombra cada paso, y cualquier persona del equipo podrá entenderla en 30 segundos. "¿Qué hace esta query?" → "Primero cuenta pedidos recientes, luego cruza con la campaña, luego clasifica en segmentos." Así de claro debe ser.
## ejercicios
Escribir la query de segmentación para Jorge
Usando campana_verano_clientes.csv (lista de la campaña), clientes.csv (tabla maestra, de donde sacas nombre y fecha_registro) y pedidos.csv, escribe una query SQL (o su equivalente en Pandas) que clasifique a cada cliente de la campaña en: premium (>6 pedidos en 3 meses), regular (2-6 pedidos), o nuevo (0-1 pedidos). Fecha de corte: 2024-06-16. Excluye pedidos cancelados. Usa LEFT JOIN para incluir clientes sin pedidos. Recuerda: nombre y fecha_registro NO están en el archivo de campaña, están en clientes.csv.
Resumen ejecutivo de segmentos para Jorge
Después del cruce, Jorge quiere un resumen rápido: ¿cuántos clientes hay en cada segmento y qué porcentaje representan? Usando Pandas sobre el resultado de la segmentación, genera una tabla con: segmento, cantidad, porcentaje. Ordena de mayor a menor.
Investigar los 6.852 clientes sin pedidos
Jorge te preguntó si los 6.852 clientes sin pedidos son captación reciente. Escribe código que: (1) filtre los clientes con 0 pedidos del resultado de la segmentación, (2) analice su fecha_registro para saber cuántos se registraron en los últimos 3 meses (post junio 2024), y (3) calcule el % de "nuevos registros" vs "registros antiguos que dejaron de comprar".
Calcular la tasa de conversión de la campaña
Bonus que Jorge agradecerá: usando campana_verano_clientes.csv (tiene la columna "compro", un booleano que indica si el cliente compró durante la campaña), calcula la tasa de conversión global y por canal de contacto. OJO con dos trampas de datos reales: (1) compro es booleano (True/False), no el texto "true"; (2) canal_contacto viene sucio, con variantes como "email"/"Email" o "push"/"push_notification" que hay que normalizar con LOWER y unificar. ¿Qué canal convirtió mejor?
Regístrate para guardar tu progreso.
## comentarios
Reporta erratas, ayuda a otros o comparte tu opinión. Sé constructivo.
Inicia sesión para comentar y responder.
cargando comentarios...