SEO - Suchmaschinenoptimierung

Teil 2: Tutorial Keyword-Recherche mit Google Docs – Abfrage der Keyword-Daten

Kostenlos oder einfach? Zwei Möglichkeiten die Keyword-Daten wie Suchvolumen und CPC zu deiner Recherche abzufragen.

© Rawpixel - Fotolia

Im ersten Teil unseres Tutorials “Longtail Keyword-Recherche mit Google Docs”, haben wir dir gezeigt, wie du mit einem einfachen Spreadsheet mehrere 100 relevante Suchbegriffe findest. Im zweiten Teil zeigen wir dir nun zwei Möglichkeiten, mit denen du das Suchvolumen, den CPC sowie die AdWords Competition zu jedem einzelnen Keyword in das Spreadsheet importierst. Am Ende des jeweiligen Abschnitts findest du den Link zu einem fertigen Google Doc. Um es im vollen Umfang nutzen zu können, ist es nötig, dass du es in deinen eigenen Google Drive kopierst. Öffne dazu die Datei und klicke auf “Datei” > “Kopie erstellen”.

Möglichkeit 1: Kostenlos, aber mit etwas Handarbeit

Wer das Suchvolumen zu einem Suchbegriff wissen möchte, der öffnet in der Regel Google AdWords und startet mit dem Keyword-Planer eine Abfrage. AdWords erlaubt es allerdings auch, sämtliche Daten für eine Liste von Keywords abzufragen (siehe Abbildung 1) und dann als CSV-Datei herunterzuladen.

Google AdWords Keyword-Planer

Abbildung 1: Suchvolumen für eine Liste von Keywords abrufen

Liste aller gefunden Keywords erstellen

Um diesen Arbeitsschritt etwas zu vereinfachen, fügen wir zunächst ein weiteres Tabellenblatt zu unserem Spreadsheet hinzu und nennen es “RawData”. Die erste Zeile des neuen Tabellenblatts dient als Bezeichnung der Spalten. Spalte “A” dient als Gesamtliste für alle gefunden Keywords. Schreibe deshalb bitte in die Zelle “A1” “Keyword”.

Ziel ist es nun, alle gefunden Keywords in einer großen Liste darzustellen, um sie so einfach zu kopieren und in das entsprechende Feld des Google AdWords Keyword-Planer einfügen zu können. Dafür schreiben wir in die Zelle “A2” folgende Formel:

=IF(ISNA('Keyword Recherche'!A1);"";'Keyword Recherche'!A1)

Wie man sieht, holen wir uns einfach die gefunden Keywords aus dem ersten Tabellenblatt, das uns als “User Interface” dient. Hierbei prüfen wir allerdings, ob überhaupt relevante Suchbegriffe gefunden wurden. Durch die IF-Abfrage “ISNA()”, verhindern wir, dass eine Fehlermeldung in unserer Gesamtliste auftaucht.

Nun kann diese Formeln einfach nach unten kopiert werden, allerdings nur bis zur Zelle “A11”. In Zelle “A12” müssen wir den Bezug anpassen, da wir sonst lediglich den Buchstaben “B” aus unserem ersten Tabellenblatt übernehmen. Diesen Schritt wiederholen wir nun so lange, bis wir alle Keywords aus dem ersten Tabellenblatt in die Gesamtliste übertragen haben.

Das Ergebnis kann nun einfach kopiert und das dafür vorgesehen Feld des Keyword-Planers eingefügt werden.

Suchvolumen abrufen und Ergebnis herunterladen

Abbildung 2: Download der Keyword-Daten als CSV-Datei

Abbildung 2: Download der Keyword-Daten als CSV-Datei

Nachdem das Dokument das Suchvolumen für die Keywords abgerufen hat, können sämtliche Daten als CSV-Datei heruntergeladen werden (siehe Abbildung 2). Die CSV-Datei kannst du nun in mit einem einfachen Text-Editor öffnen und den gesamten Inhalt kopieren.

Setze den Cursor nun in die Zelle “B1” und drücke “STRG”+”V”. Alle Daten werden nun in der Tabelle angezeigt und du kannst sie nach Bedarf filtern und sortieren. Leider werden die Daten nicht in der Reihenfolge heruntergeladen, in der du sie abgefragt hast. Um sie trotzdem auf dem ersten Tabellenblatt anzeigen lassen zu können, solltest du mit der Funktion “VLOOKUP” arbeiten. In der Beispiel-Datei haben wir diese Formel eingefügt.

Fertiges Google Spreadsheet öffnen

Möglichkeit 2: Die einfache Art und Weise voll automatisiert

Grundsätzlich soll ein Tool einen Prozess erleichtern oder im besten Fall komplett übernehmen. Das Abfragen der Keyword-Daten mit Möglichkeit 1 ist relativ umständlich. Deshalb zeigen wir mit der zweiten Möglichkeit nun, wie man den gesamten Prozess automatisiert und die relevanten Keyword-Daten in die Liste der Keyword-Recherche importiert.

Daten-Quelle

Um die Daten zu den einzelnen Schlüsselwörtern zu erhalten, brauchen wir eine Quelle. In diesem Fall greifen wir eine so genannte API-Schnittstelle zurück. Google bietet für sein Produkt AdWords eine solche Schnittstelle an. Allerdings ist sie für Werbetreibende gedacht und nicht für die Suchmaschinenoptimierung. Dies bedeutet, dass wir für dieses Tool nur sehr schwer einen Zugang zur Google AdWords API erhalten.

Drittanbieter haben allerdings den Bedarf erkannt und bieten Schnittstellen, um die Keyword-Daten auch für den Bereich der Suchmaschinenoptimierung nutzen zu können. In diesem Tutorial werden wir die Schnittstelle des Anbieters APIMetrics verwenden. Das Abfragen der Keyword-Daten kostet hier aktuell (Stand: 29. Oktober 2014) 0,0008 EUR pro Schlüsselwort. Somit kostet die Abfrage der Daten, zu beispielsweise 250 Keywords, 0,20 EUR. Jeder muss für sich selbst entscheiden, ob ihm die Arbeitsersparnis soviel wert ist. Eine kostenlose und automatisierte Lösung ist derzeit leider nicht bekannt.

Spreadsheet vorbereiten

Bevor wir mit den Formeln beginnen, muss das Spreadsheet wieder entsprechend vorbereitet werden. Hierzu ist es zunächst nötig, die Gesamtliste der gefunden Keywords, wie bei Möglichkeit 1 gezeigt, zu erstellen. Diese Gesamtliste sollte ebenfalls in einem neuen Tabellenblatt hinterlegt werden, welches die Bezeichnung “RawData” erhält.

Abbildung 3: Spreadsheet vorbereiten

Abbildung 3: Spreadsheet vorbereiten

Wie in Abbildung 3 zu sehen ist, brauchen wir nun zwei neue Felder. Schreibe dazu in das Feld “F4” die Bezeichnung “API-User” und das Feld “F5” “API-Key”. Der Zugriff auf eine API-Schnittstelle ist in den meisten Fällen durch einen User- und einem Key-Schlüssel geschützt, was man mit Benutzernamen und Passwort gleichsetzen kann.

Um den Zugriff auf APIMetrics zunächst ausprobieren zu können, solltest du dort jeweils die Wörter “public” einfügen. Dies erlaubt es dir auch ohne Benutzerkonto, einige Abfragen am Tag zu machen. Wenn du bereits ein Benutzerkonto hast, kannst du in die Felder G4 und G5 die entsprechenden Daten eintragen.

Tabellenblatt “RawData” vorbereiten

Abbildung 4: Headlines des Tabellenblatts “RawData”

Abbildung 4: Headlines des Tabellenblatts “RawData”

Das neu hinzugefügte Tabellenblatt “RawData” verfügt bereits über die Gesamtliste aller Keywords. Nun müssen die Bezeichnungen der Spalten wie in Abbildung 4 eingefügt werden. Bitte achte darauf, die identische Reihenfolge zu verwenden. Im Folgenden nochmals alle Spaltenüberschriften dieses Tabellenblatts in Reihenfolge:

  1. Keywords
  2. Abfrage
  3. CPC
  4. Suchvolumen
  5. Competition
  6. Date Query
  7. Date Last SERP
  8. Categories

Google Docs um eine Funktion erweitern

Im Teil 1 unseres Tutorials haben wir dir gezeigt, wie du eine XML-Datei mit der Funktion “ImportXML” in dein Spreadsheet importierst. Nun ist es so, dass beinahe alle Schnittstellen-Anbieter das Format JSON verwenden um das Ergebnis von Abfragen auszugeben. Leider bietet Google Docs keine fertige Funktion, mit der du eine JSON-Datei importieren kannst.

Tutorial zu Ende? Nein – wir erweitern ganz einfach unser Spreadsheet um eine eigene Funktion, mit der genau das möglich ist. Paul Gambill beschreibt in einem Artikel auf Medium.com, wie man eine eigene “ImportJSON”-Funktion in sein Google Spreadsheet einbaut. Wir gehen dazu wie folgt vor:

  1. Klicke im Menü auf “Tools” > “Skripteditor”
  2. Wähle nun: “Create Script for Spreadsheet”
  3. Lösche den gesamten Inhalt der Beispiel-Datei
  4. Füge dafür dieses gesamte Script ein: ImportJSON auf GitHub
  5. Benenne die Datei Code.gs um in “ImportJSON.gs”
  6. Klicke auf speichern und schließe den Skripteditor wieder

Klickst du jetzt in eine beliebige Zelle und schreibst dort “=Import”, dann siehst du bereits die Funktion “ImportJSON” in der Auswahlliste (siehe Abbildung 5).

Abbildung 5: Funktion ImportJSON in der Auswahlliste

Abbildung 5: Funktion ImportJSON in der Auswahlliste

Jetzt geht es ans Eingemachte: Die Formeln

Unser beiden Tabellenblätter sind nun fertig vorbereitet. Da die Abfrage der Keyword-Daten nicht kostenlos ist, fügen wir uns noch ein kleines Hilfsmittel ein, mit dem wir bestimmen können, welche Daten genau abgefragt werden sollen. Dazu nutzen wir die Spalte E unseres ersten Tabellenblatts, dem User-Interface.

Abbildung 6: Spalte E dient als Hilfsmittel

Abbildung 6: Spalte E dient als Hilfsmittel

Unsere Formel setzen wir so um, dass nur Daten aus der Schnittstelle abgefragt werden, wenn ein “X” in der Zeile des entsprechenden Keywords steht. Möchtest du immer alle Keyword-Daten abfragen, dann schreibe einfach hinter jedes Keyword in die Spalte “E” ein “X” und speichere die Datei so ab.

Das Ergebnis unserer Abfrage im JSON-Format

In Abbildung 7 siehst du, wie das Ergebnis einer Abfrage im Rohformat aussieht. Wie du erkennen kannst, ist das Format auch der Grund, warum wir im Tabellenblatt “RawData” die Bezeichnung der Spalten in der vorgegeben Reihenfolge vornehmen sollten.

Abbildung 7: Ergebnis im JSON-Format

Abbildung 7: Ergebnis im JSON-Format

Wir starten nun mit der ersten Formel. Diese wird im Tabellenblatt “RawData” in die Zelle “B2” eingetragen.

=IF(AND(ISERROR(A2) = FALSE;'Keyword Recherche'!E3 = "X");ImportJSON("http://www.apimetrics.de/api/google?user="&'Keyword Recherche'!G4&"&password="&'Keyword Recherche'!G5&"&service=keywordinfo&keyword="&A2&""; "/"; "noHeaders");"Nicht abfragen")

Was genau passiert hier?

Im ersten Schritt fragen wir ab, ob überhaupt ein Keyword-Vorschlag gefunden wurde. Ist dies der Fall, fragen wir weiterhin ab, ob in der entsprechenden Zeile des Keywords in der Spalte “E” auf dem ersten Tabellenblatt, ein “X” eingetragen ist. Sind beide Abfragen wahr, wird eine Anfrage an die Schnittstelle gestellt, indem die ImportJSON-Funktion genutzt wird.

Die ImportJSON-Funktion benötigt zunächst die URL, an die die Anfrage gesendet wird. In der URL werden auch die Zugangsdaten zur Schnittstelle benötigt, welche wir im ersten Tabellenblatt hinterlegt haben. API-User und API-Key fügen wir deshalb aus den entsprechenden Zellen in die URL ein. Am Ende der Anfrage-URL hängst du das Keyword an, zu dem die Daten benötigt werden.

Um der ImportJSON-Funktion zu sagen, dass sie den gesamten Inhalt der JSON-Datei importieren soll, weisen wir hier den Parameter “/” zu. Außerdem übergeben wir noch den Wert “noHeaders”, was dazu führt, dass die Bezeichnungen aus der JSON-Datei nicht übernommen, sondern nur die Daten importiert werden.

Wurden alle vorherigen Schritte richtig ausgeführt und die Formel eingefügt, sollten nun bereits die Daten automatisch importiert sein (siehe Abbildung 8).

Abbildung 8: Die erste erfolgreiche Abfrage an die Schnittstelle

Abbildung 8: Die erste erfolgreiche Abfrage an die Schnittstelle

Die Formel kannst du nun nach unten kopieren und für alle gefunden Keywords anwenden.

Daten in das User-Interface (Tabellenblatt 1) übernehmen

Um die abgefragten Daten nun in unser User-Interface zu übernehmen, fügen wir in Zelle “B3” im Tabellenblatt 1 folgende Formel ein:

=IF(E3="X";RawData!D2;"")

Wir fragen zunächst ab, ob in der Spalte “E” ein “X” steht. Ist dies der Fall, werden die Daten aus dem Tabellenblatt “RawData” importiert. Wenn nicht, wird kein Inhalt angezeigt. Diese Formel solltest du nun nach rechts für “CPC” und “Wettbewerb” kopieren und dann weiter nach unten.

Ein kleiner Hinweis: Der Anbieter APIMetrics ist noch relativ jung und hat deshalb nicht immer sämtliche Keyword-Daten sofort parat. Findet die Formel keine Daten zu einem Keyword, entstehen auch keine Kosten. In der Regel dauert es dann allerdings auch nur wenige Minuten, bis auch diese Keyword-Daten vorhanden sind. Wer also dann die Abfrage einfach erneut startet, hat wirklich alle Daten.

Fertiges Google Spreadsheet öffnen

(In der Vorlage werden mit Absicht lediglich die Daten eines Keywords abgefragt, da sonst realtiv schnell die “public”-Anfragen an die APIMetrics-Schnittstelle ausgeschöpft wären.)

Herzlichen Glückwunsch, dein eigenes Keyword-Recherche Tool ist fertig

Wir sind am Ende unseres Tutorials angelangt. Du bist nun im Besitz eines eigenen Keyword-Recherche Tools, welches dir sehr viel Arbeit und Zeit ersparen kann. Vielleicht hat es dich auch auf eigene Ideen gebracht, wie man dieses Tool erweitern kann, oder welche Tools man sich noch mit Google Docs noch bauen kann.

Wenn du Fragen zum Tutorial oder zum Tool hast, oder uns Feedback geben möchtest, nutze bitte die Kommentar-Funktion. Wir freuen uns!

14 Gedanken zu „Teil 2: Tutorial Keyword-Recherche mit Google Docs – Abfrage der Keyword-Daten

  1. Josha

    Hallo Christoph,
    ich erhalte ebenfalls den Fehler: SyntaxError: Unexpected token: Q (Zeile 132).
    Ich habe mir einen Account bei APImetrics eingerichtet und sowohl User als auch PW eingetragen.
    Kannst du mir bitte weiterhelfen?

    Antworten
  2. Daniel

    Hi,
    vielen Dank für die Anleitung. Habe mir dazu mal ein kleines PHP-Script geschrieben. Ich finde jedoch keine wirkliche Dokumentation für das API. Kann man, wie beim Keywordplanner auch, Eingrenzungen wie „Land: Deutschland“ oder „Sprache: Deutsch“ in die Abfrage schicken? Aktuell habe ich zumindest keine Anhaltspunkte, worauf sich die Ergebniszahlen beziehen.

    Viele Grüße,
    Daniel

    Antworten
  3. heyjoeman

    Hi,

    super Tutorial. Habe auch dieses Problem mit „Fehler: SyntaxError: Unexpected token: Q (Zeile 132).“
    Bei mir ist es Spalte Q.
    Ein Testaccount bei APImetrics ist eingerichtet.
    Passe auch die Zellen in der Formel manuell an, und bekomme diesen Fehler.

    Was kann es sein ?

    Antworten
  4. Marc

    Hi Chrsitoph,

    ein riesen Kompliment von meiner Seite für dein Tutorial. Echt klasse! Vielleicht möchtest du noch ein wenig dran feilen? Ich bin nicht besonders fit in diesen Dingen, daher ist es mir aufgefallen. Vorschläge:

    – Möglichkeit 1 hört etwas plötzlich auf. Es wäre noch gut die Spalten C und D im User Interface zu beschreiben. Wichtig der Tipp in der VLOOKUP das mittlere Attribut (3) durch eine „5“ in Spalte C und durch eine „4“ in Spalte D zu ersetzen.

    – Das Beispiel Sheet (Keyword Recherche) addressiert in den Zellen B3, C3 und D3 die Zelle A$3 als absoluten Wert. Kopiert man diese Zeile nach unten, wird immer A3 angesprochen, was ja nicht sein soll. Der Rechner soll ja immer den nächsten Wert in der Spalte A nutzen und in den RawData suchen. Dazu muss nur das absolutierende „$“ gelöscht werden. Dann lässt sich alles nach unten durchziehen.

    – Wer Möglichkeit 1 ein wenig schicker haben möchte und – so wie ich – nicht besonders auf „#N/A“ steht, wenn es einen Quellwert nicht geben sollte, der kann es sich leicht machen und folgende Formel verwenden (Achtung, immer an die entsprechende Spalte anpassen. D.h. die 3 in der Mitte für das Suchvolumen in Spalte B, ein 5 für den CPC in Spalte C und eine 4 für den Wettbewerb in Spalte D. Allerdings habe ich keine Ahnung, wieso das so ist):

    =IF(ISNA(VLOOKUP(A3;RawData!C$1:K$261;3;FALSE));““;(VLOOKUP(A3;RawData!C$1:K$261;3;FALSE)))

    Nochmals vielen Dank, Chrsitop! Wäre wirklich super, wenn ich auch Bescheid bekommen könnte, wenn du wieder etwas veröffentlichst. Mir wird das Sheet sicherlich sehr viel Arbeit abnehmen.

    VG Marc

    Antworten
  5. tom

    Ich habe nur #ERROR! bei RAWData. Nur B2 +B3 funktioniert.
    In B4 steht eigentlich das gleiche analog drin:

    =IF(AND(ISERROR(A4) = FALSE;’Keyword Recherche‘!E5 = „X“);ImportJSON(„http://www.apimetrics.de/api/google?user=“&’Keyword Recherche‘!G4&“&password=“&’Keyword Recherche‘!G5&“&service=keywordinfo&keyword=“&A4&““; „/“; „noHeaders“);“Nicht abfragen“)

    Eine Idee?

    Antworten
    1. Christoph PaterokChristoph Paterok Artikelautor

      Hallo Tom,

      grundsätzlich könnte es daran liegen, dass API-User und API-Key noch „public“ sind. Damit sind nur wenige abfragen möglich, ist auch nur zum Testen gedacht.

      Allerdings kann ich mir das gerne mal anschauen. Schreib mir doch einfach mal eine E-Mail an paterok [at] gmail.com.

      Viele Grüße
      Christoph

      Antworten
  6. Daniel

    Super Eintrag. Bei mir hat alles gleich funktioniert. Nun würde mich es nur noch interessieren wie man es denn nun mit AdWords verbinden kann. Das wäre der Hammer.

    Antworten
    1. Christoph PaterokChristoph Paterok Artikelautor

      Hallo Daniel,

      freut mich wenn es Dir gefällt – Danke für Dein Feedback.

      Ich mach mir mal ein paar Gedanken, wie das funktionieren könnte und werde ggf. noch ein Tutorial dazu veröffentlichen :)

      Viele Grüße

      Christoph

      Antworten
      1. Daniel

        Super. Wenn du eine Lösung gefunden haben solltest. wäre es nett wenn du mich auch persönlich Benachrichtigen könntest. Da ich daran sehr interessiert bin.

        Antworten
  7. Ralf Seybold

    … Außerdem… Verändert man die Zeile so, kann man sie ohne Probleme nach unten kopieren…

    =IF(AND(ISERROR(A2) = FALSE;’Keyword Recherche‘!E3 = „X“);ImportJSON(„http://www.apimetrics.de/api/google?user=“&’Keyword Recherche‘!$G$4&“&password=“&’Keyword Recherche‘!$G$5&“&service=keywordinfo&keyword=“&A2&““; „/“; „noHeaders“);“Nicht abfragen“)

    Antworten
    1. Christoph PaterokChristoph Paterok Artikelautor

      Hallo Ralf,
      wie im Tutorial beschrieben, funktionieren der API-User und der API-Key „public“ nur begrenzt. Es kann sein, dass Du die Datei aufgerufen hast, während noch einige andere User sie geöffnet hatten. Habe alles gerade getestet – es funktioniert.

      Antworten

Schreibe einen Kommentar

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