Día 4 (tarde) — La tabla gold para María

1-- ================================================================
2-- TABLA GOLD: campaign_performance
3-- Granularidad: campaña + día
4-- Propósito: métricas diarias por campaña para dashboards y reportes
5-- ================================================================
6
7CREATE TABLE IF NOT EXISTS gold.campaign_performance AS
8
9WITH ventas_limpias AS (
10    -- Partimos de la tabla silver (ya limpia y validada)
11    SELECT
12        campana,
13        fecha_compra::date AS fecha,
14        cliente_id,
15        producto_id,
16        cantidad,
17        precio,
18        descuento_pct,
19        es_anonimo
20    FROM silver.campaign_ventas
21    WHERE cantidad > 0  -- Por si acaso (ya filtrado en silver, pero defense in depth)
22),
23
24metricas_diarias AS (
25    SELECT
26        campana,
27        fecha,
28
29        -- Revenue
30        SUM(precio * cantidad) AS revenue_bruto,
31        SUM(precio * cantidad * (1 - COALESCE(descuento_pct, 0) / 100.0)) AS revenue_neto,
32
33        -- Volumen
34        COUNT(*) AS num_transacciones,
35        SUM(cantidad) AS unidades_vendidas,
36
37        -- Clientes
38        COUNT(DISTINCT CASE WHEN NOT es_anonimo THEN cliente_id END) AS clientes_unicos,
39        SUM(CASE WHEN es_anonimo THEN 1 ELSE 0 END) AS ventas_anonimas,
40
41        -- Productos
42        COUNT(DISTINCT producto_id) AS productos_distintos,
43
44        -- Ticket medio (revenue neto / transacciones identificadas)
45        SUM(precio * cantidad * (1 - COALESCE(descuento_pct, 0) / 100.0))
46            / NULLIF(COUNT(*), 0) AS ticket_medio
47
48    FROM ventas_limpias
49    GROUP BY campana, fecha
50)
51
52SELECT
53    campana,
54    fecha,
55    revenue_bruto,
56    revenue_neto,
57    num_transacciones,
58    unidades_vendidas,
59    clientes_unicos,
60    ventas_anonimas,
61    productos_distintos,
62    ticket_medio,
63    -- Métricas acumuladas (running totals)
64    SUM(revenue_neto) OVER (
65        PARTITION BY campana ORDER BY fecha
66    ) AS revenue_neto_acumulado,
67    SUM(num_transacciones) OVER (
68        PARTITION BY campana ORDER BY fecha
69    ) AS transacciones_acumuladas
70
71FROM metricas_diarias
72ORDER BY campana, fecha;

La tabla gold: métricas diarias + running totals para gráficos de evolución

1-- ================================================================
2-- TABLA GOLD AUXILIAR: campaign_top_products
3-- Granularidad: campaña + producto (rankeado por revenue)
4-- ================================================================
5
6CREATE TABLE IF NOT EXISTS gold.campaign_top_products AS
7
8WITH producto_stats AS (
9    SELECT
10        v.campana,
11        v.producto_id,
12        p.nombre AS producto_nombre,
13        p.marca,
14        cat.nombre AS categoria,
15        SUM(v.precio * v.cantidad) AS revenue_bruto,
16        SUM(v.cantidad) AS unidades_vendidas,
17        COUNT(DISTINCT v.cliente_id) AS compradores_unicos,
18        COUNT(*) AS num_transacciones
19    FROM silver.campaign_ventas v
20    LEFT JOIN silver.productos p ON v.producto_id = p.producto_id
21    LEFT JOIN silver.categorias cat ON p.categoria_id = cat.categoria_id
22    WHERE v.cantidad > 0
23    GROUP BY v.campana, v.producto_id, p.nombre, p.marca, cat.nombre
24),
25
26rankeados AS (
27    SELECT
28        *,
29        ROW_NUMBER() OVER (
30            PARTITION BY campana
31            ORDER BY revenue_bruto DESC
32        ) AS ranking
33    FROM producto_stats
34)
35
36SELECT * FROM rankeados
37WHERE ranking <= 50  -- Top 50 por campaña (María pidió 10 pero guardamos más)
38ORDER BY campana, ranking;

Top productos por revenue — ROW_NUMBER para el ranking

1# pipelines/campaign_ingestion/gold.py
2"""Construcción de la tabla gold/campaign_performance."""
3import logging
4from pathlib import Path
5
6import pandas as pd
7
8logger = logging.getLogger(__name__)
9
10
11def build_campaign_performance(silver_path: Path) -> pd.DataFrame:
12    """Construye la tabla gold con métricas diarias por campaña."""
13    # Leer datos de silver
14    df = pd.read_parquet(silver_path / "campaign_ventas.parquet")
15    logger.info(f"Leídas {len(df):,} filas de silver")
16
17    # Granularidad: campaña + día
18    df["fecha"] = df["fecha_compra"].dt.date
19
20    gold = df.groupby(["campana", "fecha"]).agg(
21        revenue_bruto=("precio", lambda x: (x * df.loc[x.index, "cantidad"]).sum()),
22        num_transacciones=("venta_id", "count"),
23        unidades_vendidas=("cantidad", "sum"),
24        clientes_unicos=("cliente_id", "nunique"),
25        productos_distintos=("producto_id", "nunique"),
26        ventas_anonimas=("es_anonimo", "sum"),
27    ).reset_index()
28
29    # Revenue neto (con descuento)
30    neto = df.copy()
31    neto["rev_neto"] = (
32        neto["precio"] * neto["cantidad"]
33        * (1 - neto["descuento_pct"].fillna(0) / 100.0)
34    )
35    gold_neto = neto.groupby(["campana", neto["fecha_compra"].dt.date]).agg(
36        revenue_neto=("rev_neto", "sum"),
37    ).reset_index()
38    gold_neto.columns = ["campana", "fecha", "revenue_neto"]
39
40    # Merge
41    gold = gold.merge(gold_neto, on=["campana", "fecha"])
42
43    # Ticket medio
44    gold["ticket_medio"] = gold["revenue_neto"] / gold["num_transacciones"]
45
46    # Running totals (acumulados)
47    gold = gold.sort_values(["campana", "fecha"])
48    gold["revenue_neto_acumulado"] = gold.groupby("campana")["revenue_neto"].cumsum()
49    gold["transacciones_acumuladas"] = gold.groupby("campana")["num_transacciones"].cumsum()
50
51    logger.info(f"Tabla gold construida: {len(gold)} filas (días × campañas)")
52    return gold
53
54
55def build_top_products(silver_path: Path, top_n: int = 50) -> pd.DataFrame:
56    """Top N productos por revenue en cada campaña."""
57    ventas = pd.read_parquet(silver_path / "campaign_ventas.parquet")
58    productos = pd.read_parquet(silver_path / "productos.parquet")
59
60    ventas["revenue"] = ventas["precio"] * ventas["cantidad"]
61
62    stats = ventas.groupby(["campana", "producto_id"]).agg(
63        revenue_bruto=("revenue", "sum"),
64        unidades_vendidas=("cantidad", "sum"),
65        compradores_unicos=("cliente_id", "nunique"),
66    ).reset_index()
67
68    # Enriquecer con nombre del producto
69    stats = stats.merge(
70        productos[["producto_id", "nombre", "marca"]],
71        on="producto_id",
72        how="left",
73    )
74
75    # Ranking por revenue dentro de cada campaña
76    stats["ranking"] = stats.groupby("campana")["revenue_bruto"].rank(
77        ascending=False, method="dense"
78    ).astype(int)
79
80    return stats[stats["ranking"] <= top_n].sort_values(["campana", "ranking"])

gold.py — La misma lógica del SQL implementada en Pandas para ejecución local

1María García  15:02 PM
2Oye!! ¿cómo van los números? Mañana a las 12 presento a dirección
3¿Me puedes pasar un preview? Solo necesito:
4- Revenue total verano vs primavera
5- Clientes nuevos captados en cada campaña
6- Top 10 productos más vendidos en cada una
7Si puedes mandármelo en un Excel sería 💯
8(perdona que sea pesada, es que Laura me está presionando mucho 😅)
9
10Tú  15:15 PM
11Tranquila María, lo tengo casi listo. Te mando un resumen por email
12antes de las 17:00 con esos 3 puntos.
13Una duda: cuando dices "clientes nuevos captados", ¿te refieres a
14clientes que se REGISTRARON durante la campaña o clientes que hicieron
15su PRIMERA compra durante la campaña?
16
17María García  15:18 PM
18Hmm buena pregunta 🤔
19Los dos datos me interesan, pero lo principal es los que COMPRARON
20por primera vez. Esos son los que cuenta Laura.
21
22Tú  15:20 PM
23Perfecto, te incluyo ambos. A las 17 lo tienes.

Clarificar definiciones ANTES de dar números — fundamental

1Asunto: Preview datos campañas — para tu presentación del viernes
2
3Hola María,
4
5Aquí van los números que necesitas. Todos calculados sobre datos
6validados y limpios (42.735 transacciones de verano, 31.048 de primavera).
7
8━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
91. REVENUE TOTAL
10━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
11                   Verano         Primavera      Diferencia
12Revenue bruto:     €847.234       €612.891       +38.2%
13Revenue neto:      €721.449       €534.112       +35.1%
14Descuento medio:   14.8%          12.8%          —
15
16→ Verano generó un 35% más de revenue neto, aunque también
17  se aplicaron más descuentos.
18
19━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
202. CLIENTES NUEVOS CAPTADOS
21━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
22                   Verano         Primavera
23Primera compra:    3.412          2.189          +55.8%
24Registro nuevo:    4.891          3.102          +57.6%
25
26→ De los 15K clientes que recibieron la campaña de verano,
27  3.412 hicieron su PRIMERA compra durante la promo.
28  Eso es un 22.4% de conversión de new-to-buyer.
29
30━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
313. TOP 5 PRODUCTOS (por revenue neto)
32━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
33Verano:
34 1. Pack BBQ premium (€23.450)
35 2. Cerveza artesana 12-pack (€18.920)
36 3. Helado premium 1L (€15.340)
37 4. Gazpacho fresco 2L (€12.890)
38 5. Sandía entera (€11.230)
39
40Primavera:
41 1. Aceite oliva virgen extra 1L (€14.230)
42 2. Espárragos frescos 500g (€11.890)
43 3. Vino rosado (€10.450)
44 4. Fresas 1kg (€9.870)
45 5. Jamón ibérico 100g (€8.920)
46
47→ Los tops de cada campaña coinciden con la estacionalidad
48  (BBQ en verano, productos frescos primavera).
49
50━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
51NOTAS TÉCNICAS (por si Laura pregunta):
52- Revenue NETO = después de descuentos, ANTES de devoluciones
53  (las devoluciones tardan hasta 14 días, no hay datos completos aún)
54- "Cliente nuevo" = primera compra durante la campaña
55- Ventas anónimas (guest checkout) NO están incluidas en las
56  métricas de clientes pero SÍ en el revenue.
57━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
58
59Si necesitas más detalle o algún corte diferente, dime.
60¡Mucha suerte mañana!

Comunicar datos a no-técnicos: claro, con contexto y anticipando preguntas

1# Pipeline de Ingesta de Campañas de Marketing
2
3## Qué hace
4Ingesta CSVs de campañas de Marketing desde raw, los limpia,
5valida y deja una tabla gold con métricas diarias por campaña.
6
7## Arquitectura
8raw/ (CSVs originales) → silver/ (limpio, Parquet) → gold/ (agregado)
9
10## Cómo ejecutar
11
12### Pipeline completo (una campaña):
13```bash
14# 🟦 Windows (PowerShell):
15python pipelines\campaign_ingestion\run.py --campaign verano
16
17# 🍎 Mac (Terminal):
18python pipelines/campaign_ingestion/run.py --campaign verano
19```
20
21### Solo tabla gold (si silver ya está listo):
22```bash
23python pipelines/campaign_ingestion/run.py --only-gold
24```
25
26### Tests:
27```bash
28pytest pipelines/campaign_ingestion/tests/ -v
29```
30
31## Campañas soportadas
32| Campaña     | Periodo           | Archivos en raw    |
33|-------------|-------------------|--------------------|
34| verano      | Jun-Sep 2024      | 3 CSVs de María    |
35| primavera   | Mar-May 2024      | 1 CSV (otro formato)|
36
37## Añadir una nueva campaña
381. Añadir entrada al Enum Campaign en config.py
392. Añadir mapping de columnas en COLUMN_MAPPINGS
403. Añadir config de limpieza en CAMPAIGN_CONFIG
414. Añadir rango de fechas en DATE_RANGES
425. Ejecutar con --campaign <nombre>
436. Verificar que pytest pasa
44
45## Problemas conocidos
46- Los CSVs de Marketing no tienen formato estándar (cada campaña diferente)
47- Las ventas anónimas (guest checkout) no tienen cliente_id
48- Las devoluciones NO están descontadas del revenue (tardan 14 días)
49
50## Owner
51- Pipeline: tú
52- Datos fuente: María García (Marketing)
53- Tabla gold: consumida por dashboards de Metabase + Jorge (análisis)

README pragmático: qué hace, cómo ejecutar, cómo extender, problemas conocidos