Cómo los expertos en SEO pueden utilizar ChatGPT para BigQuery con ejemplos

La IA está dando forma a todos los campos al hacer que habilidades (como la codificación o la visualización de datos) sean accesibles para todos, lo que no estaba disponible en el pasado.

Un operador de IA que pueda ejecutar las indicaciones correctas puede realizar tareas de dificultad baja y media, lo que permite centrarse más en la toma de decisiones estratégicas.

En esta guía, le explicaremos paso a paso cómo utilizar chatbots de IA con ChatGPT como ejemplo para ejecutar consultas complejas de BigQuery para sus necesidades de informes de SEO.

Revisaremos dos ejemplos:

También le dará una idea general de cómo puede utilizar los chatbots para reducir la carga al ejecutar informes de SEO.

¿Por qué necesitas aprender BigQuery?

Las herramientas de SEO como Google Search Console o Google Analytics 4 tienen interfaces de usuario accesibles que puede utilizar para acceder a los datos. Pero a menudo limitan lo que puede hacer y muestran datos incompletos, lo que generalmente se denomina muestreo de datos.

En GSC, esto sucede porque la herramienta omite consultas anónimas y limita las filas de la tabla a hasta 1000 filas.

Captura de pantalla de Google Search Console, mayo de 2024

Al utilizar BigQuery, puede resolver ese problema y ejecutar los informes complejos que desee, eliminando el problema de muestreo de datos que ocurre con bastante frecuencia cuando se trabaja con sitios web grandes.

(Como alternativa, puedes intentar usar Looker Studio, pero el propósito de este artículo es ilustrar cómo puedes operar ChatGPT para BigQuery).

Para este artículo, asumimos que ya ha conectado sus cuentas GSC y GA4 a BigQuery. Si aún no lo has hecho, quizás quieras consultar nuestras guías sobre cómo hacerlo:

Conceptos básicos de SQL

Si conoce el lenguaje de consulta estructurado (SQL), puede omitir esta sección. Pero para aquellos que no lo saben, aquí hay una referencia rápida a las declaraciones SQL:

Las condiciones que utilizaremos para que puedas familiarizarte:

Ahora, profundicemos en ejemplos de cómo puedes usar BigQuery a través de ChatGPT.

1. Cómo Analizar TraDisminución de ffic debido al impacto del algoritmo de Google

Si te ha afectado una actualización del algoritmo de Google, lo primero que debes hacer es ejecutar informes sobre las páginas afectadas y analizar por qué te ha afectado.

Recuerde, lo peor que puede hacer es empezar a cambiar algo en el sitio web de inmediato en modo pánico. Esto puede provocar fluctuaciones en el tráfico de búsqueda y dificultar aún más el análisis del impacto.

Si tiene menos páginas en el índice, es posible que le resulte satisfactorio utilizar los datos de la interfaz de usuario de GSC para analizar sus datos, pero si tiene decenas de miles de páginas, no le permitirá exportar más de 1000 filas (ya sean páginas o consultas) de datos.

Supongamos que tiene una semana de datos desde que terminó de implementarse la actualización del algoritmo y desea compararlos con los datos de la semana anterior. Para ejecutar ese informe en BigQuery, puede comenzar con este sencillo mensaje:

Imagine you are a data analyst experienced in Google Analytics 4 (GA4), Google Search Console, SQL, and BigQuery.
Your task is to generate an SQL query to compare 'WEB' Search Console data for the periods '2024-05-08' to '2024-05-20' and '2024-04-18' to '2024-04-30'. 
Extract the total clicks, impressions, and average position for each URL for each period. 
Additionally, calculate the differences in these metrics between the periods for each URL 
(where average position should be calculated as the sum of positions divided by the sum of impressions).

Details:

BigQuery project name: use_your_bigquery_projectname
Dataset name: searchconsole
Table name: searchdata_url_impression
Please provide the SQL query that meets these requirements.

Una vez que obtenga un código SQL, cópielo y péguelo en el editor SQL de BigQuery, pero apuesto a que el código inicial que obtendrá tendrá errores. Por ejemplo, es posible que los nombres de las columnas de la tabla no coincidan con el contenido de su conjunto de datos de BigQuery.

Error en BigQuery SQL cuando el nombre de la columna no coincide con la columna del conjunto de datos.

Cosas como esta suceden con bastante frecuencia cuando se realizan tareas de codificación a través de ChatGPT. Ahora, profundicemos en cómo solucionar rápidamente problemas como este.

Simplemente haga clic en su conjunto de datos en el panel izquierdo-derecho, seleccione todas las columnas en el lado derecho y haga clic Copiar como tabla.

Cómo seleccionar todas las columnas de la tabla en BigQuery.

Una vez que lo tenga, simplemente cópielo y péguelo como mensaje de seguimiento y presione ingresar.

Generate SQL again by taking into account that my table schema is as follows : 
"fullname mode type description
data_date DATE 
site_url STRING 
url STRING 
query STRING 
is_anonymized_query BOOLEAN 
is_anonymized_discover BOOLEAN 
country STRING 
search_type STRING 
device STRING 
is_amp_top_stories BOOLEAN 
is_amp_blue_link BOOLEAN 
is_job_listing BOOLEAN 
is_job_details BOOLEAN 
is_tpf_qa BOOLEAN 
is_tpf_faq BOOLEAN 
is_tpf_howto BOOLEAN 
is_weblite BOOLEAN 
is_action BOOLEAN 
is_events_listing BOOLEAN 
is_events_details BOOLEAN 
is_search_appearance_android_app BOOLEAN 
is_amp_story BOOLEAN 
is_amp_image_result BOOLEAN 
is_video BOOLEAN 
is_organic_shopping BOOLEAN 
is_review_snippet BOOLEAN 
is_special_announcement BOOLEAN 
is_recipe_feature BOOLEAN 
is_recipe_rich_snippet BOOLEAN 
is_subscribed_content BOOLEAN 
is_page_experience BOOLEAN 
is_practice_problems BOOLEAN 
is_math_solvers BOOLEAN 
is_translated_result BOOLEAN 
is_edu_q_and_a BOOLEAN 
impressions INTEGER 
clicks INTEGER 
sum_position INTEGER 
is_product_snippets BOOLEAN 
is_merchant_listings BOOLEAN 
is_learning_videos BOOLEAN "

Esto arreglará el SQL generado de acuerdo con la estructura de la tabla GSC de la siguiente manera:

WITH period1 AS (
 SELECT 
   url,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions,
   IFNULL(SUM(sum_position) / NULLIF(SUM(impressions), 0), 0) AS avg_position
 FROM 
   `use_your_bigquery_projectname.searchconsole.searchdata_url_impression`
 WHERE 
   data_date BETWEEN '2024-05-08' AND '2024-05-20' 
 AND search_type="WEB"
  GROUP BY 
  url
), 
period2 AS (
 SELECT 
  url,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  IFNULL(SUM(sum_position) / NULLIF(SUM(impressions), 0), 0) AS avg_position
 FROM 
  `use_your_bigquery_projectname.searchconsole.searchdata_url_impression`
  WHERE 
  data_date BETWEEN '2024-04-18' AND '2024-04-30'
 AND search_type="WEB"
 GROUP BY 
 url
),
comparison AS (
 SELECT
  COALESCE(p1.url, p2.url) AS url,
  IFNULL(p1.clicks, 0) AS clicks_period1,
  IFNULL(p2.clicks, 0) AS clicks_period2,
  IFNULL(p1.impressions, 0) AS impressions_period1,
  IFNULL(p2.impressions, 0) AS impressions_period2,
  IFNULL(p1.avg_position, 0) AS avg_position_period1,
  IFNULL(p2.avg_position, 0) AS avg_position_period2,
  IFNULL(p1.clicks, 0) - IFNULL(p2.clicks, 0) AS clicks_diff,
  IFNULL(p1.impressions, 0) - IFNULL(p2.impressions, 0) AS impressions_diff,
  IFNULL(p1.avg_position, 0) - IFNULL(p2.avg_position, 0) AS avg_position_diff
 FROM
  period1 p1
 FULL OUTER JOIN
   period2 p2
 ON
  p1.url = p2.url
)
SELECT 
 url,
 clicks_period1,
 clicks_period2,
 clicks_diff,
 impressions_period1,
 impressions_period2,
 impressions_diff,
 avg_position_period1,
 avg_position_period2,
 avg_position_diff
FROM 
 comparison
ORDER BY 
 url;

Ahora, ejecútelo y disfrute de los datos exportándolos a CSV o a Google Sheets.

Cómo ejecutar SQL en BigQuery.

En caso de que tenga millones de URL, es posible que no pueda trabajar en Google Sheets o exportar CSV porque los datos son demasiado grandes. Además, esas aplicaciones tienen limitaciones en cuanto a la cantidad de filas que puede tener en un solo documento. En ese caso, puede guardar los resultados como una tabla de BigQuery y conectarse a ella con Looker Studio para ver los datos.

Pero recuerda que BigQuery es un servicio freemium. Es gratuito hasta 1 TB de datos de consultas procesadas al mes. Una vez que exceda ese límite, su tarjeta de crédito se cargará automáticamente en función de su uso.

Eso significa que si conectas tu BigQuery a Looker Studio y exploras tus datos allí, se descontarán de tu facturación cada vez que abras tu panel de Looker.

Por eso, cuando las exportaciones tienen unas pocas decenas de miles o cientos de miles de filas, me gusta usar Google Sheets. Puedo conectarlo fácilmente a Looker Studio para visualizar y combinar datos, y esto no contará en mi facturación.

Si tiene ChatGPT Plus, simplemente puede usar este GPT personalizado que he creado, que tiene en cuenta los esquemas de tablas para GA4 y Search Console. En la guía anterior, supuse que estabas usando la versión gratuita e ilustraba cómo puedes usar ChatGPT en general para ejecutar BigQuery.

En caso de que quieras saber qué hay en ese GPT personalizado, aquí tienes la captura de pantalla del backend.

GPT personalizado con esquemas de tablas de BigQuery.

Nada complicado: solo necesita copiar tablas de BigQuery como JSON en el paso explicado anteriormente y cargarlas en el GPT personalizado para que pueda hacer referencia a la estructura de la tabla. Además, hay un mensaje que le solicita a GPT que consulte los archivos JSON adjuntos al redactar consultas.

Este es otro ejemplo de cómo puedes usar ChatGPT para realizar tareas de manera más efectiva, eliminando tareas repetitivas.

Si necesita trabajar con otro conjunto de datos (diferente de GA4 o GSC) y no conoce SQL, puede cargar el esquema de la tabla de BigQuery en ChatGPT y redactar SQL específicos para esa estructura de tabla. Fácil, ¿no?

Como tarea, te sugiero analizar qué consultas se han visto afectadas por AI Overviews.

No hay un diferenciador en la tabla de Google Search Console para hacer eso, pero puede ejecutar una consulta para ver qué páginas no perdieron clasificación pero tuvieron una caída significativa en el CTR después del 14 de mayo de 2024, cuando Google introdujo las descripciones generales de IA.

Puede comparar el período de dos semanas después del 14 de mayo con las dos semanas anteriores. Todavía existe la posibilidad de que la caída del CTR se deba a otras funciones de búsqueda, como que un competidor obtenga un fragmento destacado, pero debería encontrar suficientes casos válidos en los que sus clics se hayan visto afectados por AI Overviews (anteriormente Search Generative Experience o “SGE”).

2. Cómo Combinar tiburónh Datos de tráfico con métricas de participación de GA4

Al analizar el tráfico de búsqueda, es vital comprender cuánto interactúan los usuarios con el contenido porque las señales de participación del usuario son factores de clasificación. Tenga en cuenta que no me refiero a las métricas exactas definidas en GA4.

Sin embargo, las métricas de participación de GA4, como el “tiempo promedio de participación por sesión”, que es el tiempo promedio que su sitio web estuvo enfocado en el navegador de un usuario, pueden indicar si sus artículos son lo suficientemente buenos para que los usuarios los lean.

Si es demasiado bajo, significa que las páginas de tu blog pueden tener un problema y los usuarios no las leen.

Si combina esa métrica con los datos de Search Console, puede encontrar que las páginas con clasificaciones bajas también tienen un tiempo promedio de participación por sesión bajo.

Tenga en cuenta que GA4 y GSC tienen diferentes modelos de distribución de fuentes. GA4 utiliza el modelo de atribución de último clic, lo que significa que si uno visita desde Google una página de artículo una vez y luego regresa directamente dos veces más, GA4 puede atribuir las tres visitas a Google, mientras que GSC informará solo una.

Por lo tanto, no es 100% preciso y puede que no sea adecuado para informes corporativos, pero tener métricas de participación de GA4 junto con los datos de GSC proporciona información valiosa para analizar las correlaciones de sus clasificaciones con la participación.

Usar ChatGPT con BigQuery requiere un poco de preparación. Antes de pasar al mensaje, le sugiero que lea cómo se estructuran las tablas GA4, ya que no es tan simple como las tablas de GSC.

Tiene una columna event_params, que tiene un tipo de registro y contiene dimensiones como page_location, ga_session_idy compromiso_tiempo_msec. Realiza un seguimiento de cuánto tiempo un usuario interactúa activamente con su sitio web.

clave event_params engagement_time_msec no es el tiempo total en el sitio sino el tiempo dedicado a interacciones específicas (como hacer clic o desplazarse), cuando cada interacción agrega un nuevo tiempo de participación. Es como sumar todos los pequeños momentos en los que los usuarios utilizan activamente su sitio web o aplicación.

Por lo tanto, si sumamos esa métrica y la promediamos entre sesiones para las páginas, obtenemos el tiempo de participación promedio por sesión.

Ahora, una vez que entiendas engagement_time_msec pidámosle a ChatGPT que nos ayude a construir una consulta que extraiga el “tiempo de participación promedio por sesión” de GA4 para cada URL y lo combine con los datos de rendimiento de búsqueda de artículos de GSC.

El mensaje que usaría es:

Imagine you are a data analyst experienced in Google Analytics 4 (GA4), Google Search Console, SQL, and BigQuery.
Compose a SQL query that pulls the following data from Google Search Console for each URL for the previous 7 days, excluding the current day:

1. Clicks,
2. Impressions,
3. Average position (calculated as the sum of positions divided by the sum of impressions).

From GA4 BigQuery table unnest from event_params ga_session_id, engagement_time_msec and page_location.
Select only rows which have engagement_time_msec set as not null group all sessions with the same IDs and page_location and SUM engagement_time_msec and devides to SUM of sessions number 

Join GA4 and Google Search Console data by URLs for the same period. 
Additonally Optimize the query to pull from GA4's table partitions and not query the entire table.

Details:
1. BigQuery project name: use_your_bigquery_projectname 
2. GA4 dataset name: use_your_ga4_bigquery_dataset_name
3. GA4 table name: use_your_ga4_bigquery_table_name_under_dataset
3. Google Search Console dataset name: use_your_gsc_bigquery_table_name_under_dataset
3. Google Search Console table name: use_your_gsc_bigquery_table_name_under_dataset
4. Here is BigQuery tables schemas for GA4: [copy table schema here]
5. Here is BigQuery tables schemas for Google Search Console: [copy table schema here]

Una vez que copié y pegué en BigQuery, me dio resultados en los que el “tiempo de participación promedio por sesión” era todo nulo. Entonces, aparentemente, ChatGPT necesita más contexto y orientación sobre cómo funciona GA4.

Ayudé a brindar conocimientos adicionales como una pregunta de seguimiento de la documentación oficial de GA4 sobre cómo calcula engagement_time_msec. Copié y pegué el documento en el mensaje de seguimiento y pedí consultar ese conocimiento al redactar la consulta, lo que ayudó. (Si recibe algún error de sintaxis, simplemente cópielo y péguelo como una pregunta de seguimiento y solicite que lo solucionen).

Como resultado de 15 minutos de esfuerzo, obtuve el SQL correcto:

WITH
-- Step 1: Filter GA4 data for the last 7 days and unnest event_params
ga4_data AS (
  SELECT
    event_date,
    event_timestamp,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
  FROM
    `your_bq_project_name.your_bq_ga4_dataset.events_*`
  WHERE
     _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') IS NOT NULL
),

-- Step 2: Aggregate GA4 data by session ID and page location
aggregated_ga4_data AS (
  SELECT
    page_location,
    SUM(engagement_time_msec/1000) / NULLIF(COUNT(DISTINCT ga_session_id), 0) AS avg_engagement_time_msec
  FROM
    ga4_data
  GROUP BY
    page_location
),
-- Step 3: Filter GSC data for the last 7 days and select urls which had clicks
gsc_data AS (
  SELECT
    url,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    SUM(sum_position) / SUM(impressions) AS avg_position
  FROM
    `your_bq_project_name.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    and 
    clicks > 0
  GROUP BY
    url
)

-- Joining Google Search Console data with GA4 data by page_location and url 
SELECT
  gsc.url,
  gsc.clicks,
  gsc.impressions,
  gsc.avg_position,
  ga4.avg_engagement_time_msec
FROM
  gsc_data AS gsc
LEFT JOIN
  aggregated_ga4_data AS ga4
ON
  gsc.url = ga4.page_location
ORDER BY
  gsc.clicks DESC;

Esto extrae datos de GSC con métricas de participación de GA4.

Search Console combinó datos con GA4

Tenga en cuenta que es posible que observe discrepancias entre los números en la interfaz de usuario de GA4 y los datos consultados en las tablas de BigQuery.

Esto sucede porque GA4 se centra en “Usuarios activos” y agrupa puntos de datos raros en una categoría “(otros)”, mientras que BigQuery muestra todos los datos sin procesar. GA4 también utiliza datos modelados para detectar lagunas cuando no se otorga el consentimiento, algo que BigQuery no incluye.

Además, GA4 puede muestrear datos para generar informes más rápidos, mientras que BigQuery incluye todos los datos. Estas variaciones significan que GA4 ofrece una descripción general rápida, mientras que BigQuery proporciona un análisis detallado. Obtenga una explicación más detallada de por qué sucede esto en este artículo.

Quizás pueda intentar modificar las consultas para incluir solo usuarios activos y acercar los resultados a la interfaz de usuario de GA4.

Alternativamente, puedes usar Looker Studio para combinar datos, pero tiene limitaciones con conjuntos de datos muy grandes. BigQuery ofrece escalabilidad al procesar terabytes de datos de manera eficiente, lo que lo hace ideal para informes de SEO a gran escala y análisis detallados.

Sus capacidades SQL avanzadas permiten consultas complejas para obtener información más profunda que Looker Studio u otras herramientas de paneles no pueden igualar.

Conclusión

Usar las capacidades de codificación de ChatGPT para redactar consultas de BigQuery para tus necesidades de informes te eleva y abre nuevos horizontes donde puedes combinar múltiples fuentes de datos.

Esto demuestra cómo ChatGPT puede optimizar tareas complejas de análisis de datos, permitiéndole centrarse en la toma de decisiones estratégicas.

Al mismo tiempo, estos ejemplos nos enseñaron que los humanos necesitan absolutamente operar chatbots de IA porque pueden alucinar o producir respuestas incorrectas.

Más recursos:

Imagen de portada: NicoElNino/Shutterstock