Exporter Search Console vers BigQuery : méthode et requêtes SQL

L’export de Search Console vers BigQuery conserve chaque jour un historique détaillé de performance interrogeable avec SQL. Il devient utile lorsque l’interface ne suffit plus pour comparer beaucoup de pages, de requêtes ou d’appareils, lorsque plusieurs personnes travaillent sur les mêmes définitions, ou lorsqu’un historique futur doit rejoindre d’autres données internes. Il ne récupère pas les jours antérieurs à son activation et ne transforme pas les métriques Search Console en mesure exacte du classement.

La méthode commence avant la requête SQL. Il faut décider si le volume justifie BigQuery, attribuer un propriétaire au projet Cloud, configurer les permissions, puis vérifier chaque journée exportée. Les analyses doivent respecter les partitions, les règles d’agrégation et la présence de requêtes anonymisées. Sinon, un tableau peut comparer des ensembles incomplets ou compter plusieurs fois les mêmes impressions.

Réponse en bref

Pour exporter Search Console vers BigQuery et exploiter les données SEO :

1. confirmez que l’équipe a besoin d’un historique quotidien détaillé ou d’analyses trop volumineuses pour l’interface ;
2. choisissez un projet Google Cloud facturé, une localisation de dataset et un propriétaire capables d’être maintenus dans le temps ;
3. accordez au compte de service Search Console uniquement les rôles demandés par la procédure officielle ;
4. activez l’export dans les paramètres de la propriété et attendez jusqu’à quarante-huit heures avant de conclure à un échec ;
5. contrôlez la présence des tables searchdata_site_impression, searchdata_url_impression et ExportLog ;
6. filtrez toujours data_date afin de limiter les données lues et la facture ;
7. agrégez les métriques avant de les comparer et n’additionnez jamais des lignes détaillées comme si elles représentaient des visites ;
8. conservez les requêtes anonymisées dans les totaux, mais excluez-les des analyses qui ont besoin du texte de la requête ;
9. surveillez les journées manquantes, les courriels de Search Console et les journaux Cloud ;
10. documentez chaque vue SQL, son périmètre, ses exclusions et la décision éditoriale qu’elle doit préparer.

La prochaine action utile est de répondre à trois questions : le site dépasse-t-il réellement les analyses de l’interface, l’historique futur mérite-t-il d’être conservé dès maintenant, et qui possède le projet Cloud ? Si l’une de ces réponses manque, préparez le contrat de données avant d’activer l’export.

Choisir BigQuery seulement quand l’usage le justifie

Search Console fournit déjà des rapports, des filtres et une API. Une petite équipe qui examine chaque semaine quelques pages peut rester dans l’interface et exporter ponctuellement un tableau. BigQuery ajoute de la valeur lorsque la question exige de croiser beaucoup de dimensions, de répéter la même méthode, de conserver une profondeur historique future ou de joindre les performances organiques à un référentiel maîtrisé.

Les usages raisonnables comprennent :

• comparer des groupes de pages définis par un catalogue ou un gabarit ;
• suivre des ensembles de requêtes sans télécharger manuellement plusieurs fichiers ;
• produire la même vue pour plusieurs périodes avec une définition versionnée ;
• distinguer pays, appareil, type de recherche et apparence dans les résultats ;
• conserver les données quotidiennes au-delà des usages immédiats de l’interface ;
• rapprocher une URL canonique d’un inventaire éditorial, d’un statut de publication ou d’une famille de contenus.

En revanche, BigQuery ne résout pas une question mal formulée. Si l’équipe ne sait pas quelle décision prendra le tableau, elle déplacera simplement son hésitation vers SQL. Il ne remplace pas non plus l’analyse de la qualité d’une page, du rendu JavaScript, du crawl, des liens internes ou de la satisfaction du lecteur.

Avant l’activation, écrivez une fiche courte :

Champ	Décision à documenter
propriété Search Console	domaine ou préfixe d’URL réellement concerné
finalité	décision SEO que l’historique doit préparer
projet Cloud	projet facturé et administré dans la durée
localisation	région du dataset, choisie avant sa création
propriétaires	responsable Search Console, administrateur Cloud et analyste
durée	conservation attendue et éventuelle expiration des partitions
accès	personnes et services autorisés à lire ou interroger les tables
surveillance	canal d’alerte et responsable des journées manquantes
coût	plafond, estimation et méthode de validation des requêtes

Cette fiche évite qu’un export stratégique dépende d’un compte temporaire ou d’un projet que personne ne surveille.

Préparer le projet Cloud et les permissions

La configuration officielle repose sur une propriété Search Console vérifiée et un projet Google Cloud avec la facturation activée. Search Console écrit dans BigQuery au moyen du compte de service search-console-data-export@system.gserviceaccount.com. La documentation demande de lui accorder les rôles BigQuery Job User et BigQuery Data Editor dans le projet de destination.

Ces rôles autorisent le service à exécuter le travail d’export et à écrire les tables. Ils ne doivent pas devenir le modèle d’accès de tous les analystes. Les personnes qui interrogent les données reçoivent leurs propres permissions minimales, par groupe ou compte de service identifié. Séparez l’administration du projet, l’écriture automatique de Search Console et la lecture analytique.

Procédez dans cet ordre :

1. choisissez ou créez le projet Cloud de destination ;
2. vérifiez la facturation et le responsable budgétaire ;
3. ajoutez le compte de service Search Console avec les deux rôles requis ;
4. ouvrez les paramètres de la propriété Search Console ;
5. saisissez l’identifiant du projet Cloud et configurez l’export groupé ;
6. notez l’heure d’activation et le propriétaire chargé du premier contrôle ;
7. attendez le délai annoncé avant de modifier les permissions.

Google précise que le premier export peut prendre jusqu’à quarante-huit heures et qu’il n’effectue pas de reprise des données antérieures. Attendre le premier résultat ne fait donc pas perdre une archive déjà promise : cette archive n’existait pas dans le dataset. Si un historique antérieur est indispensable, il faut l’obtenir séparément dans les limites des rapports ou de l’API, puis le stocker dans des tables distinctes dont la provenance est explicite.

Comprendre les trois tables avant toute analyse

L’export crée un dataset généralement nommé searchconsole, avec deux tables de performance et un journal.

searchdata_site_impression

Cette table agrège les performances par propriété et par dimensions disponibles au niveau du site. Elle convient aux tendances globales par date, pays, appareil, type de recherche ou apparence. Elle ne contient pas la dimension URL. Utilisez-la lorsque la question porte sur la propriété entière et que l’ajout d’une page n’est pas nécessaire.

searchdata_url_impression

Cette table ajoute les dimensions liées à l’URL. Elle sert aux analyses par page et aux couples page-requête. Elle peut contenir davantage de lignes parce que chaque combinaison de dimensions sépare les métriques. Additionner des positions ou compter les lignes n’a pas de sens ; il faut agréger les clics et impressions et recalculer les ratios.

ExportLog

Le journal décrit les exports réussis, leur date de données et des informations de suivi. Il aide à vérifier la complétude, mais la documentation prévient qu’un export échoué ne produit pas forcément sa ligne dans ExportLog. L’absence d’un échec dans le journal ne prouve donc pas que chaque journée est présente. Il faut comparer les dates attendues aux tables et surveiller aussi les messages et journaux du système.

Le champ data_date correspond à la date des données dans le fuseau du Pacifique utilisé par Search Console. Une journée métier française ne coïncide pas exactement avec cette frontière. Conservez data_date pour les comparaisons Search Console et expliquez la différence avant toute jointure avec des événements horodatés en Europe/Paris.

La table site expose sum_top_position et la table URL sum_position. Dans les exemples officiels, la position moyenne affichable se calcule en divisant le champ correspondant par les impressions, puis en ajoutant un, car la valeur stockée commence à zéro. Cette métrique reste une moyenne de meilleures positions observées, pas le rang fixe d’une page.

Respecter l’agrégation et les requêtes anonymisées

Chaque ligne correspond à une combinaison de dimensions, pas à une session. Si une analyse ne conserve pas le pays ou l’appareil, elle doit additionner les clics et impressions de toutes les lignes correspondantes. Les taux se recalculent ensuite à partir des sommes. Faire la moyenne des CTR de plusieurs lignes donnerait le même poids à une ligne d’une impression et à une ligne de dix mille impressions.

La règle générale est :

clics = SUM(clicks)
impressions = SUM(impressions)
CTR = SUM(clicks) / SUM(impressions)
position = SUM(sum_top_position) / SUM(impressions) + 1
position URL = SUM(sum_position) / SUM(impressions) + 1

Utilisez SAFE_DIVIDE afin qu’une absence d’impressions ne transforme pas la requête en erreur.

Les requêtes anonymisées doivent aussi être comprises. Pour protéger la confidentialité, Search Console ne fournit pas le texte des requêtes rares ou sensibles. Ces impressions restent utiles dans des totaux agrégés, mais aucune chaîne de caractères exploitable ne permet de les classer. Une vue « toutes les impressions » doit donc les conserver. Une matrice nécessitant le texte de la requête peut filtrer is_anonymized_query, à condition d’indiquer que son total sera inférieur au total de la propriété.

Ne remplacez pas ces lignes par « autre » puis ne prétendez pas connaître l’intention de ce groupe. Elles signalent une part non détaillable. La proportion peut varier selon la période et le niveau d’agrégation, ce qui doit être contrôlé avant d’interpréter une évolution du nombre de requêtes connues.

Requête 1 : vérifier la complétude quotidienne

La première vue ne mesure pas le SEO. Elle vérifie que les jours attendus sont présents dans les deux tables. Remplacez YOUR_PROJECT par l’identifiant réel du projet.

WITH calendar AS (
  SELECT day
  FROM UNNEST(
    GENERATE_DATE_ARRAY(
      DATE_SUB(CURRENT_DATE(), INTERVAL 21 DAY),
      DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    )
  ) AS day
),
site_days AS (
  SELECT DISTINCT data_date AS day
  FROM `YOUR_PROJECT.searchconsole.searchdata_site_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 21 DAY)
    AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
),
url_days AS (
  SELECT DISTINCT data_date AS day
  FROM `YOUR_PROJECT.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 21 DAY)
    AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
)
SELECT
  calendar.day,
  site_days.day IS NOT NULL AS site_table_present,
  url_days.day IS NOT NULL AS url_table_present
FROM calendar
LEFT JOIN site_days USING (day)
LEFT JOIN url_days USING (day)
ORDER BY calendar.day DESC;

Le décalage de trois jours évite de marquer immédiatement les données récentes comme absentes alors qu’elles sont encore en traitement. Adaptez ce tampon à la latence réellement observée, sans transformer une semaine manquante en normalité. Une ligne false déclenche un contrôle des paramètres Search Console, des permissions et des journaux Cloud.

Complétez cette requête avec ExportLog pour comprendre les exports réussis, mais ne l’utilisez pas comme unique source d’alerte. Selon Google, les tentatives échouées ne figurent pas dans ce journal. La complétude se juge donc par comparaison entre le calendrier attendu et les partitions réellement disponibles.

Requête 2 : suivre la tendance quotidienne de la propriété

La deuxième vue calcule les métriques quotidiennes de toute la propriété sur vingt-huit jours.

SELECT
  data_date,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)) + 1 AS avg_position
FROM `YOUR_PROJECT.searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)
  AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
  AND search_type = 'WEB'
GROUP BY data_date
ORDER BY data_date;

Cette courbe aide à repérer une rupture, pas à lui attribuer automatiquement une cause. Un changement peut venir de la demande, de la saison, de l’apparence des résultats, d’un incident de mesure, d’une modification du site ou d’un ensemble de facteurs. Annotez séparément les publications, migrations, incidents et changements de gabarit, puis formulez une hypothèse à vérifier.

Le filtre search_type = 'WEB' rend le périmètre explicite. Vérifiez la valeur disponible dans vos tables avant de la copier dans un tableau récurrent. Si l’analyse doit réunir plusieurs types de recherche, gardez cette dimension dans le résultat au lieu de les additionner silencieusement.

Requête 3 : construire une matrice page-requête exploitable

Cette vue regroupe les couples page-requête non anonymisés et conserve uniquement ceux qui disposent d’un volume minimal dans la période. Le seuil n’est pas une règle SEO universelle ; il évite seulement de prendre une décision éditoriale sur une poignée d’impressions.

SELECT
  url,
  query,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1 AS avg_position
FROM `YOUR_PROJECT.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)
  AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
  AND search_type = 'WEB'
  AND NOT is_anonymized_query
  AND query IS NOT NULL
  AND query != ''
GROUP BY url, query
HAVING SUM(impressions) >= 50
ORDER BY impressions DESC;

La matrice permet d’examiner quelle page est associée à quelle formulation, si plusieurs URL semblent répondre au même besoin ou si une page reçoit des impressions sur une intention qu’elle traite mal. Elle ne prouve pas une cannibalisation. Deux pages peuvent apparaître pour la même requête dans des contextes différents. L’audit de cannibalisation sur 49 contenus propose les vérifications de contenu, d’intention et de prochaine action à mener avant de fusionner ou rediriger une URL.

Conservez le seuil dans la documentation de la vue. Pour un petit site, cinquante impressions peuvent être trop exigeantes ; pour un grand site, elles peuvent être insuffisantes. Comparez aussi la part des impressions anonymisées afin de ne pas présenter cette matrice comme l’intégralité de la demande.

Requête 4 : comparer deux fenêtres sans mélanger les dénominateurs

La quatrième vue compare, par URL, les vingt-huit jours disponibles les plus récents avec les vingt-huit jours précédents. Le tampon de trois jours reste exclu.

WITH page_periods AS (
  SELECT
    url,
    CASE
      WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)
        AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
        THEN 'current'
      WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 59 DAY)
        AND DATE_SUB(CURRENT_DATE(), INTERVAL 32 DAY)
        THEN 'previous'
    END AS period,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions
  FROM `YOUR_PROJECT.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 59 DAY)
    AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    AND search_type = 'WEB'
  GROUP BY url, period
)
SELECT
  url,
  SUM(IF(period = 'current', clicks, 0)) AS current_clicks,
  SUM(IF(period = 'previous', clicks, 0)) AS previous_clicks,
  SUM(IF(period = 'current', impressions, 0)) AS current_impressions,
  SUM(IF(period = 'previous', impressions, 0)) AS previous_impressions,
  SUM(IF(period = 'current', clicks, 0))
    - SUM(IF(period = 'previous', clicks, 0)) AS click_change,
  SUM(IF(period = 'current', impressions, 0))
    - SUM(IF(period = 'previous', impressions, 0)) AS impression_change
FROM page_periods
WHERE period IS NOT NULL
GROUP BY url
HAVING current_impressions > 0 OR previous_impressions > 0
ORDER BY ABS(impression_change) DESC;

Cette requête utilise deux fenêtres de même longueur et calcule les différences après agrégation. Elle ne corrige pas la saisonnalité. Pour un sujet annuel, comparez aussi une période équivalente de l’année précédente si l’historique existe. Pour une migration, créez des groupes de pages stables afin qu’une nouvelle URL ne soit pas interprétée séparément de l’ancienne.

La sortie sert à choisir les pages à examiner. Avant de modifier un contenu, contrôlez la requête, le pays, l’appareil, le type de recherche, l’indexation, le rendu et les changements connus. Une baisse d’impressions peut refléter une demande plus faible ; une hausse sans clic peut révéler une nouvelle couverture sur des positions éloignées.

Maîtriser le coût de chaque requête

BigQuery facture principalement les données lues selon le mode choisi. La première protection consiste à filtrer la colonne de partition data_date. Une requête sans date peut scanner tout l’historique alors que la décision ne concerne que quatre semaines.

Appliquez les règles suivantes :

• affichez ou estimez les octets traités avant l’exécution ;
• utilisez le dry run ou le validateur de requête pour obtenir cette estimation ;
• fixez un maximum d’octets facturables pour les comptes et travaux récurrents ;
• sélectionnez les colonnes nécessaires au lieu d’utiliser SELECT * ;
• matérialisez une vue contrôlée si la même transformation lourde est répétée ;
• filtrez les partitions aussi dans les sous-requêtes ;
• testez avec une courte période avant d’élargir l’historique.

Ajouter LIMIT 100 ne réduit pas nécessairement le volume lu dans une table non clusterisée. Google le rappelle dans ses bonnes pratiques de coût : la limite réduit le nombre de lignes retournées, pas les octets parcourus. Une prévisualisation ou un filtre de partition est plus sûr pour explorer le schéma.

Une expiration des partitions peut contenir le stockage, mais elle détruit l’histoire que l’export devait préserver. La documentation Search Console demande une durée d’au moins quatorze jours. Décidez la conservation selon la finalité, les obligations et le budget, puis alertez avant toute modification. Ne changez pas le schéma des tables exportées ; créez vos vues ou tables dérivées dans un autre espace pour éviter de casser les écritures futures.

Surveiller les échecs avant qu’ils ne deviennent une lacune

Un tableau récurrent devrait commencer par un état des données, pas par un graphique SEO. Affichez la dernière date disponible dans chaque table, les partitions manquantes, la date de dernière réussite connue et le statut de l’alerte.

La documentation de surveillance indique que Search Console retente pendant environ une semaine certaines journées en échec. Des erreurs prolongées peuvent conduire à l’arrêt de l’export après environ un mois. Ces délais ne sont pas une raison d’attendre passivement. Dès qu’une date manque après la latence habituelle :

1. vérifiez que l’export reste activé dans Search Console ;
2. examinez les changements récents de facturation, permissions, quotas et dataset ;
3. consultez les journaux Google Cloud et les courriels adressés aux propriétaires ;
4. comparez les deux tables de performance ;
5. notez la lacune dans les tableaux qui couvrent cette période ;
6. corrigez la cause sans renommer les tables ni modifier leur schéma.

Une reprise réussie peut compléter un jour manquant. Tant que cette reprise n’est pas constatée dans les partitions, n’imputez pas une baisse de clics au site. Le pipeline de données fait partie du diagnostic SEO.

Transformer une vue SQL en décision éditoriale

Une analyse utile sépare quatre niveaux : observation, hypothèse, changement et vérification.

Observation : « Les impressions de ce groupe de pages diminuent de 18 % entre deux fenêtres de vingt-huit jours. » Cette phrase doit conserver le périmètre, les dates, le type de recherche et les exclusions.

Hypothèse : « La demande a pu se déplacer vers une formulation que les pages traitent peu. » L’hypothèse n’est pas une conclusion et peut nécessiter la matrice page-requête, les tendances externes ou une inspection des résultats.

Changement : « Ajouter une section répondant à cette sous-question sur la page dont l’intention et la prochaine action correspondent. » La décision explique pourquoi cette URL, et non une nouvelle page, reçoit l’ajout.

Vérification : « Contrôler après une période définie les impressions, clics, requêtes associées et conversions internes, sans attendre une position garantie. » Le délai doit tenir compte de la découverte, de la demande et du volume.

La routine Search Console pour piloter le SEO fournit un rythme de décision hebdomadaire et mensuel. BigQuery ne remplace pas cette discipline ; il la rend reproductible lorsque l’échelle ou l’historique dépassent l’interface.

Versionnez les requêtes dans le dépôt analytique avec leur objectif, leur propriétaire, leur date de vérification et une requête de contrôle. Si un champ Google change, l’équipe peut identifier les vues affectées. Si un seuil éditorial change, l’historique du raisonnement reste lisible.

Gouverner les accès et les jointures

Les requêtes peuvent révéler des intentions rares, des noms ou des situations personnelles. Leur présence dans Search Console ne justifie pas une diffusion générale. Restreignez les accès, évitez les copies locales et définissez ce qui peut apparaître dans un tableau partagé. Relier une requête à un identifiant individuel n’est ni nécessaire ni techniquement fondé : Search Console fournit des agrégats, pas une chaîne d’attribution personnelle. Préférez des référentiels éditoriaux comme la famille de page, le modèle ou la date de publication.

Documentez les transformations qui regroupent des URLs. Les paramètres, protocoles, sous-domaines et variantes canoniques peuvent fragmenter les lignes. N’effacez pas cette différence avant d’avoir vérifié si elle signale réellement une incohérence technique. L’article sur la connexion entre Search Console et un workflow IA détaille les contrôles à imposer lorsqu’une analyse alimente un assistant : accès en lecture, limites, journalisation et validation humaine avant toute modification éditoriale.

Limites

L’export BigQuery reflète les données que Search Console met à disposition et leurs règles d’agrégation. Il ne représente pas toutes les recherches, ne livre pas le texte des requêtes anonymisées et ne permet pas de reconstituer un parcours individuel. Les impressions, clics, CTR et positions moyennes ne prouvent ni la satisfaction, ni le chiffre d’affaires, ni la cause d’une évolution.

Les exemples SQL supposent le schéma documenté le 11 août 2026 et une table dont les valeurs correspondent aux filtres indiqués. Vérifiez les colonnes, les types, la localisation et les valeurs présentes dans votre propre dataset avant d’automatiser une vue. Les délais d’export et de reprise peuvent évoluer ; la documentation officielle reste la référence opérationnelle.

Enfin, activer un export n’améliore pas la visibilité. Il crée une capacité d’observation future. La valeur apparaît seulement si l’équipe surveille la qualité des données, formule une décision, modifie le bon contenu et vérifie le résultat sans confondre corrélation et causalité.

Articles liés

• Connecter Search Console à une IA sans automatiser les mauvaises décisions
• Installer une routine Search Console pour piloter le SEO
• Auditer la cannibalisation sur 49 contenus