Last Purchase Recency Band

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Last Purchase Recency Band per registered customer and snapshot date, rolled up to the band distribution over all registered customers in scope.
-- Backend source: commerce order table + CRM customer master (no GA4 events export).
-- Single analysis date: snapshot_start_date = snapshot_end_date. Trend rebuild: widen the range; each snapshot only uses orders placed on or before it.
-- Rerun the latest 3 snapshot dates daily so late POS and marketplace orders restate them; older snapshots stay frozen.
WITH params AS (
  SELECT
    DATE '2026-09-14' AS snapshot_start_date,
    DATE '2026-09-14' AS snapshot_end_date,
    'America/New_York' AS reporting_tz
),
snapshots AS (
  SELECT snapshot_date
  FROM params AS p,
    UNNEST(GENERATE_DATE_ARRAY(p.snapshot_start_date, p.snapshot_end_date, INTERVAL 1 DAY)) AS snapshot_date
),
customer_master AS (
  SELECT
    c.customer_id AS source_customer_id,
    COALESCE(m.resolved_customer_id, c.customer_id) AS customer_id,
    c.registered_at,
    c.email_sha256,
    c.email_verified,
    c.erased_at,
    c.is_test_account
  FROM `your-project.crm.customers` AS c
  LEFT JOIN `your-project.crm.customer_id_map` AS m
    ON m.source_customer_id = c.customer_id
  WHERE c.account_type = 'registered'
),
registered_customers AS (
  SELECT
    cm.customer_id,
    DATE(MIN(cm.registered_at), p.reporting_tz) AS registered_date
  FROM customer_master AS cm
  CROSS JOIN params AS p
  GROUP BY cm.customer_id, p.reporting_tz
  HAVING COUNTIF(cm.erased_at IS NOT NULL) = 0
    AND COUNTIF(COALESCE(cm.is_test_account, FALSE)) = 0
),
verified_emails AS (
  SELECT
    email_sha256,
    MIN(customer_id) AS customer_id
  FROM customer_master
  WHERE email_verified = TRUE
    AND email_sha256 IS NOT NULL
    AND erased_at IS NULL
  GROUP BY email_sha256
  HAVING COUNT(DISTINCT customer_id) = 1
),
qualifying_orders AS (
  SELECT
    COALESCE(m.resolved_customer_id, o.customer_id, ve.customer_id) AS customer_id,
    DATE(o.order_placed_at, p.reporting_tz) AS order_date
  FROM `your-project.commerce.orders` AS o
  CROSS JOIN params AS p
  LEFT JOIN `your-project.crm.customer_id_map` AS m
    ON m.source_customer_id = o.customer_id
  LEFT JOIN verified_emails AS ve
    ON o.customer_id IS NULL
    AND ve.email_sha256 = o.customer_email_sha256
  WHERE DATE(o.order_placed_at, p.reporting_tz) <= p.snapshot_end_date
    AND (o.order_status = 'completed' OR o.financial_status IN ('paid', 'partially_refunded', 'refunded'))
    AND COALESCE(o.order_status, '') NOT IN ('cancelled', 'voided', 'draft')
    AND o.cancelled_at IS NULL
    AND COALESCE(o.fraud_status, 'not_reviewed') != 'rejected'
    AND COALESCE(o.is_test_order, FALSE) = FALSE
    AND COALESCE(o.is_staff_order, FALSE) = FALSE
    AND NOT (o.total_price = 0 AND COALESCE(o.order_type, '') IN ('replacement', 'warranty_reship'))
    -- Optional win-back view only (document on the dashboard): AND COALESCE(o.order_source, '') != 'subscription_renewal'
),
customer_snapshot AS (
  SELECT
    s.snapshot_date,
    rc.customer_id,
    rc.registered_date,
    MAX(qo.order_date) AS last_order_date
  FROM snapshots AS s
  CROSS JOIN registered_customers AS rc
  LEFT JOIN qualifying_orders AS qo
    ON qo.customer_id = rc.customer_id
    AND qo.order_date <= s.snapshot_date
  GROUP BY s.snapshot_date, rc.customer_id, rc.registered_date
),
customer_days AS (
  SELECT
    snapshot_date,
    customer_id,
    last_order_date,
    DATE_DIFF(snapshot_date, last_order_date, DAY) AS days_since_last_purchase
  FROM customer_snapshot
  WHERE registered_date <= snapshot_date
    OR last_order_date IS NOT NULL
),
customer_band AS (
  SELECT
    snapshot_date,
    customer_id,
    last_order_date,
    days_since_last_purchase,
    CASE
      WHEN last_order_date IS NULL THEN 'No purchase'
      WHEN days_since_last_purchase <= 30 THEN '0-30 days'
      WHEN days_since_last_purchase <= 90 THEN '31-90 days'
      WHEN days_since_last_purchase <= 180 THEN '91-180 days'
      WHEN days_since_last_purchase <= 365 THEN '181-365 days'
      ELSE '366+ days'
    END AS last_purchase_recency_band,
    CASE
      WHEN last_order_date IS NULL THEN 6
      WHEN days_since_last_purchase <= 30 THEN 1
      WHEN days_since_last_purchase <= 90 THEN 2
      WHEN days_since_last_purchase <= 180 THEN 3
      WHEN days_since_last_purchase <= 365 THEN 4
      ELSE 5
    END AS band_sort_key
  FROM customer_days
),
band_counts AS (
  SELECT
    snapshot_date,
    band_sort_key,
    last_purchase_recency_band,
    COUNT(DISTINCT customer_id) AS customers
  FROM customer_band
  GROUP BY snapshot_date, band_sort_key, last_purchase_recency_band
)
-- Customer-level rows for audiences and joins: SELECT * FROM customer_band
SELECT
  snapshot_date,
  band_sort_key,
  last_purchase_recency_band,
  customers,
  SUM(customers) OVER (PARTITION BY snapshot_date) AS registered_customers_in_scope,
  ROUND(100 * SAFE_DIVIDE(customers, SUM(customers) OVER (PARTITION BY snapshot_date)), 2) AS pct_of_registered_customers,
  SUM(IF(band_sort_key <= 4, customers, 0)) OVER (PARTITION BY snapshot_date) AS active_customers_0_365_days
FROM band_counts
ORDER BY snapshot_date, band_sort_key;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Last Purchase Recency Band per registered customer and snapshot date, rolled up to the band distribution over all registered customers in scope.
-- Backend datasets: commerce_orders_dataset + crm_customer_profile_dataset + crm_customer_id_map_dataset (no web ExperienceEvent data).
-- Single analysis date: snapshot_start_date = snapshot_end_date. Trend rebuild: widen the range; each snapshot only uses orders placed on or before it.
-- Rerun the latest 3 snapshot dates daily so late POS and marketplace orders restate them; older snapshots stay frozen.
WITH params AS (
  SELECT
    CAST('2026-09-14' AS DATE) AS snapshot_start_date,
    CAST('2026-09-14' AS DATE) AS snapshot_end_date,
    'America/New_York' AS reporting_tz
),
snapshots AS (
  SELECT explode(sequence(p.snapshot_start_date, p.snapshot_end_date, INTERVAL 1 DAY)) AS snapshot_date
  FROM params AS p
),
customer_master AS (
  SELECT
    c.customer_id AS source_customer_id,
    COALESCE(m.resolved_customer_id, c.customer_id) AS customer_id,
    c.registered_at,
    c.email_sha256,
    c.email_verified,
    c.erased_at,
    c.is_test_account
  FROM crm_customer_profile_dataset AS c
  LEFT JOIN crm_customer_id_map_dataset AS m
    ON m.source_customer_id = c.customer_id
  WHERE c.account_type = 'registered'
),
registered_customers AS (
  SELECT
    cm.customer_id,
    to_date(from_utc_timestamp(MIN(cm.registered_at), p.reporting_tz)) AS registered_date
  FROM customer_master AS cm
  CROSS JOIN params AS p
  GROUP BY cm.customer_id, p.reporting_tz
  HAVING SUM(CASE WHEN cm.erased_at IS NOT NULL THEN 1 ELSE 0 END) = 0
    AND SUM(CASE WHEN COALESCE(cm.is_test_account, false) THEN 1 ELSE 0 END) = 0
),
verified_emails AS (
  SELECT
    email_sha256,
    MIN(customer_id) AS customer_id
  FROM customer_master
  WHERE email_verified = true
    AND email_sha256 IS NOT NULL
    AND erased_at IS NULL
  GROUP BY email_sha256
  HAVING COUNT(DISTINCT customer_id) = 1
),
qualifying_orders AS (
  SELECT
    COALESCE(m.resolved_customer_id, o.customer_id, ve.customer_id) AS customer_id,
    to_date(from_utc_timestamp(o.order_placed_at, p.reporting_tz)) AS order_date
  FROM commerce_orders_dataset AS o
  CROSS JOIN params AS p
  LEFT JOIN crm_customer_id_map_dataset AS m
    ON m.source_customer_id = o.customer_id
  LEFT JOIN verified_emails AS ve
    ON o.customer_id IS NULL
    AND ve.email_sha256 = o.customer_email_sha256
  WHERE to_date(from_utc_timestamp(o.order_placed_at, p.reporting_tz)) <= p.snapshot_end_date
    AND (o.order_status = 'completed' OR o.financial_status IN ('paid', 'partially_refunded', 'refunded'))
    AND COALESCE(o.order_status, '') NOT IN ('cancelled', 'voided', 'draft')
    AND o.cancelled_at IS NULL
    AND COALESCE(o.fraud_status, 'not_reviewed') <> 'rejected'
    AND COALESCE(o.is_test_order, false) = false
    AND COALESCE(o.is_staff_order, false) = false
    AND NOT (o.total_price = 0 AND COALESCE(o.order_type, '') IN ('replacement', 'warranty_reship'))
    -- Optional win-back view only (document on the dashboard): AND COALESCE(o.order_source, '') <> 'subscription_renewal'
),
customer_snapshot AS (
  SELECT
    s.snapshot_date,
    rc.customer_id,
    rc.registered_date,
    MAX(qo.order_date) AS last_order_date
  FROM snapshots AS s
  CROSS JOIN registered_customers AS rc
  LEFT JOIN qualifying_orders AS qo
    ON qo.customer_id = rc.customer_id
    AND qo.order_date <= s.snapshot_date
  GROUP BY s.snapshot_date, rc.customer_id, rc.registered_date
),
customer_days AS (
  SELECT
    snapshot_date,
    customer_id,
    last_order_date,
    datediff(snapshot_date, last_order_date) AS days_since_last_purchase
  FROM customer_snapshot
  WHERE registered_date <= snapshot_date
    OR last_order_date IS NOT NULL
),
customer_band AS (
  SELECT
    snapshot_date,
    customer_id,
    last_order_date,
    days_since_last_purchase,
    CASE
      WHEN last_order_date IS NULL THEN 'No purchase'
      WHEN days_since_last_purchase <= 30 THEN '0-30 days'
      WHEN days_since_last_purchase <= 90 THEN '31-90 days'
      WHEN days_since_last_purchase <= 180 THEN '91-180 days'
      WHEN days_since_last_purchase <= 365 THEN '181-365 days'
      ELSE '366+ days'
    END AS last_purchase_recency_band,
    CASE
      WHEN last_order_date IS NULL THEN 6
      WHEN days_since_last_purchase <= 30 THEN 1
      WHEN days_since_last_purchase <= 90 THEN 2
      WHEN days_since_last_purchase <= 180 THEN 3
      WHEN days_since_last_purchase <= 365 THEN 4
      ELSE 5
    END AS band_sort_key
  FROM customer_days
),
band_counts AS (
  SELECT
    snapshot_date,
    band_sort_key,
    last_purchase_recency_band,
    COUNT(DISTINCT customer_id) AS customers
  FROM customer_band
  GROUP BY snapshot_date, band_sort_key, last_purchase_recency_band
)
-- Customer-level rows for audiences and joins: SELECT * FROM customer_band
SELECT
  snapshot_date,
  band_sort_key,
  last_purchase_recency_band,
  customers,
  SUM(customers) OVER (PARTITION BY snapshot_date) AS registered_customers_in_scope,
  ROUND(100.0 * customers / NULLIF(SUM(customers) OVER (PARTITION BY snapshot_date), 0), 2) AS pct_of_registered_customers,
  SUM(CASE WHEN band_sort_key <= 4 THEN customers ELSE 0 END) OVER (PARTITION BY snapshot_date) AS active_customers_0_365_days
FROM band_counts
ORDER BY snapshot_date, band_sort_key;