lección 3

Día 2 (mañana) — El cruce para Jorge

Escribes tu primera query SQL de segmentación real, descubres que 6.782 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-team
2
3Elena Torres  9:31 AM
4Standup:
5- Yo: revisando el modelo de dbt para el Q4, PR abierta
6- 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 Jorge
9¿Algo bloqueado?
10
11Raúl Vega  9:38 AM
12lo del inventario espero hoy, me falta un edge case con los productos
13descatalogados
14
15Tú  9:42 AM
16Nada bloqueado. Voy con el cruce para Jorge y la limpieza de los
17datos 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.sql
2-- Cruce para Jorge: segmentación de clientes de la campaña de verano
3
4-- Paso 1: Contar pedidos recientes por cliente
5WITH pedidos_recientes AS (
6    SELECT
7        cliente_id,
8        COUNT(*) AS pedidos_3m
9    FROM pedidos
10    WHERE fecha_pedido >= '2024-06-16'  -- últimos 3 meses desde sept 2024
11      AND estado NOT IN ('cancelado')
12    GROUP BY cliente_id
13),
14
15-- Paso 2: Cruzar con los clientes de la campaña y la tabla maestra
16-- LEFT JOIN: queremos TODOS los de la campaña, tengan pedidos o no
17-- JOIN con clientes (maestra) porque nombre/email/fecha_registro NO están
18-- en campana_verano_clientes.csv (ahí solo está el cliente_id de la campaña)
19segmentacion AS (
20    SELECT
21        c.cliente_id,
22        m.nombre,
23        m.email,
24        m.fecha_registro,
25        COALESCE(p.pedidos_3m, 0) AS pedidos_ultimos_3m,
26        CASE
27            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 segmento
31    FROM campana_verano_clientes c
32    LEFT JOIN clientes m ON c.cliente_id = m.cliente_id
33    LEFT JOIN pedidos_recientes p ON c.cliente_id = p.cliente_id
34)
35
36-- Paso 3: Resultado ordenado por actividad
37SELECT * FROM segmentacion
38ORDER 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.

El resultado del cruce: tres segmentos y un hallazgo que hay que consultar con Jorge

### 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.782 clientes sin pedidos son un dato relevante para negocio.

1Tú  11:05 AM
2Jorge, te dejo el CSV en el canal #mkt-data.
3Un dato: hay 6.782 clientes de la campaña que no tienen ningún
4pedido en los últimos 3 meses. ¿Es normal? ¿Pueden ser cuentas
5nuevas que se registraron pero no compraron?
6
7Jorge Ruiz  11:12 AM
8Buena observación, y me temo que no es lo que los dos pensábamos.
9Yo daba por hecho que serían los captados en la campaña de junio.
10Mírales la fecha de registro antes de darlo por bueno: si la mayoría
11son de junio, es captación que no convirtió y se arregla con un
12remarketing. Si son clientes de hace años que han dejado de comprar,
13es otra cosa y bastante peor.
14¿Me puedes añadir la fecha de registro al CSV?
15Así distingo "captados pre-campaña" de "ya eran clientes".
16
17Tú  11:15 AM
18Claro, te lo actualizo en 20 min.

Jorge no lo da por bueno de memoria — pide el dato que lo decide

Fíjate en lo que pasó: comunicaste un dato anómalo, y en lugar de confirmártelo de memoria, Jorge te pidió el dato que lo decide. Eso es exactamente lo que hay que hacer con una corazonada, la tuya o la suya. Actualizas la query añadiendo m.fecha_registro al SELECT, reexportas — y el resultado desmiente a los dos: de los 6.782, solo 349 se registraron a partir de junio. Los otros 6.433 son clientes de siempre que han dejado de comprar. La campaña no falló captando: la empresa está perdiendo clientes antiguos, que es un problema mucho más caro.

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 necesita
2import pandas as pd
3from datetime import datetime, timedelta
4
5# Cargar datos
6clientes_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')
9
10# Convertir fechas: pedidos.csv trae ISO con hora y un 4% en DD/MM/YYYY.
11# OJO con el atajo format='mixed', dayfirst=True: con dayfirst=True Pandas
12# lee '2024-06-03' como año-día-mes y te manda 16.834 pedidos a otro mes.
13# Dos pasadas: primero ISO, y lo que quede, con el día delante.
14fechas = pd.to_datetime(pedidos['fecha_pedido'], format='ISO8601',
15                        errors='coerce')
16resto = fechas.isna()
17fechas[resto] = pd.to_datetime(pedidos.loc[resto, 'fecha_pedido'],
18                               dayfirst=True, errors='coerce')
19pedidos['fecha_pedido'] = fechas
20
21# Paso 1: Filtrar pedidos últimos 3 meses (desde 2024-06-16)
22fecha_corte = pd.Timestamp('2024-06-16')
23pedidos_recientes = pedidos[
24    (pedidos['fecha_pedido'] >= fecha_corte) &
25    (pedidos['estado'] != 'cancelado')
26]
27
28# Paso 2: Contar pedidos por cliente
29conteo = (
30    pedidos_recientes
31    .groupby('cliente_id')
32    .size()
33    .reset_index(name='pedidos_3m')
34)
35
36# Paso 3: LEFT JOIN con clientes de la campaña
37resultado = clientes_campana.merge(
38    conteo, on='cliente_id', how='left'
39)
40resultado['pedidos_3m'] = resultado['pedidos_3m'].fillna(0).astype(int)
41
42# Paso 4: Clasificar en segmentos
43def clasificar(pedidos):
44    if pedidos > 6:
45        return 'premium'
46    elif pedidos >= 2:
47        return 'regular'
48    else:
49        return 'nuevo'
50
51resultado['segmento_calculado'] = resultado['pedidos_3m'].apply(clasificar)
52
53# Paso 5: Enriquecer con datos de la tabla maestra (nombre, fecha_registro)
54# OJO: campana_verano_clientes.csv NO trae nombre ni fecha_registro,
55# solo el cliente_id. Hay que cruzarlo con clientes.csv.
56# Y OJO otra vez: clientes.csv tiene 301 cliente_id repetidos. Si cruzas
57# sin deduplicar, el LEFT JOIN devuelve 15.552 filas en vez de 15.247 —
58# cada id repetido multiplica su fila. Una fila por cliente y listo.
59maestra = clientes_maestra.drop_duplicates(subset=['cliente_id'])
60resultado = resultado.merge(
61    maestra[['cliente_id', 'nombre', 'fecha_registro']],
62    on='cliente_id', how='left'
63)
64
65# Resumen
66print("Distribución de segmentos:")
67print(resultado['segmento_calculado'].value_counts())
68print(f"\nClientes sin pedidos: {(resultado['pedidos_3m'] == 0).sum()}")
69
70# Exportar para Jorge (con fecha_registro incluida)
71cols_jorge = ['cliente_id', 'nombre', 'fecha_registro',
72              'pedidos_3m', 'segmento_calculado']
73resultado[cols_jorge].to_csv('output/segmentacion_jorge.csv', index=False)
74print("\nArchivo 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.782 clientes de la campaña no tienen pedidos recientes — y que el 95 % de ellos no son captación fallida, sino clientes antiguos que se han ido
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.

Regístrate para guardar tu progreso.