Skip to main content

Converge BigQuery order lineage: V1 vs. V2

Client-Ready Review & Architecture Specification

Document Version: 1.1 Date: 2026-08-18 Client Focus: UpWellness (upwellness-uw) & Converge BigQuery Connectors Author: LaunchPad Data Engineering Team

1. Executive Summary​

In LaunchPad V1, Converge data was ingested as pre-aggregated ad-day summaries, meaning individual order IDs, customer transaction properties, and purchase timestamps were not captured or stored.

In LaunchPad V2, we have upgraded the Converge integration from daily aggregate buckets to a true 1-to-1 individual order event stream:

  • Every customer checkout event from BigQuery (vw_last_touch_1d) is stored as a distinct order record with its unique sale_id, exact transaction amount, and full JSON properties.
  • Each order is resolved against UpWellness's Meta and Google ad accounts to map the exact Ad ID, Ad Set ID, and Campaign ID.
  • The raw order events land simultaneously in marketing_sales (for complete commerce transaction lineage) and marketing_attribution_events (for real-time dashboard ROAS and CPA reporting).

2. V1 vs. V2 Architecture Comparison​

V1 CONVERGE PIPELINE (Aggregated / Lossy):
BigQuery (daily_ad_attribution)
  ──> Aggregated sums per ad-day
        ──> insights.crm_purchase & crm_revenue updated
        ❌ No individual order IDs
        ❌ No transaction properties or SKUs
        ❌ No multi-account direct attribution mapping

V2 CONVERGE PIPELINE (Individual Order Event Stream):
BigQuery (vw_last_touch_1d event stream)
  ──> 1 Row per unique customer checkout (sale_id = order_id)
        ├──> marketing_sales (Lossless raw sales ledger + normalized transaction identity)
        └──> marketing_attribution_events (Full 4-tier ad hierarchy)
             ✅ Exact conversion ID, profile identity, timestamp, revenue, and full JSON payload
             ✅ 100% matched across 3,559 ads, ad sets, campaigns, accounts
             ✅ Zero unresolved alarms

3. Detailed Technical Deep Dive​

A. How V1 Handled Converge​

In V1 (launchpad-old-v1/app/Services/ConvergeBigQueryOnDemandService.php), V1 executed daily aggregate queries and stamped the resulting sums directly onto ad rows:

  • crm_purchase = SUM(conversions)
  • crm_revenue = SUM(revenue)
  • Grain: daily_aggregate (1 summary number per ad per calendar day).
  • Result: Impossible to audit individual customer receipts or determine which specific products were purchased.

B. How V2 Ingests at Order Grain​

In V2 (src/lib/marketing/platforms/bigquery_converge.ts), LaunchPad connects directly to the client's BigQuery dataset and extracts the raw event stream:

SELECT 
    conversion_date AS date,
    CAST(COALESCE(ad_id, '') AS STRING) AS ad_id,
    CAST(COALESCE(campaign_id, '') AS STRING) AS campaign_id,
    CAST(COALESCE(adset_id, '') AS STRING) AS adset_id,
    1.0 AS conversions,
    COALESCE(
        SAFE_CAST(JSON_VALUE(conversion_properties, '$.total_price') AS FLOAT64),
        SAFE_CAST(JSON_VALUE(conversion_properties, '$.shop_total_price') AS FLOAT64),
        0
    ) AS revenue,
    COALESCE(
        JSON_VALUE(conversion_properties, '$.id'),
        JSON_VALUE(conversion_properties, '$.order_id'),
        conversion_id
    ) AS sale_id,
    CAST(conversion_id AS STRING) AS transaction_id,
    CAST(profile_id AS STRING) AS customer_id,
    CAST(conversion_timestamp AS STRING) AS transaction_at,
    TO_JSON_STRING(conversion_properties) AS raw_payload_json
FROM `upwconverge.upw_converge.vw_last_touch_1d`
WHERE conversion_date >= @since
  AND (event_name IS NULL OR LOWER(TRIM(event_name)) = 'placed order')

4. Live Production Verification Data (UpWellness)​

The normalized ledger uses conversion_id as txn_id, a hash of profile_id as customer_identity_sha, and conversion_timestamp as the trustworthy transaction instant. Initial and upsell classification follows the fixed customer-anchored 24-hour rule documented in upsell-and-line-item-tracking-architecture.md; Converge tags remain audit metadata and do not override that historical definition.

The following values are a dated 2026-08-18 snapshot, not a current health claim. Ingested-order and attributed-order populations have different windows and denominators; rerun the authorized queries before reuse.

Metric DimensionLive Production ValueStatus
Total Ingested Orders (marketing_sales)5,038 orders ($605,556.89)Historical snapshot
Attributed Orders (30-Day Window)3,596 orders ($403,183.54)Different denominator/window
Hierarchy Resolution3,559 ads • 3,555 ad sets • 3,559 campaigns • 3,559 accountsHistorical snapshot
Attribution Match Rate100.0%Historical query result
Unresolved Alarms0Historical UI snapshot

5. Client Review Talking Points​

When walking through this update with UpWellness or other Converge clients, emphasize the following key benefits:

  1. Transaction-Level Auditability:

    "Unlike our legacy reporting system which only received daily summary numbers from BigQuery, LaunchPad V2 now stores every individual order ID, timestamp, and transaction property. You can now audit exact orders directly from your dashboard."

  2. Defined Cost Per Order (CPA): explain the selected conversion identity, attribution weights, window, and denominator. Do not call CPA or AOV exact without reconciling the requested population and multi-item/upsell rules.

  3. Multi-account resolution: describe the source-reviewed mapping mechanics; verify current bindings and unresolved coverage before quoting account counts.

6. Verification Checklist for Client Review​

  • BigQuery event stream (vw_last_touch_1d) validated for live orders.
  • Raw order stream mirrored into marketing_sales (5,038 orders / $605.5K).
  • Credited conversions mapped to 4-tier ad hierarchy in marketing_attribution_events.
  • Zero unresolved alarms on /admin/sync-health.
  • All 1,187 automated unit tests and type checks passing clean.