Prévision SEO dans Google Sheets

En 2015, j’ai publié un article donnant un outil de prévision gratuit et simple, et parlant de cas d’utilisation pour la prévision dans le référencement. C’était un moyen rapide et efficace de voir si une modification du trafic de votre site est une sorte de saisonnalité que vous pouvez ignorer, quelque chose à célébrer ou un signe inquiétant de perte de trafic.

En bref : vous pouvez entrer une série de données, et cela les tracera sur un graphique comme l’image ci-dessus.

Cinq ans plus tard, je reçois toujours des gens – d’anciens collègues à de parfaits inconnus – qui me posent des questions sur cet outil, et le plus souvent, on me demande une version qui fonctionne directement dans les feuilles de calcul.

Je trouve cela facile à comprendre : une feuille de calcul est plus flexible, plus facile à déboguer, plus facile à développer, plus facile à entretenir et un format que les gens connaissent très bien.

Le compromis lors de l’optimisation de ces choses est que, même si j’ai amélioré cet outil il y a quelques années, j’ai toujours dû garder les choses gérables dans l’environnement de programmation réputé instable qu’est Excel / Google Sheets. Cela signifie que le modèle partagé dans ce post utilise un modèle plus simple et légèrement moins performant que certains outils avec exécution de code externe (par exemple Forecast Forge).

Dans cet article, je vais donner un modèle gratuit, vous montrer comment cela fonctionne et comment l’utiliser, puis vous montrer comment créer votre propre (meilleure ?) version. (Si vous avez besoin d’un rappel sur le moment d’utiliser les prévisions en général et sur des concepts tels que les intervalles de confiance, reportez-vous à l’article original lié ci-dessus.).

Types de prévisions SEO

Il y a une chose que je veux développer avant d’entrer dans les feuilles de calcul : les différents types de prévisions SEO.

En gros, je pense que vous pouvez classer les prévisions SEO en trois groupes :

1. “Je me sens optimiste – ajoutez 20% à cette année” ou des changements similaires similaires aux chiffres existants. Les versions plus complexes peuvent n’ajouter que 20 % à certains groupes de pages ou de mots clés. Je pense que beaucoup d’agences utilisent ce genre de prévisions dans des pitchs, et cela revient à s’appuyer sur l’expérience.
2. Modèles de mots clés/CTR, lorsque vous estimez un changement de classement (ou un ensemble de changements de classement), puis extrapolez le changement résultant du trafic à partir du volume de recherche et des données CTR (vous pouvez voir une méthodologie similaire ici). Encore une fois, des versions plus complexes pourraient avoir une base pour le changement de classement (par exemple “Et si nous échangions des places avec le concurrent A dans chaque mot-clé du groupe X où ils nous surclassent actuellement ?”).
3. Prévision statistique basée sur des données historiques, lorsque vous extrapolez à partir des tendances précédentes et de la saisonnalité pour voir ce qui se passerait si tout restait constant (même niveau d’activité marketing par vous et vos concurrents, etc.).

Le type deux a ses mérites, mais si vous comparez les données Ahrefs/SEMRush/Sistrix à vos propres analyses, vous verrez à quel point il est difficile de généraliser. Soit dit en passant, je ne pense pas que le premier type soit aussi ridicule qu’il n’y paraît, mais ce n’est pas quelque chose que j’explorerai plus loin dans cet article. Dans tous les cas, le modèle de cet article s’inscrit dans le type trois.

Qu’est-ce qui en fait une prévision SEO ?

Pourquoi, rien du tout. Une chose que vous remarquerez à propos de ma description du type trois ci-dessus est qu’elle ne mentionne rien de spécifique au référencement. Cela pourrait également s’appliquer au trafic direct, par exemple. Cela dit, il y a quelques raisons pour lesquelles je suggère cela spécifiquement comme prévision SEO :

• Nous sommes sur le blog Moz et je suis consultant SEO.
• Il existe de meilleures méthodologies disponibles pour de nombreux autres canaux.

J’ai mentionné que le type deux ci-dessus est très difficile, en raison de la nature hautement non déterministe du référencement et de la qualité généralement médiocre des données détaillées dans la Search Console et d’autres plates-formes spécifiques au référencement. De plus, pour avoir une idée précise de la saisonnalité, vous devez avoir entreposé vos données Search Console pendant au moins deux ans.

Pour de nombreux autres canaux, des données historiques détaillées de haute qualité existent et les relations sont beaucoup plus prévisibles, ce qui permet des prévisions plus granulaires. Par exemple, pour la recherche payante, l’outil Forecast Forge que j’ai mentionné ci-dessus intègre des facteurs tels que les données de conversion au niveau des mots clés et le coût par clic en fonction de vos données historiques, d’une manière qui serait extrêmement peu pratique pour le référencement.

Cela dit, nous pouvons toujours combiner plusieurs types de prévisions dans le modèle ci-dessous. Par exemple, plutôt que de prévoir le trafic de votre site dans son ensemble, vous pouvez prévoir les sous-dossiers séparément, ou la marque/non-marque séparément, et vous pouvez ensuite appliquer un pourcentage de croissance à certains domaines ou intégrer des changements de classement anticipés. Mais, on prend de l’avance…

Comment utiliser le modèle

La première chose que vous devrez faire est de faire une copie (sous le menu “Fichier” en haut à gauche, mais automatique avec le lien que j’ai inclus). Cela signifie que vous pouvez entrer vos propres données et jouer à votre guise, et vous pouvez toujours revenir et obtenir une nouvelle copie plus tard si vous en avez besoin.

Ensuite, sur le premier onglet, vous remarquerez que certaines cellules sont surlignées en vert ou en bleu :

Vous ne devriez modifier les valeurs que dans les cellules colorées.

Les cellules bleues de la colonne E servent essentiellement à s’assurer que tout est correctement étiqueté dans la sortie. Ainsi, par exemple, si vous collez des données de session, des données de clic ou des données de revenus, vous pouvez définir cette étiquette. De même, si vous entrez un mois de début 2018-01 et 36 mois de données historiques, la sortie des prévisions commencera en janvier 2021.

Sur cette note, il doit s’agir de données mensuelles – c’est l’un des compromis pour la simplicité que j’ai mentionné plus tôt. Vous pouvez coller jusqu’à une décennie de données mensuelles historiques dans la colonne B, en commençant par la cellule B2, mais vous devez faire attention à quelques points :

• Vous avez besoin d’au moins 24 mois de données pour que le modèle ait une bonne idée de la saisonnalité. (S’il n’y a qu’un seul mois de janvier dans vos données historiques et qu’il s’agissait d’un pic de trafic, comment suis-je censé savoir s’il s’agissait d’un événement ponctuel ou d’un événement annuel ?)
• Vous avez besoin de mois complets. Donc, si nous sommes le 25 mars 2021 lorsque vous lisez ceci, le dernier mois de données que vous devez inclure est février 2021.

Assurez-vous également de supprimer tous les restes de mes exemples de données dans la colonne B.

Les sorties

Une fois que vous avez fait cela, vous pouvez vous diriger vers l’onglet “Sorties”, où vous verrez quelque chose comme ceci :

La colonne C est probablement celle qui vous intéresse. Gardez à l’esprit qu’elle est pleine de formules ici, mais vous pouvez copier et coller en tant que valeurs dans une autre feuille, ou simplement aller à Fichier> Télécharger> Valeurs séparées par des virgules pour obtenir le brut Les données.

Vous remarquerez que je n’affiche que 15 mois de prévisions dans ce graphique par défaut, et je vous recommande de faire de même. Comme je l’ai mentionné ci-dessus, l’hypothèse implicite d’une prévision est que le contexte historique se poursuit, à moins que vous n’incluiez explicitement des scénarios modifiés comme les verrouillages COVID dans votre modèle (plus à ce sujet dans un instant !). La probabilité que cette hypothèse se maintienne dans deux ou trois ans dans le futur est faible, donc même si j’ai fourni des valeurs prévisionnelles plus loin dans le futur, vous devez garder cela à l’esprit.

Les limites supérieure et inférieure indiquées sont des intervalles de confiance à 95 % – encore une fois, vous pouvez récapituler ce que cela signifie dans mon article précédent si vous le souhaitez.

Cas d’utilisation avancés

Vous avez peut-être déjà remarqué l’onglet “Avancé”:

Bien que j’aie dit que je voulais garder cela simple, j’ai estimé qu’étant donné tout ce qui s’est passé en 2020, de nombreuses personnes auraient besoin d’intégrer des facteurs externes majeurs dans leur modèle.

Dans l’exemple ci-dessus, j’ai rempli la colonne B avec une variable indiquant si le Royaume-Uni était ou non sous verrouillage COVID. J’ai utilisé « 0,5 » pour indiquer que nous sommes entrés dans le confinement à la mi-mars.

Vous pouvez probablement mieux utiliser cela pour les facteurs pertinents pour votre entreprise, mais il y a quelques points importants à garder à l’esprit avec cet onglet :

• C’est bien de le laisser complètement intact si vous ne voulez pas ajouter ces variables supplémentaires.
• Allez de gauche à droite – vous pouvez laisser la colonne C vide si vous utilisez la colonne B, mais ce n’est pas bien de laisser B vide si vous utilisez C.
• Si vous utilisez une variable « fictive » (par exemple « 1 » pour quelque chose qui est actif), vous devez vous assurer de remplir les 0 dans les autres cellules pendant au moins la période de vos données historiques.
• Vous pouvez entrer des valeurs futures – par exemple, si vous prédisez un verrouillage COVID en mars 2021 (enfoiré !), vous pouvez entrer quelque chose dans cette cellule pour qu’il soit intégré dans la prévision.
• Si vous n’entrez pas de valeurs futures, le modèle prédira en fonction de ce nombre qui sera égal à zéro dans le futur. Ainsi, si vous avez saisi « PPC de marque actif » comme variable fictive pour les données historiques, puis que vous l’avez laissée vide pour les périodes futures, le modèle supposera que vous avez désactivé le PPC de marque à l’avenir.
• Ajouter trop de données ici pour trop peu de périodes historiques entraînera quelque chose appelé “surajustement” – je ne veux pas entrer dans les détails à ce sujet, c’est pourquoi cet onglet s’appelle “Avancé”, mais essayez de ne pas vous laisser emporter.

Voici quelques exemples de cas d’utilisation de cet onglet à considérer :

• Indiquez si le PPC de marque était actif (0 ou 1)
• Indiquez si vous diffusez ou non des publicités télévisées
• Entrez les verrouillages COVID
• Saisissez les mises à jour d’algorithmes importantes pour votre entreprise (une colonne par mise à jour)

Pourquoi mes estimations sont-elles différentes de votre ancien outil ? L’un d’eux se trompe-t-il ?

Il existe deux différences majeures de méthode entre ce modèle et mon ancien outil :

• L’ancien outil utilisait la bibliothèque Causal Impact de Google, le nouveau modèle utilise une régression des moindres carrés ordinaires.
• L’ancien outil capturait les tendances non linéaires en utilisant la période de temps au carré comme variable prédictive (par exemple, mois 1 = 1, mois 2 = 4, mois 3 = 9, etc.) et en essayant d’adapter la courbe de trafic à cette courbe. C’est ce qu’on appelle une régression quadratique. Le nouvel outil capture les tendances non linéaires en ajustant chaque période comme un multiple de la période précédente (par exemple mois 1 = X * mois 2 où X peut être n’importe quelle valeur). C’est ce qu’on appelle un modèle AR(1).

Si vous constatez une différence significative dans les valeurs de prévision entre les deux, cela revient presque certainement à la deuxième raison, et bien que cela ajoute un peu de complexité, dans la grande majorité des cas, la nouvelle technique est plus réaliste et flexible.

Il est également beaucoup moins susceptible de prédire un trafic nul ou négatif dans le cas d’une forte tendance à la baisse, ce qui est bien.

Comment ça marche?

Il y a un onglet caché dans le modèle où vous pouvez jeter un coup d’œil, mais la version courte est la formule de feuille de calcul “LINEST ()”.

Les entrées que j’utilise sont :

• Variables dépendantes
  • Tout ce que vous mettez comme colonne B dans l’onglet des entrées (comme le trafic)
• Variables indépendantes
  • Passage linéaire du temps
  • Trafic de la période précédente
  • Variables fictives pour 11 mois (le 12ème mois est représenté par les 11 autres variables toutes étant 0)
  • Jusqu’à trois variables “avancées”

La formule donne alors une série de « coefficients » en tant que sorties, qui peuvent être multipliés par des valeurs et additionnés pour former une prédiction comme :

• Trafic « Période 10 » = Interception + (Coefficient de temps * 10) + (Coefficient de la période précédente * Trafic de la période 9)

Vous pouvez voir dans cette feuille cachée que j’ai étiqueté et codé par couleur de nombreuses sorties de la formule Linest, ce qui peut vous aider à démarrer si vous voulez jouer avec vous-même.

Extensions potentielles

Si vous voulez jouer avec cela vous-même, voici quelques domaines que j’ai personnellement en tête pour une expansion ultérieure qui pourraient vous intéresser :

• Données quotidiennes au lieu de mensuelles, avec une saisonnalité hebdomadaire (par exemple, creux tous les dimanches)
• Objectifs de croissance intégrés (par exemple, entrer une croissance de 20 % d’ici la fin de 2021)

Richard Fergiedont j’ai mentionné l’outil Forecast Forge à quelques reprises ci-dessus, a également fourni d’excellentes suggestions pour améliorer la précision des prévisions avec une complexité supplémentaire assez limitée :

• Lissez les données et évitez les prédictions négatives dans les cas extrêmes en prenant le log() des entrées et en fournissant un exposant des sorties (le lissage des données peut ou non être une bonne chose selon votre point de vue !).
• Régresser sur les 12 mois précédents, au lieu d’utiliser le mois précédent + la saisonnalité (cela nécessite des données historiques de 3 ans minimum)

Je peux ou non inclure tout ou partie de ce qui précède moi-même au fil du temps, mais si c’est le cas, je m’assurerai d’utiliser le même lien et de le noter dans la feuille de calcul, donc cet article est toujours lié au plus à jour -version datée.

Si vous êtes arrivé jusqu’ici, qu’aimeriez-vous voir ? Faites-moi savoir dans les commentaires!