Fortgeschrittene SEO-Audit-Techniken: Excel

Lernen Sie ein paar fortgeschrittene Tricks wie das Kombinieren von Datensätzen und das Extrahieren von Daten aus URLs mit Excel und Google Sheets für Ihr SEO-Audit.

Das Vorbereiten und Arbeiten mit Daten erfordert ein wenig Arbeit, aber mit ein wenig Übung werden Sie in kürzester Zeit ein ol’ Profi sein. Es ist wirklich viel einfacher als Sie denken.

Wenn Sie mühelos durch die Datenaufbereitung gehen möchten, laden Sie das vollständige Arbeitsbuch für technische SEO-Audits herunter.

Tipps zum Bereinigen und Aufbereiten von Daten

Eine der ersten Herausforderungen wird das Bereinigen der Daten sein.

Mit anderen Worten, Sie müssen leere Zeilen oder Spalten entfernen, die Ihre Daten durcheinander bringen können, alle Spalten korrekt beschriften und die Daten standardisieren (z. B. könnten Sie „AZ“ in einem Datensatz haben, aber „Arizona“ in einem anderen Datensatz. Sie müssen gleich sein, damit Excel oder Google Sheets erkennen können, dass sie gleich sind.)

Das Bereinigen von Daten ist mühsam, aber es gibt einige Tricks, die es schneller machen können.

So finden und entfernen Sie leere Zeilen

Google Sheets und Excel funktionieren im Wesentlichen auf die gleiche Weise, um Leerzeichen zu entfernen. Es ist nur so, dass die Befehle etwas anders aussehen.

In Excel:

1. Wählen Sie alle Daten aus.
2. Klicken Sie auf Startseite > Suchen & Auswählen > Gehen Sie zu Spezial, wählen Sie „Leerzeichen“ aus und klicken Sie auf „OK.“
3. Klicken Sie mit der rechten Maustaste auf eine ausgewählte Zelle und wählen Sie „Löschen“ aus dem Dropdown-Menü.
4. Wählen Sie „Ganze Zeile“ und klicken Sie auf „OK.“

In Google Tabellen:

1. Wählen Sie die Daten aus.
2. Klicken Sie auf Datenfilter und deaktivieren Sie das Kontrollkästchen neben Leerzeichen.“
3. Wählen Sie dann „Filter erstellen“ auf der Registerkarte Daten aus.
4. Klicken Sie auf das Filtersymbol in Zeile 1. Dann löschen Sie > Leerzeichen.
5. Strg (oder Cmd) + A und Zeilen löschen.
6. Schalten Sie den Filter aus (Registerkarte Daten > Filter ausschalten).

So finden und entfernen Sie leere Spalten

Die Schritte zum Entfernen leerer Spalten sind im Wesentlichen die gleichen wie beim Entfernen leerer Zeilen. Überspringen Sie diesen Schritt jedoch, und Sie werden feststellen, dass die Datenanalyse plötzlich viele Fehler beinhaltet.

In Excel:

1. Daten auswählen.
2. Klicken Sie auf Startseite > Suchen & Auswählen > Gehen Sie zu Speziellen > Leerzeichen und klicken Sie auf „OK.“
3. Wählen Sie mit Strg (oder Befehlstaste) + Leertaste die gesamte Spalte aus und klicken Sie im Kontextmenü auf Löschen.
4. Wählen Sie „Gesamte Spalte“ aus und klicken Sie auf „OK.“

In Google Tabellen:

1. Wählen Sie die Daten aus.
2. Klicken Sie auf Daten > Filtern.
3. Klicken Sie auf die Schaltfläche Filter in Zeile 1 und deaktivieren Sie das Kontrollkästchen neben Leerzeichen.“
4. Wählen Sie die leeren Spalten aus, klicken Sie mit der rechten Maustaste und wählen Sie „Spalte löschen“ aus dem Menü.

So verwenden Sie Verketten, um zwei Zellen zu kombinieren

Sie können Text mit der Funktion VERKETTEN zu einer einzelnen Zelle kombinieren.

Es ist besonders nützlich, um Vor- und Nachnamen, Datumsangaben oder andere Daten zu kombinieren, die Ihnen in mehreren Spalten angezeigt werden. Und indem Sie sie verketten, können Sie Daten in einem einzigen Blatt organisieren und zusammenführen.

In Excel und Google Sheets:

Nehmen wir an, wir haben zwei Datensätze in separaten Blättern:

Blatt 1 enthält Vorname, Nachname und E-Mail-Adresse.

Blatt 2 enthält Vorname, Nachname und Telefonnummer.

Lassen Sie’s die Daten aus den beiden Blättern kombinieren, um die Arbeit mit den Daten zu erleichtern.

Lassen Sie uns zuerst Vor- und Nachnamen kombinieren, falls wir mehr als einen John in unserem Datensatz haben.

1. Fügen Sie eine neue Spalte in die Blätter 1 und 2 rechts neben der Spalte „Nachname" ein.

2. Geben Sie in C2 auf beiden Blättern die Formel ein:

=VERKETTEN(A2,“ „,B2)

(Dies besagt, lassen Sie für die Zeichenfolge in A2 ein Leerzeichen (“ „) und fügen Sie dann den Inhalt von B2 hinzu.)

3. Wählen Sie C2 aus, klicken Sie auf die untere rechte Ecke von C2 und ziehen Sie es auf dieunten in Ihren Daten, um die Formel nach unten zu kopieren.

4. Fügen Sie nun eine neue Spalte rechts neben dem kombinierten Namen (C) in Blatt 1 ein.

5. Geben Sie in der ersten Zeile der neuen Spalte die folgende Formel ein:

=SVERWEIS(Tabelle1!C2, Blatt 2!$C: $E,2,FALSE)

Diese Formel sucht nach dem, was in C2 in Blatt 2 steht (dem vollständigen Namen von John Doe) und gibt die entsprechende Telefonnummer an Blatt 1 zurück.

6. Kopieren Sie die Formel in die erste Zeile der neuen Spalte in Blatt 1 und fügen Sie sie wie bei der Formel Verketten in die verbleibenden Zeilen der Spalte ein.

Weitere schnelle und hilfreiche Befehle zur Datenbereinigung

Möchten Sie unerwünschten Speicherplatz in einer Zelle entfernen? Verwenden Sie Trimmen. (Es funktioniert sowohl in Excel als auch in Google Sheets.)

So kombinieren Sie Datensätze mit Google Sheets zu einer einzigen Tabelle

Eine weitere häufige Aufgabe, die Sie bei der Arbeit mit Daten erledigen werden, besteht darin, alles in dasselbe Blatt zu bringen.

Aber ein Wort der Warnung: Das Kombinieren mehrerer Datendateien kann ein Albtraum sein, wenn Sie Ihre Daten nicht vorbereiten. Stellen Sie also sicher, dass sie alle die gleiche Struktur und das gleiche Format haben, bevor Sie beginnen.

Beginnen wir mit etwas Einfachem und arbeiten uns nach oben.

Importieren Sie einen gesamten Datensatz in einen anderen

Importieren Sie Daten in ein vorhandenes Blatt. Datei > Importieren> Wählen Sie den Datensatz zu importieren > Einfügen. Ändern Sie dann die Dropdown-Liste in "Neues Blatt einfügen.” Es werden die neuen Daten unten hinzufügen.

Verwenden Sie die „ABFRAGE“-Funktion

Nehmen wir an, Sie haben ein Blatt mit Traffic- und Absprungraten und möchten ein Blatt mit Absprungraten erstellen, die unter 50% liegen, um Ihre leistungsstärkeren Seiten zu identifizieren.

= ABFRAGE(Blatt1!A2:C5, „WÄHLEN SIE A,B,C AUS, WOBEI C < 0,5")

Diese Formel erstellt einen neuen Datensatz, der alle Informationen für alle Seiten enthält, die unsere Anforderung einer Absprungrate von 50% erfüllen.

In dieser Formel ist Blatt1!A2: C5 sind die Bereiche, die es durchschaut; WÄHLEN SIE A, B, C aus, welche Zellen gezogen werden sollen, wenn die Absprungrate unter 0,5 liegt. und C < 0,5" ist die mathematische Funktion, die angibt, dass die Informationen nur dann vom anderen Blatt kopiert werden sollen, wenn der Wert in Spalte C kleiner als 0,5 ist.

Verwenden Sie die „SVERWEIS“-Funktion

Mit der SVERWEIS-Funktion können Sie nach einem Wert in einem Datensatz suchen und einen entsprechenden Wert aus einem anderen Datensatz zurückgeben. Hier ist ein Beispiel für die Verwendung:

Zwei Blätter: eines mit Absprungraten (Blatt 2) und eines mit Verkehrszahlen (Blatt 1).

= SVERWEIS (Blatt 1!A2, Blatt2!A:B, 2, FALSE)

Diese Formel sucht nach dem Wert in Tabelle1!A2 in der ersten Spalte von Blatt 2 und geben Sie den entsprechenden Wert aus der zweiten Spalte von Blatt 2 zurück, wenn sie übereinstimmen.

Ihre Seitennamen müssen auf beiden Blättern gleich sein. Wenn Sie eine "Verkaufsseite" in einem Datensatz und eine "Zielseite" auf dem anderen haben, funktioniert es nicht.

Die Funktionen „INDEX“ und „MATCH“

=INDEX(Blatt 2!B:B, STREICHHOLZ(Blatt 1!A2, Blatt2!A:A, 0))

Diese Formel sucht nach dem Wert in Tabelle1!A2 in der ersten Spalte von Blatt 2 und geben Sie den entsprechenden Wert aus der zweiten Spalte von Blatt 2 zurück.

Importfunktionen In der Bearbeitungsleiste

IMPORTRANGE kann die Aufgabe über die Bearbeitungsleiste erledigen.

=IMPORTRANGE(„spreadsheet_url“, „range_string“)

Kopieren Sie die URL für den anderen Datensatz. (Alles vor dem # -Zeichen.)

Dann sagen Sie ihm, über welchen Bereich kopiert werden soll. (Es wird automatisch aktualisiert, wenn Änderungen auf dem anderen Blatt vorgenommen werden.)

Es wird Ihnen einen Fehler geben, also müssen Sie ihm nur Zugriff geben. Andere Importtypen, mit denen Sie spielen können?

Import über RSS-Feed:

=IMPORTFEED(https://example.com/rss , „Titel der Elemente“)

Import über XML:

=IMPORTXML(„https://example.com/&rdquo ;, „xpath_query“)

Import über strukturierte Online-Datendatei (wie eine CSV):

=IMPORTDATA(„https://example.com/file.csv&rdquo 😉

Import aus einer Tabelle auf einer Webseite:

=IMPORTHTML(„https://example.com/slug&rdquo ;,“Tabelle“,1)

(Wobei „Tabelle“ die Abfrage und „1“ die Indexstartposition ist.)

Lösungen ohne Formeln

Google Sheets-Add-Ons können auch sehr hilfreich sein, wenn Sie Formeln vermeiden möchten. Ich würde Sie jedoch ermutigen, die Formeln auszuprobieren. Sie sind flexibel. (Und sie geben dir das Gefühl, klug und mächtig zu sein, wenn sie arbeiten.)

So kombinieren Sie Datensätze mit Excel zu einer einzigen Tabelle

Einfacher Import

Datei auswählen > Importieren > Wählen Sie den Datensatz aus. Sagen Sie ihm dann, ob es sich um eine Datei mit begrenzter oder fester Breite handelt. > Sagen Sie ihm, wie die Datei abgegrenzt ist (Tabulatoren, Doppelpunkte usw.).). > Wählen Sie das Datenformat (Text, Termine, etc.). > Fertig.

Sagen Sie ihm schließlich, ob Sie es zu einem vorhandenen Blatt hinzufügen möchten (und wo) oder ob es ein neues Blatt erstellen soll.

Verwenden Sie die Power Query von Excel

Wenn Ihnen das Abrufen von Abfragen in Google Sheets gefallen hat, werden Sie Power Query in Excel lieben. Außerhalb von Python, R und dem Erlernen einer ganzen Programmiersprache ist dies die nützlichste und leistungsstärkste Funktion, mit der Sie mit großen Datensätzen arbeiten können.

Sie finden auch eine hervorragende Dokumentation und viele Videos (mit Daten, die Sie herunterladen können, um mitzuverfolgen), die Ihnen helfen, die Verwendung zu erlernen. Monatliche Berichtsdaten sind eine hervorragende Praxis.

Mit einer frischen Excel-Datei beginnen? Importieren Sie Ihre Datendateien direkt hinein.

Daten > Daten abrufen & transformieren > Daten abrufen. Wählen Sie den Dateityp aus, den Sie importieren oder über eine URL hinzufügen möchten, und befolgen Sie die Anweisungen zum Importieren der Datei in Excel.

Fahren Sie fort, alle Datendateien in Excel zu importieren.

Jetzt müssen Sie die Datendateien kombinieren.

Daten > Daten abrufen & transformieren > Abfragen kombinieren > Abfragen anhängen.

Wählen Sie die gewünschten aus. Sie können auch eine benutzerdefinierte Spalte hinzufügen, die angibt, aus welcher Datei die Daten stammen.

Das NACHSCHLAGEN in Excel

Die „NACHSCHLAGEN“-Funktion funktioniert genau wie das Nachschlagen in Google Tabellen. Hier ist ein Beispiel:

=SVERWEIS(A2, Blatt1!A: B, 2, FALSE)

Diese Formel sucht in der ersten Spalte von Blatt 2 nach dem Wert in A2 und gibt den entsprechenden Wert aus der zweiten Spalte von Blatt 2 zurück.

Verwenden Sie „INDEX“ Und „MATCH“

INDEX und MATCH funktionieren auch in Excel magisch.

=INDEX(Blatt 2!B5:C8,STREICHHOLZ(Blatt 1!A2, Blatt2!A5:A8,0)ÜBEREINSTIMMUNG(D1, Blatt2!A4: C4,0))

Diese Formel funktioniert, indem Sie den Bereich auf Blatt 2 (in blau) durchsucht, indem Sie die erste Spalte (A5: A8) nach dem Wert in A2 auf eine exakte Übereinstimmung (0) überprüft. Das ist der ganze Text in Rot.

Der grüne Text ist die Zeileninformation. Es überprüft die erste Zeile (A4: C4) auf den in D1 gefundenen Wert und sucht nach einer genauen Übereinstimmung (0).

Möchten Sie den Wert nur kopieren, wenn der Wert einen bestimmten Betrag beträgt? 1 = kleiner als und -1 = mehr als.

Wenn das nicht so aufregend erscheint, warten Sie einfach, bis Sie sehen, was Sie als nächstes damit anfangen können.

Mit Daten arbeiten

Werte mit zwei benutzerdefinierten Variablen nachschlagen

Manchmal möchten Sie einfach nur einen bestimmten Wert nachschlagen können.

In diesem Beispiel haben wir monatliche Verkehrszahlen in den Spalten und Seiten in den Zeilen.Jetzt können wir INDEX und MATCH verwenden, um nachzusehen, wie viel Verkehr die Kontaktseite (oder eine beliebige Seite) im Monat Januar (oder einem beliebigen Monat) erhalten hat, indem wir einfach G2 und G3 eingeben.

Die Formel, die Sie in G4 eingeben:

=INDEX(B2:C5,ÜBEREINSTIMMUNG(G3,A2:A5,0),ÜBEREINSTIMMUNG(G2,B1:C1,0))

Der INDEX muss wissen, in welchem Bereich er aussehen soll. Dann möchte es wissen, welche Spalte und Zeile es finden muss. Die Übereinstimmungsfunktionen fungieren als Zeilen- und Spaltennummern, indem sie nach genauen Übereinstimmungen suchen, je nachdem, was Sie in G2 und G3 eingegeben haben.

Das bedeutet, dass Sie diesen kleinen Tippabschnitt auf ein sauberes Blatt verschieben und ein Dashboard einrichten können, damit jeder in Ihrem Team Daten nachschlagen kann!

Wenn Ihnen das gefallen hat, versuchen Sie es mit SUMIFs und AVERAGEIFs , die Daten basierend auf mehreren Kriterien summieren (oder mitteln).

Dies ist perfekt, um Ihre Google Analytics-Daten schnell nach bestimmten Kriterien wie Traffic-Quelle, Medium und Datumsbereich zusammenzufassen.

Pivot-Tabellen erstellen

Pivot-Tabellen sind wahrscheinlich eines der wichtigsten Werkzeuge, die Sie haben werden, wenn Sie versuchen, große Datenmengen zusammenzufassen und in ein besser verwaltbares Format umzuwandeln.

Verwenden Sie es, um Ihre Berichterstattung zu beschleunigen, indem Sie:

• Verkehrsdaten nach Produkt, Kategorie oder Zeitraum zusammenfassen.
• Analysieren Sie den Datenverkehr im Zeitverlauf, um saisonale Trends zu erkennen.
• Vergleichen Sie Daten über verschiedene Kategorien hinweg, z. B. Absprungraten für die Zielseiten für verschiedene Produkte oder Regionen.
• Filtern Sie Daten, um nur Daten für ein bestimmtes Produkt oder eine bestimmte Region anzuzeigen.
• Berechnen Sie Metriken wie Durchschnittswerte, Zählungen und Prozentsätze. Zum Beispiel der durchschnittliche Traffic pro Tag für jedes Produkt in den letzten 30 Tagen.
• Identifizieren Sie Ausreißer wie Seiten oder Produkte, die viel mehr oder viel weniger verkaufen als andere Produkte.

In Google Tabellen:

Markieren Sie die Daten, die Sie analysieren möchten.

So erstellen Sie die Tabelle: Daten > Pivot-Tabelle, um ein neues Blatt mit einer leeren Pivot-Tabelle zu öffnen.

Richten Sie nun Ihre Pivot-Tabelle ein.

Wählen Sie in den Feldern „Zeilen“ und „Spalten“ die Felder aus, die Sie als Zeilen und Spalten verwenden möchten. Wählen Sie im Feld „Werte“ das Feld aus, das Sie analysieren möchten.

Brauchen Sie etwas anderes? Passen Sie Ihre Pivot-Tabelle an, indem Sie die Zusammenfassungsfunktion (z. B. Summe, Durchschnitt, Anzahl) ändern, die Daten sortieren und filtern sowie Zwischensummen und Gesamtsummen hinzufügen.

Profi-Tipp: Haben Sie einen großen Datensatz? Wählen Sie die gesamte Tabelle aus, indem Sie auf die obere linke Ecke der Tabelle klicken.

In Excel:

Markieren Sie zunächst wie in Sheets die Daten, die Sie analysieren möchten. Bei einem großen Datensatz funktioniert das Klicken auf die obere linke Ecke der Tabelle auch hier.

Einfügen > Pivot-Tabelle > Fügen Sie den Bereich ein, in dem sich Ihre Daten befinden, die Sie analysieren möchten. Dann, wenn Sie die Pivot-Tabelle auf einem neuen Blatt oder in dem vorhandenen Blatt haben möchten.

(Wenn Sie versehentlich einen leeren Bereich ausgewählt haben, erhalten Sie eine Fehlermeldung. Wenn es sich also beschwert, ist dies normalerweise der Grund.)

Richten Sie nun Ihre Pivot-Tabelle ein. Fügen Sie alles, was Sie wollen, in die Spalten in das Feld " Werte" und die Zeilen in das Feld " Zeilen" ein.

Und wenn Sie es ändern müssen (damit es zum Beispiel Durchschnitt anstelle von Summe verwendet), ändern Sie das mit der Informationstaste auf dem entsprechenden Wert.

Verwenden Sie REGEXTRACT, um Informationen aus URLs abzurufen

Die Regexextract-Formel extrahiert bestimmte Daten aus Textzeichenfolgen mithilfe regulärer Ausdrücke. So können Sie beispielsweise mit Regexextract die Quelle und das Medium Ihres Website-Traffics aus Google Analytics-Berichten extrahieren.

Um die Domain aus einer URL in Zelle A2 zu extrahieren, können Sie die folgende Formel verwenden:

=REGEXEXTRACT(A2,“^(?: https-Verbindung?://)?(?:[^@n]+@)?(?in: www.)?([^:/n]+)“)

Reguläre Ausdrücke können komplex sein, daher ist es wichtig, Ihre Formel mit verschiedenen Beispielen zu testen, um sicherzustellen, dass sie die richtigen Daten extrahiert.

Tabellen minimieren

Um eine Tabelle zu minimieren, klicken Sie auf das kleine Pfeilsymbol in der oberen linken Ecke der Tabelle, um sie zu reduzieren und die Daten auszublenden, sodass nur der Tabellenkopf sichtbar bleibt.

Um die Tabelle wieder zu erweitern, klicken Sie einfach auf das Pfeilsymbol.

Sie können auch die Funktion „Daten“ > „Zeilen gruppieren“ oder „Spalten gruppieren“ verwenden, um verwandte Zeilen oder Spalten zu gruppieren und zusammenklappbare Abschnitte zu erstellen.

Einfrieren von Zeilen und Spalten

Das Einfrieren von Zeilen und Spalten hält bestimmte Zeilen oder Spalten auf dem Bildschirm sichtbar, während Sie durch den Rest der Tabelle scrollen.

In Google Tabellen: Wählen Sie die Zeile oder Spalte aus, die Sie einfrieren möchten. Klicken Sie auf Ansicht > Einfrieren > Bis zur aktuellen Zeile oder Spalte (oder bis einschließlich der aktuellen Zeile/Spalte.)

In Excel: Wählen Sie die Zeile oder Spalte aus, die Sie einfrieren möchten. Klicken Sie dann auf Ansicht > „Fenster einfrieren,“ „Obere Zeile einfrieren,“ oder „Erste Spalte einfrieren.“

Wenn Sie wirklich mit einer großen Datenmenge zu kämpfen haben, probieren Sie Filter aus. Auf diese Weise können Sie irrelevante Daten entfernen und nur die Daten anzeigen, die bestimmte Kriterien erfüllen.

Zusätzliche Tipps für große Datensätze

Verwenden Sie benannte Bereiche, um einem Zellbereich in Ihrem Blatt einen bestimmten Namen zuzuweisen. Dies kann es einfacher machen, auf große Datensätze zu verweisen und diese zu analysieren.

Vermeiden Sie die Verwendung zu vieler Formeln. Diese verlangsamen die Leistung Ihres Blattes und können Ihnen auch das Leben sehr schwer machen, insbesondere wenn Sie anfangen, mehrere Formeln miteinander zu verketten.

Bedingte Formatierung verwenden:

In Google Tabellen:

Wählen Sie den Zellbereich aus, auf den Sie die bedingte Formatierung anwenden möchten. Klicken Sie auf Format > Bedingte Formatierung.

Wählen Sie im Bereich „Bedingte Formatierungsregeln“ auf der rechten Seite des Bildschirms die Art der Formatierung aus, die Sie anwenden möchten. Es stehen verschiedene Optionen zur Auswahl, einschließlich "Farbskala," "Symbolsatz," und „Benutzerdefinierte Formel.“

In Excel:

Wählen Sie den Zellbereich aus, auf den Sie die bedingte Formatierung anwenden möchten, und klicken Sie auf Start > Stile > Bedingte Formatierung.

Wählen Sie im Dropdown-Menü die Art der Formatierung aus, die Sie anwenden möchten, einschließlich „Regeln für Hervorhebungen von Zellen,“Regeln für oben/unten,“ und „Datenbalken.“

Fazit

Excel und Google Sheets sehen möglicherweise zu einfach aus, um viel zu tun, oder sie erscheinen zu kompliziert, um sie zu lernen, aber keine dieser Annahmen ist wahr.

Mit ein paar Formeln und Tricks im Ärmel werden Sie schnell feststellen, dass sie zu den leistungsstärksten und wichtigsten Tools in Ihrer SEO-Toolbox gehören.

Bereit, ein Datenprofi zu werden? Laden Sie das vollständige Arbeitsbuch zum technischen SEO-Audit herunter und beherrschen Sie die Kunst der Aufbereitung und Arbeit mit Daten.

Ausgewähltes Bild: Paulo Bobita/Search Engine Journal

In-Post-Bilder: Screenshot des Autors aus Excel/Google Sheets, Mai 2023