So verwenden Sie Big Query- und GSC-Daten für die Inhaltsleistungsanalyse

Wenn Sie schon immer von Leuten beeindruckt waren, die die Google Search Console API nutzen, um coole Dinge zu tun, ist dieser Artikel eine gute Lektüre für Sie.

Sie können BigQuery mit dem GSC-Massendatenexport verwenden, um einige der gleichen Vorteile zu nutzen, ohne die Hilfe eines Entwicklers zu benötigen.

Mit BigQuery können Sie große Datenmengen aus dem GSC-Massendatenexport effizient analysieren.

Sie haben keinen Echtzeit-Datenabruf; Das ist in unserem Szenario mit der API möglich, aber Sie können sich auf tägliche Datenimporte verlassen, was bedeutet, dass Sie mit aktuellen Informationen arbeiten.

Durch die Nutzung von BigQuery und dem GSC-Massendatenexport können Sie auf umfassende Suchanalysedaten zugreifen – das ist der Teil, von dem jeder auf LinkedIn schwärmt.

Laut Gus Pelogia, SEO-Produktmanager bei Indeed:

„Es ist wirklich ein Game Changer und eine großartige Gelegenheit, SQL zu lernen. Endlich können wir die Einschränkungen von GSC und externen SEO-Tools umgehen. Ich war überrascht, wie einfach es war, Daten abzurufen.“

Ein strukturierter Ansatz zur Verwendung von BigQuery- und Google Search Console (GSC)-Daten für die Inhaltsleistungsanalyse

Das Ziel dieses Artikels besteht nicht darin, Ihnen eine lange Liste von Fragen oder eine umfangreiche Schritt-für-Schritt-Anleitung für die Durchführung der intensivsten Prüfung aller Zeiten zu liefern.

Mein Ziel ist es, Ihnen den Einstieg in die Datenanalyse zu erleichtern, ohne die Einschränkungen, die die Benutzeroberfläche der Google Search Console mit sich bringt. Dazu müssen Sie fünf Schritte berücksichtigen:

• Identifizieren Sie Anwendungsfälle.
• Identifizieren Sie relevante Metriken für jeden Anwendungsfall.
• Fragen Sie die Daten ab.
• Erstellen Sie einen Looker Studio-Bericht, um Stakeholdern und Teams das Verständnis Ihrer Analyse zu erleichtern.
• Automatisieren Sie die Berichterstattung.

Das Problem, mit dem wir beim Einstieg in BigQuery oft konfrontiert sind, besteht darin, dass wir alle die Daten sofort abfragen möchten. Aber das reicht nicht.

Der wahre Mehrwert, den Sie erzielen können, liegt in einem strukturierten Ansatz für Ihre Datenanalyse.

1. Identifizieren Sie Anwendungsfälle

Es wird oft empfohlen, dass Sie Ihre Daten kennen, bevor Sie herausfinden, was Sie analysieren möchten. Das trifft zwar zu, wird Sie aber in diesem Fall einschränken.

Wir empfehlen Ihnen, zunächst den spezifischen Zweck und die Ziele für die Analyse der Inhaltsleistung zu bestimmen.

Anwendungsfall Nr. 1: Identifizieren Sie die Abfragen und Seiten, die die meisten Klicks bringen

„Ich glaube, dass jedes hochwertige SEO-Audit auch die Sichtbarkeit und Leistung der Website bei der Suche analysieren sollte. Sobald Sie diese Bereiche identifiziert haben, wissen Sie, worauf Sie sich bei Ihren Prüfungsempfehlungen konzentrieren müssen.“

Sagte Olga Zarr in ihrem Leitfaden „So prüfen Sie eine Website mit der Google Search Console“.

Dazu benötigen Sie die Abfragen Und die Seiten, die die meisten Klicks bringen.

Anwendungsfall Nr. 2: Berechnung des UQC

Wenn Sie Schwachstellen oder Chancen erkennen möchten, bietet die Berechnung der Unique Query Count (UQC) pro Seite wertvolle Erkenntnisse.

Sie wissen das bereits, weil Sie diese Art der Analyse in SEO-Tools wie SEMrush, SE Ranking, Dragon Metrics oder Serpstat verwenden (letzteres bietet eine tolle Anleitung zur Verwendung der Google Search Console zum Erstellen von Content-Plänen).

Es ist jedoch unglaublich nützlich, dies mit Ihren eigenen Google Search Console-Daten nachzubilden. Sie können den Prozess regelmäßig automatisieren und replizieren.

Dies hat Vorteile:

• Es hilft zu erkennen, welche Seiten ein vielfältiges Spektrum an Suchanfragen anziehen und welche möglicherweise stärker auf bestimmte Themen ausgerichtet sind.
• Seiten mit einem hohen UQC bieten möglicherweise Möglichkeiten für eine weitere Optimierung oder Erweiterung, um von einem breiteren Spektrum an Suchanfragen zu profitieren.
• Die Analyse des UQC pro Seite kann auch Aufschluss darüber geben, welche Positionsbereiche (z. B. Positionen 1–3, 4–10 usw.) hinsichtlich der Anzahl eindeutiger Abfragen eine größere Variabilität aufweisen. Dies kann dabei helfen, Optimierungsbemühungen zu priorisieren.
• Wenn Sie wissen, wie der UQC im Laufe des Jahres schwankt, können Sie Content-Planungs- und Optimierungsstrategien entwickeln, um diese an saisonale Trends anzupassen und Spitzenzeiten der Suchaktivität zu nutzen.
• Durch den Vergleich von UQC-Trends über verschiedene Zeiträume hinweg können Sie die Wirksamkeit der Bemühungen zur Inhaltsoptimierung beurteilen und Bereiche für weitere Verbesserungen identifizieren.

Anwendungsfall Nr. 3: Bewertung des Inhaltsrisikos

Jess Joyce, B2B- und SaaS-SEO-Expertin, verfügt über ein umsatzgenerierendes Content-Optimierungs-Framework, das sie mit Kunden teilt.

Einer der entscheidenden Schritte besteht darin, Seiten zu finden, auf denen von Quartal zu Quartal ein Rückgang der Klicks und Impressionen zu verzeichnen war. Sie verlässt sich dabei auf die Daten der Search Console.

Es wäre großartig, diese Abfrage zu erstellen, aber bevor wir uns darauf einlassen, müssen wir das Inhaltsrisiko bewerten.

Wenn Sie den Prozentsatz der Gesamtklicks, die von den obersten 1 % der Seiten einer Website stammen, basierend auf der Anzahl der Klicks, die jede Seite erhält, berechnen, können Sie schnell feststellen, ob Sie sich in der Gefahrenzone befinden – d. h., ob mit Überschreitungen potenzielle Risiken verbunden sind -Vertrauen auf eine kleine Teilmenge von Seiten.

Deshalb ist das wichtig:

• Eine übermäßige Abhängigkeit von einer kleinen Untergruppe von Seiten kann schädlich sein, da dadurch die Diversifizierung des Datenverkehrs auf der Website verringert wird und diese anfällig für Schwankungen oder Rückgänge des Datenverkehrs auf diesen spezifischen Seiten wird.
• Einschätzung der Gefahrenzone: Ein Prozentwert über 40 % deutet auf eine hohe Abhängigkeit des organischen Traffics von den obersten 1 % der Seiten hin, was auf ein potenzielles Risiko schließen lässt.
• Diese Abfrage liefert wertvolle Einblicke in die Verteilung des organischen Traffics auf einer Website.

2. Identifizieren Sie relevante Kennzahlen

Durch die Analyse Ihrer Inhalte können Sie erkennen, welche Inhalte effektiv sind und welche nicht, und so datengestützte Entscheidungen treffen.

Unabhängig davon, ob es darum geht, bestimmte Inhaltstypen zu erweitern oder einzustellen, können Sie durch die Nutzung der Erkenntnisse aus Ihren Daten Ihre Content-Strategie an die Vorlieben Ihres Publikums anpassen.

Metriken und Analysen im Content-Marketing liefern die wesentlichen Daten für die Erstellung von Inhalten, die bei Ihrem Publikum Anklang finden.

Anwendungsfall Nr. 1: Identifizieren Sie die Abfragen und Seiten, die die meisten Klicks bringen

Für diesen Anwendungsfall benötigen Sie einige ziemlich einfache Daten.

Lassen Sie uns alles hier auflisten:

• URLs und/oder Abfragen.
• Klicks.
• Eindrücke.
• Suchtyp: Wir möchten nur Websuchen, keine Bilder oder andere Suchtypen.
• Über einen bestimmten Zeitraum.

Der nächste Schritt besteht darin, zu bestimmen, aus welcher Tabelle Sie diese Informationen beziehen sollen. Denken Sie daran, wie wir bereits besprochen haben:

• 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.

In diesem Fall benötigen Sie die nach URL aggregierten Leistungsdaten, also die Verwendung von searchdata_url_impression Tisch.

Anwendungsfall Nr. 2: Berechnung des UQC

Für diesen Anwendungsfall müssen wir auch auflisten, was wir brauchen:

• URL: Wir wollen UQC pro Seite berechnen.
• Abfrage: Wir möchten die Abfragen, die jeder URL zugeordnet sind.
• Suchtyp: Wir wollen nur Websuchen, keine Bilder oder andere Arten.
• Wir müssen noch eine Tabelle auswählen. In diesem Fall benötigen Sie die nach URL aggregierten Leistungsdaten. Dies bedeutet also, dass Sie die verwenden müssen searchdata_url_impression Tisch.

Anwendungsfall Nr. 3: Bewertung des Inhaltsrisikos

Um den „Klickbeitrag der Top-1-%-Seiten nach Klicks“ zu berechnen, benötigen Sie die folgenden Messwerte:

• URL: Wird zur Berechnung des Klickbeitrags verwendet.
• Klicks: Die Anzahl der Klicks, die jede URL erhalten hat.
• Suchtyp: Gibt die Art der Suche an, normalerweise „WEB“ für Websuchen.
• Wir müssen noch eine Tabelle auswählen. In diesem Fall benötigen Sie die nach URL aggregierten Leistungsdaten. Dies bedeutet also, dass Sie die verwenden müssen searchdata_url_impression Tisch. (Stimme des Erzählers: Bemerken Sie einen Trend? Wir üben mit einer Tabelle, sodass Sie sich sehr gut damit vertraut machen können.)

3. Fragen Sie die Daten ab

Anwendungsfall Nr. 1: Identifizieren Sie die Abfragen und Seiten, die die meisten Klicks bringen

Lassen Sie uns alles zusammenfügen, um eine Abfrage zu erstellen, oder?

Sie möchten Seiten mit den meisten Klicks und Impressionen sehen. Dies ist ein einfacher Code, den Sie dem BigQuery-Handbuch von Marco Giordano entnehmen können, das über seinen Newsletter verfügbar ist.

Wir haben es leicht an unsere Bedürfnisse angepasst und um sicherzustellen, dass Sie die Kosten niedrig halten.

Kopieren Sie diese Abfrage, um die Seiten mit den meisten Klicks und Impressionen zu erhalten:

SELECT url, SUM(clicks) as total_clicks, SUM(impressions) as total_impressions FROM `pragm-ga4.searchconsole.searchdata_url_impression`WHERE search_type = 'WEB' and url NOT LIKE '%#%'AND data_date = "2024-02-13"GROUP BY urlORDER BY total_clicks DESC;

Es basiert auf einem der gängigsten SQL-Muster. Es ermöglicht Ihnen, nach einer Variablen zu gruppieren, in unserem Fall nach URLs. Anschließend können Sie die gewünschten aggregierten Metriken auswählen.

In unserem Fall haben wir Impressionen und Klicks angegeben, sodass wir Klicks und Impressionen zusammenfassen (zwei Spalten).

Lassen Sie uns die von Marco geteilte Anfrage aufschlüsseln:
SELECT-Anweisung

SELECT url, SUM(clicks) as total_clicks, SUM(impressions) as total_impressions: Gibt die Spalten an, die im Ergebnissatz abgerufen werden sollen.

• URL: Stellt die URL der Webseite dar.
• SUM(clicks) as total_clicks: Berechnet die Gesamtzahl der Klicks für jede URL und weist ihr einen Alias ​​total_clicks zu.
• SUM(impressions) as total_impressions: Berechnet die Gesamtzahl der Impressionen für jede URL und weist ihr einen Alias ​​„total_impressions“ zu.

FROM-Klausel

• FROM table_name`pragm-ga4.searchconsole.searchdata_url_impression`: Gibt die Tabelle an, aus der die Daten abgerufen werden sollen.
• Tabellenname: Stellt den Namen der Tabelle dar, die die relevanten Daten enthält.

• Wichtig zu wissen: Ersetzen Sie unseren Tabellennamen durch Ihren Tabellennamen.

WHERE-Klausel

• WHERE search_type = ‘WEB’ and url NOT LIKE ‘%#%’: Filtert die Daten basierend auf bestimmten Bedingungen.
• search_type = ‘WEB’: Stellt sicher, dass nur Daten enthalten sind, die sich auf Websuchergebnisse beziehen.
• URL NICHT LIKE ‘%#%’: Schließt URLs aus, deren Adresse „#“ enthält, und filtert Ankerlinks innerhalb von Seiten heraus.
• data_date = „13.02.2024“: Diese Bedingung filtert die Daten so, dass nur Datensätze für das Datum „13.02.2024“ enthalten sind. Dadurch wird sichergestellt, dass sich die Analyse ausschließlich auf die an diesem bestimmten Datum erfassten Daten konzentriert, was eine detailliertere Untersuchung der Webaktivitäten für diesen Tag ermöglicht.
• (Erzählerstimme: Wir empfehlen Ihnen, einen Termin auszuwählen, um die Kosten niedrig zu halten.)

Wichtig zu wissen: Wir empfehlen Ihnen, zwei Tage vor dem heutigen Datum auszuwählen, um sicherzustellen, dass Ihnen Daten zur Verfügung stehen.

GROUP BY-Klausel

• GRUPPE NACH URL: Gruppiert die Ergebnisse nach der URL-Spalte.
• Dadurch werden die Daten gruppiert, sodass die SUM-Funktion die Gesamtzahl der Klicks und Impressionen für jede eindeutige URL berechnet.

ORDER BY-Klausel

• ORDER BY total_clicks DESC: Gibt die Reihenfolge der Ergebnismenge basierend auf der Spalte „total_clicks“ in absteigender Reihenfolge an.
• Dadurch werden die URLs im Ergebnissatz basierend auf der Gesamtzahl der Klicks angeordnet, wobei die URL mit der höchsten Anzahl an Klicks zuerst angezeigt wird.

Diese Abfrage ist immer noch komplexer als die, die die meisten Anfänger erstellen würden, da sie nicht nur Daten aus der richtigen Tabelle abruft, sondern sie auch basierend auf bestimmten Bedingungen filtert (Entfernen von Ankerlinks und Suchtypen, die nicht ausschließlich WEB sind).

Anschließend berechnet es die Gesamtzahl der Klicks und Impressionen für jede URL, gruppiert die Ergebnisse nach URL und ordnet sie basierend auf der Gesamtzahl der Klicks in absteigender Reihenfolge.

Aus diesem Grund sollten Sie zunächst mit Ihrem Anwendungsfall beginnen, dann die Metriken ermitteln und dann die Abfrage schreiben.

Kopieren Sie diese SQL, um die Abfragen in GSC mit den meisten Klicks und Impressionen zu erhalten:

SELECT query, SUM(clicks) as total_clicks, SUM(impressions) as total_impressions FROM `pragm-ga4.searchconsole.searchdata_url_impression`WHERE search_type = 'WEB'AND data_date = "2024-02-13"GROUP BY queryORDER BY total_clicks DESC;

Dies ist die gleiche Abfrage, aber anstatt die URL hier abzurufen, rufen wir die Abfrage ab und aggregieren die Daten basierend auf diesem Feld. Das sieht man im GRUPPIERE NACH Abfrageteil.

Das Problem bei dieser Abfrage besteht darin, dass Sie wahrscheinlich viele „Null“-Ergebnisse erhalten. Es handelt sich hierbei um anonymisierte Anfragen. Sie können diese mithilfe dieser Abfrage entfernen:

SELECT query, SUM(clicks) as total_clicks, SUM(impressions) as total_impressions FROM `pragm-ga4.searchconsole.searchdata_url_impression`WHERE search_type = 'WEB'AND is_anonymized_query = falseAND data_date = "2024-02-13"GROUP BY QueryORDER BY total_clicks DESC;

Gehen wir nun noch einen Schritt weiter. Mir gefällt, wie Iky Tai, SEO bei GlobalShares, auf LinkedIn vorgegangen ist. Zunächst müssen Sie definieren, was die Abfrage bewirkt: Sie können die leistungsstarken URLs nach Klicks für einen ausgewählten Datumsbereich anzeigen.

Das SQL q Uery muss die Daten aus der angegebenen Tabelle abrufen. Filtern Sie es nach einem Datumsbereichkein bestimmtes Datum, berechnen Sie die Gesamtzahl der Impressionen und Klicks für jede URL, gruppieren Sie die Ergebnisse nach URL und ordnen Sie sie basierend auf der Gesamtzahl der Klicks in absteigender Reihenfolge.

Nachdem dies nun erledigt ist, können wir die SQL-Abfrage erstellen:

SELECTurl,SUM(impressions) AS impressions,SUM(clicks) AS clicksFROM`pragm-ga4.searchconsole.searchdata_url_impression`WHEREdata_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)GROUP BYurlORDER BYclicks DESC;

Bevor Sie Ihren Weg zum Ruhm kopieren und einfügen, nehmen Sie sich die Zeit, um zu verstehen, wie das aufgebaut ist:

SELECT-Anweisung

• SELECT url, SUM(impressions) AS impressions, SUM(clicks) AS clicks: Gibt die Spalten an, die im Ergebnissatz abgerufen werden sollen.
• URL: Stellt die URL der Webseite dar.
• SUM(Impressions) AS Impressions: Berechnet die Gesamtzahl der Impressionen für jede URL.
• SUM(Klicks) AS-Klicks: Berechnet die Gesamtzahl der Klicks für jede URL.

FROM-Klausel

• FROM searchconsole.searchdata_url_impression: Gibt die Tabelle an, aus der die Daten abgerufen werden sollen.
• (Erzählerstimme: Sie müssen den Namen Ihres Tisches ersetzen.)
• searchconsole.searchdata_url_impression: Stellt den Datensatz und die Tabelle dar, die die Suchdaten für einzelne URLs enthalten.

WHERE-Klausel

• WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY): Filtert die Daten basierend auf dem Datumsbereich.
• data_date: Stellt das Datum dar, an dem die Suchdaten aufgezeichnet wurden.
• ZWISCHEN: Gibt den Datumsbereich von vor drei Tagen (INTERVALL 3 TAG) bis gestern (INTERVALL 1 TAG) an.
• DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY): Berechnet das Datum vor drei Tagen ab dem aktuellen Datum.
• DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY): Berechnet das gestrige Datum aus dem aktuellen Datum.

Wichtig zu wissen: Wie bereits erwähnt, stehen Ihnen möglicherweise keine Daten für die letzten zwei Tage zur Verfügung. Das bedeutet, dass Sie dieses Intervall auf fünf und drei Tage statt auf drei und einen Tag ändern könnten.

GROUP BY-Klausel

GRUPPE NACH URL: Gruppiert die Ergebnisse nach der URL-Spalte.

• Dadurch werden die Daten gruppiert, sodass die SUM-Funktion Impressionen und Klicks für jede eindeutige URL berechnet.

ORDER BY-Klausel

ORDER BY clicks DESC: Gibt die Reihenfolge des Ergebnissatzes basierend auf der Spalte „Klicks“ in absteigender Reihenfolge an.

• Dadurch werden die URLs im Ergebnissatz basierend auf der Gesamtzahl der Klicks angeordnet, wobei die URL mit der höchsten Anzahl an Klicks zuerst angezeigt wird.

Wichtiger Hinweis: Wenn Sie zum ersten Mal loslegen, empfehle ich Ihnen, ein LLM wie Gemini oder ChatGPT zu verwenden, um Abfragen in verständliche Blöcke zu unterteilen.

Anwendungsfall Nr. 2: Berechnung des UQC

Hier ist ein weiteres nützliches Marco-Handbuch, das wir geändert haben, um Ihnen Daten für sieben Tage (den Wert einer Woche) zu liefern:

SELECT url, COUNT(DISTINCT(query)) as unique_query_count FROM `pragm-ga4.searchconsole.searchdata_url_impression`WHERE search_type = 'WEB' and url NOT LIKE '%#%'AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)GROUP BY urlORDER BY unique_query_count DESC;

” alt=”Komplexe SQL-Abfrage von BigQuery in der Schnittstelle” width=”1600″ height=”855″ sizes=”(max-width: 1600px) 100vw, 1600px” srcset=”https://behmaster.ir/wp-content/uploads/2024/03/localimages/sql-bq-complex-query-65d0821a8f5a2-sej.png 1600w, https://behmaster.ir/wp-content/uploads/2024/03/localimages/sql-bq-complex-query-65d0821a8f5a2-sej-480×257.png 480w, https://behmaster.ir/wp-content/uploads/2024/03/localimages/sql-bq-complex-query-65d0821a8f5a2-sej-680×363.png 680w, https://behmaster.ir/wp-content/uploads/2024/03/localimages/sql-bq-complex-query-65d0821a8f5a2-sej-768×410.png 768w, https://behmaster.ir/wp-content/uploads/2024/03/localimages/sql-bq-complex-query-65d0821a8f5a2-sej-1024×547.png 1024w” src=”https://behmaster.ir/wp-content/uploads/2024/03/localimages/sql-bq-complex-query-65d0821a8f5a2-sej.png”>Screenshot aus Google Cloud, Februar 2024

Dieses Mal werden wir die Abfrage nicht aufschlüsseln.

Diese Abfrage berechnet die Unique Query Count (UQC) pro Seite, indem sie die eindeutigen Abfragen zählt, die jeder URL zugeordnet sind, wobei URLs, die „#“ enthalten, und die Filterung für Websuchen ausgenommen werden.

Dies geschieht für einen Zeitraum von sieben Tagen, wobei berücksichtigt wird, dass für die beiden vorherigen Tage möglicherweise keine Daten verfügbar sind.

Die Ergebnisse werden dann nach der Anzahl der eindeutigen Suchanfragen in absteigender Reihenfolge sortiert und geben Aufschluss darüber, welche Seiten ein breites Spektrum an Suchanfragen anziehen.

Anwendungsfall Nr. 3: Bewertung des Inhaltsrisikos

Diese Abfrage berechnet den Prozentsatz der gesamten Klicks, der gemessen an den Klicks auf die obersten 1 % der URLs entfällt. Dies ist eine weitaus komplexere Abfrage als die vorherigen. Es stammt direkt aus Marcos Playbook:

WITH PageClicksRanked AS (SELECTurl,SUM(clicks) AS total_clicks,PERCENT_RANK() OVER (ORDER BY SUM(clicks) DESC) AS percent_rankFROM`pragm-ga4.searchconsole.searchdata_url_impression`WHEREsearch_type = 'WEB'AND url NOT LIKE '%#%'GROUP BYurl)SELECTROUND(SUM(CASE WHEN percent_rank <= 0.01 THEN total_clicks ELSE 0 END) / SUM(total_clicks) * 100, 2) AS percentage_of_clicksFROMPageClicksRanked;

Diese SQL-Abfrage ist komplexer, da sie erweiterte Techniken wie Fensterfunktionen, bedingte Aggregation und allgemeine Tabellenausdrücke umfasst.

Lassen Sie es uns aufschlüsseln:

Common Table Expression (CTE) – PageClicksRanked

• Dieser Teil der Abfrage erstellt einen temporären Ergebnissatz mit dem Namen PageClicksRanked.
• Es berechnet die Gesamtzahl der Klicks für jede URL und weist jeder URL basierend auf der Gesamtzahl der Klicks einen Prozentrang zu. Der Perzentilrang wird mithilfe der Fensterfunktion PERCENT_RANK() berechnet, die jeder Zeile innerhalb einer Partition des Ergebnissatzes einen relativen Rang zuweist.
• Ausgewählte Spalten:
  • URL: Die URL, von der die Klicks stammen.
  • SUM(clicks) AS total_clicks: Die Gesamtzahl der Klicks für jede URL.
  • PERCENT_RANK() OVER (ORDER BY SUM(clicks) DESC) AS percent_rank: Berechnet den Perzentilrang für jede URL basierend auf der Gesamtzahl der Klicks, geordnet in absteigender Reihenfolge.

Bedingungen

• search_type = 'WEB': Filtert die Daten so, dass nur Websuchergebnisse enthalten sind.
• AND url NOT LIKE '%#%': Schließt URLs, die „#“ enthalten, aus dem Ergebnissatz aus.

Gruppierung

• GRUPPE NACH URL: Gruppiert die Daten nach URL, um die Gesamtzahl der Klicks für jede URL zu berechnen.

Hauptabfrage

• In diesem Teil der Abfrage wird der Prozentsatz der Gesamtklicks berechnet, der gemessen an den Klicks auf das oberste 1 % der URLs entfällt.
• Es fasst die Gesamtzahl der Klicks für URLs zusammen, deren Prozentrang kleiner oder gleich 0,01 ist (oberstes 1 %), und dividiert diese durch die Gesamtsumme der Klicks über alle URLs hinweg. Anschließend wird das Ergebnis mit 100 multipliziert, um den Prozentsatz zu erhalten.

Spalten ausgewählt

• ROUND(SUM(CASE WHEN percent_rank <= 0.01 THEN total_clicks ELSE 0 END) / SUM(total_clicks) * 100, 2) AS percentage_of_clicks: Berechnet den Prozentsatz der Klicks, die auf die obersten 1 % der URLs entfallen. Die CASE-Anweisung filtert die URLs mit einem Prozentrang kleiner oder gleich 0,01 heraus und summiert dann die Gesamtklicks für diese URLs. Schließlich wird diese Summe durch die Gesamtsumme der Klicks über alle URLs dividiert und mit 100 multipliziert, um den Prozentsatz zu erhalten. Mit der ROUND-Funktion wird das Ergebnis auf zwei Dezimalstellen gerundet.

Quelle

• FROM PageClicksRanked: Verwendet den PageClicksRanked CTE als Datenquelle für Berechnungen.

(Stimme des Erzählers: Aus diesem Grund teilen wir komplexere Abfragen nicht sofort. Das Schreiben komplexer Abfragen erfordert sofort Wissen, Übung und Verständnis der zugrunde liegenden Daten und Geschäftsanforderungen.)

Um solche Abfragen zu schreiben, benötigen Sie:

• Ein solides Verständnis der SQL-Syntax: SELECT-Anweisungen, GROUP BY, Aggregatfunktionen, Unterabfragen und Fensterfunktionen zum Starten.
• Ein tiefes Verständnis des Datenbankschemas, weshalb wir uns die Zeit genommen haben, sie in einem anderen Artikel durchzugehen.
• Üben! Das Schreiben und Optimieren von SQL-Abfragen reicht aus. Das gilt auch für die Arbeit an Datensätzen und die Lösung analytischer Probleme! Übung bedeutet, einen iterativen Ansatz zu verfolgen, um Abfragen zu experimentieren, zu testen und zu verfeinern.
• Ein gutes Kochbuch haben: Abgesehen von guten Abfragen, die Sie optimieren und auf die Sie sich verlassen können.
• Problemlösende Fähigkeiten: Um den richtigen Ansatz zu finden, muss man komplexe Analyseaufgaben in überschaubare Schritte zerlegen können. Deshalb haben wir mit dem Fünf-Schritte-Framework begonnen.
• Eine Leistungsmentalität: Sie möchten die Abfrageleistung verbessern, insbesondere bei komplexen Abfragen für große Datenmengen. Wenn Sie dies nicht tun, könnten Sie am Ende viel Geld für BigQuery ausgeben.

4. Erstellen Sie Looker Studio-Dashboards

Sobald dies erledigt ist, können Sie Looker Studio verwenden, um Dashboards und Visualisierungen zu erstellen, die Ihre Content-Leistungsmetriken darstellen.

Sie können diese Dashboards anpassen, um Daten für verschiedene Stakeholder und Teams auf sinnvolle Weise darzustellen. Das bedeutet, dass Sie nicht der Einzige sind, der auf die Informationen zugreift.

Wir werden uns in einem anderen Artikel mit diesem Teil des Frameworks befassen.

Wenn Sie jedoch mit einem Looker Studio-Dashboard unter Verwendung von BigQuery-Daten beginnen möchten, hat Emad Sharaki sein fantastisches Dashboard geteilt. Wir empfehlen Ihnen, es auszuprobieren.

" alt="Emad BQ-Dashboard für SEOs" width="1600" height="1586" sizes="(max-width: 1600px) 100vw, 1600px" srcset="https://behmaster.ir/wp-content/uploads/2024/03/localimages/emad-bq-dashboard-65d0828465028-sej.png 1600w, https://behmaster.ir/wp-content/uploads/2024/03/localimages/emad-bq-dashboard-65d0828465028-sej-480x476.png 480w, https://www.searchenginejournal.com/wp-content/uploads/2024/02/emad-bq-dashboard-65d0828465028-sej-680x674.png 680w, https://www.searchenginejournal.com/wp-content/uploads/2024/02/emad-bq-dashboard-65d0828465028-sej-768x761.png 768w, https://behmaster.ir/wp-content/uploads/2024/03/localimages/emad-bq-dashboard-65d0828465028-sej-1024x1015.png 1024w" src="https://behmaster.ir/wp-content/uploads/2024/03/localimages/emad-bq-dashboard-65d0828465028-sej.png">Bild von Emad Sharaki, Februar 2024

5. Automatisieren Sie die Berichterstellung

Sobald Sie dies alles erledigt haben, können Sie geplante Abfragen in BigQuery einrichten, um in regelmäßigen Abständen automatisch die in den Tabellen vorhandenen GSC-Daten abzurufen.

Dies bedeutet, dass Sie die Erstellung und Verteilung von Berichten innerhalb Ihres Unternehmens automatisieren können.

Sie können sich vorerst die offizielle Dokumentation für diesen Teil ansehen. Wir werden dies zu einem späteren Zeitpunkt in einem anderen speziellen Artikel behandeln.

Der einzige Tipp, den wir hier geben, ist, dass Sie Abfragen nach dem typischen Exportfenster planen sollten, um sicherzustellen, dass Sie die aktuellsten verfügbaren Daten abfragen.

Um die Aktualität der Daten zu überwachen, sollten Sie die Abschlusszeiten des Exports im Exportprotokoll von BigQuery verfolgen.

Sie können die Reporting-Automatisierung nutzen, um andere Teams bei der Erstellung und Optimierung von Inhalten zu unterstützen. Gianna Brachetti-Truskawa, SEO PM und Strategin, unterstützt Redaktionsteams durch die direkte Integration von Berichten in das CMS.

Dies bedeutet, dass Redakteure bestehende Artikel nach Leistung filtern und ihre Optimierungsbemühungen entsprechend priorisieren können. Ein weiteres zu berücksichtigendes Automatisierungsberichtselement ist die Integration mit Jira, um Ihre Leistung mit einem Dashboard mit benutzerdefinierten Regeln zu verbinden.

Dies bedeutet, dass Artikel an die Spitze des Backlogs gezogen werden können und dass saisonale Themen rechtzeitig zum Backlog hinzugefügt werden können, um für Dynamik zu sorgen.

Weitergehen

Natürlich benötigen Sie mehr Anwendungsfälle und ein tieferes Verständnis der Art der Inhaltsprüfung, die Sie durchführen möchten.

Der Rahmen, den wir in diesem Artikel vorgestellt haben, ist jedoch eine großartige Möglichkeit, sicherzustellen, dass die Dinge strukturiert bleiben. Wenn Sie noch weiter gehen möchten, hat Lazarina Stoy, SEO-Datenexpertin, ein paar Tipps für Sie:

„Bei der Analyse der Inhaltsleistung ist es wichtig zu verstehen, dass nicht alle Inhalte gleich sind. Verwenden Sie SQL Case/When-Anweisungen, um Teilmengen des Inhalts basierend auf dem Seitentyp (Unternehmensseite, Blogbeitrag, Fallstudie usw.), Inhaltsstrukturmustern (Konzepterklärung, Nachrichtenartikel, Tutorial, Leitfaden usw.), Titelmustern usw. zu erstellen. Zielabsicht, Zielgruppen, Inhaltscluster und jede andere Art von Klassifizierung, die für Ihren Inhalt einzigartig ist.

Auf diese Weise können Sie überwachen und Fehler beheben, wenn Sie leistungsschwache Muster erkennen, und die Bemühungen, die sich auszahlen, verstärken, wann immer solche erkannt werden.“

Wenn Sie auf der Grundlage dieser Überlegungen Abfragen erstellen, teilen Sie sie uns mit, damit wir sie in das Kochbuch der Abfragen aufnehmen können, die für die Inhaltsleistungsanalyse verwendet werden können!

Abschluss

Wenn Sie diesem strukturierten Ansatz folgen, können Sie BigQuery- und GSC-Daten effektiv nutzen, um die Leistung Ihrer Inhalte zu analysieren und zu optimieren und gleichzeitig die Berichterstellung zu automatisieren, um Stakeholder auf dem Laufenden zu halten.

Denken Sie daran: Durch das Sammeln der Abfragen aller anderen werden Sie nicht über Nacht zum BigQuery-Profi. Ihr Wert liegt darin, Anwendungsfälle herauszufinden.

Danach können Sie die benötigten Metriken ermitteln und die von anderen erstellten Abfragen optimieren oder Ihre eigenen schreiben. Sobald Sie das in der Tasche haben, ist es an der Zeit, ein Profi zu werden, indem Sie anderen ermöglichen, das von Ihnen erstellte Dashboard zur Visualisierung Ihrer Ergebnisse zu verwenden.

Sie werden beruhigt sein, wenn Sie einige dieser Aktionen automatisieren und Ihre Fähigkeiten und Fragen noch weiter entwickeln!

Mehr Ressourcen:

• Ein vollständiger Google Search Console-Leitfaden für SEO-Profis
• So mischen Sie Daten in Looker Studio mit praktischen Beispielen
• So führen Sie ein SEO-Audit durch: Die ultimative Checkliste

Ausgewähltes Bild: Suvit Topaiboon/Shutterstock