Der Rohdaten-Export der Analytics Tracking Daten ist eines der interessantesten und mächtigsten Features von GA360, der kostenpflichtigen Version von Google Analyitcs. Der BigQuery Export ermöglicht die Analyse der Trackinig Daten ohne jegliche Limitierung oder Stichprobenerhebung (Sampling). Dieser Artikel zeigt einfache Beispiele wie man auf die Google Analytics Daten in BigQuery zugreift, um Statistiken zu analysieren.
Um BigQuery Anfragen auszuführen kann man natürlich direkt das einfache Web-Interface unter https://bigquery.cloud.google.com nutzen. Elegangter ist dagegen die Nutzung der BigQuery API in Verbindung mit einer der vielen unterstützten Programmiersprachen. Die unten gezeigten Beispiele verwenden Google Cloud Datalab, ein sehr nützliches Web-Basiertes Tool von Google, welches Python Code, Dokumentation und mögliche Visualisierung kombiniert.
Eine vollständige Beschreibung des GA360 BigQuery Export Schemas kann man unter https://support.google.com/analytics/answer/3437719 finden.
Benötigte Bibliotheken importieren
import datalab.bigquery as bq
import pandas as pd
import matplotlib.pyplot as plt
Beispiel 1: Nutzer, Sitzungen und Seitenaufrufe für einen gegebenen Zeitraum abfragen
Anmerkungen:
- In BigQuery bleibt die visitId über die 0h Grenze bestehen
- Im GA Web-Interface werden nur Sitzungen gezählt, die mindestens eine einen User-Interaction Hit beinhalten
%%sql
SELECT
date,
EXACT_COUNT_DISTINCT(fullVisitorId) AS Users,
EXACT_COUNT_DISTINCT(CONCAT(STRING(fullVisitorId), STRING(visitStartTime))) AS GASessions,
EXACT_COUNT_DISTINCT(CONCAT(STRING(fullVisitorId), STRING(visitId))) AS BigQuerySessions,
SUM(IF(hits.type ='PAGE', 1, 0)) AS PageViews
FROM TABLE_DATE_RANGE([myProject:123456789.ga_sessions_],
TIMESTAMP('2018-06-01'),
TIMESTAMP('2018-06-05'))
GROUP BY date
date | Users | GASessions | BigQuerySessions | PageViews |
---|---|---|---|---|
20180604 | 23.623 | 28.730 | 28.730 | 74.206 |
20180601 | 15.326 | 19.526 | 19.526 | 58.923 |
20180602 | 14.322 | 18.080 | 18.080 | 52.225 |
20180603 | 16.301 | 21.035 | 21.035 | 61.926 |
20180605 | 19.137 | 23.870 | 23.870 | 65.074 |
Beispiel 2: Abfrage von Sitzungen gruppiert nach Quelle und Medium
Das totals.visits Feld enthält für Sitzungen mit mindestens einem Interaction Hit den Wert 1.
%%sql
SELECT
trafficSource.source,
trafficSource.medium,
SUM(totals.visits) as Sessions
FROM TABLE_DATE_RANGE([myProject:123456789.ga_sessions_],
TIMESTAMP('2018-06-01'),
TIMESTAMP('2018-06-05'))
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 10
trafficSource_source | trafficSource_medium | Sessions |
---|---|---|
organic | 35.731 | |
cpc | 30.458 | |
newsletter | 13.384 | |
(direct) | (none) | 12.449 |
myPartner1 | partner | 5.278 |
myPartner2 | partner | 2.022 |
bing | cpc | 1.649 |
bing | organic | 1.382 |
myPartner3 | partner | 1.371 |
m.facebook.com | referral | 1.078 |
Beispiel 3: Abfrage von Seitenaufrufen gruppiert nach der Seite
Seitenaufrufe haben den Werte "PAGE" im Feld hits.type.
%%sql
SELECT
hits.page.pagePath,
COUNT(*) as pageviews
FROM TABLE_DATE_RANGE([myProject:123456789.ga_sessions_],
TIMESTAMP('2018-06-01'),
TIMESTAMP('2018-06-05'))
WHERE hits.type = 'PAGE'
GROUP BY hits.page.pagePath
ORDER BY pageviews DESC
LIMIT 10
hits_page_pagePath | pageviews |
---|---|
/shop/page1 | 32.030 |
/home/ | 23.035 |
/shop/customer/account/login/ | 19.960 |
/ | 15.730 |
/news | 14.756 |
/shop/zubehoer | 12.288 |
/shop/ | 11.801 |
/page3 | 10.084 |
/shop/checkout/cart/ | 10.038 |
/shop/ | 7.790 |
Beispiel 4: Abfrage von Daten gruppiert nach einer Benutzerdefinierten Dimension
Benutzerdefinierte Dimensionen sind in BigQuery in einem verschachtelten Feld mit Index / Wert Paaren gespeichert. Benutzerdefinierte Dimensionen mit Nutzer- oder Sitzungs-Gültigkeit sind auf oberster Ebene der sitzungsgruppierten Daten in BigQuery gespeichert. Benutzerdefinierte Dimensionsn mit Hit-Scope sind dagegen für jeden einzelnen Tracking Hit im Objekt hits.customDimensions abgelegt.
%%sql
SELECT pagetype, SUM(pageviews) AS pageviews
FROM
(
SELECT
1 AS pageviews,
IF(hits.customDimensions.index = 4, hits.customDimensions.value, null) AS pagetype,
FROM TABLE_DATE_RANGE([myProject:123456789.ga_sessions_],
TIMESTAMP('2018-06-01'),
TIMESTAMP('2018-06-05'))
WHERE hits.type='PAGE'
HAVING Pagetype IS NOT NULL
)
GROUP BY pagetype
ORDER BY pageviews DESC
pagetype | pageviews |
---|---|
content | 70.177 |
listing | 52.203 |
homepage | 35.239 |
product | 34.134 |
Beispiel 5: Daten in ein Pandas Dataframe laden und als Chart darstellen
BigQuery Abfrage-Resultate können direkt in das für Python weit verbreitete Pandas Dataframe Format geladen werden, um dann weiter verarbeitet oder als Chart dargestellt zu werden.
Schritt 1: Query definieren
%%sql --module myExampleQuery
SELECT
date,
EXACT_COUNT_DISTINCT(fullVisitorId) AS Users,
EXACT_COUNT_DISTINCT(CONCAT(STRING(fullVisitorId), STRING(visitStartTime))) AS GASessions,
EXACT_COUNT_DISTINCT(CONCAT(STRING(fullVisitorId), STRING(visitId))) AS BigQuerySessions,
SUM(IF(hits.type ='PAGE', 1, 0)) AS PageViews
FROM TABLE_DATE_RANGE([myProject:123456789.ga_sessions_],
TIMESTAMP('2018-06-01'),
TIMESTAMP('2018-06-15'))
GROUP BY date
Schritt 2: Daten in Pandas Dataframe laden
myDataFrame = bq.Query(myExampleQuery).to_dataframe()
Preview der Daten
myDataFrame.head()
Die Daten als Chart ausgeben
myPlot = myDataFrame.plot(x="date",y=["GASessions","Users"])
myPlot.set_xticklabels(myDataFrame["date"], rotation=25)
plt.show()