Falls ihr vorab noch grundlegende Informationen über BigQuery und damit die perfekte Vorbereitung für den folgenden Artikel sucht, empfehlen wir euch:
BigQuery – Eigenschaften und Fähigkeiten – Teil 1
“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 findet ihr ein kleines Starter Pack und die wichtigsten Punkte, um 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 ist die Product Linking Option “BigQuery” relativ schnell gefunden – doch Obacht! Es gibt noch ein paar weitere Dinge, die du beachten solltest, damit der Export einwandfrei funktioniert. Übrigens liefert Google hier eine Schritt-für-Schritt Anleitung, die die Verknüpfung kinderleicht macht.
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 bei euch – man kann jeweils einen Google Analytics View pro Property mit BigQuery verknüpfen. Wir empfehlen, den View zu verknüpfen, den man auch für das Google Analytics Reporting nutzt. Das heißt: verknüpft besser nicht den Rohdaten View, den ihr womöglich als Backup in GA erstellt habt, sondern nutzt den View, in dem die Bots & Co. bereits gefiltert wurden. Das hat den positiven Effekt, dass ihr mit BigQuery und Google Analytics die gleichen Zahlen reporten werdet. Die Rohdaten würden ansonsten 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 & Felder, in denen die Google Analytics Daten gespeichert werden und sollte ab jetzt euer 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’, d.h. 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 übrigens auch im Google Analytics Sample Dataset:
Abb. 4: Preview aus Google Analytics Sample Dataset
Quelle: bigquery.cloud.google.com/table/bigquery-public-data:google_analytics_sample
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. 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
Quelle: bigquery.cloud.google.com/table/bigquery-public-data:google_analytics_sample
Jetzt kann es losgehen.
Aber Achtung: solltet ihr dem Beispiel oben gefolgt sein, befindet ihr euch gerade in einer Legacy SQL Abfrage. Dort lässt sich das zum Beispiel an den eckigen Klammern der Datenquelle [bigquery-public… 20170801] erkennen. Demnach, solltet ihr Standard SQL nutzen wollen, müsste zu Beginn der Abfrage ein #standardsql eingefügt und die Syntax etwas angepasst werden:
Abb. 7: Abfragefenster #standardsql
Quelle: bigquery.cloud.google.com/table/bigquery-public-data:google_analytics_sample
Wem das allerdings auf Dauer zu viel Aufwand ist, der kann sich auch einfach eins der verfügbaren AddOns herunterladen, welches den Wechsel von Standard zu Legacy oder andersherum von selbst erledigt und zudem praktischerweise auch noch anzeigt, welches Budget man da gerade auf den Kopf haut.
Beispielsweise das Add On superQuery, mit dem sich die Visualisierung dann folgendermaßen ändert:
Abb. 8: Add On superQuery
Quelle: bigquery.cloud.google.com/table/bigquery-public-data:google_analytics_sample
Abb. 9: Es gibt ein neues Interface, welche das Standard SQL standardmäßig nutzt.
Jetzt kann es aber wirklich losgehen! Somit, um sich etwas mit der Syntax & der Datenstruktur vertraut zu machen, folgen ein paar Beispiele für einfache Abfragen in BigQuery in Standard SQL:
FRAGE: Wie viele Sessions hatte der Google Merchandise Shop pro Tag am 01.06.2019?
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_20170615` 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 Hilfe derer sich die ein oder andere Limitation des Google Analytics Interfaces überwinden lässt:
-
- 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.
