Nivel 7 · Desafío final: el informe de embudo para el presupuesto

Qué necesita el informe

El paquete de presupuesto de Marketing tiene tres tablas, y cada una usa una técnica distinta de este capítulo:

1. Conversión por fuente y dispositivo (2025): un GROUP BY con dos columnas.
2. Costo por conversión por año y canal de pago: dos tablas, cada una agregada por su cuenta, y luego unidas.
3. Embudo por fuente (2025): dos etapas por fuente con tasas por paso.

Agrega antes de unir

marketing_spend tiene una fila por día y canal. web_sessions tiene una fila por visita. Si las unes por canal y año, cada fila de gasto se repite una vez por cada sesión coincidente: el gasto de paid de 2025 pasa de $340,778 a $1.2 mil millones de la nada.

La solución es llevar cada tabla a la granularidad de la respuesta antes, una fila por año y canal, en su propia CTE:

WITH spend AS (SELECT year(spend_date) AS year, channel, SUM(amount) AS spend FROM marketing_spend GROUP BY 1, 2),
     conv  AS (SELECT year(started_at) AS year, source, COUNT(*) FILTER (WHERE converted) AS conversions FROM web_sessions GROUP BY 1, 2)

Ahora el join es uno a uno y nada se cuenta dos veces. Organic y direct no tienen gasto, así que un inner join desde spend los descarta, que es lo que debe hacer una tabla de costos.

Tasas por paso según la fuente

Para la tabla de embudo, cuenta las sesiones con interacción y las conversiones por fuente en una sola pasada, y divide cada etapa entre la etapa anterior. engaged_pct muestra qué tan bien los visitantes de una fuente pasan de la página de aterrizaje; engaged_conv_pct muestra si los que exploran llegan a comprar. Ordenar por la segunda deja arriba a las fuentes cuyos visitantes con interacción compran.

Salvedades para el paquete

• Las conversiones son sesiones convertidas de último contacto, no pedidos. Los pedidos de la app y del marketplace no están en los datos de sesiones.
• Solo los clientes conocidos pueden convertir aquí, así que las fuentes con muchos visitantes anónimos se ven más débiles.
• Faltan doce filas de día/canal en marketing_spend por caídas del seguimiento, así que algunos totales anuales están ligeramente bajos.

Errores comunes

• Sumar el gasto después de un join con las sesiones.
• Dividir las conversiones entre todas las sesiones en la tasa del paso, lo que repite la tasa global.
• Filtrar 2025 con year() = 2025 en una tabla y con un rango de fechas en la otra y obtener ventanas distintas. Usa la misma regla en ambos lados.