Abandoned Cart Recovery Rate

{
  "digitalData": {
    "cart": {
      "cartID": "c_82f1",
      "attributes": {
        "merchantID": "m_31"
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-100"
          },
          "quantity": 1
        }
      ]
    },
    "transaction": {
      "transactionID": "o_93a2",
      "attributes": {
        "merchantID": "m_31",
        "cartID": "c_82f1"
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-100"
          },
          "quantity": 1
        }
      ]
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "purchase",
          "eventAction": "purchase"
        },
        "attributes": {
          "merchantID": "m_31",
          "cartID": "c_82f1",
          "transactionID": "o_93a2"
        }
      }
    ]
  }
}

{
  "event": "purchase",
  "merchant_id": "m_31",
  "cart_id": "c_82f1",
  "ecommerce": {
    "transaction_id": "o_93a2",
    "items": [
      {
        "item_id": "SKU-100",
        "quantity": 1
      }
    ]
  }
}

[
  {
    "event": "cart_add",
    "cart": {
      "merchantID": "m_31",
      "cartID": "c_82f1"
    },
    "commerce": {
      "productListAdds": {
        "value": 1
      }
    },
    "productListItems": [
      {
        "SKU": "SKU-100",
        "quantity": 1
      }
    ]
  },
  {
    "event": "purchase",
    "cart": {
      "merchantID": "m_31",
      "cartID": "c_82f1"
    },
    "commerce": {
      "purchases": {
        "value": 1
      },
      "order": {
        "purchaseID": "o_93a2"
      }
    },
    "productListItems": [
      {
        "SKU": "SKU-100",
        "quantity": 1
      }
    ]
  }
]

{
  "eventType": "commerce.purchases",
  "identityMap": {},
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "o_93a2"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-100",
      "quantity": 1
    }
  ],
  "_tenant": {
    "cartContext": {
      "merchantID": "m_31",
      "cartID": "c_82f1"
    }
  }
}

-- BigQuery Standard SQL integration template; not a query over native GA4 tables.
-- Bind the three logical relations below to governed backend integration views.
-- Browser events alone cannot establish abandonment or the denominator.
-- This shared contract applies to both SQL templates. No deployment is asserted.
--
-- CONFIGURATION CONTRACT
-- cart_recovery_config has exactly one validated row:
-- abandonment_seconds INT64 > 0, recovery_seconds INT64 > 0,
-- arrival_seconds INT64 >= 0, report_lookback_seconds INT64 > 0,
-- report_end_lag_seconds INT64 >= 0, reporting_timezone STRING (valid IANA zone),
-- restatement_policy STRING = 'recompute_affected_cohorts'. No default durations.
-- Upstream validation rejects missing/duplicate rows, nulls, invalid signs,
-- invalid timezones and values that overflow timestamp arithmetic.
-- abandonment_seconds, recovery_seconds and arrival_seconds govern fact creation.
-- Preserve the applied recovery and arrival durations on each abandonment fact.
-- Use UTC whole-second timestamps and the same observation second on both platforms.
-- Report boundaries are relative to observation time. For local calendar periods,
-- resolve boundaries in reporting_timezone into elapsed-second offsets first.
--
-- FACT CONTRACT
-- cart_abandonment_facts: merchant_id STRING, canonical_cart_id STRING,
-- abandonment_ts TIMESTAMP, recovery_seconds INT64, arrival_seconds INT64,
-- measurement_complete BOOL. Unique key: (merchant_id, canonical_cart_id).
-- Keys must be nonempty; timestamps and durations must be nonnull and valid.
-- Build facts from complete ordered backend cart history, not a reporting-window scan.
-- Resolve merged, restored and checkout identifiers into one merchant-scoped lifecycle.
-- Freeze its first abandonment at inactivity expiry while the cart is nonempty,
-- with no accepted, payment-confirmed order at or before expiry. Re-entry cannot reset it.
-- Exclude emptied carts and test, bot or confirmed fraud activity.
-- Deduplicate source deliveries by stable source keys. Order simultaneous updates
-- by authoritative sequence and event ID; quarantine unresolved conflicts.
--
-- completed_order_facts: merchant_id STRING, transaction_id STRING,
-- canonical_cart_id STRING, purchase_ts TIMESTAMP.
-- Unique key: (merchant_id, transaction_id); keys and timestamps are nonnull.
-- Each accepted, payment-confirmed production order links to at most one canonical cart.
-- Deduplicate by merchant/order key and authoritative acceptance time.
-- Require explicit commerce lineage; customer or device identity alone is insufficient.
-- Exclude test, bot and confirmed fraud orders. Partial basket purchases and changed
-- prices qualify. Legitimate later refunds or cancellations retain completion.
-- Read orders through every selected recovery deadline.
--
-- COVERAGE, PRIVACY AND INTERPRETATION
-- measurement_complete is true only with complete lifecycle and order-link coverage
-- through the recovery deadline. No linked order then means observed non-recovery.
-- Missing linkage is unknown coverage. False/null candidate coverage suppresses output.
-- Upstream reconciliation must detect absent lifecycle records, missing keys and
-- unresolved conflicts and block publication; these SELECT queries cannot detect them.
-- Include anonymous and marketing-suppressed carts and purchases with or without contact.
-- No campaign touch or attribution model applies; recovery does not establish causation.
-- Respect collection permission separately from marketing permission. Keep customer
-- linkage in controlled backend systems; send no contact details or contact hashes.
-- Opaque cart/order keys are customer-linkable: Restricted sensitivity and pii_flag=true.
-- All mapping values are synthetic examples. Payloads are fragments, not complete
-- event envelopes. Collection supplies event IDs and timestamps, including XDM _id
-- and timestamp. Empty identityMap is intentional; person identity is unnecessary.
-- _tenant must be replaced with the declared organization namespace before collection.
-- Cart context is custom; commerce purchase and item fields use platform structures.
-- This count-based KPI needs no monetary fields. Report sales separately if needed.
--
-- REPORTING CONTRACT
-- Select first abandonment in [report_start, report_end); finalize only after the
-- recovery deadline plus arrival allowance. Exclude immature carts from final results.
-- Mature-cart results are not estimates for all carts in an unfinished reporting period.
-- Segment both populations by frozen abandonment attributes, retaining unknown values.
-- Combine disjoint populations by summing counts, then recompute the percentage.
-- Recompute affected abandonment cohorts for late data and retain revision history.
--
-- DETERMINISTIC SYNTHETIC TEST SPECIFICATION (NOT EXECUTED)
-- Shared symbols: T is the fixed whole-second test observation instant; S and E are
-- report boundaries derived from valid configuration; A is first abandonment;
-- R > 0 is configured recovery duration; L >= 0 is arrival allowance; D = A + R.
-- Choose S <= A < E and D + L <= T unless a test overrides this.
-- Expected tuples below are (eligible carts, carts with orders, status, percentage).
-- F = 'final_mature_carts'; I = 'incomplete_coverage'. Each case is independent.
-- 1. No facts: (0,0,F,NULL). One complete cart, no order: (1,0,F,0).
-- 2. Four complete carts; two distinct orders linked to only one cart in (A,D]:
--    (4,1,F,25). Duplicate deliveries of either order leave this unchanged.
-- 3. One cart with an order at D: (1,1,F,100); only an order at D+1 second: (1,0,F,0).
-- 4. Empty cart, order before inactivity expiry, or order exactly at expiry:
--    each produces no abandonment fact upstream, hence (0,0,F,NULL).
--    A malformed abandonment fact with a purchase at A must fail upstream validation;
--    the SQL join itself never credits a purchase at A.
-- 5. Restored/merged aliases of one lifecycle produce one fact; one linked order:
--    (1,1,F,100). Re-entry leaves A and D unchanged; an order after D remains uncredited.
-- 6. Two merchants share the same cart ID; an order belongs only to the first:
--    (2,1,F,50). One order linked to two carts fails upstream uniqueness validation.
-- 7. A customer/device-only match cannot credit a cart. With complete authoritative
--    linkage proving no matching cart order: (1,0,F,0); unavailable linkage: NULLs/I.
-- 8. Mature cart at S: (1,0,F,0); cart at E: (0,0,F,NULL).
--    At T=D+L-1 second: (0,0,F,NULL); at T=D+L: (1,0,F,0), with no orders.
-- 9. One candidate with false or null measurement_complete:
--    (NULL,NULL,I,NULL), even if an apparent linked order exists.
--    An absent lifecycle or missing key must block publication upstream, not pass as zero.
-- 10. Changing only marketing contact, suppression or shopper identification leaves
--     all counts unchanged. Collection restrictions must be reflected in coverage.
-- 11. Test, bot and fraud carts produce no facts. Test/bot/fraud orders do not credit
--     an otherwise valid cart: (1,0,F,0). A valid partial purchase with changed prices:
--     (1,1,F,100); a later legitimate refund or cancellation leaves it unchanged.
--     Unresolved lifecycle conflicts block publication upstream.
-- 12. Disjoint groups with 1/2 and 1/8 yield (10,2,F,20), not an average of percentages.
--     Later segment changes leave the original segment assignment unchanged.
--     A late qualifying order changes its original cohort from (1,0,F,0) to (1,1,F,100).
-- 13. Reject missing, duplicate or null configuration; zero inactivity/recovery/lookback;
--     negative durations or end lag; invalid timezone; and arithmetic overflow.
--     Zero arrival allowance and zero end lag are valid.
-- Run these cases against both integrations with identical T, boundaries and facts.
-- Require identical counts/status and percentage difference <= 0.000001.
WITH bounds AS (
  SELECT
    TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), SECOND) AS observation_ts,
    report_lookback_seconds,
    report_end_lag_seconds
  FROM cart_recovery_config
), candidates AS (
  SELECT
    c.merchant_id,
    c.canonical_cart_id,
    c.abandonment_ts,
    TIMESTAMP_ADD(c.abandonment_ts, INTERVAL c.recovery_seconds SECOND) AS recovery_end_ts,
    c.measurement_complete
  FROM cart_abandonment_facts AS c
  CROSS JOIN bounds AS b
  WHERE c.abandonment_ts >= TIMESTAMP_SUB(
      TIMESTAMP_SUB(b.observation_ts, INTERVAL b.report_end_lag_seconds SECOND),
      INTERVAL b.report_lookback_seconds SECOND)
    AND c.abandonment_ts < TIMESTAMP_SUB(b.observation_ts, INTERVAL b.report_end_lag_seconds SECOND)
    AND TIMESTAMP_ADD(
      TIMESTAMP_ADD(c.abandonment_ts, INTERVAL c.recovery_seconds SECOND),
      INTERVAL c.arrival_seconds SECOND) <= b.observation_ts
), outcomes AS (
  SELECT
    c.merchant_id,
    c.canonical_cart_id,
    c.measurement_complete,
    MAX(IF(o.transaction_id IS NOT NULL, 1, 0)) AS has_order
  FROM candidates AS c
  LEFT JOIN completed_order_facts AS o
    ON o.merchant_id = c.merchant_id
    AND o.canonical_cart_id = c.canonical_cart_id
    AND o.purchase_ts > c.abandonment_ts
    AND o.purchase_ts <= c.recovery_end_ts
  GROUP BY c.merchant_id, c.canonical_cart_id, c.measurement_complete
), totals AS (
  SELECT
    COUNT(*) AS abandoned_carts,
    COALESCE(SUM(has_order), 0) AS carts_with_orders,
    COUNTIF(measurement_complete IS NULL OR NOT measurement_complete) AS incomplete_carts
  FROM outcomes
)
SELECT
  IF(incomplete_carts > 0, 'incomplete_coverage', 'final_mature_carts') AS result_status,
  IF(incomplete_carts = 0, abandoned_carts, NULL) AS abandoned_carts,
  IF(incomplete_carts = 0, carts_with_orders, NULL) AS abandoned_carts_with_orders,
  IF(incomplete_carts = 0, 100.0 * SAFE_DIVIDE(carts_with_orders, abandoned_carts), NULL) AS abandoned_cart_recovery_rate_pct
FROM totals;

-- AEP Query Service read-only integration template.
-- Bind cart_recovery_config, cart_abandonment_facts and completed_order_facts to
-- integrated datasets implementing the shared contract and synthetic tests in ga4_sql.
-- These are logical integration relations, not native AEP recovery datasets.
-- The shared upstream validation, lifecycle, coverage, privacy and reporting rules
-- are mandatory. This query does not detect missing lifecycle records or validate
-- configuration uniqueness. Neither template nor its tests has been executed here.
--
-- AEP field types:
-- cart_recovery_config: abandonment_seconds, recovery_seconds, arrival_seconds,
-- report_lookback_seconds, report_end_lag_seconds BIGINT;
-- reporting_timezone, restatement_policy STRING. Exactly one validated row.
-- cart_abandonment_facts: merchant_id, canonical_cart_id STRING;
-- abandonment_ts TIMESTAMP; recovery_seconds, arrival_seconds BIGINT;
-- measurement_complete BOOLEAN. Unique nonnull merchant/cart key.
-- completed_order_facts: merchant_id, transaction_id, canonical_cart_id STRING;
-- purchase_ts TIMESTAMP. Unique nonnull merchant/order key; one cart per order.
-- Session timezone must be UTC; input timestamps have whole-second resolution.
-- Match BigQuery's observation second and resolved report boundaries for parity.
WITH bounds AS (
  SELECT
    unix_timestamp(current_timestamp()) AS observation_epoch,
    report_lookback_seconds,
    report_end_lag_seconds
  FROM cart_recovery_config
), candidates AS (
  SELECT
    c.merchant_id,
    c.canonical_cart_id,
    c.abandonment_ts,
    CAST(from_unixtime(unix_timestamp(c.abandonment_ts) + c.recovery_seconds) AS TIMESTAMP) AS recovery_end_ts,
    c.measurement_complete
  FROM cart_abandonment_facts c
  CROSS JOIN bounds b
  WHERE c.abandonment_ts >= CAST(from_unixtime(
      b.observation_epoch - b.report_end_lag_seconds - b.report_lookback_seconds) AS TIMESTAMP)
    AND c.abandonment_ts < CAST(from_unixtime(
      b.observation_epoch - b.report_end_lag_seconds) AS TIMESTAMP)
    AND unix_timestamp(c.abandonment_ts) + c.recovery_seconds + c.arrival_seconds <= b.observation_epoch
), outcomes AS (
  SELECT
    c.merchant_id,
    c.canonical_cart_id,
    c.measurement_complete,
    MAX(CASE WHEN o.transaction_id IS NOT NULL THEN 1 ELSE 0 END) AS has_order
  FROM candidates c
  LEFT JOIN completed_order_facts o
    ON o.merchant_id = c.merchant_id
    AND o.canonical_cart_id = c.canonical_cart_id
    AND o.purchase_ts > c.abandonment_ts
    AND o.purchase_ts <= c.recovery_end_ts
  GROUP BY c.merchant_id, c.canonical_cart_id, c.measurement_complete
), totals AS (
  SELECT
    COUNT(*) AS abandoned_carts,
    COALESCE(SUM(has_order), 0) AS carts_with_orders,
    COALESCE(SUM(CASE WHEN measurement_complete = true THEN 0 ELSE 1 END), 0) AS incomplete_carts
  FROM outcomes
)
SELECT
  CASE WHEN incomplete_carts > 0 THEN 'incomplete_coverage' ELSE 'final_mature_carts' END AS result_status,
  CASE WHEN incomplete_carts = 0 THEN abandoned_carts ELSE NULL END AS abandoned_carts,
  CASE WHEN incomplete_carts = 0 THEN carts_with_orders ELSE NULL END AS abandoned_carts_with_orders,
  CASE WHEN incomplete_carts = 0 THEN 100.0 * carts_with_orders / NULLIF(abandoned_carts, 0) ELSE NULL END AS abandoned_cart_recovery_rate_pct
FROM totals;