Google Sheets, l’outil SEO multifonction


Madeline
Sur twitter

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é).

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.

https://chrome.google.com/webstore/detail/google-analytics/fefimfimnhjjkomigakinmjileehfopp?utm_source=permalink

Le module Google Analytics pour Google Sheet
Le module Google Analytics pour Google Sheet

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.

https://chrome.google.com/webstore/detail/search-analytics-for-shee/ieciiohbljgdndgfhgmdjhjgganlbncj?utm_source=permalink

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.

https://chrome.google.com/webstore/detail/majestic/ibgpannijkpmffaofdhoiabomgmiohdk?utm_source=permalink

Majestic Data Extractor sur Google Sheet
Récupérer les données Majestic SEO sur Google Sheet

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.

Aperçu des données Majestic SEO pour une liste d'URL (issues de Google Search Console)

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

Exemple de recherchev
La rechercheV pour réunir les données Google Search Console et l’onglet Majestic SEO

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)

Google Sheet : le & en action pour concaténer

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)

Les différentes façons de concaténer sur Google Sheet

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))

Array formula + transpose en action

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))); » « )) )

En mode bourrin : augmenter la formule pour avoir tous les résultats dans une seule colonne

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 :

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.

On traduit nos combinaisons avec 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 !

Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *