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

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.

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

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.

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

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).

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.

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.

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).

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!