Customer Retention Rate

{
  "digitalData": {
    "transaction": {
      "transactionID": "T-100245",
      "total": {
        "basePrice": 79,
        "shipping": 5.5,
        "transactionTotal": 84.5,
        "currency": "USD"
      },
      "profile": {
        "profileInfo": {
          "profileID": "CUST-000123"
        }
      }
    },
    "user": [
      {
        "profile": [
          {
            "profileInfo": {
              "profileID": "CUST-000123",
              "loginStatus": "logged_in"
            }
          }
        ]
      }
    ]
  }
}

{
  "event": "purchase",
  "user_id": "CUST-000123",
  "ecommerce": {
    "transaction_id": "T-100245",
    "value": 84.5,
    "currency": "USD",
    "shipping": 5.5,
    "tax": 0,
    "items": [
      {
        "item_id": "SKU-4471",
        "item_name": "Organic Cotton Tee",
        "item_category": "Apparel",
        "price": 39.5,
        "quantity": 2
      }
    ]
  }
}

{
  "event": "purchase",
  "user": {
    "customerId": "CUST-000123",
    "loginStatus": "logged_in"
  },
  "transaction": {
    "purchaseID": "T-100245",
    "total": 84.5,
    "currency": "USD",
    "products": [
      {
        "sku": "SKU-4471",
        "name": "Organic Cotton Tee",
        "category": "Apparel",
        "price": 39.5,
        "quantity": 2
      }
    ]
  }
}

{
  "eventType": "commerce.purchases",
  "identityMap": {
    "CRMID": [
      {
        "id": "CUST-000123",
        "primary": true
      }
    ]
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-100245",
      "priceTotal": 84.5,
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-4471",
      "name": "Organic Cotton Tee",
      "quantity": 2,
      "priceTotal": 79
    }
  ]
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Customer Retention Rate for the last complete month vs the month before it.
-- CRR = ((customers at end - new customers) / customers at start) * 100
WITH params AS (
  SELECT
    DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH) AS cur_start,
    LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH) AS cur_end,
    DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH), MONTH) AS prev_start,
    LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH), MONTH) AS prev_end
),
purchase_events AS (
  SELECT
    e.user_id,
    PARSE_DATE('%Y%m%d', e.event_date) AS purchase_date,
    MAX(IF(ep.key = 'transaction_id', ep.value.string_value, NULL)) AS transaction_id,
    MAX(IF(ep.key = 'value', COALESCE(ep.value.double_value, CAST(ep.value.int_value AS FLOAT64)), NULL)) AS order_value,
    MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) AS traffic_type
  FROM `your-project.analytics_123456789.events_*` AS e,
    UNNEST(e.event_params) AS ep
  WHERE e.event_name = 'purchase'
    AND e.user_id IS NOT NULL
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 26 MONTH))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY e.user_id, e.event_date, e.event_timestamp, e.user_pseudo_id
),
refunds AS (
  -- transactions fully refunded or cancelled up to the end of the current period
  -- (GA4 full refund = refund event sent without an items array)
  SELECT DISTINCT COALESCE(r.transaction_id, r.ecommerce_transaction_id) AS transaction_id
  FROM (
    SELECT
      e.event_timestamp,
      e.user_pseudo_id,
      ANY_VALUE(e.ecommerce.transaction_id) AS ecommerce_transaction_id,
      MAX(IF(ep.key = 'transaction_id', ep.value.string_value, NULL)) AS transaction_id
    FROM `your-project.analytics_123456789.events_*` AS e,
      UNNEST(e.event_params) AS ep
    WHERE e.event_name = 'refund'
      AND ARRAY_LENGTH(e.items) = 0
      AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 26 MONTH))
                            AND FORMAT_DATE('%Y%m%d', LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH))
    GROUP BY e.event_timestamp, e.user_pseudo_id
  ) AS r
  WHERE COALESCE(r.transaction_id, r.ecommerce_transaction_id) IS NOT NULL
),
orders AS (
  -- one row per transaction; drop zero-value orders, test orders (transaction_id prefixed 'test'
  -- or internal traffic) and orders fully refunded or cancelled by the end of the period
  SELECT pe.user_id, MIN(pe.purchase_date) AS purchase_date, pe.transaction_id
  FROM purchase_events AS pe
  LEFT JOIN refunds AS rf
    ON rf.transaction_id = pe.transaction_id
  WHERE pe.transaction_id IS NOT NULL
    AND pe.order_value > 0
    AND NOT STARTS_WITH(LOWER(pe.transaction_id), 'test')
    AND COALESCE(pe.traffic_type, '') != 'internal'
    AND rf.transaction_id IS NULL
  GROUP BY pe.user_id, pe.transaction_id
),
first_purchase AS (
  SELECT user_id, MIN(purchase_date) AS first_purchase_date
  FROM orders
  GROUP BY user_id
),
start_base AS (
  SELECT DISTINCT o.user_id
  FROM orders AS o, params AS p
  WHERE o.purchase_date BETWEEN p.prev_start AND p.prev_end
),
end_base AS (
  SELECT DISTINCT o.user_id
  FROM orders AS o, params AS p
  WHERE o.purchase_date BETWEEN p.cur_start AND p.cur_end
),
new_customers AS (
  SELECT f.user_id
  FROM first_purchase AS f, params AS p
  WHERE f.first_purchase_date BETWEEN p.cur_start AND p.cur_end
)
SELECT
  (SELECT cur_start FROM params) AS period_start,
  (SELECT cur_end FROM params) AS period_end,
  (SELECT COUNT(*) FROM start_base) AS customers_at_start,
  (SELECT COUNT(*) FROM end_base) AS customers_at_end,
  (SELECT COUNT(*) FROM new_customers) AS new_customers,
  (SELECT COUNT(*) FROM end_base AS e JOIN start_base AS s USING (user_id)) AS retained_from_start,
  ROUND(SAFE_DIVIDE(
    (SELECT COUNT(*) FROM end_base) - (SELECT COUNT(*) FROM new_customers),
    (SELECT COUNT(*) FROM start_base)
  ) * 100, 2) AS customer_retention_rate_pct,
  ROUND(SAFE_DIVIDE(
    (SELECT COUNT(*) FROM end_base AS e JOIN start_base AS s USING (user_id)),
    (SELECT COUNT(*) FROM start_base)
  ) * 100, 2) AS cohort_retention_rate_pct;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Customer Retention Rate for the last complete month vs the month before it.
-- CRR = ((customers at end - new customers) / customers at start) * 100
WITH params AS (
  SELECT
    add_months(trunc(current_date(), 'MM'), -1) AS cur_start,
    date_sub(trunc(current_date(), 'MM'), 1) AS cur_end,
    add_months(trunc(current_date(), 'MM'), -2) AS prev_start,
    date_sub(add_months(trunc(current_date(), 'MM'), -1), 1) AS prev_end
),
order_totals AS (
  -- original order value per purchase ID
  SELECT commerce.order.purchaseID AS purchase_id, MAX(commerce.order.priceTotal) AS order_total
  FROM commerce_experience_event_dataset
  WHERE eventType = 'commerce.purchases'
    AND commerce.order.purchaseID IS NOT NULL
    AND timestamp >= add_months(trunc(current_date(), 'MM'), -26)
  GROUP BY commerce.order.purchaseID
),
credit_memos AS (
  -- Adobe Commerce credit memos (refunds) issued up to the end of the current period, summed per order
  SELECT commerce.order.purchaseID AS purchase_id, SUM(commerce.order.priceTotal) AS refunded_total
  FROM commerce_experience_event_dataset
  WHERE eventType = 'commerce.backofficeCreditMemoIssued'
    AND commerce.order.purchaseID IS NOT NULL
    AND timestamp >= add_months(trunc(current_date(), 'MM'), -26)
    AND timestamp < trunc(current_date(), 'MM')
  GROUP BY commerce.order.purchaseID
),
cancellations AS (
  -- Adobe Commerce order cancellations up to the end of the current period (any cancellation counts as full)
  SELECT DISTINCT commerce.order.purchaseID AS purchase_id
  FROM commerce_experience_event_dataset
  WHERE eventType = 'commerce.backofficeOrderCancelled'
    AND commerce.order.purchaseID IS NOT NULL
    AND timestamp >= add_months(trunc(current_date(), 'MM'), -26)
    AND timestamp < trunc(current_date(), 'MM')
),
refunds AS (
  -- purchase IDs fully refunded (summed credit memos >= original order total) or cancelled;
  -- partially refunded orders are kept, matching the GA4 full-refund rule
  SELECT cm.purchase_id
  FROM credit_memos cm
  JOIN order_totals ot ON ot.purchase_id = cm.purchase_id
  WHERE cm.refunded_total >= ot.order_total
  UNION
  SELECT ca.purchase_id
  FROM cancellations ca
),
orders AS (
  -- one row per purchase ID; drop zero-value orders, test orders (purchaseID prefixed 'test')
  -- and orders fully refunded or cancelled by the end of the period
  SELECT
    ev.identityMap['CRMID'][0].id AS customer_id,
    ev.commerce.order.purchaseID AS purchase_id,
    MIN(CAST(ev.timestamp AS DATE)) AS purchase_date
  FROM commerce_experience_event_dataset ev
  LEFT JOIN refunds rf
    ON rf.purchase_id = ev.commerce.order.purchaseID
  WHERE ev.eventType = 'commerce.purchases'
    AND ev.commerce.purchases.value > 0
    AND ev.commerce.order.priceTotal > 0
    AND ev.commerce.order.purchaseID IS NOT NULL
    AND lower(ev.commerce.order.purchaseID) NOT LIKE 'test%'
    AND ev.identityMap['CRMID'][0].id IS NOT NULL
    AND ev.timestamp >= add_months(trunc(current_date(), 'MM'), -26)
    AND rf.purchase_id IS NULL
  GROUP BY ev.identityMap['CRMID'][0].id, ev.commerce.order.purchaseID
),
first_purchase AS (
  SELECT customer_id, MIN(purchase_date) AS first_purchase_date
  FROM orders
  GROUP BY customer_id
),
start_base AS (
  SELECT DISTINCT o.customer_id
  FROM orders o CROSS JOIN params p
  WHERE o.purchase_date BETWEEN p.prev_start AND p.prev_end
),
end_base AS (
  SELECT DISTINCT o.customer_id
  FROM orders o CROSS JOIN params p
  WHERE o.purchase_date BETWEEN p.cur_start AND p.cur_end
),
new_customers AS (
  SELECT f.customer_id
  FROM first_purchase f CROSS JOIN params p
  WHERE f.first_purchase_date BETWEEN p.cur_start AND p.cur_end
),
counts AS (
  SELECT
    (SELECT COUNT(*) FROM start_base) AS customers_at_start,
    (SELECT COUNT(*) FROM end_base) AS customers_at_end,
    (SELECT COUNT(*) FROM new_customers) AS new_customers,
    (SELECT COUNT(*) FROM end_base e JOIN start_base s ON e.customer_id = s.customer_id) AS retained_from_start
)
SELECT
  p.cur_start AS period_start,
  p.cur_end AS period_end,
  c.customers_at_start,
  c.customers_at_end,
  c.new_customers,
  c.retained_from_start,
  ROUND(CASE WHEN c.customers_at_start = 0 THEN NULL
        ELSE (c.customers_at_end - c.new_customers) * 100.0 / c.customers_at_start END, 2) AS customer_retention_rate_pct,
  ROUND(CASE WHEN c.customers_at_start = 0 THEN NULL
        ELSE c.retained_from_start * 100.0 / c.customers_at_start END, 2) AS cohort_retention_rate_pct
FROM counts c CROSS JOIN params p;