Customer Lifetime Value

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventName": "purchase",
          "eventAction": "purchase",
          "timeStamp": "2026-03-14T18:42:07Z"
        }
      }
    ],
    "user": [
      {
        "profile": [
          {
            "profileInfo": {
              "profileID": "5f2c8e1a9d4b7036e1c4a8f2b9d7e3a6c0f5b8d2e4a1c7f9b3d6e0a2c5f8b1d4",
              "returningStatus": "returning"
            }
          }
        ]
      }
    ],
    "transaction": {
      "transactionID": "ORD-100245871",
      "total": {
        "basePrice": 137,
        "voucherCode": "WELCOME10",
        "voucherDiscount": 10,
        "currency": "USD",
        "taxRate": 0.08,
        "shipping": 5.99,
        "shippingMethod": "standard_ground",
        "priceWithTax": 137.16,
        "transactionTotal": 143.15
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-48213",
            "productName": "Merino Crew Sweater",
            "sku": "SKU-48213"
          },
          "quantity": 1,
          "price": {
            "basePrice": 89,
            "voucherCode": "WELCOME10",
            "voucherDiscount": 10,
            "currency": "USD"
          }
        },
        {
          "productInfo": {
            "productID": "SKU-30577",
            "productName": "Organic Cotton Tee",
            "sku": "SKU-30577"
          },
          "quantity": 2,
          "price": {
            "basePrice": 24,
            "voucherDiscount": 0,
            "currency": "USD"
          }
        }
      ],
      "attributes": {
        "paymentMethod": "credit_card"
      }
    }
  }
}

{
  "event": "purchase",
  "user_id": "5f2c8e1a9d4b7036e1c4a8f2b9d7e3a6c0f5b8d2e4a1c7f9b3d6e0a2c5f8b1d4",
  "customer_type": "returning",
  "payment_type": "credit_card",
  "ecommerce": {
    "transaction_id": "ORD-100245871",
    "currency": "USD",
    "value": 127,
    "tax": 10.16,
    "shipping": 5.99,
    "coupon": "WELCOME10",
    "items": [
      {
        "item_id": "SKU-48213",
        "item_name": "Merino Crew Sweater",
        "price": 79,
        "quantity": 1,
        "discount": 10
      },
      {
        "item_id": "SKU-30577",
        "item_name": "Organic Cotton Tee",
        "price": 24,
        "quantity": 2,
        "discount": 0
      }
    ]
  }
}

{
  "event": "purchase",
  "identity": {
    "CRMID": "5f2c8e1a9d4b7036e1c4a8f2b9d7e3a6c0f5b8d2e4a1c7f9b3d6e0a2c5f8b1d4",
    "authenticatedState": "authenticated"
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-100245871",
      "priceTotal": 143.15,
      "currencyCode": "USD",
      "taxAmount": 10.16,
      "discountAmount": 10,
      "payments": [
        {
          "transactionID": "PAY-5520913",
          "paymentType": "credit_card",
          "paymentAmount": 143.15,
          "currencyCode": "USD"
        }
      ]
    },
    "shipping": {
      "shippingAmount": 5.99,
      "shippingMethod": "standard_ground"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-48213",
      "name": "Merino Crew Sweater",
      "quantity": 1,
      "priceTotal": 79,
      "discountAmount": 10,
      "currencyCode": "USD"
    },
    {
      "SKU": "SKU-30577",
      "name": "Organic Cotton Tee",
      "quantity": 2,
      "priceTotal": 48,
      "discountAmount": 0,
      "currencyCode": "USD"
    }
  ]
}

{
  "eventType": "commerce.purchases",
  "timestamp": "2026-03-14T18:42:07Z",
  "identityMap": {
    "CRMID": [
      {
        "id": "5f2c8e1a9d4b7036e1c4a8f2b9d7e3a6c0f5b8d2e4a1c7f9b3d6e0a2c5f8b1d4",
        "authenticatedState": "authenticated",
        "primary": true
      }
    ]
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-100245871",
      "priceTotal": 143.15,
      "currencyCode": "USD",
      "taxAmount": 10.16,
      "discountAmount": 10,
      "payments": [
        {
          "transactionID": "PAY-5520913",
          "paymentType": "credit_card",
          "paymentAmount": 143.15,
          "currencyCode": "USD"
        }
      ]
    },
    "shipping": {
      "shippingAmount": 5.99,
      "shippingMethod": "standard_ground"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-48213",
      "name": "Merino Crew Sweater",
      "quantity": 1,
      "priceTotal": 79,
      "discountAmount": 10,
      "currencyCode": "USD"
    },
    {
      "SKU": "SKU-30577",
      "name": "Organic Cotton Tee",
      "quantity": 2,
      "priceTotal": 48,
      "discountAmount": 0,
      "currencyCode": "USD"
    }
  ]
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Historical 36-month Customer Lifetime Value (contribution margin, USD reporting currency).
-- GA4 purchase events link the hashed user_id to transaction_id; the CRM identity map resolves it to customer_id.
-- Margin, refunds, costs, offline orders and first-touch channel grouping come from backend order and CRM tables.
WITH ga4_purchase_events AS (
  SELECT
    e.user_id,
    TIMESTAMP_MICROS(e.event_timestamp) AS event_ts,
    COALESCE(
      NULLIF(e.ecommerce.transaction_id, ''),
      (SELECT MAX(IF(ep.key = 'transaction_id', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep)
    ) AS transaction_id
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE e._TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 72 MONTH))
    AND e.event_name = 'purchase'
    AND e.user_id IS NOT NULL
),
digital_orders AS (
  SELECT
    transaction_id,
    ARRAY_AGG(user_id ORDER BY event_ts LIMIT 1)[OFFSET(0)] AS user_id
  FROM ga4_purchase_events
  WHERE transaction_id IS NOT NULL
  GROUP BY transaction_id
),
valid_orders AS (
  SELECT
    o.order_id,
    COALESCE(o.customer_id, im.customer_id) AS customer_id,
    DATE(o.order_timestamp) AS order_date,
    o.currency_code
  FROM `your-project.commerce_backend.orders` AS o
  LEFT JOIN digital_orders AS d
    ON d.transaction_id = o.order_id
  LEFT JOIN `your-project.crm.customer_identity_map` AS im
    ON im.user_id = d.user_id
  WHERE o.order_status NOT IN ('cancelled', 'fraud_declined')
    AND NOT IFNULL(o.is_test_order, FALSE)
    AND NOT IFNULL(o.is_employee_order, FALSE)
    AND COALESCE(o.customer_id, im.customer_id) IS NOT NULL
),
line_totals AS (
  SELECT
    l.order_id,
    SUM(IF(l.line_status NOT IN ('cancelled', 'exchange_replacement') AND NOT IFNULL(l.is_gift_card_sale, FALSE), l.net_merchandise_amount_excl_tax, 0)) AS net_merchandise_revenue,
    SUM(IF(l.line_status NOT IN ('cancelled', 'exchange_replacement') AND NOT IFNULL(l.is_gift_card_sale, FALSE), l.cogs_amount, 0)) AS cogs_amount
  FROM `your-project.commerce_backend.order_lines` AS l
  GROUP BY l.order_id
),
refund_totals AS (
  SELECT
    r.order_id,
    SUM(r.refunded_merchandise_amount) AS refunded_amount,
    SUM(IFNULL(r.return_shipping_cost, 0) + IFNULL(r.return_processing_cost, 0)) AS return_costs,
    SUM(IFNULL(r.restocked_cogs_amount, 0)) AS restocked_cogs
  FROM `your-project.commerce_backend.refunds` AS r
  GROUP BY r.order_id
),
cost_totals AS (
  SELECT
    c.order_id,
    SUM(IFNULL(c.shipping_revenue_excl_tax, 0)) AS shipping_revenue,
    SUM(IFNULL(c.carrier_cost, 0)) AS carrier_cost,
    SUM(IFNULL(c.pick_pack_cost, 0)) AS fulfilment_cost,
    SUM(IFNULL(c.payment_processing_fee, 0)) AS payment_cost
  FROM `your-project.commerce_backend.order_costs` AS c
  GROUP BY c.order_id
),
order_fx AS (
  -- USD orders use 1; otherwise the latest rate on or before the order date (7-day lookback).
  SELECT order_id, fx_rate
  FROM (
    SELECT
      v.order_id,
      CASE WHEN v.currency_code = 'USD' THEN 1 ELSE fx.fx_rate END AS fx_rate,
      ROW_NUMBER() OVER (PARTITION BY v.order_id ORDER BY fx.rate_date DESC) AS rn
    FROM valid_orders AS v
    LEFT JOIN `your-project.finance.fx_rates_daily` AS fx
      ON fx.from_currency = v.currency_code
     AND fx.to_currency = 'USD'
     AND fx.rate_date BETWEEN DATE_SUB(v.order_date, INTERVAL 7 DAY) AND v.order_date
  ) AS t
  WHERE rn = 1
),
order_margin AS (
  SELECT
    v.customer_id,
    v.order_date,
    (
      IFNULL(lt.net_merchandise_revenue, 0)
      - IFNULL(rf.refunded_amount, 0)
      + (IFNULL(ct.shipping_revenue, 0) - IFNULL(ct.carrier_cost, 0))
      - (IFNULL(lt.cogs_amount, 0) - IFNULL(rf.restocked_cogs, 0))
      - IFNULL(ct.fulfilment_cost, 0)
      - IFNULL(ct.payment_cost, 0)
      - IFNULL(rf.return_costs, 0)
    ) * ofx.fx_rate AS contribution_margin_usd
  FROM valid_orders AS v
  LEFT JOIN order_fx AS ofx ON ofx.order_id = v.order_id
  LEFT JOIN line_totals AS lt ON lt.order_id = v.order_id
  LEFT JOIN refund_totals AS rf ON rf.order_id = v.order_id
  LEFT JOIN cost_totals AS ct ON ct.order_id = v.order_id
),
customer_orders AS (
  SELECT
    om.customer_id,
    om.order_date,
    om.contribution_margin_usd,
    MIN(om.order_date) OVER (PARTITION BY om.customer_id) AS first_order_date
  FROM order_margin AS om
),
customer_clv AS (
  SELECT
    co.customer_id,
    DATE_TRUNC(co.first_order_date, MONTH) AS acquisition_cohort,
    IFNULL(cu.first_touch_channel_group, '(unknown)') AS first_touch_channel_group,
    IF(DATE_ADD(co.first_order_date, INTERVAL 12 MONTH) <= CURRENT_DATE(),
       SUM(IF(co.order_date < DATE_ADD(co.first_order_date, INTERVAL 12 MONTH), co.contribution_margin_usd, 0)), NULL) AS clv_12m,
    IF(DATE_ADD(co.first_order_date, INTERVAL 24 MONTH) <= CURRENT_DATE(),
       SUM(IF(co.order_date < DATE_ADD(co.first_order_date, INTERVAL 24 MONTH), co.contribution_margin_usd, 0)), NULL) AS clv_24m,
    IF(DATE_ADD(co.first_order_date, INTERVAL 36 MONTH) <= CURRENT_DATE(),
       SUM(IF(co.order_date < DATE_ADD(co.first_order_date, INTERVAL 36 MONTH), co.contribution_margin_usd, 0)), NULL) AS clv_36m
  FROM customer_orders AS co
  LEFT JOIN `your-project.crm.customers` AS cu
    ON cu.customer_id = co.customer_id
  WHERE NOT IFNULL(cu.is_wholesale_account, FALSE)
  GROUP BY co.customer_id, co.first_order_date, cu.first_touch_channel_group
)
SELECT
  acquisition_cohort,
  first_touch_channel_group,
  COUNT(DISTINCT customer_id) AS cohort_customers,
  COUNTIF(clv_36m IS NOT NULL) AS customers_with_36m_tenure,
  ROUND(AVG(clv_12m), 2) AS mean_clv_12m_usd,
  ROUND(APPROX_QUANTILES(clv_12m, 2 IGNORE NULLS)[SAFE_OFFSET(1)], 2) AS median_clv_12m_usd,
  ROUND(AVG(clv_24m), 2) AS mean_clv_24m_usd,
  ROUND(APPROX_QUANTILES(clv_24m, 2 IGNORE NULLS)[SAFE_OFFSET(1)], 2) AS median_clv_24m_usd,
  ROUND(AVG(clv_36m), 2) AS mean_clv_36m_usd,
  ROUND(APPROX_QUANTILES(clv_36m, 2 IGNORE NULLS)[SAFE_OFFSET(1)], 2) AS median_clv_36m_usd
FROM customer_clv
WHERE acquisition_cohort >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 60 MONTH), MONTH)
GROUP BY acquisition_cohort, first_touch_channel_group
ORDER BY acquisition_cohort, first_touch_channel_group;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Historical 36-month Customer Lifetime Value (contribution margin, USD reporting currency).
-- Web ExperienceEvents link the hashed identityMap CRMID to commerce.order.purchaseID; the backend identity map resolves it to customer_id.
-- Margin, refunds, costs, offline orders and first-touch channel grouping come from backend order and CRM datasets.
WITH web_purchase_events AS (
  SELECT
    identityMap['CRMID'][0].id AS crm_id_hash,
    commerce.order.purchaseID AS purchase_id,
    ROW_NUMBER() OVER (PARTITION BY commerce.order.purchaseID ORDER BY timestamp) AS rn
  FROM aep_web_experience_events
  WHERE eventType = 'commerce.purchases'
    AND timestamp >= add_months(current_date(), -72)
    AND identityMap['CRMID'][0].id IS NOT NULL
    AND commerce.order.purchaseID IS NOT NULL
),
digital_orders AS (
  SELECT purchase_id, crm_id_hash
  FROM web_purchase_events
  WHERE rn = 1
),
valid_orders AS (
  SELECT
    o.order_id,
    COALESCE(o.customer_id, im.customer_id) AS customer_id,
    to_date(o.order_timestamp) AS order_date,
    o.currency_code
  FROM aep_backend_orders o
  LEFT JOIN digital_orders d
    ON d.purchase_id = o.order_id
  LEFT JOIN aep_backend_customer_identity_map im
    ON im.crm_id_hash = d.crm_id_hash
  WHERE o.order_status NOT IN ('cancelled', 'fraud_declined')
    AND NOT COALESCE(o.is_test_order, false)
    AND NOT COALESCE(o.is_employee_order, false)
    AND COALESCE(o.customer_id, im.customer_id) IS NOT NULL
),
line_totals AS (
  SELECT
    l.order_id,
    SUM(CASE WHEN l.line_status NOT IN ('cancelled', 'exchange_replacement') AND NOT COALESCE(l.is_gift_card_sale, false) THEN l.net_merchandise_amount_excl_tax ELSE 0 END) AS net_merchandise_revenue,
    SUM(CASE WHEN l.line_status NOT IN ('cancelled', 'exchange_replacement') AND NOT COALESCE(l.is_gift_card_sale, false) THEN l.cogs_amount ELSE 0 END) AS cogs_amount
  FROM aep_backend_order_lines l
  GROUP BY l.order_id
),
refund_totals AS (
  SELECT
    r.order_id,
    SUM(r.refunded_merchandise_amount) AS refunded_amount,
    SUM(COALESCE(r.return_shipping_cost, 0) + COALESCE(r.return_processing_cost, 0)) AS return_costs,
    SUM(COALESCE(r.restocked_cogs_amount, 0)) AS restocked_cogs
  FROM aep_backend_refunds r
  GROUP BY r.order_id
),
cost_totals AS (
  SELECT
    c.order_id,
    SUM(COALESCE(c.shipping_revenue_excl_tax, 0)) AS shipping_revenue,
    SUM(COALESCE(c.carrier_cost, 0)) AS carrier_cost,
    SUM(COALESCE(c.pick_pack_cost, 0)) AS fulfilment_cost,
    SUM(COALESCE(c.payment_processing_fee, 0)) AS payment_cost
  FROM aep_backend_order_costs c
  GROUP BY c.order_id
),
order_fx AS (
  -- USD orders use 1; otherwise the latest rate on or before the order date (7-day lookback).
  SELECT order_id, fx_rate
  FROM (
    SELECT
      v.order_id,
      CASE WHEN v.currency_code = 'USD' THEN 1 ELSE fx.fx_rate END AS fx_rate,
      ROW_NUMBER() OVER (PARTITION BY v.order_id ORDER BY fx.rate_date DESC) AS rn
    FROM valid_orders v
    LEFT JOIN aep_backend_fx_rates_daily fx
      ON fx.from_currency = v.currency_code
     AND fx.to_currency = 'USD'
     AND fx.rate_date BETWEEN date_sub(v.order_date, 7) AND v.order_date
  ) t
  WHERE rn = 1
),
order_margin AS (
  SELECT
    v.customer_id,
    v.order_date,
    (
      COALESCE(lt.net_merchandise_revenue, 0)
      - COALESCE(rf.refunded_amount, 0)
      + (COALESCE(ct.shipping_revenue, 0) - COALESCE(ct.carrier_cost, 0))
      - (COALESCE(lt.cogs_amount, 0) - COALESCE(rf.restocked_cogs, 0))
      - COALESCE(ct.fulfilment_cost, 0)
      - COALESCE(ct.payment_cost, 0)
      - COALESCE(rf.return_costs, 0)
    ) * ofx.fx_rate AS contribution_margin_usd
  FROM valid_orders v
  LEFT JOIN order_fx ofx ON ofx.order_id = v.order_id
  LEFT JOIN line_totals lt ON lt.order_id = v.order_id
  LEFT JOIN refund_totals rf ON rf.order_id = v.order_id
  LEFT JOIN cost_totals ct ON ct.order_id = v.order_id
),
customer_orders AS (
  SELECT
    om.customer_id,
    om.order_date,
    om.contribution_margin_usd,
    MIN(om.order_date) OVER (PARTITION BY om.customer_id) AS first_order_date
  FROM order_margin om
),
customer_clv AS (
  SELECT
    co.customer_id,
    to_date(date_trunc('MONTH', co.first_order_date)) AS acquisition_cohort,
    COALESCE(cu.first_touch_channel_group, '(unknown)') AS first_touch_channel_group,
    CASE WHEN add_months(co.first_order_date, 12) <= current_date()
         THEN SUM(CASE WHEN co.order_date < add_months(co.first_order_date, 12) THEN co.contribution_margin_usd ELSE 0 END) END AS clv_12m,
    CASE WHEN add_months(co.first_order_date, 24) <= current_date()
         THEN SUM(CASE WHEN co.order_date < add_months(co.first_order_date, 24) THEN co.contribution_margin_usd ELSE 0 END) END AS clv_24m,
    CASE WHEN add_months(co.first_order_date, 36) <= current_date()
         THEN SUM(CASE WHEN co.order_date < add_months(co.first_order_date, 36) THEN co.contribution_margin_usd ELSE 0 END) END AS clv_36m
  FROM customer_orders co
  LEFT JOIN aep_backend_customers cu
    ON cu.customer_id = co.customer_id
  WHERE NOT COALESCE(cu.is_wholesale_account, false)
  GROUP BY co.customer_id, co.first_order_date, cu.first_touch_channel_group
)
SELECT
  acquisition_cohort,
  first_touch_channel_group,
  COUNT(DISTINCT customer_id) AS cohort_customers,
  COUNT(clv_36m) AS customers_with_36m_tenure,
  ROUND(AVG(clv_12m), 2) AS mean_clv_12m_usd,
  ROUND(percentile_approx(clv_12m, 0.5), 2) AS median_clv_12m_usd,
  ROUND(AVG(clv_24m), 2) AS mean_clv_24m_usd,
  ROUND(percentile_approx(clv_24m, 0.5), 2) AS median_clv_24m_usd,
  ROUND(AVG(clv_36m), 2) AS mean_clv_36m_usd,
  ROUND(percentile_approx(clv_36m, 0.5), 2) AS median_clv_36m_usd
FROM customer_clv
WHERE acquisition_cohort >= to_date(date_trunc('MONTH', add_months(current_date(), -60)))
GROUP BY acquisition_cohort, first_touch_channel_group
ORDER BY acquisition_cohort, first_touch_channel_group;