Nivel 1 · Costo por conversión en SQL

Cómo lee un analista este ticket

Jordan necesita tres números por canal: cuántas visitas trajo, cuántas de esas convirtieron y cuánto costó. Dos tablas los contienen, y ninguna está al grano de la respuesta:

• web_sessions tiene una fila por visita. source dice de dónde vino la visita y converted dice si terminó en una compra.
• marketing_spend tiene una fila por día y por canal. Sus valores de channel (paid, email, social) coinciden con web_sessions.source, pero organic y direct no tienen ningún gasto, porque nadie paga por ellos.

La respuesta es una fila por fuente (o por fuente y mes). Así que el plan es: agrega cada tabla a ese grano por su cuenta y después une los dos resultados pequeños.

Paso 1: tasa de conversión

Una tabla, un GROUP BY. El COUNT(*) FILTER (WHERE converted) de DuckDB cuenta solo las filas convertidas; SUM(CASE WHEN converted THEN 1 ELSE 0 END) es la forma portable de escribirlo. Multiplica por 100.0 antes de dividir para que la división sea decimal.

Ten cuidado con cómo describes el resultado. En este conjunto de datos las sesiones no están vinculadas a pedidos, así que esta es una tasa de conversión de sesiones tal como la reportó la herramienta de analítica web, no un conteo de pedidos.

Paso 2: la trampa de la duplicación y su solución

La consulta tentadora une las tablas en bruto:

SELECT w.source, SUM(m.amount) ...
FROM web_sessions AS w
JOIN marketing_spend AS m ON m.channel = w.source

Cada sesión de paid coincide con cada día de gasto de paid, así que el gasto se multiplica por el número de sesiones. Los números salen en miles de millones y nadie lo nota hasta que lo nota el director financiero. Incluso unir también por fecha repite el gasto de cada día una vez por cada sesión de ese día.

La solución es el patrón del capítulo de CTE: preagrega antes de unir. Una CTE reduce las sesiones a una fila por fuente, otra reduce el gasto a una fila por canal, y entonces el JOIN es uno a uno. Parte del lado de las sesiones con un LEFT JOIN para que organic y direct se queden en la respuesta con gasto NULL. Dejarlas fuera ocultaría que aportan gratis una gran parte de las conversiones.

NULLIF(a, b) devuelve NULL cuando a es igual a b, y a en caso contrario. Así, NULLIF(c.conversions, 0) convierte un cero en NULL, y cualquier cosa dividida entre NULL es NULL:

SELECT 5000 / NULLIF(25, 0) AS per_conversion,   -- 200.0
       5000 / NULLIF(0, 0)  AS no_conversions;   -- NULL, not an error

Un canal con gasto y sin conversiones da entonces un costo por conversión NULL. Postgres y la mayoría de los almacenes de datos lanzan un error de división entre cero sin esto, así que escríbelo de forma explícita.

Paso 3: mes a mes para paid

La misma receta, con month añadido al grano en ambos lados. strftime(spend_date, '%Y-%m') crea la etiqueta de mes en texto ('2025-03'), exactamente como en el caso de las fiestas; constrúyela igual en ambas CTE para que las claves del JOIN coincidan. Las dos CTE tienen una fila por mes, así que aquí un JOIN interno es seguro. Las cifras mensuales de paid son ruidosas porque las conversiones por mes son números pequeños; fíjate en la dirección, no en los decimales.

SQL y pandas lado a lado

Idea	SQL	pandas
contar donde es verdadero	COUNT(*) FILTER (WHERE converted)	("converted", "sum")
preagregar	una CTE con GROUP BY	groupby(...).agg(...) en un nuevo DataFrame
conservar fuentes sin gasto	LEFT JOIN	merge(how="left")
división segura	NULLIF(x, 0)	dividir entre 0 da inf; reemplázalo por NaN

Errores comunes

• Unir tablas en bruto con granos distintos. Pregúntate siempre «¿cuántas filas por clave tiene cada lado?» antes de un JOIN.
• Días de gasto faltantes. Faltan doce filas de día y canal en marketing_spend por caídas del seguimiento. Los totales quedan ligeramente bajos; dilo en el informe en lugar de inventar valores.
• Filtrar el año solo en un lado. Ambas tablas necesitan el filtro de 2025, o el gasto de tres años se divide entre las conversiones de uno.
• Atribución. «Fuente de la sesión» es atribución de último contacto. Es el punto de partida habitual y la primera pregunta habitual de Finanzas; menciónalo.