Combined Auto Order Cohort Analysis

Overview

One query in this library is long enough to warrant its own page. It classifies every auto order into a monthly cohort, works out the primary rebill interval from the frequency on the first item, tracks rebill revenue and activity across the following 12 months, and ends with six alternative reports built on that shared foundation.

Pick a report by changing the final select. The other five are commented out directly beneath it.

Final select	What it returns
per_item_cumlative_analysis	Cumulative revenue by month, by cohort and primary item
per_item_monthly_analysis	Revenue in each individual month, by cohort and primary item
per_item_churn_analysis	Auto orders still active in each month, by cohort and primary item
primary_rebill_interval_cumlative_analysis	Cumulative revenue by month, by cohort and rebill interval
primary_rebill_interval_monthly_analysis	Revenue in each individual month, by cohort and rebill interval
primary_rebill_interval_churn_analysis	Auto orders still active in each month, by cohort and rebill interval

The last two commented lines go further and pull a single number out of the churn report, showing how to answer a question such as "what share of monthly subscriptions are still active at the end of month three".

Month 0 is the cohort's original orders, so month_0 and cumulative_month_0 hold original order revenue and month_1 onward hold rebill revenue. A month that has not happened yet returns null rather than zero, which keeps averages over partial cohorts honest.

This query scans the whole uc_auto_orders table and builds a 12-element array per row. Run the BigQuery dry-run estimate before running it against a large account.

The Query

with classified_auto_orders AS (
  SELECT auto_order_code,
  original_order_id,
  original_order.summary.subtotal.value as original_revenue,
  enabled,
  canceled_dts,
  disabled_dts,
  DATE_TRUNC(cast(original_order.creation_dts as date), month) as cohort,
  items[SAFE_OFFSET(0)].original_item_id as primary_item_id,
  case 
  -- when enabled we can use the delta between the next shipment date and last date
  when items[SAFE_OFFSET(0)].frequency = 'Monthly' then '1 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every 2 Months' then '2 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every 3 Months' then '3 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every 4 Months' then '4 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every 6 Months' then '6 month'
  -- handle the arbitrary frequency
  when items[SAFE_OFFSET(0)].frequency = 'Every...' and items[SAFE_OFFSET(0)].arbitrary_schedule_days between 0 and 31 then '1 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every...' and items[SAFE_OFFSET(0)].arbitrary_schedule_days between 32 and 62 then '2 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every...' and items[SAFE_OFFSET(0)].arbitrary_schedule_days between 63 and 93 then '3 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every...' and items[SAFE_OFFSET(0)].arbitrary_schedule_days between 94 and 124 then '4 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every...' and items[SAFE_OFFSET(0)].arbitrary_schedule_days between 125 and 155 then '5 month'
  when items[SAFE_OFFSET(0)].frequency = 'Every...' and items[SAFE_OFFSET(0)].arbitrary_schedule_days between 155 and 186 then '6 month'
--  when items[SAFE_OFFSET(0)].frequency = 'Every 1 Month' then '1 month'
  when items[SAFE_OFFSET(0)].next_shipment_dts is not null and DATETIME_DIFF(items[SAFE_OFFSET(0)].next_shipment_dts, items[SAFE_OFFSET(0)].last_order_dts, day) between 0 and 31 then '1 month'
  when items[SAFE_OFFSET(0)].next_shipment_dts is not null and DATETIME_DIFF(items[SAFE_OFFSET(0)].next_shipment_dts, items[SAFE_OFFSET(0)].last_order_dts, day) between 32 and 62 then '2 month'
  when items[SAFE_OFFSET(0)].next_shipment_dts is not null and DATETIME_DIFF(items[SAFE_OFFSET(0)].next_shipment_dts, items[SAFE_OFFSET(0)].last_order_dts, day) between 63 and 93 then '3 month'
  when items[SAFE_OFFSET(0)].next_shipment_dts is not null and DATETIME_DIFF(items[SAFE_OFFSET(0)].next_shipment_dts, items[SAFE_OFFSET(0)].last_order_dts, day) between 94 and 124 then '4 month'
  when items[SAFE_OFFSET(0)].next_shipment_dts is not null and DATETIME_DIFF(items[SAFE_OFFSET(0)].next_shipment_dts, items[SAFE_OFFSET(0)].last_order_dts, day) between 125 and 155 then '5 month'
  when items[SAFE_OFFSET(0)].next_shipment_dts is not null and DATETIME_DIFF(items[SAFE_OFFSET(0)].next_shipment_dts, items[SAFE_OFFSET(0)].last_order_dts, day) between 155 and 186 then '6 month'
  end as primary_rebill_interval,
  (
    select coalesce(sum(summary.subtotal.value - coalesce(summary.subtotal_refunded.value, 0)), 0) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
  ) as rebill_revenue,
  (
    select count(*) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null and coalesce(summary.subtotal_refunded.value, 0) = 0
  ) as rebill_count,
  rebill_orders
  FROM `my-data-warehouse.ultracart_dw.uc_auto_orders`
),
classified_auto_orders_with_months as (
  select *, GENERATE_DATE_ARRAY(cohort, DATE_ADD(cohort, interval 11 month), INTERVAL 1 month) as future_activity_months
  from classified_auto_orders  
),
classified_auto_orders_with_month_stats as (
  select * except (rebill_orders, future_activity_months),
  ARRAY(
    select as struct month,
    (
      select count(*) from UNNEST(rebill_orders) r where DATE_TRUNC(r.creation_dts, month) = month and r.payment.payment_dts is not null and coalesce(summary.subtotal_refunded.value, 0) = 0
    ) as rebill_count,
    (
      select coalesce(sum(r.summary.subtotal.value), 0) from UNNEST(rebill_orders) r where DATE_TRUNC(r.creation_dts, month) = month and r.payment.payment_dts is not null and coalesce(summary.subtotal_refunded.value, 0) = 0
    ) as rebill_revenue,
    case when canceled_dts is null and disabled_dts is null then 1
      when canceled_dts is not null and DATE_TRUNC(canceled_dts, month) <= month then 0
      when canceled_dts is not null and DATE_TRUNC(canceled_dts, month) > month then 1
      when disabled_dts is not null and DATE_TRUNC(disabled_dts, month) <= month then 0
      when disabled_dts is not null and DATE_TRUNC(disabled_dts, month) > month then 1
      else 0
    end as active    
    FROM UNNEST(future_activity_months) month 
    where month <= CURRENT_DATE()
  ) as rebill_activity
  from classified_auto_orders_with_months
),
primary_item_cohort_rebill as (
  select primary_item_id, cohort, ra.month as rebill_month, sum(ra.rebill_count) as rebill_count, sum(ra.rebill_revenue) as rebill_revenue, sum(ra.active) as rebill_active_count 
  from classified_auto_orders_with_month_stats
  CROSS JOIN UNNEST(rebill_activity) ra
  group by primary_item_id, cohort, ra.month
  order by primary_item_id, cohort, ra.month
),
primary_rebill_interval_cohort_rebill as (
  select primary_rebill_interval, cohort, ra.month as rebill_month, sum(ra.rebill_count) as rebill_count, sum(ra.rebill_revenue) as rebill_revenue, sum(ra.active) as rebill_active_count 
  from classified_auto_orders_with_month_stats
  CROSS JOIN UNNEST(rebill_activity) ra
  group by primary_rebill_interval, cohort, ra.month
  order by primary_rebill_interval, cohort, ra.month
),
primary_item_cohort_rebill_agg as (
  SELECT primary_item_id, cohort, ARRAY_AGG(STRUCT(rebill_month as month, rebill_count, rebill_revenue, rebill_active_count)) AS rebill_activity
  FROM primary_item_cohort_rebill
  GROUP BY primary_item_id, cohort
),
primary_rebill_interval_cohort_rebill_agg as (
  SELECT primary_rebill_interval, cohort, ARRAY_AGG(STRUCT(rebill_month as month, rebill_count, rebill_revenue, rebill_active_count)) AS rebill_activity
  FROM primary_rebill_interval_cohort_rebill
  GROUP BY primary_rebill_interval, cohort
),
per_item_classified_auto_orders_rolled_up as (
  select primary_item_id, cohort, 
  ROUND(avg(rebill_revenue), 2) as avg_rebill_revenue, 
  ROUND(avg(rebill_count), 1) as avg_rebill_count,
  sum(original_revenue) as total_original_revenue,
  sum(rebill_revenue) as total_rebill_revenue,
  count(*) as total_customers,
  sum(
    case when enabled then 1 else 0 end
  ) as active_customers
  from classified_auto_orders_with_months 
  where primary_rebill_interval is not null
  group by primary_item_id, cohort
  order by primary_item_id, cohort
),
primary_rebill_interval_classified_auto_orders_rolled_up as (
  select primary_rebill_interval, cohort,
  ROUND(avg(rebill_revenue), 2) as avg_rebill_revenue, 
  ROUND(avg(rebill_count), 1) as avg_rebill_count,
  sum(original_revenue) as total_original_revenue,
  sum(rebill_revenue) as total_rebill_revenue,
  count(*) as total_customers,
  sum(
    case when enabled then 1 else 0 end
  ) as active_customers
  from classified_auto_orders_with_months 
  where primary_rebill_interval is not null
  group by primary_rebill_interval, cohort
  order by primary_rebill_interval, cohort
),
per_item_output as (
  select t1.*,
  -- revenue per month
  t1.total_original_revenue as month_0,
  t2.rebill_activity[safe_offset(0)].rebill_revenue as month_1,
  t2.rebill_activity[safe_offset(1)].rebill_revenue as month_2,
  t2.rebill_activity[safe_offset(2)].rebill_revenue as month_3,
  t2.rebill_activity[safe_offset(3)].rebill_revenue as month_4,
  t2.rebill_activity[safe_offset(4)].rebill_revenue as month_5,
  t2.rebill_activity[safe_offset(5)].rebill_revenue as month_6,
  t2.rebill_activity[safe_offset(6)].rebill_revenue as month_7,
  t2.rebill_activity[safe_offset(7)].rebill_revenue as month_8,
  t2.rebill_activity[safe_offset(8)].rebill_revenue as month_9,
  t2.rebill_activity[safe_offset(9)].rebill_revenue as month_10,
  t2.rebill_activity[safe_offset(10)].rebill_revenue as month_11,
  t2.rebill_activity[safe_offset(11)].rebill_revenue as month_12,
  -- cumulative revenue per month
  t1.total_original_revenue as cumulative_month_0,
  case when t2.rebill_activity[SAFE_OFFSET(0)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 0
    ) 
  else null end as cumulative_month_1,
  case when t2.rebill_activity[SAFE_OFFSET(1)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 1
    ) 
  else null end as cumulative_month_2,
  case when t2.rebill_activity[SAFE_OFFSET(2)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 2
    ) 
  else null end as cumulative_month_3,
  case when t2.rebill_activity[SAFE_OFFSET(3)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 3
    ) 
  else null end as cumulative_month_4,
  case when t2.rebill_activity[SAFE_OFFSET(4)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 4
    ) 
  else null end as cumulative_month_5,
  case when t2.rebill_activity[SAFE_OFFSET(5)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 5
    ) 
  else null end as cumulative_month_6,
  case when t2.rebill_activity[SAFE_OFFSET(6)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 6
    ) 
  else null end as cumulative_month_7,
  case when t2.rebill_activity[SAFE_OFFSET(7)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 7
    ) 
  else null end as cumulative_month_8,
  case when t2.rebill_activity[SAFE_OFFSET(8)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 8
    ) 
  else null end as cumulative_month_9,
  case when t2.rebill_activity[SAFE_OFFSET(9)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 9
    ) 
  else null end as cumulative_month_10,
  case when t2.rebill_activity[SAFE_OFFSET(10)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 10
    ) 
  else null end as cumulative_month_11,
  case when t2.rebill_activity[SAFE_OFFSET(11)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 11
    ) 
  else null end as cumulative_month_12,
  -- active percentage
  1 as active_month_0,
  case when t2.rebill_activity[SAFE_OFFSET(0)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(0)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_1,
  case when t2.rebill_activity[SAFE_OFFSET(1)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(1)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_2,
  case when t2.rebill_activity[SAFE_OFFSET(2)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(2)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_3,
  case when t2.rebill_activity[SAFE_OFFSET(3)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(3)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_4,
  case when t2.rebill_activity[SAFE_OFFSET(4)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(4)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_5,
  case when t2.rebill_activity[SAFE_OFFSET(5)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(5)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_6,
  case when t2.rebill_activity[SAFE_OFFSET(6)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(6)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_7,
  case when t2.rebill_activity[SAFE_OFFSET(7)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(7)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_8,
  case when t2.rebill_activity[SAFE_OFFSET(8)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(8)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_9,
  case when t2.rebill_activity[SAFE_OFFSET(9)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(9)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_10,
  case when t2.rebill_activity[SAFE_OFFSET(10)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(10)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_11,
  case when t2.rebill_activity[SAFE_OFFSET(11)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(11)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_12,
  from per_item_classified_auto_orders_rolled_up t1
  left join primary_item_cohort_rebill_agg t2 on t2.primary_item_id = t1.primary_item_id and t2.cohort = t1.cohort
  order by primary_item_id, cohort
),

primary_rebill_interval_output as (
  select t1.*,
  -- revenue per month
  t1.total_original_revenue as month_0,
  t2.rebill_activity[safe_offset(0)].rebill_revenue as month_1,
  t2.rebill_activity[safe_offset(1)].rebill_revenue as month_2,
  t2.rebill_activity[safe_offset(2)].rebill_revenue as month_3,
  t2.rebill_activity[safe_offset(3)].rebill_revenue as month_4,
  t2.rebill_activity[safe_offset(4)].rebill_revenue as month_5,
  t2.rebill_activity[safe_offset(5)].rebill_revenue as month_6,
  t2.rebill_activity[safe_offset(6)].rebill_revenue as month_7,
  t2.rebill_activity[safe_offset(7)].rebill_revenue as month_8,
  t2.rebill_activity[safe_offset(8)].rebill_revenue as month_9,
  t2.rebill_activity[safe_offset(9)].rebill_revenue as month_10,
  t2.rebill_activity[safe_offset(10)].rebill_revenue as month_11,
  t2.rebill_activity[safe_offset(11)].rebill_revenue as month_12,
  -- cumulative revenue per month
  t1.total_original_revenue as cumulative_month_0,
  case when t2.rebill_activity[SAFE_OFFSET(0)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 0
    ) 
  else null end as cumulative_month_1,
  case when t2.rebill_activity[SAFE_OFFSET(1)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 1
    ) 
  else null end as cumulative_month_2,
  case when t2.rebill_activity[SAFE_OFFSET(2)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 2
    ) 
  else null end as cumulative_month_3,
  case when t2.rebill_activity[SAFE_OFFSET(3)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 3
    ) 
  else null end as cumulative_month_4,
  case when t2.rebill_activity[SAFE_OFFSET(4)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 4
    ) 
  else null end as cumulative_month_5,
  case when t2.rebill_activity[SAFE_OFFSET(5)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 5
    ) 
  else null end as cumulative_month_6,
  case when t2.rebill_activity[SAFE_OFFSET(6)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 6
    ) 
  else null end as cumulative_month_7,
  case when t2.rebill_activity[SAFE_OFFSET(7)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 7
    ) 
  else null end as cumulative_month_8,
  case when t2.rebill_activity[SAFE_OFFSET(8)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 8
    ) 
  else null end as cumulative_month_9,
  case when t2.rebill_activity[SAFE_OFFSET(9)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 9
    ) 
  else null end as cumulative_month_10,
  case when t2.rebill_activity[SAFE_OFFSET(10)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 10
    ) 
  else null end as cumulative_month_11,
  case when t2.rebill_activity[SAFE_OFFSET(11)].month <= CURRENT_DATE() then
    t1.total_original_revenue + (
      select sum(rebill_revenue) from UNNEST(t2.rebill_activity) WITH OFFSET as offset where offset <= 11
    ) 
  else null end as cumulative_month_12,
  -- active percentage
  1 as active_month_0,
  case when t2.rebill_activity[SAFE_OFFSET(0)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(0)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_1,
  case when t2.rebill_activity[SAFE_OFFSET(1)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(1)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_2,
  case when t2.rebill_activity[SAFE_OFFSET(2)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(2)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_3,
  case when t2.rebill_activity[SAFE_OFFSET(3)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(3)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_4,
  case when t2.rebill_activity[SAFE_OFFSET(4)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(4)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_5,
  case when t2.rebill_activity[SAFE_OFFSET(5)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(5)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_6,
  case when t2.rebill_activity[SAFE_OFFSET(6)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(6)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_7,
  case when t2.rebill_activity[SAFE_OFFSET(7)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(7)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_8,
  case when t2.rebill_activity[SAFE_OFFSET(8)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(8)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_9,
  case when t2.rebill_activity[SAFE_OFFSET(9)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(9)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_10,
  case when t2.rebill_activity[SAFE_OFFSET(10)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(10)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_11,
  case when t2.rebill_activity[SAFE_OFFSET(11)].month <= CURRENT_DATE() then
    ROUND(SAFE_DIVIDE(t2.rebill_activity[SAFE_OFFSET(11)].rebill_active_count, t1.total_customers), 4)
  else null end as active_month_12,
  from primary_rebill_interval_classified_auto_orders_rolled_up t1
  left join primary_rebill_interval_cohort_rebill_agg t2 on t2.primary_rebill_interval = t1.primary_rebill_interval and t2.cohort = t1.cohort
  where t1.cohort is not null
  order by primary_rebill_interval, cohort
),

per_item_cumlative_analysis as (
  select * except (
    month_0, month_1, month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12,
    active_month_1, active_month_2, active_month_3, active_month_4, active_month_5, active_month_6, active_month_7, active_month_8, active_month_9, active_month_10, active_month_11, active_month_12
  )
  from per_item_output
),
per_item_monthly_analysis as (
  select * except (
    cumulative_month_0, cumulative_month_1, cumulative_month_2, cumulative_month_3, cumulative_month_4, cumulative_month_5, cumulative_month_6, cumulative_month_7, cumulative_month_8, cumulative_month_9, cumulative_month_10, cumulative_month_11, cumulative_month_12,
    active_month_1, active_month_2, active_month_3, active_month_4, active_month_5, active_month_6, active_month_7, active_month_8, active_month_9, active_month_10, active_month_11, active_month_12
  )
  from per_item_output
),
per_item_churn_analysis as (
  select * except (
    month_0, month_1, month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12,
    cumulative_month_0, cumulative_month_1, cumulative_month_2, cumulative_month_3, cumulative_month_4, cumulative_month_5, cumulative_month_6, cumulative_month_7, cumulative_month_8, cumulative_month_9, cumulative_month_10, cumulative_month_11, cumulative_month_12
  )
  from per_item_output
),
primary_rebill_interval_cumlative_analysis as (
  select * except (
    month_0, month_1, month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12,
    active_month_1, active_month_2, active_month_3, active_month_4, active_month_5, active_month_6, active_month_7, active_month_8, active_month_9, active_month_10, active_month_11, active_month_12
  )
  from primary_rebill_interval_output
),
primary_rebill_interval_monthly_analysis as (
  select * except (
    cumulative_month_0, cumulative_month_1, cumulative_month_2, cumulative_month_3, cumulative_month_4, cumulative_month_5, cumulative_month_6, cumulative_month_7, cumulative_month_8, cumulative_month_9, cumulative_month_10, cumulative_month_11, cumulative_month_12,
    active_month_1, active_month_2, active_month_3, active_month_4, active_month_5, active_month_6, active_month_7, active_month_8, active_month_9, active_month_10, active_month_11, active_month_12
  )
  from primary_rebill_interval_output
),
primary_rebill_interval_churn_analysis as (
  select * except (
    month_0, month_1, month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12,
    cumulative_month_0, cumulative_month_1, cumulative_month_2, cumulative_month_3, cumulative_month_4, cumulative_month_5, cumulative_month_6, cumulative_month_7, cumulative_month_8, cumulative_month_9, cumulative_month_10, cumulative_month_11, cumulative_month_12
  )
  from primary_rebill_interval_output
)
select * from per_item_cumlative_analysis
-- select * from per_item_monthly_analysis
-- select * from per_item_churn_analysis
-- select * from primary_rebill_interval_cumlative_analysis
-- select * from primary_rebill_interval_monthly_analysis
-- select * from primary_rebill_interval_churn_analysis
-- select AVG(active_month_3) as average_active_at_end_of_month_3_for_1_month_rebills from primary_rebill_interval_churn_analysis where primary_rebill_interval = '1 month' and active_month_3 is not null
-- select active_month_3 from primary_rebill_interval_churn_analysis where primary_rebill_interval = '1 month' and active_month_3 is not null order by cohort