Wie sehr beeinflusst das Ranking eines Keywords den Traffic, der über das Keyword kommt? Um Zusammenhänge zu erkennen kann eine gemeinsame Betrachtung und grafische Darstellung der Daten sinnvoll sein. In diesem Use Case zeigen wir euch, wie ihr die SEOlytics-Daten mit denen aus Google Analytics verknüpft. Ziel ist es, zu einem bestimmten Keyword ein Excel-Diagramm zu erstellen, das die Traffic- und Rankingdaten des Keywords miteinander in Relation setzt.

Korrelationsdiagramm

Um die Daten komfortabel in Excel zu integrieren, nutzen wir das Excel Add-in “SeoTools for Excel” von Nils Bosma, das wir euch ja bereits in zwei Posts vorstellten:

Im Folgenden zeigen wir euch nun Schritt für Schritt und anhand von zahlreichen Screenshots, wie ihr ein Korrelationsdiagramm mit Traffic- und Ranking-Daten erstellt.

Voraussetzung dafür ist natürlich, dass ihr das Excel Add-in heruntergeladen habt und einen Google Analytics Account sowie einen SEOlytics Account mit API-Key besitzt. SEOlytics ELITE-Nutzer können jederzeit ohne Aufpreis einen API-Key erhalten. Weitere Details dazu findet ihr auch in unserer API Dokumentation.

 

Google Analytics Daten importieren

Im ersten Schritt erstellen wir in einem neuen Excel-Sheet eine kleine Tabelle mit der  Google Analytics Nummer, der Domain für die wir den Traffic abrufen möchten sowie das zu untersuchende Keyword. Außerdem fügen wir noch den Zeitraum hinzu, für den wir die Daten erheben möchten. In unserem Beispiel wollen wir zum Keyword „Taufe“ die Daten aus einem Zeitraum von 8 Wochen (= 56 Tage), beginnend am 26.04.2012, erfassen.

Tabelle anlegen

Danach möchten wir die Daten aus Google Analytics mittels „SeoTools for Excel“ importieren. Daher wechseln wir – wenn nicht schon geschehen – in den Excel-Tab „SEO-Tools“ und rufen über den Button „Analytics“ die Eingabemaske auf (siehe auch nächsten Screenshot). Nach Eingabe von Benutzername und Passwort muss dort die Domain ausgesucht werden, von der die Daten abgerufen werden sollen. Als nächstes wählen wir die „Metric“ aus, die in das Diagramm einfließen soll. In unserem Beispiel möchten wir die Besuche als Messgröße für den Traffic heranziehen und wählen diese unter „Session->Visits“ aus.

Nachdem das Start- und Enddatum des Erhebungszeitraums eingetragen ist kann man sich unter „Dimensions“ entscheiden, in welchem Intervall Daten importiert werden sollen. Da wir einen täglichen Datenpunkt erzeugen möchten, entscheiden wir uns für „Time->Date„.

Für unseren Use Case ist zudem nur der Traffic aus dem organischen Suchmaschinenbereich interessant. Deshalb wählen wir im nächsten Punkt das“Segment“ „Non paid search traffic„.

Der „Filter“ wird manuell definiert. Da wir die Daten zu einem bestimmten Keyword erheben möchten lautet er in unserem Fall „ga:keyword==platzhalter“. Wobei „platzhalter“ durch das jeweilige Keyword ersetzt werden muss und in unserem Beispiel „taufe“ heißt.

Als Letztes passen wir in dieser Maske noch in „Max results“ die Anzahl der Tage an, für die wir ein Ergebnis haben möchten.

Kleiner Tipp: beim Mouseover der Eingabefelder erscheinen auch kurze Erklärungen zum jeweiligen Feld.

Analytics Daten abrufen

Mit dem Mausklick auf „Preview result“ erhält man eine Vorschau der Ergebnisse und kann die Parameter somit überprüfen und gegebenenfalls noch einmal anpassen.

Damit in Excel auch die Formel hinterlegt wird, muss „Dump results“ markiert sein. Wir schließen die Eingabe mit „Insert formula“ ab und umgehend fließen die Daten in die Excel ein. Die Formel, die in der Zelle zum Datenimport hinterlegt ist, wird aber im nächsten Schritt noch manuell angepasst.

 

Analytics Formel anpassen

Bevor wir alle Werte in der Formel manuell anpassen können, benötigen wir in einem Zwischenschritt noch einen Hilfswert. Wie im nächsten Screenshot zu sehen ist, legen wir dafür eine kleine separate Tabelle an. Der Hilfswert selber (im Beispiel Zelle E2) setzt sich zusammen aus der Spaltenüberschrift „ga:keyword==“ und dem konkreten Keyword, für das wir die Daten importieren möchten (Zelle B4). Die verwendete Formel lautet also =E1&B4

Hilfswert erstellen

Jetzt nehmen wir uns die hinterlegte Formel (ga:date) vor und passen diese manuell an. Einmal korrekt angelegt, generieren sich zukünftig Traffic-Daten immer automatisch neu, wenn wir in unserer oben erstellten Tabelle die Werte ändern.

In der Formel ersetzen wir daher die hinterlegten Werte durch die entsprechenden Zellen in unseren Tabellen. Die fertige Formal lautet dann:

=Dump(GoogleAnalytics(B2;“ga:visits“;B7;B5;“ga:date“;“gaid::-5″;E2;““;1;B6;FALSCH;FALSCH))

Analytics Formel anpassen

Daraufhin werden die Traffic-Daten in Excel importiert.

Zur besseren Lesbarkeit passen wir nun auch das importierte Datumsformat an. In der leeren Spalte vor dem Analytics Datum generieren wir ein neues im bekannten Format mit der folgenden Formel =DATUM(LINKS(B11;4);TEIL(B11;5;2);RECHTS(B11;2)) Wobei „B11“ die Zelle vor dem neuen Datum bezeichnet. Dies ändern wir in der gesamten Spalte vor dem Datum.

Datumsformat editieren

 

SEOlytics Daten importieren

Jetzt möchten wir über „SeoTools for Excel“ die Rankingdaten unseres Keywords aus SEOlytics abfragen. Also wechseln wir gegebenenfalls wieder auf den Excel-Tab „SEO-Tools“ und rufen über den Button „SEOlytics“ die Eingabemaske zu „SEOlyticsMonitoredKeywordRanking“ auf.

Hier die entsprechende Domain und das Keyword auswählen. In den „Fields“ markieren wir „date“ und „position„. Achtet wieder darauf, dass „Dump“ markiert ist, damit in Excel die Formel hinterlegt wird.

SEOlytics Daten abrufen

Nachdem die Daten aus SEOlytics eingeflossen sind, möchten wir auch hier wieder die hinterlegte Formel anpassen, damit die Daten zukünftig automatisch importiert werden. Wie bei Google Analytics müssen wir einen kleinen Hilfswert konstruieren, der hier den Wert „Anzahl der Tage+1“ (also in unserem Beispiel B6+1) besitzt.

Hilfswert 2 erstellen

 

SEOlytics Formel anpassen

Da wir jetzt alle Werte beisammen haben, passen wir die SEOlytics Formel manuell an. Wir ersetzen die Werte durch die entsprechenden Zellen unserer Tabelle. Im Anschluss fließen die Rankings in die Tabelle ein und werden jedes Mal automatisch aktualisiert, sobald wir einen der verwendeten Werte variieren. Die angepasste Formel lautet:

=Dump(SEOlyticsMonitoredKeywordRanking(B3;“de_de“;B4;B5;E3;“google“;17;FALSCH))

SEOlytics Formel anpassen

Wir haben die Daten aus SEOlytics bewusst etwas abseits der Analytics-Tabelle einfließen lassen. Mit der SVERWEIS Formel setzen wir die Rankings nun neben die Visits. Damit  werden sie auch automatisch mit dem Datum in der richtigen Reihenfolge sortiert:

=SVERWEIS(A11;F:G;2;0)

Dies übernehmen wir in der gesamten Spalte und betiteln sie mit „Ranking“.

SVERWEIS Formel

 

Diagramm erstellen

Mit der Tabelle aus Datum, Visits aus Google Analytics und Rankings aus SEOlytics können wir jetzt einfach ein Diagramm in Excel erstellen. Am besten nutzt man dafür aber ein neues Excel-Sheet, denn wenn es in derselben Tabelle angelegt ist, verschwindet es bei jeder Änderung des Keywords o. ä.

Auf einem neuen Tabellenblatt übernehmen wir deshalb die relevanten Werte aus der Ursprungstabelle (in diesem Beispiel von „Tabelle1“): Datum, Visits und Ranking.

Diagramm Tabelle anlegen

Diese Tabelle nutzen wir nun als Datenquelle für unser Diagramm. Wie ihr das erstellt müssen wir euch bestimmt nicht erläutern – achtet dabei darauf, dass die Visits auf die Sekundärachse kommen. Fertig ist das Korrelationsdiagramm mit Ranking- und Traffic-Daten, das ihr ganz nach euren Wünschen gestalten könnt.

Korrelationsdiagramm

Beitrag weiterempfehlen

10. Juli 2012 | von Jens | 5

Zu diesem Beitrag können keine Kommentare mehr abgegeben werden.