Cómo calcular el ROI real de tus keywords con SQL

Más allá de los clics

El volumen de clics de una keyword no indica su valor real. Una keyword con 1.000 clics mensuales que no genera ninguna conversión tiene menos valor que una con 50 clics que genera 10 ventas. Sin embargo, la mayoría de los análisis de keywords se quedan en la superficie: clics, impresiones, posición.

Calcular el ROI (retorno de inversión) real de una keyword requiere conectar dos datos que viven en fuentes distintas: los clics vienen de Google Search Console, y las conversiones vienen de GA4. SQL en BigQuery permite hacer esa conexión.

El concepto: valor por keyword

El valor por keyword se calcula dividiendo el revenue (o el valor de las conversiones) generado por los usuarios que llegaron a través de esa keyword, entre el número de clics de esa keyword. El resultado es un «valor por clic» que permite comparar keywords entre sí de forma objetiva.

-- Fórmula: valor por clic
valor_por_clic = revenue_total / clics_gsc

-- Fórmula: tasa de conversión por keyword
tasa_conversion = conversiones / clics_gsc * 100

La query paso a paso

La query de valor por keyword del catálogo resuelve este cálculo. Su estructura sigue el patrón de CTEs que es estándar para cruces de datos:

-- CTE 1: datos de GSC (keywords + URLs + clics)
WITH gsc_keywords AS (
  SELECT query AS keyword, url, SUM(clicks) AS clics_gsc
  FROM gsc_url_impression
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
    AND CURRENT_DATE()
  GROUP BY keyword, url
  HAVING clics_gsc >= 5
),
-- CTE 2: datos de GA4 (URLs + conversiones + revenue)
ga4_revenue AS (
  SELECT url_normalizada, COUNT(*) AS conversiones,
    SUM(event_value) AS revenue_total
  FROM ga4_organic_conversions
  GROUP BY url_normalizada
)
-- Cruce final
SELECT keyword, url, clics_gsc, conversiones, revenue_total,
  ROUND(SAFE_DIVIDE(revenue_total, clics_gsc), 2) AS valor_por_clic
FROM gsc_keywords
INNER JOIN ga4_revenue ON gsc_keywords.url = ga4_revenue.url_normalizada
ORDER BY revenue_total DESC

El INNER JOIN conecta solo las URLs que existen en ambas fuentes: tienen clics en GSC y conversiones en GA4.

Es importante notar que el filtro HAVING clics_gsc >= 5 en el primer CTE elimina keywords con muy pocos clics. Esto evita que el resultado se llene de keywords de cola larga con un solo clic y una conversión casual, que mostrarían una tasa de conversión del 100% sin significancia estadística. El umbral puede ajustarse según el volumen del sitio: para sitios con mucho tráfico, un mínimo de 20 o 50 clics produce resultados más fiables.

Interpretación de los resultados

El resultado es una tabla donde cada fila muestra una keyword, su URL, los clics que genera, las conversiones asociadas y el valor por clic. Los hallazgos más accionables suelen ser:

• Keywords con valor por clic alto y pocos clics: son las que más se beneficiarían de mejorar su posición. Si una keyword genera 2 USD por clic pero solo tiene 20 clics, subir de la posición 8 a la posición 3 podría multiplicar su impacto.
• Keywords con muchos clics y valor cero: generan tráfico pero no conversiones. Puede indicar un problema de intent mismatch (la keyword trae tráfico que no coincide con lo que el sitio ofrece) o una oportunidad de CRO en la landing page.
• Keywords con tasa de conversión anormalmente alta: merecen atención especial. Pueden ser las keywords más valiosas del sitio y conviene proteger su posición.

Cuando no hay revenue directo

Muchos sitios no registran revenue en GA4 (sitios de contenido, sitios de generación de leads, SaaS con proceso de venta largo). En estos casos, se puede asignar un valor estimado a cada tipo de conversión:

-- Asignar valor estimado por tipo de conversión
CASE event_name
  WHEN 'generate_lead' THEN 50  -- valor estimado de un lead
  WHEN 'sign_up' THEN 20        -- valor de un registro
  WHEN 'demo_request' THEN 200  -- valor de una solicitud de demo
  ELSE 0
END AS valor_estimado

Los valores estimados se definen con el equipo comercial o de marketing y permiten calcular un «ROI estimado» que, aunque no sea exacto, es mucho mejor que no medir nada. Revisar y actualizar estos valores cada trimestre asegura que el cálculo se mantenga alineado con la realidad del negocio.

Periodicidad recomendada

Este análisis conviene ejecutarlo mensualmente. El ROI por keyword puede variar según la estacionalidad del negocio, cambios en el funnel de conversión o actualizaciones de algoritmo. Un seguimiento mensual permite detectar cambios de tendencia antes de que impacten significativamente en los resultados.

Para automatizar, la query se puede programar como Scheduled Query en BigQuery, con los resultados conectados a un dashboard en Looker Studio.

Un refinamiento adicional consiste en segmentar el ROI por tipo de keyword. Las keywords de marca (brand keywords) suelen tener tasas de conversión mucho más altas que las genéricas, pero su valor estratégico es diferente. Separar ambas categorías en el análisis, usando un filtro WHERE keyword NOT LIKE '%nombre_marca%', permite evaluar el ROI del SEO no-brand de forma aislada, que es generalmente el indicador más relevante del rendimiento de la estrategia de contenido.