Automatisierte GA4- & Meta-CAPI-Event-Validierung in BigQuery

Ein Abgleich zwischen GA4 und Meta CAPI zeigt, welche erwarteten Geschäftsvorgänge in den jeweiligen Datenquellen nachweisbar sind. Eine Abweichung erklärt aber noch nicht die Ursache: Einwilligung, unterschiedliche Ereignisdefinitionen, verspätete Verarbeitung und technische Fehler können dasselbe Muster erzeugen. GA4 ist zudem nicht automatisch ein reiner Browserdatenstrom.

1. Erst festlegen, was überhaupt übereinstimmen soll

Die Grundlage ist eine verlässliche Liste der Geschäftsvorgänge, etwa bestätigter Bestellungen. Ein Vergleich nur zwischen zwei Tracking-Tabellen übersieht Vorgänge, die in beiden fehlen. Auch die Referenzliste benötigt eine eigene Vollständigkeitskontrolle.

Ein stabiler Schlüssel verbindet die Quellen. UUIDv4 ist eine Möglichkeit, keine Pflicht und kein Verschlüsselungsverfahren. Derselbe Vorgang behält bei Wiederholungen dieselbe Kennung. Unterschiedliche Vorgänge erhalten unterschiedliche Kennungen. Ein zusätzlicher Geltungsbereich verhindert Kollisionen zwischen Shops oder Datenquellen.

Die Ereignisnamen brauchen eine ausdrückliche Zuordnung, zum Beispiel GA4 purchase und Meta Purchase zum gemeinsamen Typ purchase. Die GA4-Bestellkennung transaction_id, ein eigener Abgleichparameter und Metas event_id sind verschiedene Felder. GA4-Kaufdubletten und Meta-Pixel/CAPI-Deduplizierung folgen jeweils eigenen Regeln. Ein BigQuery-Treffer beweist keine Werbezuordnung und keine erfolgreiche Deduplizierung bei Meta.

2. Drei vorbereitete Tabellen statt ungeprüfter Rohdaten

Das folgende Beispiel setzt eigene Tabellen voraus. Die Tabellen entstehen nicht automatisch durch die Verknüpfung mit GA4; Einleseprozesse und Qualitätsprüfungen müssen zuerst eingerichtet werden.

• expected_events: genau eine Zeile je scope_id, event_kind und event_id; dazu business_time als tatsächlicher Geschäftszeitpunkt sowie expected_ga4 und expected_meta als BOOL. TRUE bedeutet, dass eine identifizierbare Übermittlung für diesen Dienst vorgesehen und zulässig ist; FALSE bedeutet nicht erwartet; NULL bedeutet ungeklärt.
• ga4_observations: eine Zeile je tatsächlich exportiertem Ereignis, mit demselben zusammengesetzten Schlüssel und loaded_at als Einlesezeitpunkt. Eine erneute Einlesung derselben Exportzeile darf keine zusätzliche Beobachtung erzeugen. Tatsächlich mehrfach aufgezeichnete Ereignisse bleiben dagegen sichtbar.
• capi_attempts: eine Zeile je Versandversuch und zugehörigem Ereignis, mit Schlüssel, loaded_at und receipt_state. Zulässige Zustände sind received, rejected und unknown. Nur ein eindeutig zuordenbarer Empfangsnachweis erhält received.

HTTP 200 allein ist kein Attributionsnachweis. Bei einer Antwort mit Empfangsanzahl muss auch die Zuordnung zur Anfrage stimmen. Eine unklare oder teilweise Batch-Antwort darf nicht pauschal jedes Ereignis als empfangen markieren. Fehler und unklare Versuche bleiben im Protokoll; spätere erfolgreiche Wiederholungen werden zusätzlich erfasst. Zugriffstoken und vollständige Kundendaten gehören nicht in dieses Prüfprotokoll.

Leere Kennungen, doppelte Parameterwerte, unbekannte Ereignisnamen und Beobachtungen ohne Referenzvorgang benötigen eigene Fehlerlisten. Ungültige Datensätze dürfen nicht still verschwinden. scope_id umfasst die feste Zuordnung zwischen Shop, GA4-Property beziehungsweise Stream und Meta-Datenquelle.

3. Den Geschäftstag auswerten und Wiederholungen zusammenfassen

Die Abfrage bewertet beispielhaft einen vier Tage zurückliegenden UTC-Geschäftstag. Das schafft Abstand zu normalen Nachläufen, garantiert aber keine Vollständigkeit. Vor dem Lauf müssen Export, Referenzliste und CAPI-Protokoll ausreichend aktuell sein. Fehlende Tabellen sind ein Ladefehler, kein Beweis für null Ereignisse.

GA4-Tagestabellen richten sich nach der Property-Zeitzone. Die Dokumentation beschreibt event_timestamp als Empfangszeitpunkt; Übertragung und Geschäftsvorgang können auseinanderliegen. Deshalb stammt der bewertete Tag hier aus business_time. Die vorbereiteten Beobachtungstabellen müssen passende spätere Eingänge enthalten. Nur die tägliche GA4-Tabelle des Geschäftstags einzulesen reicht dafür nicht generell.

-- GoogleSQL. Prepared tables are defined in the article.
-- @run_time is supplied by BigQuery Scheduled Queries.
DECLARE target_day DATE DEFAULT DATE_SUB(DATE(@run_time, 'UTC'), INTERVAL 4 DAY);
DECLARE observed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP();

CREATE TEMP TABLE expected AS
SELECT scope_id, event_kind, event_id, expected_ga4, expected_meta
FROM `project_id.monitoring.expected_events`
WHERE business_time >= TIMESTAMP(target_day, 'UTC')
  AND business_time < TIMESTAMP(DATE_ADD(target_day, INTERVAL 1 DAY), 'UTC');

ASSERT (
  SELECT COUNT(*) = 0 FROM expected
  WHERE scope_id IS NULL OR TRIM(scope_id) = ''
     OR event_kind IS NULL OR TRIM(event_kind) = ''
     OR event_id IS NULL OR TRIM(event_id) = ''
) AS 'Invalid key in expected events';

ASSERT NOT EXISTS (
  SELECT 1 FROM expected
  GROUP BY scope_id, event_kind, event_id
  HAVING COUNT(*) > 1
) AS 'Duplicate business-event key';

CREATE TEMP TABLE result AS
WITH ga4 AS (
  SELECT g.scope_id, g.event_kind, g.event_id, COUNT(*) AS ga4_rows
  FROM `project_id.monitoring.ga4_observations` g
  JOIN expected e USING (scope_id, event_kind, event_id)
  WHERE g.loaded_at <= observed_at
  GROUP BY g.scope_id, g.event_kind, g.event_id
),
capi AS (
  SELECT c.scope_id, c.event_kind, c.event_id,
         COUNT(*) AS capi_attempts,
         COUNTIF(c.receipt_state = 'received') AS capi_receipts,
         COUNTIF(c.receipt_state = 'unknown') AS capi_unknown
  FROM `project_id.monitoring.capi_attempts` c
  JOIN expected e USING (scope_id, event_kind, event_id)
  WHERE c.loaded_at <= observed_at
  GROUP BY c.scope_id, c.event_kind, c.event_id
)
SELECT target_day AS business_day, observed_at AS checked_at, e.*,
       COALESCE(g.ga4_rows, 0) AS ga4_rows,
       COALESCE(c.capi_attempts, 0) AS capi_attempts,
       COALESCE(c.capi_receipts, 0) AS capi_receipts,
       COALESCE(c.capi_unknown, 0) AS capi_unknown
FROM expected e
LEFT JOIN ga4 g USING (scope_id, event_kind, event_id)
LEFT JOIN capi c USING (scope_id, event_kind, event_id);

SELECT *,
  CASE
    WHEN expected_ga4 IS NULL OR expected_meta IS NULL THEN 'SCOPE_UNKNOWN'
    WHEN NOT expected_ga4 OR NOT expected_meta THEN 'OUTSIDE_JOINT_SCOPE'
    WHEN ga4_rows > 0 AND capi_receipts > 0 THEN 'OBSERVED_BOTH'
    WHEN ga4_rows = 0 AND capi_receipts = 0 THEN 'UNCONFIRMED_BOTH'
    WHEN ga4_rows = 0 THEN 'UNCONFIRMED_GA4'
    ELSE 'UNCONFIRMED_CAPI'
  END AS reconciliation_status,
  ga4_rows > 1 AS repeated_ga4_rows,
  (expected_ga4 = FALSE AND ga4_rows > 0)
    OR (expected_meta = FALSE AND capi_attempts > 0) AS unexpected_transmission
FROM result;

Die Gruppierung verhindert, dass zwei GA4-Zeilen und drei CAPI-Versuche sechs vermeintliche Vorgänge erzeugen. Wiederholungen bleiben als Anzahl sichtbar. UNCONFIRMED bedeutet fehlenden Nachweis im betrachteten Datenbestand, nicht automatisch einen defekten Tag. unexpected_transmission kennzeichnet außerdem Übermittlungen, die laut Referenz nicht vorgesehen waren.

Das Beispiel ist kein vollständiger Einleseprozess. Tabellenzugriffe müssen passend partitioniert oder begrenzt, Berechtigungen eingerichtet und Kosten vorab geprüft werden. Ergebnisse mit Geschäftstag und Prüfzeit speichern; erneute Auswertungen dürfen ältere Tagesstände gezielt ersetzen, statt die Fehlerquote durch angehängte Doppelzeilen zu verändern.

4. Einen Befund alarmieren, nicht nur einen erfolgreichen SQL-Lauf

Eine nachgelagerte Ansicht latest_reconciliation enthält genau den jüngsten erfolgreich berechneten Stand je Geschäftstag und Schlüssel. Die folgende separate Abfrage liefert nur Tage oberhalb eines beispielhaften Schwellenwerts. Die Mindestmenge von 100 und die Grenze von fünf Prozent sind zu begründen, keine allgemein gültigen Qualitätsnormen.

-- A separate scheduled SELECT over the latest successful snapshot.
WITH counts AS (
  SELECT business_day,
    COUNTIF(expected_ga4 AND expected_meta) AS comparable,
    COUNTIF(expected_ga4 AND expected_meta
            AND (ga4_rows = 0 OR capi_receipts = 0)) AS unconfirmed
  FROM `project_id.monitoring.latest_reconciliation`
  GROUP BY business_day
),
rates AS (
  SELECT *, 100 * SAFE_DIVIDE(unconfirmed, comparable) AS rate_pct
  FROM counts
)
SELECT *
FROM rates
WHERE comparable >= 100 AND rate_pct > 5;

Bei einer leeren Vergleichsmenge bleibt die Quote mit SAFE_DIVIDE unbekannt. Fälle außerhalb des gemeinsamen Umfangs und ungeklärte Erwartungen zählen nicht in diesen Nenner. Eigene Kontrollen müssen solche Fälle, unerwartete Übermittlungen, ungültige IDs, Wiederholungen und unvollständige Datenzuflüsse überwachen.

Die Scheduled-Query-Metrik last_execution_job_rows zählt Ergebniszeilen, nicht den Wert von rate_pct. Für die reine Alarmabfrage kann eine gefilterte Monitoring-Regel mit Zeilenzahl größer null verwendet werden. Der letzte Wert kann bei einem fehlgeschlagenen oder abgeschalteten Auftrag weiter bestehen bleiben. Deshalb sind Ausführungsfehler und Aktualität separat zu überwachen; ein dauerhaft gespeicherter alter Alarm ist kein neuer Tagesbefund.

Praxiskontrolle: Bestellungen mit dem Shop-Export abgleichen

Das Werkzeug Shop-Umsatz und GA4 abgleichen vergleicht zwei CSV-Dateien lokal. Die Bestell-ID bildet den gemeinsamen Schlüssel. Namen, E-Mail-Adressen und Anschriften sind nicht nötig: Bestell-ID, Betrag und Währung genügen. Eine Bestell-ID ist dadurch nicht automatisch anonym.

1. Beide Exporte für denselben Zeitraum und dieselbe Zeitzone vorbereiten. Steuern, Versand und Erstattungen müssen gleich behandelt werden. Artikelpositionen und Erstattungen vorab zu einem vereinbarten Betrag je Bestellung zusammenfassen.
2. Trennzeichen und Dezimalzeichen je Datei wählen, Datei laden und die Spalten für Bestell-ID, Betrag und Währung zuordnen. Kennungen mit führenden Nullen bleiben Text.
3. Nachkommastellen und eine ausdrückliche Toleranz je Bestellung wählen. Es findet keine Währungsumrechnung statt.
4. Die getrennten Gruppen prüfen: auf einer Seite fehlend, doppelte ID, andere Währung und Betrag außerhalb der Toleranz.
5. Ergebnis exportieren und einzelne Bestellungen in den Quellsystemen untersuchen.

Doppelte IDs werden markiert statt addiert: Ein mehrfach gesendetes Kaufereignis, ein Export auf Artikelebene und eine Erstattungszeile erfordern unterschiedliche Deutungen. Negative Beträge werden als Zahlen akzeptiert; die fachliche Definition muss dennoch zwischen den Dateien übereinstimmen. Eine fehlende Analytics-Bestellung kann mit Einwilligung, Zeitpunkt, Filtern oder Erfassungsfehlern zusammenhängen und beweist allein keinen defekten Tag. Auch eine Differenz von null belegt nicht, dass alle Bestellungen exportiert wurden. Das Werkzeug addiert keine Gesamtsummen über unterschiedliche Währungen.

Maßgeblich sind der gewählte Export und die GA4-E-Commerce-Dokumentation. Das Data-API-Schema unterscheidet Transaktions-IDs und Umsatzkennzahlen; eine allgemeine Gesamtumsatzspalte ist nicht automatisch mit dem Bestellbetrag eines Shops gleichzusetzen.

Die Analytics-CSV mit BigQuery vorbereiten

Der Exporthelfer im Umsatzabgleich erzeugt eine Kaufabfrage für einen vorhandenen täglichen GA4-Export. Projekt, Datensatz, Zeitraum und Rundungspräzision werden lokal eingegeben. Die Seite führt das SQL nicht aus und fordert keinen BigQuery-Zugriff an.

Die Ergebnisspalten transaction_id, amount und currency passen zu den Vergleichsfeldern. Jeder erfasste Kauf bleibt eine Zeile. Eine zusätzliche Kaufereignis-Anzahl hilft beim Erkennen wiederholter Bestell-IDs; diese werden weder still addiert noch entfernt. Fehlende Beträge werden nicht durch null ersetzt.

1. Vor dem Shop-Export die Definition des Kaufbetrags und die Tagesgrenzen abstimmen. Diese Abfrage schließt Erstattungen aus und addiert Steuer oder Versand nicht separat.
2. SQL erzeugen, in BigQuery validieren und die Kostenschätzung ansehen. Vor der Ausführung eine Obergrenze für abrechenbare Bytes festlegen.
3. Das vollständige Ergebnis als lokale CSV speichern. Im Vergleich Komma als Feldtrenner, Dezimalpunkt und dieselben Nachkommastellen wie in der Abfrage wählen.
4. Wiederholte IDs und fehlende Felder vor der Interpretation von Betragsabweichungen untersuchen. Der Vergleich akzeptiert bis zu 20.000 Datenzeilen je Datei. Kennungen in Tabellenprogrammen als Text behandeln.

Zwei herunterladbare, erfundene CSV-Dateien ermöglichen einen nachvollziehbaren Einstieg mit je einem Fall in allen sechs Ergebnisgruppen. Die Beispieldateien sind unabhängig vom SQL und verwenden zwei Nachkommastellen bei null Toleranz.

Quellen: GA4-Exportschema, Abfrageprüfung und Kostenkontrolle sowie Abfrageergebnisse als CSV speichern.

Praxisprüfung: das items-Array vor dem Senden kontrollieren

Ein E-Commerce-Ereignis kann erfolgreich ankommen, obwohl seine Produktdaten fehlerhaft sind. Der GA4 E-Commerce Item Array Validator prüft das JSON-Array lokal und nennt das betroffene Feld, etwa items[0].price. Ein Preis in Anführungszeichen wie "12.50" erscheint als Typfehler und wird nicht stillschweigend umgewandelt.

Die Prüfung unterscheidet Fehler von Prüfhinweisen. Wiederholte Artikel-IDs können Varianten bezeichnen und beweisen keine doppelten Käufe. Ohne Preis ist keine vollständige Wertsumme möglich. Mengen von null bleiben null; fehlende Mengen werden mit eins gerechnet. Die Summe verwendet den bereits rabattierten Preis, ohne Steuern und Versand. Währung und Transaktionskennung auf Ereignisebene benötigen weiterhin eine eigene Prüfung.

Ein sinnvoller Ablauf verbindet die Array-Prüfung mit der Kontrolle des tatsächlichen Netzwerkaufrufs und dem anschließenden Bestellabgleich. Eine bestandene Strukturprüfung belegt allein noch keine korrekte Erfassung oder Berichterstattung. Grundlage sind Googles E-Commerce-Anleitung und die purchase-Referenz.