Churned Customers

{
  "digitalData": {
    "transaction": {
      "transactionID": "txn-1",
      "total": {
        "transactionTotal": 100,
        "currency": "USD"
      }
    },
    "user": [
      {
        "profile": [
          {
            "profileInfo": {
              "profileID": "user-1"
            },
            "attributes": {
              "accountType": "consumer"
            }
          }
        ]
      }
    ],
    "event": [
      {
        "eventInfo": {
          "eventName": "purchase"
        }
      },
      {
        "eventInfo": {
          "eventName": "refund"
        },
        "attributes": {
          "transactionID": "txn-1",
          "value": 100,
          "currency": "USD"
        }
      }
    ]
  }
}

[
  {
    "event": "purchase",
    "ecommerce": {
      "transaction_id": "txn-1",
      "value": 100,
      "currency": "USD",
      "items": [
        {
          "item_id": "SKU-1",
          "item_name": "item",
          "price": 100,
          "quantity": 1
        }
      ]
    },
    "user_id": "user-1",
    "account_type": "consumer"
  },
  {
    "event": "refund",
    "ecommerce": {
      "transaction_id": "txn-1",
      "value": 100,
      "currency": "USD"
    },
    "user_id": "user-1"
  }
]

[
  {
    "event": "purchase",
    "purchase": {
      "transactionID": "txn-1",
      "value": 100,
      "currency": "USD",
      "items": [
        {
          "itemId": "SKU-1",
          "itemName": "item",
          "price": 100,
          "quantity": 1
        }
      ]
    },
    "user": {
      "id": "user-1",
      "accountType": "consumer"
    }
  },
  {
    "event": "refund",
    "refund": {
      "transactionID": "txn-1",
      "value": 100,
      "currency": "USD"
    },
    "user": {
      "id": "user-1"
    }
  }
]

[
  {
    "eventType": "commerce.purchases",
    "commerce": {
      "order": {
        "purchaseID": "txn-1",
        "priceTotal": 100,
        "currencyCode": "USD"
      },
      "purchases": {
        "value": 1
      }
    },
    "identityMap": {
      "customerID": [
        {
          "id": "user-1",
          "authenticatedState": "authenticated",
          "primary": true
        }
      ]
    },
    "_tenantId": {
      "accountType": "consumer"
    }
  },
  {
    "eventType": "refund",
    "commerce": {
      "order": {
        "purchaseID": "txn-1",
        "priceTotal": 100,
        "currencyCode": "USD"
      }
    },
    "identityMap": {
      "customerID": [
        {
          "id": "user-1",
          "authenticatedState": "authenticated",
          "primary": true
        }
      ]
    }
  }
]

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE inactivity_days INT64 DEFAULT 365;

WITH
-- Purchases per transaction (double-fires collapsed), identified logged-in customers only; guest orders without user_id excluded
purchases AS (
  SELECT
    ecommerce.transaction_id AS transaction_id,
    ANY_VALUE(user_id) AS customer_id,
    MIN(PARSE_DATE('%Y%m%d', event_date)) AS purchase_date,
    MAX(ecommerce.purchase_revenue) AS order_value,
    LOGICAL_OR((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'account_type') IN ('test', 'employee', 'reseller')) AS is_excluded_account
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(start_date, INTERVAL inactivity_days DAY)) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'purchase'
    AND user_id IS NOT NULL
    AND ecommerce.transaction_id IS NOT NULL
  GROUP BY transaction_id
),
-- Refunded amount per transaction (cancellations arrive as full refunds)
refunds AS (
  SELECT
    ecommerce.transaction_id AS transaction_id,
    SUM(ecommerce.refund_value) AS refunded_value
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(start_date, INTERVAL inactivity_days DAY)) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'refund'
  GROUP BY transaction_id
),
-- Purchase days that reset the clock: fully refunded orders and test, employee and reseller accounts removed
purchase_days AS (
  SELECT DISTINCT p.customer_id, p.purchase_date
  FROM purchases p
  LEFT JOIN refunds r USING (transaction_id)
  WHERE (r.transaction_id IS NULL OR r.refunded_value < p.order_value)
    AND p.customer_id NOT IN (SELECT customer_id FROM purchases WHERE is_excluded_account)
),
-- Churn date of each purchase and the customer's next purchase, if any
lapses AS (
  SELECT
    customer_id,
    DATE_ADD(purchase_date, INTERVAL inactivity_days DAY) AS churn_date,
    LEAD(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS next_purchase_date
  FROM purchase_days
)
-- Customers whose purchase turned inactivity_days old in the period with no repeat order, counted once
SELECT COUNT(DISTINCT customer_id) AS churned_customers
FROM lapses
WHERE churn_date BETWEEN start_date AND end_date
  AND (next_purchase_date IS NULL OR next_purchase_date > churn_date);

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH
-- Reporting period and inactivity window (relative dates)
params AS (
  SELECT
    date_sub(current_date(), 90) AS start_date,
    date_sub(current_date(), 1) AS end_date,
    365 AS inactivity_days
),
-- Purchases per transaction (double-fires collapsed), identified logged-in customers only; guest orders without customerID excluded
purchases AS (
  SELECT
    e.commerce.order.purchaseID AS transaction_id,
    MAX(e.identityMap['customerID'][0].id) AS customer_id,
    MIN(to_date(e.timestamp)) AS purchase_date,
    MAX(e.commerce.order.priceTotal) AS order_value,
    MAX(CASE WHEN e._tenantId.accountType IN ('test', 'employee', 'reseller') THEN 1 ELSE 0 END) AS is_excluded_account
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE to_date(e.timestamp) BETWEEN date_sub(p.start_date, p.inactivity_days) AND p.end_date
    AND e.commerce.purchases.value = 1
    AND e.identityMap['customerID'][0].id IS NOT NULL
    AND e.commerce.order.purchaseID IS NOT NULL
  GROUP BY e.commerce.order.purchaseID
),
-- Refunded amount per transaction (cancellations arrive as full refunds)
refunds AS (
  SELECT
    e.commerce.order.purchaseID AS transaction_id,
    SUM(e.commerce.order.priceTotal) AS refunded_value
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE to_date(e.timestamp) BETWEEN date_sub(p.start_date, p.inactivity_days) AND p.end_date
    AND e.eventType = 'refund'
  GROUP BY e.commerce.order.purchaseID
),
-- Purchase days that reset the clock: fully refunded orders and test, employee and reseller accounts removed
purchase_days AS (
  SELECT DISTINCT pu.customer_id, pu.purchase_date
  FROM purchases pu
  LEFT JOIN refunds r ON r.transaction_id = pu.transaction_id
  WHERE (r.transaction_id IS NULL OR r.refunded_value < pu.order_value)
    AND pu.customer_id NOT IN (SELECT customer_id FROM purchases WHERE is_excluded_account = 1)
),
-- Churn date of each purchase and the customer's next purchase, if any
lapses AS (
  SELECT
    d.customer_id,
    date_add(d.purchase_date, p.inactivity_days) AS churn_date,
    LEAD(d.purchase_date) OVER (PARTITION BY d.customer_id ORDER BY d.purchase_date) AS next_purchase_date
  FROM purchase_days d
  CROSS JOIN params p
)
-- Customers whose purchase turned inactivity_days old in the period with no repeat order, counted once
SELECT COUNT(DISTINCT l.customer_id) AS churned_customers
FROM lapses l
CROSS JOIN params p
WHERE l.churn_date BETWEEN p.start_date AND p.end_date
  AND (l.next_purchase_date IS NULL OR l.next_purchase_date > l.churn_date);