• Das session_start-Event
• Sitzungen zählen: 1 Tag
• Sitzungen zählen: Statischer Datumsbereich
• Sitzungen zählen: Dynamischer Datumsbereich
• Sitzungen zählen: Echtzeit

Das session_start-Event

In Google Analytics 4 wird eine neue Sitzung (Session) gezählt, sobald der Nutzer eine App im Vordergrund öffnet oder eine Seite auf der Website aufruft (ohne dass eine andere Sitzung aktuell aktiv ist). Google Analytics 4 sendet daraufhin das Event “session_start”. Über dieses Event können wir die Anzahl der Sitzungen, die ein Nutzer auf der App oder Website begonnen hat, innerhalb BigQuery kalkulieren.

Sitzungen zählen: 1 Tag

Um die Anzahl der Sitzungen eines Tages zu zählen, müssen wir die entsprechende Tagestabelle wählen und in der WHERE-Klausel nach dem session_start-Event filtern.

-- Anzahl der Sessions an einem Tag zählen
SELECT
  COUNT(event_name) AS sessions
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE
  event_name = 'session_start';

Sitzungen zählen: Statischer Datumsbereich

Um Sitzungen für einen bestimmten Zeitraum zu zählen, wird in der FROM-Klausel statt dem Datum die Wildcard (*) gesetzt, um alle Tagestabellen auszuwählen. Anschließend wird der Datumsbereich mit dem _TABLE_SUFFIX bestimmt:

-- Anzahl der Sessions, statischer Datumsbereich
SELECT
  COUNT(event_name) AS sessions
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'session_start'
  AND _TABLE_SUFFIX BETWEEN '20210101' AND '20210131';

Sitzungen zählen: Dynamischer Datumsbereich

Ein dynamischer Datumsbereich hat einen Versatz des aktuellen Datums (sagt man so, oder?). Beispiel: Vor 1 Woche bis gestern. Damit kann man z.B. eine SQL-Abfrage schreiben, die zum Zeitpunkt des Aufrufs immer die letzten 7 Tage oder die letzten 30 Tage zurückgibt.

Um nun den gestrigen Tag dynamisch abzufragen müssen wir zunächst den aktuellen Tag wählen. Dazu gibt es in BigQuery die Funktion current_date():

SELECT
  CURRENT_DATE();

Dann müssen wir 1 Tag subtrahieren, um das gestrige Tage zu bekommen. Auch hier gibt es eine Funktion – date_sub().

SELECT
  DATE_SUB(CURRENT_DATE(), interval 1 day);

Wie man sehen kann, nimmt die Funktion 2 Parameter entgegen. Einmal den aktuellen Tag und einmal das Intervall, mit dem wir subtrahieren möchten. Damit wird aber das Datum im Datumsformat zurückgegeben (2021-30-08). Die Tabellen in BigQuery benötigen aber einen String – also 20213008. Deshalb müssen wir formatieren:

SELECT
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), interval 1 day));

‘%Y%m%d’ bedeutet:

• %Y = Jahr vierstellig
• %m = Monat zweistellig
• %d = Tag zweistellig

Also genau so wie wir es brauchen. Somit wird “2021-30-08” zu “20213008”. Diese Funktion können wir nun kopieren und in unsere vorherige SQL-Abfrage statt dem letzten ausgewählten Tag hinzufügen:

-- Anzahl der Sessions, dynamischer Datumsbereich
SELECT
  COUNT(event_name) AS sessions
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'session_start'
  AND _TABLE_SUFFIX BETWEEN '20210101' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), interval 1 day));

Sitzungen zählen: Echtzeit

Um Sitzungen ab einem bestimmten Zeitpunkt bis jetzt (also Echtzeit) zu zählen, müssen 2 Tabellen abgefragt werden. Einmal die Tabelle “events_” (historische Daten) und einmal die Tabelle “events_intraday_” (wo die heutigen Daten in fast Echtzeit zur Verfügung stehen). Damit wir Sitzungen in Echtzeit zählen können, müssen wir 2 Sachen an unserer letzten SQL-Abfrage anpassen:

• Das heutige Datum wählen
• Das _TABLE_SUFFIX bei beiden Tabellen extrahieren (mit einem Regex)

Das heutige Datum wählen ist einfach:

FORMAT_DATE('%Y%m%d', CURRENT_DATE())

Um das Datum der Tabellen zu extrahieren und anzuwenden, müssen wir uns einem Regex bedienen. In Grunde sagen wir, dass aus den gewählten Tabellen mit “events_*” aus der FROM-Klausel nur der Datumsbereich extrahiert werden soll. Das geschieht mit:

[0-9]+

Zusätzlich müssen wir die BigQuery-Funktion “REGEXP_EXTRACT()” anwenden. Die fertige SQL-Abfrage lautet:

-- Anzahl der Sessions in Echtzeit
SELECT
  COUNT(event_name) AS sessions
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'session_start'
  AND REGEXP_EXTRACT(_TABLE_SUFFIX, '[0-9]+') BETWEEN '20210101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE());