Estimer l’évolution de trafic en fonction des positions

Logo ExcelAujourd'hui nous allons nous armer de notre plus beau tableur, j'ai choisi Excel pour ma part, et définir les mots clés sur lesquels nous sommes déjà plus ou moins bien positionnés à travailler en priorité en fonction du trafic qu'ils pourront générer.

Voilà comment ça va se passer : nous allons récupérer nos données de positionnement, le nombre de recherches mensuelles avec le niveau de concurrence, une estimation du taux de clic en fonction de la position et ensuite nous allons faire plein de calculs, avec certaines formules plutôt barbares.

Récupération des données

Pour simplifier le travail je vais récupérer mon positionnement, le volume de recherche et le niveau de concurrence avec SEMRush. Pour l'estimation du taux de clic, je vais récupérer les données issues de cette étude, elle n'est pas récente je vous l'accorde mais étant donné le fait que je veux ces données sur les 20 premières positions, je prends ce que j'ai sous la dent.

Je classe tout ça dans un tableau Excel et voilà déjà une première partie de mon tableau, dans les 4 premières colonnes nous avons les requêtes, nos positions, le volume de recherche et le niveau de concurrence. Dans les colonnes suivantes, nous avons le taux de clic pour chacune des positions de la SERP, de la 1ère à la 20ème.

Tableur

 

Les calculs

Nous allons remplir les colonnes correspondant au taux de clic selon la position. Pour cela rien de plus simple : nous allons multiplier le volume de recherche par le taux de clic. Voici la formule pour la première position, adaptée à mon fichier (que vous pourrez télécharger à la fin de l'article). Ensuite, il vous suffira d'utiliser la poignée de recopie pour remplir les autres positions.

=$C3*E$2

Bien, nous avons maintenant une estimation des visites en fonction de la position du mot-clé. Comme notre position n'est jamais la même, nous allons la récupérer pour évaluer notre trafic SEO actuel. Nous allons donc créer une nouvelle colonne après les taux de clic pour les 20 premières places : nous sommes colonne Y. Voici la formule à utiliser :

=DECALER($D3;0;$B3)

Ici nous demandons à Excel de partir de la colonne D (qui est ici la concurrence, mais ça pourrait être tout à fait autre chose, peu importe), de rester sur la même ligne et de se décaler de x colonnes, x étant la valeur fournie dans la colonne B (notre position actuelle, donc).

Ça y est, nous pouvons estimer notre trafic pour chaque mot clé, mais nous n'allons pas nous arrêter en si bon chemin.

En SEO, il faut se fixer des objectifs : admettons que dans notre cas, l'objectif est d'augmenter chaque mot clé de 5 places. Nous allons donc devoir récupérer le trafic estimé si nous gagnons 5 places pour chaque expression. Nouvelle colonne (Z), nouvelle formule :

=SI($B3<6;"";DECALER($D3;0;$B3-5))

Ici, nous demandons presque la même chose que précédemment, nous lui demandons en plus de nous donner la valeur de la 5ème colonne avant notre position et de ne rien afficher si notre position est inférieure à 6 (de 1 à 5 donc, pour ne pas sortir de la plage du trafic estimé, vous me suivez ?)

Allons un peu plus loin, pourquoi ne pas demander de connaitre le nombre de visites gagnées si nous améliorons notre ranking de 5 places ? Encore une fois, nous allons créer une nouvelle colonne (nous sommes désormais colonne AA) et mettre en place une nouvelle formule :

=SI(Z3="";"Non";Z3-Y3)

Nous connaissons maintenant le trafic supplémentaire que nous pouvons gagner. C'est déjà pas mal de données, mais encore une fois nous avons choisi d'aller un peu plus loin en mettant en exergue les requêtes les moins concurrentielles et qui nous amèneront le plus de trafic. Nous allons ici nous baser sur l'astuce partagée par RaphSEO qui semble toucher pas mal avec Excel. Nouvelle colonne (AB), et nouvelle formule :

=SI(ET(D3<0,7;AA3>150;AA3<>"Non");"Go !";"Attends un peu")

Et voilà le résultat :

Résultat

Pour faciliter la lecture, vous pouvez bien sûr classer les données comme je l'ai fait ici en mettant en premier les mots clés dont le message est "Go !" Soyez aussi un peu plus futé que moi, ne mettez pas de décimale, je n'ai jamais vu de demi-visite :-)

Si vous avez peur de vous planter dans tout ça, voici un fichier exemple que vous pouvez télécharger. Bien sûr dans l'exemple notre objectif est de gagner 5 positions, si votre objectif diffère vous pouvez bien évidemment modifier le fichier en conséquence. Il en est de même pour la dernière colonne : à vous de voir à partir de quel niveau le trafic supplémentaire et la concurrence sont intéressants pour vous.

Si votre Excel est configuré en anglais il faudra modifier les formules, utilisez "IF" à la place de "SI", "AND" à la place de "ET" et "OFFSET" à la place de "DECALER".

Et voilà, c'est déjà la fin de cet article, et j'en profite pour remercier Jean-Christophe, mon collègue du service Business Intelligence, qui m'a aidé à mettre en place les idées que j'avais pour créer ce tableau. J'espère que ça vous aura aidé et que vous n'avez pas trop mal à la tête ;-)

Petit rappel : il reste seulement 4 jours avant la fin du Earlybirds pour le SMX (mais si, vous savez, la période à tarif réduit), et en plus vous avez 15% de réduction supplémentaire grâce au code NOVISEO13 ;-)

Catégorie Référencement | Thématique .

25 réponses à Estimer l’évolution de trafic en fonction des positions

  1. yohan says:

    concernant la position des requêtes j’ai remarquer que les résultats varient si on est connecté où non à google + , je pense que beaucoup le savent mais bon on sait jamais , ça m’a longtemps induit en erreur .
    Bonne journée

  2. yohann says:

    Sympa le doc excel, je vais surement le tester dans les jours a venir, ça permets de bien visualiser les mots clés les plus générateur et dont la concurrence reste raisonnable.

    Bien sur on peut aussi changer la dernière formule en fonction de la thématique (changer la concurrence max et le trafic estimé).

    Grand merci à toi !

  3. Remi says:

    Une méthode approuvée et une démonstration claire, le fichier Excel à disposition c’est la cerise sur le gâteau.

    Il y a cependant une limite à cette méthode : sur certaines requêtes il n’y a parfois qu’un seul résultat au-dessus de la ligne de flottaison, de quoi doucher vos espoirs de faire un « coup » sur un mot-clé. Mais la méthode est bonne et en global on sera récompensé en l’utilisant, il ne faut donc pas oublier de faire quelques visites sur la SERP pour se rendre compte de la situation.
    Le tableau Excel paramétré à la perfection est un atout mais il ne sait pas tout !

  4. Thomas says:

    Pas mal du tout ! Ça va certainement me servir.
    L’avantage c’est que le fichier peut évoluer pour rajouter des fonctionnalités :)

    Merci pour ça, et aussi pour le dossier sur l’audit SEO, il est au top !

  5. Cédric says:

    Sympa ce petit fichier Excel, c’est une petite variante de celui de Raph. J’utilise également ce type de document mais je suis en train de travailler sur l’automatisation de la collecte de données. Car récupérer les positions manuellement … c’est un peu lourd. Idem pour les volumes de recherche et le niveau de concurrence. Pour les positions j’ai trouvé la solution, en revanche pour le reste, si quelqu’un a une piste je suis preneur.

    Ensuite, pour perfectionner ton fichier, j’aurais inscrit les paramètres 0,7 (niveau de concurrence plafond dans ton fichier) et 150 (minimum de visites) dans des cellules à part afin de pouvoir les modifier facilement. J’ai un exemple où le niveau de concurrence est supérieur à 0,95 sur 90% des mots-clés … et pourtant je dois me positionner.

    En tout cas, vaste sujet et ce type de fichier est un très bon point de départ pour chacun.

  6. Jude says:

    Excel est vraiment l’outil des SEO ! J’avais téléchargé récemment sur visibilité ref (sans faire de pub) un doc de priorisation des mots-clés, dans le même esprit que celui-ci. merci pour le partage de la feuille Excel

  7. Noudchananh says:

    C’est pour ce genre d’articles que je fais de la veille SEO ! Un grand merci pour cette info car ce n’est pas un truc que j’aurai pensé de moi-même. Je me contentais bêtement de l’estimation de trafic de Google Keywords sans chercher plus.

    Je t’en suis très reconnaissant et si j’avais une info équivalente en échange, je la donnerai volontiers … à moins qu’une rencontre avec une soeur tiens toujours (sauf si tu réserves cela seulement pour des jeux concours ^^)

  8. Call center says:

    Ce calcul permet donc d’évaluer les efforts à fournir pour une présence plus soutenue! super! vive Excel! lol

  9. Jerome says:

    Tu penses vraiment que les résultats de l’etude qui date de 2011 sont encore valables ?
    J’ai un doute entre le knowledge graph, google adress, le nombre deliens commerciaux qui sont plus nombreux…Ca match les estimations que tu fais et les résultats que tu as?

  10. Amelie says:

    Merci pour cette astuce, j’ai commencé à travailler un fichier qui s’en inspire. Tout comme le tuto de Raph, j’imagine qu’il ne faut pas hésiter à modifier les données de filtrage dans les formules en fonction du marché (il y a des secteurs plus concurrentiels que d’autres).

    L’idée de Cédric de stocker les paramètres de filtrage dans une cellule à part est une très bonne idée, ça évite de refaire l’incrémentation à chaque fois qu’on change quelque-chose.

  11. Lucie says:

    Ca c’est cool, c’est très bien vu ! Il fallait y penser car les formules ne sont franchement pas complexes du tout…

    Je pense que je vais utiliser cette méthode pour mes clients. Cela va me permettre de prendre les bonnes décisions au bon moment.

  12. Gregory says:

    Très bon au quotidien !
    Un bon partage très utile appréciable (et apprécié) ;)

  13. solange says:

    j’aimerai bien faire ces calculs, mais honnetement je n’y comprends pas grand chose. Pourquoi faire ces calculs ? comment à partir de ces calculs on peut gagner des positions ?

  14. Jérôme says:

    Ton idée est bonne mais malheureusement je ne pense pas que ce soit très fiable. Les taux de clics énormément varier d’une requête à l’autre.
    En fonction des résultats tu vas avoir plus ou moins d’adwords, vidéos, adresses…donc en fonction de la résolution de l’utilisateur il ne verra qu’un seul résultat ou plus.

  15. Monica says:

    (Sylvainx10 + Raph²) / Kei x Aspro = $$€ HT !
    A part ça on nous faisait croire de notre plein gré que le référencement en 2013 serait essentiellement le renouveau du contenu frais et des belles lettres. Et ben les chiffres peuvent encore se rouler de belles formules dans le ranking…
    Bertrand Renard, sors de leurs corps!

  16. Florian says:

    Bonjour et bravo !

    J’ai beau être formateur avancé excel je n’avais pas toutes ses stats qui permettent de fraire de très belles analyses.

    Excel est un outil très puissant lorsqu’on le maitrise !

    Merci beaucoup

  17. Olivier says:

    Merci pour cette astuce, comme quoi les bon vieux outils comme Excel on un potentiel intéressant.
    Pour Cédric tu as certain logiciels qui te permettent de récupérer les mots clefs génériques mais surtout ceux de tes concurrents. Ensuite passer tous ces mots clefs à la moulinette ça peut être vraiment intéressant.

  18. Sylvain says:

    @tous ceux qui en parlent : bien évidemment que la méthode n’est pas parfaite, bien évidemment que les données de l’étude sont erronées (elle a plus de deux ans !), bien évidemment que le taux de clic n’est pas égal pour chaque type de requête. L’important ici n’est pas de calculer à la visite près le trafic que l’on va gagner mais de définir des priorités. Des fois je me demande vraiment si certains cherchent à aller plus loin que ce qui est dit sur les blogs SEO…

    @Cédric : peut-être pourrais-tu développer un petit script qui requête Adwords en fonction des mots clés que tu as ? Celui-ci récupèrerait le volume et la concurrence, que tu pourrais ensuite associer avec tes positions ?

    Pour les paramètres dans des cellules à part, je suis tout à fait d’accord, je dois avouer que j’ai fini le fichier un peu à la va-vite :/

    @Amélie, oui en effet il ne faut pas hésiter à modifier des choses là-dedans, c’est simplement une base à adapter ;-)

    @Solange : en effet, ces calculs ne permettent pas de gagner des positions.

  19. Erwan says:

    Quelque soit la requête ou les chiffres d’une étude, les gains sont un peu distribués comme dans un tournoi de poker : le premier prend tout, les 2ème et 3ème s’en sortent bien, de 4 à 10 on se plaint pas et ensuite c’est la misère :) .

  20. Florian says:

    @Sylvain : La problématique auquel tu t’opposes c’est le champ de réflexion ce que tu fais là c’est une étude à la marge, tu analyses le champ des possibilité et l’impact de l’évolution des mots clefs sur certaines actions, peut importe l’actualité des données du moment qu’ils ont été bons à un moment donné, en gardant tout de même une certaine relativité. Une telle étude n’est pas une étude forecasts mais une étude de potentiel.

    Elle répond à la réponse Oui ou Non je dois agir, et pas à Combien je vais gagner.

  21. Sylvain says:

    @Florian : Oui exactement, ça m’apprendra à vouloir faire des titres un peu « catchy » ;-) Ta dernière phrase résume bien l’objectif de l’article, et bizarrement elle correspond plutôt bien au champ lexical que j’ai utilisé pour remplir la dernière colonne !

  22. Evenstood says:

    Pas mal, je vois que le Excel aide un bon paquet de personnes dans le SEO. Je suis pas hyper concerné par la chose ni par le logiciel mais je dois bien avouer qu’il permet de faire de chouettes choses.

    Ton exemple est plutôt démonstratif dans ce sens, je trouve que c’est une très bonne idée de pouvoir d’un coup d’oeil savoir sur quelles requêtes travailler pour booster son trafic.

  23. Tres sympa, j’utilise semrush depuis un moment mais je ne faisais pas ce genre de feuille excel, me contentant des indications du logiciels, + les informations d’adwords

    Je vais me lancer directement et tenter de faire quelques calculs sur des sites !

  24. lucas says:

    Super article, personnellement j’ai peu modifié le fichier excel de sorte qu’il me donne une valeur que j’ai appelé le « Coefficient d’intérêt », il correspond en fait à la valeur estimée en position 9 (à 2% du trafic total) divisé par la concurrence que fournis Google. Effectivement, ça ne veut pas forcément dire grand chose vu que la concurrence que nous fournis Google est celle d’adword, mais il donne quand même une petite idée. Le mieux, en fait ça serait de trouver le nombre de résultats par requête de sorte qu’on pondère un peu, mais bon, je ne sais pas ou trouver ça facilement.
    Voici la formule que j’utilise:
    = A1*2%/B1 = A1*0,02/B1
    Avec A1:Recherches totales et B1: Concurrence

  25. Geraldine says:

    Merci pour le détail de ce tableau. Super partage ! Je vais comparer avec celui que j’utilise, quitte à modifier-améliorer quelques paramètres. Merci beaucoup !

Laisser un commentaire

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

*

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Veuillez utiliser votre nom ou votre pseudo. Tout commentaire ne respectant pas cette règle verra son lien supprimé.