Financing Method

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventName": "add_payment_info",
          "eventAction": "add_payment_info"
        },
        "attributes": {
          "financingMethod": "fha",
          "transactionID": "TXN-20260914-004512"
        }
      }
    ]
  }
}

{
  "event": "add_payment_info",
  "financing_method": "fha",
  "transaction_id": "TXN-20260914-004512"
}

{
  "data": {
    "financingMethod": "fha",
    "transactionID": "TXN-20260914-004512"
  },
  "event": "add_payment_info"
}

{
  "eventType": "add_payment_info",
  "commerce": {
    "order": {
      "purchaseID": "TXN-20260914-004512"
    }
  },
  "_tenant": {
    "propertyTransaction": {
      "financingMethod": "fha"
    },
    "trafficType": "external"
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Financing Method: event-level volume per normalised financing method, stitched to the CRM / loan origination record on transaction_id.
-- Set before running: the _TABLE_SUFFIX date range and the CRM transaction table name.
-- The CRM table stores the Closing Disclosure-confirmed value already in the controlled vocabulary (Jumbo assigned against the FHFA county limit).
WITH financing_events AS (
  SELECT
    e.event_date,
    e.event_timestamp,
    e.event_name,
    e.user_pseudo_id,
    CONCAT(e.user_pseudo_id, '.', CAST((SELECT ep.value.int_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'ga_session_id') AS STRING)) AS session_key,
    LOWER(TRIM((SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'financing_method'))) AS financing_method_raw,
    EXISTS (SELECT 1 FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'financing_method') AS has_financing_param,
    COALESCE(NULLIF(e.ecommerce.transaction_id, ''), (SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'transaction_id')) AS transaction_id,
    (SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'traffic_type') AS traffic_type
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'
    AND e.event_name IN ('add_payment_info', 'generate_lead', 'submit_form')
),
normalised AS (
  SELECT
    f.*,
    CASE
      WHEN f.financing_method_raw IS NULL OR f.financing_method_raw IN ('', 'unknown', 'not set', '(not set)', 'undecided') THEN 'Unknown'
      WHEN f.financing_method_raw IN ('cash', 'all cash', 'cash purchase') THEN 'Cash'
      WHEN f.financing_method_raw IN ('conventional', 'conventional loan', 'conforming') THEN 'Conventional'
      WHEN f.financing_method_raw IN ('jumbo', 'jumbo loan') THEN 'Jumbo'
      WHEN f.financing_method_raw IN ('fha', 'fha loan', 'fha 203(b)', 'fha 203(k)', 'fha 203b', 'fha 203k') THEN 'FHA'
      WHEN f.financing_method_raw IN ('va', 'va loan') THEN 'VA'
      WHEN f.financing_method_raw IN ('usda', 'usda loan', 'usda rural development') THEN 'USDA'
      WHEN f.financing_method_raw IN ('seller financing', 'owner will carry', 'contract for deed', 'land contract') THEN 'Seller Financing'
      WHEN f.financing_method_raw IN ('other', 'assumed', 'assumable', 'lease purchase', 'private', 'private loan', 'hard money') THEN 'Other'
      ELSE 'Unknown'
    END AS financing_method
  FROM financing_events AS f
  WHERE f.event_name = 'add_payment_info'
     OR f.has_financing_param
),
crm_transactions AS (
  SELECT
    t.transaction_id,
    t.confirmed_financing_method,
    t.transaction_status,
    t.is_test_record
  FROM (
    SELECT
      transaction_id,
      financing_method AS confirmed_financing_method,
      transaction_status,
      is_test_record,
      ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY status_updated_at DESC) AS rn
    FROM `your-project.crm.property_transactions`
    WHERE transaction_id IS NOT NULL
  ) AS t
  WHERE t.rn = 1
)
SELECT
  n.financing_method,
  COUNT(*) AS events,
  COUNTIF(n.event_name = 'add_payment_info') AS add_payment_info_events,
  COUNTIF(n.event_name = 'generate_lead') AS generate_lead_events,
  COUNTIF(n.event_name = 'submit_form') AS submit_form_events,
  COUNT(DISTINCT n.user_pseudo_id) AS users,
  COUNT(DISTINCT n.session_key) AS sessions,
  COUNT(DISTINCT n.transaction_id) AS transactions_captured,
  COUNT(DISTINCT c.transaction_id) AS transactions_matched_to_crm,
  COUNT(DISTINCT IF(c.confirmed_financing_method = n.financing_method, c.transaction_id, NULL)) AS transactions_confirmed_same_method,
  SAFE_DIVIDE(COUNT(*), SUM(COUNT(*)) OVER ()) AS share_of_events
FROM normalised AS n
LEFT JOIN crm_transactions AS c
  ON n.transaction_id IS NOT NULL
 AND c.transaction_id = n.transaction_id
WHERE COALESCE(n.traffic_type, '') <> 'internal'
  AND COALESCE(c.is_test_record, FALSE) = FALSE
GROUP BY n.financing_method
ORDER BY events DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Financing Method: event-level volume per normalised financing method, stitched to the CRM / loan origination dataset on transaction_id.
-- Set before running: the timestamp range and the CRM backend dataset name.
-- Sessions use SESS_TIMEOUT (30 minutes) over all web events in the window before the financing events are filtered.
WITH sessionised AS (
  SELECT
    w._id AS event_id,
    w.timestamp,
    w.eventType AS event_type,
    w.identityMap['ECID'][0].id AS ecid,
    SESS_TIMEOUT(w.timestamp, 60 * 30) OVER (PARTITION BY w.identityMap['ECID'][0].id ORDER BY w.timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session,
    LOWER(TRIM(w._tenant.propertyTransaction.financingMethod)) AS financing_method_raw,
    w._tenant.propertyTransaction.financingMethod IS NOT NULL AS has_financing_value,
    NULLIF(w.commerce.order.purchaseID, '') AS transaction_id,
    w._tenant.trafficType AS traffic_type
  FROM web_experience_events AS w
  WHERE w.timestamp >= TIMESTAMP '2026-08-01 00:00:00'
    AND w.timestamp < TIMESTAMP '2026-09-01 00:00:00'
),
normalised AS (
  SELECT
    s.event_id,
    s.event_type,
    s.ecid,
    CONCAT(s.ecid, '-', CAST(s.session.num AS STRING)) AS session_key,
    s.transaction_id,
    s.traffic_type,
    CASE
      WHEN s.financing_method_raw IS NULL OR s.financing_method_raw IN ('', 'unknown', 'not set', '(not set)', 'undecided') THEN 'Unknown'
      WHEN s.financing_method_raw IN ('cash', 'all cash', 'cash purchase') THEN 'Cash'
      WHEN s.financing_method_raw IN ('conventional', 'conventional loan', 'conforming') THEN 'Conventional'
      WHEN s.financing_method_raw IN ('jumbo', 'jumbo loan') THEN 'Jumbo'
      WHEN s.financing_method_raw IN ('fha', 'fha loan', 'fha 203(b)', 'fha 203(k)', 'fha 203b', 'fha 203k') THEN 'FHA'
      WHEN s.financing_method_raw IN ('va', 'va loan') THEN 'VA'
      WHEN s.financing_method_raw IN ('usda', 'usda loan', 'usda rural development') THEN 'USDA'
      WHEN s.financing_method_raw IN ('seller financing', 'owner will carry', 'contract for deed', 'land contract') THEN 'Seller Financing'
      WHEN s.financing_method_raw IN ('other', 'assumed', 'assumable', 'lease purchase', 'private', 'private loan', 'hard money') THEN 'Other'
      ELSE 'Unknown'
    END AS financing_method
  FROM sessionised AS s
  WHERE s.event_type = 'add_payment_info'
     OR (s.event_type IN ('generate_lead', 'submit_form') AND s.has_financing_value)
),
crm_transactions AS (
  SELECT
    t.transaction_id,
    t.confirmed_financing_method,
    t.transaction_status,
    t.is_test_record
  FROM (
    SELECT
      transaction_id,
      financing_method AS confirmed_financing_method,
      transaction_status,
      is_test_record,
      ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY status_updated_at DESC) AS rn
    FROM aep_crm_property_transactions
    WHERE transaction_id IS NOT NULL
  ) AS t
  WHERE t.rn = 1
)
SELECT
  n.financing_method,
  COUNT(*) AS events,
  SUM(CASE WHEN n.event_type = 'add_payment_info' THEN 1 ELSE 0 END) AS add_payment_info_events,
  SUM(CASE WHEN n.event_type = 'generate_lead' THEN 1 ELSE 0 END) AS generate_lead_events,
  SUM(CASE WHEN n.event_type = 'submit_form' THEN 1 ELSE 0 END) AS submit_form_events,
  COUNT(DISTINCT n.ecid) AS users,
  COUNT(DISTINCT n.session_key) AS sessions,
  COUNT(DISTINCT n.transaction_id) AS transactions_captured,
  COUNT(DISTINCT c.transaction_id) AS transactions_matched_to_crm,
  COUNT(DISTINCT CASE WHEN c.confirmed_financing_method = n.financing_method THEN c.transaction_id END) AS transactions_confirmed_same_method,
  CAST(COUNT(*) AS DOUBLE) / NULLIF(SUM(COUNT(*)) OVER (), 0) AS share_of_events
FROM normalised AS n
LEFT JOIN crm_transactions AS c
  ON n.transaction_id IS NOT NULL
 AND c.transaction_id = n.transaction_id
WHERE COALESCE(n.traffic_type, '') <> 'internal'
  AND COALESCE(c.is_test_record, FALSE) = FALSE
GROUP BY n.financing_method
ORDER BY events DESC;