Sommaire
Je suis actuellement Responsable SEO et web Analytics du groupe EBRA. J'ai ouvert ce blog pour partager des astuces, des tuto, ou des informations plus orientées techniques et opérationnelles.
- Looker Studio : gérer les dates et la granularité dans les graphs - 14 mars 2023
- Optimiser les pages avec les bons mots clés (DataStudio + Google Search Console) - 29 novembre 2020
- Seocamp 2020, les slides ? - 9 octobre 2020
Voici un article pour accompagner la présentation que j’ai faite à Angers, lors du 7e webcampday. Les slides étaient assez animés, en pdf, ça rend moins bien :
Google Sheet est la version d’Excel en ligne proposée par Google. Evidemment, comparé à l’original, à savoir Microsoft Excel, Google Sheet a quelques défauts : il devient horriblement lent lorsqu’on traite beaucoup de données. Il appartient à Google, et ce n’est peut-être pas l’outil idéal pour surveiller ses backlinks.
Alors pourquoi parler de Google Sheet alors que c’est une pâle copie d’Excel ? Déjà, tout le monde n’a pas la même version qu’excel : si vous êtes sur mac, peut-être que spreadsheet est mieux… si vous êtes sur windows, on n’a pas forcément tous la même version, ce qui peut compliquer la tâche lorsqu’on fait un tutoriel. Au moins sur spreadsheet, tout le monde utilise la même version.
L’outil de Google a quelques avantages : on peut ajouter des modules complémentaires, ce qui permet de collecter directement les données dans le tableur (vous me direz : bah, excel aussi,…. c’est pas faux…mais si vous connaissez les modules sur Excel vous semblez être un utilisateur assez avancé).
Cet article est donc le 1er d’une série pour découvrir les fonctionnalités de Google Sheets bien pratiques lorsqu’on fait du SEO. On partira d’utilisation simples à des connaissances plus poussées :
- 1ere partie : les modules complémentaires et fonctions basiques (similaires à excel)
- 2e partie : les fonctions import et regex, spécifiques à Google Sheets, le point fort de Google Sheets
- 3e partie : les fonctions personnalisées avec Apps scripts
Il existe des fonctions spécifiques à Google Spreadsheet qui sont vraiment ultra pratiques en SEO. Pour obtenir les mêmes, il faudra installer l’addon seo tools for excel…qui n’est pas très cher, mais si on peut avoir la même chose gratuitement, pourquoi s’en passer ?
Enfin, avec quelques connaissances en javascript, google sheet peut vraiment devenir un outil puissant. Si vous n’aimez pas Excel, j’espère au moins que cet article permettra de comprendre en quoi cet outil est ultra pratique lorsqu’on fait du SEO, même si les fonctions sont parfois ultra moches.
Mais repassons à Google Spreadsheet et la première fonctionnalité : collecter des données sans passer par des fonctions.
Les modules complémentaires utiles
Google Analytics
C’est un classique. Ce module peut avoir perdu en intérêt depuis qu’il y a Google Data Studio. On a moins besoin de passer par excel pour faire des bilans depuis qu’il y a Google Data Studio. Pourtant Google Data Studio a des limites, et la première et la surcouche d’échantillonnage : vous pouvez avoir les « vrais » chiffres dans Google Analytics et avoir un échantillonnage dans Google Data Studio. L’add-on Google Analytics permet d’empêcher cet échantillonnage. Et pour obtenir certaines informations, il vaut parfois mieux passer par Google Sheet et connecter ensuite Google Sheet à Data Studio. Donc sans m’attarder sur les possibilités, Google Analytics est un « must have », c’est un impondérable pour pouvoir mesurer les performances SEO.
Lorsque vous obtenez les chiffres de Google Analytics dans Google Sheet, vous pouvez ensuite les retravailler et faire pas mal de choses.
Search Analytics for sheet
Ce module permet de récupérer les chiffres de Google Search Console directement dans Google Sheet. Il est possible de paramétrer des backups quotidiens (ou mensuels) pour conserver un historique encore plus long que 16 mois.
Il est également très facile d’obtenir la combinaisons mot clé/page de destination. Le module est moins complet que l’API de Search Console (toutes les données de l’API ne sont pas dans le module) mais il offre déjà un grand jeu de données.
Majestic SEO
Le SEO, c’est une histoire de liens. Pour apprivoiser le SEO, il faut créer des liens, et Majestic SEO permet de mesurer tout cela. Si vous avez un abonnement Majestic SEO (et accès à l’API), vous pouvez avoir les données dans Google Sheet. Via le module, on peut soit obtenir les données Majestic SEO en soumettant une liste d’URL via le formulaire… ou bien lorsqu’on a une liste d’URL présente dans Google Sheet, on peut lui demander d’enrichir les informations en obtenant différents indicateurs pour la dite liste d’URL.
Donc imaginez, vous avez récupérer les statistiques d’audience de votre site, en regardant quelles sont les pages qui reçoivent le plus de trafic SEO.
Ensuite, avec cette liste d’URL, vous regardez dans google search console quelle est la position moyenne, quels sont les mots clés qui apportent des clics… puis par dessus cela, vous regardez si ces URL reçoivent des liens, en regardant également le nombre de domaines référents puis le trust flow et citation flow de chaque page, ça donne déjà un bon nombre d’informations utiles en SEO.
Via les 3 modules, vous avez récolté les données. Maintenant, via les fonctions, on va pouvoir tout réunir.
Les fonctions utiles en SEO
recherchev est une fonction avancée dans excel mais un basique pour du SEO
La recherchev, ou vlookup, est une fonction indispensable. Cette fonction permet de chercher des données dans une autre table : par exemple vous avez les URL de search console dans une table, vous avez les données majestic dans une autre… et bien pour les URL issues de google search console, vous voulez chercher les indicateurs majestic SEO.
=recherchev(valeur recherchée, table où rechercher les nouvelles informations, numéro de colonne où se trouve les bonnes données, vrai ou faux pour une recherche large (pour une recherche exacte = faux)).
Le V signifie recherche verticale. Si vous voulez faire une recherche horizontale (les données se trouvent sur une autre ligne), il faut utiliser rechercheH.
Concatener : assembler du texte
On a parfois besoin d’assembler rapidement du texte. Pour cela il existe la fonction concatener, ou bien l’utilisation du &..
=A1&B1 correspond exactement à =concatener(A1;B1) ou bien concatener(A1:B1) ou encore concat(A1;B1)
Si vous avez 3 éléments, par exemple vous souhaitez mettre un espace entre chaque élément vous pouvez soit faire =A1& » « &B1 ou bien =concatener(A1; » « ;B1).
S’il faut assembler encore plus de cellules, c’est un peu embêtant de tout faire à la main. Du coup il existe une autre fonction join
join : concaténer plus rapidement une plage de données
Si vous avez les cellules A1 à Z1 à concatener, avec un espace à chaque fois entre, vous pouvez faire : =A1& » « &B1& » « &C1& » « &…
C’est long. Du coup, vous pouvez faire =join(séparateur; plage de données), soit =join( » « ;A1:Z1). Sans le séparateur, on aurait pu simplement faire =concatener’A1:Z1).
Et si vous voulez supprimez les espaces inutiles (des cellules vides, éviter d’avoir 2 espaces consécutifs), la fonction textjoin sera votre meilleure alliée.
arrayformula : une fonction qui s’applique sur tout un tableau
arrayformula : la fonction pratique googlesheet. Ici, elle va nous servir pour accélérer la concaténation. Arrayformula permet d’utilisée une plage de données pour des fonctions qui n’autorisent que des cellules. Vous mettez la formule dans une cellule, la formule s’applique pour toute la plage de données, pour le tableau.
La fonction peut permettre d’accélérer certains calculs. C’est un travail à la marge mais lorsque vous avez des centaines voir des milliers voire des centaines de millier de cellules qui ont des fonctions, Google Sheet (et Excel aussi) peut peiner… et le processus de calcul peut devenir long… très long… Du coup si on a qu’une seule fonction qui s’appliquer à des milliers de cellule, c’est un peu plus rapide. Ca ne sera pas super long…mais pas super rapide non plus.
si on veut concaténer toutes les cellules A avec toutes les cellules B, ce sera : =ARRAYFORMULA(A1:A& » « &B1:B)
Faire toutes les combinaisons possibles avec ArrayFormula
Si vous avez des mots dans la colonne A et d’autres en colonnes B, et vous voulez faire toutes les combinaisons possibles, on peut également dire : on a tous les mots dans la colonne A, à partir de A2…. et transformer la colonne B en une ligne….puis dire qu’on veut appliquer la concaténation à tout le tableau, toutes les lignes via toutes les colonnes.
Techniquement, ca devient :
ARRAYFORMULA(A2:A27& » « &transpose(B2:B27))
Vous pourrez dire : c’est bien, mais ce sera largement plus pratique dans une seule colonne.
Google Sheet : faire toutes les combinaisons de mots clés possibles (combiner toutes les cellules A avec toutes les cellules B)
Ici, malheureusement, il n’y a pas de formule magique. Enfin si, il y en a une mais elle est compliquée :
= ArrayFormula(transpose(split(rept(CONCATENER(A2:A&" ");NBVAL(B2:B));" "))&" "&transpose(split(CONCATENER(rept(B2:B&" ";NBVAL(A2:A)));" ")) ) 
On sauvegarde ceci dans un document et on remercie chaudement Max Makhrov qui avait répondu à ce problème sur stackoverflow.
L’avantage d’excel et de google Sheet, c’est qu’il y a de nombreux utilisateurs…donc probablement quelqu’un qui a déjà eu le problème…et quelqu’un a peut être déjà trouvé une solution. Du coup, je vous laisse regarder le stack overflow pour trouver comment combiner 3 éléments.
Spoiler : il faut combiner 2 éléments, obtenir le résultat, puis combiner ce résultat avec ce nouvel élément #inception
Mais il sera possible d’avoir ce résultat :
Si on ne veut pas s’embêter avec cette fonction complexe, on ira directement voir les fonctions personnalisées.
GoogleTranslate : traduire directement dans Google Sheet
Si ensuite, vous souhaitez traduire ces combinaisons dans une autre langue, Google a eu la bonne idée d’intégrer une fonction qui fait appel à Google translate…et l’a logiquement appelé : googletranslate.
Attention, la fonction a les avantages et les défauts de google translate : cela ne remplace pas le travail d’un vrai interprète…mais ça peut dépanner.
Vous pouvez combiner cette fonction avec DetectLanguage pour gagner encore un peu de temps : traduis moi cela en anglais, et débrouille toi pour reconnaitre la langue d’origine.
Suivre les cours des bourses directement dans Spreadsheet avec GoogleFinance
Google connecte certains de ses services directement à Google Spreadsheet. On vient de le voir, c’est le cas avec Google Translate…mais aussi avec Google Finance. Et vous l’aurez deviné, la fonction s’appelle…. tadaaa Googlefinance.
Hop, c’est tout pour le moment… on verra les options plus poussées une autre fois !