Subscription Status

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Subscription Status (one value per subscription contract) from the subscription billing backend.
-- Sources: billing.subscriptions (contract header), billing.subscription_status_history (type-2 provider status records
-- keyed on the provider event timestamp), billing.subscription_migration_map, billing.subscription_customer_events
-- (server-side subscribe / unsubscribe / purchase log) and billing.stores (reporting time zone).
DECLARE report_start_date DATE DEFAULT DATE '2026-01-01';
DECLARE report_end_date DATE DEFAULT DATE '2026-08-31';

-- Step 1: effective-dated canonical status intervals (valid_from <= ts < valid_to).
CREATE TEMP TABLE subscription_status_intervals AS
WITH migration_target AS (
  SELECT subscription_id AS target_subscription_id, MIN(original_started_at) AS original_started_at
  FROM `your-project.billing.subscription_migration_map`
  GROUP BY subscription_id
),
contracts AS (
  SELECT
    COALESCE(ml.subscription_id, s.subscription_id) AS subscription_key,
    s.subscription_id,
    LOWER(s.provider) AS provider,
    s.customer_id,
    s.store_id,
    s.plan_id,
    COALESCE(mt.original_started_at, s.started_at) AS subscription_started_at,
    ml.migrated_at AS legacy_migrated_at
  FROM `your-project.billing.subscriptions` AS s
  LEFT JOIN `your-project.billing.subscription_migration_map` AS ml
    ON ml.legacy_subscription_id = s.subscription_id
  LEFT JOIN migration_target AS mt
    ON mt.target_subscription_id = s.subscription_id
  WHERE NOT COALESCE(s.is_test_mode, FALSE)
    AND NOT COALESCE(s.is_internal_account, FALSE)
    AND NOT COALESCE(s.is_gifted_zero_value, FALSE)
),
mapped AS (
  SELECT
    c.subscription_key,
    c.subscription_id,
    c.provider,
    c.customer_id,
    c.store_id,
    c.plan_id,
    c.subscription_started_at,
    h.valid_from,
    LEAST(
      COALESCE(h.valid_to, TIMESTAMP '9999-12-31 23:59:59+00'),
      COALESCE(c.legacy_migrated_at, TIMESTAMP '9999-12-31 23:59:59+00')
    ) AS valid_to,
    LOWER(h.provider_status) AS provider_status,
    LOWER(h.previous_provider_status) AS previous_provider_status,
    LOWER(h.cancellation_reason) AS cancellation_reason,
    COALESCE(h.retry_limit_reached, FALSE) AS retry_limit_reached,
    CASE
      WHEN c.provider = 'stripe' THEN
        CASE
          WHEN LOWER(h.provider_status) IN ('incomplete', 'incomplete_expired') THEN NULL
          WHEN LOWER(h.provider_status) = 'trialing' THEN 'trialing'
          WHEN LOWER(h.provider_status) = 'active' AND COALESCE(h.pause_collection_set, FALSE) THEN 'paused'
          WHEN LOWER(h.provider_status) = 'active' THEN 'active'
          WHEN LOWER(h.provider_status) = 'paused' THEN 'paused'
          WHEN LOWER(h.provider_status) IN ('past_due', 'unpaid') THEN 'past_due'
          WHEN LOWER(h.provider_status) = 'canceled'
            AND (COALESCE(h.ended_on_final_schedule_phase, FALSE) OR COALESCE(h.cancel_at_set_as_contract_term, FALSE)) THEN 'expired'
          WHEN LOWER(h.provider_status) = 'canceled' THEN 'cancelled'
          ELSE '(other)'
        END
      WHEN c.provider = 'shopify' THEN
        CASE
          WHEN LOWER(h.provider_status) = 'active'
            AND COALESCE(h.first_cycle_zero_priced, FALSE)
            AND NOT COALESCE(h.has_successful_billing_attempt, FALSE) THEN 'trialing'
          WHEN LOWER(h.provider_status) = 'active' THEN 'active'
          WHEN LOWER(h.provider_status) = 'paused' THEN 'paused'
          WHEN LOWER(h.provider_status) = 'failed' THEN 'past_due'
          WHEN LOWER(h.provider_status) = 'cancelled' THEN 'cancelled'
          WHEN LOWER(h.provider_status) = 'expired' THEN 'expired'
          ELSE '(other)'
        END
      WHEN c.provider = 'recharge' THEN
        CASE
          WHEN LOWER(h.provider_status) = 'cancelled' THEN 'cancelled'
          WHEN LOWER(h.provider_status) = 'expired' THEN 'expired'
          WHEN LOWER(h.provider_status) = 'active'
            AND COALESCE(h.is_free_trial_product, FALSE)
            AND NOT COALESCE(h.first_charge_processed, FALSE) THEN 'trialing'
          WHEN LOWER(h.provider_status) = 'active'
            AND LOWER(h.latest_charge_status) = 'error'
            AND (h.latest_charge_retry_date IS NOT NULL OR COALESCE(h.retry_limit_reached, FALSE)) THEN 'past_due'
          WHEN LOWER(h.provider_status) = 'active'
            AND (COALESCE(h.charges_skipped_through_next_billing, FALSE) OR COALESCE(h.pause_action_applied, FALSE)) THEN 'paused'
          WHEN LOWER(h.provider_status) = 'active' THEN 'active'
          ELSE '(other)'
        END
      ELSE '(other)'
    END AS subscription_status
  FROM contracts AS c
  JOIN `your-project.billing.subscription_status_history` AS h
    ON h.subscription_id = c.subscription_id
  WHERE c.legacy_migrated_at IS NULL
     OR h.valid_from < c.legacy_migrated_at
)
SELECT
  subscription_key,
  subscription_id,
  provider,
  customer_id,
  store_id,
  plan_id,
  subscription_started_at,
  valid_from,
  valid_to,
  subscription_status,
  CASE
    WHEN subscription_status <> 'cancelled' THEN NULL
    WHEN (provider = 'stripe' AND cancellation_reason IN ('payment_failed', 'payment_disputed'))
      OR (provider = 'shopify' AND previous_provider_status = 'failed')
      OR (provider = 'recharge' AND retry_limit_reached) THEN 'involuntary'
    ELSE 'voluntary'
  END AS cancellation_type
FROM mapped
WHERE subscription_status IS NOT NULL
  AND valid_from < valid_to;

-- Step 2: status as of event time (journey analysis on subscribe, unsubscribe, purchase).
SELECT
  e.event_id,
  e.event_name,
  e.event_ts,
  i.subscription_key AS subscription_id,
  i.customer_id,
  i.provider AS billing_platform,
  i.plan_id,
  i.subscription_status AS subscription_status_at_event,
  i.cancellation_type
FROM `your-project.billing.subscription_customer_events` AS e
LEFT JOIN `your-project.billing.subscription_migration_map` AS ml
  ON ml.legacy_subscription_id = e.subscription_id
JOIN subscription_status_intervals AS i
  ON i.subscription_key = COALESCE(ml.subscription_id, e.subscription_id)
 AND e.event_ts >= i.valid_from
 AND e.event_ts < i.valid_to
WHERE e.event_name IN ('subscribe', 'unsubscribe', 'purchase')
  AND DATE(e.event_ts) BETWEEN report_start_date AND report_end_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY e.event_id ORDER BY i.valid_from DESC) = 1;

-- Step 3: period-end snapshot at 23:59:59 on the last day of each month in the store reporting time zone.
CREATE TEMP TABLE subscription_status_month_end AS
WITH periods AS (
  SELECT LAST_DAY(month_start, MONTH) AS period_end_date
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE_TRUNC(report_start_date, MONTH), DATE_TRUNC(report_end_date, MONTH), INTERVAL 1 MONTH)) AS month_start
)
SELECT
  p.period_end_date,
  i.store_id,
  i.provider,
  i.subscription_key,
  i.customer_id,
  i.plan_id,
  i.subscription_status,
  i.cancellation_type
FROM subscription_status_intervals AS i
JOIN `your-project.billing.stores` AS st
  ON st.store_id = i.store_id
JOIN periods AS p
  ON TIMESTAMP(DATETIME(p.period_end_date, TIME '23:59:59'), st.reporting_time_zone) >= i.valid_from
 AND TIMESTAMP(DATETIME(p.period_end_date, TIME '23:59:59'), st.reporting_time_zone) < i.valid_to
QUALIFY ROW_NUMBER() OVER (PARTITION BY p.period_end_date, i.subscription_key ORDER BY i.valid_from DESC) = 1;

-- Step 4: month-end distribution; the share denominator is every in-scope contract of the store at the snapshot.
SELECT
  period_end_date,
  store_id,
  provider AS billing_platform,
  subscription_status,
  cancellation_type,
  COUNT(DISTINCT subscription_key) AS subscription_contracts,
  SAFE_DIVIDE(
    COUNT(DISTINCT subscription_key),
    SUM(COUNT(DISTINCT subscription_key)) OVER (PARTITION BY period_end_date, store_id)
  ) AS share_of_store_contracts
FROM subscription_status_month_end
GROUP BY period_end_date, store_id, provider, subscription_status, cancellation_type
ORDER BY period_end_date, store_id, billing_platform, subscription_status;

-- Step 5: customer-level rollup at month end (precedence active > past_due > trialing > paused > cancelled > expired).
SELECT
  period_end_date,
  store_id,
  customer_id,
  CASE MIN(CASE subscription_status
             WHEN 'active' THEN 1 WHEN 'past_due' THEN 2 WHEN 'trialing' THEN 3
             WHEN 'paused' THEN 4 WHEN 'cancelled' THEN 5 WHEN 'expired' THEN 6 ELSE 7 END)
    WHEN 1 THEN 'active' WHEN 2 THEN 'past_due' WHEN 3 THEN 'trialing'
    WHEN 4 THEN 'paused' WHEN 5 THEN 'cancelled' WHEN 6 THEN 'expired' ELSE '(other)'
  END AS customer_subscription_status,
  COUNT(DISTINCT subscription_key) AS subscription_contracts
FROM subscription_status_month_end
GROUP BY period_end_date, store_id, customer_id;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Subscription Status (one value per subscription contract) from backend datasets ingested from the billing platforms:
-- subscription_contracts_dataset, subscription_status_history_dataset (type-2 records keyed on the provider event timestamp),
-- subscription_migration_map_dataset, subscription_customer_events_dataset (server-side subscribe / unsubscribe /
-- purchase log) and store_settings_dataset (reporting time zone). Reporting window: 2026-01-01 to 2026-08-31.

-- Step 1: effective-dated canonical status intervals (valid_from <= ts < valid_to).
CREATE TEMP TABLE subscription_status_intervals AS
SELECT
  m.subscription_key,
  m.subscription_id,
  m.provider,
  m.customer_id,
  m.store_id,
  m.plan_id,
  m.subscription_started_at,
  m.valid_from,
  m.valid_to,
  m.subscription_status,
  CASE
    WHEN m.subscription_status <> 'cancelled' THEN NULL
    WHEN (m.provider = 'stripe' AND m.cancellation_reason IN ('payment_failed', 'payment_disputed'))
      OR (m.provider = 'shopify' AND m.previous_provider_status = 'failed')
      OR (m.provider = 'recharge' AND m.retry_limit_reached) THEN 'involuntary'
    ELSE 'voluntary'
  END AS cancellation_type
FROM (
  SELECT
    c.subscription_key,
    c.subscription_id,
    c.provider,
    c.customer_id,
    c.store_id,
    c.plan_id,
    c.subscription_started_at,
    h.valid_from,
    LEAST(
      COALESCE(h.valid_to, TIMESTAMP '9999-12-31 23:59:59'),
      COALESCE(c.legacy_migrated_at, TIMESTAMP '9999-12-31 23:59:59')
    ) AS valid_to,
    LOWER(h.previous_provider_status) AS previous_provider_status,
    LOWER(h.cancellation_reason) AS cancellation_reason,
    COALESCE(h.retry_limit_reached, false) AS retry_limit_reached,
    CASE
      WHEN c.provider = 'stripe' THEN
        CASE
          WHEN LOWER(h.provider_status) IN ('incomplete', 'incomplete_expired') THEN NULL
          WHEN LOWER(h.provider_status) = 'trialing' THEN 'trialing'
          WHEN LOWER(h.provider_status) = 'active' AND COALESCE(h.pause_collection_set, false) THEN 'paused'
          WHEN LOWER(h.provider_status) = 'active' THEN 'active'
          WHEN LOWER(h.provider_status) = 'paused' THEN 'paused'
          WHEN LOWER(h.provider_status) IN ('past_due', 'unpaid') THEN 'past_due'
          WHEN LOWER(h.provider_status) = 'canceled'
            AND (COALESCE(h.ended_on_final_schedule_phase, false) OR COALESCE(h.cancel_at_set_as_contract_term, false)) THEN 'expired'
          WHEN LOWER(h.provider_status) = 'canceled' THEN 'cancelled'
          ELSE '(other)'
        END
      WHEN c.provider = 'shopify' THEN
        CASE
          WHEN LOWER(h.provider_status) = 'active'
            AND COALESCE(h.first_cycle_zero_priced, false)
            AND NOT COALESCE(h.has_successful_billing_attempt, false) THEN 'trialing'
          WHEN LOWER(h.provider_status) = 'active' THEN 'active'
          WHEN LOWER(h.provider_status) = 'paused' THEN 'paused'
          WHEN LOWER(h.provider_status) = 'failed' THEN 'past_due'
          WHEN LOWER(h.provider_status) = 'cancelled' THEN 'cancelled'
          WHEN LOWER(h.provider_status) = 'expired' THEN 'expired'
          ELSE '(other)'
        END
      WHEN c.provider = 'recharge' THEN
        CASE
          WHEN LOWER(h.provider_status) = 'cancelled' THEN 'cancelled'
          WHEN LOWER(h.provider_status) = 'expired' THEN 'expired'
          WHEN LOWER(h.provider_status) = 'active'
            AND COALESCE(h.is_free_trial_product, false)
            AND NOT COALESCE(h.first_charge_processed, false) THEN 'trialing'
          WHEN LOWER(h.provider_status) = 'active'
            AND LOWER(h.latest_charge_status) = 'error'
            AND (h.latest_charge_retry_date IS NOT NULL OR COALESCE(h.retry_limit_reached, false)) THEN 'past_due'
          WHEN LOWER(h.provider_status) = 'active'
            AND (COALESCE(h.charges_skipped_through_next_billing, false) OR COALESCE(h.pause_action_applied, false)) THEN 'paused'
          WHEN LOWER(h.provider_status) = 'active' THEN 'active'
          ELSE '(other)'
        END
      ELSE '(other)'
    END AS subscription_status
  FROM (
    SELECT
      COALESCE(ml.subscription_id, s.subscription_id) AS subscription_key,
      s.subscription_id,
      LOWER(s.provider) AS provider,
      s.customer_id,
      s.store_id,
      s.plan_id,
      COALESCE(mt.original_started_at, s.started_at) AS subscription_started_at,
      ml.migrated_at AS legacy_migrated_at
    FROM subscription_contracts_dataset AS s
    LEFT JOIN subscription_migration_map_dataset AS ml
      ON ml.legacy_subscription_id = s.subscription_id
    LEFT JOIN (
      SELECT subscription_id AS target_subscription_id, MIN(original_started_at) AS original_started_at
      FROM subscription_migration_map_dataset
      GROUP BY subscription_id
    ) AS mt
      ON mt.target_subscription_id = s.subscription_id
    WHERE NOT COALESCE(s.is_test_mode, false)
      AND NOT COALESCE(s.is_internal_account, false)
      AND NOT COALESCE(s.is_gifted_zero_value, false)
  ) AS c
  JOIN subscription_status_history_dataset AS h
    ON h.subscription_id = c.subscription_id
  WHERE c.legacy_migrated_at IS NULL
     OR h.valid_from < c.legacy_migrated_at
) AS m
WHERE m.subscription_status IS NOT NULL
  AND m.valid_from < m.valid_to;

-- Step 2: status as of event time (journey analysis on subscribe, unsubscribe, purchase).
SELECT
  event_id,
  event_name,
  event_ts,
  subscription_id,
  customer_id,
  billing_platform,
  plan_id,
  subscription_status_at_event,
  cancellation_type
FROM (
  SELECT
    e.event_id,
    e.event_name,
    e.event_ts,
    i.subscription_key AS subscription_id,
    i.customer_id,
    i.provider AS billing_platform,
    i.plan_id,
    i.subscription_status AS subscription_status_at_event,
    i.cancellation_type,
    ROW_NUMBER() OVER (PARTITION BY e.event_id ORDER BY i.valid_from DESC) AS rn
  FROM subscription_customer_events_dataset AS e
  LEFT JOIN subscription_migration_map_dataset AS ml
    ON ml.legacy_subscription_id = e.subscription_id
  JOIN subscription_status_intervals AS i
    ON i.subscription_key = COALESCE(ml.subscription_id, e.subscription_id)
   AND e.event_ts >= i.valid_from
   AND e.event_ts < i.valid_to
  WHERE e.event_name IN ('subscribe', 'unsubscribe', 'purchase')
    AND to_date(e.event_ts) BETWEEN to_date('2026-01-01') AND to_date('2026-08-31')
) AS x
WHERE rn = 1;

-- Step 3: period-end snapshot at 23:59:59 on the last day of each month in the store reporting time zone.
CREATE TEMP TABLE subscription_status_month_end AS
SELECT
  period_end_date,
  store_id,
  provider,
  subscription_key,
  customer_id,
  plan_id,
  subscription_status,
  cancellation_type
FROM (
  SELECT
    p.period_end_date,
    i.store_id,
    i.provider,
    i.subscription_key,
    i.customer_id,
    i.plan_id,
    i.subscription_status,
    i.cancellation_type,
    ROW_NUMBER() OVER (PARTITION BY p.period_end_date, i.subscription_key ORDER BY i.valid_from DESC) AS rn
  FROM subscription_status_intervals AS i
  JOIN store_settings_dataset AS st
    ON st.store_id = i.store_id
  JOIN (
    SELECT last_day(month_start) AS period_end_date
    FROM (
      SELECT explode(sequence(to_date('2026-01-01'), to_date('2026-08-01'), interval 1 month)) AS month_start
    ) AS months
  ) AS p
    ON to_utc_timestamp(CAST(concat(CAST(p.period_end_date AS STRING), ' 23:59:59') AS TIMESTAMP), st.reporting_time_zone) >= i.valid_from
   AND to_utc_timestamp(CAST(concat(CAST(p.period_end_date AS STRING), ' 23:59:59') AS TIMESTAMP), st.reporting_time_zone) < i.valid_to
) AS s
WHERE rn = 1;

-- Step 4: month-end distribution; the share denominator is every in-scope contract of the store at the snapshot.
SELECT
  period_end_date,
  store_id,
  provider AS billing_platform,
  subscription_status,
  cancellation_type,
  COUNT(DISTINCT subscription_key) AS subscription_contracts,
  COUNT(DISTINCT subscription_key)
    / NULLIF(SUM(COUNT(DISTINCT subscription_key)) OVER (PARTITION BY period_end_date, store_id), 0) AS share_of_store_contracts
FROM subscription_status_month_end
GROUP BY period_end_date, store_id, provider, subscription_status, cancellation_type
ORDER BY period_end_date, store_id, billing_platform, subscription_status;

-- Step 5: customer-level rollup at month end (precedence active > past_due > trialing > paused > cancelled > expired).
SELECT
  period_end_date,
  store_id,
  customer_id,
  CASE MIN(CASE subscription_status
             WHEN 'active' THEN 1 WHEN 'past_due' THEN 2 WHEN 'trialing' THEN 3
             WHEN 'paused' THEN 4 WHEN 'cancelled' THEN 5 WHEN 'expired' THEN 6 ELSE 7 END)
    WHEN 1 THEN 'active' WHEN 2 THEN 'past_due' WHEN 3 THEN 'trialing'
    WHEN 4 THEN 'paused' WHEN 5 THEN 'cancelled' WHEN 6 THEN 'expired' ELSE '(other)'
  END AS customer_subscription_status,
  COUNT(DISTINCT subscription_key) AS subscription_contracts
FROM subscription_status_month_end
GROUP BY period_end_date, store_id, customer_id;