Subscription Cancellation Reason

{
  "event": [
    {
      "eventInfo": {
        "eventName": "submit_form"
      },
      "attributes": {
        "form_type": "subscription_cancellation",
        "subscription_id": "SUB-1",
        "cancellation_reason": "too_expensive"
      }
    }
  ]
}

{
  "event": "submit_form",
  "form_type": "subscription_cancellation",
  "subscription_id": "SUB-1",
  "cancellation_reason": "too_expensive"
}

{
  "event": "form_submit",
  "eventInfo": {
    "form_type": "subscription_cancellation",
    "subscription_id": "SUB-1",
    "cancellation_reason": "too_expensive"
  }
}

{
  "eventType": "web.formFilledOut",
  "_tenant": {
    "form_type": "subscription_cancellation",
    "subscription_id": "SUB-1",
    "cancellation_reason": "too_expensive"
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Relative reporting window: last 90 complete days
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Subscription-cancellation submit_form events with subscription_id and cancellation_reason pivoted from event_params
WITH cancel_events AS (
  SELECT
    event_timestamp,
    (SELECT COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) FROM UNNEST(event_params) ep WHERE ep.key = 'subscription_id') AS subscription_id,
    (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'cancellation_reason') AS cancellation_reason
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'submit_form'
    AND (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'form_type') = 'subscription_cancellation'
),
-- One value per cancellation: keep the last submit per subscription_id
cancellations AS (
  SELECT
    subscription_id,
    cancellation_reason,
    ROW_NUMBER() OVER (PARTITION BY subscription_id ORDER BY event_timestamp DESC) AS rn
  FROM cancel_events
  WHERE subscription_id IS NOT NULL
)
-- Reason per cancellation; a missing reason is reported as (not set) to flag tagging gaps
SELECT
  subscription_id,
  COALESCE(cancellation_reason, '(not set)') AS cancellation_reason
FROM cancellations
WHERE rn = 1;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window: last 90 complete days
WITH params AS (
  SELECT
    CAST(DATE_SUB(CURRENT_DATE, 90) AS DATE) AS start_date,
    CAST(DATE_SUB(CURRENT_DATE, 1) AS DATE) AS end_date
),
-- Subscription-cancellation form submits with subscription_id and cancellation_reason
cancel_events AS (
  SELECT
    w.timestamp AS event_ts,
    w._tenant.subscription_id AS subscription_id,
    w._tenant.cancellation_reason AS cancellation_reason
  FROM aep_web_events_dataset w
  CROSS JOIN params p
  WHERE w.eventType = 'web.formFilledOut'
    AND w._tenant.form_type = 'subscription_cancellation'
    AND CAST(w.timestamp AS DATE) BETWEEN p.start_date AND p.end_date
),
-- One value per cancellation: keep the last submit per subscription_id
cancellations AS (
  SELECT
    subscription_id,
    cancellation_reason,
    ROW_NUMBER() OVER (PARTITION BY subscription_id ORDER BY event_ts DESC) AS rn
  FROM cancel_events
  WHERE subscription_id IS NOT NULL
)
-- Reason per cancellation; a missing reason is reported as (not set) to flag tagging gaps
SELECT
  subscription_id,
  COALESCE(cancellation_reason, '(not set)') AS cancellation_reason
FROM cancellations
WHERE rn = 1;