fbpx

Consultas útiles para BigQuery y Google Search Console: Todo lo que puedes extraer

Por Paul
Por Paul

Google Search Console proporciona una gran cantidad de datos sobre el rendimiento de un sitio web en las búsquedas de Google, pero a veces es necesario ir más allá del panel de control de GSC para hacer análisis avanzados. Al integrar estos datos con BigQuery, puedes hacer consultas complejas y extraer información valiosa para mejorar tu SEO y monitorear el estado de tu sitio.

En este artículo, exploraremos diferentes consultas SQL que puedes ejecutar en BigQuery sobre los datos de Google Search Console. Estas consultas te ayudarán a obtener insights importantes como qué URLs están indexadas, qué páginas tienen problemas de rendimiento, y más.

Cómo realizar consultas en BigQuery

Antes de pasar a las consultas, te dejo una breve explicación de cómo ejecutarlas en BigQuery:

1. Accede a BigQuery desde la consola de Google Cloud Platform.

2. Selecciona el dataset que contiene tus datos de Google Search Console.

3. Abre el editor de consultas y pega la consulta SQL correspondiente.

4. Ejecuta la consulta haciendo clic en el botón “Run” para ver los resultados.

¿Qué datos puedes extraer desde Google Search Console y Bigquery?

Field Name
Descripción
Tipo de dato
data_date
Fecha en que los datos fueron capturados en GSC
DATE
site_url
URL principal del sitio (sitio raíz)
STRING
url
URL específica que aparece en los resultados de búsqueda
STRING
query
Consulta de búsqueda realizada por el usuario
STRING
is_anonymized_query
Indica si la consulta fue anonimizada por Google (True/False)
BOOLEAN
is_anonymized_discover
Indica si la consulta provino de Google Discover y está anonimizada
BOOLEAN
country
País desde el cual se realizó la búsqueda
STRING
search_type
Tipo de búsqueda: web, imágenes, vídeos, noticias, etc.
STRING
device
Tipo de dispositivo utilizado: móvil, escritorio, tablet
STRING
is_amp_top_stories
Indica si la URL aparece en AMP Top Stories
BOOLEAN
is_amp_blue_link
Indica si la URL aparece en un enlace AMP estándar
BOOLEAN
is_job_listing
Indica si la URL está listada como oferta de trabajo en los resultados
BOOLEAN
is_job_details
Indica si la URL muestra detalles específicos de una oferta de trabajo
BOOLEAN
is_tpf_qa
Indica si la URL aparece con un snippet de preguntas y respuestas
BOOLEAN
is_tpf_faq
Indica si la URL aparece con un snippet de FAQ
BOOLEAN
is_tpf_howto
Indica si la URL aparece con un snippet de ‘Cómo hacer’
BOOLEAN
is_weblite
Indica si la URL es parte de la versión ligera de Google para conexiones lentas
BOOLEAN
is_action
Indica si la URL es una ‘acción’ en el contexto de Google Actions
BOOLEAN
is_events_listing
Indica si la URL está mostrando una lista de eventos
BOOLEAN
is_events_details
Indica si la URL muestra detalles específicos de eventos
BOOLEAN
is_search_appearance_android_app
Indica si la URL aparece en los resultados de búsqueda como una app de Android
BOOLEAN
is_amp_story
Indica si la URL aparece en el formato de AMP Story
BOOLEAN
is_amp_image_result
Indica si la URL aparece como resultado de una imagen en AMP
BOOLEAN
is_video
Indica si la URL contiene un vídeo que aparece en los resultados de búsqueda
BOOLEAN
is_organic_shopping
Indica si la URL aparece en Google Shopping orgánico
BOOLEAN
is_review_snippet
Indica si la URL muestra un snippet de reseñas
BOOLEAN
is_special_announcement
Indica si la URL muestra un anuncio especial
BOOLEAN
is_recipe_feature
Indica si la URL muestra características específicas de recetas
BOOLEAN
is_recipe_rich_snippet
Indica si la URL muestra un snippet enriquecido de receta
BOOLEAN
is_subscribed_content
Indica si la URL muestra contenido con suscripción
BOOLEAN
is_page_experience
Indica si la URL cumple con los criterios de ‘Experiencia en la página’ (Core Web Vitals)
BOOLEAN
is_practice_problems
Indica si la URL muestra ejercicios prácticos
BOOLEAN
is_math_solvers
Indica si la URL está relacionada con la solución de problemas matemáticos
BOOLEAN
is_translated_result
Indica si la URL aparece como resultado traducido
BOOLEAN
is_edu_q_and_a
Indica si la URL está relacionada con preguntas y respuestas educativas
BOOLEAN
is_product_snippets
Indica si la URL muestra snippets de productos (precio, disponibilidad, etc.)
BOOLEAN
is_merchant_listings
Indica si la URL aparece en listas de comerciantes
BOOLEAN
is_learning_videos
Indica si la URL muestra vídeos educativos
BOOLEAN
impressions
Número de veces que la URL apareció en los resultados de búsqueda
INTEGER
clicks
Número de clics que recibió la URL
INTEGER
sum_position
Posición promedio de la URL en los resultados de búsqueda
INTEGER

Situaciones clave que puedes extraer de BigQuery y Google Search Console

1. Extraer todas las URLs indexadas con impresiones

Para asegurarte de que todas tus URLs están correctamente indexadas en Google y han aparecido en los resultados de búsqueda, puedes extraer todas las URLs que han recibido al menos una impresión. Esto es importante porque una URL que no recibe impresiones puede estar fuera del índice de Google o no estar bien optimizada para las consultas relevantes.

SELECT
  url,
  SUM(impressions) AS total_impressions
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
WHERE
  impressions > 0
GROUP BY
  url
ORDER BY
  total_impressions DESC

Esta consulta extrae todas las URLs que tienen impresiones y las ordena por el total de impresiones recibidas. Es un buen punto de partida para identificar qué páginas están recibiendo tráfico en Google.

2. Identificar URLs sin impresiones

Las URLs que no reciben impresiones podrían tener problemas de indexación, contenido irrelevante o estar en una estructura poco accesible para los motores de búsqueda. Identificar estas páginas te permite analizar si necesitan ajustes o si hay problemas de indexación que deban resolverse.

SELECT
  url
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
WHERE
  impressions = 0
GROUP BY
  url

Esta consulta te permite ver todas las URLs que no están recibiendo impresiones, lo que indica que no están apareciendo en los resultados de búsqueda. Esto puede ser una señal de que no están bien optimizadas o de que no han sido indexadas correctamente.

3. Identificar URLs con impresiones pero sin clics

Si una página está recibiendo impresiones pero no genera clics, puede ser indicativo de que el título o la descripción meta no son lo suficientemente atractivos, o que la página no está respondiendo bien a la intención de búsqueda. Esto te ayudará a revisar esas páginas para mejorar su CTR (Click Through Rate).

SELECT
  url,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
WHERE
  impressions > 0
  AND clicks = 0
GROUP BY
  url
ORDER BY
  total_impressions DESC

Esta consulta identifica las páginas que están apareciendo en los resultados de búsqueda pero no están logrando atraer clics. Una buena forma de solucionar esto es mejorar el contenido de los fragmentos visibles en los resultados de búsqueda (meta título y descripción).

URLs con baja posición promedio en los resultados de búsqueda

Una baja posición promedio puede indicar que una URL no está bien posicionada para las consultas relevantes o que necesita más optimización de SEO, como mejorar el contenido o los enlaces entrantes. Esta consulta te permite identificar esas páginas con baja visibilidad.

SELECT
  url,
  AVG(sum_position) AS avg_position
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
GROUP BY
  url
HAVING
  avg_position > 50
ORDER BY
  avg_position DESC

Aquí se listan las URLs que están posicionadas en promedio más allá del puesto 50. Estas páginas podrían necesitar una estrategia de optimización para mejorar su clasificación en Google.

5. Rendimiento por dispositivo (móvil o escritorio)

El comportamiento de las URLs puede variar dependiendo del dispositivo desde el que se accede. Saber cómo rinden tus URLs en móvil y en escritorio te permite ajustar la experiencia de usuario y optimizar el rendimiento SEO para cada plataforma.

SELECT
  url,
  device,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
GROUP BY
  url, device
ORDER BY
  total_impressions DESC

Esta consulta separa el rendimiento de las URLs según el dispositivo, lo que te permitirá optimizar el diseño y la funcionalidad de las páginas para móviles o escritorio, dependiendo de los resultados.

6. Filtrar URLs que están en AMP (Accelerated Mobile Pages)

Si utilizas AMP en tu sitio, puede ser útil identificar qué URLs están sirviendo como páginas AMP y cuáles no. Esto te ayuda a asegurarte de que tus páginas AMP estén optimizadas para móviles y aparezcan en los resultados donde más se necesitan.

SELECT
  url,
  is_amp_top_stories,
  is_amp_blue_link
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
WHERE
  is_amp_top_stories = TRUE
  OR is_amp_blue_link = TRUE
GROUP BY
  url, is_amp_top_stories, is_amp_blue_link

Esta consulta te permite ver qué URLs están sirviendo como páginas AMP. Es útil para asegurarte de que las páginas críticas para el rendimiento móvil estén optimizadas y funcionando correctamente.

7. Identificar URLs con características avanzadas en los resultados de búsqueda (snippets)

Google Search puede mostrar fragmentos enriquecidos para diferentes tipos de contenido, como recetas, productos, reseñas, etc. Saber qué páginas están mostrando estas características te ayuda a identificar oportunidades para mejorar el contenido de otras páginas y aumentar su visibilidad.

SELECT
  url,
  is_recipe_rich_snippet,
  is_review_snippet,
  is_product_snippets
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
WHERE
  is_recipe_rich_snippet = TRUE
  OR is_review_snippet = TRUE
  OR is_product_snippets = TRUE
GROUP BY
  url, is_recipe_rich_snippet, is_review_snippet, is_product_snippets

Con esta consulta, puedes ver qué páginas están mostrando fragmentos enriquecidos en los resultados de búsqueda. Esto puede ser una ventaja competitiva, ya que estos fragmentos suelen atraer más clics y ofrecen mayor visibilidad en Google.

8. Consultar URLs que aparecen en búsquedas de productos en Google Shopping

Si tienes un ecommerce, es útil saber qué productos aparecen en Google Shopping de manera orgánica, lo que te permite identificar qué productos están bien posicionados y cuáles podrían necesitar mejoras.

SELECT
  url,
  is_product_snippets,
  is_organic_shopping
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
WHERE
  is_organic_shopping = TRUE
GROUP BY
  url, is_product_snippets, is_organic_shopping

Esta consulta te ayuda a identificar qué productos aparecen en los resultados de Google Shopping, lo que te permite optimizar tus descripciones y detalles de producto para obtener mejores resultados de búsqueda y ventas.

9. Identificar URLs con problemas de Core Web Vitals

SELECT
  url,
  is_page_experience
FROM
  `tu_proyecto.tu_dataset.searchdata_url_impression`
WHERE
  is_page_experience = FALSE
GROUP BY
  url

Aquí se muestran las URLs que no cumplen con los criterios de “Experiencia en la página” (Core Web Vitals). Esto te da una lista de páginas que necesitan ajustes en su velocidad, interactividad o estabilidad visual.

En Resumen

Con estas consultas, puedes aprovechar al máximo los datos de Google Search Console en BigQuery para obtener insights valiosos sobre el rendimiento de tus páginas. Desde la optimización de la indexación y el CTR, hasta la identificación de problemas técnicos y oportunidades de mejora en dispositivos móviles y AMP, estas consultas te permiten tener un control exhaustivo sobre el SEO de tu sitio web.

Paul Cris
Últimas entradas de Paul Cris (ver todo)
El SEO Secreto...

Copyright © 2024. – paulcris.com

Auditoria Gratuita SEO