Search Console a BigQuery: exportar datos con SQL

La exportación masiva de Search Console a BigQuery guarda cada día todas las filas de rendimiento que el informe recorta, y te deja consultarlas con SQL. Aquí tienes cómo montarla, qué tablas crea y cuánto cuesta de verdad.

Qué es la exportación masiva de Search Console a BigQuery y cuándo te conviene

Search Console muestra una muestra. La exportación masiva envía a diario todo el rendimiento de tu propiedad a BigQuery, sin el tope de filas del informe. Si prefieres el lado visual, el dashboard con Data Studio y Search Console es el camino sin código.

El techo que rompe el informe diario

La Search Analytics API admite un rowLimit de 1 a 25.000 filas, 1.000 por defecto, y para paginar hay que usar startRow. Es una ventana, no un archivo. La exportación masiva no pasa por ese techo: está pensada para sitios con decenas de miles de páginas o consultas diarias.

Cuándo no vale la pena

Con menos de mil URLs y revisión semanal, probablemente no compensa: montas Cloud, facturación y mantenimiento para ver las mismas veinte consultas. Solo vale la pena si necesitas histórico largo y detalle por URL y consulta.

Lo que no entra: consultas anonimizadas

La exportación incluye todo salvo las consultas anonimizadas, filtradas por privacidad. Llegan con query vacío e is_anonymized_query en verdadero; si sumas sin filtrar, esa fila fantasma suele ser la mayor de tu propiedad.

Antes de empezar: el proyecto de Google Cloud

Facturación activa y las dos APIs que hay que habilitar

Necesitas un proyecto de Google Cloud con facturación activa y, dentro, la BigQuery API y la BigQuery Storage API habilitadas. Sin facturación, la exportación falla el primer día.

El service account que escribe en tu proyecto y sus dos roles

Google escribe con la cuenta de servicio search-console-data-export@system.gserviceaccount.com, a la que concedes dos roles: BigQuery Job User para lanzar trabajos y BigQuery Data Editor para crear y escribir tablas. Con uno solo, la configuración se acepta y la exportación muere después.

Solo el propietario de la propiedad puede activarlo

El interruptor está reservado al propietario verificado en Search Console. Un usuario con permiso completo ve el panel, pero no inicia ni detiene la exportación; si trabajas para un cliente, que la active él o te haga propietario.

Configurar la exportación en Search Console paso a paso

Project ID, no project number

El formulario pide el identificador del proyecto, y aquí está el error más repetido: hay que escribir el project ID (el nombre corto, tipo mi-proyecto-123), nunca el project number numérico. La documentación lo subraya en negrita porque el número parece válido.

Nombre del dataset y ubicación: se eligen una vez

El dataset por defecto se llama searchconsole y siempre empieza por esa cadena. Si exportas varias propiedades al mismo proyecto, cada una necesita un dataset distinto: dos propiedades no comparten destino. La ubicación no se cambia con facilidad después.

Qué pasa en las primeras 48 horas

La primera exportación llega hasta 48 horas después e incluye los datos del día en que la activaste. No rellena el pasado: para histórico anterior hay que tirar de la API o de informes guardados.

Las tres tablas que aparecen en BigQuery

`searchdata_site_impression`: la vista por propiedad

Agrega el rendimiento a nivel de propiedad. Campos clave: data_date (hora del Pacífico), site_url, query, country con código ISO de tres letras, search_type (web, image, video, news, discover, googleNews), device, impressions, clicks y sum_top_position. En propiedades de dominio, site_url llega como sc-domain:ejemplo.com.

`searchdata_url_impression`: la vista por URL y tipo de resultado

Es la tabla de trabajo para SEO: añade url, is_anonymized_discover y booleanos is_* de apariciones enriquecidas, como is_tpf_faq. Ojo con el campo de posición: aquí se llama sum_position, no sum_top_position. Si los mezclas, las consultas devuelven ceros silenciosos.

`ExportLog`: el vigilante que casi nadie mira

Registra las exportaciones correctas con agenda, namespace, data_date, epoch_version y publish_time; suele haber dos filas por día porque cada tabla se exporta por separado. Los intentos fallidos no se registran aquí, y epoch_version sube cuando Google corrige datos de un día.

Por qué hay filas repetidas y por qué siempre hay que agregar

Search Console acumula los datos de forma incremental y los exporta sin comprimir, así que las claves se repiten. Leer una sola fila miente: el ejemplo oficial muestra tres filas del mismo día con 1.000, 200 y 5.000 impresiones, y el total real es 6.200. Usa siempre SUM.

Tres consultas SQL que sí sirven

Serie diaria con CTR y posición media (la posición empieza en cero)

SELECT
  data_date AS fecha,
  SUM(impressions) AS impresiones,
  SUM(clicks) AS clics,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)) + 1 AS posicion_media
FROM `tu_proyecto.searchconsole.searchdata_site_impression`
WHERE search_type = 'web'
  AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY fecha
ORDER BY fecha DESC;

La posición media real se calcula sumando sum_top_position, dividiendo entre impresiones y sumando 1 porque las posiciones se guardan desde cero. SAFE_DIVIDE evita el error de división por cero en días sin impresiones.

Consultas con impresiones y cero clics: la lista de oportunidades

SELECT
  query,
  SUM(impressions) AS impresiones,
  SUM(clicks) AS clics,
  SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)) + 1 AS posicion_media
FROM `tu_proyecto.searchconsole.searchdata_url_impression`
WHERE search_type = 'web'
  AND query != ''
  AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY query
HAVING clics = 0 AND impresiones >= 100
ORDER BY impresiones DESC
LIMIT 100;

El filtro query != '' saca las anonimizadas. Lo que queda son temas donde ya apareces y nadie hace clic: candidatos a reescribir título y meta, no a contenido nuevo.

Páginas que caen frente a las cuatro semanas anteriores

WITH periodos AS (
  SELECT
    url,
    IF(data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY), 'actual', 'anterior') AS periodo,
    SUM(clicks) AS clics
  FROM `tu_proyecto.searchconsole.searchdata_url_impression`
  WHERE search_type = 'web'
    AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY) AND CURRENT_DATE()
  GROUP BY url, periodo
)
SELECT
  url,
  SUM(IF(periodo = 'anterior', clics, 0)) AS clics_antes,
  SUM(IF(periodo = 'actual', clics, 0)) AS clics_ahora,
  SUM(IF(periodo = 'actual', clics, 0)) - SUM(IF(periodo = 'anterior', clics, 0)) AS diferencia
FROM periodos
GROUP BY url
HAVING clics_antes >= 20
ORDER BY diferencia ASC
LIMIT 50;

Este es el diagnóstico que el informe no da: páginas con base suficiente que perdieron clics. Si la caída es general, conviene pasar por la guía para diagnosticar una caída de tráfico. El caso de uso estrella es cruzarlo con tus logs del servidor: lo que Google dice que ve y lo que rastreó de verdad.

Costos y cuotas: cómo no despertarte con una factura

La capa gratis: 10 GiB de almacenamiento y 1 TiB de consultas al mes

BigQuery regala los primeros 10 GiB de almacenamiento mensual y el primer TiB consultado al mes. Un sitio mediano tarda años en acercarse, siempre que acotes las consultas. Revisa la tarifa vigente antes de automatizar algo que consulte millones de filas cada hora.

Vencimiento de particiones (nunca menos de 14 días)

Los datos se acumulan para siempre en tu proyecto, y eso se paga. Configura un vencimiento de particiones: el mínimo aceptado son 14 días y, si caduca antes, la exportación falla.

Acota siempre por fecha: la tabla está particionada por día

Filtra por data_date en cada consulta. Al estar particionada por día, el filtro reduce los bytes leídos y el costo. Una consulta sin filtro sobre dos años de datos gasta la capa gratis en una tarde.

Cuando la exportación falla: los errores que vas a ver

Los cuatro de configuración: permisos, facturación, esquema y ubicación

Los mensajes más comunes son permisos faltantes, facturación ausente, API de BigQuery no habilitada y desajuste de esquema. Se suman dataset ocupado por otra exportación y vencimiento de partición demasiado corto. El desajuste aparece cuando alguien toca las columnas a mano.

Los reintentos tienen fecha de caducidad

Ante un error no transitorio, Google reintenta al día siguiente con los datos pendientes, los conserva unos siete días y luego los descarta. Tras un mes de fallos seguidos, la exportación se detiene entera.

El botón de prueba y los logs de Cloud

El panel de ajustes permite iniciar, detener, reiniciar y ver el estado del último intento; al detener, puede tardar hasta 24 horas en parar y las tablas no se borran. Para errores silenciosos, busca search-console-data-export en Cloud Logs Explorer.

Checklist para dejarlo funcionando hoy

Proyecto de Cloud con facturación y las dos APIs de BigQuery.
Cuenta de servicio con BigQuery Job User y Data Editor.
Project ID correcto y dataset que empiece por searchconsole.
Vencimiento de particiones en 14 días o más.
Consulta de prueba acotada por data_date y con SUM.
Primera revisión en ExportLog a las 48 horas.

Conclusión: datos propios, no capturas de pantalla

La exportación masiva no te hace mejor SEO por sí sola: te da el archivo completo para hacer mejores preguntas. Montarla es media hora de configuración y una decisión de costos bien tomada. A cambio, dejas de discutir sobre una captura y comparas períodos con SQL.