Bonjour à tous, ne vous méprenez pas sur le titre de l’article.
Je ne vous propose pas de devenir riches et drainer des millions de visiteurs en un clic (quoi que) mais simplement une petite astuces pour gagner du temps dans votre recherche de mots clés.
Vous le savez probablement, identifier les KW porteurs de sa thématique est une étape incontournable dans sa stratégie de ranking. Et c’est surtout le moyen de s’assurer un blog ou site rentable, car être premier sur « référenceur freelance à Tataouine-Les-Bains » ne devrait pas vous apporter énormément de contrats. (je vous dirais ça bientôt)
Le but de cet article est de vous montrer comment dans votre stratégie vous pouvez inclure des mots clés qui draineront du trafic sans quasiment aucune action de netlinking. Ce que je cherche ici, n’est pas un Top KW avec des milliers de visiteurs jours et très concurrentiel mais plus une somme de mots clés qui rapporteront du trafic ciblé sans beaucoup d’effort.
Lister ses mots clés thématiques
Le SEO par nature est fainéant donc je ne vais pas faire ça à la main. Le SEO par nature est radin donc je ne vais pas payer un tool pour ça.
C’est pourquoi je vais vous montrer qu’avec Outil mot clé de Google ou SemRush pour ceux qui ont une licence et excel on peut faire des merveilles.
- 1ère étape direction le générateur de mot clé Google ; Vous rentrez votre mot clé ou votre expression, vous remplissez le Captcha s’il vous est demandé et surtout vous n’oubliez pas dans la colonne de gauche de décocher « large » pour cocher « Exact ». J’insiste là dessus car beaucoup font cet erreur et du coup se plantent royalement sur le potentiel d’un mot clé. dans notre exemple sur le terme « referencement » en requête large on obtient 301 000 recherches mensuelle pour la France alors qu’en exacte on passe à 18 100 soit presque 17 fois moins de trafic possible. J’en profite pour vous conseiller vivement de vous créer un compte gratuit car vous aurez plus de résultats et vous verrez les CPC des KW (je dis ça je dis rien 🙂 )
Identification des mots clés et relevé de valeurs
- ensuite cliquez sur « importer » (numéro 3) puis « tous les résultats de recherche » (numéro 4) puis « csv pour excel ». A présent vous vous retrouvez avec un joli CSV
- Séance nettoyage on a beaucoup de colonnes et toutes ne nous intéressent pas. Vous allez donc garder les colonnes suivantes: Mot clé, concurrence, recherche mensuelle et CPC approximatif (noté le approximatif 😀 ) Si tout va bien vous devriez avoir plus que 4 colonnes (A, B, C et D)
- A présent dans la cellule E2 vous allez écrire la formule suivante: =SI(ET(B2<0,50;C2>500;D2>3); »Vrai »; »Faux ») vous appliquez cette formule à toutes vos lignes avec une incrémentation.
- cliquez sur la colonne E puis dans l’onglet Accueil cliquez sur « mise en forme conditionnelle » puis « règles de mise en surbrillance des cellules » puis « texte qui contient » dans le champ vide vous allez écrire « Vrai » et choisir une couleur de remplissage dans le menu déroulant (le vert par exemple)
kw excel
Et voici le joli résultat que vous devriez obtenir. En un clin d’oeil vous avez directement le KW sur lequel vous pouvez bosser.
Mise en valeur du résultat de mot clé
Bon ok j’explique un peu. Le seul point important réside dans la formule =SI(ET(B2<0,50;C2>500;D2>3); »Vrai »; »Faux ») et le choix des valeurs pour les cellules.
Concrètement ici l’objectif était pour moi de trouver un KW avec une concurrence pas trop importante, générant suffisamment de recherches mensuelles et avec un CPC qui m’intéresse. Pour ce dernier point, si la recherche n’est pas destinée à un MFT vous pouvez supprimer cette variable. Autant dire que faire ce genre de calcul à la main peut être casse bonbons. Trouver un KW répondant à plusieurs critères c’est pas toujours simple à analyser et visualiser.
Donc ici je cherchais un Mot clé donc la concurrence ne dépassait pas 0,5 générant au moins 500 recherches mensuelles exactes et avec un CPC supérieur à 3. j’ai pris ces chiffres totalement au hasard, ils sont à adapter à vos besoins et / ou compétences.
Bref vous avez là un outil bien pratique à pas cher qui va vous apporter du trafic ciblé et qualifié.
Enjoy
Et mince, moi qui comptait travailler sur un projet pour l’école ce matin… Je crois que plutôt je vais allez directement tester tout çà.
C’est la que je me rend compte que je ne maîtrise pas bien Excel et que c’est un tord dans le milieu du référencement (ou celui de l’internet en général) où il y a beaucoup de données à gérer et qu’il faut bien pouvoir les traiter sans forcément le faire manuellement.
Un tuto tout ce qu’il y a de plus précis.
Merci pour ces conseils.
Sympa comme technique pour utiliser le générateur de kw google. La potions magique : tu mixe ça avec les données semrush (parfois gg craque sur les cpc) et tu as quelque chose de bien complet 😉
++
Ok mais Semrush il ne retourne que des clopinettes dans sa version gratuite. Ah moins que je n’ai loupé quelque chose ?…
Ah ben tient, je ne suis pas le seul à utiliser cette technique. Seule chose que je ne faisait pas c’est la formule de mise en valeur. Je classais par ordre croissant de concurrence et regardait directement à l’oeil les expressions intéressantes.
Oui idem pour ma part.
En fait je croise les données Google pour le nombre de références sur un mot clef donné avec les données Google Adwords pour déterminer l’intérêt des internautes.
Il ne reste plus qu’à commencer par les mots clefs qui sont à la fois les plus demandés et les moins présents dans le moteur de recherche.
quelqu’un aurai la formule =si(et(B2500,D2>3), « vrai », « faux »)
pour openOffice ?
par avance merci
Chris.
Dixit batchris, de même suis interressé par une version open office de la formule…merci d’avance. 🙂
ma petite « pierre » à l’édifice de ceux qui utilise open office.
La formule indiquée dans l’article fonctionne très bien (dernière version de open sur pc) mais j’ai constaté que le résultat était faux…FAUX dans la colonne E alors que en vérifiant les conditions ce se devait être VRAI.
alors ? enfin de compte à l’ouverture du CSV, on choisit « tabulation » pour re-créer les colonnes. Tout est ok.
Je me suis apperçu que dans les cellules il y avait un apostrophe devant les données (exemple ‘0.22), invisible si on ne sélectionne pas la cellule…le supprimer et tout est ok.
le bug était là…à moins que je ne sois le seul à avoir vécu çà
argh, formule mal passée. j’encode :
=IF(AND(B2<0,5;C2>500;D2>3);"Vrai";"Faux")Très bon tuto ! Merci 😉
Je me suis créé un compte gratuit comme tu le préconise mais je ne vois pas la colonne cpc approximatif. Une astuce ?
Merci pour l’astuce que je ne connaissais pas. Cela va me permettre de gagner un temps pour ma recherche que je faisais fastidieusement.
Au plaisir de vous lire.
J’aime bcp de type de tuto avec les formules excel. Il y a de l’info et de la technique.
Merci pour ces conseils.
@Guillaume désolé de te débaucher 😀
@franck oui mais là je suis parti sur du 100% gratuit pour ceux n’ayant pas d’abo semrush
@Johnny oui chacun ses petites bidouilles, moi j’aime quand c’est visuel
@batchris tu as essayé elle ne fonctionne pas? sinon peut être une âme charitable passera par là
@CedriKo tu as un menu déroulant à droite où tu peux choisir les colonnes à faire apparaitre ou non
@all merci
Pfiou…j’ai beaucoup de mal avec l’utilisation de Excel mais ton outil peut vraiment faire gagner du temps en agence.
Pour une utilisation personnelle, je pense que SEMRush peut suffire amplement, on fait des test de concurrence et on voit comment ça prend (ou pas).
Mais très bon partage, je vais quand même tester ça ça me titille.
Comme quoi avec un simple tableau XLS on peut faire merveille. Pourquoi vouloir faire compliqué 😉
Clairement c’est là que l’on voit la puissance de Excel dans le SEO .
Il faut noter que cela est tout autant possible via un script Php et un fichier txt, xls ou une base Mysql, le principe étant exactement le même, appliquer une formule à une variable .
L’avantage d’Excel, c’est la simplicité.
L’avantage de passer par un script php c’est que l’on peut simplement garder l’historique de ses recherches, et que l’on peut créer des interactions avec d’autres scripts ( recherche d’images pour le Kw, etc etc )
Tu es vraiment un pGm d’Excel, j’adore 😉 Et pour ceux qui n’ont pas SemRush ?
EDIT Admin: c’est là où on voit que c’est un spam pourri. Tu n’as même pas lu l’article ^^
Sinon, si on est encore plus fainéant et qu’on n’a pas envie de se retrouver avec des centaines de tableaux partout on peut le faire avec Market Samurai. On s’épargne la recherche de KW, l’export dans Excel et le traitement du tableau (le soft va chercher les mots clés dans le générateur google et on peut ensuite les trier avec des filtres). Et tout ça en restant en mode radin car les modules de recherche et d’analyse des mots clés sont utilisables même si on n’achète pas de licence.
Mais pour moi un vrai SEO ne peut pas se contenter bien longtemps d’outils gratuits.
Oh joli Market Samurai ! Merci.
Je ne connaissais pas. Manque plus qu’une version Mac.
De mémoire, Market Samurai est en flex du coup utilisable aussi sur Mac.
Sympa cette formule sur Excel. Je suis sûr que vous le savez mais il est important de le souligner pour les néophytes: la concurrence indiquée est la concurrence Adwords, or un mot clé très concurrentiel sur Adwords peut avoir un intérêt mince dans une campagne SEO et des mots clés très concurrentiels en organique indiquer un niveau de concurrence faible ou moyen dans Adwords. Ex: crédit mutuelle 27 100 recherches mensuelles locales en exact mais concurrence faible.
Merci Raph pour ce tuto, simple mais diablement efficace! Ha oui, au passage grâce à toi, je vais devoir rebosser une partie de mon référencement sur mon site, merci 😉
Un petite gifle mais qui fait du bien. Si tu peux faire d’autres petits tutos dans l’esprit (sans dévoiler tous tes secrets de référenceur, quoi que ^^) je pense qu’on serait nombreux à les lire.
Merci encore.
Eric
Merci pour ce petit tuto bien sympathique, que je viens de tester et qui fonctionne malgré qu’en gardant tes critères, je n’ai rien qui me correspond..Tout est faux, je vais devoir trouver une niche plus ROIste.
Pour moi, il est primordial d’utiliser les outils gratuits (cf Keyword tool) qui sont très performants avant de se jeter sur des outils payants et parfois décevant!…
A+ Xav
J »utilise l’outil de Google mais j’avoue que le tableur excel est une vrai découverte pour moi. Encore beaucoup de chose à apprendre!
Voilà, je peux enfin remercier mon professeur d’informatique de m’avoir bourré le crane avec les formules Excel pendant ma formation. J’utilise une technique similaire pour élaborer mes stratégies KW 🙂
Excellent article et tutorial, je sais que je néglige trop les outils de bureautiqu qui peuvent amener de telles perles d’analyse.
Mon souci toutefois est que je ne trouve pas ces commandes sous Open Office, aussi j’espère qu’il n’est pas nécessaire d’avoir une licence d’un certain produit commercial pour appliquer ce calcul ? Une idée ?
Edit: entre le moment où j’ai noté de répondre ce matin et l’actualisation de ma page, bcp de commentaires, et des questions similaires ! 😉
Et ben c’est très sympa, ça…!
Thx dude !
PS : Souci avec ta formule chez oim’, du coup, j’utilise :
=SI(ET(B2500;D2>0); "Vrai"; "Faux")Excellent article. C’est fout les pouvoirs d’excel.
Hello Raph,
Super cool de te voir joujouter avec excel.
J’ai une petite question concernant la concurrence : pourquoi tu prend ce facteur dans le calcul de ton KW cible?
Cette donnée permet de voir la concurrence existante sur les annonces adwords et non pas la concurrence du KW sur les resultats naturels non?
Ba dis donc, tu te remets aux articles utile? Commet ça se fait? :p
Merci, j’avoue que j’avais fait ça, j’utilisais semrush pour trouver les bons keywords + traffic travis et je revenais le lendemain matin mais ça va être plus facile maintenant.
Du coup petite astuce en plu clique droit sur la colonne trie a-z et hop vous avez tous les « bons » keywords. Un jour il faudra que je me mette vraiment à excel..
Les articles où tu mix Seo et Excel sont vraiment ceux que je préfère. En effet moi je suis une grosse buse avec excel mon cerveau étant souvent réfractaire à une certaine logique mathématique !! Donc ne te prive pas dans le futur à ré-éditer ce genre de billets pour les pauvres billes comme moi. Merci encore
@Evenstood sauf que tu ne filtres qu’une variable à la fois
@Christian par ce qu’ils existent de bon marketeux qui vendraient du sable à un touareg
@Phil oui sauf que tout le monde n’a pas les compétences pour utiliser script, Bdd … là je pense que c’est à la portée du plus grand nombre; Après il y a mieux je te le concède
@Samuel je ne me souvients plus exactement comment fonctionne Market samourai, je dois toujours l’avoir dans un coin; mais tu sais avec que du gratuit et un peu de jugeote il y a déjà de quoi bien s’amuser
@Erwan Absolument, mais disons que j’en ai une utilisation bien particulière pour monter des sites à pubs 😉
@Eric je tâcherai de récidiver, mais je suis tellement meilleur dans les conneries plutôt que le sérieux 🙂
@Xavier, attention les valeurs sont a adapter, j’ai mis des trucs au hasard. Il faut choisir en fonction de ta théma, de la concurrence…
@Thomas, disons que c’est un petit plus qui rend bien service
@Jeremy oui on pourrait faire un site entier sur excel et le seo je crois
@Pierre ta réponse est dans le com de Sylvain 😉
@Emmanuelle soucis? chez moi ça fonctionne nickel, tu sais où me trouver si besoin est 😉
@Pablo oui tu as raison, mais disons que j’ai une technique pas trop mal rodée là dessus pour mes MFT qui me permet de monter des sites sur des niches sans concurrence. Et du coup du bon contenu et une poignée de Bl et je ne touche plus à rien. Je t’en aurais bien dit plus mais vu que tu aimes pas mes pulls à rayures :p Le nombre de résultats sur la requête est intéressant aussi
@Soul la dépression sûrement ou la fièvre, je ne vois pas d’autres explications 🙂 Ouep ça aide pas mal avec ça, outil google, semrush, ahrefs.. plein de possibilités
@Seb Merci mais tu sais j’ai un niveau vraiment basique dans excel. Du coup je réfléchis à quoi j’aurais besoin et regardes si une formule existe. ensuite j’adapte et voilà ce que ça donne
@Sylvain Merci, les adeptes de Open Office t’en seront reconnaissants 😉
NICE! Sympa le petit tuto, et surtout sympa les petties formules excels – ca fait gagner du temps tout ca.
Merci
Bonsoir, encore un tutoriel au travers d un excellent article très bien illustré.
Je vais de ce pas tester tout ces conseils mais pour moi se sera sous open office car trop cher Microsoft 😉
Merci beaucoup pour la formule adaptée à open office.
Merci beaucoup pour ce tuto, je m’en vais tester ça de ce pas.
@Sylvain Merci pour la version open office.
Question de noob : sur SEMRush les CPC indiqués sont ils ceux d’adwords (annonceur) ou ceux d’adsense (affilié) car il doit y avoir du 30% de différence non ?
Merci pour ce tutoriel que je viens de tester et qui se montre très efficace.
Merci pour l’article. Je viens de tester, c’est sympa.
Juste une petite question. Dans adwords, dans la colonne concurrence, on voit soit « élevé », « moyenne », « faible ». Ça désigne la concurrence sur ces mots clés. Pourtant lorsqu’on importe le fichier, la concurrence est désignée par un nombre entre 0 et 1. Elle signifique quoi exactement? Ça désigne le nombre de sites qui sont optimisés pour un mot clé donné? Ou bien c’est un indicateur pour les personnes qui veulent acheter ce mot clé? 🙂
En gros, s’il est faible, cela veut dire qu’il sera plus facile de se positionner ou ça veut uniquement dire qu’il n’y a pas beaucoup d’annonceurs qui veulent ce mot clé? 🙂
Merci !
Simple, précis et rapide à mettre en œuvre, quoi demander de mieux…
Merci pour ce tuto !
Les chiffres sont bien sûr à adapter en fonction de sa thématique
Excellente méthodologie que voilà.
J’utilise la même, mais j’y ajoute d’autres variables pour pouvoir faire le tri quand le nombre de mots clés pertinents est trop grand : le nombre de résultats sur Google et le nombre de mots clés de l’expression (plus l’expression contient de mots, plus il est facile de se positionner dessus).
Bonjour Daniel, est ce que éventuellement tu pourrais compléter la formule en ajoutant comme tu l’indiques si bien le nombre de résultats sur Google et le nombre de mots clés de l’expression ? Je sais que je PEUX le faire mais j’ai tellement de choses à apprendre que ça me ferait gagner un temps précieux…
Au passage, ce site regorge d’excellents tuto, merci de les partager !
Excel est l’ami du SEO. Bien utiliser les formules d’Excel peut être un énorme gain de temps. Sinon, félicitations, tu es déjà prems sur « référenceur freelance à Tataouine-Les-Bains »! Je sens que les contrats vont tomber! 😉
C’est vrai que je ne prends jamais la peine d’exporter mes Keywords dans excel je fais tout direct dans le Keyword Tool de Adwords… je vais essayer autant je vais découvrir des mots clés auxquels je n’avais pas prêté attention 🙂
Super tuto, et très précis. Merci 😉 !
Je vais m’essayer à cela le plus tôt possible !
après avoir pas mal galéré, il apparait que le séparateur est une tabulation (pour les gens utilisant open office)
merci à Raph pour l’article 🙂
encore moi 🙂
sur open office mac, la formule de sylvain ne fonctionnait pas, j’ai donc tenté celle de Raph, qui fonctionne à condition de remplacer les guillemets wordpress par des double quotes
Merci beaucoup pour ces petites astuces à adapter en fonction des besoins 🙂 Mais une question : pourquoi passer par un nettoyage des colonnes dans excel, alors qu’il est possible de les choisir directement dans l’interface Adwords avant l’export (menu déroulant « colonne ») ?
Ha une petite astuce wordpress au passage pour que les quotes ne soient pas mal interprétées, et ne flingue pas la formule, il faut les mettre au format html : & q u o t ; (sans les espaces ^^)
Très concret comme tuto ! J’utilisais déjà un peu les deux outils (générateur de mots clés et Excel) mais pas de de cette manière là. C’était beaucoup plus complexe pour arriver à une analyse moins fine…
petit tuto bien sympa et utile. On peut même mettre tout ça dans les Google Docs, et utiliser la puissance de ceux-ci.
Bonjour à tous,
Désolé mais n’ayant pas MS Excel pourriez m’indiquez la marche à suivre pour appliquer cette formule dans OpenOffice Windows ?
Merci
Mais cet article tombe à point ! Merci 🙂
Vu que j’ai perdu quelques minutes à chercher pourquoi j’avais un #NOM qui apparaissait suite à la formule… Sur Mac et en français ca donne donc : =SI(ET(B2500;D2>3); »Vrai »; »Faux »)
edit1: ah zut, c’est pas du tout ce que j’ai écrit 🙂
c’est pas B2500 mais bien B2500;D2>3
edit2: bon, abort mission… 🙂
Merci pour ce billet. C’était très intéressant.
J’y ai retrouvé un tuto très pratique.
Je ne connaissais pas cette méthodologie, merci Raph. Perso je ne vais pas toujours aussi loin que ca pour le choix des mots-clés, mais prendre la concurrence, le CPC voire d’autres critères est intéressant et plus partinent !
Merci, cela donne envie de tester par curiosité en se faisant plusieurs feuilles critères 1, critères 2 …
Je viens de faire le test et c’est Ok! J’ai aussi essayé celui de « guillaumedesbieys » dérivé de la méthode de Raph. Chapeau à vous hein!
Super Tuto Raph, il y a quand même un truc qui me laisse perplexe. Est ce que la valeur 0,39 te garantie que la concurrence sur les résultats organiques est modérée ?
Cette valeur est seulement relative aux autres mots clés de la thématique, à mon avis faut faire gaffe avec ça !
Bonjour, bon décidément j’ai encore énormément de choses à apprendre sur le sujet ! Je ne suis pas très experte dans l’utilisation d’excel donc il va me falloir un peu de temps pour me familiariser avec la bête! Que tout çà me paraît compliqué à une novice comme moi ! Y a pas plus simple ?! Merci
@Julien de rien c’est fait pour
@Simon sur SemRush comme adwords ce sont les CPC max annonceurs donc ce n’est pas la réalité de tous les jours
@Sebastian plus on est proche de 0 moins il y a d’annonceurs sur ce KW et vice versa Donc c’est un indice de concurrence adwords et non pas naturel
@Pierre oui à chacun de trouver ses valeurs
@Daniel moi aussi mais j’allais pas tout donner quand même 😀
@Arnaud oui depuis que je suis 1er sur ce kw je croule sous les demandes
@Jguiss dans keyword tool tu ne peux pas tout faire ou tout voir d’où mon passage dans excel
@Christian thx
@Julien non surtout pas les outils GG c’est le mal 🙂
@Gaelle désolé j’adapte par rapport à ce que j’ai et je fais
@Nico qui peut plus peut le moins
@Paco oui Guillaume m’avait demandé pour faire ça sur mon site et je trouvais que c’était mieux sur le sien merci à lui
@Chloé on apprend tous les jours et c’est tant mieux 😉
je suis bien contente d’avoir atterri sur cet article. Je retrouve un tutoriel bien pratique pour mon métier de tous les jours. Merci!
Cool, merci pour cet article et merci à Sylvain pour la formule en anglais qui était la raison d’un blocage sur mon PC.
Quelques heures d’économiser par mois avec ça… Thanks
Merci pour ce tuto super pratique qui me rappelle à quel point il est impératif que j’apprenne à me servir d’excel. Bcp de temps économisé pour les mois à venir !
Quelqu’un a déjà essayé la formule avec Google Doc ?
Il suffit de le traduire en anglais !
=IF(AND(B2500;D2>3); « Vrai »; « Faux »)
Bonsoir,
je ne suis pas développeur, je ne suis pas référenceur pro, j’aime bien excel, j’aime bien le référencement donc cet article tombe vraiment à point nommé. La difficulté que je vais rencontrer va se situer au niveau du champ lexical des mots clés recherchés. J’ai parfois l’impression que google ne donne pas toute l’étendue des mots clés ou ces derniers ne sont pas assez recherchés par les utilisateurs.
Merci pour ce tuto, ces astuces me seront utiles, c’est vrai que je reste assez basique niveau référencement sur mon blog. Je ne prends jamais vraiment le temps de travailler ça sérieusement (suivi, recherche ou analyse avec document excel…). Grâce à votre article je vais peut-être m’y mettre ^^
Merci Raph pour cette article ,je viens de relire ton article pour la enième fois. Concrètement pour les MFT, la colonne « concurrence » a très peu d’intérêt, quel tool utilises-tu pour connaitre la concurrence SEO? Merci, Xavier
Merci pour la formule. Juste une remarque qui me semble a son importance, vous raisonnez sur de la concurrence Adwords, ce qui est différents de la concurrence seo. Il n’y a pas de données dans le générateur de mots clés pour la concurrence naturelle.