Search Console et BigQuery : les lignes répétées ne sont pas forcément des doublons

Votre nouveau tableau SEO affiche moins de clics que l’export qui l’alimente, mais un meilleur taux de clic. Le nettoyage semblait pourtant raisonnable : supprimer les doublons, calculer une moyenne et ne garder que les requêtes renseignées. Trois transformations familières ont changé la population mesurée avant même que quelqu’un examine les pages.

La vraie question est de savoir quelles lignes représentent des contributions à additionner. Dans l’export natif Search Console vers BigQuery, une clé répétée n’est pas automatiquement une erreur. Confondre consolidation et dédoublonnage peut fabriquer une amélioration du CTR, déplacer une priorité éditoriale et faire chercher un problème de visibilité sur une page qui n’a pas changé.

Nous partirons de cinq lignes fictives pour suivre les impressions, les clics et les positions jusqu’au résultat publié. L’exercice distingue les requêtes visibles du total, puis montre comment recevoir une correction historique sans additionner deux versions. Les calculs sont reproductibles localement ; ils ne proviennent d’aucun compte client et ne constituent pas une mesure de performance de BigQuery.

Le pilotage de la performance et du SEO technique commence aussi par la fiabilité des observations. Un audit technique des données et des pages permet de séparer une anomalie de transformation d’un véritable défaut de crawl, d’indexation ou de parcours, avant d’engager les développeurs sur la mauvaise correction.

Pour qui : des équipes dont les exports alimentent les décisions SEO

Ce diagnostic concerne un export natif Search Console vers BigQuery déjà disponible ou en cours de qualification. Il devient utile lorsque les équipes data préparent les chiffres remis au SEO, à la direction ou aux responsables de catalogue. Il ne remplace pas la configuration initiale des accès et ne demande pas d’activer un service payant pour reproduire l’exercice.

Reconnaître un nettoyage qui change le résultat

Un signal faible apparaît quand une étape réduit fortement le nombre de lignes sans expliquer les clics retirés. Un autre se trouve dans un tableau dont le CTR s’améliore après une modification du code, alors que personne n’a touché aux titres, aux contenus ou aux offres. Commencez par comparer les transformations avant d’attribuer cette hausse à une action marketing.

Demandez le résultat avant nettoyage, après consolidation et après filtrage. Chaque passage doit préciser sa population : propriété, URL, type de recherche, appareil, pays et période. Le responsable SEO peut alors vérifier le sens du chiffre, tandis que la personne chargée des données retrouve la transformation exacte qui l’a modifié.

Limiter la première enquête à un périmètre vérifiable

Choisissez une journée et une URL dont les lignes peuvent être examinées sans parcourir tout l’historique. Conservez les dimensions présentes, même si le futur graphique ne les affiche pas. Un regroupement par page est une décision analytique ; il ne prouve pas que les lignes brutes étaient déjà uniques au niveau page-jour.

Le problème diffère d’une jointure qui multiplie les observations. Le dossier sur la qualité des données et les pertes de jointure couvre ce deuxième risque. Ici, nous examinons d’abord une perte provoquée par un nettoyage trop agressif, avant tout rapprochement avec un référentiel métier.

Pourquoi une clé répétée ne doit pas disparaître automatiquement

Les consignes Google pour interroger l’export précisent que les données ne sont pas nécessairement consolidées par date, URL, site ou combinaison de clés. Elles demandent d’agréger les métriques. Pour les clics et impressions, cela signifie additionner les contributions avec SUM, au grain choisi pour le résultat.

Ne pas inventer un identifiant de fait avec les seules dimensions

Une contrainte d’unicité construite sur date, page, requête, appareil et pays peut rejeter une deuxième contribution pourtant utile. Garder arbitrairement la première ligne ou la dernière ne résout pas ce problème. Aucun ordre technique de lecture ne permet de décider que les autres métriques doivent être effacées.

Dans notre contrat de réception, les lignes sources restent intactes et la sortie agrégée devient unique au grain demandé. Les tests d’unicité portent donc sur cette sortie, après sommation. Appliquer le même contrôle à l’entrée sans tenir compte du format de l’export reviendrait à exiger de Google une consolidation que sa documentation ne garantit pas.

Distinguer contribution native et chargement effectué deux fois

Cette règle ne signifie pas qu’il faut accepter toute duplication introduite par votre propre pipeline. Si un même fichier intermédiaire est chargé deux fois dans une table dérivée, il faut détecter ce rejeu au niveau du lot ou de la partition. Les dimensions SEO ne suffisent toujours pas à identifier cette erreur d’ingestion.

Conservez séparément la provenance du chargement et les faits qu’il contient. Une source relue doit pouvoir remplacer la version dérivée correspondante sans additionner son ancien résultat. Le choix devient explicite : sommer les contributions d’une même version, puis empêcher qu’une version déjà reçue soit cumulée avec elle-même.

Exemple concret : trois contributions qui deviennent un faux meilleur CTR

Le jeu fictif décrit le 20 octobre 2026, pour la recherche Web sur mobile en France. Deux URL imaginaires appartiennent à la même propriété : A représente une offre d’intégration, B une ressource pédagogique. Les chemins servent uniquement à reconnaître les lignes ; les volumes ci-dessous ne sont pas ceux du site Dawap.

Les trois lignes visibles d’A portent la même requête fictive, « connecteur erp ». Les deux premières sont volontairement identiques, y compris leurs métriques. Elles modélisent deux contributions, pas deux copies accidentelles d’un fichier. Leur ressemblance ne fournit donc aucune autorisation de supprimer l’une d’elles.

Recevoir la somme avant de calculer le taux

Pour A sur les requêtes visibles, le total attendu est de 400 + 400 + 200 = 1 000 impressions et 4 + 4 + 12 = 20 clics. Le CTR vaut 20 ÷ 1 000, soit 2 %. Ces deux sommes constituent les premiers résultats à vérifier ; le pourcentage vient ensuite.

Un DISTINCT sur toutes les valeurs ne conserve que deux lignes : 400 impressions avec quatre clics, puis 200 avec douze clics. Il produit 600 impressions, seize clics et environ 2,67 % de CTR. Le taux paraît meilleur précisément parce que le nettoyage a retiré une contribution dont le taux était plus faible.

Calculer les ratios à partir des sommes, jamais des moyennes affichées

Même sans supprimer de ligne, une moyenne simple des trois CTR reste incorrecte pour représenter leur ensemble. Les taux unitaires sont 1 %, 1 % et 6 %. Leur moyenne vaut environ 2,67 %, alors que les contributions n’ont pas le même nombre d’impressions. Le calcul donne ainsi autant de poids à 200 impressions qu’à 400.

Conserver le numérateur et le dénominateur dans les tables dérivées

Stockez les sommes de clics et d’impressions avec le résultat, plutôt qu’un seul pourcentage arrondi. Une consolidation hebdomadaire pourra alors recalculer son taux à partir des sept journées. Si elle ne reçoit que les CTR quotidiens, elle ne sait plus combien chaque journée doit peser dans la moyenne.

La même précaution s’applique aux regroupements par appareil ou famille de pages. Le taux global ne se déduit pas de la moyenne des cartes visibles. Une carte peu exposée peut avoir un excellent CTR sans compenser le poids d’une famille très exposée ; gardez ces dénominateurs accessibles à la personne qui arbitre.

Ajouter une seule fois le décalage de position

La référence des tables exportées utilise sum_position au niveau URL et sum_top_position au niveau propriété. Ces sommes partent de zéro. La position moyenne affichée s’obtient en divisant la somme correspondante par les impressions, puis en ajoutant un.

Pour les trois contributions visibles d’A, la somme vaut 3 400. Le résultat est donc 3 400 ÷ 1 000 + 1 = 4,4. La moyenne simple des positions 5, 5 et 2 donnerait 4, ce qui raconte encore une meilleure performance fictive. Sans impressions, notre exercice laisse le ratio absent au lieu d’inventer une position zéro.

Garder les requêtes anonymisées dans le total qu’elles contribuent à former

Les cinq lignes complètes représentent 1 300 impressions et 38 clics. Les seules requêtes visibles représentent 1 100 impressions et 30 clics ; la contribution anonymisée apporte les 200 impressions et huit clics restants. Ces deux populations répondent à des questions différentes et doivent conserver des libellés différents dans le tableau.

Filtrer les libellés sans rebaptiser le résultat « total »

Un classement de mots recherchés peut exclure les requêtes anonymisées puisque leur texte n’est pas exploitable. Cette exclusion ne doit pas se propager silencieusement au total de la page. Dans l’exemple, A compte 28 clics toutes contributions confondues, contre vingt sur sa requête visible ; aucun de ces résultats n’est interchangeable avec l’autre.

Utilisez l’indicateur is_anonymized_query pour exprimer cette distinction. La documentation décrit le libellé anonymisé tantôt comme vide, tantôt comme nul ; ne fondez pas toute la logique sur une seule représentation supposée. Notre contrôle remplace la valeur nulle par une chaîne vide et vérifie que les totaux restent identiques.

Ne pas transformer l’anonymat en requêtes inventées

Les huit clics masqués ne peuvent pas être attribués automatiquement au mot « connecteur erp », même si c’est le seul libellé visible du petit exercice. Ils ne prouvent pas davantage une demande pour une marque, un logiciel ou un service précis. Le manque de détail doit rester visible dans l’analyse commerciale.

Pour étudier une famille de formulations connues, vérifiez aussi le filtre lui-même. Le diagnostic sur les filtres littéraux et les références produit dans Search Console traite ce risque. Un filtre correct qualifie la population visible ; il ne restitue pas les libellés que Google ne fournit pas.

Choisir entre propriété et URL avant de rapprocher les totaux

Les tables searchdata_site_impression et searchdata_url_impression ne constituent pas deux moitiés d’un même total à réunir. La première porte l’agrégation par propriété, la seconde par URL. Les définitions Google des impressions et positions expliquent que plusieurs pages présentes dans un même résultat peuvent compter différemment selon ce regroupement.

Utilisez la table URL pour examiner les pages qui nécessitent une enquête, et celle de propriété pour une lecture au niveau du site. Dans une revue qui présente les deux, affichez clairement le grain. Chercher à forcer leurs impressions à égalité peut supprimer une différence de définition légitime plutôt que corriger une anomalie.

La valeur de position demande la même discipline. Une moyenne décrit une distribution d’apparitions ; elle n’est pas une promesse de classement pour la prochaine recherche manuelle. Le type de résultat, l’appareil et la population observée comptent. Un changement de composition doit être examiné avant de conclure à une progression uniforme de toutes les pages.

Conservez également l’URL attribuée par Search Console avant tout mapping interne. L’attribution se fait généralement à la canonical choisie par Google. Un crawl, des logs et un sitemap apportent d’autres observations ; une normalisation de chemin ne les rend pas automatiquement équivalentes à cette attribution de performance.

Reproduire les erreurs sans connecter un compte Google

Le script Python d’agrégation de l’export contient les cinq lignes et les résultats attendus. Il fonctionne avec Python 3 et sa bibliothèque standard, sans dépendance externe, accès réseau ou identifiant Google. Ses fractions exactes permettent de retarder l’arrondi jusqu’à l’affichage.

Lancez python3 agregation_export.py après avoir enregistré le fichier. Les dix-neuf contrôles doivent passer, notamment les sommes complètes, la population visible, les valeurs absentes et les reprises. Les témoins incorrects sont acceptés par le test uniquement lorsqu’ils produisent l’écart attendu ; ils rendent les erreurs reproductibles au lieu de les décrire abstraitement.

L’exercice ne valide pas une requête GoogleSQL, un schéma de production ou la facturation d’un projet Cloud. Pour adapter le calcul, reprenez les exemples officiels, filtrez une période bornée avec data_date, groupez les dimensions utiles et sommez les métriques avant la division. Vérifiez les valeurs effectives des champs de votre export avant de figer vos filtres.

Le contrat relie une entrée identifiée, la version de transformation et une sortie contrôlée. La journalisation garde la période, le grain et les sommes nécessaires au recalcul ; les arrondis appartiennent à la présentation. Une table intermédiaire qui ne conserve que « 2,67 % » ne permet plus de distinguer notre suppression de contribution, une moyenne mal pondérée ou une véritable observation de ce taux.

Lire ExportLog par table et rechercher les journées absentes

ExportLog décrit les exports réussis, avec la table concernée et la date de données. Il ne constitue pas un registre de toutes les tentatives échouées. Les deux tables de performance étant exportées séparément, la réussite de l’une ne prouve pas que l’autre couvre déjà la même journée.

Contrôler une plage de dates plutôt que sa seule borne maximale

Dans notre simulation, la table propriété dispose des 20, 21 et 22 octobre, tandis que la table URL ne dispose que des 20 et 21. Une comparaison commune ne peut pas présenter le 22 comme complet. L’absence de partition URL n’équivaut pas à zéro clic : elle signifie que la donnée attendue manque à cette lecture.

Un autre témoin possède les 20 et 22, mais pas le 21. Sa date maximale semble récente et masque pourtant un trou intérieur. Comparez l’ensemble des journées attendues aux journées reçues, pour chaque table nécessaire. Conservez la date métier en heure du Pacifique ; ne la remplacez pas par la date française d’exécution du traitement.

Diagnostiquer la tentative avant d’annoncer le rétablissement

La procédure Google de suivi des exports renvoie au statut courant et aux journaux Cloud pour les erreurs. Un test de configuration réussi ne déclenche pas immédiatement un export et ne garantit pas sa réussite future. Le rétablissement demande une nouvelle réception des données attendues.

La fenêtre de reprise n’est pas illimitée : Google indique des tentatives pendant environ une semaine pour une journée manquante, puis un arrêt global après environ un mois d’échecs. Ces durées approximatives ne sont pas un engagement de service. Le monitoring doit prévenir un responsable assez tôt pour enquêter, sans attendre le comité mensuel.

Recevoir une partition corrigée sans cumuler deux versions

La référence d’export prévoit des corrections de données anciennes et un epoch_version incrémenté dans le journal. Dans notre scénario, une révision fictive remplace les douze clics de la troisième ligne par quatorze. Les impressions restent identiques : la nouvelle version complète contient 1 300 impressions et quarante clics.

Si une table dérivée ajoute cette version à l’ancienne, elle affiche 2 600 impressions et 78 clics. Ce n’est ni la version initiale ni la version corrigée. La réception proposée remplace la partition dérivée concernée, puis vérifie ses agrégats ; rejouer la même correction laisse le résultat à quarante clics.

Cette procédure concerne votre transformation, pas une modification du schéma des tables gérées par Search Console. Gardez les enrichissements et les contrôles dans des objets séparés. La documentation de mise en place de l’export natif avertit qu’un changement de schéma des tables exportées peut interrompre l’alimentation.

Versionnez aussi la sortie présentée au comité. Si le tableau conserve une réponse en cache, son invalidation doit suivre la réception de la partition ; sinon, le calcul corrigé reste invisible au lecteur. Si une correction change un chiffre déjà utilisé, indiquez la période et l’écart, puis réexaminez seulement les décisions concernées. Un rollback analytique peut réafficher une version antérieure identifiée ; il ne doit pas faire disparaître la trace de la correction ni la raison de son refus éventuel.

Erreurs fréquentes : corriger le site pour expliquer un calcul défectueux

Interpréter une hausse artificielle comme une victoire éditoriale

Paradoxalement, retirer des clics peut améliorer le CTR affiché. Notre passage de 2 % à environ 2,67 % le démontre sans changement de contenu. Avant de féliciter une nouvelle accroche ou de généraliser un format de titre, vérifiez que la population et la transformation sont restées comparables.

La même prudence évite une refonte inutile. Une baisse liée à une journée absente ne justifie pas de modifier le rendu JavaScript, les redirections ou la navigation. Examinez le HTML réellement servi, l’effet du cache sur les routes concernées et les passages de Googlebot dans les logs lorsque des indices techniques le justifient. Le suivi d’indexation et la mesure du trafic doivent pouvoir se contredire sans que le dashboard efface l’écart.

Traiter BigQuery comme une reconstruction automatique de tout le passé

La mise en place de l’export ne reconstitue pas automatiquement les données antérieures à son activation. Google renvoie vers les rapports ou l’API pour cet historique. Si vous rapprochez plusieurs moyens de collecte, identifiez la rupture de source et leurs limites respectives au lieu de fabriquer une série continue supposée homogène.

Un budget de requêtes se protège également par un périmètre explicite. Une enquête sur une journée ne demande pas de relire toutes les partitions. Le coût caché vient autant des analyses répétées sur une mauvaise hypothèse que des calculs inutiles ; documenter une requête reçue et ses contrôles réduit ces deux formes de reprise.

Plan d’action : qualifier une mesure avant de la publier dans le tableau SEO

Faire recevoir une journée par les responsables SEO et data

Retenez une date disponible, une table et une population assez petites pour rapprocher les sommes. Écrivez la question attendue : performance d’une URL toutes requêtes confondues, classement de requêtes visibles ou exposition globale de la propriété. Cette phrase détermine les dimensions et les filtres ; elle évite de changer de sens au milieu du calcul.

Conservez les lignes sources, le code de transformation et les résultats intermédiaires. La QA vérifie les sommes avant de regarder les graphiques. Demandez au responsable SEO d’expliquer le dénominateur et à la personne data de montrer l’étape qui le produit. Si ces explications divergent, la mesure reste en qualification, même si la requête s’exécute sans erreur.

Éprouver les cas qui rendent un résultat plausible mais faux

Ajoutez les contributions identiques, les tailles de groupes différentes et une requête anonymisée au jeu de recette. Vérifiez les variantes avec texte vide ou nul, puis l’absence d’impressions. Introduisez une journée manquante au milieu de la période et une correction historique. Chaque témoin doit faire échouer le raccourci qu’il représente, sans modifier la règle attendue pour lui donner raison.

Dans la CI de votre transformation, protégez ces tests lors des changements de SQL ou de modèle. Ils n’exigent pas un crawl complet du site à chaque exécution. Une recette séparée vérifie le tableau final : libellés des populations, dates couvertes, arrondis et visibilité des absences. Une mesure juste mais présentée sous le mauvais intitulé reste dangereuse pour la décision.

Ouvrir le tableau avec un contrat de reprise exploitable

Le runbook désigne le responsable de l’export, celui du calcul et celui de la décision SEO. Il précise comment geler une période incomplète, réactualiser une partition corrigée et retrouver la dernière version reçue. La journalisation conserve les tables, dates et versions utilisées ; elle ne se limite pas à une durée d’exécution et au message « succès ».

• Publier la mesure lorsque les contributions, les ratios et toutes les journées nécessaires ont été contrôlés.
• Maintenir une lecture partielle lorsque son périmètre limité est explicite et accepté pour une décision précise.
• Bloquer la comparaison lorsqu’une suppression de lignes, une révision cumulée ou une journée absente reste inexpliquée.

Le premier bénéfice à mesurer est la disparition des écarts de calcul non expliqués et du temps passé à les reconstruire. Les gains de trafic se suivent ensuite avec une hypothèse et une période comparables. Aucune correction de pipeline ne doit être vendue comme une augmentation des visites tant qu’elle ne fait que rétablir le chiffre déjà observé.

Contenus liés pour passer d’une mesure reçue à une enquête utile

Une fois les contributions correctement agrégées, le diagnostic sur la fiabilité des données SEO prolonge le travail vers les jointures et référentiels. Le modèle d’état des URL entre sitemap, logs et Search Console aide ensuite à préserver ce que chaque source prouve, sans transformer un chiffre de performance en statut d’indexation.

La lecture des recherches multimodales et des pages images demande également de distinguer les rapports disponibles. Un nouveau tableau de bord ne rend pas toutes les dimensions interchangeables : vérifiez la donnée réellement exposée avant d’ajouter une ventilation qui semblerait utile à la direction.

Conclusion : une ligne répétée peut contenir la part manquante du résultat

Le piège initial tient en quatre clics et 400 impressions supprimés sans justification. Le tableau devient plus flatteur alors qu’il décrit moins fidèlement sa source. Dans un export à contributions, la bonne réception commence par une somme expliquée, puis conserve les éléments nécessaires pour reconstruire chaque ratio.

Les autres garde-fous protègent le sens de cette somme : anonymat visible, grain annoncé, dates couvertes et version reçue. Ils permettent de décider si une variation mérite une enquête sur le site ou une correction du calcul. Ils ne garantissent ni un classement ni une hausse des demandes commerciales.

Un audit technique de votre mesure SEO peut partir d’un seul écart reproductible entre export et tableau. Il retrouve les contributions perdues, les filtres implicites et les périodes incomplètes, puis établit les contrôles nécessaires avant de relancer une analyse de pages.

Dawap accompagne la performance et le SEO technique des sites web en reliant données, application et décisions concrètes. Nous pouvons cadrer cette réception avec vos équipes, rendre les transformations vérifiables et concentrer les corrections sur les problèmes dont les preuves dépassent une simple différence de dashboard.