Search Console API com Python e BigQuery: Como Superar 1.000 Linhas e Detectar Quedas de CTR

A interface gráfica do Google Search Console impõe uma barreira invisível para quem gerencia portais de grande porte, e-commerces com milhares de produtos ou operações de conteúdo com alto volume de tráfego. Ao exportar dados manualmente pela interface web ou por planilhas, o sistema corta a listagem em exatamente 1.000 linhas. Para sites com histórico relevante, essas 1.000 linhas representam apenas as palavras-chave com maior volume imediato, ocultando dezenas de milhares de termos de cauda longa e variações semânticas que compõem a maior fatia do tráfego orgânico.

Trabalhar apenas com os dados visíveis no painel web impede a identificação de comportamentos anômalos em termos específicos. A perda de posições em palavras secundárias ou quedas abruptas de taxa de clique (CTR) em páginas estratégicas passam despercebidas até que a queda agregada atinja a receita da empresa.

Integrar a Search Console API com Python e carregar esses registros brutos no Google BigQuery resolve essa limitação de ponta a ponta. Com um pipeline de extração estruturado em scripts automatizados, é possível contornar a amostragem, extrair até 25.000 linhas por requisição e, por meio de paginação diária por data e offset, recuperar centenas de milhares de linhas por dia. Uma vez armazenados no BigQuery, consultas SQL avançadas permitem cruzar dados históricos, isolar quedas de CTR em posições estáveis e identificar canibalização de palavras-chave ou a interferência de novos elementos da SERP.

Por que a interface web do Search Console limita a visão de dados

O painel do Google Search Console foi desenhado para diagnósticos pontuais de usabilidade e consultas agregadas. A infraestrutura que sustenta a interface web precisa entregar respostas visuais em segundos para milhões de propriedades simultâneas, o que justifica a restrição rígida de exibição de dados.

A barreira de 1.000 linhas na interface e no Google Planilhas

Quando você acessa o relatório de Desempenho no Search Console e aplica um filtro amplo de datas, a tabela inferior exibe as consultas ordenadas por cliques ou impressões. O seletor de paginação no rodapé permite avançar até no máximo 1.000 registros.

Se você clicar no botão de exportação e selecionar CSV ou Google Planilhas, o arquivo gerado conterá exatamente essas mesmas 1.000 linhas. As consultas posicionadas da linha 1.001 em diante são descartadas na exportação da interface, mesmo que o site registre 60.000 termos distintos que receberam impressões naquele mesmo intervalo.

Essa restrição prejudica diretamente:

Portais de comércio eletrônico com navegação facetada. Milhares de variações de filtros, marcas e categorias têm suas consultas de busca ocultadas pelo teto de registros.
Plataformas de tecnologia e blogs técnicos B2B. Consultas de cauda longa com termos técnicos específicos, que possuem baixa frequência individual mas altíssima intenção de conversão comercial, ficam inacessíveis.
Sites com segmentação regional ou múltiplos diretórios. A agregação nacional mascara termos regionais específicos em favor das grandes cidades com maior densidade demográfica.

Amostragem e perda da cauda longa orgânica

A perda das linhas não visíveis não afeta apenas o volume absoluto de palavras-chave catalogadas. Ela distorce o cálculo real de CTR por agrupamento de tópicos. Quando as consultas de cauda longa somem do relatório, a média de taxa de clique exibida no gráfico superior passa a refletir apenas o comportamento dos termos de marca e das palavras-chave mais concorridas da cabeça da cauda.

Além disso, o Search Console omite consultas com volume residual de impressões para proteger a privacidade dos usuários. No entanto, mesmo entre as consultas elegíveis que superam o limiar de privacidade, a interface web descarta tudo o que excede a milésima posição da tabela. Para auditar com precisão o inventário completo de buscas, a extração programática via API é a única alternativa viável.

Como a Search Console API opera com Python

A Search Console API oferece um endpoint específico para relatórios de desempenho denominado searchanalytics.query. Diferente da interface gráfica, a API permite configurar parâmetros avançados de filtragem, granularidade e paginação.

Limites oficiais de requisição e cotas da API

Ao interagir com o endpoint de Search Analytics, você deve considerar três limites estabelecidos pela documentação oficial da Google Cloud Platform:

Limite por requisição única. O parâmetro rowLimit aceita um valor máximo de 25.000 linhas por chamada.
Offset de paginação via startRow. A API suporta o parâmetro startRow em base zero para indicar onde a fatia de registros deve começar.
Cota de requisições por minuto. As cotas padrão permitem até 1.200 consultas por minuto por projeto no GCP, com restrições adicionais de taxa por propriedade individual no Search Console.

A combinação de rowLimit configurado em 25.000 com incrementos sequenciais no startRow em intervalos de 25.000 registros permite esgotar os dados disponíveis para uma dimensão. No entanto, tentar puxar um intervalo agregado de três meses em um único lote fará com que a API interrompa os resultados precocemente ao atingir os tetos internos de agrupamento do Google. A melhor prática consiste em fatiar as chamadas por dia individual.

Estrutura de dimensões e agregação por página e consulta

Para obter a visão mais detalhada possível, a requisição deve solicitar as dimensões combinadas:

date (data exata do evento)
page (URL canônica de destino)
query (termo de pesquisa exato)
country (país de origem da busca)
device (dispositivo móvel, desktop ou tablet)

Ao combinar page e query no mesmo array de dimensões, o Search Console quebra cada par URL-termo em uma linha independente. Isso é fundamental para identificar canibalização, pois permite verificar se uma mesma consulta gerou impressões para duas ou mais páginas diferentes no mesmo dia.

Configuração do ambiente no Google Cloud e autenticação

Antes de escrever o código em Python, é necessário criar as credenciais adequadas no console do Google Cloud Platform (GCP).

Criação do projeto e da conta de serviço no GCP

A autenticação recomendada para rotinas de backend e scripts agendados é o uso de uma Conta de Serviço (Service Account). Esse modelo dispensa a necessidade de login interativo via navegador a cada execução.

O processo de configuração segue estas etapas:

Acesse o Console do Google Cloud e crie um novo projeto dedicado a dados e SEO.
No menu de APIs e Serviços, clique em Biblioteca e ative a Google Search Console API e a BigQuery API.
Em Credenciais, clique em Criar Credenciais e selecione Conta de serviço.
Defina um nome descritivo para a conta de serviço, como gsc-bigquery-extractor.
Na aba de Chaves da conta de serviço criada, clique em Adicionar Chave e escolha o formato JSON. Faça o download seguro do arquivo.

Delegação de permissões na propriedade do Search Console

Com o arquivo JSON da conta de serviço em mãos, copie o endereço de e-mail gerado pelo GCP, que possui o sufixo @<nome-do-projeto>.iam.gserviceaccount.com.

Em seguida:

Acesse o Google Search Console com a conta proprietária do domínio.
No menu lateral esquerdo, selecione a propriedade desejada e clique em Configurações.
Selecione Usuários e permissões.
Clique em Adicionar usuário, cole o e-mail da conta de serviço e conceda a permissão de Proprietário ou Total.

Script Python para extração diária sem truncamento

Para contornar o limite e extrair todo o inventário diário, o script em Python itera data por data. Para cada dia do calendário, ele executa um loop de paginação utilizando startRow até que a resposta retorne menos registros do que o rowLimit solicitado.

As bibliotecas necessárias para executar a rotina são instaladas via pip:

pip install google-api-python-client google-auth google-cloud-bigquery pandas

Abaixo está o código completo do módulo extrator:

import time
from datetime import datetime, timedelta
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

SERVICE_ACCOUNT_FILE = "credentials/gsc-credentials.json"
SCOPES = ["https://www.googleapis.com/auth/webmasters.readonly"]
SITE_URL = "sc-domain:cayogalotti.com.br"


def get_gsc_service():
    credentials = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES
    )
    return build("searchconsole", "v1", credentials=credentials)


def fetch_gsc_data_for_date(service, target_date):
    rows_collected = []
    start_row = 0
    row_limit = 25000
    date_str = target_date.strftime("%Y-%m-%d")

    while True:
        request_body = {
            "startDate": date_str,
            "endDate": date_str,
            "dimensions": ["date", "page", "query", "country", "device"],
            "rowLimit": row_limit,
            "startRow": start_row,
            "dataState": "final",
        }

        try:
            response = (
                service.searchanalytics()
                .query(siteUrl=SITE_URL, body=request_body)
                .execute()
            )
        except HttpError as err:
            if err.resp.status in [429, 500, 503]:
                time.sleep(10)
                continue
            raise err

        rows = response.get("rows", [])
        if not rows:
            break

        for row in rows:
            keys = row.get("keys", [])
            rows_collected.append(
                {
                    "data_date": keys[0],
                    "page": keys[1],
                    "query": keys[2],
                    "country": keys[3],
                    "device": keys[4],
                    "clicks": int(row.get("clicks", 0)),
                    "impressions": int(row.get("impressions", 0)),
                    "ctr": float(row.get("ctr", 0.0)),
                    "position": float(row.get("position", 0.0)),
                }
            )

        start_row += len(rows)
        if len(rows) < row_limit:
            break

        time.sleep(0.5)

    return rows_collected

O parâmetro dataState configurado com o valor final garante que o script busque apenas dados consolidados, evitando incongruências provocadas pelos dados preliminares dos últimos dois dias.

Modelagem e ingestão de dados no Google BigQuery

Despejar milhões de linhas no BigQuery sem planejamento prévio de arquitetura gera custos elevados de consulta e lentidão em relatórios analíticos. O schema precisa ser desenhado considerando a frequência de leitura dos analistas de SEO.

Criação do dataset e schema otimizado

No BigQuery, as melhores práticas de engenharia de dados recomendam aplicar particionamento e agrupamento:

Particionamento por data. Ao particionar a tabela pela coluna data_date, o motor do BigQuery analisa apenas os blocos de disco correspondentes aos dias solicitados na cláusula WHERE, reduzindo o custo em gigabytes processados.
Agrupamento por página e consulta. O clustering interno organiza os blocos de armazenamento em ordem física pelas colunas page e query. Consultas que filtram uma URL específica leem frações mínimas da partição.

O comando DDL abaixo pode ser executado diretamente no console do BigQuery para estruturar a tabela:

CREATE TABLE IF NOT EXISTS `projeto_seo.search_console.desempenho_diario` (
  data_date DATE NOT NULL,
  page STRING NOT NULL,
  query STRING NOT NULL,
  country STRING NOT NULL,
  device STRING NOT NULL,
  clicks INT64 NOT NULL,
  impressions INT64 NOT NULL,
  ctr FLOAT64 NOT NULL,
  position FLOAT64 NOT NULL
)
PARTITION BY data_date
CLUSTER BY page, query;

Essa modelagem de particionamento e clustering segue o mesmo padrão de governança analítica que utilizamos na exportação do GA4 para o BigQuery, permitindo unificar no futuro a jornada do clique orgânico com o comportamento de navegação da sessão.

Pipeline de inserção com carregamento idempotente em Python

A ingestão de dados pode ser realizada via streaming ou via carga em lote. Para pipelines diários de SEO, a carga em lote é superior, pois não gera custos adicionais de streaming e permite garantir idempotência.

Se um script falhar na metade ou precisar ser reexecutado para uma data passada, a tabela não deve acumular registros duplicados. Usamos o modo WRITE_TRUNCATE direcionado a uma partição de data específica:

from google.cloud import bigquery


def load_partition_to_bigquery(rows_data, target_date):
    if not rows_data:
        return

    client = bigquery.Client()
    date_str = target_date.strftime("%Y-%m-%d")
    table_id = "projeto_seo.search_console.desempenho_diario"
    partition_target = f"{table_id}${date_str.replace('-', '')}"

    job_config = bigquery.LoadJobConfig(
        schema=[
            bigquery.SchemaField("data_date", "DATE", mode="REQUIRED"),
            bigquery.SchemaField("page", "STRING", mode="REQUIRED"),
            bigquery.SchemaField("query", "STRING", mode="REQUIRED"),
            bigquery.SchemaField("country", "STRING", mode="REQUIRED"),
            bigquery.SchemaField("device", "STRING", mode="REQUIRED"),
            bigquery.SchemaField("clicks", "INT64", mode="REQUIRED"),
            bigquery.SchemaField("impressions", "INT64", mode="REQUIRED"),
            bigquery.SchemaField("ctr", "FLOAT64", mode="REQUIRED"),
            bigquery.SchemaField("position", "FLOAT64", mode="REQUIRED"),
        ],
        write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
        source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,
    )

    load_job = client.load_table_from_json(
        rows_data, partition_target, job_config=job_config
    )
    load_job.result()

O uso de decorators de partição com WRITE_TRUNCATE substitui exclusivamente os registros do dia especificado, mantendo os demais dias intocados e imunes a duplicidades.

Por que o CTR desaba mesmo com a posição média intacta

Um dos erros mais comuns de auditoria SEO é presumir que a perda de cliques orgânicos decorre unicamente de perdas de ranqueamento. Em monitoramentos baseados no painel web, analistas costumam verificar apenas se a posição média caiu de 2,4 para 3,1.

Contudo, é frequente observar cenários em que a posição média registrada pelo Google Search Console permanece rigorosamente estável, oscilando entre 1,8 e 2,0, enquanto a taxa de clique cai de 28% para 9%.

Existem quatro causas técnicas que explicam essa desconexão:

Expansão de resumos gerados por inteligência artificial. A introdução de respostas diretas na SERP, como as novidades de Google AI Overviews, empurra os links orgânicos tradicionais para baixo da primeira dobra da tela. O usuário obtém a resposta sintetizada no topo e encerra a consulta sem clicar, inflando as impressões e derrubando o CTR mesmo com a URL na primeira posição técnica.
Concorrência com múltiplos blocos patrocinados. Para termos comerciais concorridos, o Google pode intercalar até quatro anúncios patrocinados de topo acompanhados de extensões de imagem e carrosséis de produtos, empurrando o primeiro resultado orgânico para mais de 1.200 pixels abaixo do topo visual da tela.
Canibalização interna e fragmentação de snippets. Quando duas páginas do mesmo domínio concorrem pela mesma palavra-chave, o Google alterna as URLs na SERP. Essa oscilação impede a fixação de sitelinks e reduz a autoridade visual do domínio, dispersando a atenção do visitante.
Desconexão entre título e intenção de busca. Alterações no comportamento do usuário ou atualizações de snippets automáticos pelo Google podem tornar o título menos atraente do que os concorrentes adjacentes.

Auditar essa dinâmica em escala exige modelos estatísticos que relacionem a posição média ao CTR esperado, algo impossível de executar na interface limitada a 1.000 linhas.

Consultas SQL no BigQuery para detectar anomalias e quedas de CTR

Com os dados diários consolidados no BigQuery, podemos construir consultas analíticas para calcular desvios de taxa de clique em grande escala.

Criação da curva de referência de CTR esperado por posição

O primeiro passo analítico é construir uma tabela de linha de base com o CTR médio observado em cada faixa de posição para o domínio. Cada segmento de mercado possui uma curva própria, influenciada pela intensidade de marca e pelo tipo de busca.

A consulta abaixo calcula a média de CTR para cada posição do top 10:

WITH metricas_base AS (
  SELECT
    ROUND(position, 0) AS faixa_posicao,
    impressions,
    clicks
  FROM
    `projeto_seo.search_console.desempenho_diario`
  WHERE
    data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND position <= 10.0
    AND impressions >= 50
)
SELECT
  faixa_posicao,
  SUM(clicks) AS total_cliques,
  SUM(impressions) AS total_impressoes,
  ROUND(SUM(clicks) / NULLIF(SUM(impressions), 0), 4) AS ctr_medio_observado
FROM
  metricas_base
GROUP BY
  faixa_posicao
ORDER BY
  faixa_posicao ASC;

Essa tabela serve como régua de comparação. Se o histórico do domínio demonstra que a posição 1 gera em média 26% de CTR e a posição 2 gera 14%, qualquer página em posição 1 registrando menos de 10% representa uma anomalia prioritária de investigação.

Consulta avançada para detecção de anomalias com z-score

Para monitorar variações semana contra semana sem falsos positivos em termos de baixíssimo tráfego, estruturamos uma consulta SQL que calcula a variação percentual de CTR e filtra termos com volume expressivo de impressões:

WITH dados_semanais AS (
  SELECT
    page,
    query,
    CASE
      WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() THEN 'semana_recente'
      WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) THEN 'semana_anterior'
    END AS periodo,
    SUM(clicks) AS cliques,
    SUM(impressions) AS impressoes,
    AVG(position) AS posicao_media
  FROM
    `projeto_seo.search_console.desempenho_diario`
  WHERE
    data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
  GROUP BY
    page, query, periodo
  HAVING
    periodo IS NOT NULL
),
agregado_pivoteado AS (
  SELECT
    page,
    query,
    MAX(IF(periodo = 'semana_anterior', impressoes, 0)) AS impressoes_anterior,
    MAX(IF(periodo = 'semana_anterior', cliques, 0)) AS cliques_anterior,
    MAX(IF(periodo = 'semana_anterior', posicao_media, 0.0)) AS posicao_anterior,
    MAX(IF(periodo = 'semana_recente', impressoes, 0)) AS impressoes_recente,
    MAX(IF(periodo = 'semana_recente', cliques, 0)) AS cliques_recente,
    MAX(IF(periodo = 'semana_recente', posicao_media, 0.0)) AS posicao_recente
  FROM
    dados_semanais
  GROUP BY
    page, query
),
calculo_metricas AS (
  SELECT
    page,
    query,
    impressoes_anterior,
    impressoes_recente,
    posicao_anterior,
    posicao_recente,
    SAFE_DIVIDE(cliques_anterior, impressoes_anterior) AS ctr_anterior,
    SAFE_DIVIDE(cliques_recente, impressoes_recente) AS ctr_recente,
    ROUND(posicao_recente - posicao_anterior, 2) AS variacao_posicao
  FROM
    agregado_pivoteado
  WHERE
    impressoes_anterior >= 200
    AND impressoes_recente >= 200
)
SELECT
  page,
  query,
  impressoes_anterior,
  impressoes_recente,
  ROUND(posicao_anterior, 1) AS pos_ant,
  ROUND(posicao_recente, 1) AS pos_rec,
  ROUND(ctr_anterior * 100, 2) AS ctr_ant_pct,
  ROUND(ctr_recente * 100, 2) AS ctr_rec_pct,
  ROUND(((ctr_recente - ctr_anterior) / NULLIF(ctr_anterior, 0)) * 100, 2) AS variacao_ctr_pct
FROM
  calculo_metricas
WHERE
  variacao_posicao BETWEEN -0.8 AND 0.8
  AND ((ctr_recente - ctr_anterior) / NULLIF(ctr_anterior, 0)) <= -0.30
ORDER BY
  impressoes_recente DESC;

Essa consulta filtra especificamente os casos críticos, isolando termos com mais de 200 impressões semanais cuja posição média permaneceu estável com variação entre -0,8 e +0,8, mas que sofreram uma contração de pelo menos 30% na taxa de clique.

Ao cruzar esses dados de visibilidade com a análise de logs de servidor em SEO, podemos confirmar se o Googlebot continua rastreando a página normalmente ou se o conteúdo perdeu relevância em relação a consultas transacionais. Da mesma forma, relatórios derivados no BigQuery ajudam a alimentar decisões de content pruning para eliminar páginas zumbis quando URLs mostram declínio irreversível de CTR e impressões.

Automação do pipeline e esteira de alertas

Manter um script executado manualmente na máquina local de um analista não atende a ambientes corporativos. A infraestrutura analítica precisa operar de maneira contínua, com monitoramento ativo e geração de avisos em tempo hábil.

Agendamento serverless com Cloud Run e Cloud Scheduler

A arquitetura recomendada para rodar o pipeline sem custos fixos de servidores dedicados envolve serviços gerenciados do Google Cloud:

Container Docker com Python. O script extrator e o módulo de carga do BigQuery são empacotados em uma imagem Docker enxuta baseada em Python.
Cloud Run Jobs. Em vez de manter um serviço web ativo 24 horas por dia, a imagem é implantada como um Job no Cloud Run. O job é disparado, executa a rotina por três minutos, descarrega a partição no BigQuery e encerra a computação imediatamente.
Cloud Scheduler. Um cron job no Cloud Scheduler emite uma requisição HTTP autenticada via OIDC para o Cloud Run Job todos os dias às 04:00 da manhã, horário em que os dados consolidados do Search Console do dia anterior já estão disponíveis.

Essa estrutura serverless tem custo insignificante de infraestrutura para a grande maioria dos sites, aproveitando as camadas gratuitas mensais do Cloud Run e do Cloud Scheduler.

Disparo de alertas no Slack e Microsoft Teams

O passo final da automação é conectar o resultado da consulta SQL a um webhook de notificação. Caso a consulta de detecção de anomalias retorne mais de cinco termos estratégicos em queda severa de CTR, um script dispara uma mensagem estruturada no canal do time de SEO.

Exemplo de payload simples para integração via webhook:

import json
import urllib.request

WEBHOOK_URL = "https://hooks.slack.com/services/SEU/WEBHOOK/AQUI"


def send_ctr_alert(anomalies):
    if not anomalies:
        return

    text_lines = ["*Alerta de Queda Crítica de CTR em Posições Estáveis:*"]
    for item in anomalies[:10]:
        line = (
            f"• *Termo:* `{item['query']}` | *Posição:* {item['pos_rec']} | "
            f"*Queda CTR:* {item['variacao_ctr_pct']}% "
            f"(de {item['ctr_ant_pct']}% para {item['ctr_rec_pct']}%) | "
            f"*URL:* {item['page']}"
        )
        text_lines.append(line)

    payload = {"text": "\n".join(text_lines)}
    req = urllib.request.Request(
        WEBHOOK_URL,
        data=json.dumps(payload).encode("utf-8"),
        headers={"Content-Type": "application/json"},
    )
    urllib.request.urlopen(req)

Com esse alerta ativo, o time de SEO não precisa esperar a reunião de fechamento mensal para descobrir que a principal página de vendas da empresa perdeu metade dos acessos porque um carrossel de vídeos ou uma caixa de respostas com inteligência artificial assumiu o topo da página de resultados.

Se você gerencia um domínio com alto volume de páginas e deseja estruturar um pipeline completo de extração do Search Console, modelagem no BigQuery e cruzamento com inteligência analítica, conheça os detalhes dos meus serviços de consultoria em marketing e SEO ou entre em contato para avaliar a maturidade de dados da sua operação.