Wie SEO-Experten ChatGPT für BigQuery nutzen können (mit Beispielen)

KI verändert alle Bereiche, indem sie Fähigkeiten (wie Codierung oder Datenvisualisierung) für alle zugänglich macht, die früher nicht verfügbar waren.

Ein KI-Operator, der die richtigen Eingabeaufforderungen ausführen kann, kann Aufgaben mit niedrigem und mittlerem Schwierigkeitsgrad ausführen, sodass der Fokus stärker auf strategische Entscheidungen gelegt werden kann.

In dieser Anleitung zeigen wir Ihnen Schritt für Schritt, wie Sie KI-Chatbots mit ChatGPT als Beispiel verwenden, um komplexe BigQuery-Abfragen für Ihre SEO-Berichtsanforderungen auszuführen.

Wir werden zwei Beispiele betrachten:

• So analysieren Sie den Verkehrsrückgang aufgrund der Auswirkungen des Google-Algorithmus
• So kombinieren Sie Suchverkehrsdaten mit Engagement-Metriken von GA4

Darüber hinaus erhalten Sie einen Überblick darüber, wie Sie mithilfe von Chatbots den Aufwand beim Ausführen von SEO-Berichten verringern können.

Warum müssen Sie BigQuery lernen?

SEO-Tools wie die Google Search Console oder Google Analytics 4 verfügen über zugängliche Benutzeroberflächen, mit denen Sie auf Daten zugreifen können. Aber oft schränken sie Ihre Möglichkeiten ein und zeigen unvollständige Daten an, was normalerweise als Datenstichproben bezeichnet wird.

In GSC geschieht dies, weil das Tool anonymisierte Abfragen auslässt und die Tabellenzeilen auf bis zu 1.000 Zeilen begrenzt.

Screenshot aus der Google Search Console, Mai 2024

Mithilfe von BigQuery können Sie dieses Problem lösen und beliebig komplexe Berichte ausführen. Dadurch wird das Problem der Datenstichproben vermieden, das bei der Arbeit mit großen Websites recht häufig auftritt.

(Alternativ können Sie versuchen, Looker Studio zu verwenden, aber der Zweck dieses Artikels besteht darin, zu veranschaulichen, wie Sie ChatGPT für BigQuery betreiben können.)

Für diesen Artikel gehen wir davon aus, dass Sie Ihre GSC- und GA4-Konten bereits mit BigQuery verbunden haben. Wenn Sie dies noch nicht getan haben, können Sie unsere Anleitungen dazu lesen:

• Erste Schritte mit GSC-Abfragen in BigQuery.
• Verknüpfen von GA4 mit BigQuery.

SQL-Grundlagen

Wenn Sie mit Structured Query Language (SQL) vertraut sind, können Sie diesen Abschnitt überspringen. Für alle, die dies nicht tun, gibt es hier eine Kurzreferenz zu SQL-Anweisungen:

Damit Sie sich mit den Bedingungen vertraut machen können, verwenden wir diese:

Sehen wir uns nun Beispiele an, wie Sie BigQuery über ChatGPT verwenden können.

1. Wie man Tra analysierenRückgang der ffic-Zahlen aufgrund der Auswirkungen des Google-Algorithmus

Wenn Sie von einem Google-Algorithmus-Update betroffen sind, sollten Sie zunächst Berichte zu den betroffenen Seiten ausführen und analysieren, warum Sie betroffen sind.

Denken Sie daran: Das Schlimmste, was Sie tun können, ist, in Panik sofort mit der Änderung der Website zu beginnen. Dies kann zu Schwankungen im Suchverkehr führen und die Analyse der Auswirkungen noch schwieriger machen.

Wenn Ihr Index weniger Seiten enthält, ist die Verwendung der GSC-UI-Daten für die Analyse Ihrer Daten möglicherweise ausreichend. Wenn Ihr Index jedoch Zehntausende Seiten umfasst, können Sie nicht mehr als 1.000 Datenzeilen (Seiten oder Abfragen) exportieren.

Angenommen, Sie verfügen über Daten einer Woche seit der Einführung des Algorithmus-Updates und möchten diese mit den Daten der Vorwoche vergleichen. Um diesen Bericht in BigQuery auszuführen, können Sie mit dieser einfachen Eingabeaufforderung beginnen:

Imagine you are a data analyst experienced in Google Analytics 4 (GA4), Google Search Console, SQL, and BigQuery.Your task is to generate an SQL query to compare 'WEB' Search Console data for the periods '2024-05-08' to '2024-05-20' and '2024-04-18' to '2024-04-30'. Extract the total clicks, impressions, and average position for each URL for each period. Additionally, calculate the differences in these metrics between the periods for each URL (where average position should be calculated as the sum of positions divided by the sum of impressions).Details:BigQuery project name: use_your_bigquery_projectnameDataset name: searchconsoleTable name: searchdata_url_impressionPlease provide the SQL query that meets these requirements.

Sobald Sie einen SQL-Code erhalten, kopieren Sie ihn und fügen ihn in den BigQuery-SQL-Editor ein. Ich bin mir jedoch sicher, dass der anfängliche Code, den Sie erhalten, Fehler enthält. Beispielsweise stimmen die Spaltennamen der Tabelle möglicherweise nicht mit denen in Ihrem BigQuery-Datensatz überein.

Fehler in BigQuery SQL, wenn der Spaltenname nicht mit der Datensatzspalte übereinstimmt.

Solche Dinge passieren recht häufig, wenn man Codierungsaufgaben über ChatGPT durchführt. Sehen wir uns nun an, wie Sie solche Probleme schnell beheben können.

Klicken Sie einfach auf Ihren Datensatz im linken und rechten Bereich, wählen Sie alle Spalten auf der rechten Seite aus und klicken Sie auf Als Tabelle kopieren.

So wählen Sie alle Spalten der Tabelle in BigQuery aus.

Sobald Sie es haben, kopieren Sie es einfach und fügen Sie es als Folgeaufforderung ein und klicken Sie auf eingeben.

Generate SQL again by taking into account that my table schema is as follows : "fullname mode type descriptiondata_date DATE site_url STRING url STRING query STRING is_anonymized_query BOOLEAN is_anonymized_discover BOOLEAN country STRING search_type STRING device STRING is_amp_top_stories BOOLEAN is_amp_blue_link BOOLEAN is_job_listing BOOLEAN is_job_details BOOLEAN is_tpf_qa BOOLEAN is_tpf_faq BOOLEAN is_tpf_howto BOOLEAN is_weblite BOOLEAN is_action BOOLEAN is_events_listing BOOLEAN is_events_details BOOLEAN is_search_appearance_android_app BOOLEAN is_amp_story BOOLEAN is_amp_image_result BOOLEAN is_video BOOLEAN is_organic_shopping BOOLEAN is_review_snippet BOOLEAN is_special_announcement BOOLEAN is_recipe_feature BOOLEAN is_recipe_rich_snippet BOOLEAN is_subscribed_content BOOLEAN is_page_experience BOOLEAN is_practice_problems BOOLEAN is_math_solvers BOOLEAN is_translated_result BOOLEAN is_edu_q_and_a BOOLEAN impressions INTEGER clicks INTEGER sum_position INTEGER is_product_snippets BOOLEAN is_merchant_listings BOOLEAN is_learning_videos BOOLEAN "

Dadurch wird das generierte SQL gemäß der GSC-Tabellenstruktur wie folgt korrigiert:

WITH period1 AS ( SELECT    url,   SUM(clicks) AS clicks,   SUM(impressions) AS impressions,   IFNULL(SUM(sum_position) / NULLIF(SUM(impressions), 0), 0) AS avg_position FROM    `use_your_bigquery_projectname.searchconsole.searchdata_url_impression` WHERE    data_date BETWEEN '2024-05-08' AND '2024-05-20'  AND search_type = 'WEB'  GROUP BY   url), period2 AS ( SELECT   url,  SUM(clicks) AS clicks,  SUM(impressions) AS impressions,  IFNULL(SUM(sum_position) / NULLIF(SUM(impressions), 0), 0) AS avg_position FROM   `use_your_bigquery_projectname.searchconsole.searchdata_url_impression`  WHERE   data_date BETWEEN '2024-04-18' AND '2024-04-30' AND search_type = 'WEB' GROUP BY  url),comparison AS ( SELECT  COALESCE(p1.url, p2.url) AS url,  IFNULL(p1.clicks, 0) AS clicks_period1,  IFNULL(p2.clicks, 0) AS clicks_period2,  IFNULL(p1.impressions, 0) AS impressions_period1,  IFNULL(p2.impressions, 0) AS impressions_period2,  IFNULL(p1.avg_position, 0) AS avg_position_period1,  IFNULL(p2.avg_position, 0) AS avg_position_period2,  IFNULL(p1.clicks, 0) - IFNULL(p2.clicks, 0) AS clicks_diff,  IFNULL(p1.impressions, 0) - IFNULL(p2.impressions, 0) AS impressions_diff,  IFNULL(p1.avg_position, 0) - IFNULL(p2.avg_position, 0) AS avg_position_diff FROM  period1 p1 FULL OUTER JOIN   period2 p2 ON  p1.url = p2.url)SELECT  url, clicks_period1, clicks_period2, clicks_diff, impressions_period1, impressions_period2, impressions_diff, avg_position_period1, avg_position_period2, avg_position_diffFROM  comparisonORDER BY  url;

Führen Sie es jetzt aus und genießen Sie die Daten, indem Sie sie entweder in CSV oder in Google Sheets exportieren.

So führen Sie SQL in BigQuery aus.

Wenn Sie Millionen von URLs haben, können Sie möglicherweise nicht in Google Sheets oder CSV-Export arbeiten, da die Daten zu groß sind. Außerdem haben diese Apps Beschränkungen hinsichtlich der Anzahl der Zeilen, die Sie in einem einzelnen Dokument haben können. In diesem Fall können Sie die Ergebnisse als BigQuery-Tabelle speichern und mit Looker Studio eine Verbindung dazu herstellen, um die Daten anzuzeigen.

Denken Sie jedoch daran, dass BigQuery ein Freemium-Dienst ist. Bis zu 1 TB verarbeiteter Abfragedaten pro Monat ist der Dienst kostenlos. Sobald Sie dieses Limit überschreiten, wird Ihre Kreditkarte automatisch auf Basis Ihrer Nutzung belastet.

Das bedeutet, wenn Sie Ihr BigQuery mit Looker Studio verbinden und dort Ihre Daten durchsuchen, wird dies jedes Mal auf Ihre Rechnung angerechnet, wenn Sie Ihr Looker-Dashboard öffnen.

Deshalb verwende ich bei Exporten mit einigen Zehntausend oder Hunderttausenden Zeilen gerne Google Sheets. Ich kann es zur Datenvisualisierung und -zusammenführung problemlos mit Looker Studio verbinden, und das wird mir nicht in Rechnung gestellt.

Wenn Sie ChatGPT Plus haben, können Sie einfach dieses benutzerdefinierte GPT verwenden, das ich erstellt habe und das Tabellenschemata für GA4 und Search Console berücksichtigt. In der obigen Anleitung bin ich davon ausgegangen, dass Sie die kostenlose Version verwenden, und sie hat veranschaulicht, wie Sie ChatGPT insgesamt zum Ausführen von BigQuery verwenden können.

Falls Sie wissen möchten, was sich in diesem benutzerdefinierten GPT befindet, finden Sie hier den Screenshot des Backends.

Benutzerdefiniertes GPT mit BigQuery-Tabellenschemata.

Nichts Kompliziertes – Sie müssen lediglich im oben erläuterten Schritt Tabellen aus BigQuery als JSON kopieren und in das benutzerdefinierte GPT hochladen, damit es auf die Tabellenstruktur verweisen kann. Darüber hinaus gibt es eine Eingabeaufforderung, die GPT auffordert, beim Erstellen von Abfragen auf die angehängten JSON-Dateien zu verweisen.

Dies ist ein weiteres Beispiel dafür, wie Sie mit ChatGPT Aufgaben effektiver erledigen und sich wiederholende Aufgaben vermeiden können.

Wenn Sie mit einem anderen Datensatz (anders als GA4 oder GSC) arbeiten müssen und kein SQL kennen, können Sie das Tabellenschema von BigQuery in ChatGPT hochladen und SQLs erstellen, die speziell auf diese Tabellenstruktur zugeschnitten sind. Ganz einfach, nicht wahr?

Als Hausaufgabe schlage ich vor, dass Sie analysieren, welche Abfragen von KI-Übersichten betroffen sind.

In der Tabelle der Google Search Console gibt es hierfür kein Unterscheidungsmerkmal, Sie können jedoch eine Abfrage ausführen, um zu sehen, welche Seiten zwar nicht an Ranking verloren haben, aber nach dem 14. Mai 2024, als Google AI Overviews einführte, einen deutlichen Rückgang der Klickrate aufwiesen.

Sie können den zweiwöchigen Zeitraum nach dem 14. Mai mit den zwei Wochen davor vergleichen. Es besteht immer noch die Möglichkeit, dass der CTR-Rückgang auf andere Suchfunktionen zurückzuführen ist, z. B. dass ein Konkurrent ein Featured Snippet erhält, aber Sie sollten genügend gültige Fälle finden, in denen Ihre Klicks von AI Overviews (früher Search Generative Experience oder „SGE“) beeinflusst wurden.

2. Wie man Suche kombinierenh Verkehrsdaten mit Engagement-Metriken von GA4

Bei der Analyse des Suchverkehrs ist es wichtig zu verstehen, wie stark sich Benutzer mit Inhalten beschäftigen, da Benutzerinteraktionssignale Rankingfaktoren sind. Bitte beachten Sie, dass ich nicht die genauen Metriken meine, die in GA4 definiert sind.

Allerdings können die Engagement-Metriken von GA4 – wie etwa die „durchschnittliche Engagement-Dauer pro Sitzung“, also die durchschnittliche Zeit, die Ihre Website im Browser eines Nutzers im Fokus war – Hinweise darauf geben, ob Ihre Artikel für die Nutzer gut genug zum Lesen sind.

Wenn der Wert zu niedrig ist, liegt möglicherweise ein Problem mit Ihren Blogseiten vor, weshalb sie von den Benutzern nicht gelesen werden.

Wenn Sie diese Metrik mit Daten der Search Console kombinieren, stellen Sie möglicherweise fest, dass Seiten mit niedrigem Ranking auch eine niedrige durchschnittliche Interaktionsdauer pro Sitzung aufweisen.

Bitte beachten Sie, dass GA4 und GSC unterschiedliche Attributionsmodelle haben. GA4 verwendet Last-Data-gesteuerte oder Last-Click-Attributionsmodelle. Das bedeutet, wenn jemand von Google einmal auf eine Artikelseite gelangt und dann zwei weitere Male direkt zurückkehrt, kann GA4 alle drei Besuche Google zuordnen, während GSC nur einen meldet.

Die Genauigkeit ist also nicht 100% und möglicherweise nicht für Unternehmensberichte geeignet. Wenn Sie jedoch neben den GSC-Daten auch Engagement-Metriken von GA4 haben, erhalten Sie wertvolle Informationen für die Analyse der Korrelationen Ihrer Rankings mit dem Engagement.

Die Verwendung von ChatGPT mit BigQuery erfordert ein wenig Vorbereitung. Bevor wir uns auf die Eingabeaufforderung stürzen, schlage ich vor, dass Sie lesen, wie GA4-Tabellen strukturiert sind, da diese nicht so einfach sind wie die Tabellen von GSC.

Es gibt eine Spalte event_params, die einen Datensatztyp hat und Dimensionen wie page_location, ga_session_idund engagement_time_msec. Es verfolgt, wie lange ein Benutzer aktiv mit Ihrer Website interagiert.

event_params-Schlüssel engagement_time_msec ist nicht die Gesamtzeit auf der Website, sondern die Zeit, die für bestimmte Interaktionen (wie Klicken oder Scrollen) aufgewendet wird, wobei jede Interaktion eine neue Zeiteinheit hinzufügt. Es ist, als würde man alle kleinen Momente zusammenzählen, in denen Benutzer Ihre Website oder App aktiv nutzen.

Wenn wir diese Metrik summieren und den Durchschnitt aller Seitensitzungen berechnen, erhalten wir die durchschnittliche Interaktionsdauer pro Sitzung.

Wenn Sie nun verstehen, engagement_time_msec bitten wir ChatGPT, uns beim Erstellen einer Abfrage zu helfen, die die „durchschnittliche Engagement-Zeit pro Sitzung“ von GA4 für jede URL abruft und sie mit GSC-Suchleistungsdaten von Artikeln kombiniert.

Die Eingabeaufforderung, die ich verwenden würde, lautet:

Imagine you are a data analyst experienced in Google Analytics 4 (GA4), Google Search Console, SQL, and BigQuery.Compose a SQL query that pulls the following data from Google Search Console for each URL for the previous 7 days, excluding the current day:1. Clicks,2. Impressions,3. Average position (calculated as the sum of positions divided by the sum of impressions).From GA4 BigQuery table unnest from event_params ga_session_id, engagement_time_msec and page_location.Select only rows which have engagement_time_msec set as not null group all sessions with the same IDs and page_location and SUM engagement_time_msec and devides to SUM of sessions number Join GA4 and Google Search Console data by URLs for the same period. Additonally Optimize the query to pull from GA4's table partitions and not query the entire table.Details:1. BigQuery project name: use_your_bigquery_projectname 2. GA4 dataset name: use_your_ga4_bigquery_dataset_name3. GA4 table name: use_your_ga4_bigquery_table_name_under_dataset3. Google Search Console dataset name: use_your_gsc_bigquery_table_name_under_dataset3. Google Search Console table name: use_your_gsc_bigquery_table_name_under_dataset4. Here is BigQuery tables schemas for GA4: [copy table schema here]5. Here is BigQuery tables schemas for Google Search Console: [copy table schema here]

Nachdem ich die Daten kopiert und in BigQuery eingefügt hatte, erhielt ich Ergebnisse, bei denen die „durchschnittliche Interaktionszeit pro Sitzung“ alle Nullen enthielt. Offenbar benötigt ChatGPT also mehr Kontext und Anleitungen zur Funktionsweise von GA4.

Ich habe dabei geholfen, zusätzliches Wissen aus der offiziellen Dokumentation von GA4 zur Berechnung von engagement_time_msec als Folgefrage bereitzustellen. Ich habe das Dokument kopiert und in die Folgeaufforderung eingefügt und darum gebeten, beim Erstellen der Abfrage auf dieses Wissen zurückzugreifen, was geholfen hat. (Wenn Sie einen Syntaxfehler erhalten, kopieren/fügen Sie ihn einfach als Folgefrage ein und bitten Sie darum, ihn zu beheben.)

Nach 15 Minuten Aufwand erhielt ich das richtige SQL:

WITH-- Step 1: Filter GA4 data for the last 7 days and unnest event_paramsga4_data AS (  SELECT    event_date,    event_timestamp,    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location  FROM    `your_bq_project_name.your_bq_ga4_dataset.events_*`  WHERE     _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY))    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') IS NOT NULL),-- Step 2: Aggregate GA4 data by session ID and page locationaggregated_ga4_data AS (  SELECT    page_location,    SUM(engagement_time_msec/1000) / NULLIF(COUNT(DISTINCT ga_session_id), 0) AS avg_engagement_time_msec  FROM    ga4_data  GROUP BY    page_location),-- Step 3: Filter GSC data for the last 7 days and select urls which had clicksgsc_data AS (  SELECT    url,    SUM(clicks) AS clicks,    SUM(impressions) AS impressions,    SUM(sum_position) / SUM(impressions) AS avg_position  FROM    `your_bq_project_name.searchconsole.searchdata_url_impression`  WHERE    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)    and     clicks > 0  GROUP BY    url)-- Joining Google Search Console data with GA4 data by page_location and url SELECT  gsc.url,  gsc.clicks,  gsc.impressions,  gsc.avg_position,  ga4.avg_engagement_time_msecFROM  gsc_data AS gscLEFT JOIN  aggregated_ga4_data AS ga4ON  gsc.url = ga4.page_locationORDER BY  gsc.clicks DESC;

Dadurch werden GSC-Daten mit Engagement-Metriken von GA4 abgerufen.

Search Console kombinierte Daten mit GA4

Bitte beachten Sie, dass möglicherweise Abweichungen zwischen den Zahlen in der GA4-Benutzeroberfläche und den aus BigQuery-Tabellen abgefragten Daten auftreten.

Dies liegt daran, dass GA4 sich auf „Aktive Benutzer“ konzentriert und seltene Datenpunkte in die Kategorie „(Sonstige)“ gruppiert, während BigQuery alle Rohdaten anzeigt. GA4 verwendet auch modellierte Daten für Lücken, wenn keine Zustimmung vorliegt, was bei BigQuery nicht der Fall ist.

Darüber hinaus kann GA4 Daten für schnellere Berichte abtasten, während BigQuery alle Daten einbezieht. Diese Unterschiede bedeuten, dass GA4 einen schnellen Überblick bietet, während BigQuery detaillierte Analysen liefert. In diesem Artikel erfahren Sie, warum das so ist.

Vielleicht können Sie versuchen, die Abfragen so zu ändern, dass nur aktive Benutzer einbezogen werden, um die Ergebnisse der GA4-Benutzeroberfläche einen Schritt näher zu bringen.

Alternativ können Sie Looker Studio zum Mischen von Daten verwenden, allerdings hat es bei sehr großen Datensätzen Einschränkungen. BigQuery bietet Skalierbarkeit durch effiziente Verarbeitung von Terabyte an Daten, wodurch es ideal für umfangreiche SEO-Berichte und detaillierte Analysen ist.

Seine erweiterten SQL-Funktionen ermöglichen komplexe Abfragen für tiefere Einblicke, mit denen Looker Studio oder andere Dashboarding-Tools nicht bieten können.

Abschluss

Die Verwendung der Codierungsfunktionen von ChatGPT zum Erstellen von BigQuery-Abfragen für Ihre Berichtsanforderungen erweitert Ihre Möglichkeiten und eröffnet neue Horizonte, in denen Sie mehrere Datenquellen kombinieren können.

Dies zeigt, wie ChatGPT komplexe Datenanalyseaufgaben rationalisieren kann, sodass Sie sich auf strategische Entscheidungen konzentrieren können.

Gleichzeitig haben wir aus diesen Beispielen gelernt, dass KI-Chatbots unbedingt von Menschen bedient werden müssen, da es zu Halluzinationen oder falschen Antworten kommen kann.

Mehr Ressourcen:

• Erste Schritte mit GSC-Abfragen in BigQuery
• Nutzung generativer KI-Tools für SEO
• Fortgeschrittenes technisches SEO: Ein vollständiger Leitfaden

Vorgestelltes Bild: NicoElNino/Shutterstock