Beginnen Sie mit GSC-Abfragen in BigQuery
BigQuery bietet eine Reihe von Vorteilen, die andere Tools nicht bieten, wenn es um die Analyse großer Mengen von Google Search Console (GSC)-Daten geht.
Damit können Sie Milliarden von Zeilen in Sekunden verarbeiten und so eine tiefgreifende Analyse großer Datenmengen ermöglichen.
Dies ist eine Weiterentwicklung der Google Search Console, die Ihnen nur den Export von 1.000 Datenzeilen ermöglicht und es zu Datendiskrepanzen kommen kann.
Sie haben alles darüber gelesen, warum Sie BigQuery als SEO-Profi nutzen sollten. Sie haben herausgefunden, wie Sie GSC mit BigQuery verbinden. Daten fließen!
Was jetzt?
Es ist Zeit, mit der Abfrage der Daten zu beginnen. Das Verstehen und effektive Abfragen der Daten ist der Schlüssel zum Gewinn umsetzbarer SEO-Erkenntnisse.
In diesem Artikel erklären wir Ihnen, wie Sie mit Ihren Anfragen beginnen können.
Grundlegendes zur GSC-Datenstruktur in BigQuery
Die Daten werden in Tabellen organisiert. Jede Tabelle entspricht einem bestimmten Google Search Console-Bericht. Die offizielle Dokumentation ist sehr umfangreich und übersichtlich.
Wenn Sie dies jedoch lesen, dann deshalb, weil Sie den Kontext und die Schlüsselelemente verstehen möchten, bevor Sie sich damit befassen.
Wenn Sie sich die Zeit nehmen, dies herauszufinden, können Sie bessere Abfragen effizienter erstellen und gleichzeitig die Kosten niedrig halten.
GSC-Tabellen, Schema und Felder in BigQuery
Schema ist der Entwurf, der abbildet, was jedes Feld (jede Information) in einer Tabelle darstellt.
In der offiziellen Dokumentation werden drei unterschiedliche Schemata vorgestellt, da nicht jede Tabelle unbedingt denselben Datentyp enthält. Stellen Sie sich Tabellen als dedizierte Ordner vor, die bestimmte Arten von Informationen organisieren.
Aus Gründen der Übersichtlichkeit wird jeder Bericht separat gespeichert. Du hast:
- searchdata_site_impression: Enthält Leistungsdaten für Ihre Immobilie, aggregiert nach Immobilie.
- searchdata_url_impression: Enthält nach URL aggregierte Leistungsdaten für Ihre Property.
- exportLog: Jeder erfolgreiche Export in eine der beiden Tabellen wird hier protokolliert.
Ein paar wichtige Hinweise zu Tabellen:
- In der offiziellen Dokumentation finden Sie, dass die Dinge nicht so laufen, wie wir es erwarten: „Die Search Console exportiert Massendaten einmal pro Tag, allerdings nicht unbedingt zur gleichen Zeit für jede Tabelle.“
- Beim GSC-Massenexport bleiben Tabellen standardmäßig für immer erhalten.
- In der Tabelle auf URL-Ebene (searchdata_url_impression) befinden sich Discover-Daten. Das Feld is_anonymized_discover gibt an, ob die Datenzeile dem Discover-Anonymisierungsschwellenwert unterliegt.
Felder sind einzelne Informationen, also die spezifische Art von Daten in einer Tabelle. Wenn es sich um eine Excel-Datei handeln würde, würden wir Felder als Spalten in einer Tabelle bezeichnen.
Wenn wir über Google Analytics sprechen, sind Felder Metriken und Dimensionen. Die folgenden wichtigen Datenfelder sind in BigQuery verfügbar, wenn Sie GSC-Daten importieren:
- Klicks – Anzahl der Klicks für eine Abfrage.
- Eindrücke – Häufigkeit, mit der eine URL für eine Abfrage angezeigt wurde.
- CTR – Klickrate (Klicks/Impressionen).
- Position – Durchschnittliche Position für eine Suchanfrage.
Nehmen wir als Beispiel das Tabellenschema „searchdata_site_impression“. Es enthält 10 Felder:
| Feld | Erläuterung |
| Daten Datum | Der Tag, an dem die Daten in dieser Zeile generiert wurden, in Pacific Time. |
| Seiten-URL | URL der Eigenschaft, sc-domain:property-name oder die vollständige URL, abhängig von Ihrer Validierung. |
| Abfrage | Die Suchanfrage des Benutzers. |
| is_anonymized_query | Bei „true“ gibt das Abfragefeld null zurück. |
| Land | Land, aus dem die Suchanfrage stammt. |
| Suchtyp | Art der Suche (Web, Bild, Video, Nachrichten, Entdecken, GoogleNews). |
| Gerät | Das vom Benutzer verwendete Gerät. |
| Eindrücke | Die Häufigkeit, mit der eine URL für eine bestimmte Suchanfrage angezeigt wurde. |
| Klicks | Die Anzahl der Klicks, die eine URL für eine Suchanfrage erhalten hat. |
| sum_top_position | Diese Berechnung ermittelt, wo Ihre Website normalerweise in den Suchergebnissen rangiert. Es untersucht die höchste Position, die Ihre Website bei verschiedenen Suchanfragen erreicht, und berechnet den Durchschnitt. |
Etwas zusammensetzen
In BigQuery bezieht sich der Datensatz für den Massenexport der Google Search Console (GSC) normalerweise auf die Sammlung von Tabellen, in denen die GSC-Daten gespeichert sind.
Der Datensatz trägt standardmäßig den Namen „searchconsole“.
” alt=”BigQuery-Suchkonsolentabellen” width=”480″ height=”204″ sizes=”(max-width: 480px) 100vw, 480px” srcset=”https://behmaster.com/wp-content/uploads/2024/02/localimages/console-659bd408d0698-sej-480×204.png 480w, https://behmaster.com/wp-content/uploads/2024/02/localimages/console-659bd408d0698-sej.png 585w” src=”https://behmaster.com/wp-content/uploads/2024/02/localimages/console-659bd408d0698-sej-480×204.png”>
Im Gegensatz zur Registerkarte „Leistung“ in GSC müssen Sie Abfragen schreiben, um BigQuery zur Rückgabe von Daten aufzufordern. Dazu müssen Sie auf die Schaltfläche „Abfrage in BigQuery ausführen“ klicken.
Sobald Sie dies getan haben, sollten Sie Zugriff auf BigQuery Studio haben, wo Sie Ihre erste SQL-Abfrage erstellen. Jedoch, Ich empfehle Ihnen noch nicht, auf diese Schaltfläche zu klicken.
Wenn Sie im Explorer Ihr Projekt öffnen, werden Ihnen die Datensätze angezeigt. Es ist ein Logo mit Quadraten mit Punkten darin. Hier sehen Sie beispielsweise, ob Sie über GA4- und GSC-Daten verfügen.
” alt=”Datensatz für die Suchimpressionstabelle ” width=”760″ height=”353″ sizes=”(max-width: 760px) 100vw, 760px” srcset=”https://behmaster.com/wp-content/uploads/2024/02/localimages/table-preview-659bd6b200c40-sej-768×357.png 768w, https://behmaster.com/wp-content/uploads/2024/02/localimages/table-preview-659bd6b200c40-sej-480×223.png 480w, https://behmaster.com/wp-content/uploads/2024/02/localimages/table-preview-659bd6b200c40-sej-680×316.png 680w, https://behmaster.com/wp-content/uploads/2024/02/localimages/table-preview-659bd6b200c40-sej-1024×476.png 1024w, https://behmaster.com/wp-content/uploads/2024/02/localimages/table-preview-659bd6b200c40-sej.png 1600w” src=”https://behmaster.com/wp-content/uploads/2024/02/localimages/table-preview-659bd6b200c40-sej-768×357.png”>
Wenn Sie auf die Tabellen klicken, erhalten Sie Zugriff auf das Schema. Sie können die Felder sehen, um zu bestätigen, dass es sich um die Tabelle handelt, die Sie abfragen möchten.
Wenn Sie oben in der Benutzeroberfläche auf „ABFRAGE“ klicken, können Sie Ihre SQL-Abfrage erstellen. Dies ist besser, da dadurch einige Informationen geladen werden, die Sie für Ihre Anfrage benötigen.
Das FROM wird mit der richtigen Tabelle ausgefüllt, ein Standardlimit und das Datum festgelegt, das Sie bei Bedarf ändern können.
Erste Schritte mit Ihrer ersten Anfrage
Search Console > BigQuery-Export war bisher nur für Unternehmen mit Entwicklern/einem sehr technischen SEO verfügbar. Jetzt ist es für alle verfügbar!
Das Schreiben von SQL ist eine immer wichtigere Fähigkeit für Vermarkter und ich arbeite an etwas, das dabei hilft – wenn Sie es testen möchten, schreiben Sie mir eine DM 🙂 https://t.co/voOESJfo1e
– Robin Lord (@RobinLord8) 21. Februar 2023
Die hier besprochenen Abfragen sind einfach, effizient und kostengünstig.
Haftungsausschluss: Die vorherige Aussage hängt von Ihrer spezifischen Situation ab.
Leider können Sie nicht in der Sandbox bleiben, wenn Sie lernen möchten, wie man BigQuery mit GSC-Daten verwendet. Sie müssen Ihre Rechnungsdaten eingeben. Wenn Sie dadurch ausgeflippt sind, haben Sie keine Angst. Die Kosten sollten niedrig sein.
- Die ersten 1 TiB pro Monat an Abfragedaten sind kostenlos.
- Wenn Ihr Budget knapp ist, können Sie Benachrichtigungen zum Cloud-Rechnungsbudget einrichten. Sie können eine BigQuery-spezifische Benachrichtigung festlegen und werden benachrichtigt, sobald Gebühren für die Datennutzung anfallen.
In SQL ist die Anweisung „SELECT *“ ein leistungsstarker Befehl, mit dem Sie alle Spalten aus einer angegebenen Tabelle oder bestimmte Spalten gemäß Ihrer Spezifikation abrufen können.
Mit dieser Anweisung können Sie den gesamten Datensatz oder einen Teilsatz basierend auf Ihren Auswahlkriterien anzeigen.
Eine Tabelle besteht aus Zeilen, die jeweils einen eindeutigen Datensatz darstellen, und Spalten, in denen verschiedene Attribute der Daten gespeichert sind. Mit „SELECT *“ können Sie alle Felder einer Tabelle untersuchen, ohne jede Spalte einzeln anzugeben.
Um beispielsweise eine Google Search Console-Tabelle für einen bestimmten Tag zu durchsuchen, könnten Sie eine Abfrage wie die folgende verwenden:
SELECT *
FROM `yourdata.searchconsole.searchdata_site_impression`
WHERE data_date = '2023-12-31'
LIMIT 5;
Sie müssen immer sicherstellen, dass die FROM-Klausel Ihre searchdata_site_impression-Tabelle angibt. Aus diesem Grund wird empfohlen, zuerst auf die Tabelle zu klicken, da dadurch die FROM-Klausel automatisch mit der richtigen Tabelle gefüllt wird.
Wichtig: Wir begrenzen die Daten, die wir laden, indem wir das Feld data_date verwenden. Es empfiehlt sich, die Kosten zu begrenzen (zusammen mit der Festlegung eines Limits).
” alt=”Die Ergebnisse der ersten von uns durchgeführten Abfrage werden in Tabellenform angezeigt” width=”760″ height=”393″ sizes=”(max-width: 760px) 100vw, 760px” srcset=”https://behmaster.com/wp-content/uploads/2024/02/localimages/return-results-from-query-659bd7d6823d3-sej-768×397.png 768w, https://behmaster.com/wp-content/uploads/2024/02/localimages/return-results-from-query-659bd7d6823d3-sej-480×248.png 480w, https://behmaster.com/wp-content/uploads/2024/02/localimages/return-results-from-query-659bd7d6823d3-sej-680×352.png 680w, https://behmaster.com/wp-content/uploads/2024/02/localimages/return-results-from-query-659bd7d6823d3-sej-1024×530.png 1024w, https://behmaster.com/wp-content/uploads/2024/02/localimages/return-results-from-query-659bd7d6823d3-sej.png 1600w” src=”https://behmaster.com/wp-content/uploads/2024/02/localimages/return-results-from-query-659bd7d6823d3-sej-768×397.png”>
Ihre erste URL-Impression-Abfrage
Wenn Sie Informationen für jede URL auf Ihrer Website sehen möchten, bitten Sie BigQuery, Informationen aus der Tabelle „searchdata_url_impression“ abzurufen und dabei die Felder „Abfrage“ und „Klicks“ auszuwählen.
So würde die Abfrage in der Konsole aussehen:
SELECT
url,
SUM(clicks) AS clicks,
SUM(impressions)
FROM
`yourtable.searchdata_url_impression`
WHERE
data_date = ‘2023-12-25’
GROUP BY
url
ORDER BY
clicks DESC
LIMIT
100
Sie müssen immer sicherstellen, dass die FROM-Klausel Ihre searchdata_url_impression-Tabelle angibt.
Wenn Sie GSC-Daten in BigQuery exportieren, enthält der Export Partitionstabellen. Die Partition ist das Datum.
Das bedeutet, dass die Daten in BigQuery so strukturiert sind, dass ein schneller Abruf und eine datumsbasierte Analyse möglich sind.
Deshalb wird das Datum automatisch in die Abfrage einbezogen. Wenn Sie jedoch das späteste Datum auswählen, verfügen Sie möglicherweise über keine Daten, da die Daten möglicherweise noch nicht exportiert wurden.
Aufschlüsselung der Abfrage
In diesem Beispiel wählen wir die Felder URL, Klicks und Impressionen für den 25. Dezember 2023 aus.
Wir gruppieren die Ergebnisse nach jeder URL mit der Summe der Klicks und Impressionen für jede URL.
Abschließend ordnen wir die Ergebnisse nach der Anzahl der Klicks für jede URL und begrenzen die Anzahl der Zeilen (URLs) auf 100.
Erstellen Sie Ihren bevorzugten GSC-Bericht neu
Ich empfehle Ihnen, den GSC-Leitfaden zum Exportieren von Massendaten zu lesen. Sie sollten den Export verwenden, daher werde ich keine Informationen zur Tabellenoptimierung bereitstellen. Das ist etwas fortgeschrittener als das, was wir hier behandeln.
Auf der Registerkarte „Leistung“ von GSC wird jeweils eine Dimension angezeigt, wodurch der Kontext eingeschränkt wird. Mit BigQuery können Sie mehrere Dimensionen kombinieren, um bessere Erkenntnisse zu gewinnen
Durch die Verwendung von SQL-Abfragen erhalten Sie eine übersichtliche Tabelle. Sie müssen die Besonderheiten von SQL nicht verstehen, um BigQuery optimal nutzen zu können.
Diese Abfrage erfolgt mit freundlicher Genehmigung von Chris Green. Einige seiner SQL-Abfragen finden Sie in Github.
SELECT
query,
is_anonymized_query AS anonymized,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SUM(clicks)/NULLIF(SUM(impressions), 0) AS CTR
FROM
yourtable.searchdata_site_impression
WHERE
data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
GROUP BY
query,
anonymized
ORDER BY
clicks DESC
Diese Abfrage bietet Einblicke in die Leistung von Benutzeranfragen in den letzten 28 Tagen unter Berücksichtigung von Impressionen, Klicks und CTR.
Dabei wird auch berücksichtigt, ob die Abfragen anonymisiert sind oder nicht, und die Ergebnisse werden nach der Gesamtzahl der Klicks in absteigender Reihenfolge sortiert.
Dadurch werden die Daten neu erstellt, die Sie normalerweise im Search Console-Leistungsbericht für die letzten 28 Tage finden würdenErgebnisse nach Abfrage und Differenzierung anonymer Abfragen.
Fühlen Sie sich frei, Ihren Weg zum Ruhm zu kopieren/einzufügen, aber stellen Sie immer sicher, dass Sie die FROM-Klausel mit dem richtigen Tabellennamen aktualisieren. Wenn Sie mehr darüber erfahren möchten, wie diese Abfrage erstellt wurde, finden Sie hier die Aufschlüsselung:
- WÄHLEN Klausel:
- Abfrage: Ruft die Benutzerabfragen ab.
- is_anonymized_query AS anonymized: Benennt das Feld is_anonymized_query in anonymized um.
- SUM(impressions) AS impressions: Ruft die Gesamtzahl der Impressionen für jede Abfrage ab.
- SUM(Klicks) AS-Klicks: Ruft die Gesamtzahl der Klicks für jede Abfrage ab.
- SUM(Klicks)/NULLIF(SUM(Impressionen), 0) AS CTR: Berechnet die Click-Through-Rate (CTR) für jede Abfrage. Die Verwendung von NULLIF verhindert Fehler bei der Division durch Null.
- AUS Klausel:
- Gibt die Quelltabelle als mytable.searchconsole.searchdata_site_impression an.
- WO Klausel:
- Filtert die Daten so, dass sie nur Zeilen enthalten, deren data_date innerhalb der letzten 28 Tage ab dem aktuellen Datum liegt.
- GRUPPE BY-Klausel:
- Gruppiert die Ergebnisse nach Abfrage und anonymisiert. Dies ist erforderlich, da Aggregationen (SUM) durchgeführt werden und Sie die Gesamtsummen für jede eindeutige Kombination aus Abfrage und Anonymisierung wünschen.
- SORTIEREN NACH Klausel:
- Ordnet die Ergebnisse nach der Gesamtzahl der Klicks in absteigender Reihenfolge.
Umgang mit anonymisierten Abfragen
Laut Noah Learner liefert die Google Search Console API 25-mal mehr Daten als der GSC-Performance-Tab für dieselbe Suche und bietet so eine umfassendere Ansicht.
In BigQuery können Sie auch auf die Informationen zu anonymisierten Abfragen zugreifen.
Die Zeilen werden nicht weggelassen, was Analysten hilft, vollständige Summen von Impressionen und Klicks zu erhalten Anschließend aggregieren Sie die Daten.
Für SEO-Profis ist es von entscheidender Bedeutung, das Volumen anonymisierter Abfragen in Ihren Google Search Console (GSC)-Daten zu verstehen.
Wenn Google eine Suchanfrage anonymisiert, bedeutet das, dass der eigentliche Suchanfragentext in den Daten verborgen bleibt. Dies wirkt sich auf Ihre Analyse aus:
- Durch anonymisierte Abfragen entfällt die Möglichkeit, die Sprache der Suchanfragen zu analysieren und Erkenntnisse über Suchabsichten, Themen usw. zu gewinnen.
- Ohne die Abfragedaten verpassen Sie Möglichkeiten zur Identifizierung neuer Keywords und Optimierungsmöglichkeiten.
- Das Fehlen von Abfragedaten schränkt Ihre Fähigkeit ein, Suchanfragen mit der Seitenleistung zu verknüpfen.
Die erste Abfrage zählt die Anzahl der anonymisierten vs. Nicht anonymisierte Abfragen
SELECT
CASE
WHEN query is NULL AND is_anonymized_query = TRUE THEN "no query"
ELSE
"query"
END
AS annonymized_query,
count(is_anonymized_query) as query_count
FROM
`yourtable.searchdata_url_impression`
GROUP BY annonymized_query
Aufschlüsselung der Abfrage
In diesem Beispiel verwenden wir eine CASE-Anweisung, um für jede Zeile zu überprüfen, ob die Abfrage anonymisiert ist oder nicht.
Wenn ja, geben wir im Abfragefeld „keine Abfrage“ zurück; Wenn nicht, „Abfrage“.
Anschließend zählen wir die Anzahl der Zeilen, die jeder Abfragetyp in der Tabelle hat, und gruppieren die Ergebnisse basierend auf jeder Zeile. So sieht das Ergebnis aus:
” alt=”anonymisierte Abfragen werden in den Ergebnissen angezeigt” width=”480″ height=”106″ sizes=”(max-width: 480px) 100vw, 480px” srcset=”https://behmaster.com/wp-content/uploads/2024/02/localimages/anonymized-queries-659c17f4b5d51-sej-480×106.png 480w, https://behmaster.com/wp-content/uploads/2024/02/localimages/anonymized-queries-659c17f4b5d51-sej-680×150.png 680w, https://behmaster.com/wp-content/uploads/2024/02/localimages/anonymized-queries-659c17f4b5d51-sej-768×170.png 768w, https://behmaster.com/wp-content/uploads/2024/02/localimages/anonymized-queries-659c17f4b5d51-sej.png 791w” src=”https://behmaster.com/wp-content/uploads/2024/02/localimages/anonymized-queries-659c17f4b5d51-sej-480×106.png”>
Erweiterte Abfragen für SEO-Einblicke
BigQuery ermöglicht komplexe Analysen, die Sie in der GSC-Schnittstelle nicht durchführen können. Das bedeutet, dass Sie auch individuelle Informationen erstellen können, indem Sie Muster im Benutzerverhalten aufdecken.
Sie können Suchtrends, Saisonalität im Zeitverlauf und Möglichkeiten zur Keyword-Optimierung analysieren.
Hier sind einige Dinge, die Sie beachten sollten, um das Debuggen der von Ihnen eingerichteten Filter zu erleichtern:
- Das Datum könnte ein Problem sein. Es kann bis zu zwei Tage dauern, bis Sie die Daten haben, die Sie abfragen möchten. Wenn BigQuery in der oberen rechten Ecke angibt, dass Ihre Abfrage 0 MB zur Ausführung benötigen würde, bedeutet dies, dass die gewünschten Daten noch nicht vorhanden sind oder dass für Ihre Abfrage keine Daten vorhanden sind.
- Verwenden Sie die Vorschau, wenn Sie sehen möchten, welchen Wert ein Feld zurückgibt. Es zeigt Ihnen eine Tabelle mit den Daten.
- Die Länderabkürzungen, die Sie in BigQuery erhalten, haben ein anderes Format (ISO-3166-1-Alpha-3-Format) als Sie es gewohnt sind. Einige Beispiele: FRA für Frankreich, UKR für die Ukraine, USA für die Vereinigten Staaten usw.
- Möchten Sie „hübsche“ Anfragen erhalten? Klicken Sie auf der Registerkarte „Abfrage“ auf „Mehr“ und wählen Sie „Abfrage formatieren“. BigQuery übernimmt diesen Teil für Sie!
- Wenn Sie sofort mehr Abfragen wünschen, empfehle ich Ihnen, sich für den SEOlytics-Newsletter anzumelden, da es eine ganze Reihe von SQL-Abfragen gibt, die Sie verwenden können.
Abschluss
Durch die Analyse von GSC-Daten in BigQuery werden transformative SEO-Einblicke ermöglicht, sodass Sie die Suchleistung im großen Maßstab verfolgen können.
Indem Sie die hier beschriebenen Best Practices für Abfragen, Optimierung und Fehlerbehebung befolgen, können Sie diesen leistungsstarken Datensatz optimal nutzen.
Wenn Sie dies lesen, werden Sie nicht sofort zum Experten. Dies ist der erste Schritt in Ihrem Abenteuer!
Wenn Sie mehr wissen möchten, schauen Sie sich den Blog-Beitrag von Jake Peterson an, beginnen Sie kostenlos mit dem Lost at SQL-Spiel von Robin Lord zu üben oder bleiben Sie einfach auf dem Laufenden, denn es folgen noch ein paar weitere Artikel!
Wenn Sie Fragen oder Anregungen haben, zögern Sie nicht, uns dies mitzuteilen.
Mehr Ressourcen:
- So verfolgen Sie die Gesamtrankings in Data Studio mit BigQuery
- Content-Marketing-Metriken und -Analysen: 5 Arten von Datenerkenntnissen
- Warum Keyword-Recherche für SEO nützlich ist und wie man ein Ranking erzielt
Ausgewähltes Bild: Tee11/Shutterstock
![3 neue Möglichkeiten, KI für SEO und Keyword-Recherche zu nutzen [Ohne KI-Eingabeaufforderungen]](https://behmaster.com/wp-content/uploads/2023/11/494227-3-neue-moglichkeiten-ki-fur-seo-und-keyword-recherche-zu-nutzen-ohne-ki-eingabeaufforderungen-260x150.jpg)
