Comience a realizar consultas de GSC en BigQuery

Este enfoque SEO de BigQuery responde a la pregunta: ¿Qué hago una vez que conecto BigQuery a Google Search Console?

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! BigQuery

¿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, 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. official documentation

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. three distinct schemas

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

• searchdata_site_impression: contiene datos de rendimiento de su propiedad agregados por propiedad.
• searchdata_url_impression: contiene datos de rendimiento de su propiedad agregados por URL.
• exportLog: 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 individuales de información, el tipo específico de datos en una tabla. Si se tratara de un archivo de Excel, nos referiríamos a los campos como columnas en 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 promedio 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».

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

En Explorer, cuando abra su proyecto, verá los conjuntos de datos; 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 «CONSULTA» 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.

Comenzando con su primera consulta

Search Console > BigQuery export was previously only available to companies with devs/ a super techy SEO. Now it’s available to everyone!

Writing SQL is a more and more important skill for marketers & I’m making something to help with that – if you’d like to test it DM me 🙂 https://t.co/voOESJfo1e

— Robin Lord (@RobinLord8) February 21, 2023

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 https://t.co/voOESJfo1e

– Robin Lord (@RobinLord8) 21 de febrero de 2023 February 21, 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; costs should be low

• 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 declaració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:

SELECCIONAR *

DESDE `tus datos.consola de búsqueda.impresión_sitio_datos de búsqueda`

DONDE fecha_datos = ‘2023-12-31’

LÍMITE 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:

SELECCIONAR

dirección URL,

SUMA(clics) COMO clics,

SUMA(impresiones)

DE

`tutabla.searchdata_url_impression`

DÓNDE

fecha_datos = ‘2023-12-25’

AGRUPAR POR

URL

ORDENAR POR

clics DESC

LÍMITE

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 leer 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. Esto es un poco más avanzado que lo que cubrimos aquí.

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

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

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

SELECCIONAR

consulta,

is_anonymized_query AS anonimizado,

SUMA(impresiones) COMO impresiones,

SUMA(clics) COMO clics,

SUM(clics)/NULLIF(SUM(impresiones), 0) COMO CTR

DE

yourtable.searchdata_site_impression

DÓNDE

data_date >= DATE_SUB(CURRENT_DATE(), INTERVALO 28 DÍAS)

AGRUPAR POR

consulta,

anonimizado

ORDENAR POR

clics 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 de «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:

• Cláusula SELECCIONAR: El uso de NULLIF evita errores de división por cero.
• 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.
• Cláusula DE:
• 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.
• Cláusula GRUPO POR: Esto es necesario ya que se realizan agregaciones (SUM) y desea que los totales para cada combinación única de consulta sean anónimos.
• 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.
• Cláusula ORDEN POR:
• Ordena los resultados por el número total de clics en orden descendente.

• 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.

• Especifica la tabla de origen como mytable.searchconsole.searchdata_site_impression.

• 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.

• 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.

• 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. Noah Learner

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 vs anonimizados Consultas no anónimas

SELECCIONAR

CASO

CUANDO la consulta es NULL AND is_anonymized_query = TRUE ENTONCES «sin consulta»

DEMÁS

«consulta»

FIN

COMO consulta_anonimizada,

contar (is_anonymized_query) como query_count

DE

`tutabla.searchdata_url_impression`

GRUPO POR consulta_anonimizada

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;

Luego contamos el número de filas que tiene cada tipo de consulta en la tabla y agrupamos los resultados en función de cada una de ellas. Así es como se ve el resultado:

Consultas avanzadas para obtener información sobre SEO

BigQuery permite análisis complejos que no se pueden realizar en la interfaz de GSC Esto significa que también puede crear información personalizada al revelar patrones en el comportamiento del usuario.

Puede analizar las tendencias de búsqueda, la estacionalidad a lo largo del tiempo y las oportunidades de optimización de palabras clave.

Aquí hay algunas cosas que debe tener en cuenta para ayudarlo a depurar los filtros que implemente:

• La fecha podría ser un problema. Pueden pasar hasta dos días hasta que tengas los datos que deseas consultar Si BigQuery dice en la esquina superior derecha que su consulta requeriría 0 MB para ejecutarse, significa que los datos que desea aún no están disponibles o que no hay datos para su consulta.
• Utilice la vista previa si desea ver qué devolverá un campo en términos de valor Te muestra una tabla con los datos.
• Las abreviaturas de países que obtendrás en BigQuery están en un formato diferente (formato ISO-3166-1-Alpha-3) al que estás acostumbrado. Algunos ejemplos: FRA para Francia, UKR para Ucrania, USA para Estados Unidos, etc.
• ¿Quiere recibir consultas «bonitas»?
• Si desea recibir más consultas de inmediato, le sugiero que se suscriba al boletín de SEOlytics, ya que hay bastantes consultas SQL que puede utilizar.

Conclusión

El análisis de datos de GSC en BigQuery desbloquea conocimientos de SEO transformadores, lo que te permite realizar un seguimiento del rendimiento de la búsqueda a escala.

Si sigue las mejores prácticas descritas aquí para consultar, optimizar y solucionar problemas, podrá aprovechar al máximo este potente conjunto de datos.

Leer esto no te convertirá en un experto al instante ¡Este es el primer paso en tu aventura!

Si quieres saber más, consulta la publicación del blog de Jake Peterson, comienza a practicar gratis con el juego Lost at SQL de Robin Lord o simplemente estad atentos porque tengo algunos artículos más en camino. Jake Peterson’s blog post

Si tienes dudas o consultas no dudes en hacérnoslo saber.

Más recursos:

Imagen de portada: Tee11/Shutterstock

Leer el articulo original en Search Engine Journal.