Die ultimative Keywordrecherche

3. Juli 2014  |     |  39 Kommentare
Ein Beitrag von Felix

Anfang des Jahres habe ich auf der Campixx einen Vortrag zum Thema Keywordrecherche gehalten. Das Feedback danach hat mich sehr gefreut, daher wird es Zeit die Inhalte zu veröffentlichen. In diesem Beitrag zeige ich euch, wie ihr mit Excel einen umfassenden Keywordkatalog erschaffen könnt, der als Nachschlagewerk eure zukünftige Arbeit erleichtert. Obacht, es wird etwas Daten-lastig! Aber es lohnt sich.

chaotische keywordrecherche

Chaos war gestern, jetzt wird aufgeräumt!

Schritt 1: Daten sammeln

Zuerst geht es darum, so viele Daten wie möglich zu sammeln. Je mehr Keywords wir haben, desto besser. Es lohnt sich wirklich, gleich gründlich zu arbeiten, nachträglich Keywords einzufügen wird sehr chaotisch. Die Relevanz ist dabei erst mal ziemlich zweitrangig, exportiert einfach was das Zeug hält! Wir bringen später einen Sinn in all diese Keywords, indem wir sie filtern und kategorisieren. Mögliche Quellen sind:

  • Suchanfragen aus den Webmaster Tools: Alle Begriffe exportieren, bei denen ihr schon Impressionen habt.
  • Sistrix/Searchmetrics/Seolytics Keywords: Alle bisher rankenden Keywords exportieren, möglichst aus allen verfügbaren Tools.
  • Seolytics Keywordvorschläge: Einfach die wichtigsten Keywords eingeben und jeweils die Vorschläge exportieren.
  • Ubersuggest: Die wichtigsten Keyword bei Ubersuggest eingeben, jeweils „Select all keywords“ klicken, und rechts an der Seite per „get“-Button exportieren.
  • Longtail Keywords: Mit dem Synonymizer von SeoDev, einem sehr simplen aber effektiven Spinning Tool, könnt ihr sicher gehen, dass ihr keine Kombination aus [Keyword] kaufen/online/bestellen/günstig/… vergesst
seodev synonymizer

Sehr praktisch um wirklich nichts zu vergessen: Der Synonymizer von SeoDev

Schritt 2: Konkurrenz analysieren

Womöglich hat die Konkurrenz Keywords abgedeckt, an die ihr bisher noch nicht gedacht habt. Sucht euch Konkurrenten heraus, die das gleiche oder ein kleineres Sortiment als ihr haben (nicht Amazon), und holt euch all deren Keywords. Ob Sistrix, Searchmetrics & Co., völlig egal, am besten alle!

Schritt 3: Daten zusammenführen

All diese Keywordlisten fügt ihr in einer Spalte in Excel zusammen. Jetzt habt ihr hoffentlich einen Haufen an Keywords, viele Tausend Stück. Die werden wir jetzt nach und nach sortieren. Dafür müsst ihr zuerst die doppelten Keywords entfernen. Dafür klickt ihr im Reiter „Daten“ auf „Duplikate entfernen“, wählt die Spalte mit euren Keywords aus und schon ist die Liste deutlich kürzer.

Danach reichern wir die Keywords mit Zahlen zum Suchvolumen an, für SEA lohnt sich natürlich auch der Klickpreis usw.. Leider kann man beim Google Keyword Planner nur 800 Keywords auf einmal abfragen und exportieren – dass 3000 per Datei-Upload funktionieren, ist gelogen. Also ist erst einmal Fleißarbeit gefragt (oder browser automation, munkelt man).

Schritt 4: Keywords katalogisieren

Um den Daten einen Sinn zu geben, müssen wir uns jetzt sinnvolle Kategorien überlegen, in die wir die Keywords einteilen. Bei einer simplen Info-Seite z.B. über die kanarischen Inseln wäre das:

  • Ort: Kanaren, Teneriffa, Lanzarote, Arrecife, …
  • Intention: Flug, Hotel, Mietwagen, Strand, Wetter, …

Für diese Kategorien erstelle ich jeweils eine eigene Spalte. Und jetzt geht es ans Filtern! Vom Gröbsten zum Feinsten filtern wir die Keywords und verpassen ihnen eine Kategorie. Genauer gesagt heißt das:

filtern in excel

Der Doppelklick an die rechte untere Ecke der Zelle erspart euch lästiges copy & paste.

  1. Ich klicke in Excel auf Filtern
  2. Ich filtere nach „kanaren“
  3. Ich schreibe ins oberste Feld bei meiner Kategorie „Ort“: Kanaren
  4. Ich doppelklicke auf die rechte untere Ecke der Zelle, um alle darunterliegenden Zellen auch mit „Kanaren“ zu füllen
  5. Ich filtere nach „kanarische“
  6. Ich schreibe ins oberste Feld bei meiner Kategorie „Ort“: Kanaren
  7. Ich doppelklicke auf die  rechte untere Ecke der Zelle, um alle darunterliegenden Zellen auch mit „Kanaren“ zu füllen

Und so arbeite ich mich Schritt für Schritt nach vorne, für alle Kategorien.

Schritt 5: Daten auswerten

Nachdem möglichst viele der Keywords katalogisiert wurden, geht es an die Auswertung – mit Hilfe einer Pivot-Tabelle. Dafür wählt ihr die gesamte Tabelle aus und klickt unter „Einfügen“ auf „Pivot-Tabelle“. Das Dialogfeld bestätigt ihr einfach, die Einstellungen passen.

erstellung einer pivot datei

Wer bisher nie eine Pivot-Tabellen erstellt hat: Macht es, es lohnt sich!

Jetzt zieht ihr per Drag and Drop eure Kategorien in das unterste linke Feld („Zeilenbeschriftung“, siehe Bild). Je nach Reihenfolge werden die verschiedenen Kategorien kombiniert. Das ist wie eure Navigationsstruktur zu verstehen: als erstes wählt ihr den Ort, davon abhängig die Intention, und als unterste Zeile nehmt ihr eure Keywords.

struktur der pivot tabelle

Mit den vier Feldern unten rechts bestimmt ihr die Inhalte eurer Tabelle.

Euer Suchvolumen zieht ihr in das unterste rechte Feld. Mit einem Klick auf den Pfeil neben dem Suchvolumen (in der Grafik unten rot eingekreist) stellt ihr die „Wertfeldeinstellungen“ auf „Summe“ um. So werden alle Suchvolumen für die jeweilige Keywordkombination addiert, damit wir sie anschließend absteigend sortieren können (z.B. per Rechtsklick auf ein Suchvolumen).

pivot tabelle mit suchvolumen

Der Clout an einer Pivot-Tabellen ist, dass sie Werte zusammenfassen kann – wie zum Beispiel das Suchvolumen.

Das Ergebnis ist eine 100% logische Seitenstruktur. Für jede Kategorien-Kombination sehen wir mit einem Klick alle relevanten Keywords, nach Suchvolumen sortiert. Alle diese Kombinationen (z.B. Gran Canaria + Mietwagen) sind potenzielle Landingpages, die wir nach und nach erstellen können – sofern es sich denn lohnt. Schließlich sehen wir jetzt auf einen Blick, wie viel Suchvolumen hinter der jeweiligen Landingpage steckt.  Ist das was? 🙂

Ich gebe zu, der Aufwand ist nicht gerade gering. Aber die Vorteile überwiegen dann doch:

  • Ich finde so lukrative Nischenkeywords, die ich sonst völlig übersehen hätte (z.B. „Wetter Teneriffa Süd“ mit 5400 und „Wetter Teneriffa Nord“ mit 2400 Suchanfragen/Monat).
  • Ich muss nicht jedes mal wieder eine neue Liste machen und habe am Ende nicht zig verschiedene Dateien.
  • Ich erkenne auf Anhieb, wo meine lukrativsten bzw. meistgesuchten Landingpages sind.
  • Ich sehe, welche meine wichtigsten Themenbereiche sind (z.B. auf welche kanarische Insel ich mich konzentrieren sollte).
  • Ich kann sicher sein, dass meine Seitenarchitektur danach absolut logisch ist.
  • Ich kann für meinen Blog bzw. mein Magazin alle nicht gelabelten Keywords durchforsten und finde gefragte Themenideen (ich kenn ihn zwar nicht, aber der Loro Park auf Teneriffa wird z.B. 14800 mal gesucht).

Schritt 6: Besser machen

Damit ist das Tool schon wirklich hilfreich, aber es geht natürlich immer noch besser. Ansätze dazu sind:

  • Rankings einfügen: Ihr könnt noch die aktuellen Rankings eures Projekts zu den jeweiligen Keywords in die Liste ziehen (per =SVERWEIS, vgl. Beispieldatei am Ende).
  • Keywordnutzung einfügen: Ihr könnt euch auch ziehen, ob/wie oft das Keyword auf eurer Seite bisher vorkommt (per =IsFoundOnPage mit den SeoTools).
  • Semantik einbeziehen: So wie beschrieben ist das Tool etwas „2012“. Die Gefahr besteht, dass man sich zu sehr auf die direkten Keywords bezieht. Wichtiger ist aber, dass auch semantisch passende Begriffe gesammelt werden. Das ist mit dieser Methode durchaus möglich, dafür muss man aber etwas out-of-the-box denken. Welche Tools dabei helfen, hat Sepita in seinem Vortrag auf der SMX (inzwischen leider nicht mehr auffindbar) sehr gut erklärt.
  • Es gibt sicherlich noch viele weitere Möglichkeiten – wenn ihr Ideen habt, freue ich mich über Kommentare!

Download

Weil das Ganze natürlich verständlicher ist, wenn ihr es vor euch habt, hier noch eine Beispieldatei. Die Datei hat zwar nur 800 Keywords und ich habe wenig kategorisiert, aber das Grundprinzip wird klar. Viel Erfolg damit!


Bild: Stefan62 / panthermedia.net

Jetzt teilen: Share on Facebook0Tweet about this on TwitterShare on Google+13Email this to someoneShare on LinkedIn0

E-Book: Der Weg zum erfolgreichen Online Shop

  • Das 47-seitige E-Book mit 72 konkreten Tipps für Deinen Online Shop
  • Einmal wöchentlich exklusiver Content für das Marketing Deines Shops
  • Unser Newsletter (1-2x pro Woche)
E-Book kostenlos downloaden

39 Kommentare

  • Maria

    Ich danke dir recht herzlich für diesen tollen beitrag! ich wünschte es hätte ihn schon vor einem jahr gegeben, als ich in der branche angefangen hab und mir alles selber beibringen musste.

    es hat mich immer so genervt, dass ich auf die „wie genau mache ich denn eine keywordrecherche“ nur 0815 antworten gehört/gefunden hab… wie z.b. „du must die relevanten keywords finden, weil keywords sehr wichtig ist, weil blah blah blah“. Und DAS ist endlich die antwort, die ich mir damals gewünscht hab!

    Der beitrag wird sicher sehr vielen helfen! nochmal danke und super gemacht:)

  • Was machst du mit keywords wo es kein Suchvolumen gibt? Rausschmeißen?

  • Hey Felix,
    schön, dass du deinen Campixx Vortrag noch einmal aufbereitet hast und zur Verfügung stellst. Zum Thema „Duplikate entfernen“ habe ich noch eine Anmerkung: Excel erkennt beim Entfernen der Duplikate einige unterschiedliche Schreibweisen von Wörtern und entfernt somit teilweise zu viele Keywords. Daher markiere ich in disem Fall die gesamte Spalte der Keywords, lasse mir mit den Standardeinstellungen eine Pivot-Tabelle erstellen und ziehe die Keywordspalte in das Feld „Zeilen“. Somit werden nur wirklich gleiche Schreibweisen zusammengefasst (Groß- und Kleinschreibung wird weiterhin zusammengefasst). Eine Verdeutlichung inkl. Suchvolumina (aus dem Google AdWords Keyword Planner) und Sonderzeichen habe ich hier kurz dargestellt: https://www.dropbox.com/s/yqx1jzy33ytjcry/Excel-Duplikate-entfernen_001.png
    Hieraus wird deutlich, dass unterschiedliche Schreibweisen auf jeden Fall zusätzlich betrachtet werden sollten!
    Beste Grüße, Jean-Luc

  • Felix

    @Tom: Bewusst nicht rausschmeißen, sondern einfach auf 0 setzen. Sonst zweifelt man irgendwann und schaut die noch ein zweites und drittes Mal nach.

  • Felix

    @Jean-Luc: Macht Sinn, gefällt mir!

  • Felix Foygel

    vielen vielen Dank! Eure Tipps sind wirklich goldwert! Macht weiter so!

  • Moin Felix!

    Ein wirklich klasse Artikel + Recherche-Methode, ich mag es immer wenn man mal sieht wie andere bei der Recherche vorgehen.

    Mir kam gerade noch etwas in den Sinn: Wäre es möglich (ich habe von Excel null Ahnung) sowas wie intitle:keyword oder nur Keyword bei Google abzufragen und davon die Anzahl Suchergebnisse in eine Spalte zu packen?

  • Hallo Felix,

    auch ich danke Dir für die Mühe – und den Download! Wir sammeln aktuell unsere Keywords noch manuell, weil einige Vorteile auf der Hand liegen (mehr Nähe zu den erhobenen Daten, Feingefühl für Zusammenhänge etc.), aber wir werden dein Vorgehen dennoch ausprobieren und sehen, was wir noch lernen können.

    Grüße
    Olaf

  • Bei Euch findet man doch immer mal Artikel mit Mehrwert. Das wird allgemein immer seltener bei den üblichen Blogs. Danke dafür!

  • Felix

    Danke für all eure netten Kommentare erst mal, freut mich!

    @Henry: theoretisch ja, aber in so einem großen Maßstab stößt man schnell an seine/Googles Grenzen. Mit einem ausgewählten Keywordset macht das aber durchaus Sinn.

    @Olaf: Ich will auch nicht behaupten, dass ich das immer so mache! Manchmal ist es so viel, dass es mit dem Katalog besser geht, manchmal macht es händisch mehr Sinn. Dass hier ein bisschen das Feingefühl verloren geht stimmt, aber ganz Hirn ausschalten geht eben nie. 🙂

  • @Felix:

    Hast du vllt. einen weiterführenden Link oder selbst ein Code-Schnipsel dafür? Eventuell kann man da ja ein delay einbauen, so dass man mehr abfragen kann – und selbst wenn nicht, dann macht man es nur für das Keywordset an dem man gerade dran sitzt 🙂

  • Felix

    So zum Beispiel: =XPathOnUrl(„https://www.google.de/search?q=site%3Ahenryganschof.de%20intitle%3Ahenry“;“//div[@id=’resultStats‘]“)

    Das Keyword („henry“) müsstest du eben in eine extra Zelle schieben damit das automatisch läuft. http://nielsbosma.se/projects/seotools/functions/xpathonurl/

  • Shuyin

    Hi Felix,

    das ist wirklich eine super Idee. Das Problem das ich hierbei sehe ist die Kategorisierung.

    Man gibt ein Begriff ein und kategorisiert die ausgespuckten Keywords. Der Begriff muss also in den Keywords enthalten sein. Bei semantischen Keywords wird das ein Problem, da man nicht alle semantischen Begriffe kennt um diese einzugeben.

    Oder sind deine Keywords immer alle durchkategorisiert?

    Viele Grüße

  • „…Mit dem Synonymizer von SeoDev, einem sehr simplen aber effektiven Spinning Tool, könnt ihr sicher gehen, dass ihr keine Kombination aus [Keyword] kaufen/online/bestellen/günstig/… vergesst“

    Ein nettes Online-Tool ist auch: http://midgets.dsquare.de/keyword_combination.php

    Für bis zu 4 Keywordlisten und kostet nix…

  • Luisa

    Danke für den klasse Artikel. Ich habe das auch gleich mal versucht umzusetzen. Eine Frage stellt sich mir allerdings: Wie machst du das, dass das du nur den gefilterten Ergebnissen die Kategorienamen zuordnest? In deinem Screenshot sind die gefilterten Ergebnisse, wenn du nach „Kanaren“ suchst, mit fortlaufenden Nummer. Dann kann man einfach die Kategoriebezeichnung runterziehen. Zumeist sind aber nicht alle gefilterten Ergebnisse fortlaufend in der ursprünglichen Tabelle an Keywords. Ich habe also z.B. Ergebnisse zum Thema Kanaren in Zeile 25,33 und 40. Wenn man nun die Kategoriebezeichnung runterzieht steht in allen Zeilen von 25-40 „Kanaren“. Gibt es da einen Trick, den du verwendest, damit man nicht jede Zeile einzeln copy+pasten muss?

  • Felix

    @Shuyin: Wenn es Richtung Semantik geht wird es schwierig – es bleibt halt immer auf einer manuellen Ebene. Und jedes einzelne Keyword der entsprechenden semantisch-passenden Kategorie zuzuordnen ist enorm viel Aufwand. Es ist schon möglich, aber die Methode dient eher dazu, sich auf die „Hauptkeywords“ zu fokussieren.

    @Michael: Sehr cool, danke!

    @Luisa: Eigentlich sollten die Zeilen, die du gefiltert hast, nicht ausgefüllt werden. Zumindest wenn du den Doppelklick auf die untere Zellenecke machst. Alternativ kannst du die Ecke auch anklicken und nach unten ziehen.

  • Hallo,

    ein sehr gelungener Beitrag! Der Downloadbutton lässt sich leider nicht mehr drücken, ist das beabsichtigt?

    Grüße,

    Vitaly

  • Felix

    danke vitaly, jetzt gehts wieder!

  • Lieber Felix,

    deinen Artikel habe ich mir gespeichert und erst jetzt gründlich gelesen.
    Vielen Dank dafür! Wenn man sich die ganze Zeit um sich selbst dreht, übersieht man doch so einige interessante Keywords.

    Liebe Grüße aus Hamburg
    Ulrike

  • Stephan

    Hallo Felix, danke für diesen interessanten Artikel. Glaubst du, dass man mit dem Google Keyword Planer alleine auch auskommen kann, so wie es in diesem Tutorial http://david-asen-marketing.de/keyword-recherche/ gezeigt wird oder empfiehlst du unbedingt zusätzliche Ressourcen?

  • Marco

    Warum benutzt ihr kein SECockpit?

  • Felix

    Du meinst für die Abfrage des Suchvolumens? Könnte was bringen. Allerdings kann man da wieder nicht die Keywords kategorisieren, so wie ich das sehe. Insofern arbeite ich lieber mit Excel. Über APImetrics geht die SV-Abfrage auch Excel-kompatibel.

  • sehr guter Artikel und eine super Liste. Habe mich gleich mal rangesetzt und werde mal versuchen, die ein oder andere Keyword-Liste dahin zu übertragen.
    ich berichte dann mal wie es sich auf meine Fälle anwenden lässt!

    gruß

  • Erneut ein toller Artikel von Dir. Freue mich schon auf den nächsten Blog Beitrag von Dir.

  • Marc

    Sehr schöner Artikel! Interessant wäre nun zu wissen wie es weiter geht, wie bereite ich den Content optimal auf die Keywords auf?!

  • Hey Felix!

    Vielen Dank für den coolen Artikel!!!

    Hier noch ein paar – vielleicht für manche – hilfreiche Ergänzungen:

    1. „Leider kann man beim Google Keyword Planner nur 800 Keywords auf einmal abfragen und exportieren – dass 3000 per Datei-Upload funktionieren, ist gelogen.“ Hier kann ich das Tool „LongTailPro“ empfehlen, welches genau das beim Keyword Planner macht „oder browser automation, munkelt man“ und bei dem du 10.000 eigene Keywords abfragen kannst.

    2. Außerdem empfehle ich noch den Keywordscraper von Scrapebox für die Keywordrecherche. Zum einen kannst du Meta-Keywords deiner Wettbewerber scrapen und du kannst mit dem Keywordscraper auch Suggests aus Google, Amazon etc. holen.

    3. @Henry: Mit dem „Google Competition Finder“ von Scrapebox (und einer ausreichenden Menge Proxy-Server), kannst du sehr schnell die Title-Competition für X-Tausend Keywords abfragen und in Excel mit deiner Liste matchen.

  • Felix

    Danke für die Hinweise, Matthias!

  • Auch von mir ein großes Danke für den ausführlichen und anschaulichen Artikel und natürlich für die Download-Möglichkeit. Ich habe bisher einfach so von Hand gesammelt. Mit deiner Anleitung ist das sicherlich sehr viel komfortabler. Wird gleich versucht.

    Beste Grüße
    Marion

  • Danke für die geniale Anleitung! Leider ist der SMX-Beitrag nicht mehr erreichbar…

  • Felix

    Hallo Michael, danke für den Hinweis! Leider scheinen Catbird Seat ihren Slideshare-Account gelöscht zu haben. Habe keine anderen Versionen gefunden. Schade.

  • Hi Felix,

    Glückwunsch zum sehr guten Artikel, auch wenn mittlerweile das eine oder andere Tool nicht mehr online ist und der Keyword-Fokus (= Tunnelblick) etwas nachlässt zugunsten von Content Marketing…

    Hier noch ein paar Ergänzungen und am Schluss eine Frage.

    1. Sehr praktisch bei der Kategorisierung der KWs ist die Möglichkeit, beim Google Keyword Planer die KW-Ideen im Tab „Anzeigengruppen Ideen“ runterzuladen, dann hat man mit den „Ad groups“ bereits eine rudimentäre Kategorisierung im Datenbestand.

    2. Ich habe gute Erfahrungen gemacht, indem ich den Google Planer auf gut rankende Webseiten zu meinem Thema gelenkt habe. Damit hab ich wohl „offiziell“ gescrapt…

    3. Eine praktische Frage zur Verarbeitung der Daten in einer Tabellenkalkulation habe ich und freue mich über Anregungen.

    Viele Webseitenbetreiber bzw. Suchanfragen haben einen regionalen Schwerpunkt Daher möchte ich Einträge aus meiner KW-Liste entfernen, die nicht zu meinem regionalen Schwerpunkt passen. Ein Beispiel: Eine Zahnarztpraxis in Zwickau braucht in der KW-Liste keine Keywords zu „Wurzelbehandlung Würzburg“. Dennoch habe ich wohl oder übel auch solche Einträge im Keyword-Meer.

    Nun habe ich in Spalte A die Keywords, in Spalte B eine Liste mit +2000 deutschen Städten. Eine Formel soll mir in Spalte C sagen, welche Treffer ich rauslöschen kann.

    Eine erste Formel =WENN(ZÄHLENWENN(A2;B$2:B$2070);“Löschen“;““) funktioniert nur, wenn der Wert in B2 absolut identisch ist mit dem Wert in Spalte A. Ich suche also eine Formel, welche die Werte in Spalte B nimmt und mit den in Spalte A vorhandenen Textstrings vergleicht und dann in Spalte C bei einem Treffer „Löschen“ schreibt.

    Gibt’s da eine Lösung (und wenn ja, auch für LibreOffice?)

    LG Carsten

  • Felix

    Hi Carsten,
    freut mich, dass du dich so intensiv damit auseinandergestezt hast! 🙂 Hast sehr recht mit deinen Punkten, das ist auch hilfreich.
    Zu deiner Excelfrage. Ich hätte das so gelöst: =WENN(ISTZAHL(FINDEN(B2;A2));“Stadt relevant“;“Stadt irrelevant“) – hab es aber nicht getestet. Vielleicht ists ja eine Idee. 🙂

  • Roland

    Hi und danke für den wirklich guten Tipp!
    Ich habe nur das Problem, dass die Suchanfragen innerhalb eines Clusters nicht nach Größe absteigend sortiert sind. Und mit nachträglichem Filtern wird nur nach Gesamtsumme gefiltert, die Einzelwerte bleiben aber unverändert.
    Hat jemand einen Tipp für mich?
    Danke und Grüße
    Roland

  • Felix

    Hi Roland, gern! Nach den Feiertagen komme ich jetzt zur Antwort. Du möchtest wahrscheinlich innerhalb der Pivot-Tabelle sortieren? Dann musst du direkt auf die zu sortierende Zahl rechtsklicken – in der entsprechenden Ebene(=Cluster), die du sortieren möchtest.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Willst Du fit in SEO werden?

Abonniere unseren Newsletter und bleibe in Sachen SEO, Inbound- & Online-Marketing auf dem Laufenden.

Du kannst den Newsletter jederzeit wieder abbestellen.