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.
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:
| Declaración | Descripción |
| SELECCIONAR | Recupera datos de tablas. |
| INSERTAR | Inserta nuevos datos en una tabla. |
| A MENOS QUE | Aplana una matriz en un conjunto de filas |
| ACTUALIZAR | Actualiza los datos existentes dentro de una tabla. |
| BORRAR | Elimina datos de una tabla. |
| CREAR | Crea una nueva tabla o base de datos. |
| ALTERAR | Modifica una tabla existente |
| GOTA | Elimina una tabla o una base de datos. |
Las condiciones que utilizaremos para que puedas familiarizarte:
| Condición | Descripción |
| DÓNDE | Filtra registros para condiciones específicas |
| Y | Combina dos o más condiciones donde todas las condiciones deben ser verdaderas |
| O | Combina dos o más condiciones donde al menos una condición debe ser verdadera |
| NO | Niega una condición |
| COMO | Busca un patrón específico en una columna. |
| EN | Comprueba si un valor está dentro de un conjunto de valores. |
| ENTRE | Seleccionar valores dentro de un rango determinado |
| ES NULO | Comprueba valores nulos |
| NO ES NULO | Comprueba valores no nulos |
| EXISTE | Comprueba si una subconsulta devuelve algún registro. |
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 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 el 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 su 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 un 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% exacto 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 GA4Tenga 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 poco comunes 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
