AI SEO In Detail

AI SEO In Detail › Module A · Les fondations › Épisode 5

Épisode 5 · 6:51 · gratuit

GA4 + Search Console + DataForSEO : un seul modèle de données

Croisez Google Analytics 4, Search Console et les SERP DataForSEO par page, hostname, date et requête : schéma SQLite, vue page_summary et normaliseur d'URL unique.

La lecture de la vidéo la charge depuis YouTube (mode privacy-enhanced). La vidéo est en anglais, avec des sous-titres dans votre langue.

Kit gratuit

Modèle de données SEO : schéma SQLite + normaliseur d'URL

seo_data_model.sql + normalize_url.py : cinq tables brutes (Search Console, GA4, SERP et volumes DataForSEO), une vue page_summary qui les joint par page, hôte, date et requête, et l'unique fonction d'URL que chaque import doit utiliser.

  • Charger le schéma — sqlite3 seo.db < seo_data_model.sql crée les cinq tables brutes et la vue page_summary. Ajoutez les périodes à comparer dans la table period.
  • Normaliser à l'import — Importez les lignes Search Console, GA4 et SERP via normalize_url() — la même fonction pour les trois — et récupérez GA4 avec un filtre hostName sur le domaine actuel, à chaque fois.
  • Poser une question — Lancez les requêtes d'exemple en bas du schéma : pages qui ont gagné des impressions mais pas de sessions, et pages en page deux avec un vrai volume et un concurrent juste au-dessus.
Télécharger le schéma + la fonction d'URL (zip)

SQLite · Python 3.9+ · aucune clé incluse · sans inscription

Les fichiers sont en anglais.

Transcription complète (traduite)

1,316 mots · traduction de la narration de l’épisode.

Introduction

Vous avez Search Console dans un onglet, Analytics dans un autre, et un dossier de pages de résultats venant de DataForSEO. Chacun répond à une partie de la question. Aucun n'y répond entièrement.

Alors vous copiez des chiffres entre tableurs, et les chiffres ne concordent pas, et vous ne savez pas lequel est faux.

Dans cet épisode, nous construisons un seul modèle de données, avec l'un de mes propres sites comme exemple. Un petit ensemble de tables avec des clés communes, pour poser une question et obtenir une réponse : pour cette page, quelle visibilité, combien de clics, que se passe-t-il après le clic, et face à qui elle se trouve.

Ce que sait chaque source

Commencez par noter ce que chaque source sait vraiment, et à quel niveau.

Search Console connaît les requêtes et les pages, par jour. Elle ne sait rien de ce qui suit le clic.

Analytics connaît les pages, les sessions et ce que les visiteurs ont fait, par jour. Et il connaît le hostname, ce qui compte plus qu'on ne le croit. Ce qu'Analytics ne connaît pas, c'est la requête de recherche. Pas dans ses données. Il n'y a donc aucun moyen de joindre Analytics à Search Console sur la requête.

DataForSEO connaît les mots-clés et les pages de résultats : pour un mot-clé, à une date, dans un lieu, quelles URL se classent et où.

Regardez ce qu'ils ont en commun. Page et date relient les trois. La requête relie Search Console à DataForSEO. C'est tout. Ce sont vos clés de jointure, et tout cet épisode consiste à les faire correspondre.

Clé de jointure 1 : l'URL

Clé de jointure 1 : l'URL

L'URL est l'endroit où la plupart des jointures échouent. Search Console vous donne l'URL complète, avec protocole et hostname. Analytics vous donne un chemin, souvent avec des paramètres de suivi. DataForSEO vous donne ce que Google affiche dans le résultat.

Voici une page de mon site, écrite de cinq façons. Le chemin est anonymisé, mais chacune de ces formes apparaît dans de vrais exports. Vous normalisez donc chaque URL en une forme canonique avant de la stocker. Mes règles sont simples.

Séparez le hostname du chemin, et gardez le hostname dans sa propre colonne. Mettez le hostname en minuscules. Supprimez le fragment, tout ce qui suit le dièse. Supprimez les paramètres de suivi, comme les UTM. Et choisissez une règle pour la barre oblique finale, puis appliquez-la partout.

Attention aux paramètres qui changent vraiment la page, comme un numéro de page ou un filtre. Ceux-là, vous les gardez. Si vous supprimez tout, deux pages différentes n'en font plus qu'une, et leurs chiffres s'additionnent.

Écrivez cela comme une seule fonction, et utilisez la même pour les trois sources. Si chaque import a sa propre logique de nettoyage, ils finiront par diverger.

Clé de jointure 2 : le hostname, le filtre à ne jamais oublier

Pourquoi garder le hostname dans sa propre colonne ? Parce que les propriétés Analytics vivent plus longtemps que les domaines.

Si vous changez de domaine en gardant la même propriété Analytics, l'ancien domaine peut continuer à y envoyer des données. Souvent c'est du trafic de bots, sur des pages qui portent encore votre balise.

Cela m'est arrivé, sur un site différent de celui de cet épisode. Un tableau de bord affichait plus de cinquante mille sessions. Quand je l'ai séparé par hostname, environ cinquante-cinq venaient du domaine actuel. Le reste était du spam de bots sur un domaine abandonné. Chaque graphique construit sur cette propriété mesurait la mauvaise chose.

Donc chaque requête Analytics de ce modèle filtre sur le hostname actuel. Pas à la fin, dans un graphique, mais dans l'extraction elle-même. Et quand un domaine change, vérifiez hostname contre sessions avant de réutiliser une propriété.

Clé de jointure 3 : la date et la requête

La date semble triviale, mais vérifiez les fuseaux horaires. Search Console rapporte en heure du Pacifique. Analytics rapporte dans le fuseau horaire de votre propriété. Au niveau journalier, quelques heures de trafic peuvent tomber sur le mauvais jour. Pour une analyse hebdomadaire ou mensuelle, ça n'a pas d'importance. Pour une comparaison jour par jour, ça peut en avoir.

La requête est la clé entre Search Console et DataForSEO. Stockez-la en minuscules, sans espaces superflus, avec des espaces simples. Gardez aussi la forme d'origine, car singulier et pluriel sont des mots-clés différents dans les données de volume. Nous verrons pourquoi dans l'épisode vingt.

La table au niveau de la page

La table au niveau de la page

Passons à la conception. Gardez les tables brutes séparées, une par source, chacune dans sa forme naturelle. Puis construisez une vue par-dessus : le résumé par page.

Une ligne par page et par période. De Search Console : clics, impressions et position pondérée par les impressions. D'Analytics : sessions et conversions, filtrées sur le hostname. De DataForSEO : pour les principaux mots-clés de la page, le dernier classement et combien de concurrents se classent au-dessus.

Voici les deux premières sources jointes, sur mon propre site. Une remarque sur ces données : elles sont réelles, issues d'un de mes propres sites, mais anonymisées. Domaine, URL de pages et termes de recherche sont remplacés, pour rester centrés sur les principes et non sur les mots-clés. Les chiffres sont intacts.

Regardez ce qu'une seule ligne vous dit. Cette page a eu environ trois mille impressions, à une position moyenne juste sous dix, et zéro clic. Zéro session. La ligne du dessous raconte presque la même histoire : trois mille cinq cents impressions, trois clics. Ce sont des pistes, obtenues avec une requête au lieu de trois onglets.

Les colonnes DataForSEO se joignent de la même façon, sur la page et la requête. Je les montrerai sur une boutique d'exemple inventée, car les données de résultats sont payantes et ont leur propre partie dans la série.

Remarquez ce qui n'est pas dans la vue : les sessions par requête. Impossible. Analytics ne connaît pas les requêtes, donc un tel chiffre serait inventé.

Poser une question au modèle

Voici pourquoi l'effort en vaut la peine. Avec un seul modèle, des questions qui prenaient un après-midi deviennent une requête.

Par exemple : quelles pages ont gagné des impressions le mois dernier, mais pas de sessions ? Cela combine Search Console et Analytics sur la clé page, pour deux périodes. Sur mon site, c'est une liste de deux. Une page a gagné environ soixante pour cent d'impressions en plus, et ses sessions ont baissé. L'autre a gagné plus de la moitié, et ses sessions sont restées exactement stables. Plus visible, pas plus visitée. Les deux sont vers la position sept, en première page, donc la question c'est le clic : le titre, ou ce qu'il y a d'autre sur cette page de résultats. Dans tous les cas, vous savez où regarder.

Ou : quelles pages sont en deuxième page pour un mot-clé à volume réel, avec un concurrent juste au-dessus ? C'est Search Console, DataForSEO et les données de volume dans une seule jointure. Nous utiliserons exactement ce type de requête dans le prochain module.

Ce que les chiffres ne concorderont jamais

Une dernière chose. Les clics de Search Console et les sessions d'Analytics ne seront jamais égaux pour la même page. Les clics sont comptés par Google. Les sessions sont comptées par votre balise, après consentement, bloqueurs et bots. Les voici côte à côte pour mon site. Remarquez qu'Analytics montre plus de sessions organiques que Search Console de clics, environ un virgule sept par clic sur les quatre semaines. Le canal organique d'Analytics compte tous les moteurs de recherche, Search Console ne compte que Google. Un ratio stable est normal. À surveiller : un ratio qui change brusquement. Et ici c'est le cas : pendant deux semaines il reste autour d'un et demi, puis à partir du vingt-six septembre il saute à environ deux, alors que les clics bougent à peine. Un tel décalage, c'est généralement la mesure, pas le SEO, donc c'est là que je regarderais en premier.

Pour conclure

Trois sources, trois clés, une fonction de normalisation, et un filtre hostname à chaque extraction. Le schéma et la fonction URL sont en lien dans la description. Prochain épisode : mettre en place tout l'espace de travail avec Python, SQLite et Claude Code. À bientôt.