Comment découvrir des données cachées au niveau des mots-clés à l’aide de Google Sheets

Comment découvrir des données cachées au niveau des mots-clés à l’aide de Google Sheets

Comment découvrir des données cachées au niveau des mots-clés à l'aide de Google Sheets

TL;DR

Les données au niveau des mots-clés n’ont pas disparu, elles sont juste plus difficiles à obtenir. En utilisant Google Sheets pour marier les données de la console de recherche et de Google Analytics dans une feuille, vous aurez vos mots-clés principaux et les mesures d’engagement de la page de destination ensemble (gratuitement !). Il ne s’agit pas de données parfaites au niveau des mots-clés, mais en 7 étapes, vous pouvez voir les mots-clés qui ont conduit les clics vers une page et les mesures organiques d’engagement pour cette page, le tout réuni en un seul endroit. Le module complémentaire de Google Analytics pour Google Sheets permet de mesurer l’engagement organique sur la page de destination, et le module complémentaire de Search Analytics pour Sheets permet d’extraire les premières requêtes par page de destination à partir de la console de recherche. Ensuite, utilisez VLOOKUP et une formule de tableau pour combiner les données dans un nouvel onglet qui contient les pages de destination spécifiées, les mots clés qui ont généré les clics et les mesures d’engagement spécifiées.

Comment ça, vous ne savez pas quel mot-clé est à l’origine de cette conversion ?

Depuis la disparition des données au niveau des mots-clés dans Google Analytics, les SEO s’efforcent de lier les stratégies de mots-clés à des mesures légitimes et mesurables. Nous consacrons une grande partie de notre temps, de nos ressources et de nos efforts de recherche à choisir le thème de mot-clé parfait, plein de termes pertinents sur le plan thématique qui tirent parti des nouvelles stratégies sémantiques. Nous nous assurons de créer les métadonnées parfaites, en positionnant nos principaux mots clés au bon endroit dans la balise titre et en les intégrant de manière transparente dans la méta description, mais ensuite ? Nous surveil1ents et nous nous basons sur les métriques des pages d’atterrissage, mais toutes nos données sont disjointes et nous devons extrapoler des informations basées sur une compréhension limitée de la performance réelle de nos thèmes.

Mais il y a une bonne nouvelle ! Les données au niveau des mots-clés sont toujours là – il est juste beaucoup plus difficile d’y accéder étant donné la structure des plateformes existantes. Si vous êtes comme moi, vous avez les mesures de votre page d’accueil dans Google Analytics, les données sur les clics de vos mots-clés dans la console de recherche et les thèmes de vos mots-clés dans un programme manuel (probablement Excel). Étant donné la façon dont Google Analytics exporte les données, la façon dont la console de recherche sépare les mots clés et les pages d’atterrissage, et les nuances que vous avez appliquées à vos propres documents de thèmes de mots clés, il est difficile de marier toutes les données d’une manière qui vous donne des informations exploitables et des capacités de surveillance des données en temps réel.

Difficile… mais pas impossible. Entrez : Google Sheets. En 7 étapes faciles, vous pouvez rassembler toutes ces données sur une seule feuille afin de voir votre thème de mots clés, les mots clés pour lesquels vous obtenez des clics, le classement de la page et toute mesure organique pour cette page (pensez aux mesures d’engagement, de conversion, de revenus, etc.), le tout au même endroit ! ), le tout au même endroit ! Vous pouvez surveiller les opportunités de mots-clés à une distance saisissante, vérifier si les mots-clés pour lesquels vous voulez vous classer le sont réellement, et quels termes et thèmes sont à l’origine de la majorité de vos revenus ou conversions. En fin de compte, tout cela vous donne des mesures concrètes que vous pouvez suivre et modifier grâce à des stratégies de mots-clés. C’est beaucoup plus facile que vous ne le pensez, et les étapes ci-dessous vous permettront de démarrer.

Suivez ce guide pour créer une feuille Google de base qui relie la console de recherche, Google Analytics et votre thème de mots clés en un seul endroit sur quelques pages, et vous serez alors en bonne voie pour créer des feuilles automatisées qui vous donneront un meilleur aperçu des données au niveau des mots clés !

Étape 1 : Obtenez les modules complémentaires de Google Analytics et Search Analytics for Sheets

Le module complémentaire Google Analytics vous permettra d’extraire n’importe quelle mesure de Google Analytics dans votre tableur et Search Analytics for Sheets extraira les données de la console de recherche. L’extraction de ces deux sources sera la clé pour combiner les données de Google Analytics et le rapport de Search Analytics de manière significative. Une fois que vous avez une nouvelle feuille ouverte et que vous êtes dans la fonction complémentaire, trouver et installer Google Analytics et Search Analytics for Sheets devrait être assez simple. De plus, les deux modules complémentaires sont gratuits.

Étape 2 : Créer des rapports Google Analytics

Une fois que vous avez installé le module complémentaire Google Analytics, vous trouverez « Google Analytics » dans votre menu. Survolez Google Analytics et sélectionnez Créer un nouveau rapport pour commencer. Une fois que le menu latéral s’affiche, sélectionnez le compte, la propriété et la vue dont vous voulez extraire des données. Vous pourrez également donner un nom à votre rapport (voir la note ci-dessous), puis sélectionner Créer un rapport. Vous n’avez pas à vous préoccuper des mesures et des dimensions à ce stade, mais cela viendra plus tard.

Note : A la fin de cet article, j’ai un modèle que vous pouvez utiliser pour combiner les données de Google Analytics et Search Analytics. Si vous souhaitez utiliser ce modèle, assurez-vous de nommer ce premier rapport « Organic Landing Pages Last Year ». Je vais également passer en revue les formules et les fonctions utilisées dans cet article. Vous n’avez donc pas besoin de vous fier au modèle, mais la nomenclature de chaque onglet doit être cohérente pour utiliser mes formules exactes. Il y a de nombreuses possibilités de renommer le rapport et les onglets, alors n’insistez pas si vous manquez cette partie et nommez votre rapport différemment ; sachez simplement que si à la fin le modèle ne fonctionne pas, vous devez revérifier les noms des onglets.

Étape 3 : Configurez vos rapports Google Analytics

L’onglet Configuration du rapport que vous voyez maintenant comme le premier onglet de votre feuille est l’endroit où vous pouvez configurer les données que vous voulez tirer. Je vous recommande vivement de vous familiariser avec cette fonctionnalité en regardant cette vidéo rapide de cinq minutes de Google, qui explique comment générer des rapports à partir de Google Analytics dans Google Sheets. Vous trouverez ci-dessous la liste des champs utilisés pour ce rapport, ainsi qu’un aperçu détaillé de la signification de tous ces champs et des mesures que vous pouvez y utiliser à l’adresse suivante : https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on.

Note : Si vous préférez simplement remplir votre feuille et lire plus tard les détails sur la configuration de chaque champ, vous pouvez coller les cellules ci-dessous dans votre tableau à la cellule B5 (il suffit de vérifier que cela ressemble à la capture d’écran ci-dessus) et passer au dernier paragraphe de cette section, juste après Segments.

395joursAucune
365 jours auparavant
ga:sessions, ga:rebond, ga:goalComplétionsToutes
ga:landingPagePath
-ga:sessions
sessions::condition::ga:medium==organique
Nom du rapport :

Le nom que vous avez défini lorsque vous avez créé le rapport. Il peut être modifié, mais notez que lorsque vous exécutez votre rapport, l’onglet qui le contient utilisera ce nom de rapport.

Tapez :

Le mot « core » sera automatiquement rempli pour vous, ce qui signifie que nous nous inspirons de l’API des rapports de base.

Voir :

L’ID de votre profil, que vous avez défini lors de la création du rapport, sera également automatiquement rempli.

Date de début :

Pour comparer les 30 derniers jours aux 30 jours de l’année précédente, nous définirons la date de début comme suit : 395joursAgo

Date de fin :

Pour comparer 30 jours complets l’année dernière à 30 jours complets cette année, nous fixerons la date de fin à 365 jours auparavant

Métriques :

Il s’agit des mesures que vous voulez tirer et qui dicteront les colonnes que vous voyez dans votre rapport. Pour ce rapport, nous voulons examiner les sessions, les rebonds et les objectifs atteints, c’est pourquoi nous utilisons les mesures ga:sessions, ga:rebonds, ga:objectifs atteintsTous. Google dispose d’un excellent outil de recherche des mesures possibles ici (https://developers.google.com/analytics/devguides/reporting/core/dimsmets) si vous souhaitez tester et tirer autre chose que les sessions, le taux de rebond et les objectifs atteints.

Dimensions :

Les dimensions font référence aux dimensions pour lesquelles vous voulez voir des mesures spécifiques ; dans ce cas, les pages d’atterrissage. Nous utilisons les pages d’atterrissage comme dimension car cela nous permettra de faire correspondre les données des requêtes de la page d’atterrissage de Search Analytics avec celles de la page d’atterrissage de Google Analytics. Pour extraire les mesures que vous avez sélectionnées ci-dessus par page d’atterrissage, utilisez ga:landingPagePath

Trier :

L’API Google Analytics triera par défaut vos mesures par ordre croissant. Pour moi, il est plus intéressant de voir les pages de destination les plus importantes par ordre décroissant afin que je puisse avoir un aperçu rapide des pages qui génèrent le plus de trafic vers mon site. Pour ce faire, il suffit de placer un signe moins (-) devant la mesure par laquelle vous souhaitez trier votre date : -ga:sessions. Pour en savoir plus sur le tri des données, consultez l’API Google Analytics à l’adresse suivante : https://developers.google.com/analytics/devguides/reporting/core/v3/reference#sort.

Segments :

Le dernier champ que nous allons ajouter est celui des segments, afin de pouvoir examiner uniquement le trafic organique. C’est là que vous pouvez ajouter de nouveaux utilisateurs organiques, des utilisateurs organiques de retour, ou tout autre segment spécial que vous avez créé dans Google Analytics. Cependant, pour ce rapport, nous allons utiliser le segment principal du trafic organique qui est standard dans Google Analytics : sessions::condition::ga:medium==organique.

Comme mentionné, nous voulons voir le trafic organique de chaque page au cours des 30 derniers jours par rapport à l’année précédente. Pour ce faire, nous devons générer deux rapports : un avec les données de session des 30 derniers jours, et un autre pour les données de session de la même période il y a un an. Nous avons 2015 prêt à partir, il suffit donc de le coller dans la colonne C, de renommer le nom du rapport « Pages d’atterrissage biologiques cette année » et de changer la date de début en « 30 jours avant » et la date de fin en « hier ». Vérifiez que la capture d’écran ci-dessus correspond à vos configurations avant de continuer.

Étape 4 : Exécutez votre rapport Google Analytics

Vous exécuterez le rapport que vous venez de créer en sélectionnant Exécuter les rapports sous le module complémentaire Google Analytics. Nous n’examinerons pas les rapports de programmation dans cet article, mais il peut être utile de les programmer pour qu’ils soient exécutés un jour précis afin de les aligner sur les rapports en cours que vous avez. Vous pouvez en savoir plus sur la programmation des rapports à l’adresse suivante : https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on#scheduling-reports.

Si tout s’est déroulé correctement jusqu’à présent, vous devriez voir ce popup :

Si, pour une raison quelconque, vous voyez un popup indiquant que vous avez une erreur, Google Analytics vous permet de savoir exactement quel champ a été mal rempli. Vérifiez vos segments ici (https://developers.google.com/analytics/devguides/reporting/core/v3/reference) et tant que vous utilisez un formatage valide, vous devriez pouvoir corriger les problèmes.

En supposant que tout se soit déroulé comme prévu, vous verrez une feuille de calcul qui ressemble à ceci :

Étape 5 : Exécutez votre rapport « Search Analytics for Sheets

Il est très simple d’exécuter un rapport d’analyse de recherche de feuilles. Cliquez sur votre feuille vide (feuille 1), et au même endroit où vous avez pu lancer Google Analytics, lancez la barre latérale pour Search Analytics for Sheets. A partir de là, vous autoriserez l’application et paramétrerez votre rapport. Tous les paramètres que j’ai mis à jour sont mis en évidence dans la capture d’écran ci-dessous, mais vous souhaitez les regrouper par requête et par page, les agréger par page, et faire afficher les résultats sur la feuille active. La valeur par défaut de Search Analytics for Sheets est de tirer les résultats des 90 jours précédents, mais vous pouvez ajuster cette valeur pour afficher ce qui convient le mieux à votre site web.

Tant que tout fonctionne correctement, vous verrez les principales requêtes de recherche, les pages de destination, les clics, les impressions, le CTR et la position moyenne par ordre décroissant de clics. Renommez la feuille 1 en « Données de la console de recherche » et votre feuille devrait ressembler à ceci :

Étape 6 : Supprimez le nom de domaine des pages de destination de Search Analytics

J’espère que vous pouvez voir où cela va maintenant. Nous avons un onglet avec toutes nos données Google Analytics par page de destination, et un autre avec nos données Search Analytics par page de destination, il ne reste donc plus qu’à marier les données.

Tout d’abord, il nous suffit de retirer le nom de domaine des données de la console de recherche. Vous remarquerez que les données de Google Analytics tirent les pages d’atterrissage supérieures, à l’exception de https://domain-name.com, et que la console de recherche tire le domaine entier. Pour ce faire, vous devez exécuter une fonction « rechercher et remplacer » dans la colonne « Page » de l’onglet « Console de recherche » de Google Sheets et remplacer https://domain-name.com sans remplacement (en éliminant le nom de domaine de l’URL).

Étape 7 : Combiner les données

Téléchargez le modèle de données au niveau des mots-clés ici. Ce modèle comporte les formules appropriées pour tirer les sessions de la page d’accueil d’une année sur l’autre, le taux de rebond et les conversions totales des objectifs. J’ai également configuré la colonne C comme « Mots-clés cibles » pour taper les termes que vous ciblez activement sur chaque page. De cette façon, vous pouvez voir si ce que vous ciblez est similaire à ce pour quoi vous êtes classé dans Google. Une fois le modèle créé, copiez l’onglet « Données sur les mots-clés » dans votre feuille de travail.

Une fois la feuille copiée, vous devriez voir une nouvelle feuille avec un onglet appelé Données sur les mots-clés. À partir de là, sélectionnez l’onglet Données sur les mots-clés et cliquez sur Copier vers…

Sélectionnez la feuille que vous avez construite avec vos données, et une copie de l’onglet Données sur les mots-clés s’affichera à la fin de votre feuille.

Si vous avez tout fait correctement jusqu’à présent, vous pourrez mettre à jour vos URL et les données apparaîtront automatiquement dans le modèle pour vos pages spécifiques. Lorsque vous ajoutez l’URL de votre page, veillez à ne pas inclure le nom de domaine. Par exemple, si vous voulez voir les données de https://www.domain-name.com/products/, vous devez taper /products/ dans la cellule B6 et voir les données se remplir. Assurez-vous également que tout correspond avec les barres obliques entre vos données Google Analytics et celles de votre console de recherche. Si vous avez des problèmes de duplication des structures d’URL, vous devrez peut-être travailler un peu avec les données pour rendre le formatage de la structure d’URL cohérent (et vous devriez aussi corriger cela du côté du serveur !). ). Vos résultats devraient ressembler à ceci :

Comment le modèle fonctionne-t-il ?

Si vous souhaitez examiner plus de deux pages et en faire un rapport plus solide, vous voulez probablement comprendre les formules qui contrôlent les résultats afin de pouvoir développer les données.

La majorité de ce modèle utilise VLOOKUP pour intégrer les données de Google Analytics dans la feuille. Si vous n’êtes pas sûr du fonctionnement de VLOOKUP, vous pouvez en savoir plus à ce sujet ici.

La colonne de variation en pourcentage d’une année sur l’autre et la colonne du taux de rebond sont des calculs simples. Par exemple, le pourcentage de variation dans la cellule G6 est calculé en utilisant =(E6-F6)/F6 et le taux de rebond dans la cellule I6 utilise =(H6/E6). Vous connaissez probablement déjà ces fonctions courantes d’Excel.

La formule la plus compliquée est la formule de tableau qui est utilisée pour extraire les données des mots-clés de Search Analytics. Étant donné qu’un VLOOKUP s’arrête après le premier résultat, et que nous voulons voir jusqu’à cinq résultats pour les requêtes, nous utilisons une formule de tableau pour extraire les résultats dans cinq cellules maximum. Il existe d’autres fonctions qui permettent également d’effectuer cette opération (c’est-à-dire d’extraire toutes les correspondances possibles dans une feuille) ; toutefois, la formule de tableau est unique en ce sens qu’elle nous permet de limiter les résultats à cinq lignes (sinon, si vous avez 10 correspondances pour un terme mais 4 pour un autre, vous ne pourriez pas structurer votre feuille de manière à afficher plusieurs pages dans un même onglet).

Voici la formule de tableau qui est utilisée dans la cellule D6 :

=ArrayFormula(IF(ISERROR(INDEX(‘Données de la console de recherche’!$A$1:$B$5000,SMALL(IF(‘Données de la console de recherche’!$B$1:$B$5000=$B$6,ROW(‘Données de la console de recherche’ ! $A$1:$B$5000)),ROW(2:2)),1)), «  »,INDEX(« Search Console Data’!$A$1:$B$5000,SMALL(IF(« Search Console Data’!$B$1:$B$5000=$B$6,ROW(« Search Console Data’!$B$1:$B$5000)),ROW(2:2)))

Cette formule permet de tirer plusieurs valeurs pour la valeur de B6, mais permet également à la formule de se déplacer vers le bas et de se développer dans la cellule D11. La formule du tableau dans la cellule D11 est la suivante :

=ArrayFormula(IF(ISERROR(INDEX(‘Données de la console de recherche’!$A$1:$B$5000,SMALL(IF(‘Données de la console de recherche’!$B$1:$B$5000=$B$6,ROW(‘Données de la console de recherche’ ! $A$1:$B$5000)),ROW(7:7)),1)), «  »,INDEX(« Search Console Data’!$A$1:$B$5000,SMALL(IF(« Search Console Data’!$B$1:$B$5000=$B$6,ROW(« Search Console Data’!$B$1:$B$5000)),ROW(7:7))))

Vous pouvez en apprendre davantage sur les formules de tableau ici, mais la façon dont elles sont exécutées dans Google Sheets est un peu différente de celle d’Excel. D’après mes recherches, cette formule a donné les résultats que je voulais (plusieurs correspondances contrôlées dans un ensemble spécifique de cellules), mais si vous connaissez une fonction dans Google Sheets qui fait quelque chose de similaire, n’hésitez pas à partager les commentaires !

Conclusion

Les données au niveau des mots-clés n’ont pas disparu ! Google nous donne des indications précieuses sur les termes qui conduisent les utilisateurs vers nos sites. Il nous suffit de combiner les données de manière significative. Google Sheets est un moyen puissant de se connecter à diverses API et de tirer des charges de données de plusieurs sources. Le rapport de Search Analytics présente certaines limites (voir cet excellent article de Russ Jones sur certaines inexactitudes qu’il a trouvées dans les données de Search Analytics de la console de recherche). Nous espérons donc que cette petite feuille vous inspirera pour élargir les données et inclure plus de mesures d’engagement de Google Analytics, des données supplémentaires sur les clics de la console de recherche, des données sur les classements, des données sur le trafic en dehors de la navigation organique, et plus encore. Sans oublier que tout cela peut être programmé, de sorte que vous pouvez avoir vos données de Search Analytics et Google Analytics prêtes lorsque vous ouvrez vos feuilles et automatiser presque tout ce processus.

Nous n’avons pas à utiliser des outils comme Search Console et Google Analytics dans le vide simplement parce qu’ils existent de cette façon. Essayez de combiner les données de votre propre chef pour obtenir des informations plus utiles sur vos campagnes !

De plus, si vous avez adoré cela, si tout cela ne vous convient pas, si vous connaissez des outils payants qui font cela, si vous faites cela d’une autre manière, si vous faites cela d’une manière plus importante, ou si cela n’a tout simplement pas de sens pour vous – commentez ! J’aimerais savoir comment les autres SEO glanent des informations sur les données des mots-clés à l’aube de (non fourni) et améliorer ce processus avec votre aide !

Criez

Une mention spéciale est adressée à @mihaiaperghis pour la publication de cet article de blog sur l’utilisation de l’analyse de recherche dans les feuilles de Google pour un meilleur référencement, alors que je terminais cet article. Grâce à votre article, j’ai pu trouver un moyen facile et gratuit de tirer parti de l’API de Search Analytics dans des feuilles. Avant de lire, j’utilisais et j’ai écrit sur un add-on payant qui coûtait ~30$/mois, donc grâce à votre post, je peux dire que tout ce processus est gratuit. Merci également à @SWallaceSEO pour avoir revu cet article, testé la feuille, et m’avoir aidé avec les modifications et le débogage !

À propos de SarahLively –

Sarah Lively est la directrice associée du référencement à l’agence Nebo. Dans le cadre de ses fonctions, elle met en œuvre des stratégies de référencement à multiples facettes pour une série de clients, notamment le développement de stratégies de mots clés, l’optimisation du contenu des pages, l’optimisation technique du site et les campagnes hors page destinées à accroître la notoriété de la marque et à renforcer l’autorité du site. Depuis qu’elle a rejoint Nebo en 2013, Sarah a développé et exécuté des campagnes de référencement réussies pour des clients de toutes tailles, couvrant une variété de secteurs verticaux, notamment les stations de luxe, les fabricants de plastiques poreux, les organisations immobilières et les détaillants nationaux. Elle combine son expertise en psychologie et en études d’audience avec sa compréhension approfondie du marketing numérique pour générer une croissance organique à long terme qui permet aux clients d’atteindre et de dépasser les objectifs de leur site web. En plus de son travail chez Nebo, Sarah siège au conseil d’administration de l’American Marketing Association d’Atlanta, où elle veille à ce que les membres de l’organisation s’engagent et se développent pour la section.

43
83