Cómo consultar datos de Google Search Console en BigQuery

¿Quieres ver el contenido de la página? Mostrar

Actualizado: 11 de junio de 2025

Google Search Console (GSC) se ha convertido en una herramienta esencial para los creadores de contenido y los profesionales del marketing digital. Permite a los usuarios entender cómo se comportan sus sitios web en los resultados de búsqueda de Google. Sin embargo, uno de sus principales inconvenientes es que el panel de GSC solo muestra las 1,000 consultas de búsqueda más relevantes, lo que puede limitar la comprensión global del rendimiento de un sitio. Esto puede ser un obstáculo significativo cuando se busca un análisis más exhaustivo.

Una alternativa es utilizar la API de Google Search Console en Google Sheets, que permite acceder a un máximo de 25,000 filas de datos. Pero hay una opción aún más poderosa: conectar Google Search Console a BigQuery. Esta integración ofrece acceso a todos los datos en bruto y la capacidad de ejecutar consultas para analizar el rendimiento de búsqueda de manera efectiva.

Si esto te resulta interesante, a continuación, te guiaré a través de los pasos necesarios para conectar y consultar los datos de Google Search Console en BigQuery. Además, aprenderás a ejecutar tus primeras consultas.

Conectar Google Search Console a BigQuery

El primer paso es vincular tu propiedad de Google Search Console a BigQuery. Es crucial tener en cuenta que los datos solo comenzarán a exportarse una vez que se haya establecido la conexión, por lo que es recomendable configurar esto lo antes posible.

Para comenzar, abre la consola de BigQuery y busca la opción “API de BigQuery”. Asegúrate de habilitarla. Si no tienes un proyecto creado, necesitarás crear uno. Haz clic en el selector de proyectos, selecciona “Nuevo proyecto” y completa los campos necesarios, como el nombre del proyecto, que puede ser algo como GSC BigQuery, y elige una cuenta de facturación.

A continuación, busca “API de almacenamiento de BigQuery” y habilítala también. Después, es necesario configurar los permisos para que Google Search Console pueda enviar datos a tu proyecto de BigQuery. Busca “IAM” en la consola y haz clic en “Conceder acceso”. Ingresa el correo electrónico [email protected] y asigna los roles Usuario de trabajos de BigQuery y Editor de datos de BigQuery, luego haz clic en “Guardar”.

Dirígete a tu propiedad de Google Search Console, ve a Configuración > Exportación de datos masivos y proporciona el ID de tu proyecto en la nube. Puedes modificar opcionalmente el nombre del conjunto de datos y la ubicación del conjunto de datos. Una vez que estés listo, haz clic en “Continuar”.

Ten en cuenta que puede tardar unos días en aparecer la información en BigQuery, así que no te preocupes si no ves datos de inmediato. Además, BigQuery tiene un costo asociado, el cual puede variar según la cantidad de datos almacenados. Generalmente, los costos son mínimos, pero es bueno estar informado. Puedes consultar más detalles sobre precios aquí.

Consultando datos de Google Search Console en BigQuery

Una vez que hayas conectado GSC a BigQuery, regresa a la interfaz de BigQuery y busca “BigQuery”. En el panel izquierdo, encontrarás el panel Explorer, que contiene una lista de conjuntos de datos, tablas y consultas guardadas.

Con esta conexión, tendrás un conjunto de datos llamado searchconsole que incluye dos tablas: searchdata_site_impression, que está agrupada por propiedad, y searchdata_url_impression, que está agrupada por URL. Para este tutorial, nos centraremos en searchdata_site_impression.

Entendiendo los datos en BigQuery

Es fundamental conocer la estructura de los datos que se encuentran en las tablas. Puedes ver una vista previa de los datos seleccionando la tabla deseada en el panel Explorer y luego accediendo a la pestaña Vista previa.

Para este ejemplo, selecciona el conjunto de datos searchconsole y la tabla searchdata_site_impression. Esto abrirá una nueva pestaña donde podrás ver una porción de los datos disponibles.

Esquema de datos

Una vez que los datos comiencen a fluir hacia BigQuery, es útil entender la estructura de la información. Aquí están los campos clave que encontrarás:

data_date: Indica la fecha en que se registraron los datos. Recuerda que los datos de Google Search Console tienen un retraso de dos días.
site_url: La URL de la propiedad. Para propiedades a nivel de dominio, se mostrará como sc-domain:nombre-de-la-propiedad. Para propiedades de prefijo de URL, será la URL completa, como https://misitio.com/ayuda/.
query: El término de búsqueda que el usuario escribió en Google.
is_anonymized_query: Si este valor es verdadero, el campo de consulta será nulo.
country: El país desde donde se realizó la búsqueda.
search_type: El tipo de búsqueda en Google, que puede ser:
- WEB: La pestaña de búsqueda predeterminada.
- IMAGE: Búsqueda de imágenes de Google.
- VIDEO: Búsqueda de videos de Google.
- NEWS: Búsqueda de noticias de Google.
- DISCOVER: Feed de descubrimiento de Google.
- GOOGLENEWS: news.google.com y la aplicación de Google News.
device: El tipo de dispositivo que utilizó el usuario (por ejemplo, desktop, móvil, tableta).
impressions: La cantidad de veces que se mostró un enlace a tu sitio en los resultados de búsqueda.
clicks: La cantidad de veces que los usuarios hicieron clic en un enlace hacia tu sitio desde los resultados de búsqueda.
sum_top_position: La suma de las posiciones de los resultados de búsqueda (con 0 siendo el puesto más alto) para todas las impresiones en esta fila.

Por ejemplo, un registro puede indicar que el 13 de abril de 2025, hubo personas en Canadá que buscaron “comprar zapatos” usando Google Search en un dispositivo de escritorio. Un enlace a mi sitio apareció 79 veces (impresiones) y fue clicado 36 veces, con un total de posiciones sumadas de 389.

Crear una consulta básica

Para crear una consulta sencilla, debes hacer clic en el botón “Consulta”, lo que cargará una consulta de muestra incompleta para comenzar:

SELECT: Aquí se listan las columnas del conjunto de datos que deseas incluir en tus resultados.
FROM: Esta línea contiene la ruta completa a la tabla que estás consultando, como tuproyecto.searchconsole.searchdata_site_impression.
WHERE: Aquí puedes añadir filtros para reducir tus datos. Por defecto, filtrará data_date por el día actual.
LIMIT: Esto limita el número de filas devueltas en los resultados de tu consulta. Es importante recordar que esto solo afecta cuántas filas se muestran en tu salida, y no reduce la cantidad de datos procesados.

Consultando todos los datos

Al escribir una consulta simple para extraer datos de una tabla, puedes utilizar el símbolo asterisco (*) en la cláusula SELECT. Esto le indica a BigQuery que devuelva todas las columnas de la tabla.

SELECT * 
FROM `mi-proyecto.searchconsole.searchdata_site_impression` 
WHERE
   data_date = "2025-03-27"
LIMIT 1000

Antes de ejecutar tu consulta, observa la esquina inferior izquierda de la interfaz de BigQuery. Allí verás una estimación de cuántos datos procesará BigQuery para esta consulta. Esto es útil, ya que BigQuery ofrece 1 TB de procesamiento de consultas gratis por mes.

Es recomendable comenzar con un rango de datos más pequeño, como un solo día o una semana, antes de analizar un mes o un año. Esto te ayudará a confirmar que tu consulta devuelve los resultados deseados. A medida que vayas ajustando tu consulta, puedes evitar consultar grandes volúmenes de datos innecesarios.

Una vez que tu consulta esté lista, haz clic en “Ejecutar” (o CTRL+Enter en tu teclado) para ejecutarla.

Consultar clicks e impresiones por palabras clave

En ocasiones, es posible que solo desees un resumen simple, como el número total de impresiones o clics para cada consulta. Este tipo de resumen puede ayudarte a identificar rápidamente qué términos de búsqueda brindan más visibilidad a tu sitio.

Es importante tener en cuenta que la misma consulta puede aparecer en múltiples filas. Por ejemplo, si alguien en Canadá y Estados Unidos busca “segmentos”, los datos se presentarán en dos filas diferentes debido a las distintas ubicaciones. Lo mismo ocurre si las búsquedas se realizan en dispositivos diferentes.

Para superar esto, utiliza la función SUM para agregar tus métricas, como impresiones o clics. Al usar una función de agregación, es recomendable nombrar esa columna utilizando AS para facilitar la comprensión de la salida.

También necesitarás incluir una cláusula GROUP BY para indicarle a BigQuery cómo agrupar las filas antes de aplicar la agregación.

SELECT 
   query,
   SUM(clicks) AS clicks
FROM `mi-proyecto.searchconsole.searchdata_site_impression` 
WHERE
   data_date = "2025-03-27"
GROUP BY 
   query

Es posible que algunas filas muestren un valor nulo en el campo de consulta. Esto ocurre cuando is_anonymized_query está configurado como true. Para enfocarte en los términos de búsqueda más efectivos, puedes ordenar los resultados por la cantidad de clics utilizando la función ORDER BY.

SELECT 
   query,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions
FROM `mi-proyecto.searchconsole.searchdata_site_impression` 
WHERE
   data_date = "2025-03-27"
   AND is_anonymized_query = false
   AND search_type = 'WEB'
GROUP BY 
   query
ORDER BY
   clicks DESC

Si solo deseas ver las diez mejores palabras clave, puedes ajustar la función LIMIT.

SELECT 
   query,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions
FROM `mi-proyecto.searchconsole.searchdata_site_impression` 
WHERE
   data_date = "2025-03-27"
   AND is_anonymized_query = false
   AND search_type = 'WEB'
GROUP BY 
   query
ORDER BY
   clicks DESC
LIMIT 10

Crear métricas adicionales

Una de las ventajas de utilizar SQL en BigQuery es la posibilidad de crear métricas personalizadas, como la tasa de clics (CTR) y la posición promedio. La tasa de clics se calcula como el porcentaje de impresiones que llevan a un clic, y la posición promedio se basa en la clave sum_top_position.

La función ROUND() puede ser útil para redondear el número de decimales mostrados en los resultados, lo que mejora la presentación de tus datos.

Además, puedes personalizar el orden de las columnas en tu salida reordenándolas en la declaración SELECT, lo que facilita priorizar las métricas y dimensiones más relevantes para ti.

SELECT 
   query,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions,
   ROUND(SUM(clicks) / SUM(impressions), 2) AS ctr,
   ROUND(SUM(sum_top_position) / SUM(impressions) + 1.0, 1) AS avg_position
FROM `mi-proyecto.searchconsole.searchdata_site_impression` 
WHERE
   data_date = "2025-03-27"
   AND is_anonymized_query = false
   AND search_type = 'WEB'
GROUP BY 
   query
ORDER BY
   clicks DESC
LIMIT 10

Actualizar el rango de fechas

Al principio, es aconsejable construir una consulta usando solo un día de datos, ya que probablemente necesitarás ejecutarla varias veces antes de definir lo que realmente deseas. Una vez que hayas finalizado la consulta, puedes ampliar el rango de fechas según tus necesidades. Si deseas analizar datos entre dos fechas específicas, puedes utilizar el comando BETWEEN.

SELECT *
FROM `mi-proyecto.searchconsole.searchdata_site_impression`
WHERE
   data_date BETWEEN "2025-03-01" AND "2025-03-31"

También puedes configurar la fecha para que se actualice de manera más dinámica utilizando los comandos CURRENT_DATE() e INTERVAL.

Ten en cuenta que los datos de Google Search Console tienen un retraso de dos días. Por lo tanto, si estableces tu rango de fechas en los últimos 30 días, en realidad obtendrás datos de 28 días, ya que no se incluirán los dos días más recientes.

SELECT *
FROM `mi-proyecto.searchconsole.searchdata_site_impression`
WHERE
   data_date >= CURRENT_DATE() - INTERVAL 30 DAY

Recetas de consulta

Ahora que hemos revisado cómo construir consultas básicas, aquí tienes una versión más completa que incluye varios componentes útiles. Puedes copiar esta consulta y adaptarla según tus necesidades. Personalmente, encuentro útil guardar una consulta base como esta, ya que acelera la creación de nuevas consultas y garantiza consistencia.

Si hay líneas que no necesitas de inmediato pero deseas mantener, puedes usar “–” para comentarlas. Así no afectarán la salida, pero seguirán estando disponibles como referencia.

SELECT 
   query,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions,
   ROUND(SUM(clicks) / SUM(impressions), 2) AS ctr,
   ROUND(SUM(sum_top_position) / SUM(impressions) + 1.0, 1) AS avg_position
FROM `mi-proyecto.searchconsole.searchdata_site_impression` -- agrega el nombre del proyecto
WHERE
   data_date BETWEEN "YYYY-MM-DD" AND "YYYY-MM-DD" -- actualiza con fechas
   -- data_date >= CURRENT_DATE() - INTERVAL 30 DAY -- otra opción
   AND is_anonymized_query = false
   AND search_type = 'WEB'
GROUP BY 
   query
ORDER BY
   clicks DESC
LIMIT 10

Filtrando consultas

Hay muchas maneras diferentes de filtrar tus datos según el objetivo específico de cada consulta. Utilizando query LIKE ‘%texto%’, puedes filtrar los resultados para incluir solo palabras clave que coincidan con un patrón específico. Por ejemplo, si reemplazas %texto% con %segmento%, obtendrás palabras clave como “segmentos”, “segmentación” o “ayuda con segmentos”.

SELECT 
   query,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions,
   ROUND(SUM(clicks) / SUM(impressions), 2) AS ctr,
   ROUND(SUM(sum_top_position) / SUM(impressions) + 1.0, 1) AS avg_position
FROM `mi-proyecto.searchconsole.searchdata_site_impression` -- agrega el nombre del proyecto
WHERE
   data_date BETWEEN "YYYY-MM-DD" AND "YYYY-MM-DD" -- actualiza con fechas
   -- data_date >= CURRENT_DATE() - INTERVAL 30 DAY -- otra opción
   AND is_anonymized_query = false
   AND search_type = 'WEB'
   AND query LIKE '%texto%' -- reemplaza texto con tu cadena deseada
GROUP BY 
   query
ORDER BY 
   clicks DESC
LIMIT 10

Si deseas filtrar por varias palabras clave diferentes, puedes usar regex para ayudar. Aquí, puedes filtrar resultados basados en palabras clave que contengan cualquiera de varios términos específicos utilizando regex. Por ejemplo, (ga4|embudo) devolverá palabras clave como “instalación de ga4” y “embudo de exploraciones”. Para esto, puedes agregar REGEXP_CONTAINS(query, r'(texto|otroTexto)’) a la consulta.

SELECT 
   query,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions,
   ROUND(SUM(clicks) / SUM(impressions), 2) AS ctr,
   ROUND(SUM(sum_top_position) / SUM(impressions) + 1.0, 1) AS avg_position
FROM `mi-proyecto.searchconsole.searchdata_site_impression` -- agrega el nombre del proyecto
WHERE
   data_date BETWEEN "YYYY-MM-DD" AND "YYYY-MM-DD" -- actualiza con fechas
   -- data_date >= CURRENT_DATE() - INTERVAL 30 DAY -- otra opción
   AND is_anonymized_query = false
   AND search_type = 'WEB'
   AND REGEXP_CONTAINS(query, r'(texto|otroTexto)') -- reemplaza texto con tu cadena deseada
GROUP BY 
   query
ORDER BY 
   clicks DESC
LIMIT 10

Aunque no incluyas una columna específica en tu declaración SELECT, aún puedes usarla para filtrar resultados. Por ejemplo, podrías filtrar por device = ‘ESCRITORIO’ o country = ‘usa’ sin mostrar esos campos en la salida.

SELECT 
   query,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions,
   ROUND(SUM(clicks) / SUM(impressions), 2) AS ctr,
   ROUND(SUM(sum_top_position) / SUM(impressions) + 1.0, 1) AS avg_position
FROM `mi-proyecto.searchconsole.searchdata_site_impression` -- agrega el nombre del proyecto
WHERE
   data_date BETWEEN "YYYY-MM-DD" AND "YYYY-MM-DD" -- actualiza con fechas
   -- data_date >= CURRENT_DATE() - INTERVAL 30 DAY -- otra opción
   AND is_anonymized_query = false
   AND search_type = 'WEB'
   AND REGEXP_CONTAINS(query, r'(texto|otroTexto)') -- reemplaza texto con tu cadena deseada
   AND device = 'ESCRITORIO'
   AND country = 'usa'
GROUP BY 
   query
ORDER BY 
   clicks DESC
LIMIT 10

Te recomiendo que estudies un poco de SQL para conocer más sobre las diferentes funciones y métodos disponibles para personalizar tus consultas. Puedes ser muy creativo o mantenerlo simple, dependiendo de tus necesidades. Un gran recurso para comenzar es W3 Schools, que ofrece tutoriales gratuitos y algunos problemas de práctica.

Guardar consultas para uso futuro

Para guardar una consulta y poder usarla en el futuro, ve a Guardar > Guardar consulta, asígnale un nombre y haz clic en “Guardar”. Esto es útil para volver a esta consulta base, copiarla y construir otras consultas sobre ella.

La consulta guardada se encontrará bajo Consultas en el panel Explorer.

De manera similar, puedes guardar tu consulta como una vista seleccionando Guardar consulta > Guardar copia como vista. Esto crea una tabla virtual a la que puedes acceder fácilmente más adelante, lo que te permite obtener tus resultados sin necesidad de ejecutar la consulta cada vez.

Conectar y consultar datos de Google Search Console en BigQuery: Palabras finales

Vincular Google Search Console a BigQuery te brinda un nuevo nivel de flexibilidad y la capacidad de analizar tu rendimiento en búsqueda. Con solo habilidades básicas de SQL, puedes crear consultas simples pero efectivas que te muestren información más allá de lo que está disponible en la interfaz estándar de Google Search Console.

Empieza poco a poco y experimenta con algunas consultas. Si tienes alguna pregunta, no dudes en dejar un comentario.