BigQuery + GSC: les requetes que votre agence ne lance pas

L'interface Search Console vous plafonne a 1 000 lignes par rapport et agrege les donnees d'une maniere qui cache plus qu'elle ne montre. Connectez GSC a BigQuery via le Bulk Data Export et la donne change : vous accedez a des millions de lignes d'impression, requete, page et appareil, sans echantillonnage. La plupart des agences s'arretent au dashboard par defaut et livrent un rapport avec un graphique en courbes. Le probleme n'est pas le manque de donnees, c'est le manque de SQL. Ce post apporte des requetes qui tournent en production et repondent a des questions que le dashboard ignore : quelles pages perdent des impressions mois apres mois sur les requetes de bas de funnel ?

Avant toute requete, le setup. Activez le Bulk Data Export dans Settings > Bulk Data Export et pointez vers un projet GCP avec billing actif. Les tables qui comptent sont searchdata_site_impression et searchdata_url_impression. La premiere contient des requetes anonymisees filtrees, parfaite pour les tendances agregees ; la seconde offre la granularite par URL. Les couts ? Un site avec 5M d'impressions/mois genere environ 200 Mo/jour, ce qui revient a moins d'un dollar par mois avec des requetes on-demand soignees. Avant de plonger dans le SQL, assurez-vous que votre tracking on-page est propre, comme le montre Comment auditer le SEO on-page sans tomber dans les conjectures, sinon vous correlerez du bruit.

Requete 1 : content decay avec fenetre glissante de 28 jours. Utilisez LAG() sur une agregation hebdomadaire par URL pour detecter les pages qui ont perdu plus de 30% de clics par rapport a la fenetre precedente, en filtrant a minimum 100 clics/semaine pour couper le bruit. Le resultat est une liste de reecriture priorisee, infiniment meilleure que d'ouvrir le panneau pour scruter des fleches rouges. Croisez avec la feuille de Content decay : reperer les articles qui perdent du trafic et vous avez deja le backlog du trimestre. Pour decider entre reecrire et refaire de zero, Reecrire ou refaire : trancher avec les donnees de la SERP propose le framework data-SERP qui s'integre directement dans ce pipeline.

Requete 2 : mots-cles en striking distance en position 8-20 avec CTR sous le benchmark. Joignez searchdata_url_impression avec une CTE de benchmark par position (utilisez les chiffres de Benchmark CTR par position : donnees actualisees 2026 ou calculez sur votre propre site). Quand une URL apparait en position 11 avec un CTR de 0,8% et que le benchmark est a 2,1%, vous avez un probleme de title ou de meta description, pas de ranking. C'est la requete qui paie le consultant a elle seule : elle sort une liste de 50-200 pages ou modifier un title en un sprint devient des clics le mois suivant. Combinez avec les patterns de Title tags qui convertissent : 7 modeles testes sur des SERP reelles.

Requete 3 : cannibalisation reelle, pas celle d'Ahrefs. Groupez par query et comptez les URLs distinctes avec plus de 10 impressions dans la meme semaine. Si une query a 3+ URLs qui remontent, vous avez une vraie cannibalisation (avec signal de demande), pas juste deux pages partageant un mot. Exportez en CSV et croisez avec les balises canonical reelles via un crawl Screaming Frog. Vingt pour cent des cas sont des canonicals mal configures, voir Canonical tags : les erreurs frequentes qui saignent le trafic organique. Le reste releve de l'editorial : consolider, faire un 301 ou repositionner une des pages sur une autre intention.

Requete 4 : branded vs non-branded avec REGEXP_CONTAINS. Construisez une colonne calculee classant la query comme brand quand elle matche votre regex de marque (incluez les fautes de frappe courantes). Groupez par mois et vous verrez ce que beaucoup d'agences cachent : 60% du trafic organique de nombreux clients est du brand search, donc le SEO ne l'a pas apporte. Cette requete sauve des relations et justifie un investissement honnete, en ligne avec KPIs SEO honnetes : au-dela du ranking et du trafic et Attribution SEO: prouver le ROI sans dernier clic. Montrez-la au CFO avant qu'il ne demande.

Requete 5 : intent mismatch via patterns de query. Classez les queries par intention (informationnelle, transactionnelle, navigationnelle, comparative) avec CASE WHEN sur des regex simples ("comment", "meilleur", "prix", "vs", "avis"). Croisez avec le type de page (PLP, PDP, blog, home) et vous decouvrez des pages produit qui rankent sur des queries info ou l'inverse. C'est de l'or pour l'e-commerce, comme le detaille On-page e-commerce: PLP vs PDP sans cannibalisation. Pour fonder la classification, Intention de recherche : 4 types et comment les mapper sur la SERP propose la taxonomie que j'utilise.

Requete 6 : page-query fingerprint pour detecter les changements d'algorithme. Snapshottez les top 20 queries par URL chaque semaine. Quand l'ensemble change de plus de 40% en une semaine sans modification on-page, Google a reinterprete la page. Vous ne le voyez qu'avec BigQuery, le panneau ne vous le dira jamais. Takeaway pratique : planifiez ces six requetes dans les Scheduled Queries de BigQuery, materialisez en tables hebdomadaires, branchez Looker Studio dessus et vous avez un observatoire a moins de 5 dollars/mois qui surpasse n'importe quelle plateforme a 500 dollars. Le SQL n'est pas un luxe de grosse agence, c'est le plancher de quiconque prend le SEO au serieux.