Posts Recentes:

SoftBank adquire fabricante de chips de IA do Reino Unido Graphcore

Empresa de chips do Reino Unido Gráficooré foi...

Pesquisadores do Google alertam que a IA está destruindo a internet

Um estudo feito por pesquisadores do Google aponta...

Como os especialistas em SEO podem utilizar ChatGPT para BigQuery com exemplos


A IA está moldando todos os campos, tornando acessíveis a todos habilidades (como codificação ou visualização de dados), que não estavam disponíveis no passado.

Um operador de IA que consegue executar os prompts corretos pode realizar tarefas de dificuldade de baixo e médio nível, permitindo mais foco na tomada de decisões estratégicas.

Neste guia, orientaremos você passo a passo sobre como usar chatbots de IA com ChatGPT como exemplo para executar consultas complexas do BigQuery para suas necessidades de relatórios de SEO.

Analisaremos dois exemplos:

Também lhe dará uma ideia geral de como você pode usar chatbots para reduzir a carga ao executar relatórios de SEO.

Por que você precisa aprender o BigQuery?

Ferramentas de SEO como Google Search Console ou Google Analytics 4 têm interfaces de usuário acessíveis que você pode usar para acessar dados. Mas muitas vezes eles limitam o que você pode fazer e mostram dados incompletos, o que geralmente é chamado de amostragem de dados.

No GSC, isso acontece porque a ferramenta omite consultas anônimas e limita as linhas da tabela a até 1.000 linhas.

Captura de tela do GSC Captura de tela do Google Search Console, maio de 2024

Ao usar o BigQuery, você pode resolver esse problema e executar quaisquer relatórios complexos que desejar, eliminando o problema de amostragem de dados que ocorre com frequência ao trabalhar com sites grandes.

(Como alternativa, você pode tentar usar o Looker Studio, mas o objetivo deste artigo é ilustrar como operar o ChatGPT para BigQuery.)

Para este artigo, presumimos que você já conectou suas contas GSC e GA4 ao BigQuery. Se você ainda não fez isso, verifique nossos guias sobre como fazer:

Noções básicas de SQL

Se você conhece a Linguagem de Consulta Estruturada (SQL), pode pular esta seção. Mas para quem não sabe, aqui está uma referência rápida às instruções SQL:

DeclaraçãoDescrição
SELECIONARRecupera dados de tabelas
INSERIRInsere novos dados em uma tabela
DESCONECTARAchata um array em um conjunto de linhas
ATUALIZARAtualiza os dados existentes em uma tabela
EXCLUIRExclui dados de uma tabela
CRIARCria uma nova tabela ou banco de dados
ALTERARModifica uma tabela existente
DERRUBARExclui uma tabela ou banco de dados.

As condições que utilizaremos para que você se familiarize:

DoençaDescrição
ONDEFiltra registros para condições específicas
ECombina duas ou mais condições onde todas as condições devem ser verdadeiras
OUCombina duas ou mais condições onde pelo menos uma condição deve ser verdadeira
NÃONega uma condição
COMOPesquisa um padrão especificado em uma coluna.
EMVerifica se um valor está dentro de um conjunto de valores
ENTRESelecione valores dentro de um determinado intervalo
É NULOVerifica se há valores nulos
NÃO É NULOVerifica se há valores não nulos
EXISTEVerifica se uma subconsulta retorna algum registro

Agora, vamos ver exemplos de como você pode usar o BigQuery via ChatGPT.

1. Como fazer Analisar TraDeclínio de fic devido ao impacto do algoritmo do Google

Se você foi afetado por uma atualização de algoritmo do Google, a primeira coisa que você deve fazer é gerar relatórios nas páginas afetadas e analisar por que você foi afetado.

Lembre-se de que a pior coisa que você pode fazer é começar a mudar algo no site imediatamente, em modo de pânico. Isso pode causar flutuações no tráfego de pesquisa e dificultar ainda mais a análise do impacto.

Se você tiver menos páginas no índice, poderá achar o uso dos dados da UI do GSC satisfatório para analisar seus dados, mas se tiver dezenas de milhares de páginas, não permitirá exportar mais de 1.000 linhas (páginas ou consultas) de dados.

Digamos que você tenha uma semana de dados desde que a atualização do algoritmo foi concluída e deseja compará-los com os dados da semana anterior. Para executar esse relatório no BigQuery, você pode começar com este prompt simples:

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.

Depois de obter um código SQL, copie e cole-o no editor SQL do BigQuery, mas aposto que o código inicial que você receberá terá erros. Por exemplo, os nomes das colunas da tabela podem não corresponder ao que está no seu conjunto de dados do BigQuery.

Erro no BigQuery SQL quando o nome da coluna não corresponde à coluna do conjunto de dados.Erro no BigQuery SQL quando o nome da coluna não corresponde à coluna do conjunto de dados.

Coisas assim acontecem com bastante frequência ao realizar tarefas de codificação via ChatGPT. Agora, vamos ver como você pode corrigir rapidamente problemas como esse.

Basta clicar em seu conjunto de dados no painel esquerdo-direito, selecionar todas as colunas no lado direito e clicar Copiar como tabela.

Como selecionar todas as colunas da tabela no bigquery.Como selecionar todas as colunas da tabela no BigQuery.

Depois de obtê-lo, basta copiá-lo e colá-lo como um prompt de acompanhamento e clicar digitar.

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 "

Isso corrigirá o SQL gerado de acordo com a estrutura da tabela GSC da seguinte forma:

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;

Agora, execute-o e aproveite os dados exportando-os para CSV ou Planilhas Google.

Como executar SQL no BigQueryComo executar SQL no BigQuery.

Caso você tenha milhões de URLs, talvez não consiga trabalhar no Planilhas Google ou na exportação de CSV porque os dados são muito grandes. Além disso, esses aplicativos têm limitações quanto ao número de linhas que você pode ter em um único documento. Nesse caso, você pode salvar os resultados como uma tabela do BigQuery e conectar-se a ela com o Looker Studio para visualizar os dados.

Mas lembre-se de que o BigQuery é um serviço freemium. É gratuito até 1 TB de dados de consulta processados ​​por mês. Depois de exceder esse limite, seu cartão de crédito será cobrado automaticamente com base no seu uso.

Isso significa que se você conectar seu BigQuery ao Looker Studio e navegar por seus dados lá, eles serão contabilizados em seu faturamento sempre que você abrir o painel do Looker.

É por isso que, quando as exportações têm algumas dezenas de milhares ou centenas de milhares de linhas, gosto de usar o Planilhas Google. Posso conectá-lo facilmente ao Looker Studio para visualização e combinação de dados, e isso não contará no meu faturamento.

Se você possui ChatGPT Plus, pode simplesmente usar este GPT personalizado que criei, que leva em consideração esquemas de tabela para GA4 e Search Console. No guia acima, presumi que você estava usando a versão gratuita e ilustrou como você pode usar o ChatGPT em geral para executar o BigQuery.

Caso você queira saber o que há nesse GPT customizado, aqui está a captura de tela do backend.

GPT personalizada com esquemas de tabela do bigQueryGPT personalizado com esquemas de tabela do BigQuery.

Nada complicado – você só precisa copiar tabelas do BigQuery como JSON na etapa explicada acima e carregá-las no GPT personalizado para que possa fazer referência à estrutura da tabela. Além disso, há um prompt que solicita à GPT que consulte os arquivos JSON anexados ao redigir consultas.

Esta é mais uma ilustração de como você pode usar o ChatGPT para realizar tarefas de forma mais eficaz, eliminando tarefas repetitivas.

Se você precisar trabalhar com outro conjunto de dados (diferente de GA4 ou GSC) e não souber SQL, poderá fazer upload do esquema da tabela do BigQuery para o ChatGPT e compor SQLs específicos para essa estrutura de tabela. Fácil, não é?

Como lição de casa, sugiro que você analise quais consultas foram afetadas pelas visões gerais de IA.

Não há nenhum diferenciador na tabela do Google Search Console para fazer isso, mas você pode executar uma consulta para ver quais páginas não perderam classificação, mas tiveram uma queda significativa de CTR após 14 de maio de 2024, quando o Google introduziu as visões gerais de IA.

Você pode comparar o período de duas semanas após 14 de maio com as duas semanas anteriores. Ainda existe a possibilidade de que a queda da CTR tenha ocorrido devido a outros recursos de pesquisa, como um concorrente obtendo um snippet em destaque, mas você deve encontrar casos válidos suficientes em que seus cliques foram afetados por visões gerais de IA (anteriormente Search Generative Experience ou “SGE”).

2. Como fazer Combinar pesquisah Dados de tráfego com métricas de engajamento do GA4

Ao analisar o tráfego de pesquisa, é vital compreender o quanto os usuários se envolvem com o conteúdo porque os sinais de envolvimento do usuário são fatores de classificação. Observe que não me refiro às métricas exatas definidas no GA4.

No entanto, as métricas de engajamento do GA4 – como “tempo médio de engajamento por sessão”, que é o tempo médio que seu site ficou em foco no navegador do usuário – podem indicar se seus artigos são bons o suficiente para os usuários lerem.

Se estiver muito baixo, significa que as páginas do seu blog podem estar com problemas e os usuários não as leem.

Se você combinar essa métrica com os dados do Search Console, poderá descobrir que páginas com classificações baixas também têm um tempo médio de engajamento por sessão baixo.

Observe que GA4 e GSC têm modelos de atribuição diferentes. O GA4 usa modelos de atribuição baseados em últimos dados ou de último clique, o que significa que se alguém visitar a página de um artigo do Google uma vez e depois voltar diretamente mais duas vezes, o GA4 poderá atribuir todas as três visitas ao Google, enquanto o GSC reportará apenas uma.

Portanto, não é 100% preciso e pode não ser adequado para relatórios corporativos, mas ter métricas de engajamento do GA4 junto com dados do GSC fornece informações valiosas para analisar as correlações de suas classificações com o engajamento.

Usar o ChatGPT com o BigQuery requer um pouco de preparação. Antes de entrarmos no prompt, sugiro que você leia como as tabelas GA4 são estruturadas, pois não são tão simples quanto as tabelas do GSC.

Possui uma coluna event_params, que possui um tipo de registro e contém dimensões como page_location, ga_session_ide engagement_time_msec. Ele rastreia por quanto tempo um usuário interage ativamente com seu site.

chave event_params engagement_time_msec não é o tempo total no site, mas o tempo gasto em interações específicas (como clicar ou rolar), quando cada interação adiciona um novo tempo de engajamento. É como somar todos os pequenos momentos em que os usuários estão usando ativamente seu site ou aplicativo.

Portanto, se somarmos essa métrica e fizermos a média entre as sessões das páginas, obteremos o tempo médio de engajamento por sessão.

Agora, uma vez que você entende engagement_time_msec vamos pedir ao ChatGPT para nos ajudar a construir uma consulta que extraia o “tempo médio de engajamento por sessão” do GA4 para cada URL e o combine com os dados de desempenho de pesquisa de artigos do GSC.

O prompt que eu usaria é:

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]



Depois de copiar e colar no BigQuery, obtive resultados com “tempo médio de engajamento por sessão” sendo todos nulos. Então, aparentemente, o ChatGPT precisa de mais contexto e orientação sobre como o GA4 funciona.

Ajudei a fornecer conhecimento adicional como uma pergunta de acompanhamento da documentação oficial do GA4 sobre como ele calcula engagement_time_msec. Copiei e colei o documento no prompt de acompanhamento e pedi para consultar esse conhecimento ao redigir a consulta, o que ajudou. (Se você receber algum erro de sintaxe, basta copiá-lo/colá-lo como uma pergunta de acompanhamento e pedir para corrigi-lo.)

Como resultado de 15 minutos de esforço, obtive o SQL correto:


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;

Isso extrai dados do GSC com métricas de engajamento do GA4.

Dados combinados do Search Console com GA4Dados combinados do Search Console com GA4

Observe que você pode notar discrepâncias entre os números na IU do GA4 e os dados consultados nas tabelas do BigQuery.

Isso acontece porque o GA4 se concentra em “Usuários ativos” e agrupa pontos de dados raros em uma categoria “(outros)”, enquanto o BigQuery mostra todos os dados brutos. O GA4 também usa dados modelados para lacunas quando o consentimento não é dado, o que o BigQuery não inclui.

Além disso, o GA4 pode coletar amostras de dados para relatórios mais rápidos, enquanto o BigQuery inclui todos os dados. Essas variações significam que o GA4 oferece uma visão geral rápida, enquanto o BigQuery fornece uma análise detalhada. Aprenda uma explicação mais detalhada de por que isso acontece neste artigo.

Talvez você possa tentar modificar as consultas para incluir apenas usuários ativos para aproximar os resultados da IU do GA4.

Como alternativa, você pode usar o Looker Studio para combinar dados, mas ele tem limitações com conjuntos de dados muito grandes. O BigQuery oferece escalabilidade ao processar terabytes de dados com eficiência, tornando-o ideal para relatórios de SEO em grande escala e análises detalhadas.

Seus recursos SQL avançados permitem consultas complexas para insights mais profundos que o Looker Studio ou outras ferramentas de painel não conseguem igualar.

Conclusão

Usar as habilidades de codificação do ChatGPT para compor consultas do BigQuery para suas necessidades de relatórios eleva você e abre novos horizontes onde você pode combinar várias fontes de dados.

Isso demonstra como o ChatGPT pode agilizar tarefas complexas de análise de dados, permitindo que você se concentre na tomada de decisões estratégicas.

Ao mesmo tempo, estes exemplos ensinaram-nos que os humanos precisam absolutamente de operar chatbots de IA porque podem ter alucinações ou produzir respostas erradas.

Mais recursos:


Imagem em destaque: NicoElNino/Shutterstock

Últimas

Câmeras Waymo capturam imagens de pessoa acusada de supostos cortes de pneus de robotaxi

Um morador de Castro Valley foi acusado na...

SoftBank adquire fabricante de chips de IA do Reino Unido Graphcore

Empresa de chips do Reino Unido Gráficooré foi...

Pesquisadores do Google alertam que a IA está destruindo a internet

Um estudo feito por pesquisadores do Google aponta...

César Lattes, físico e professor brasileiro, é homenageado no Google

Hoje, o Google celebra com um Doodle animado...

Assine

spot_img

Veja Também

Câmeras Waymo capturam imagens de pessoa acusada de supostos cortes de pneus de robotaxi

Um morador de Castro Valley foi acusado na...

SoftBank adquire fabricante de chips de IA do Reino Unido Graphcore

Empresa de chips do Reino Unido Gráficooré foi...

Pesquisadores do Google alertam que a IA está destruindo a internet

Um estudo feito por pesquisadores do Google aponta...

César Lattes, físico e professor brasileiro, é homenageado no Google

Hoje, o Google celebra com um Doodle animado...

O WhatsApp agora permite que empresas enviem códigos de autenticação para usuários na Índia

O WhatsApp agora permite que empresas enviem códigos...
spot_img

Câmeras Waymo capturam imagens de pessoa acusada de supostos cortes de pneus de robotaxi

Um morador de Castro Valley foi acusado na quinta-feira por supostamente cortar os pneus de 17 robotaxis Waymo em São Francisco entre 24...

SoftBank adquire fabricante de chips de IA do Reino Unido Graphcore

Empresa de chips do Reino Unido Gráficooré foi formalmente adquirida pelo SoftBank do Japão.Rumores do acordo abundam há algum tempomas negociações prolongadas e...

Pesquisadores do Google alertam que a IA está destruindo a internet

Um estudo feito por pesquisadores do Google aponta que a IA generativa, a mesma que a gigante de Mountain View está super incentivando,...