Automated GA4 & Meta CAPI Event Validation in BigQuery

Modern conversion attribution relies on parallel event transmission: client-side browser tracking via Google Analytics 4 (GA4) alongside server-side tracking via Meta Conversions API (CAPI). However, browser restrictions, Intelligent Tracking Prevention (ITP), ad blockers, and network timeouts frequently cause discrepancies between client-side and server-side event pipelines. Implementing an automated event reconciliation pipeline in Google BigQuery allows data engineering teams to monitor conversion integrity every day, detect missing conversions in a daily run, and verify that both streams carry the same deduplication key.

1. Architectural Framework: Dual-Stream Event Tracking

Reconciling GA4 and Meta CAPI event streams requires a shared, immutable cryptographic identifier across both systems. The reconciliation model is built upon three foundational components:

• Deduplication Key (event_id / transaction_id): Every conversion event generated on the web application must assign a unique UUIDv4 string. This string is transmitted simultaneously as a custom parameter in GA4 (event_id) and as the native event_id parameter in the Meta CAPI payload.
• GA4 BigQuery Daily Export: Raw event streams are exported automatically into the dataset analytics_XXXXXXXXX.events_YYYYMMDD.
• Meta CAPI Server-Side Log Table: A dedicated BigQuery table (e.g., populated via server-side Google Tag Manager BigQuery logging tags or custom webhook sinks) recording all outbound CAPI requests and HTTP response codes.

2. Step-by-Step BigQuery Schema Preparation

To enable accurate SQL reconciliation, both data streams must be normalized into staging views before joining:

1. Extracting GA4 Conversions: In BigQuery, create a dedicated view that flattens GA4 conversion events (such as purchase or generate_lead) and extracts the custom event_id parameter from the nested event_params array.
2. Extracting Meta CAPI Server Logs: Create a matching view from the server-side log table that filters for successful HTTP 200 responses from the Meta Graph API and casts the timestamp to UTC.
3. Time-Window Partitioning: Since server-side retries or offline batch uploads can delay CAPI transmission, reconciliation queries must evaluate a rolling 48-hour time window to prevent false-positive discrepancy alerts.

3. Step-by-Step SQL Reconciliation Query Setup

The following production-grade SQL query performs a FULL OUTER JOIN between the normalized GA4 and Meta CAPI conversion streams, categorizing each transaction to expose silent data losses:

WITH ga4_events AS (
    SELECT
        event_date,
        TIMESTAMP_MICROS(event_timestamp) AS ga4_event_time,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_id') AS event_id,
        event_name
    FROM
        `project_id.analytics_XXXXXXXXX.events_*`
    WHERE
        _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY))
            AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
        AND event_name IN ('purchase', 'generate_lead')
),
capi_events AS (
    SELECT
        DATE(event_timestamp) AS event_date,
        event_timestamp AS capi_event_time,
        event_id,
        event_name AS capi_event_name
    FROM
        `project_id.meta_capi_logs.outbound_events`
    WHERE
        DATE(event_timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY) AND CURRENT_DATE()
        AND http_status_code = 200
)
SELECT
    COALESCE(g.event_id, c.event_id) AS event_id,
    g.event_name AS ga4_name,
    c.capi_event_name AS capi_name,
    CASE
        WHEN g.event_id IS NOT NULL AND c.event_id IS NOT NULL THEN 'MATCHED_BOTH'
        WHEN g.event_id IS NOT NULL AND c.event_id IS NULL THEN 'MISSING_IN_META_CAPI'
        WHEN g.event_id IS NULL AND c.event_id IS NOT NULL THEN 'MISSING_IN_GA4_BROWSER'
    END AS reconciliation_status
FROM
    ga4_events g
FULL OUTER JOIN
    capi_events c
    ON g.event_id = c.event_id
WHERE
    DATE(COALESCE(g.ga4_event_time, c.capi_event_time)) = DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY);

4. Step-by-Step Automated Alerting Configuration

To automate operational monitoring without requiring manual SQL execution, scheduled validation must be configured:

1. BigQuery Scheduled Query: Navigate to BigQuery and select Schedule > Create Scheduled Query. Set the schedule to run daily at 06:00 UTC (after GA4 exports are finalized) and save the output into a monitoring table named analytics_validation.daily_capi_reconciliation.
2. Threshold Detection View: Create an aggregation query that calculates the daily discrepancy percentage:
   SELECT COUNTIF(reconciliation_status != 'MATCHED_BOTH') / COUNT(*) * 100 AS error_rate_pct FROM `analytics_validation.daily_capi_reconciliation`;
3. Cloud Monitoring Alert Policy: In Google Cloud Monitoring, create a log-based metric tracking the scheduled query execution. Define an alert policy that triggers automated Slack or PagerDuty webhooks if the discrepancy rate exceeds 5.0% on any consecutive 24-hour window.

5. Summary & Architectural Value

What this tutorial achieves: The deployment of a fully automated SQL validation and alerting pipeline in BigQuery that reconciles GA4 client-side events with Meta Conversions API server-side logs every day using unique deduplication keys.

Resulting value: Analytics and performance marketing teams gain complete transparency over conversion integrity. Discrepancies caused by broken browser scripts, Safari ITP restrictions, ad blockers, or server-side endpoint failures are flagged by the daily run. Furthermore, verifying that event_id parameters match across both channels shows whether the server-side events carry the same ID as the GA4 events sent from the browser. That is one precondition for Meta to recognize a Pixel event and a CAPI event of the same conversion as duplicates via this ID and the event name, provided the Pixel sends the same ID. The deduplication itself happens at Meta and is not checked by the query.

Practical check: reconcile orders with the shop export

The Shop vs. GA4 Revenue Reconciliation tool compares two CSV files locally. The order ID is the join key. No personal customer data is needed: an order ID, amount and currency are enough.

1. Prepare both exports for the same period and timezone, with the same treatment of tax, shipping and refunds. Aggregate line items and refunds to one agreed amount per order before comparison.
2. Select each file’s separator and decimal mark, then load it. Map the order ID, amount and currency columns. Identifiers with leading zeros remain text.
3. Select the decimal precision and an explicit per-order tolerance. Currency conversion is not performed.
4. Review the separate groups: missing on either side, duplicate ID, different currency and amount outside tolerance.
5. Export the result and investigate individual orders in the source systems.

Duplicate IDs are flagged rather than added together: a repeated purchase event, an item-level export and a refund row require different interpretations. Negative amounts are accepted only as numbers; the business definition still has to agree between files. A missing Analytics order can reflect consent, timing, filtering or collection failures and is not by itself proof of a faulty tag. A zero difference also does not establish that all orders were exported. The tool never combines totals across currencies.

Definitions should follow the chosen export and the GA4 ecommerce documentation. The Data API schema distinguishes transaction IDs and revenue metrics; a generic total-revenue column is not automatically equivalent to a shop’s order amount.

Preparing the Analytics CSV from BigQuery

The export helper in the revenue reconciliation tool generates a purchase query for an existing daily GA4 export. Project, dataset, dates and rounding precision are entered locally. The page does not run the SQL or request BigQuery access.

The resulting columns transaction_id, amount and currency match the comparison fields. Each recorded purchase remains a row. An additional purchase-event count helps identify repeated transaction IDs; these are not silently summed or discarded. A missing amount is not replaced with zero.

1. Agree on the purchase-amount definition and reporting-day boundaries before creating the shop file. This query excludes refunds and does not add tax or shipping separately.
2. Generate SQL, validate it in BigQuery and inspect the cost estimate. Set a maximum bytes billed before execution.
3. Save the complete results as a local CSV. Load the file with comma-separated fields and decimal dot, using the same precision as the query.
4. Review repeated IDs and missing fields before interpreting amount differences. The comparison accepts up to 20,000 data rows per file. Keep identifiers as text when using spreadsheet software.

Two downloadable invented CSV files provide a reproducible first test, with one case in each of the six result groups. They are independent of the SQL and use two decimals with zero tolerance.

References: GA4 export schema, query validation and cost controls, and saving query results as CSV.

Practical check: inspect the items array before sending

An e-commerce event can arrive successfully while its product data is wrong. The GA4 E-Commerce Item Array Validator checks the JSON array locally and points to the affected field, for example items[0].price. A quoted price such as "12.50" is reported as a type error instead of silently converted.

The check distinguishes errors from review notes. Repeated product IDs can represent variants; they do not automatically prove duplicate purchases. Missing prices prevent a complete calculated value. Zero quantities remain zero, while omitted quantities default to one. The sum uses the already discounted price and excludes tax and shipping. Event-level currency and transaction identifiers still need a separate check.

A useful workflow is to check the array, inspect the actual network request and then reconcile the recorded orders. Passing this structural check alone does not establish correct collection or reporting. Rules and limits follow Google’s e-commerce guide and the purchase reference.