Comment exploiter efficacement ses données Google Search Console grâce à BigQuery ?

comment réconcilier son transfert Google Search Console → BigQuery avec l’interface Search Console

Comment exploiter efficacement ses données Google Search Console grâce à BigQuery ?Introduction - Pourquoi cet article ?1. Présentation de la Google Search Console 1. Les KPIs de la Google Search Console 2. Explication des scopes 1. Scope site 2. Scope url 3. Qu’est ce que cela implique sur nos KPIs préférés ? 2. Présentation de l’import natif BigQuery 1. Déverser sa Google Search Console vers BigQuery 2. Présentation de l’output BigQuery et réconciliation avec l’interface 3. Réconciliation de son export natif 1. Exploiter la table site_impression Quelques tips : 2. Exploiter la table url_impression Outro

Introduction - Pourquoi cet article ?

En 6 ans en tant que consultant & data analyste, dont une bonne partie au sein d’équipes SEO, la question de l’exploitation de la donnée Search Console s’est très souvent posée.

C’est un outil incontournable que l’on retrouve dans toutes les équipes et dont on peut tirer des insights super intéressants pour peu que l’on comprenne comment elle fonctionne et qu’on sache comment exploiter l’ensemble des données qu’elle met à disposition.

🚫

Manque de bol, c’est évidemment ces deux points qui pèchent le plus.

D’expérience, le fonctionnement de la Search Console, que ce soit par la différence entre le scope site et le scope url ou “l’anonymisation” de certains termes de recherches (non-provided) par Google, a toujours posé problème.

L’interface, comme le connecteur natif entre la Search Console et Looker Studio sont très contraignants et ne permettent pas d’exploiter l’outil à son plein potentiel

C’est pourquoi, ces dernières années, les équipes SEO les plus matures en ce qui concerne la gouvernance et l’activation de leur données, déversent leur Search Console dans des outils comme Google BigQuery. Et c’est d’autant plus vrai depuis que Google a mis en place un transfert natif qui permet en quelques clics seulement de faire le lien entre sa Google Search Console et BigQuery.

Et c’est donc sous ce prisme que j’ai décidé d’écrire cet article dont voici les objectifs :

Présenter la Search Console et décrire son fonctionnement

Faire un petit guide sur comment déverser sa Google Search Console dans BigQuery

Décrire le modèle de donnée issu de la connection native

Vous aider à réconcilier son export BigQuery avec l’interface Google Search Console

Présenter quelques tips pour aller un peu plus loin que le simple CC - CV de l’interface

1. Présentation de la Google Search Console

Dans cet article, je ne parlerai que de l’onglet “Performance” de la SearchConsole. C’est l’onglet le plus utilisé par nos webmestres & experts SEO et c’est celui qui permet de visualiser les données que nous allons déverser dans BigQuery.

1. Les KPIs de la Google Search Console

Rapidement, nous allons commencer par lister les 4 KPIs principaux que la SearchConsole et expliquer à quoi ils correspondent

KPI	Définition
Impressions	Nombre de fois que vos urls ou votre site a été affiché sur une page de résultats Google (SERP) sur la période sélectionnée
Clicks	Nombre de fois que vos urls ou votre site a été cliqué depuis une SERP sur la période sélectionnée
CTR (taux de clics)	Simple division entre vos clics et vos impressions ie : Nombre de fois qu’un utilisateur a cliqué sur l’une de vos urls quand il en a eu l’occasion
Position Moyenne	Position moyenne à laquelle apparaissent vos urls ou votre site quand ils sont affichés sur une SERP

Simple, efficace, 4 KPIs, c’est tout.

2. Explication des scopes

Rentrons maintenant dans le vif du sujet : les scopes de la Search Console ! Selon moi, c’est l’élément principal de confusion et donc indirectement, le point le plus important de cet article.

Il est donc important de savoir que la Search Console permet de visualiser la donnée sous deux niveau de granularité différents, site et urls.

1. Scope site

C’est le scope par défaut pour l’onglet performance.

👨‍🎓

Principes de base :

Retenons que si aucun filtre n’est appliqué sur les urls, on reste sur le scope site.

Sur une requête, si plusieurs urls de votre site se positionnent, la Search Console prendra la meilleure position et ne comptera qu’une seule impression pour le calcul du CTR et de la position moyenne.

Il correspond à la table site_impression dans l’export BigQuery et dans la connexion native entre la Google Search Console et Looker Studio par exemple.

2. Scope url

Vous l’auriez donc compris, le scope url est un simple changement de granularité qui s’opère dès qu’on applique un filtre sur les urls.

👨‍🎓

Principes de base :

Le changement de scope s’opère dès qu’un filtre est fait sur les urls.

Fonctionne même si la règle n’exfiltre aucune url.
Pour avoir une vision exhaustive des impressions urls, il faut appliquer un filtre qui peut sembler un peu contre-intuitif Exemple ici avec un filtre sur url matches regex “ .* ”

Quand on passe au scope url, chaque impression/position est prise en compte même si vous positionnez plusieurs urls sur la même requête.

Correspond à la table url_impression dans l’export BigQuery et dans la connexion native entre la Google Search Console et Looker Studio par exemple.

3. Qu’est ce que cela implique sur nos KPIs préférés ?

KPI	Impacts
Impressions	→ On observera plus d’impressions au scope url (parfois beaucoup plus si votre site est très sujet au multipositionning & aux sitelinks).
Clicks	→ Plus de clics aussi, même si c’est dans une moindre mesure
CTR	→ Mécaniquement donc un CTR plus faible
Position Moyenne	→ Une position moyenne différente (pas forcément toujours dans le même sens selon les sites)

2. Présentation de l’import natif BigQuery

1. Déverser sa Google Search Console vers BigQuery

Si vous souhaitez vous amuser, voici la documentation officielle. Que je vous résume très rapidement ici :

Dans votre projet BigQuery, allez dans la bibliothèque d’API (API & Service > Bibliothèque) pour activer l’API de la GSC

Autoriser le compte de service de la search console dans l’admin afin que ce dernier puisse accéder à votre espace BQ et y déverser de la donnée → Allez dans l’onglet IAM and Admin et donner à “search-console-data-export@system.gserviceaccount.com” les accès suivant : a) BigQuery JobUser b) BigQuery Data Editor

Allez dans votre SearchConsole, dans l’onglet Settings > Bulk data export

⚠️

Dans le cas de “sous-propriété” pour des répertoires de langues ou des sous-section d’un même site, si on souhaite monitorer au scope site, il faut faire un export par propriété, sinon tout sera agrégé au niveau domaine dans la table site_impression. On perdra alors cette granularité.

→ Il reste possible de le faire au scope url mais dans certains cas, on souhaiterais avoir les deux → Dans ce cas précis, on peut faire un export global et un export par propriété afin de disposer de toute la granularité nécessaire

Renseignez les informations du projet BigQuery qui va recevoir les données (celui sur lequel on vient de faire tout le setup idéalement, sinon, retour à l’étape 1)

Attendez que les données arrivent dans le projet

2. Présentation de l’output BigQuery et réconciliation avec l’interface

Une fois que l’export a été mis en place, la donnée va naturellement et quotidiennement se déverser dans BigQuery.

Points saillants à noter :

L’export contient trois tables

Une table de log contenant l’ensemble des exports unitaires (deux lignes par jour)
Une table site_impression

Une table assez simple qui permet d’avoir un overview des données au scope site.

Schéma de la table scope_site que vous allez récupérer.

Une table url_impression

Une table beaucoup plus complexe qui permet d’avoir une vision plus granulaire de la performance SEO
Cependant, de façon très macro, les deux tables fonctionnent exactement de la même manière (l’export au scope url permet juste de filtrer plus efficacement sa donnée sur un type d’affiche ou un autre)

Schéma de la table scope_url que vous allez récupérer

⚡

Tips :

La query ci-dessous permet de voir les différents booléens qu’il pourrait être intéressant de conserver Si la somme > 0 ⇒ certaines lignes sont en “true” pour le booléen concerné

sql
SELECT  
sum(cast(is_amp_top_stories as int)) as is_amp_top_stories,
sum(cast(is_amp_blue_link as int)) as is_amp_blue_link,
sum(cast(is_job_listing as int)) as is_job_listing,
sum(cast(is_job_details as int)) as is_job_details,
sum(cast(is_tpf_qa as int)) as is_tpf_qa,
sum(cast(is_tpf_faq as int)) as is_tpf_faq,
sum(cast(is_tpf_howto as int)) as is_tpf_howto,
sum(cast(is_weblite as int)) as is_weblite,
sum(cast(is_action as int)) as is_action,
sum(cast(is_events_listing as int)) as is_events_listing,
sum(cast(is_events_details as int)) as is_events_details,
sum(cast(is_search_appearance_android_app as int)) as is_search_appearance_android_app,
sum(cast(is_amp_story as int)) as is_amp_story,
sum(cast(is_amp_image_result as int)) as is_amp_image_result,
sum(cast(is_video as int)) as is_video,
sum(cast(is_organic_shopping as int)) as is_organic_shopping,
sum(cast(is_review_snippet as int)) as is_review_snippet,
sum(cast(is_special_announcement as int)) as is_special_announcement,
sum(cast(is_recipe_feature as int)) as is_recipe_feature,
sum(cast(is_recipe_rich_snippet as int)) as is_recipe_rich_snippet,
sum(cast(is_subscribed_content as int)) as is_subscribed_content,
sum(cast(is_page_experience as int)) as is_page_experience,
sum(cast(is_practice_problems as int)) as is_practice_problems,
sum(cast(is_math_solvers as int)) as is_math_solvers,
sum(cast(is_translated_result as int)) as is_translated_result,
sum(cast(is_edu_q_and_a as int)) as is_edu_q_and_a,
sum(cast(is_product_snippets as int)) as is_product_snippets,
sum(cast(is_merchant_listings as int)) as is_merchant_listings,
sum(cast(is_learning_videos as int)) as is_learning_videos,

FROM `reporting-digital.searchconsole_unnest.searchdata_url_impression`

L’export commence le jour où vous mettez en place le transfert de données, donc pas d’historique (et c’est assez important à noter). L’export n’est pas rétroactif, et se prive des 16 mois qu’offre l’interface.

Il peut être donc intéressant de mettre en place l’export le plus vite possible
Dans un premier temps, il faudra donc utiliser les deux sources en parallèle

Dans les deux cas, l’export contient des informations très intéressantes que l’interface n’offre pas : le volume de clics et d’impressions réalisés sur le “non provided” par exemple.

Ce sont toutes les queries que la Search Console considère comme “sensibles” ou contenant des informations sur l’utilisateur.
Les lignes concernées afficheront une query “null” et un True pour la colonne “is_anonymized_query”

3. Réconciliation de son export natif

1. Exploiter la table site_impression

L’idée principale de cette table est de pouvoir “recréer” l’onglet performance de l’interface et de suivre les KPIs principaux de la Google Search Console au niveau macro.

Voici une requête SQL permettant simplement de vérifier que la donnée est ISO à l’interface :

sql
SELECT  
	search_type, 
	SUM(impressions) as impressions, 
	SUM(clicks) as clicks, 
	ROUND(SUM(clicks) / SUM(impressions) * 100, 1) as CTR,
	SUM(sum_top_position) as sum_top_pos, 
	ROUND(1+ (SUM(sum_top_position)/ SUM(impressions)), 1) as position_moyenne_site
FROM `votreprojetbigquery` 
WHERE date BETWEEN "2024-01-03" AND "2024-01-17"
GROUP BY search_type

Voici le résultat de la requête :

search_type	impressions	clicks	CTR	position_moyenne_site
WEB	55506	1250	2.3	23.6
IMAGE	264	2	0.8	61.2

Et le résultat de l’interface Google Search Console :

→ Nous retrouvons ainsi les données de notre Google Search Console avec un scope site.

Quelques tips :

👉 Position moyenne

Pour que la position moyenne soit ISO à ce qu’affiche l’interface, il faut ajouter 1 à notre requête.

sql
1+ (sum(sum_top_position)/ sum(impressions))

Pourquoi ? Parce que la Google Search Console n’est même pas cohérente avec elle même et que l’export commence à 0 et l’interface à 1 pour la top position… Lien vers la doc

👉 Echantillonnage

Dans certains cas, si le volume de données est très élevé, il peut arriver que la Google Search Console l’échantillonne, auquel cas, il pourra y avoir une différence mineure (en général inférieure à 1%)

👉 Search Type

Par défaut, l’interface est filtrée sur le search_type “WEB” qui correspond aux liens bleus des SERPs Google. Il est aussi possible de ne conserver que cette data en filtrant sur la colonne portant le même nom.

⚡

Pour conclure : Au scope site, aucune difficulté, le modèle de donnée est très simple et une simple query nous donne des infos ISO au click près sur l’interface, Looker Studio via connecteur natif & BigQuery.

2. Exploiter la table url_impression

Cette table viendra surtout compléter la première en descendant d’un niveau de granularité.

Voici une requête SQL permettant simplement de vérifier que la donnée est ISO à l’interface :

sql
SELECT  
	search_type, 
	sum(impressions) as impressions, 
	sum(clicks) as clicks, 
	round(sum(clicks) / sum(impressions) * 100, 2) as CTR,
	round(1 + (sum(sum_position)/ sum(impressions)), 2) as position_moyenne_url
	
FROM `reporting-digital.searchconsole_unnest.searchdata_url_impression` 

WHERE data_date >= "2024-01-03"
and data_date <= "2024-01-17"
and search_type = "WEB"

group by search_type

order by impressions desc

Voici le résultat de la requête :

search_type	impressions	clicks	CTR	position_moyenne_url
WEB	72752	1273	1.75	21.42
IMAGE	288	2	0.69	63.28

👉

Rappel : Sur l’interface Search Console, cette information n’existe pas vraiment (en théorie) dans la mesure ou l’interface n’affiche que des valeurs au scope site sauf si on filtre sur un subset d’urls Il existe cependant un moyen contre-intuitif pour arriver à ses fins : → Utiliser un filtre qui ne filtre aucune url pour forcer la Search Console à passer en scope URL

De cette manière, sans avoir filtré aucune URL, nous obtenons les chiffres suivants: … qui sont ISO au click près à ceux que nous avons sur BigQuery

⚡

Conclusion sur la manipulation des exports : Par défaut, la donnée est immédiatement ISO à l’interface, il faut juste faire attention à deux ou trois petites choses si on ne veut pas se foirer lors de sa première exploration :

Selon le scope que l’on veut, il faut utiliser la bonne table et la comparer avec ou sans filtre à l’interface

Filtrer sur le bon search_type (WEB le plus souvent)

Recalculer sa position moyenne en divisant le champ sum_position (ou sum_top_position dans le cas du scope site) par les impressions et en ajoutant 1

Ne pas oublier le top provided (qui rajoute des lignes mais qui n’impacte en rien le total car il est masqué sur l’interface mais pris en compte malgré tout)

Rien de plus, honnêtement, c’est beaucoup moins sorcier que ça en a l’air…

Outro

Et voilà, même moi je suis déçu…

En commençant mon exploration de l’export, après autant de retours clients, je pensais trouver un modèle de données super complexe, être poussé dans mes retranchements et devoir redoubler d’ingéniosité pour tout déplier… Mais rien de tout ça.

C’est simplement que l’outil est tout sauf clair de base et que du coup personne n’y comprend rien. En revanche, il est possible d’aller beaucoup plus loin avec ces données :

Profiter de la centralisation des données dans BigQuery pour faire des croisements avec d’autres sources de données.

Reventiler sa donnée “non provided” afin d’estimer au mieux sa performance marque vs hors-marque

Etc.

Si malgré tout, vous avez encore des difficultés avec votre Search Console, ou simplement que vous souhaitez aller plus loin sur tous vos sujet data SEO ou data en général, n’hésitez pas à me contacter sur linkedin pour en discuter. Sur ce, Ciao !

✍️

L’auteur : Xavier Stevens

Data Analyst chez UnNest, je vous aide à transformer votre donnée en insights actionnables.

Avec le SQL comme LV3, je modélise & transforme tout type de données afin de conduire des analyses et in fine aider mes clients à prendre des décisions data driven.

✉️ Me contacter : xavier.s@unnest.co

↗️ Me suivre sur LinkedIn

fullname	type	description
data_date	DATE
site_url	STRING
query	STRING
is_anonymized_query	BOOLEAN	Parfois, google ne donne pas l’information sur le terme de recherche qui a débouché sur le clic (dans ce cas la, ce champ sera 1 et la query sera null)
country	STRING
search_type	STRING
device	STRING
impressions	INTEGER
clicks	INTEGER
sum_top_position	INTEGER	Somme des positions (meilleure URL positionnée par impression)

fullname	type	description
data_date	DATE
site_url	STRING
url	STRING
query	STRING
is_anonymized_query	BOOLEAN	Parfois, google ne donne pas l’information sur le terme de recherche qui a débouché sur le clic (dans ce cas la, ce champ sera 1 et la query sera null)
is_anonymized_discover	BOOLEAN
country	STRING
search_type	STRING
device	STRING
is_amp_top_stories	BOOLEAN
is_amp_blue_link	BOOLEAN
is_job_listing	BOOLEAN
is_job_details	BOOLEAN
is_tpf_qa	BOOLEAN
is_tpf_faq	BOOLEAN
is_tpf_howto	BOOLEAN
is_weblite	BOOLEAN
is_action	BOOLEAN
is_events_listing	BOOLEAN
is_events_details	BOOLEAN
is_search_appearance_android_app	BOOLEAN
is_amp_story	BOOLEAN
is_amp_image_result	BOOLEAN
is_video	BOOLEAN
is_organic_shopping	BOOLEAN
is_review_snippet	BOOLEAN
is_special_announcement	BOOLEAN
is_recipe_feature	BOOLEAN
is_recipe_rich_snippet	BOOLEAN
is_subscribed_content	BOOLEAN
is_page_experience	BOOLEAN
is_practice_problems	BOOLEAN
is_math_solvers	BOOLEAN
is_translated_result	BOOLEAN
is_edu_q_and_a	BOOLEAN
is_product_snippets	BOOLEAN
is_merchant_listings	BOOLEAN
is_learning_videos	BOOLEAN
impressions	INTEGER
clicks	INTEGER
sum_position	INTEGER	somme de toutes les positions pour toutes les urls