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:
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.
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:
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:
| Stellungnahme | Beschreibung |
| WÄHLEN | Ruft Daten aus Tabellen ab |
| EINFÜGEN | Fügt neue Daten in eine Tabelle ein |
| UNNEST | Reduziert ein Array in eine Reihe von Zeilen |
| AKTUALISIEREN | Aktualisiert vorhandene Daten innerhalb einer Tabelle |
| LÖSCHEN | Löscht Daten aus einer Tabelle |
| ERSTELLEN | Erstellt eine neue Tabelle oder Datenbank |
| ÄNDERN | Ändert eine vorhandene Tabelle |
| FALLEN | Löscht eine Tabelle oder eine Datenbank. |
Damit Sie sich mit den Bedingungen vertraut machen können, verwenden wir diese:
| Zustand | Beschreibung |
| WO | Filtert Datensätze nach bestimmten Bedingungen |
| UND | Kombiniert zwei oder mehr Bedingungen, wobei alle Bedingungen erfüllt sein müssen |
| ODER | Kombiniert zwei oder mehr Bedingungen, wobei mindestens eine Bedingung erfüllt sein muss |
| NICHT | Negiert eine Bedingung |
| WIE | Sucht nach einem angegebenen Muster in einer Spalte. |
| IN | Überprüft, ob ein Wert innerhalb einer Wertemenge liegt |
| ZWISCHEN | Auswählen von Werten innerhalb eines bestimmten Bereichs |
| IST NULL | Prüft auf Nullwerte |
| IST NICHT NULL | Prüft auf Werte ungleich Null |
| EXISTIERT | Überprüft, ob eine Unterabfrage Datensätze zurückgibt |
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_projectname
Dataset name: searchconsole
Table name: searchdata_url_impression
Please 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 description
data_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_diff
FROM
comparison
ORDER 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 in diesem benutzerdefinierten GPT enthalten ist, 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-Kennzahlen 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 die Benutzer sie nicht lesen.
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 Zuordnungsmodelle für die Herkunft haben. GA4 verwendet das Last-Click-Zuordnungsmodell. Das bedeutet, dass GA4, wenn jemand von Google aus einmal auf eine Artikelseite gelangt und dann zwei weitere Male direkt darauf zurückkommt, alle drei Besuche Google zuordnen kann, 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_name
3. GA4 table name: use_your_ga4_bigquery_table_name_under_dataset
3. Google Search Console dataset name: use_your_gsc_bigquery_table_name_under_dataset
3. Google Search Console table name: use_your_gsc_bigquery_table_name_under_dataset
4. 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_params
ga4_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 location
aggregated_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 clicks
gsc_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_msec
FROM
gsc_data AS gsc
LEFT JOIN
aggregated_ga4_data AS ga4
ON
gsc.url = ga4.page_location
ORDER BY
gsc.clicks DESC;
Dadurch werden GSC-Daten mit Engagement-Metriken von GA4 abgerufen.
Search Console kombinierte Daten mit GA4Bitte 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 die effiziente Verarbeitung von Terabyte an Daten und ist daher ideal für umfangreiche SEO-Berichte und detaillierte Analysen.
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:
Vorgestelltes Bild: NicoElNino/Shutterstock