Content-Gap-Analyse-Vorlage für Google Sheets

Erstellen Sie eine Content-Gap-Analyse in Google Sheets oder Excel mit fertigen Tabs, Spalten und Formeln, die jedes Keyword bewerten, für das Ihre Mitbewerber ranken und Sie nicht.

Nutzen Sie diese Vorlage, um einen Stapel Keyword-Exporte in eine funktionierende Gap-Analyse-Tabelle zu verwandeln. Jede Variante ist ein Tab der Arbeitsmappe, mit den genauen Spalten zum Hinzufügen und den Formeln, die das Abgleichen und Bewerten für Sie erledigen. Richten Sie die Tabs von links nach rechts ein, fügen Sie Ihre eigenen Exporte in die Eingabe-Tabs ein und lassen Sie dann die Formeln die Keywords hervorbringen, über die es sich zuerst zu schreiben lohnt. Alles funktioniert in Google Sheets und Excel gleich; nur ein paar Funktionsnamen unterscheiden sich, dort vermerkt, wo es zählt.

6 einsatzbereite Varianten

Setup- & Anleitungs-Tab

Beginnen Sie hier, um Ihre Mitbewerber zu benennen, Ihre Bewertungsschwellen zu setzen und zu definieren, was als Lücke gilt.

Ziel: Konfigurieren Sie die Arbeitsmappe einmal, damit jeder andere Tab aus einem einzigen Satz von Eingaben liest statt aus fest codierten Werten.

Auszufüllende Zellen

  • Ihre Domain: [yourdomain.com]
  • Mitbewerber-Domains 1–3: [competitor-a.com], [competitor-b.com], [competitor-c.com]
  • Ranking-Schwelle (Sie „decken" ein Keyword ab, wenn Sie in den Top ranken): [20]
  • Mindest-Monatsvolumen zum Behalten: [50]
  • Max. Keyword-Difficulty, die Sie anvisieren: [40]

Zu erstellende benannte Bereiche

  1. Wählen Sie die Schwellenzelle und benennen Sie sie rank_threshold, damit Formeln sie über den Namen referenzieren können.
  2. Tun Sie dasselbe für min_volume und max_kd.
  3. Fügen Sie eine einzeilige Notiz hinzu, die Ihr Team daran erinnert, aus welchem SEO-Tool die Exporte stammen, damit Aktualisierungen konsistent bleiben.

Tab „Ihre Seiten"

Importieren Sie jedes Keyword, für das Ihre Website bereits rankt, damit die Arbeitsmappe weiß, was Sie abdecken.

Ziel: einen sauberen Export Ihrer eigenen rankenden Keywords vorhalten, gegen den der Gap-Tab nachschlagen kann.

Spalten (A–E)

  • A – Keyword: [Keyword-Export einfügen]
  • B – Ihre Position: [aktuelles Ranking]
  • C – Ranking-URL: [Ihre URL]
  • D – Volumen: [monatliche Suchen]
  • E – Abgedeckt?: Formel unten

Formel für Spalte E

  1. Geben Sie in E2 =IF(B2<=rank_threshold,"Covered","Weak") ein und ziehen Sie sie nach unten.
  2. Die Formel markiert ein Keyword nur dann mit Covered, wenn Ihre Position die Schwelle aus dem Setup-Tab übertrifft.
  3. Fixieren Sie Zeile 1 und sortieren Sie absteigend nach Volumen, damit Ihre stärksten Begriffe oben stehen.

Tab „Mitbewerber-URLs"

Stapeln Sie die rankenden Keywords Ihrer Mitbewerber in eine Liste, die die Gap-Formel durchsuchen kann.

Ziel: den Keyword-Export jedes Mitbewerbers in einem einzigen Tab kombinieren, nach Quelle markiert, damit nichts isoliert analysiert wird.

Spalten (A–F)

  • A – Keyword: [Mitbewerber-Keyword]
  • B – Mitbewerber-Domain: [welcher Mitbewerber]
  • C – Ihre Position: [Ranking]
  • D – Ihre Ranking-URL: [Mitbewerber-URL]
  • E – Volumen: [monatliche Suchen]
  • F – Keyword-Difficulty: [KD]

So bauen Sie es auf

  1. Fügen Sie den Export jedes Mitbewerbers unter den letzten ein und halten Sie Spalte B auf die richtige Domain gesetzt.
  2. Fügen Sie =COUNTIF(B:B,[competitor-a.com]) über der Tabelle ein, um die Zeilenzahl pro Mitbewerber zu prüfen.
  3. Entfernen Sie offensichtliche Markenbegriffe und navigationale Suchanfragen vor der Bewertung; sie sind selten echte Lücken.

Tab Gap-Abgleich-Formel

Gleichen Sie die beiden Listen ab, damit die Tabelle markiert, welche Mitbewerber-Keywords Sie nicht abdecken.

Ziel: jedes Mitbewerber-Keyword markieren, für das Sie entweder nicht ranken oder schwach ranken. Das ist der Kern der Analyse.

Spalten (A–E)

  • A – Keyword: ziehen Sie eindeutige Keywords aus dem Tab Competitor URLs.
  • B – Ranken Sie?: =IFERROR(VLOOKUP(A2,'Your Pages'!A:B,2,FALSE),"No")
  • C – Lückentyp: =IF(B2="No","Missing",IF(B2>rank_threshold,"Weak","Covered"))
  • D – Volumen: =IFERROR(VLOOKUP(A2,'Competitor URLs'!A:E,5,FALSE),0)
  • E – Difficulty: =IFERROR(VLOOKUP(A2,'Competitor URLs'!A:F,6,FALSE),0)

Hinweise

  1. Tauschen Sie in Excel VLOOKUP gegen XLOOKUP für saubereres, richtungssicheres Abgleichen.
  2. Zeilen mit „Missing" und „Weak" sind Ihre Lücken; Zeilen mit „Covered" können ausgefiltert werden.
  3. Umschließen Sie Lookups mit IFERROR, damit nicht gefundene Keywords einen sauberen Wert statt eines Fehlers zurückgeben.

Tab Gap-Bewertungs-Formel

Verwandeln Sie rohes Volumen und Difficulty in einen einzigen Prioritätswert, nach dem Sie sortieren können.

Ziel: Lücken nach Chance einstufen, damit der Aufwand in die Keywords mit dem besten Ertrag fließt, nicht nur in die mit dem höchsten Volumen.

Eine Score-Spalte hinzufügen

  • Zuerst filtern: nur Zeilen behalten, in denen Volumen ≥ min_volume und Difficulty ≤ max_kd.
  • Chancen-Score: =ROUND((D2/(E2+1))*IF(C2="Missing",1.5,1),1)
  • Intent-Tag: [Informativ/Kommerziell/Transaktional]
  • Cluster: [Themen-Cluster, zu dem dies gehört]

Wie der Score funktioniert

  1. Volumen geteilt durch Difficulty belohnt Keywords, die oft gesucht, aber leichter zu gewinnen sind.
  2. Der Multiplikator 1.5 hebt völlig fehlende Themen über solche, für die Sie bereits schwach ranken.
  3. Passen Sie die Gewichte an Ihre Nische an; eine Formel ist ein Ausgangspunkt, kein Urteil.

Tab Prioritäts-Ansicht

Ein gefiltertes, sortiertes Dashboard der als Nächstes zu briefenden Lücken, bereit zur Übergabe an Autoren.

Ziel: die fertige Shortlist präsentieren, damit ein Content-Lead Arbeit zuweisen kann, ohne die Formel-Tabs anzufassen.

Anzuzeigende Spalten

  • Keyword, Lückentyp, Volumen, Difficulty, Chancen-Score
  • Zugewiesen an: [Verantwortlicher]
  • Ziel-Veröffentlichungsdatum: [Datum]
  • Status: [Idee/Gebrieft/In Arbeit/Veröffentlicht]

Die Ansicht aufbauen

  1. Verwenden Sie =SORT(FILTER(...)) in Sheets oder eine Pivot-Tabelle, um die höchstbewerteten Lücken automatisch zu ziehen.
  2. Fügen Sie bedingte Formatierung hinzu, damit „Missing"-Zeilen und hohe Scores auf einen Blick auffallen.
  3. Aktualisieren Sie die Eingabe-Tabs monatlich; die Priority View aktualisiert sich selbst, weil sie aus den Formeln liest.

So verwendest du diese Vorlage

  1. Duplizieren Sie die Arbeitsmappe und füllen Sie den Setup-Tab aus: Ihre Domain, bis zu 3 Mitbewerber und Ihre Schwellen.
  2. Exportieren Sie Ihre eigenen rankenden Keywords aus Ihrem SEO-Tool und fügen Sie sie in den Tab Your Pages ein.
  3. Exportieren Sie die rankenden Keywords jedes Mitbewerbers und stapeln Sie sie im Tab Competitor URLs, nach Domain markiert.
  4. Lassen Sie den Tab Gap Matching die beiden Listen abgleichen und jedes Keyword als Missing, Weak oder Covered kennzeichnen.
  5. Filtern Sie Covered-Zeilen aus, damit nur echte Lücken im Blick bleiben.
  6. Wenden Sie die Formel Gap Scoring an und sortieren Sie dann die Priority View nach Chancen-Score.
  7. Kürzen Sie die Shortlist auf Keywords, die zu Ihrem Intent und Geschäft passen, und weisen Sie dann Verantwortliche und Daten zu.
  8. Fügen Sie jeden Monat frische Exporte erneut ein, damit die Formeln neu rechnen und die Priority View aktuell bleibt.

Profi-Tipps

  • Halten Sie rohe Exporte auf eigenen Eingabe-Tabs und bearbeiten Sie sie nie von Hand; erledigen Sie alle Logik in Formel-Tabs, damit eine Aktualisierung nur ein Überschreiben durch Einfügen ist.
  • Teilen Sie Volumen durch Difficulty, statt allein nach Volumen zu sortieren: ein Keyword mit geringerem Volumen, für das Sie tatsächlich ranken können, schlägt oft ein riesiges, für das Sie es nicht können.
  • Gruppieren Sie abgeglichene Lücken in Themen-Cluster, bevor Sie sie briefen, damit eine starke Pillar-Seite mehrere verwandte Keywords auf einmal einfangen kann.
  • Bevorzugen Sie in Excel XLOOKUP gegenüber VLOOKUP; es bricht nicht, wenn Sie Spalten einfügen, und liest sich sauberer, wenn jemand anderes die Datei öffnet.

Haeufige Fragen

Brauche ich Google Sheets, oder funktioniert das in Excel?

Es funktioniert in beiden. Die Tab-Struktur und die Spalten sind identisch, und fast jede Formel ist gemeinsam. Die einzigen lohnenden Tausche sind XLOOKUP für VLOOKUP und natives FILTER/SORT, die sich beide in modernem Excel und Sheets etwas sauberer verhalten.

Wie viele Mitbewerber sollte ich laden?

Zwei oder drei direkte Mitbewerber reichen meist, um die wichtigen Lücken aufzudecken, ohne die Tabelle im Rauschen zu ertränken. Wählen Sie Seiten, die wirklich um Ihre Keywords konkurrieren, statt der größten Marke in Ihrem Umfeld, deren Rankings Sie realistisch vielleicht nie erreichen.

Woher kommen die Keyword-Exporte?

Jedes SEO-Tool, das die Keywords exportiert, für die eine Domain rankt, funktioniert; Sie fügen diesen Export in die Eingabe-Tabs ein. Die Vorlage ist bewusst tool-unabhängig, nutzen Sie also, welche Plattform Sie bereits haben, und behalten Sie jeden Monat dieselbe Quelle für konsistente Vergleiche.

Was bedeutet der Chancen-Score eigentlich?

Es ist ein einfaches Verhältnis von Suchvolumen zu Difficulty, angehoben für Themen, die Ihnen völlig fehlen. Es ist eine Sortierhilfe, keine Garantie. Behandeln Sie einen hohen Score als Anstoß, das Keyword zu prüfen, nicht als Beweis, dass es ranken oder konvertieren wird.

Warum erscheinen manche Keywords als Fehler?

Meist findet ein Lookup keine Übereinstimmung, oft durch überzählige Leerzeichen oder uneinheitliche Groß-/Kleinschreibung zwischen Tabs. Lookups mit IFERROR zu umschließen behebt das, und TRIM auf eingefügte Keywords anzuwenden entfernt verstecktes Leerzeichen, das Übereinstimmungen still zerstört.

Wie oft sollte ich die Analyse neu aufbauen?

Monatlich oder vierteljährlich reicht für die meisten Seiten. Weil die Formeln aus den Eingabe-Tabs lesen, bedeutet Aktualisieren, neue Exporte einzufügen, statt etwas neu zu bauen. Rankings verschieben sich allmählich, also fügt zu häufiges Prüfen meist nur Arbeit hinzu, ohne die Prioritäten stark zu ändern.