BigQuery - Eigenschaften und Fähigkeiten - Teil 2

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 beachtet werden sollten, damit der Export einwandfrei funktioniert. Google liefert hier eine Schritt-für-Schritt Anleitung, die die Verknüpfung kinderleicht macht.

https://support.google.com/analytics/answer/3416092

 

B. Es ist wichtig zu wissen, wie Google Analytics Daten in BigQuery gespeichert werden

 

1. Welche Daten werden gespeichert?

Die Wahl liegt bei euch – 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. Sprich: 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 Filterauswand 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.

Die Benennung der Tabellen erfolgt 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.

Das 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. Mit Hilfe dieses Samples kann sich der User 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.

 

Um das Ganze etwas haptischer darzustellen, hier ein Vergleich zwischen einer relationalen Datenbank und der genesteten Struktur in BigQuery.

https://lh5.googleusercontent.com/3ZM1gNpcHSHKJJBdAmbrzxJoL3dPgAc117JkWeHnqWehxAab6J-EyglnVkIJ6cpyb0pgfNpIvF0442k3kZ-6JCIstP6t1hKYyV6sSbylj1PgrCgRgRnz9dJzs6E9Ri3LX0T-qlTi

Abb. 1: Relationale Datenbank

 

https://lh4.googleusercontent.com/xFKrKBY4U-35ZJqU3BFLMA1jMr8mIJEhSp7TS2LcHWC8y4_jwMqEcKDrQa3dhuF_ziidtFs6GzS7vN4PRyTet2RbU4tV3Gijl-E55s_aTIYodDdRywg-98ZsiGFHfNWOWidSNIa9

Abb. 2: Nested Structure

 

In Bezug auf die Google Analytics Daten sieht die Struktur in etwa so aus:

https://lh5.googleusercontent.com/8wKkAA-X9OnbePRIV_m3d9leqwuWk1imme5gBmim1JcUDaLxUzcdingda7sx7z5zpbE6f1IlB4ekwiyfLDHvN_kWhz8c9YvbUGLIhCaJfBv0oDfJeNL65ouWNv-_pEvjR4e168li

Abb. 3: Nested Structure in Bezug auf Google Analytics Daten

 

Eine Zeile der Datentabelle entspricht 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).

 

Eine Preview mit allen Spalten findet sich übrigens auch im Google Analytics Sample Dataset:

https://lh3.googleusercontent.com/26ANBGMz_sJ1IU8wKHfeMamlD6jiY2r0mJEvy6oYXXtQSbWvFxnE48GDoisI0jWY4LclGGSItKwmA84nt4_G2lorIGtTSz38RPQeGCCY8-YvdL5m4UpLdnIrHMXAEDcfiT22BOKc

Abb. 4: Preview aus Google Analytics Sample Dataset

Quelle: https://bigquery.cloud.google.com/table/bigquery-public-data:google_analytics_sample.ga_sessions_20170801?pli=1&tab=preview

 

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

 

  1. das Öffnen des Google Analytics Sample Datasets  und
  2. der Klick auf “Query Table” oben rechts im Fenster nachdem ihr euer Dataset durch anklicken aufgerufen habt.

https://lh4.googleusercontent.com/1lL18HO_xX8FYpv6pSZO30_K_Saa_o_ScA_nRNQUWg8QZqp-H4HeJIr7Tww-WszilXfBELM1QLW_pHf6r_j1UJYxdU3nav-1sM6n5WiGNmjdJ0afPF5xz8Jk90XClvu_z05Va57d

Abb. 5: Öffnen des Google Analytics Sample Datasets

 

Sofort öffnet sich ein Abfragefenster, in dem das Wichtigste schon vordefiniert ist. Durch einen Klick auf “Format Query” wird die Abfrage direkt in das typische SQL Format umgewandelt:

https://lh6.googleusercontent.com/6hJf6Wfpi66y7f0IsupQX5shhUxEN50WaL0LSjefhFDSEDbpu5Q0GzZWo1lY4o6I5bI1nFQJ3GAjnw74lc3jOwppO6fLx2fYlZdDsnU3pbgBQwSWol7mX2qr51iJKNFtt7AWijZc

Abb. 6: Abfragefenster

Quelle: https://bigquery.cloud.google.com/table/bigquery-public-data:google_analytics_sample.ga_sessions_20170801?pli=1&tab=preview

 

 

Jetzt kann es losgehen.

 

Aber Achtung: solltet ihr dem Beispiel oben gefolgt sein, befindet ihr euch gerade in einer Legacy SQL Abfrage. Das lässt sich zum Beispiel an den eckigen Klammern der Datenquelle [bigquery-public… 20170801] erkennen.

 

Solltet ihr Standard SQL nutzen wollen, müsste zu Beginn der Abfrage ein #standardsql eingefügt und die Syntax etwas angepasst werden:

https://lh4.googleusercontent.com/SNC3qo22Yy2CmI96H1D3cPK-LNdwOVl__kHV1nsSyZJRy4QWwaBaaArFmaOuK0A3iPv9NFhzil4f6QZA38YL2Bmdwqy6-q8bl_37Qx8RYyhGwxvAq-y9ot8fogU-eqV8YQtM7kky

Abb. 7: Abfragefenster #standardsql

Quelle: https://bigquery.cloud.google.com/table/bigquery-public-data:google_analytics_sample.ga_sessions_20170801?pli=1&tab=preview

 

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.

Ein Beispiel ist das Add On superQuery, mit dem sich die Visualisierung dann folgendermaßen ändert:

https://lh5.googleusercontent.com/-CFqnybE4CaoG-orqxtqPObFnijj_4Tgqiewnlo3ffQXQ14BZxIeJX-TZjM16jCm5yuc3EcA8534P5XYktc1xGxwMNa4TOGN3YYc7uAP-3RvheJ0Bgx6cb9hYP8j3wFZmj3nZJ4U

Abb. 7: Add On superQuery

Quelle: https://bigquery.cloud.google.com/table/bigquery-public-data:google_analytics_sample.ga_sessions_20170801?pli=1&tab=preview

 

Jetzt kann es aber wirklich losgehen!

 

Um sich etwas mit der Syntax & der Datenstruktur vertraut zu machen, 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.08.2018?

 

SELECT

  date,

  SUM(totals.visits) AS Sessions

FROM

  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`

GROUP BY

  date

Wird beispielsweise eine ganze Woche als Zeitrahmen benötigt, könnte die Abfrage folgendermaßen angepasst werden:

 

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

 

Sollten mehr Metriken als nur die Sessions benötigt werden, kann die Abfrage beliebig erweitert werden:

 

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 ein Datennest abgefragt werden muss, ist die Frage nach seitenbezogenen Metriken. Würden wir uns beispielsweise pro Seite ausgeben lassen wollen, wie viele Users, Sessions, Pageviews und Entrances es gab, könnte der nachfolgende Code verwendet werden.

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.

 

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:

 

    1. 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.
    2. 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.
    3. 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.
    4. 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.
    5. Cross-Device: Im Interface wird strikt zwischen Cross-Device & clientbasiertem View getrennt. Wenn allerdings eine UserId nach erfolgreichem Login übergeben wird, können in BigQuery beide Ansichten miteinander verknüpft werden, 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 Visualisierungstools problemlos erstellt werden kann.

 

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 Analytics 360 Kunden, die tiefer in das Thema Analytics und Reporting einsteigen wollen.