lección 3

Día 2: El pipeline de unificación

Diseña el esquema unified_campaigns, normaliza nombres usando la mapping table de Raúl y construye el pipeline ETL completo.

⏱ 45 min

### Miércoles 8:55 AM — La decisión de diseño

Llegas 5 minutos antes del standup. Iván ya está en su sitio con el café listo. Tiene el doc de discrepancias que escribiste ayer abierto en su monitor. "Leí tu doc anoche. Está bien. Documentas con claridad. Hoy vamos a construir."

En el standup, Carmen pregunta: "¿Cómo vamos con PharmaLife? Necesito que esta noche tengamos la tabla unificada lista, para calcular el ROAS mañana jueves a primera hora. Ana quiere los números antes del comité del viernes." Iván responde: "Hoy diseñamos el esquema y cargamos. Si no hay sorpresas, mañana a primera hora está listo." Carmen asiente: "Bien. Si hay sorpresas, me avisáis antes de las 14:00 para que pueda gestionar con Ana."

Despues del standup, Iván te lleva a la pizarra de la sala CTR (si, otra sala con nombre de métrica). "La pregunta principal es: como diseñamos la tabla destino para que las 3 fuentes quepan en un solo esquema sin perder información? Hay 3 opciones y solo una es correcta."

La decisión fundamental es la granularidad. Iván plantea las opciones: (A) Una fila por día-campaña-fuente — máxima granularidad, permite comparar fuentes entre si. (B) Una fila por día-campaign_group — ya unificado, pierde la referencia a la fuente original. (C) Una fila por semana-campaign_group — nivel de email, pierde el detalle diario de Google y Meta.

Iván dibuja las 3 opciones en la pizarra con sus pros y contras. Lo hace con calma, sin prisa. Es su forma de enseñar: no te da la respuesta directamente, te muestra el razonamiento para que llegues tú solo.

1Iván Romero [09:05 - pizarra]:
2
3Opcion A: día + campana_original + fuente
4  PRO: No pierdes nada. Puedes comparar Google vs Meta por día.
5  CON: Para calcular ROAS unificado necesitas agregar después.
6
7Opcion B: día + campaign_group (unificado)
8  PRO: Ya tienes la vision unificada lista.
9  CON: Pierdes la trazabilidad a la fuente original.
10       Si algo no cuadra, no sabes de donde viene el error.
11
12Opcion C: semana + campaign_group
13  PRO: Nivel de email, todo comparable.
14  CON: Pierdes detalle diario de Google y Meta.
15       Carmen no va a aceptar perder esa granularidad.
16
17Mi recomendación: OPCION A.
18Siempre normaliza al nivel más granular posible.
19Agregar después es trivial. Desagregar es imposible.

Iván explica por que normalizar al nivel más granular — una decisión de diseño clave

Regla de oro de data warehousing: almacena al nivel más granular disponible y agrega cuando lo necesites. Si guardas datos semanales, nunca podrás responder preguntas diarias. Si guardas datos diarios, siempre puedes agrupar a semanal. Iván lo resume así: "Agregar es un SUM(). Desagregar es inventar datos."

### El esquema de unified_campaigns

1-- Tabla destino: unified_campaigns
2-- Granularidad: 1 fila por día + campaña original + fuente
3CREATE TABLE unified_campaigns (
4  id SERIAL PRIMARY KEY,
5  date DATE NOT NULL,
6  source VARCHAR(20) NOT NULL,            -- 'google', 'meta', 'email'
7  campaign_group VARCHAR(100),            -- nombre unificado (de mapping)
8  original_campaign_name VARCHAR(200),    -- nombre original de la plataforma
9  product_category VARCHAR(50),           -- categoría de producto (de mapping)
10  impressions BIGINT DEFAULT 0,
11  clicks INTEGER DEFAULT 0,
12  conversions INTEGER DEFAULT 0,
13  spend_eur NUMERIC(12,2) DEFAULT 0,
14  revenue_eur NUMERIC(12,2) DEFAULT 0,   -- solo email tiene revenue directo
15  is_mapped BOOLEAN DEFAULT FALSE,        -- tiene mapping o es huerfana?
16  loaded_at TIMESTAMP DEFAULT NOW()
17);
18
19-- Indices para queries frecuentes
20CREATE INDEX idx_uc_date_source ON unified_campaigns(date, source);
21CREATE INDEX idx_uc_campaign_group ON unified_campaigns(campaign_group);
22CREATE INDEX idx_uc_unmapped ON unified_campaigns(is_mapped) WHERE is_mapped = FALSE;
23
24-- Constraint: no duplicados (idempotencia)
25CREATE UNIQUE INDEX idx_uc_unique ON unified_campaigns(date, source, original_campaign_name);

Esquema final: cada fila es un día-campaña-fuente, con flag de mapping

Iván señala dos decisiones importantes en el esquema: (1) El campo is_mapped te permite filtrar rápidamente campañas huérfanas sin tener que comprobar NULLs en campaign_group. (2) El UNIQUE INDEX en (date, source, original_campaign_name) garantiza idempotencia — si ejecutas el pipeline dos veces con los mismos datos, falla en vez de duplicar. "En producción quieres ON CONFLICT DO UPDATE, pero para la primera versión es mejor que falle alto para detectar bugs."

Un pipeline idempotente es el que puedes ejecutar dos veces con la misma entrada y deja la base de datos igual que si lo hubieras ejecutado una. Suena obvio y casi nunca se cumple, porque los pipelines fallan a mitad: se cae la red al insertar la fila 300 de 434 y alguien lo relanza. Y ojo, el índice único no hace idempotente al pipeline: lo hace RUIDOSO, que es distinto. Si relanzas, el INSERT falla y tú te enteras. Eso es lo que quieres en la primera versión, porque un fallo visible se arregla y un duplicado silencioso se descubre tres semanas después, cuando el ROAS del cliente sale a la mitad. Las tres formas de resolverlo, de menos a más trabajo: FALLAR (lo de hoy: el índice bloquea el segundo INSERT y alguien decide); BORRAR Y RECARGAR el periodo (DELETE WHERE date BETWEEN … antes de insertar; simple, robusto, lo que hace la mayoría de pipelines diarios); y ON CONFLICT DO UPDATE (lo más elegante, pero exige que la clave de negocio esté bien elegida: si te falta source en el índice, machacas Google con Meta). Las tres dependen de lo mismo: que (date, source, original_campaign_name) identifique de verdad una fila y solo una. Esa elección, y no el SERIAL id, es la decisión de diseño de hoy.

Iván te explica también por qué no usamos un ID auto-generado como clave primaria del negocio: "El SERIAL id es para la base de datos internamente. La clave REAL de negocio es (date, source, original_campaign_name). Eso es lo que hace única a cada fila conceptualmente. El índice único en esa combinación es nuestro guardián contra duplicados."

Además, te muestra un truco que usa en todos sus esquemas: "Fíjate en el índice parcial: CREATE INDEX idx_uc_unmapped ON unified_campaigns(is_mapped) WHERE is_mapped = FALSE. Este índice SOLO indexa las filas que son huérfanas. Con los datos de enero son el 21% en Google y el 17% en Meta, así que el índice sigue siendo pequeño y las queries de detección de huérfanas son instantáneas. Es un patrón común para filtros sobre un subconjunto reducido."

### Normalizar email: de semanal a diario

El problema más interesante del pipeline es email. Los datos vienen semanales pero la tabla destino es diaria. Iván te explica el approach: "Distribuimos las métricas equitativamente entre los 7 días de la semana. Es una simplificacion — en realidad el 60% de las aperturas ocurren en las primeras 48h — pero para un MVP es aceptable."

Raúl te da contexto adicional por Slack:

1#canal-pharmalife [10:15]
2
3Raúl Merino: Oye, sobre lo de distribuir el email a diario.
4En realidad los envios van los martes y jueves (2 envios
5por semana para cada campaña). Los martes el open rate es
6un 15% más alto que los jueves.
7
8Si quieres ser más preciso, podrias distribuir:
9- Martes: 35% del total semanal
10- Miércoles: 25% (aperturas rezagadas del martes)
11- Jueves: 25% (segundo envio)
12- Viernes: 10% (rezagadas del jueves)
13- Resto: 5%
14
15Pero para el MVP, 1/7 por día está bien. Ya refinamos después.
16
17Iván Romero: @Raúl gracias por el dato. @Tu anota esto
18como mejora para v2 en el README. Por ahora 1/7 uniforme.

Contexto de negocio de Raúl: la realidad del email no es uniforme

Repartir 1/7 no es «menos preciso»: es preciso para unas preguntas e inútil para otras. Para el total del mes da igual, porque 1/7 siete veces suma uno. Para comparar canales por semana, también da igual. Pero en cuanto alguien pregunte «¿qué día de la semana convierte mejor?», el reparto uniforme responde «todos igual» — y esa respuesta es falsa, la fabricaste tú al repartir. Raúl sabe que el martes concentra el 35%. La regla: una simplificación es aceptable cuando sabes qué preguntas invalida y lo dejas escrito. Anótalo en el README junto al reparto, no en un comentario perdido dentro de la función. Y cuando llegue la v2 con el reparto real de Raúl, los totales mensuales no se moverán ni un euro: solo se moverán los días.

1import pandas as pd
2from datetime import timedelta
3
4def distribute_weekly_to_daily(df_email: pd.DataFrame) -> pd.DataFrame:
5    """
6    Distribuye métricas semanales en 7 filas diarias.
7    Cada día recibe 1/7 de las métricas.
8    """
9    daily_rows = []
10    for _, row in df_email.iterrows():
11        week_start = pd.to_datetime(row['week_start'])
12        for day_offset in range(7):
13            day = week_start + timedelta(days=day_offset)
14            daily_rows.append({
15                'date': day.strftime('%Y-%m-%d'),
16                'source': 'email',
17                'original_campaign_name': row['campaign_name'],
18                'impressions': 0,  # email no tiene impresiones
19                'clicks': round(row['clicked'] / 7),
20                'conversions': round(row['converted'] / 7),
21                'spend_eur': round(row['total_cost'] / 7, 2),   # coste real del CSV
22                'revenue_eur': round(row['revenue_eur'] / 7, 2),
23            })
24    return pd.DataFrame(daily_rows)
25
26# Ejemplo con 1 fila semanal real:
27# Weekly_Vitamins_Promo, semana del 2024-01-01:
28#   16 conversiones | 1.465,29 EUR de ingresos | 606,14 EUR de coste
29# -> 7 filas diarias: 2 conversiones/día | 209,33 EUR/día | 86,59 EUR/día de coste

Distribución de email semanal a diario: cada día recibe 1/7

Iván te advierte: "El round() puede causar que la suma de los 7 días no cuadre exactamente con el total semanal. Por ejemplo, 16/7 = 2.29, que redondeado a entero da 2. Pero 2*7 = 14, no 16. Para un MVP acepta el error de redondeo. Para producción, asigna el resto al primer día o al último."

Al distribuir el coste real te vas a encontrar con algo llamativo: el email ingresa 48.000 EUR y cuesta 20.000, así que parece el canal más rentable con diferencia. Antes de escribir eso en ningún informe, para. Ese «coste» no es lo que vale enviar un correo (110.690 envíos cuestan unos pocos cientos de euros): son las horas de diseño, copy y gestión que la agencia imputa al canal. Google y Meta, en cambio, reportan gasto de medios. Son dos cosas distintas con el mismo nombre, y compararlas directamente da una conclusión falsa. Por eso el esquema unificado guarda source en cada fila: para que mañana, cuando alguien sume, se pueda ver de dónde viene cada euro y qué mide.

### El pipeline completo en Python

Iván te da la estructura del script principal y te pide que implementes las funciones de transformacion. El pattern es claro: (1) cargar fuente, (2) transformar al formato unificado, (3) enriquecer con mapping, (4) insertar en la tabla destino.

1import pandas as pd
2import logging
3
4logging.basicConfig(level=logging.INFO, format='%(asctime)s [%(levelname)s] %(message)s')
5logger = logging.getLogger('adpulse.unify')
6
7def load_mapping(path: str) -> pd.DataFrame:
8    """Carga la mapping table de Raúl."""
9    mapping = pd.read_csv(path)
10    logger.info(f"Mapping cargado: {len(mapping)} campaign groups")
11    return mapping
12
13def enrich_with_mapping(df: pd.DataFrame, mapping: pd.DataFrame, source: str) -> pd.DataFrame:
14    """Enriquece un DataFrame con campaign_group y product_category del mapping."""
15    # Determinar la columna de join segun la fuente
16    join_cols = {
17        'google': 'google_name',
18        'meta': 'meta_name',
19        'email': 'email_name',
20    }
21    join_col = join_cols[source]
22
23    # LEFT JOIN con mapping
24    merged = df.merge(
25        mapping[['campaign_group', join_col, 'product_category']],
26        left_on='original_campaign_name',
27        right_on=join_col,
28        how='left'
29    )
30
31    df['campaign_group'] = merged['campaign_group']
32    df['product_category'] = merged['product_category']
33    df['is_mapped'] = df['campaign_group'].notna()
34
35    # Loguear huerfanas
36    orphans = df[~df['is_mapped']]['original_campaign_name'].unique()
37    if len(orphans) > 0:
38        logger.warning(f"[{source}] {len(orphans)} campañas sin mapping: {list(orphans)}")
39
40    return df
41
42def run_pipeline(google_path: str, meta_path: str, email_path: str, mapping_path: str):
43    """Pipeline principal: ingesta + normalizacion + unificacion."""
44    mapping = load_mapping(mapping_path)
45
46    # 1. Google Ads
47    df_google = transform_google(pd.read_csv(google_path))
48    df_google = enrich_with_mapping(df_google, mapping, 'google')
49    logger.info(f"Google: {len(df_google)} filas, {df_google['is_mapped'].sum()} mapeadas")
50
51    # 2. Meta Ads
52    df_meta = transform_meta(load_meta_json(meta_path))
53    df_meta = enrich_with_mapping(df_meta, mapping, 'meta')
54    logger.info(f"Meta: {len(df_meta)} filas, {df_meta['is_mapped'].sum()} mapeadas")
55
56    # 3. Email
57    df_email = transform_email(pd.read_csv(email_path))
58    df_email = enrich_with_mapping(df_email, mapping, 'email')
59    logger.info(f"Email: {len(df_email)} filas, {df_email['is_mapped'].sum()} mapeadas")
60
61    # 4. Concatenar y devolver
62    unified = pd.concat([df_google, df_meta, df_email], ignore_index=True)
63    logger.info(f"TOTAL UNIFICADO: {len(unified)} filas")
64    logger.info(f"  Mapeadas: {unified['is_mapped'].sum()} ({unified['is_mapped'].mean()*100:.1f}%)")
65    logger.info(f"  Huerfanas: {(~unified['is_mapped']).sum()}")
66
67    return unified

Pipeline completo: patron ETL con logging y detección de huerfanas

### Code review de Iván

A las 16:00 subes tu PR. Iván lo revisa en 30 minutos y te deja cuatro comentarios. Pero antes de leerlos, te fijas en algo: Iván ha aprobado el PR. Eso significa que tus errores son MENORES — el approach general es correcto. Eso te da confianza.

1PR #247: feat(pharmalife): pipeline de unificación v1
2
3Files changed: 8  |  Additions: 342  |  Deletions: 0
4
5Iván Romero reviewed -- APPROVED with comments
6
7Comment 1 (transform_meta.py, line 34):
8> Estas usando .iterrows() para iterar el DataFrame.
9> Para 31 días x 12 campañas (372 filas) no importa, pero es un
10> mal hábito. Usa vectorizacion de Pandas cuando puedas. Si no
11> puedes, al menos documenta por que iterrows es necesario.
12> En este caso SI puedes vectorizar con .apply() o .explode().
13> Aprobado pero deja un TODO para refactorizarlo.
14
15Comment 2 (enrich_with_mapping.py, line 18):
16> Bien el LEFT JOIN. Pregunta: que pasa si la mapping table tiene
17> un google_name duplicado? (por ejemplo si Raúl comete un error
18> y mapea 2 campaign_groups al mismo nombre de Google). Tu código
19> crearia filas duplicadas silenciosamente. Anade un check de
20> unicidad ANTES del merge. Algo tipo:
21>   assert mapping['google_name'].dropna().duplicated().sum() == 0
22> Esto es OBLIGATORIO antes de merge.
23
24Comment 3 (run_pipeline.py, line 52):
25> Me gusta que loguees las huerfanas. Pero el log se pierde si
26> nadie lo mira. Para la v2, quiero que las huerfanas se guarden
27> en una tabla separada (orphan_campaigns) y que el DAG envie
28> un Slack automático a Raúl. Por ahora está bien como MVP.
29
30Comment 4 (general):
31> Buen naming en las funciones: transform_google, transform_meta,
32> enrich_with_mapping. Sigue así. El único que cambiaria es
33> "run_pipeline" -> "run_pharmalife_weekly" (más específico).
34
35OVERALL: Aprobado con 1 cambio obligatorio (check de unicidad)
36         y 3 sugerencias para v2. Merge cuando arregles el #2.

Code review real: Iván aprueba pero señala mejoras con ejemplos concretos

Arreglas el comentario 2 (el check de unicidad) en 5 minutos, pusheas, e Iván da el merge. Tu primer PR en AdPulse está en main. Es un momento pequeño pero satisfactorio: tu código ya es parte del sistema de producción.

La iteracion típica en un equipo profesional es: (1) subes PR, (2) reviewer señala problemas, (3) corriges los obligatorios y anotas los opcionales como TODO, (4) merge. No te frustres con los comentarios de code review — son la forma en que aprendes más rápido. Cada correccion de Iván te ahorra un bug futuro en producción.

### Mensaje de Raúl a las 17:00

1#canal-pharmalife [17:02]
2
3Raúl Merino: Hey! Iván me dice que encontraste 5 campañas sin
4mapping (3 Google, 2 Meta). Me las pasas?
5
6Tú: Sí, aquí van:
7  Google:
8    - PL_Search_Supplements_Generic (8.183 EUR de gasto)
9    - PL_Search_Collagen_Competitor (5.088 EUR)
10    - PL_Video_Omega3_Awareness (1.269 EUR)
11  Meta:
12    - pharmalife_protein_awareness_fb (5.156 EUR)
13    - pharmalife_collagen_broad_ig (4.737 EUR)
14
15Raúl Merino: Uf, 24.432 EUR sin mapear. Eso es un 13% del
16gasto de Google y un 14% del de Meta.
17
18Las de "Collagen" van las dos al mismo campaign_group,
19"Collagen Awareness". Las otras tres las creo mañana.
20
21Tú: Perfecto. Mientras tanto salen en el informe como
22"sin mapear" para que Ana vea que ese gasto existe.
23
24Raúl Merino: Mejor así. Actualizaré el sheet mañana
25a primera hora.

Coordinación con Raúl: el data engineer y el analista trabajan juntos

Regístrate para guardar tu progreso.