{"id":450,"date":"2026-09-08T10:10:00","date_gmt":"2026-09-08T08:10:00","guid":{"rendered":"https:\/\/www.lukaswojcik.com\/?p=450"},"modified":"2026-10-24T02:25:45","modified_gmt":"2026-10-24T00:25:45","slug":"automated-ga4-meta-capi-event-validation-in-bigquery","status":"publish","type":"post","link":"https:\/\/www.lukaswojcik.com\/blog\/en\/digital-analytics\/tutorials\/automated-ga4-meta-capi-event-validation-in-bigquery\/","title":{"rendered":"Automated GA4 & Meta CAPI Event Validation in BigQuery"},"content":{"rendered":"
Reconciling GA4 with Meta CAPI shows which expected business events are evidenced in each source. A difference does not establish its cause: consent, different event definitions, processing delays and technical failures can produce the same pattern. GA4 is not necessarily a browser-only stream either.<\/p>\n
1. Define what should match first<\/h2>\n
The starting point is a reliable list of business events, such as confirmed orders. Comparing only two tracking tables cannot reveal an event missing from both. The reference list also needs its own completeness checks.<\/p>\n
A stable key connects the sources. UUIDv4 is an option, not a requirement or encryption method. Retries retain the same identifier for the same event; distinct events receive distinct identifiers. An additional scope prevents collisions between shops or data sources.<\/p>\n
Event names need an explicit mapping, for example GA4 purchase<\/code> and Meta Purchase<\/code> to the common type purchase<\/code>. GA4’s transaction_id<\/code>, a custom reconciliation parameter and Meta’s event_id<\/code> are separate fields. GA4 purchase deduplication and Meta Pixel\/CAPI deduplication have their own rules. A BigQuery match proves neither advertising attribution nor successful deduplication at Meta.<\/p>\nAn independent reference list exposes missing evidence on both sides. Delivery attempts are aggregated before comparison.<\/figcaption><\/figure>\n
2. Prepare three tables before comparing<\/h2>\n
The example below requires custom tables. Linking GA4 does not create these tables automatically; ingestion and data-quality checks must be implemented first.<\/p>\n
\n
expected_events<\/code>: exactly one row per scope_id<\/code>, event_kind<\/code> and event_id<\/code>, plus business_time<\/code> for the actual business event and BOOL fields expected_ga4<\/code> and expected_meta<\/code>. TRUE means identifiable transmission to that service is intended and permitted; FALSE means not expected; NULL means unresolved.<\/li>\n
ga4_observations<\/code>: one row per actually exported event, with the same composite key and ingestion timestamp loaded_at<\/code>. Re-ingesting the same export row must not create another observation. Events actually recorded more than once remain visible.<\/li>\n
capi_attempts<\/code>: one row per delivery attempt and associated event, with the key, loaded_at<\/code> and receipt_state<\/code>. Allowed states are received<\/code>, rejected<\/code> and unknown<\/code>. Only evidence attributable to the particular event qualifies as received<\/code>.<\/li>\n<\/ul>\n
HTTP 200 alone is not proof of attribution. A response containing a received-event count also needs a valid association with the request. An ambiguous or partial batch response must not mark every event as received. Failed and uncertain attempts stay in the log; subsequent successful retries add further records. Access tokens and full customer payloads do not belong in this monitoring log.<\/p>\n
Blank identifiers, repeated parameter keys, unknown event names and observations without a reference event require separate error lists. Invalid records must not disappear silently. scope_id<\/code> defines the fixed relationship between shop, GA4 property or stream and Meta data source.<\/p>\n
3. Evaluate the business day and aggregate retries<\/h2>\n
The query uses a UTC business day four days in the past as an example. This allows time for ordinary delays but does not guarantee completeness. The export, reference list and CAPI log must be sufficiently current before evaluation. A missing table is an ingestion failure, not evidence of zero events.<\/p>\n
GA4 daily tables follow the property’s time zone. The documentation describes event_timestamp<\/code> as receipt time; transmission and the business event can differ. The evaluated day therefore comes from business_time<\/code>. Prepared observation tables must include relevant later arrivals. Reading only the GA4 daily table named for the business day is not generally sufficient.<\/p>\n
-- GoogleSQL. Prepared tables are defined in the article.\n-- @run_time is supplied by BigQuery Scheduled Queries.\nDECLARE target_day DATE DEFAULT DATE_SUB(DATE(@run_time, 'UTC'), INTERVAL 4 DAY);\nDECLARE observed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP();\n\nCREATE TEMP TABLE expected AS\nSELECT scope_id, event_kind, event_id, expected_ga4, expected_meta\nFROM `project_id.monitoring.expected_events`\nWHERE business_time >= TIMESTAMP(target_day, 'UTC')\n AND business_time < TIMESTAMP(DATE_ADD(target_day, INTERVAL 1 DAY), 'UTC');\n\nASSERT (\n SELECT COUNT(*) = 0 FROM expected\n WHERE scope_id IS NULL OR TRIM(scope_id) = ''\n OR event_kind IS NULL OR TRIM(event_kind) = ''\n OR event_id IS NULL OR TRIM(event_id) = ''\n) AS 'Invalid key in expected events';\n\nASSERT NOT EXISTS (\n SELECT 1 FROM expected\n GROUP BY scope_id, event_kind, event_id\n HAVING COUNT(*) > 1\n) AS 'Duplicate business-event key';\n\nCREATE TEMP TABLE result AS\nWITH ga4 AS (\n SELECT g.scope_id, g.event_kind, g.event_id, COUNT(*) AS ga4_rows\n FROM `project_id.monitoring.ga4_observations` g\n JOIN expected e USING (scope_id, event_kind, event_id)\n WHERE g.loaded_at <= observed_at\n GROUP BY g.scope_id, g.event_kind, g.event_id\n),\ncapi AS (\n SELECT c.scope_id, c.event_kind, c.event_id,\n COUNT(*) AS capi_attempts,\n COUNTIF(c.receipt_state = 'received') AS capi_receipts,\n COUNTIF(c.receipt_state = 'unknown') AS capi_unknown\n FROM `project_id.monitoring.capi_attempts` c\n JOIN expected e USING (scope_id, event_kind, event_id)\n WHERE c.loaded_at <= observed_at\n GROUP BY c.scope_id, c.event_kind, c.event_id\n)\nSELECT target_day AS business_day, observed_at AS checked_at, e.*,\n COALESCE(g.ga4_rows, 0) AS ga4_rows,\n COALESCE(c.capi_attempts, 0) AS capi_attempts,\n COALESCE(c.capi_receipts, 0) AS capi_receipts,\n COALESCE(c.capi_unknown, 0) AS capi_unknown\nFROM expected e\nLEFT JOIN ga4 g USING (scope_id, event_kind, event_id)\nLEFT JOIN capi c USING (scope_id, event_kind, event_id);\n\nSELECT *,\n CASE\n WHEN expected_ga4 IS NULL OR expected_meta IS NULL THEN 'SCOPE_UNKNOWN'\n WHEN NOT expected_ga4 OR NOT expected_meta THEN 'OUTSIDE_JOINT_SCOPE'\n WHEN ga4_rows > 0 AND capi_receipts > 0 THEN 'OBSERVED_BOTH'\n WHEN ga4_rows = 0 AND capi_receipts = 0 THEN 'UNCONFIRMED_BOTH'\n WHEN ga4_rows = 0 THEN 'UNCONFIRMED_GA4'\n ELSE 'UNCONFIRMED_CAPI'\n END AS reconciliation_status,\n ga4_rows > 1 AS repeated_ga4_rows,\n (expected_ga4 = FALSE AND ga4_rows > 0)\n OR (expected_meta = FALSE AND capi_attempts > 0) AS unexpected_transmission\nFROM result;<\/code><\/pre>\n
Aggregation prevents two GA4 rows and three CAPI attempts from becoming six apparent business events. Repetition remains visible through counts. UNCONFIRMED<\/code> means missing evidence in the available data, not necessarily a broken tag. unexpected_transmission<\/code> separately flags transmissions not intended according to the reference.<\/p>\n
This is not a complete ingestion pipeline. Table access needs suitable partitioning or limits, permissions and a cost check. Store business day and evaluation time with results. Re-evaluation should deliberately replace older daily snapshots instead of appending duplicates that change the rate.<\/p>\n
4. Alert on findings rather than successful query execution<\/h2>\n
A downstream latest_reconciliation<\/code> view contains exactly the latest successful snapshot per business day and key. The separate query below returns only days exceeding an example threshold. The minimum of 100 and five-percent threshold need justification; neither is a universal quality standard.<\/p>\n
-- A separate scheduled SELECT over the latest successful snapshot.\nWITH counts AS (\n SELECT business_day,\n COUNTIF(expected_ga4 AND expected_meta) AS comparable,\n COUNTIF(expected_ga4 AND expected_meta\n AND (ga4_rows = 0 OR capi_receipts = 0)) AS unconfirmed\n FROM `project_id.monitoring.latest_reconciliation`\n GROUP BY business_day\n),\nrates AS (\n SELECT *, 100 * SAFE_DIVIDE(unconfirmed, comparable) AS rate_pct\n FROM counts\n)\nSELECT *\nFROM rates\nWHERE comparable >= 100 AND rate_pct > 5;<\/code><\/pre>\n
With no comparable events, SAFE_DIVIDE<\/code> leaves the rate unknown. Events outside the joint scope and unresolved expectations stay outside this denominator. Separate checks must monitor those cases, unexpected transmissions, invalid IDs, repeated records and incomplete data feeds.<\/p>\n
The Scheduled Query metric last_execution_job_rows<\/code> counts result rows, not the value of rate_pct<\/code>. A Monitoring rule filtered to this violations-only query can alert when its row count exceeds zero. The previous value can persist after failed or disabled runs. Execution failures and freshness therefore need separate monitoring; an old retained alert is not a new daily finding.<\/p>\n
\n
Questions and answers<\/h2>\n
Does the reconciliation ID require a GA4 custom dimension?<\/h3>\n
Not to access a parameter actually exported in event_params<\/code>. Registration does not guarantee transmission or export, however. Missing or repeated keys need detection during ingestion.<\/p>\n
Are 48 hours and a 06:00 UTC run enough?<\/h3>\n
No. GA4 can update daily tables over several calendar days and occasionally reprocess historical data. CAPI delivery and the local log have separate delays. Waiting periods, freshness checks and re-evaluation of older days belong together; a fixed time does not guarantee export completion.<\/p>\n
Can ads_storage substitute for Meta consent checks?<\/h3>\n
No. Yes<\/code>, No<\/code>, Unset<\/code> and an absent value do not automatically establish permission for a different service. Expectations must reflect the actual documented service configuration and decision. An intentionally unsent event is not a technical fault; an unknown state is not consent.<\/p>\n<\/div>\n
![\"An](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/diagrams\/dia-450-reconciliation-20261024-en.png\")