BigQuery + GSC: queries que sua agencia nao roda

A interface do Search Console limita voce a 1.000 linhas por relatorio e agrupa dados de forma que esconde mais do que mostra. Quando voce conecta o GSC ao BigQuery via Bulk Data Export, o jogo muda: passa a ter acesso a milhoes de linhas de impressao, query, pagina e dispositivo, sem amostragem. A maioria das agencias para no painel padrao e entrega relatorio com grafico de linha. O problema nao e falta de dados, e falta de SQL. Este post traz queries que rodam em produção e respondem perguntas que o painel nao consegue, do tipo: quais paginas estao perdendo impressao mes a mes para queries de fundo de funil?

Antes de qualquer query, ajuste o setup. Ative o Bulk Data Export em Settings > Bulk Data Export e aponte para um projeto GCP com billing ativo. As tabelas que importam sao searchdata_site_impression e searchdata_url_impression. A primeira tem queries anonimizadas filtradas e e otima para tendencias agregadas; a segunda tem granularidade por URL. Custos? Um site com 5M de impressoes/mes gera cerca de 200MB/dia, o que da menos de 1 dolar/mes em storage e queries on-demand cuidadosas. Antes de partir pro SQL, garanta que seu rastreamento on-page esta limpo, conforme Como auditar SEO on-page sem cair em achismos mostra, senao voce vai correlacionar lixo.

Query 1: content decay com janela movel de 28 dias. Use LAG() sobre uma agregacao semanal por URL para detectar paginas que caíram mais de 30% em clicks vs a janela anterior, filtrando por minimo de 100 clicks/semana para cortar ruido. O resultado e uma lista priorizada de reescrita, infinitamente melhor que abrir o painel e olhar setas vermelhas. Cruze com a planilha de Content decay: identificando posts que estao perdendo trafego e voce ja tem backlog para o trimestre. Para decidir reescrita vs refazer do zero, Reescrever ou refazer: a decisao por dados de SERP tem o framework por dados de SERP que cabe direto neste pipeline.

Query 2: striking distance keywords na posicao 8-20 com CTR abaixo do benchmark. Junte searchdata_url_impression com uma CTE de benchmark por posicao (use os numeros de Benchmark de CTR por posicao: dados atualizados de 2026 ou calcule do seu proprio site). Quando uma URL aparece em posicao 11 com CTR de 0,8% e o benchmark e 2,1%, voce tem um problema de title ou meta description, nao de ranking. Essa e a query que paga o consultor sozinha: gera lista de 50-200 paginas onde mexer no title em uma sprint vira clique no mes seguinte. Combine com os padroes de Title tags que convertem: 7 padroes testados em SERPs reais.

Query 3: canibalizacao real, nao a do Ahrefs. Agrupe por query e conte URLs distintas com mais de 10 impressoes na mesma semana. Se uma query tem 3+ URLs aparecendo, voce tem canibalizacao verdadeira (com sinal de demanda), nao apenas duas paginas com palavra parecida. Exporte para CSV e cruze com canonical tags reais via crawl do Screaming Frog. Vinte por cento dos casos sao canonical mal configurado, ver Canonical tags: erros comuns que sangram trafego organico. O resto e decisao editorial: consolidar, redirecionar 301 ou reposicionar uma das paginas para outra intencao.

Query 4: branded vs non-branded com REGEXP_CONTAINS. Crie uma coluna calculada classificando query como brand quando bate seu regex de marca (inclua erros comuns de digitacao). Agrupe por mes e voce ve o que muita agencia esconde: 60% do trafego organico de muitos clientes e brand search, ou seja, SEO nao trouxe. Essa query salva relacoes e justifica investimento honesto, alinhado com KPIs de SEO honesto: alem de ranking e trafego e Atribuicao de SEO: provando ROI sem ultima clique. Mostre para o CFO antes que ele pergunte.

Query 5: intent mismatch usando padroes de query. Classifique queries por intencao (informacional, transacional, navegacional, comparativa) com CASE WHEN sobre regex simples ("como", "melhor", "preco", "vs", "review"). Cruze com o tipo de pagina (PLP, PDP, blog, home) e voce descobre paginas de produto rankeando para query informacional ou vice-versa. Isso e ouro para e-commerce, conforme On-page para e-commerce: PLP vs PDP sem canibalizar detalha. Para fundamentar a classificacao, Intencao de busca: 4 tipos e como mapear no SERP tem a taxonomia que uso.

Query 6: page-query fingerprint para detectar mudancas de algoritmo. Salve um snapshot semanal das top 20 queries por URL. Quando o conjunto muda mais de 40% em uma semana sem mudanca on-page, Google reinterpretou a pagina. Voce so descobre isso com BigQuery, o painel nunca te dira. Takeaway pratico: agende essas seis queries em Scheduled Queries do BigQuery, materialize em tabelas semanais, plugue no Looker Studio e voce tem um observatorio que custa menos de 5 dolares/mes e supera qualquer plataforma de 500 dolares. SQL nao e luxo de agencia grande, e o piso de quem leva SEO a serio.