Automatyzacja walidacji zdarzeń GA4 i Meta CAPI w BigQuery

Uzgodnienie danych GA4 i Meta CAPI pokazuje, które oczekiwane zdarzenia biznesowe mają potwierdzenie w poszczególnych źródłach. Rozbieżność nie wyjaśnia jednak przyczyny: zgoda, różne definicje zdarzeń, opóźnienia przetwarzania i błędy techniczne mogą dawać ten sam wynik. GA4 nie musi też zawierać wyłącznie danych przeglądarkowych.

1. Najpierw definicja oczekiwanej zgodności

Punktem wyjścia jest wiarygodna lista zdarzeń biznesowych, na przykład potwierdzonych zamówień. Porównanie tylko dwóch tabel śledzenia nie wykryje zdarzenia brakującego w obu. Sama lista referencyjna również wymaga kontroli kompletności.

Źródła łączy stały klucz. UUIDv4 jest możliwością, a nie obowiązkiem ani metodą szyfrowania. Ponowna wysyłka tego samego zdarzenia zachowuje identyfikator; różne zdarzenia otrzymują różne identyfikatory. Dodatkowy zakres zapobiega kolizjom między sklepami i źródłami danych.

Nazwy zdarzeń wymagają jawnego przypisania, na przykład GA4 purchase i Meta Purchase do wspólnego typu purchase. transaction_id w GA4, własny parametr uzgadniania i event_id Meta to osobne pola. Usuwanie powtórzonych zakupów GA4 i deduplikacja Pixel/CAPI mają odrębne reguły. Zgodność w BigQuery nie dowodzi atrybucji reklamowej ani skutecznej deduplikacji w Meta.

2. Trzy przygotowane tabele przed porównaniem

Poniższy przykład wymaga własnych tabel. Połączenie GA4 nie tworzy ich automatycznie; najpierw potrzebne są procesy wczytywania i kontrola jakości danych.

• expected_events: dokładnie jeden wiersz na scope_id, event_kind i event_id, a także business_time jako rzeczywisty czas zdarzenia biznesowego oraz pola BOOL expected_ga4 i expected_meta. TRUE oznacza przewidzianą i dozwoloną identyfikowalną wysyłkę do usługi; FALSE brak takiego oczekiwania; NULL stan nierozstrzygnięty.
• ga4_observations: jeden wiersz na faktycznie wyeksportowane zdarzenie, z tym samym kluczem złożonym i czasem wczytania loaded_at. Ponowny odczyt tego samego wiersza eksportu nie może tworzyć kolejnej obserwacji. Zdarzenia rzeczywiście zarejestrowane wielokrotnie pozostają widoczne.
• capi_attempts: jeden wiersz na próbę wysyłki i powiązane zdarzenie, z kluczem, loaded_at i receipt_state. Dozwolone stany to received, rejected i unknown. Stan received wymaga potwierdzenia możliwego do przypisania konkretnemu zdarzeniu.

Samo HTTP 200 nie dowodzi atrybucji. Odpowiedź z liczbą odebranych zdarzeń wymaga prawidłowego powiązania z żądaniem. Niejasna lub częściowa odpowiedź zbiorcza nie pozwala oznaczyć wszystkich zdarzeń jako odebrane. Błędne i niepewne próby pozostają w logu; późniejsze udane powtórzenia tworzą kolejne wpisy. Tokeny dostępu i pełne dane klientów nie należą do tego dziennika kontroli.

Puste identyfikatory, wielokrotne klucze parametrów, nieznane nazwy zdarzeń i obserwacje bez wpisu referencyjnego wymagają osobnych list błędów. Niepoprawne rekordy nie mogą znikać bez śladu. scope_id określa stałe powiązanie sklepu, usługi lub strumienia GA4 i źródła danych Meta.

3. Ocena dnia biznesowego i agregacja powtórzeń

Przykładowe zapytanie ocenia dzień biznesowy UTC sprzed czterech dni. Zapewnia to odstęp dla zwykłych opóźnień, ale nie gwarantuje kompletności. Eksport, lista referencyjna i log CAPI muszą być wystarczająco aktualne przed oceną. Brak tabeli oznacza problem wczytywania, a nie dowód zerowej liczby zdarzeń.

Dzienne tabele GA4 stosują strefę czasową usługi. Dokumentacja opisuje event_timestamp jako czas odbioru; wysyłka i zdarzenie biznesowe mogą następować w różnym czasie. Dlatego oceniany dzień pochodzi tutaj z business_time. Przygotowane tabele obserwacji muszą zawierać odpowiednie późniejsze dane. Odczyt wyłącznie tabeli GA4 nazwanej datą zdarzenia biznesowego nie zawsze wystarcza.

-- 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;

Agregacja zapobiega zamianie dwóch wierszy GA4 i trzech prób CAPI w sześć pozornych zdarzeń biznesowych. Powtórzenia pozostają widoczne jako liczby. UNCONFIRMED oznacza brak potwierdzenia w dostępnych danych, niekoniecznie wadliwy tag. unexpected_transmission osobno wskazuje wysyłkę nieprzewidzianą według listy referencyjnej.

Przykład nie stanowi kompletnego procesu wczytywania. Dostęp do tabel wymaga odpowiedniego partycjonowania lub ograniczeń, uprawnień i sprawdzenia kosztów. Wyniki powinny zawierać dzień biznesowy i czas kontroli. Ponowna ocena ma zastępować starsze dzienne stany, zamiast dopisywać duplikaty zmieniające wskaźnik.

4. Alarm o wyniku, a nie tylko o wykonaniu SQL

Widok latest_reconciliation zawiera dokładnie najnowszy pomyślnie obliczony stan dla dnia biznesowego i klucza. Osobne zapytanie poniżej zwraca wyłącznie dni przekraczające przykładowy próg. Minimum 100 i granica pięciu procent wymagają uzasadnienia; nie są uniwersalną normą jakości.

-- 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;

Przy braku porównywalnych zdarzeń SAFE_DIVIDE pozostawia wskaźnik nieznany. Zdarzenia poza wspólnym zakresem i nierozstrzygnięte oczekiwania nie wchodzą do mianownika. Osobne kontrole muszą obejmować te przypadki, nieoczekiwaną wysyłkę, niepoprawne ID, powtórzenia i niekompletne dopływy danych.

Metryka Scheduled Query last_execution_job_rows zlicza wiersze wyniku, a nie wartość rate_pct. Reguła Monitoring ograniczona do zapytania zwracającego naruszenia może alarmować przy liczbie wierszy większej od zera. Poprzednia wartość może pozostać po błędzie lub wyłączeniu zadania. Błędy wykonania i aktualność wymagają więc osobnego monitorowania; zachowany stary alarm nie jest nowym wynikiem dziennym.

Kontrola w praktyce: uzgodnienie zamówień z eksportem sklepu

Narzędzie Porównanie przychodów sklepu i GA4 porównuje dwa pliki CSV lokalnie. Wspólnym kluczem jest identyfikator zamówienia. Imiona, nazwiska, adresy e-mail i adresy pocztowe nie są potrzebne: wystarczą ID zamówienia, kwota i waluta. Nie oznacza to automatycznej anonimowości ID zamówienia.

1. Przygotowanie eksportów dla tego samego okresu i strefy czasowej, z identycznym traktowaniem podatków, dostawy i zwrotów. Pozycje towarów i zwroty wymagają wcześniejszego zsumowania do uzgodnionej kwoty na zamówienie.
2. Wybór separatora i znaku dziesiętnego dla każdego pliku, wczytanie i przypisanie kolumn ID, kwoty i waluty. Zera na początku identyfikatora pozostają zachowane.
3. Wybór liczby miejsc dziesiętnych i jawnej tolerancji na zamówienie. Bez przeliczania walut.
4. Kontrola osobnych grup: brak po jednej stronie, powtórzone ID, inna waluta i kwota poza tolerancją.
5. Eksport wyniku i sprawdzenie konkretnych zamówień w systemach źródłowych.

Powtórzone ID są oznaczane zamiast sumowania: ponowne wysłanie zakupu, eksport pozycji i wiersz zwrotu wymagają odmiennej interpretacji. Ujemne kwoty są akceptowane jako liczby, lecz definicja biznesowa nadal musi się zgadzać. Brak zamówienia w Analytics może wynikać ze zgody, momentu pomiaru, filtrów lub błędów zbierania; sam w sobie nie dowodzi wadliwego tagu. Zerowa różnica nie potwierdza eksportu wszystkich zamówień. Narzędzie nie sumuje kwot w różnych walutach.

Definicje wynikają z wybranego eksportu i dokumentacji ecommerce GA4. Schemat Data API rozróżnia ID transakcji i metryki przychodu; ogólny przychód całkowity nie musi odpowiadać kwocie zamówienia sklepu.

Przygotowanie pliku CSV Analytics w BigQuery

Pomocnik eksportu w porównywarce przychodów tworzy zapytanie o zakupy dla istniejącego dziennego eksportu GA4. Projekt, zbiór danych, daty i precyzja zaokrąglania są wprowadzane lokalnie. Strona nie wykonuje SQL ani nie żąda dostępu do BigQuery.

Kolumny wyniku transaction_id, amount i currency odpowiadają polom porównania. Każdy zarejestrowany zakup pozostaje wierszem. Dodatkowa liczba zdarzeń zakupu pomaga znaleźć powtarzające się identyfikatory transakcji; nie są one automatycznie sumowane ani usuwane. Brak kwoty nie jest zastępowany zerem.

1. Przed eksportem sklepu uzgodnienie definicji kwoty zakupu i granic dni. Zapytanie pomija zwroty oraz nie dodaje osobno podatków i dostawy.
2. Wygenerowanie SQL, walidacja w BigQuery i sprawdzenie szacowanych kosztów. Przed wykonaniem ustawienie maksymalnej liczby rozliczanych bajtów.
3. Zapis całego wyniku jako lokalnego CSV. W porównaniu wybór przecinka jako separatora, kropki dziesiętnej i tej samej precyzji co w zapytaniu.
4. Kontrola powtarzających się identyfikatorów i brakujących pól przed interpretacją różnic kwot. Porównanie obsługuje do 20 000 wierszy danych na plik. Identyfikatory w arkuszach powinny pozostać tekstem.

Dwa fikcyjne pliki CSV do pobrania zapewniają powtarzalny pierwszy test, po jednym przypadku w każdej z sześciu grup wyników. Są niezależne od SQL i używają dwóch miejsc dziesiętnych oraz zerowej tolerancji.

Źródła: schemat eksportu GA4, walidacja zapytań i kontrola kosztów oraz zapisywanie wyników jako CSV.

Kontrola praktyczna: tablica items przed wysłaniem

Zdarzenie e-commerce może dotrzeć do Google mimo błędnych danych produktów. GA4 E-Commerce Item Array Validator sprawdza tablicę JSON lokalnie i wskazuje konkretne pole, na przykład items[0].price. Cena zapisana w cudzysłowie, taka jak "12.50", jest zgłaszana jako błąd typu zamiast automatycznej zamiany na liczbę.

Kontrola rozróżnia błędy i uwagi. Powtórzone identyfikatory produktów mogą oznaczać warianty i nie dowodzą podwójnego zarejestrowania zakupów. Brak ceny uniemożliwia obliczenie pełnej wartości. Ilość zero pozostaje zerem, a brak ilości oznacza jeden. Suma uwzględnia cenę po rabacie, bez podatku i wysyłki. Waluta i identyfikator transakcji na poziomie zdarzenia nadal wymagają osobnej kontroli.

Przydatny przebieg pracy łączy kontrolę tablicy, sprawdzenie rzeczywistego żądania sieciowego i późniejsze uzgodnienie zamówień. Poprawna struktura nie dowodzi poprawnego zbierania ani raportowania danych. Podstawą są przewodnik Google po e-commerce oraz dokumentacja purchase.