Mit ChatGPT GA4 Abfragen für BigQuery erstellen - geht das?

Mit der fortschreitenden Entwicklung von AI (Artificial Intelligence oder Künstliche Intelligenz / KI’s) kann der Arbeitsalltag immer mehr vereinfacht werden. Geht es aber – Stand heute – schon so weit, dass uns KI’s komplexe SQL Abfragen bauen können? Wir haben es ausprobiert!

BigQuery Abfragen für GA4 mit ChatGPT erstellen

Mit der Umstellung von Universal Analytics (UA) auf Google Analytics 4 (GA4) kommt auch die Herausforderung, alle BigQuery Abfragen, die für Universal Analytics im Einsatz waren (oder noch sind), nun auch für GA4 umzuschreiben. Das Problem bei dem Ganzen ist, dass das Export-Schema der Daten aus Universal Analytics und GA4 deutlich abweicht und die BigQuery Abfragen anders aufgebaut werden müssen, um das gleiche Ergebnis zu erreichen.

Zum Vergleich, hier die Links zu den Strukturen, die Google beim BigQuery von Universal Analytics und GA4 zur Verfügung stellt:

BigQuery Abfragen - die Unterschiede

Im Universal Analytics Schema wurde noch mittels “totals” Feld eine Möglichkeit geschaffen, schnell und einfach aggregierte Daten abzufragen (wie zB absolute Anzahl an Pageviews etc). Diese Möglichkeit fällt beim GA4 Export Schema weg. Das GA4 Export Schema basiert auf dem Schema, das schon vor Jahren beim BigQuery Export von Firebase Daten (=App Tracking Daten) verwendet wurde.

Eine weitere wichtige Änderung ist, dass die “products” aus dem Universal Analytics Schema nun “items” in GA4 sind und anders zu behandeln sind.

Auch die Abfrage von Custom Parametern in GA4 sieht komplett anders aus, als die Abfrage von Custom Dimensions & Custom Metrics im Universal Analytics Export Schema.

Mit GA4 stellt sich für viele Benutzer das Thema aber auch erst zum ersten Mal, da der BigQuery Export in Universal Analytics nur für Properties mit 360 Lizenz verfügbar war.

Jetzt können auch GA4 Benutzer BigQuery verwenden und die Daten für ein erweitertes Reporting nutzen. Die Voraussetzung dafür sind aber korrekte SQL Abfragen in BigQuery.

Mit dem Fortschreiten der AI / KI (Künstliche Intelligenz) Technologien bringt sich hier eine neue Möglichkeit auf: Fragen wir einfach ChatGPT nach unserer nächsten BigQuery Abfrage!

‘Bitte schreibe mir eine GA4 BigQuery Abfrage’

Long Story Short: ChatGPT kann GA4 BigQuery Abfragen schreiben und erstellen. ChatGPT kann auch Fehler in Queries suchen und diese beheben. Alles aber nur in einem gesunden Maß. At Time of writing (zur Artikel-Erstellungszeit) wurde die GPT Version-3.5 verwendet.

Im Grunde ist eine Anfrage an ChatGPT, um eine Query zu bauen, kein schlechter Ansatz, um loszulegen. Auch wenn die eigenen SQL Skills trainiert werden sollen, kann hier guter Input gewonnen werden.

Aktuell geht das Ganze nur auf Englisch und der Detailgrad, den ChatGPT erhält, sollte so groß wie möglich sein, damit eine Abfrage gemäß den Erwartungen zurückgegeben wird.

Fangen wir aber zunächst ganz trivial an. ChatGPT kann auch von sich aus und einfach so BigQuery Abfragen generieren:

Mit einer einfachen einzeiligen Frage spuckt ChatGPT 5 BigQuery Abfragen zu GA4 aus. Theoretisch ist das Einzige, was in der Abfrage angepasst werden müsste, die Tabelle. Hier wird die Tabelle als `your_ga4_table_name` ausgegeben. Es müsste aber der eigene BigQuery Datensatz angegeben werden, wie zum Beispiel e-dialog-bq.analytics_242145219.events_20230416 (diese Abfrage wäre dann nur für diesen einen Tag: 20230416).

Wird die Abfrage nun in BigQuery getestet gibt BigQuery allerdings einen Fehler aus:

Das heißt also, die Query ist nicht vollständig korrekt und der Fehler muss behoben werden, damit Daten ausgegeben werden können. Da ChatGPT ein Chatprogramm mit Chat-Verlaufen ist, kann der Benutzer eine Rückfrage auf die vorher erhaltene Antwort stellen. Interessant, was ChatGPT zu diesem Fehler sagt?

Und siehe da: Der eigens von ChatGPT generierte Fehler wird mit Hinweisen ausgebessert und die Query funktioniert:

In der zweiten Abfrage, die uns ChatGPT generiert hat, sollten die Top 10 der am meisten aufgerufenen Seiten aufgelistet werden.

Jedoch gibt BigQuery auch hier einen Fehler aus: Unrecognized name: pagePath at [3:7]

Nach weiteren Chat Nachrichten mit der Frage, ob ChatGPT den Fehler lösen kann, gibt die AI aus, dass statt der Dimension “pagePath” nun “page_location” verwendet werden soll, was aber auch falsch ist:

Nach der Frage, ob nicht ein UNNEST-Befehl bei der Abfrage verwendet werden sollte, gibt ChatGPT schlussendlich eine andere Abfrage aus, aber für Universal Analytics und nicht für GA4.

Wie sieht es mit dem Umbau von UA Queries aus?

Die vorherige Query wurde von ChatGPT leider für Universal Analytics geschrieben und nicht für GA4. Nach einer kurzen Bitte, bessert ChatGPT das sofort aus:

Jedoch hat diese Query auch Fehler. Außerdem funktioniert die Einschränkung ‘/%’ für den Page Pfad so nicht (es müssten zum Beispiel Regular Expressions verwendet werden).

Korrekt würde die Abfrage dann so aussehen:

				
					SELECT
  event_param.value.string_value AS page_path,
  COUNT(*) AS Pageviews
FROM `e-dialog-bq.analytics_242145219.events_20230408`, 
  UNNEST(event_params) AS event_param
WHERE 
  event_name = 'page_view' 
  AND event_param.key = 'page_location' 
GROUP BY page_path
ORDER BY Pageviews DESC
LIMIT 10

Was passiert bei komplizierten Anforderungen?

Wenn eine Anforderung an ChatGPT exakt ausformuliert wird, müsste ChatGPT theoretisch in der Lage sein, die Query die benötigt wird, korrekt zu bauen.

Folgendes Fallbeispiel: Eine BigQuery-Abfrage der Google Analytics 4-Daten wird benötigt, um Verkaufszahlen eines Produkts mit den Produktansichten zu vergleichen. Um die Anzahl der Produktansichten zu erhalten, kann ChatGPT das GA4 Ereignis “view_item” verwenden und um die Verkäufe zu erhalten das Ereignis “purchases”. Dabei muss ChatGPT auf die “items” Felder zugreifen. Die resultierenden Spalten sollten wie folgt lauten: Zeilennummer, Datum (%y-%m-%d), Artikelname, Artikel-ID, Kauf-zu-Artikelansicht-Rate, Anzahl der Artikel Ansichten, Anzahl der Käufe.

Die Anfrage muss auf Englisch formuliert werden: Please write a BigQuery Query that uses Google Analytics 4 data that should compare product views against the sale count of the product. To get the product count you can use the count of the “view_item” event and to get the purchase count you can use the “purchase” event. The resulting columns should be: Row Number, Date (%y-%m-%d), Item Name, Item ID, purchase to view_item rate, count of view_items, count of purchases.

Die Antwort sieht vielversprechend aus:

Die Query erzeugt leider eine Reihe von Fehlern, die nach und nach ausgebessert werden müssen.

Gibt es keine Item Informationen im “event_params” Feld, muss dieses mit “items” in beiden Zeilen ersetzt werden. Dort muss außerdem noch das WHERE Statement entfernt werden, weil es dort kein key Feld gibt.
In der GROUP BY Klausel müsste “Purchase_to_View_Item_Rate” durch “4” ersetzt werden, da BigQuery die Referenz nicht versteht.

Nach diesen Änderungen wird allerdings noch ein Fehler geworfen: Scalar subquery produced more than one element

Das bedeutet, dass im SELECT Statement eine Sub-Query mehr als eine Zeile zurückgegeben hat, was nicht sein darf. Das erfordert also einen erweiterten Umbau dieser Query.

Die Anfrage an ChatGPT kann noch umformuliert werden:

Please write a BigQuery Query with Google Analytics 4 data, that compares the view count and the purchase count of products. The query should return the following columns: Calendar week, Item Name, Item ID, Count of how often the Product was viewed, Count of how often the product was purchased

Jedoch wird hier ziemlich das gleiche ausgegeben und das Problem mit UNNEST(event_params) ist weiterhin da.

Was jedoch einen Mehrwert bietet und jemandem eine Google Suche ersparen kann ist zum Beispiel der Code für die Kalenderwoche:

				
					FORMAT_WEEK(TIMESTAMP_MICROS(event_timestamp)) AS Calendar_Week

Gibt es Alternativen für BigQuery Abfragen für GA4 mit KI-Tools?

Im Netz gibt es alternativ noch Webseiten, die sogenannte Query Generatoren für GA4 & BigQuery anbieten. Diese zeichnen sich aber mit einer eingeschränkten Flexibilität aus. So können lediglich Dimensionen und Metriken aus einer Liste ausgewählt werden und nur einfache Queries erstellt werden.

Fazit:

ChatGPT kann eine sehr hilfreiche Anlaufstelle für SQL Beginner sein. Eine sofort einsatzfähige Query gibt ChatGPT zwar nicht aus, aber es wird definitiv der Anstoß für den Anfang der Erstellung einer Query gegeben. Zudem ist es sehr wichtig, die Formulierung der Anfrage an ChatGPT richtig, exakt und detailliert anzugeben, ansonsten kann es Missverständnisse geben. Im Endeffekt müssen Queries aber korrigiert werden, damit sie einsatzfähig werden. ChatGPT’s AI Modell muss höchstwahrscheinlich noch länger trainiert werden, bis immer einsatzfähige Queries generiert werden können.

Bis dahin helfen wir Ihnen gerne, einfache und komplexere GA4-Abfragen für BigQuery zu erstellen, die dann auch sofort funktionieren, wenn Sie sie ausführen möchten. 🙂