BigQuery tiene una serie de ventajas que no se encuentran en otras herramientas cuando se trata de analizar grandes volúmenes de datos de Google Search Console (GSC).

Le permite procesar miles de millones de filas en segundos, lo que permite un análisis profundo de conjuntos de datos masivos.

Este es un paso adelante con respecto a Google Search Console, que solo le permite exportar 1000 filas de datos y puede tener discrepancias en los datos.

Has leído todo sobre por qué deberías utilizar BigQuery como profesional de SEO. Descubriste cómo conectar GSC con BigQuery. ¡Los datos fluyen!

¿Ahora que?

Es hora de empezar a consultar los datos. Comprender y consultar eficazmente los datos es clave para obtener información SEO procesable.

En este artículo, le explicaremos cómo puede comenzar con sus consultas.

Comprender la estructura de datos de GSC en BigQuery

Los datos están organizados en tablas. Cada tabla corresponde a un informe específico de Google Search Console. La documentación oficial es muy extensa y clara.

Sin embargo, si estás leyendo esto es porque quieres entender el contexto y los elementos clave antes de sumergirte en él.

Tomarse el tiempo para resolver esto significa que podrá crear mejores consultas de manera más eficiente y, al mismo tiempo, mantener los costos bajos.

Tablas, esquemas y campos de GSC en BigQuery

El esquema es el modelo que mapea lo que cada campo (cada pieza de información) representa en una tabla.

Tiene tres esquemas distintos presentados en la documentación oficial porque cada tabla no necesariamente contiene el mismo tipo de datos. Piense en las tablas como carpetas dedicadas que organizan tipos específicos de información.

Cada informe se almacena por separado para mayor claridad. Tienes:

• impresión_sitio_datosdebúsqueda: Contiene datos de rendimiento de su propiedad agregados por propiedad.
• searchdata_url_impresión: Contiene datos de rendimiento de su propiedad agregados por URL.
• registro de exportación: cada exportación exitosa a cualquiera de las tablas se registra aquí.

Algunas notas importantes sobre las tablas:

• En la documentación oficial encontrará que las cosas no funcionan como esperamos: “Search Console exporta datos masivos una vez al día, aunque no necesariamente al mismo tiempo para cada tabla”.
• Las tablas se conservan para siempre, de forma predeterminada, con la exportación masiva de GSC.
• En la tabla de nivel de URL (searchdata_url_impression), tiene datos de Discover. El campo is_anonymized_discover especifica si la fila de datos está sujeta al umbral de anonimización de Discover.

Los campos son piezas de información individuales, el tipo específico de datos en una tabla. Si se tratara de un archivo de Excel, nos referiríamos a los campos como las columnas de una hoja de cálculo.

Si hablamos de Google Analytics, los campos son métricas y dimensiones. Estos son los campos de datos clave disponibles en BigQuery cuando importas datos de GSC:

• Clics – Número de clics para una consulta.
• Impresiones – Número de veces que se mostró una URL para una consulta.
• CTR – Tasa de clics (clics/impresiones).
• Posición – Posición media de una consulta.

Tomemos como ejemplo el esquema de la tabla searchdata_site_impression. Contiene 10 campos:

Poniendo todo junto

En BigQuery, el conjunto de datos para la exportación masiva de Google Search Console (GSC) generalmente hace referencia a la colección de tablas que almacenan los datos de GSC.

El conjunto de datos se denomina “searchconsole” de forma predeterminada.

A diferencia de la pestaña de rendimiento en GSC, debes escribir consultas para pedirle a BigQuery que devuelva datos. Para hacer eso, debe hacer clic en el botón “Ejecutar una consulta en BigQuery”.

Captura de pantalla de Google Cloud Console, enero de 2024

Una vez que haga eso, debería tener acceso a BigQuery Studio, donde creará su primera consulta SQL. Sin embargo, No te recomiendo que hagas clic en ese botón todavía..

Captura de pantalla de BigQuery Studio, enero de 2024

En Explorer, cuando abra su proyecto, verá los conjuntos de datos; Es un logo con cuadrados con puntos. Aquí es donde ves si tienes datos GA4 y GSC, por ejemplo.

Cuando haces clic en las tablas, obtienes acceso al esquema. Puede ver los campos para confirmar que esta es la tabla que desea consultar.

Si hace clic en “CONSULTAR” en la parte superior de la interfaz, puede crear su consulta SQL. Esto es mejor porque carga cierta información que necesita para su consulta..

Completará el FROM con la tabla adecuada, establecerá un límite predeterminado y la fecha que puede cambiar si es necesario.

Captura de pantalla de Google Cloud Console, enero de 2024

Comenzando con su primera consulta

Search Console > La exportación de BigQuery anteriormente solo estaba disponible para empresas con desarrolladores/SEO súper tecnológico. ¡Ahora está disponible para todos!

Escribir SQL es una habilidad cada vez más importante para los especialistas en marketing y estoy creando algo para ayudar con eso; si desea probarlo, envíeme un mensaje privado 🙂 https://t.co/voOESJfo1e

— Robin Señor (@RobinLord8) 21 de febrero de 2023

Las consultas que vamos a discutir aquí son simples, eficientes y de bajo costo.

Descargo de responsabilidad: La declaración anterior depende de su situación específica.

Lamentablemente, no puedes quedarte en la zona de pruebas si quieres aprender a usar BigQuery con datos de GSC. Debes introducir tus datos de facturación. Si esto te ha asustado, no temas; los costos deben ser bajos.

• El primer TiB por mes de datos de consulta es gratuito.
• Si tiene un presupuesto ajustado, puede configurar alertas de presupuesto de facturación en la nube; puede configurar una alerta específica de BigQuery y recibir una notificación tan pronto como se produzcan cargos por uso de datos.

En SQL, la instrucción ‘SELECT *’ es un comando poderoso que se utiliza para recuperar todas las columnas de una tabla específica o recuperar columnas específicas según su especificación.

Esta declaración le permite ver el conjunto de datos completo o un subconjunto según sus criterios de selección.

Una tabla consta de filas, cada una de las cuales representa un registro único, y columnas, que almacenan diferentes atributos de los datos. Con “SELECT *”, puede examinar todos los campos de una tabla sin especificar cada columna individualmente.

Por ejemplo, para explorar una tabla de Google Search Console para un día específico, puede utilizar una consulta como:

SELECT *

FROM `yourdata.searchconsole.searchdata_site_impression`

WHERE data_date="2023-12-31"

LIMIT 5;

Siempre debe asegurarse de que la cláusula FROM especifique su tabla searchdata_site_impression. Es por eso que se recomienda comenzar haciendo clic primero en la tabla, ya que automáticamente completa la cláusula FROM con la tabla correcta.

Importante: Limitamos los datos que cargamos utilizando el campo data_date. Es una buena práctica limitar los costos (además de establecer un límite).

Su primera consulta de impresión de URL

Si desea ver información para cada URL de su sitio, deberá pedirle a BigQuery que extraiga información de la tabla “searchdata_url_impression”, seleccionando los campos “consulta” y “clics”.

Así es como se vería la consulta en la consola:

SELECT

url,

SUM(clicks) AS clicks,

SUM(impressions)

FROM

`yourtable.searchdata_url_impression`

WHERE

data_date = ‘2023-12-25’

GROUP BY

url

ORDER BY

clicks DESC

LIMIT

100

Siempre debe asegurarse de que la cláusula FROM especifique su tabla searchdata_url_impression.

Cuando exportas datos de GSC a BigQuery, la exportación contiene tablas de particiones. La partición es la fecha.

Esto significa que los datos en BigQuery están estructurados de una manera que permite una rápida recuperación y análisis según la fecha.

Por eso la fecha se incluye automáticamente en la consulta. Sin embargo, es posible que no tenga datos si selecciona la última fecha, ya que es posible que los datos aún no se hayan exportado.

Desglose de la consulta

En este ejemplo, seleccionamos los campos URL, clics e impresiones para el 25 de diciembre de 2023.

Agrupamos los resultados en función de cada URL con la suma de clics e impresiones de cada una de ellas.

Por último, ordenamos los resultados según la cantidad de clics para cada URL y limitamos la cantidad de filas (URL) a 100.

Recreando su informe GSC favorito

Te recomiendo que leas la guía de exportación masiva de datos de GSC. Debería utilizar la exportación, por lo que no proporcionaré información sobre la optimización de tablas. Eso es un poco más avanzado de lo que cubrimos aquí.

La pestaña de rendimiento de GSC muestra una dimensión a la vez, lo que limita el contexto. BigQuery le permite combinar varias dimensiones para obtener mejores conocimientos

El uso de consultas SQL significa que obtendrá una tabla ordenada. No es necesario comprender los entresijos de SQL para aprovechar al máximo BigQuery.

Esta consulta es cortesía de Chris Verde. Puede encontrar algunas de sus consultas SQL en Github.

SELECT

query,

is_anonymized_query AS anonymized,

SUM(impressions) AS impressions,

SUM(clicks) AS clicks,

SUM(clicks)/NULLIF(SUM(impressions), 0) AS CTR

FROM

yourtable.searchdata_site_impression

WHERE

data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)

GROUP BY

query,

anonymized

ORDER BY

clicks DESC

Esta consulta proporciona información sobre el rendimiento de las consultas de los usuarios durante los últimos 28 días, considerando impresiones, clics y CTR.

También considera si las consultas son anónimas o no, y los resultados se ordenan según el número total de clics en orden descendente.

Esto recrea los datos que normalmente encontraría en el informe “Rendimiento” de Search Console de los últimos 28 días de datos.resultados por consulta y diferenciación de consultas anónimas.

Siéntete libre de copiar y pegar tu camino hacia la gloria, pero asegúrate siempre de actualizar la cláusula FROM con el nombre de tabla correcto. Si tiene curiosidad por saber más sobre cómo se creó esta consulta, aquí está el desglose:

• SELECCIONAR cláusula:
  • consulta: Recupera las consultas de los usuarios.
  • is_anonymized_query AS anonimizado: cambia el nombre del campo is_anonymized_query a anonimizado.
  • SUM(impresiones) AS impresiones: Recupera el total de impresiones de cada consulta.
  • SUM(clics) COMO clics: recupera el total de clics para cada consulta.
  • SUM(clics)/NULLIF(SUM(impresiones), 0) AS CTR: Calcula la tasa de clics (CTR) para cada consulta. El uso de NULLIF evita errores de división por cero.
• DE cláusula:
  • Especifica la tabla de origen como mytable.searchconsole.searchdata_site_impression.
• DÓNDE cláusula:
  • Filtra los datos para incluir solo filas donde data_date se encuentra dentro de los últimos 28 días a partir de la fecha actual.
• GRUPO Cláusula POR:
  • Agrupa los resultados por consulta y anonimizados. Esto es necesario ya que se realizan agregaciones (SUM) y desea que los totales para cada combinación única de consulta sean anónimos.
• ORDENAR POR cláusula:
  • Ordena los resultados por el número total de clics en orden descendente.

Manejo de consultas anónimas

Según Noah Learner, la API de Google Search Console ofrece 25 veces más datos que la pestaña de rendimiento de GSC para la misma búsqueda, lo que proporciona una vista más completa.

En BigQuery, también puedes acceder a la información sobre consultas anónimas.

No omite las filas, lo que ayuda a los analistas a obtener sumas completas de impresiones y clics cuando agrega los datos.

Comprender el volumen de consultas anónimas en los datos de Google Search Console (GSC) es clave para los profesionales de SEO.

Cuando Google anonimiza una consulta, significa que el texto real de la consulta de búsqueda está oculto en los datos. Esto afecta su análisis:

• Las consultas anónimas eliminan la capacidad de analizar el lenguaje de consulta de búsqueda y extraer información sobre la intención, los temas, etc. del buscador.
• Sin los datos de la consulta, se pierden oportunidades de identificar nuevas palabras clave y oportunidades de optimización.
• No tener datos de consulta restringe su capacidad para conectar las consultas de búsqueda con el rendimiento de la página.

La primera consulta cuenta el número de anónimos vs. Consultas no anónimas

SELECT

CASE

WHEN query is NULL AND is_anonymized_query = TRUE THEN "no query"

ELSE

"query"

END

AS annonymized_query,

count(is_anonymized_query) as query_count

FROM

`yourtable.searchdata_url_impression`

GROUP BY annonymized_query

Desglose de la consulta

En este ejemplo, utilizamos una declaración CASE para verificar para cada fila si la consulta es anónima o no.

Si es así, devolvemos “sin consulta” en el campo de consulta; si no, “consulta”.

Luego contamos el número de filas…