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

dateUsersGASessionsBigQuerySessionsPageViews
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_sourcetrafficSource_mediumSessions
google organic 35.731
google cpc 30.458
newsletter email 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_pagePathpageviews
/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

 

pagetypepageviews
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()

 

 dateUsersGASessionsBigQuerySessionsPageViews
0 20180609 14544 18597 18597 48974
1 20180602 14322 18080 18080 52225
2 20180606 15657 19573 19573 54294
3 20180604 23623 28730 28730 74206
4 20180607 16474 20964 20964 57126

Die Daten als Chart ausgeben

myPlot = myDataFrame.plot(x="date",y=["GASessions","Users"])
myPlot.set_xticklabels(myDataFrame["date"], rotation=25)
plt.show()

 

bigquery basic examples plot