In diesem Artikel geht es um BigQuery – Google Analytics Starter Pack. Für grundlegende Informationen über BigQuery und damit die perfekte Vorbereitung für den folgenden Artikel, empfehlen wir vorab: BigQuery – Einstieg in die cloudbasierte Datenbank
“BigQuery & Google Analytics – Starter Pack”
Wo fängt man an, wenn es darum geht, Google Analytics Daten mit Hilfe von BigQuery auszuwerten? Und vor allem, wie funktioniert das Ganze? Nachfolgend werden ein kleines Starter Pack und die wichtigsten Punkte vorgestellt, um direkt loszulegen.
A. Eine Verknüpfung zwischen Google Analytics & BigQuery muss her
Google Analytics lässt sich einfach mit BigQuery verknüpfen. Im Adminbereich von Google Analytics 360 ist die Product Linking Option “BigQuery” relativ schnell gefunden. Doch Obacht! Es gibt ein paar weitere Dinge zu beachten, damit der Export einwandfrei funktioniert. Google liefert hier eine Schritt-für-Schritt Anleitung, die die Verknüpfung kinderleicht macht.
Für die Verknüpfung einer Google Analytics 4 Property sollte diese Anleitung genutzt werden. An dieser Stelle sei jedoch gesagt, dass sich die folgenden Erklärungen und das Datenschema auf den Google Analytics 360 Export beziehen.
B. Es ist wichtig zu wissen, wie Google Analytics Daten in BigQuery gespeichert werden
1. Welche Daten werden gespeichert?
In diesem Fall liegt die Wahl beim Anwender. Es kann jeweils ein Google Analytics View pro Property mit BigQuery verknüpft werden. Wir empfehlen, den View zu verknüpfen, der auch für das Google Analytics Reporting genutzt wird. Das heißt: Der Rohdaten View, der womöglich als Backup in GA erstellt wurde, sollte besser nicht verknüpft werden. Daher ist es sinnvoller den View zu verwenden, der mit Hilfe der Filter und sonstigen Einstellungen bereits von Bots & Co. befreit wurde. Das hat den positiven Effekt, dass mit BigQuery und Google Analytics die gleichen Zahlen reportet werden. Die Rohdaten würden sonst Datenunterschiede und/oder einen immensen Filteraufwand in BQ mit sich bringen.
Nachdem ein View für den Export ausgewählt wurde, werden die Google Analytics Daten täglich in einer jeweils neuen Datentabelle gespeichert. Dementsprechend erfolgt die Benennung der Tabellen dabei immer nach Schema F: “ga_sessions_YYYMMDD” (Beispiel: “ga_sessions20170801”). Doch wie sehen diese Daten dann tatsächlich aus? Und wie kann ich sie abfragen?
2. Wie sieht das Exportschema aus?
Glücklicherweise hat Google uns hier eine Doku bereitgestellt. Dieses Exportschema umfasst die Struktur und Erklärungen der Spalten und Felder, in denen die Google Analytics Daten gespeichert werden. Somit sollte es ab jetzt ein neuer stetiger Begleiter sein, wenn es um Google Analytics Daten in BigQuery geht.
3. Gibt es Beispieldaten?
Ja, mit dem Google Analytics Sample Dataset liefert Google ein Beispiel Dataset aus dem Google Merchandising Store. Demnach kann sich der User mit Hilfe dieses Samples zum einen vorab einen guten Überblick davon verschaffen, wie die Daten grundsätzlich einlaufen werden. Zum anderen bildet es eine perfekte Spielwiese, um sich mit BigQuery, den Google Analytics Daten und im speziellen mit den SQL Dialekten vertraut zu machen.
4. Wie sieht die Tabellenstruktur aus?
Anders als bei einer relationalen Datenbank sind die Google Analytics Daten ‘genested’. Das heißt eine Zeile kann wiederum mehrere Zeilen beinhalten, die wiederum mehrere Zeilen enthalten können. Diese Besonderheit in Kombination mit den Google Analytics spezifischen Dimensionen und Metriken bedarf etwas Übung.
Demzufolge, um das Ganze etwas haptischer darzustellen, ist hier ein Vergleich zwischen einer relationalen Datenbank und der genesteten Struktur in BigQuery.
Abb. 1: Relationale Datenbank
Abb. 2: Nested Structure
In Bezug auf die Google Analytics Daten sieht die Struktur in etwa so aus:
Abb. 3: Nested Structure in Bezug auf Google Analytics Daten
Folglich entspricht eine Zeile der Datentabelle jeweils einer Session. Die Aktionen innerhalb der Session (z.B. Pageviews oder Events) werden wiederum in Nestern innerhalb der Session abgebildet, die weitere Nester enthalten können (z.B. Produktdetails, der auf der Seite abgebildeten Produkte).
Übrigens, eine Preview mit allen Spalten findet sich auch im Google Analytics Sample Dataset:
Abb. 4: Preview aus Google Analytics Sample Dataset
C. Queries – Und wie frage ich diese nested Daten ab?
Die Google Analytics Daten können sowohl mit einem von Google entwickelten SQL Dialekt (Legacy SQL Reference) als auch mit Standard SQL (Standard SQL Dialekt) abgefragt werden. Da der Legacy Dialekt ein Relikt aus den Anfangszeiten ist, der zwar noch unterstützt, aber nicht mehr gefördert wird, empfiehlt es sich auf den Standard zu setzen. In Bezug auf die Nested Structure bietet Legacy SQL zwar teils eine einfachere Handhabe, allerdings ist Standard SQL breiter aufgestellt und ermöglicht zusätzliche Optionen, wie zum Beispiel Custom Functions.
Der einfachste Weg mit einer Query loszulegen, ist:
- das Öffnen des Google Analytics Sample Datasets und
- der Klick auf “Query Table” oben rechts im Fenster nachdem ihr euer Dataset durch anklicken aufgerufen habt.
Abb. 5: Öffnen des Google Analytics Sample Datasets
Dadurch öffnet sich sofort ein Abfragefenster, in dem das Wichtigste schon vordefiniert ist. Infolgedessen wird durch einen Klick auf “Format Query” die Abfrage direkt in das typische SQL Format umgewandelt:
Abb. 6: Abfragefenster mit Formatierungsoption
Jetzt kann es losgehen.
Google erwartet Abfragen in Standard SQL. Sollte es gewünscht sein, Abfragen im Legacy SQL Dialekt zu nutzen, muss ein #legacySQL vor der Abfrage erfolgen (siehe Legacy SQL Dokumentation).
Es folgen ein paar Beispiele für einfache Abfragen in BigQuery in Standard SQL. Die Kosten für die Abfragen sind marginal bis fast nicht vorhanden und werden vom monatlichen Freibudget aufgefangen. Um bei größeren Abfragen jedoch den Überblick zu behalten, welche Kosten eine Abfrage verursachen wird, empfiehlt es sich die Menge der prozessierten Daten im Auge zu behalten.
Abb. 7: Kostenkontrolle anhand von prozessierter Datenmenge
FRAGE: Wie viele Sessions hatte der Google Merchandise Shop pro Tag am 01.08.2017?
SELECT date, SUM(totals.visits) AS Sessions FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` GROUP BY date
Wenn man beispielsweise eine ganze Woche als Zeitrahmen benötigt, könnte man die Abfrage folgendermaßen anpassen:
SELECT date, SUM(totals.visits) AS Sessions FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170726' AND '20170801' GROUP BY date
Somit, sollte man mehr Metriken als nur die Sessions benötigen, kann man die Abfrage beliebig erweitern:
SELECT date, COUNT(DISTINCT(fullVisitorId)) AS Users, SUM(totals.newVisits) AS NewUsers, SUM(totals.visits) AS Sessions, SUM(totals.transactions) AS Transactions, ROUND(SUM(totals.transactions)/SUM(totals.visits)*100,2) AS SessionConversionRate, ROUND((SUM(totals.totalTransactionRevenue)/POW(10,6))/SUM(totals.transactions),2) AS AverageOrderValue FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` GROUP BY date
Ein Beispiel für eine Abfrage, in der man ein Datennest abfragen muss, ist die Frage nach seitenbezogenen Metriken. Wenn wir uns beispielsweise pro Seite ausgeben lassen wollen, wie viele User, Sessions, Pageviews und Entrances es gab, könnte man den nachfolgenden Code verwenden.
Mit UNNEST(hits) ‘öffnen’ wir sozusagen das Nest, um es für die Abfrage verfügbar zu machen.
SELECT hits.page.pagePath, COUNT(DISTINCT(fullVisitorId)) AS Users, COUNT(DISTINCT(CONCAT(fullVisitorId, CAST(visitId AS STRING)))) AS Sessions, COUNT(hits.page.pagePath)AS Pageviews, COUNT(hits.isEntrance) AS Entrances FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` AS t, UNNEST(hits) as hits WHERE hits.type = 'PAGE' GROUP BY 1 ORDER BY 2 DESC
Doch das ist nur der Anfang – mit BigQuery eröffnen sich ganz neue Analysemöglichkeiten, die dem Analysten im Interface bislang verwehrt bleiben. Beispiele hierfür sind komplexe Kohortenanalysen, Deepdives zu User Engagement, Cross-Device-Verhalten, detaillierte User Journeys, uvm.
Weitere Vorteile von BigQuery
Aber auch im Kleinen bringt BigQuery Vorteile mit sich. Mit deren Hilfe lässt sich die ein oder andere Limitation des Google Analytics Interfaces überwinden:
-
- Scopes in Google Analytics → nicht jede Dimension lässt sich mit jeder Metrik kombinieren. Mit Hilfe von BigQuery lässt sich dieses Problem an vielen Stellen umgehen. Die Analysemöglichkeiten werden flexibler.
- Anzahl der Dimensionen & Metriken: Im interface ist die Anzahl der angezeigten Dimensionen & Metriken begrenzt. Mit BigQuery lassen sich beliebig viele Dimensionen und Metriken miteinander kombinieren.
- Filter: die Filteroptionen in Google Analytics könnten flexibler sein. In BigQuery ist die Filternutzung deutlich individueller – dem Filtern sind hier (fast) keine Grenzen gesetzt.
- Einblick in Sessionverläufe / User Journeys: ein großer Vorteil ist zudem die Verfügbarkeit von Visit IDs und User IDs. Das Interface bietet nur limitierten Einblick in den Verlauf von Sessions & User Journeys. Mit BigQuery lässt sich beides jedoch detailliert ausgeben und auswerten.
- Cross-Device: Im Interface wird strikt zwischen Cross-Device & clientbasiertem View getrennt. Wenn allerdings eine UserId nach erfolgreichem Login übergeben wird, kann man in BigQuery beide Ansichten miteinander verknüpfen, d.h. sobald eine LoginId verfügbar ist, wird der User deviceübergreifend betrachtet, liegt keine ID vor, greift die ursprüngliche clientbasierte User Definition.
Ein Zugewinn ist zudem die Möglichkeit zusätzliche Datenquellen mit den Google Analytics Daten über BigQuery verknüpfen zu können. So lassen sich beispielsweise Daten weiterer Marketingtools oder auch Daten des DWH miteinander kombinieren und auswerten. Diese Verknüpfung ermöglicht ein übergreifendes Reporting, das mithilfe eines Visualisierungs-Tools problemlos erstellt werden kann.
Fazit
Aus unserer Sicht ist BigQuery in Kombination mit Google Analytics eine sinnvolle zusätzliche Erweiterung, die zum einen Limitationen des Interface umgehen, aber auch neuen Möglichkeiten der Analyse eröffnen und Reportings standardisieren kann.
Durch das schnelle Aufsetzen der Verknüpfung und den im Verhältnis zu anderen Datenbanklösungen geringen Kosten, ist BigQuery ein Must-have für alle Google Marketing Plattform Kunden, die tiefer in das Thema Analytics und Reporting einsteigen wollen.
Ist dieses Thema für Ihr Unternehmen interessant oder haben Sie weitere Fragen zu BigQuery, der Google Cloud oder Google Analytics 4? Dann melden Sie sich gerne direkt bei uns.
