Keywordrecherche und Analyse in Excel: Stichwortgruppierung

Warum ist Keyword-Gruppierung so wichtig?

Wenn Sie Keyword-Recherchen für SEO durchführen ist das Ergebnis dieser Recherche eine Keyword-Map, die im wesentlichen eine Liste von Keywords und deren Suchvolumen ist, die nach bestimmten Interessengebieten gruppiert sind. Natürlich gibt es immer mehrere vordefinierte Themen, aber die wichtigsten Themen werden in der Regel während der Recherche selbst entdeckt. Und deshalb brauchen wir Gruppierung – um Themen mit den besten Kombinationen von Keywords und ihrem jeweiligen Suchvolumen zu definieren. Denken Sie an die folgenden Gruppierungsbeispiele: Marken-Keywords, Lokalität (Stadt, Region), Jahr, Transaktion, Information.

Vorbereitung und Bereinigung der Daten

Bei allen Aufgaben im Zusammenhang mit großen Datenmengen ist das wichtigste richtig aufbereitete und saubere Daten. Wir haben bereits mehrere Tabellen mit verschiedenen Schlüsselwörtern. Nachdem wir sie in einer einzigen Liste mit den Spalten Keyword, Search Volume und Competition zusammengefasst haben, gehen wir wie folgt vor: 

1. Machen Sie aus dieser Liste eine „intelligente Tabelle“, indem Sie auf eine beliebige Zelle in Ihrer Liste klicken und STRG + T auf Ihrer Tastatur drücken (oder gehen Sie zum oberen Menü Einfügen -> Tabelle). 

Sie werden sehen, dass Microsoft-Excel automatisch die gesamte Liste erkannt und alle erforderlichen Zellen ausgewählt hat. Wenn dies nicht passiert ist, wählen Sie manuell den gesamten Bereich aus und wiederholen Sie dann die Tabellenerstellung.

2. Entfernen Sie doppelte Keywords aus Ihrer Tabelle. Klicken Sie auf eine beliebige Zelle in Ihrer Tabelle und gehen Sie zum oberen Menü Daten -> Duplikate entfernen.

Deaktivieren Sie im neuen Popup-Fenster alle Spalten, indem Sie auf die Schaltfläche „Alle abwählen“ klicken. Wählen Sie dann nur die Schlüsselwortspalte aus und drücken Sie auf „Ok“.

Grundlegende Gruppierung: Wortzahl und Suchvolumen

Jetzt können Sie mit der Gruppierung Ihrer Keywords beginnen. Lassen Sie uns eine grundlegende Gruppierung vornehmen: nach der Anzahl der Wörter in einer Phrase und nach Suchvolumen.

Da es keine Standardformel zum Zählen von Wörtern in einer einzelnen Zelle gibt, berechnen wir  jetzt die Anzahl der Leerzeichen zwischen den Wörtern (falls vorhanden).

Hier ist eine Formel, um das zu tun:

=LÄNGE([@Keyword])-LÄNGE(WECHSELN([@Keyword],“ „,““))+1

Eine Gruppierung nach Suchvolumen ist mit einer Reihe von verschachtelten IF-Anweisungen möglich. Wir müssen nur die Bucket-Größe definieren. Lassen Sie uns das folgenden Buckets/Gruppen definieren:

• Unter 100
• 100 – 499
• 500 – 999
• 1000 +

Hier ist eine Formel für diese Art der Gruppierung:

=WENN([@[Search Volume]]<100,“0-100″,WENN([@[Search Volume]]<500,“100-499″,WENN([@[Search Volume]]<1000,“500-999″,“1000+“)))

Beachten Sie, dass bei verschachtelten IF-Anweisungen die Reihenfolge der Formeln wichtig ist: Excel berechnet solche Formeln von links nach rechts. Das heißt, dass die allererste Bedingung zuerst geprüft wird, wenn die Bedingung nicht erfüllt ist. Dann wird die  zweite Bedingung geprüft, dann die dritte und so weiter. Wenn wir in unserem Fall die Bedingung „<1000“ an erster Stelle setzen, würden wir die ersten beiden Gruppen nicht sehen.

Erweiterte Gruppierung: Verwenden zusätzliche Tabellen zum Automatisieren von Formeln

Das Beispiel mit verschachtelten IF-Anweisungen ist tatsächlich dasjenige, das wir jetzt ausbauen werden, um die erweiterte Gruppierung durchzuführen. Wir werden auch die Total Row-Funktionen unserer „Smart Tables“ verwenden.

Werfen wir einen kurzen Blick darauf, was eine Gesamtzeile eines „Smart-Table“ ist.

 

Total Row bietet, wenn aktiviert, eine Reihe von Berechnungen, die für jede Spalte durchgeführt werden können: Zeilen zählen, Zahlen summieren, Minimal- oder Maximalwert anzeigen usw. Es ist auch möglich, andere integrierte Excel-Funktionen zu verwenden. Das Beste an der Total Row ist, dass sie unabhängig von der Größe der „Smart Table“ funktioniert, das heißt egal ob 5 Zeilen oder 5000 Zeilen, sie führt die gleiche Berechnung aus. Da wir das alles jetzt wissen, werden wir die Gesamtzeile verwenden, um schnell mehrere IF-Bedingungen für unsere erweiterte Gruppierung mit der Funktion REPT (Wiederholung) zu erstellen.

Sehen Sie sich das Beispiel unten an.

Hier sehen Sie die Tabelle mit zwei Spalten: Lookup und Formula. Die erste Spalte dient nur der manuellen Eingabe. Die Spalte Formel erstellt automatisch eine IF-Bedingung, die wir weiter verwenden werden. Es nimmt einfach den Wert der Lookup-Spalte und verkettet ihn mit anderen Textelementen, um daraus eine Formelzeichenfolge zu erstellen.  Mit anderen Worten,  die Ergebnisse der Formeln in dieser Spalte sind andere Formeln (von Excel vorerst als Textzeichenfolgen wahrgenommen).

Hier ist die Formel, die Werte nimmt und sie verkettet:

=“WENN(ISTZAHL(SUCHEN(„““&WECHSELN([@Lookup],“ „,“*“)&““““&“,[@Keyword]“&“)),“““&[@Lookup]&“““,“

Und hier ist das Ergebnis dieser Formel:

WENN(ISTZAHL(SUCHEN(„seo“,[@Keyword])),“seo“,

Sie können jetzt in dieser Spalte für jede Zeile eine Reihe von IF-Bedingungen sehen. Und ja, es liegt kein Fehler vor – die schließenden Klammern wurden absichtlich übersprungen. Aber wie vereinen wir all das in einer einzigen Formel? Nun, die Magie passiert in der Summenzeile, in der wir die folgende Formel verwenden, um alle IF-Bedingungen in einer einzigen langen Zeichenfolge zu verketten:

=TEXTKETTE(„=“,[Formula],FALSCH,WIEDERHOLEN(„)“,ANZAHL2([Formula])))

Diese Formel nimmt alle Werte aus der Formelspalte und verkettet sie zu einer einzigen Zeichenfolge.  Es fügt auch ein „=“-Symbol und schließende Klammern hinzu, sodass die resultierende Zeichenfolge genau so aussieht, wie eine Excel-Formel aussehen sollte.

Es gibt jedoch zwei Einschränkungen in Excel, die Sie kennen müssen.

Die erste ist, dass die maximale Anzahl verschachtelter IF-Anweisungen in einer Formel 64 beträgt, was bedeutet, dass wir für eine einzelne Spalte mit Schlüsselwortgruppierungen nicht mehr als 64 Schlüsselwörter verwenden dürfen. Deshalb würde man beispielsweise bei einer Keyword-Analyse mit geografischer Gruppierung nach Städten in Deutschland mit mehr als 100.000 Einwohnern zwei solcher Spalten benötigen.

Die zweite Einschränkung ist die Formellänge von Excel. Sie darf 8.192 Zeichen nicht überschreiten. Diese Einschränkung hatte nie Auswirkungen auf meine komplexesten Formeln, aber sie existiert trotzdem.

Gruppierungsregeln implementieren

Ok, was machen wir jetzt mit all diesen Spalten, Zeichen und Formeln?

• Kopieren Sie die resultierende Zelle in die Formelspalte in unsere Summenzeile.
• Wechseln Sie zurück zu unserer Keyword-Tabelle und fügen Sie kopierte Daten als „Nur Werte“ ein (drücken Sie dazu STRG+ALT+V und wählen Sie „Werte“ im Menü).

• Drücken Sie F2, um die Zelle einzugeben, und drücken Sie dann die Eingabetaste.

Danach wird die Formel nicht mehr als Textstring, sondern als eigentliche Formel wahrgenommen. Deshalb wird es sofort funktionieren und Sie sehen die Ergebnisse der Gruppierung, die durch die von uns verwendeten Bedingungen definiert sind.

Hier ist ein Beispiel dafür, wie eine Tabelle mit mehreren Gruppierungsbedingungen aussehen kann:

Wenn Sie diesen Trick kennen, können Sie das Gruppieren von Keywords viel einfacher machen. Erstellen Sie einfach mehrere Kopien solcher „intelligenten Tabellen“ – eine für jeden Gruppierungstyp und geben Sie einfach Textzeichenfolgen ein, von denen Sie annehmen, dass sie in Ihren Schlüsselwörtern vorkommen. Kopiere Sie dann die resultierenden Formeln aus der Summenzeile in Ihre Keyword-Tabelle und genießen Sie!

Bonus

Um Ihnen das Leben noch einfacher zu machen, haben wir eine herunterladbare Excel-Datei mit Gruppierungstabellenvorlagen erstellt, die Sie für Ihre Keyword-Recherche verwenden können.

Die Datei enthält eine verbesserte Gruppierungstabelle mit einer zusätzlichen Themenspalte, die hilft, mehrere verwandte Unterthemen in einer einzigen Gruppierungsregel zu definieren (wenn Sie beispielsweise verschiedene BMW-Automodelle in einer einzigen Gruppe mit Untergruppen wie „X3“, „X5“ gruppieren möchten, „i3“, usw.).