Average Subscription Duration

-- ==========================================
-- 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);

WITH subs AS (
  -- Paid subscription records (trials that never reached a paid charge, test, staff and fraud-cancelled excluded); involuntary cancellations end on the last dunning date
  SELECT
    customer_id,
    first_paid_date,
    IF(cancellation_type = 'involuntary', last_dunning_date, cancellation_effective_date) AS end_dt
  FROM `your-project.crm.subscriptions`
  WHERE first_paid_date IS NOT NULL
    AND first_paid_date <= end_date
    AND NOT is_test
    AND NOT is_staff
    AND COALESCE(cancellation_reason, '') != 'fraud'
),
flagged AS (
  -- A record opens a new subscription unless it reactivates 0 to 30 days after the customer's previous cancellation (overlapping records stay separate)
  SELECT
    *,
    IF(DATE_DIFF(first_paid_date, LAG(end_dt) OVER (PARTITION BY customer_id ORDER BY first_paid_date), DAY) BETWEEN 0 AND 30, 0, 1) AS is_new
  FROM subs
),
chained AS (
  -- Number the stitched subscriptions per customer
  SELECT
    *,
    SUM(is_new) OVER (PARTITION BY customer_id ORDER BY first_paid_date ROWS UNBOUNDED PRECEDING) AS sub_seq
  FROM flagged
),
stitched AS (
  -- One row per subscription: first paid charge to final end (NULL while still active); pauses do not end it
  SELECT
    MIN(first_paid_date) AS first_paid_date,
    IF(COUNTIF(end_dt IS NULL) > 0, NULL, MAX(end_dt)) AS end_dt
  FROM chained
  GROUP BY customer_id, sub_seq
)
-- Mean days from first paid charge to cancellation, or to period end if still active, over subscriptions active at any point in the period
SELECT
  ROUND(AVG(DATE_DIFF(LEAST(COALESCE(end_dt, end_date), end_date), first_paid_date, DAY)), 1) AS average_subscription_duration_days
FROM stitched
WHERE COALESCE(end_dt, end_date) >= start_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Reporting period: last 90 days up to yesterday
  SELECT
    DATE_SUB(CURRENT_DATE, 90) AS start_date,
    DATE_SUB(CURRENT_DATE, 1) AS end_date
),
subs AS (
  -- Paid subscription records (trials that never reached a paid charge, test, staff and fraud-cancelled excluded); involuntary cancellations end on the last dunning date
  SELECT
    s.customer_id,
    s.first_paid_date,
    CASE WHEN s.cancellation_type = 'involuntary' THEN s.last_dunning_date ELSE s.cancellation_effective_date END AS end_dt
  FROM aep_backend_dataset_crm_subscriptions s
  CROSS JOIN params p
  WHERE s.first_paid_date IS NOT NULL
    AND s.first_paid_date <= p.end_date
    AND NOT s.is_test
    AND NOT s.is_staff
    AND COALESCE(s.cancellation_reason, '') != 'fraud'
),
flagged AS (
  -- A record opens a new subscription unless it reactivates 0 to 30 days after the customer's previous cancellation (overlapping records stay separate)
  SELECT
    *,
    CASE WHEN DATEDIFF(first_paid_date, LAG(end_dt) OVER (PARTITION BY customer_id ORDER BY first_paid_date)) BETWEEN 0 AND 30 THEN 0 ELSE 1 END AS is_new
  FROM subs
),
chained AS (
  -- Number the stitched subscriptions per customer
  SELECT
    *,
    SUM(is_new) OVER (PARTITION BY customer_id ORDER BY first_paid_date ROWS UNBOUNDED PRECEDING) AS sub_seq
  FROM flagged
),
stitched AS (
  -- One row per subscription: first paid charge to final end (NULL while still active); pauses do not end it
  SELECT
    MIN(first_paid_date) AS first_paid_date,
    CASE WHEN COUNT(*) > COUNT(end_dt) THEN NULL ELSE MAX(end_dt) END AS end_dt
  FROM chained
  GROUP BY customer_id, sub_seq
)
-- Mean days from first paid charge to cancellation, or to period end if still active, over subscriptions active at any point in the period
SELECT
  ROUND(AVG(DATEDIFF(LEAST(COALESCE(st.end_dt, p.end_date), p.end_date), st.first_paid_date)), 1) AS average_subscription_duration_days
FROM stitched st
CROSS JOIN params p
WHERE COALESCE(st.end_dt, p.end_date) >= p.start_date;