Google Analytics 4 bietet einen kostenlosen Rohdaten Export nach BigQuery. Wie Dataform Repositorys basierend auf diesen Exporten datensparsam automatisiert werden können, zeige ich in diesem Beitrag.
Google Analytics 4 Rohdatenexport
Die Rohdaten von Google Analytics 4 werden von Google in relativ regelmäßigen Abständen in BigQuery geschrieben. Dabei wird für jeden Tag ein Tabelle angelegt. So findet sich in der Tabelle „events_20230802“ die Daten vom 07.07.23.
In folgendem Screenshot kann man sehen, dass am 1.08.2023 fünfmal GA4 Daten nach BigQuery exportiert wurden. Die ersten 4 Jobs updaten die Tabelle „events_20230730“ und der letzte die Tabelle „events_20230731“. Im Graphen kann man gut erkennen, dass diese Exporte über den Tag verteilt stattfinden.
Wichtig zu beachten ist, dass diese Rohdatenexporte nicht immer zum gleichen Zeitpunkt stattfinden und Daten manchmal nochmal ergänzt werden. In der Praxis habe ich auch bei Tabellen, die älter sind als 6 Tagen, Updates gesehen, obwohl Google in der offiziellen Dokumentation 72 Stunden, bzw. 3 Tage angibt.
Daily export tables (events_YYYYMMDD) are created after Analytics collects all of the events for the day. Analytics will update daily tables for up to 72 hours beyond the date of the table with events that are timestamped with the date of the table, e.g., event bundles that come in late from Measurement Protocol or the Firebase SDKs. For example, if the table date is 20220101, then Analytics will update the table through 20220104 with events that are timestamped 20220101.
https://support.google.com/analytics/answer/9358801?hl=en
Um zu vermeiden, dass wir mehrere Dataform executions kurz hintereinander triggern, ziehen wir uns bewusst die letzten 3. bzw. 5 Tage der Rohdaten, um die neuen Daten einzupflegen.
In der Vergangenheit habe ich gerne ein Log Sink auf die Update Prozeduren von den GA4 Tabellen gesetzt. Leider hat sich in der Praxis herausgestellt, dass zu viele Dataform executions mit zu geringem zeitlichen Intervall getriggert wurden, wodurch wir das Update Limit von BigQuery erreichen.
Ein Dynamischer Trigger für Dataform
Folgende Services werden für die Automatisierung genutzt:
- Log Sink – Hier definieren wir die Logs, die später als Auslöser genutzt werden sollen
- Pub/Sub Topic – Übermittelt unseren Log an den Workflow
- Workflows – Hier definieren wir welches Dataform Repository / Workspace wir ausführen und setzen ggf. Environment Variablen
Log Sink
Als Erstes setzen wir eine Log Sink auf, die als Auslöser unserer Automatisierung dienen soll. Jedes Mal, wenn Google eine neue Tabelle für GA4 in BigQuery erstellt, soll unsere Pipeline ausgeführt werden.
Hierfür können wir im Logs Explorer unter Logging unseren Filter testen, den wir für die Log Sink nutzen wollen.
proto_payload.authentication_info.principal_email="firebase-measurement@system.gserviceaccount.com"
proto_payload.resource_name=~"projects/{PROJECT_ID}/datasets/{DATASET_ID}/tables/events_2"
proto_payload.authorization_info.permission="bigquery.tables.create"Folgender Filter erfasst alle GA4 Rohdatenexporten, ohne die Intraday Tabellen. In diesem Beispiel müssen die Project ID und die Dataset ID durch die eigenen Daten ersetzt werden. Am besten testest du den Filter im Log Explorer, bevor du, den Log Sink final erstellst.
Als Ziel für die Log Sink wird ein Pub/Sub Topic gewählt, den du entweder vorher erstellst, oder beim Prozess des Anlegens des Log Sinks anlegst. Dort ist zu dann zu sehen, welche Logs deinen Workflow auslösen würden.
Wir begrenzen hier die Auslösung nur auf Logs, bei denen neue Tabellen angelegt werden, da BigQuery update / merge Limits hat die ansonsten zu Fehlen führt. Bei Nutzung dieser Vorgehensweise ist zu beachten, dass in Dataform die letzten 3 bzw. 8 Tage abgefragt werden sollten, um auch die später geupdateten Daten aus den Update Jobs zu erfassen.
Workflow + Eventarc
Wenn der Log Sink inkl. Pub/Sub Topic als Ziel angelegt ist, können wir den Workflow und den Eventarc Trigger anlegen.
In der Source vom Workflow hinterlegen wir folgenden Code. Dieser Code kümmert sich um folgendes:
- Wir weisen der Variable current_date die aktuellen DATETIME zu
- Wir setzen die Repository Variable, die sie aus den auszuführendem Dataform Workflow definiert
- Dataform Ausführung – wir übergeben bei vars den Tabellennamen an Dataform und definieren den Workspace unter dem Punkt gitCommitish.
- Sollte es Probleme geben das GitHub Repository zu erreichen und die Compilation Results zu erstellen, gibt es 3 Retry Versuche
main:
params: [args]
steps:
- init:
assign:
- current_date: ${time.format(sys.now())}
- repository: projects/se-ifs-prod/locations/europe-west3/repositories/marketing_dwh
- createCompilationResult:
try:
call: http.post
args:
url: ${"https://dataform.googleapis.com/v1beta1/" + repository + "/compilationResults"}
auth:
type: OAuth2
body:
gitCommitish: dev
codeCompilationConfig:
vars:
current_date: ${current_date}
result: compilationResult
retry: ${http.default_retry}
- createWorkflowInvocation:
call: http.post
args:
url: ${"https://dataform.googleapis.com/v1beta1/" + repository + "/workflowInvocations"}
auth:
type: OAuth2
body:
compilationResult: ${compilationResult.body.name}
result: workflowInvocation
- complete:
return: ${workflowInvocation.body.name}
Dataform Workspace
Im Dataform Workspace setzen wir im dataform.json die Umgebungsvariable unter vars.
{
"defaultSchema": "ga4_transformations",
"assertionSchema": "ga4_assertions",
"warehouse": "bigquery",
"defaultDatabase": "GCP_PROJECT",
"defaultLocation": "REGION",
"vars": {
"current_date": "not_set"
}
}
Dieser Variable können wir dann in den SQLX Dateien referenzieren und z.B. im Where Clause im SQL nutzen.
Wir nutzen die Environment Variable „current_date“ dann in Dataform, um bei manueller Auslösung im Workspace alle Daten abzufragen. Wenn die Automatisierung über Workflows eine Ausführung triggert, filtern wir mit „_table_suffix >= format_date(„%Y%m%d“,current_date()-3)“ nur die letzten 3 Tage der GA4 Rohdaten.
${when(dataform.projectConfig.vars.current_date !== "not_set",`_table_suffix >= format_date("%Y%m%d",current_date()-3)`,true)}
Zusammenfassung
Die Automatisierung über Workflows ist flexibel und über die Dataform API nach Belieben anpassbar. So können Umgebungsvariablen, prefix für Tabellen oder Datsets, oder auszuführende Tags an den Dataform Workspace übergeben werden.
Die Eventarc Trigger bieten durch die native Integration in die GCP Dienste eine große Flexibilität. Bei größeren Projekten, bei denen mehrere unterschiedliche Datenquellen, wie z.B. der Google Ads Rohdatenexport, oder Meta Rohdaten genutzt werden, bietet es sich an, weitere Trigger für die Auslösung des Workflows zu nutzen. In Verbindung mit den Dataform Tags können dann nur die Bestandteile einer SQL Pipeline ausgeführt werden, die von dem Rohdatenupdate betroffen sind.
Für das Deployment dieser Automatisierung nutze ich aktuell ein Terraform Script. Wie dieses genutzt werden kann, zeige ich einem der nächsten Beiträge.