1. Présentation de la Google Search Console (GSC) et des spécificités de son modèle de données.

2. Présentation du data transfert natif → Comment déverser sa donnée GSC dans BigQuery

3. Présenter une modélisation de donnée qui permet de réconcilier sa donnée brute avec la donnée de l’interface web.

4. Donner quelques tips pour aller un peu plus loin.

1. Prérequis : disposer d’un projet bigquery qui servira à recevoir la donnée. → Voici la documentation google pour la création d’un projet BigQuery

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

2. Autorisez 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

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

4. 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 0)

2. Attendez que les données arrivent dans le projet

• 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 searchdata_site_impression (scope site) que vous allez récupérer.

fullname	type	Commentaire
data_date	DATE
site_url	STRING
query	STRING
is_anonymized_query	BOOLEAN	Parfois, google ne donne pas l’information sur le keyword recherché par l’utilisateur (dans ce cas la, ce champ prendra la valeur “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)

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”

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

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

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"

group by search_type

👉

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

1. Cas IA Overview
Pour rappel, IA Overview est une SERP feature qui n’est pas encore déployée en France. C’est un résumé généré par IA affiché directement dans la page de résultats, qui donne une synthèse rapide d’une requête et propose des liens pour approfondir (souvent placé en haut des résultats).
Illustration de l’affichage IAO sur une SERP UK.



Pour ce qui est de la donnée IA Overview dans la GSC :
Documentation google :



Tableau récap :

KPI	Impacts
Impressions	→ Au scope site, seule la position de la meilleure URL sera prise en compte. → Au scope URL, toutes les positions seront comptabilisées. Notes : ne comptent comme imprimées que les URLs qui ont été réellement affichées. C’est notamment important dans le cas ou certains liés sont derrière un bouton dépliant (en général au delà de la 3ème position).
Clicks	→ Au scope site, seul le premier clic est comptabilisé à comprendre, lors de ctrl + clics multiples, seuls le premier clic sera pris en compte → Au scope URL, tous les clics sont comptabilisés
CTR	→ RAS (ratio entre les clics et les impressions)
Position Moyenne	Pour ce qui est de la position, Google considère que l’ensemble du bloc occupe une position unique dans la SERP. → Au scope site, RAS même si cela implique que tous les sites présents dans IAO recevront pour un même keyword, une impression sur la même position… → Au scope URL par contre, on retrouve un fonctionnement similaire aux sitelinks. Différentes URLs peuvent recevoir une impression sur la même position si un site positionne plus d’une URL dans le bloc IAO (car toutes seront interprétées comme faisant partie d’un même bloc avec sa position organique).

2. Cas IA Mode
Pour rappel, IA mode est une feature qui n’est pas encore déployée en France.
Comme le fait ChatGPT, IA Mode est une expérience de recherche conversationnelle avec un LLM dans Google Search : au lieu d’afficher surtout une liste de liens, Google génère une réponse IA plus complète, avec la possibilité de poser des questions de suivi, et en mettant des liens vers des sources pour approfondir.
Pour ce qui est de la donnée IA Mode dans GSC :
Documentation google :



Tableau récap :

KPI	Impacts
Impressions	→ Au scope site, seule la position de la meilleure URL sera prise en compte. → Au scope URL, toutes les positions seront comptabilisées.
Clicks	→ Au scope site, seul le premier clic est comptabilisé à comprendre, lors de ctrl + clics multiples, seuls le premier clic sera pris en compte → Au scope URL, tous les clics sont comptabilisés
CTR	→ RAS (ratio entre les clics et les impressions)
Position Moyenne	Pour IA Mode, RAS. Le seul soucis reste qu’il n’y a aucun moyen d’identifier une position IA Mode vs une “vraie” position SEO.

• Profiter de la centralisation des données dans BigQuery pour faire des croisements avec d’autres sources de données.
• Donnée site centric (outils analytics)
• Donnée de positionnement (notamment pour apporter un peu de lumière sur les nouveaux sujets IAO).
• Donnée techniques
• Etc.

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

• Conduire des analyses pour identifier de potentielles créations/optimisations de contenus.

• Etc.

✍️

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