Cohort and Churn Queries

Overview

A cohort groups customers or auto orders by when they started, then follows each group forward through time. The queries here build those groups and pivot the result so each cohort is a row and each month since the cohort started is a column.

Cohort queries scan the whole history of a table, so they are among the more expensive queries in this library. Run one with the BigQuery dry-run estimate first if cost matters.

Auto Order Weekly Cohort by Main Item Id

Groups auto orders into weekly cohorts by the first item on the subscription, then reports how many are still enabled, how many were refunded, gross, refunded, and net revenue for both the original order and the rebills, and the average number of rebills per auto order.

WITH ao_rows as (
    -- Group in weekly cohorts
    SELECT DATE_TRUNC(original_order.creation_dts, WEEK) as weekly_cohort,
    original_order_id,
    -- Consider the first auto order item as the main subscription
    items[safe_offset(0)].original_item_id as main_auto_order_item_id,
    -- integer 1 = enabled 0 = disabled that can be summed
    case when enabled then 1 else 0 end as enabled,
    original_order.creation_dts,
    -- has the original order been refunded?
    case when original_order.summary.total_refunded.value > 0 then 1 else 0 end as original_order_refunded,
    -- when was the original order refunded?
    original_order.refund_dts as original_order_refund_dts,
    -- how many unrefunded rebills have there been?
    (
        select count(*) from UNNEST(rebill_orders) as r where r.summary.total_refunded.value is null
    ) as rebill_order_unrefunded,    
    -- have any of the rebills been refunded?
    coalesce((
        select 1 from UNNEST(rebill_orders) as r where r.summary.total_refunded.value > 0 LIMIT 1
    ), 0) as rebill_order_refunded,
    -- when was the most recent rebill refund
    (
        select max(refund_dts) from UNNEST(rebill_orders) as r where r.summary.total_refunded.value > 0
    ) as rebill_order_refund_dts,
    -- how much unrefunded revenue has been generated?
    (original_order.summary.total.value) as gross_original_total_revenue,
    (coalesce(original_order.summary.total_refunded.value, 0)) as refunded_original_total_revenue,
    (original_order.summary.total.value - coalesce(original_order.summary.total_refunded.value, 0)) as net_original_total_revenue,
    -- how much unrefunded revenue has been generated?
    (coalesce((
      select sum(r.summary.total.value) from UNNEST(rebill_orders) as r
    ), 0)) as gross_rebill_total_revenue,
    (coalesce((
      select  sum(coalesce(r.summary.total_refunded.value, 0)) from UNNEST(rebill_orders) as r
    ), 0)) as refunded_rebill_total_revenue,
    (coalesce((
      select sum(r.summary.total.value) - sum(coalesce(r.summary.total_refunded.value, 0)) from UNNEST(rebill_orders) as r
    ), 0)) as net_rebill_total_revenue,
    -- how much unrefunded revenue has been generated?
    (original_order.summary.total.value  
    + coalesce((
      select sum(r.summary.total.value)  from UNNEST(rebill_orders) as r
    ), 0)) as gross_combined_total_revenue,
    (coalesce(original_order.summary.total_refunded.value, 0) 
    + coalesce((
      select  sum(coalesce(r.summary.total_refunded.value, 0)) from UNNEST(rebill_orders) as r
    ), 0)) as refunded_combined_total_revenue,
    (original_order.summary.total.value - coalesce(original_order.summary.total_refunded.value, 0) 
    + coalesce((
      select sum(r.summary.total.value) - sum(coalesce(r.summary.total_refunded.value, 0)) from UNNEST(rebill_orders) as r
    ), 0)) as net_combined_total_revenue,
    -- how many rebills
    ARRAY_LENGTH(rebill_orders) as rebill_count
    FROM `my-data-warehouse.my_dataset.uc_auto_orders`
    -- Filter auto orders that had test orders that no longer exist
    where original_order.creation_dts is not null
)
select 
-- output by weekly cohort and main item id
weekly_cohort, main_auto_order_item_id, 
-- how many auto orders in this group?
count(*) as auto_order_count, 
-- how many are still enabled?
sum(enabled) as still_enabled_count,
-- how many have had the original a rebill order refunded?
sum(
    case when original_order_refunded > 0 or rebill_order_refunded > 0 then 1 else 0 end
) as refund_count,
-- sum the various types of revenue
sum(gross_original_total_revenue) as gross_original_total_revenue,
sum(refunded_original_total_revenue) as refunded_original_total_revenue,
sum(net_original_total_revenue) as net_original_total_revenue,
sum(gross_rebill_total_revenue) as gross_rebill_total_revenue,
sum(refunded_rebill_total_revenue) as refunded_rebill_total_revenue,
sum(net_rebill_total_revenue) as net_rebill_total_revenue,
sum(gross_combined_total_revenue) as gross_combined_total_revenue,
sum(refunded_combined_total_revenue) as refunded_combined_total_revenue,
sum(net_combined_total_revenue) as net_combined_total_revenue,
-- what is the average number of rebills for people in this cohort?
trunc(avg(rebill_count), 2) as average_number_of_rebills_per_auto_order
from ao_rows 
group by weekly_cohort, main_auto_order_item_id
order by weekly_cohort desc, main_auto_order_item_id

Auto Order Churn Overall

Builds a daily active count of auto orders from the first subscription forward, then rolls it up by month into starting and ending active counts, additions, cancellations, a churn percentage, and whether the month was growing or shrinking.

WITH auto_order_rows as (
  SELECT 
  auto_order_oid, 
  CAST(DATETIME_TRUNC(original_order.creation_dts, day) as DATE) as start_dts, 
  CAST(DATETIME_TRUNC(coalesce(canceled_dts, disabled_dts), day) as DATE) as churn_dts
  FROM `my-data-warehouse.my_dataset.uc_auto_orders`
  where original_order.current_stage not in ('REJ') and original_order.payment.payment_dts is not null and original_order.payment.test_order is false
),
daily_date_range_rows as (
  SELECT date
  FROM UNNEST(
    GENERATE_DATE_ARRAY((select min(start_dts) from auto_order_rows), CURRENT_DATE(), INTERVAL 1 DAY)
  ) as date
),
daily_rows as (
  select *,
  (
    select count(*) from auto_order_rows 
    where start_dts < date and (churn_dts is null or churn_dts >= date)
  ) as active_at_start,
  (
    select count(*) from auto_order_rows where 
    churn_dts = date
  ) as churned_today,
  (
    select count(*) from auto_order_rows where 
    start_dts = date
  ) as added_today,
  (
    select count(*) from auto_order_rows 
    where start_dts <= date and (churn_dts is null or churn_dts > date)
  ) as active_at_end
  from daily_date_range_rows
  order by date
), 
monthly_date_range_rows as (
  SELECT first_of_month, DATE_SUB(DATE_ADD(first_of_month, INTERVAL 1 MONTH), INTERVAL 1 DAY) as end_of_month
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE_TRUNC((select min(start_dts) from auto_order_rows), MONTH), CURRENT_DATE(), INTERVAL 1 MONTH)
  ) as first_of_month
),
period_rows as (
  select 
  first_of_month as period_start_date, 
  LEAST(end_of_month, CURRENT_DATE()) as period_end_date,
  DATE_DIFF(LEAST(end_of_month, CURRENT_DATE()), first_of_month, day) + 1 as days_in_period 
  from monthly_date_range_rows
),
daily_rows_in_period as (
  select *,
  case when period_start_date = daily_rows.date then active_at_start else null end as period_active_at_start,
  case when period_end_date = daily_rows.date then active_at_end else null end as period_active_at_end
  from period_rows
  left join daily_rows on daily_rows.date between period_rows.period_start_date and period_rows.period_end_date
  order by period_rows.period_start_date, daily_rows.date
),
churn_stats as (
  select 
  period_start_date as month, 
  --period_end_date, 
  days_in_period,
  coalesce(max(period_active_at_start), 0) as period_starting_active,
  sum(added_today) as added_in_period, 
  sum(churned_today) as churned_in_period,
  coalesce(max(period_active_at_end), 0) as period_ending_active,  
  ROUND((SAFE_DIVIDE(sum(churned_today), sum(active_at_start))) * days_in_period * 100, 2) as churn_percentage
  from daily_rows_in_period 
  group by period_start_date, period_end_date, days_in_period
  order by period_start_date, period_end_date
)
select * except(days_in_period),
case 
  when added_in_period > churned_in_period then 'growing'
  when added_in_period < churned_in_period then 'shrinking'
  else ''
end as outcome 
from churn_stats where churn_percentage is not null order by month desc

Auto Order Churn by Item

Runs the same monthly churn calculation as Auto Order Churn Overall, broken out by the first item on each auto order.

WITH auto_order_rows as (
  SELECT 
  auto_order_oid, 
  items[SAFE_OFFSET(0)].original_item_id as primary_item_id,
  CAST(DATETIME_TRUNC(original_order.creation_dts, day) as DATE) as start_dts, 
  CAST(DATETIME_TRUNC(coalesce(canceled_dts, disabled_dts), day) as DATE) as churn_dts
  FROM `my-data-warehouse.my_dataset.uc_auto_orders`
  where original_order.current_stage not in ('REJ') and original_order.payment.payment_dts is not null and original_order.payment.test_order is false
),
primary_item_id_rows as (
  select distinct primary_item_id from auto_order_rows order by primary_item_id
),
daily_date_range_rows as (
  SELECT date
  FROM UNNEST(
    GENERATE_DATE_ARRAY((select min(start_dts) from auto_order_rows), CURRENT_DATE(), INTERVAL 1 DAY)
  ) as date
),
item_daily_date_range_rows as (
  select * from primary_item_id_rows 
  right join daily_date_range_rows on 1 = 1
),
item_daily_rows as (
  select *,
  (
    select count(*) from auto_order_rows 
    where auto_order_rows.primary_item_id = item_daily_date_range_rows.primary_item_id and
    start_dts <= date and (churn_dts is null or churn_dts >= date)
  ) as active_at_start,
  (
    select count(*) from auto_order_rows where 
    auto_order_rows.primary_item_id = item_daily_date_range_rows.primary_item_id and
    churn_dts = date
  ) as churned_today,
  (
    select count(*) from auto_order_rows where 
    auto_order_rows.primary_item_id = item_daily_date_range_rows.primary_item_id and
    start_dts = date
  ) as added_today
  from item_daily_date_range_rows
  order by date
), 
monthly_date_range_rows as (
  SELECT first_of_month, DATE_SUB(DATE_ADD(first_of_month, INTERVAL 1 MONTH), INTERVAL 1 DAY) as end_of_month
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE_TRUNC((select min(start_dts) from auto_order_rows), MONTH), CURRENT_DATE(), INTERVAL 1 MONTH)
  ) as first_of_month
),
period_rows as (
  select 
  first_of_month as period_start_date, 
  LEAST(end_of_month, CURRENT_DATE()) as period_end_date,
  DATE_DIFF(LEAST(end_of_month, CURRENT_DATE()), first_of_month, day) + 1 as days_in_period 
  from monthly_date_range_rows
),
item_daily_rows_in_period as (
  select *,
  case when period_start_date = item_daily_rows.date then active_at_start else null end as period_active_at_start,
  case when period_end_date = item_daily_rows.date then active_at_start - churned_today else null end as period_active_at_end
  from period_rows
  left join item_daily_rows on item_daily_rows.date between period_rows.period_start_date and period_rows.period_end_date
  order by period_rows.period_start_date, item_daily_rows.date
),
churn_stats as (
  select primary_item_id, period_start_date, period_end_date, days_in_period,
  max(period_active_at_start) period_starting_active,
  sum(added_today) as added_in_period, 
  sum(churned_today) as churned_in_period,
  max(period_active_at_end) period_ending_active,  
  ROUND((SAFE_DIVIDE(sum(churned_today), sum(active_at_start))) * days_in_period * 100, 2) as churn_percentage
  from item_daily_rows_in_period 
  group by primary_item_id, period_start_date, period_end_date, days_in_period
  order by primary_item_id, period_start_date, period_end_date
)
select * except(days_in_period),
case 
  when added_in_period > churned_in_period then 'growing'
  when added_in_period < churned_in_period then 'shrinking'
  else ''
end as outcome
from churn_stats where churn_percentage is not null

Customer Cohort Revenue

Groups customers into monthly cohorts by hashed email and pivots their revenue across the first 12 months. The commented where utm_source line marks where to narrow a cohort by traffic source, affiliate, or first item purchased.

with 
customer_bulk_rows as (
    -- everythign is grouped by hashed email to count as a unique customer
    select billing.email_hash,
    -- initial items on the order (except kit components)
    ARRAY(
        select as struct item.merchant_item_id from UNNEST(o1.items) item where kit_component is false
    ) as initial_item_ids,
    -- advertising sources
    (
    select value from UNNEST(properties) where name = 'ucasource'
    ) as utm_source,
    (
    select value from UNNEST(properties) where name = 'ucacampaign'
    ) as utm_campaign,
    affiliates[SAFE_OFFSET(0)].affiliate_oid,
    -- core values about each order that we want in an array.  
    ARRAY(
        select as struct order_id, summary.total.value as total_revenue, creation_dts, cast(creation_dts as date) as creation_date
        from `my-data-warehouse.my_dataset.uc_orders` o2 where o2.billing.email_hash = o1.billing.email_hash
    ) as order_array,
    -- clever use of run numbering which will be filtered in the next query to prevent correlation error
    ROW_NUMBER() OVER ( PARTITION BY billing.email_hash ORDER BY creation_dts ) AS rownum,
    from `my-data-warehouse.my_dataset.uc_orders` o1
    where billing.email_hash is not null
    order by billing.email_hash
),
customer_rows as (
    select *,
    -- calculate their cohort startin group
    (
        select DATE_TRUNC(min(creation_date), MONTH) from UNNEST(order_array)
    ) as cohort_group,
    -- calculate the last period they had any activity
    (
        select DATE_TRUNC(max(creation_date), MONTH) from UNNEST(order_array)
    ) as end_period_start_of_month
    from customer_bulk_rows 
    -- clever filtering to the first row number
    where rownum = 1
),
monthly_date_range_rows as (
  -- generate monthly periods since our first order
  SELECT first_of_month, DATE_SUB(DATE_ADD(first_of_month, INTERVAL 1 MONTH), INTERVAL 1 DAY) as end_of_month
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE_TRUNC((select cast(min(creation_dts) as date) from `my-data-warehouse.my_dataset.uc_orders`), MONTH), CURRENT_DATE(), INTERVAL 1 MONTH)
  ) as first_of_month
),
customer_cohort_intermediate_rows as (
    -- grab the main data, but strip off columns we don't need to output
    select * except (order_array, end_period_start_of_month, rownum),
    -- roll up LTV across all the orders
    (
        select sum(total_revenue) from UNNEST(order_array)
    ) as ltv,
    -- generate the periods
    array (
        select as struct 
        -- nice incremently number by period
        ROW_NUMBER() OVER ( PARTITION BY email_hash ) AS period_number,
        -- date range of the period
        period.first_of_month, period.end_of_month, 
        -- number of orders in the period
        count(distinct(order_id)) as period_order_count,
        -- revenue in the period
        coalesce(sum(total_revenue), 0) as period_total_revenue,
        -- array of orders that occurred during this period
        (
            SELECT ARRAY_AGG(t)
            FROM (SELECT DISTINCT * FROM UNNEST(order_array) v where v.creation_date between period.first_of_month and period.end_of_month) t        
        ) as orders   
        from UNNEST(order_array) o
        RIGHT OUTER JOIN monthly_date_range_rows period on o.creation_dts between period.first_of_month and period.end_of_month 
        -- only look at periods that are between their first and last order
        where period.first_of_month >= cohort_group and period.first_of_month <= end_period_start_of_month
        group by period.first_of_month, period.end_of_month
    ) as periods
    from customer_rows
    order by cohort_group, email_hash
),
customer_cohort_rows as (
  select *,
  -- add in a count of the number of total periods
  ARRAY_LENGTH(periods) as period_count
  from customer_cohort_intermediate_rows
), 
customer_cohort_agg_rows as (
  select cohort_group, p.period_number, sum(p.period_total_revenue) as total_revenue
  from customer_cohort_rows 
  CROSS JOIN UNNEST(periods) p
  -- TODO: This is where you would filter down customers based upon the traffic source, affiliate id or initial item id
  -- where utm_source = 'google'  
  group by cohort_group, p.period_number
  order by cohort_group, p.period_number
)
-- Output a pivoted result of the revenue for each cohort group over the first 12 months for those customers
select * 
from customer_cohort_agg_rows
PIVOT(sum(total_revenue) as period_total_revenue FOR period_number IN (
    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
))
-- Start from a particular month and go forward
where cohort_group >= cast('2021-01-01' as date)
order by cohort_group

Auto Order Cohort Revenue

Groups auto orders into monthly cohorts by the original order date and pivots their revenue across the first 12 months.

with 
customer_bulk_rows as (
    -- everythign is grouped by hashed email to count as a unique customer
    select auto_order_oid as customer_key,
    -- initial items on the order (except kit components)
    ARRAY(
        select as struct item.original_item_id from UNNEST(ao1.items) item
    ) as initial_item_ids,
    -- advertising sources
    (
    select value from UNNEST(ao1.original_order.properties) where name = 'ucasource'
    ) as utm_source,
    (
    select value from UNNEST(ao1.original_order.properties) where name = 'ucacampaign'
    ) as utm_campaign,
    ao1.original_order.affiliates[SAFE_OFFSET(0)].affiliate_oid,
    -- core values about each order that we want in an array.  
    ARRAY(
        select as struct ao1.original_order.order_id, ao1.original_order.summary.total.value as total_revenue, ao1.original_order.creation_dts, cast(ao1.original_order.creation_dts as date) as creation_date
        union all 
        select as struct order_id, summary.total.value as total_revenue, creation_dts, cast(creation_dts as date) as creation_date
        from ao1.rebill_orders
    ) as order_array,
    -- clever use of run numbering which will be filtered in the next query to prevent correlation error
    ROW_NUMBER() OVER ( PARTITION BY ao1.auto_order_oid ) AS rownum,
    from `my-data-warehouse.my_dataset.uc_auto_orders` ao1
    order by auto_order_oid
),
customer_rows as (
    select *,
    -- calculate their cohort startin group
    (
        select DATE_TRUNC(min(creation_date), MONTH) from UNNEST(order_array)
    ) as cohort_group,
    -- calculate the last period they had any activity
    (
        select DATE_TRUNC(max(creation_date), MONTH) from UNNEST(order_array)
    ) as end_period_start_of_month
    from customer_bulk_rows 
    -- clever filtering to the first row number
    where rownum = 1
),
monthly_date_range_rows as (
  -- generate monthly periods since our first order
  SELECT first_of_month, DATE_SUB(DATE_ADD(first_of_month, INTERVAL 1 MONTH), INTERVAL 1 DAY) as end_of_month
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE_TRUNC((select cast(min(original_order.creation_dts) as date) from `my-data-warehouse.my_dataset.uc_auto_orders`), MONTH), CURRENT_DATE(), INTERVAL 1 MONTH)
  ) as first_of_month
),
customer_cohort_intermediate_rows as (
    -- grab the main data, but strip off columns we don't need to output
    select * except (order_array, end_period_start_of_month, rownum),
    -- roll up LTV across all the orders
    (
        select sum(total_revenue) from UNNEST(order_array)
    ) as ltv,
    -- generate the periods
    array (
        select as struct 
        -- nice incremently number by period
        ROW_NUMBER() OVER ( PARTITION BY customer_key ) AS period_number,
        -- date range of the period
        period.first_of_month, period.end_of_month, 
        -- number of orders in the period
        count(distinct(order_id)) as period_order_count,
        -- revenue in the period
        coalesce(sum(total_revenue), 0) as period_total_revenue,
        -- array of orders that occurred during this period
        (
            SELECT ARRAY_AGG(t)
            FROM (SELECT DISTINCT * FROM UNNEST(order_array) v where v.creation_date between period.first_of_month and period.end_of_month) t        
        ) as orders   
        from UNNEST(order_array) o
        RIGHT OUTER JOIN monthly_date_range_rows period on o.creation_dts between period.first_of_month and period.end_of_month 
        -- only look at periods that are between their first and last order
        where period.first_of_month >= cohort_group and period.first_of_month <= end_period_start_of_month
        group by period.first_of_month, period.end_of_month
    ) as periods
    from customer_rows
    order by cohort_group, customer_key
),
customer_cohort_rows as (
  select *,
  -- add in a count of the number of total periods
  ARRAY_LENGTH(periods) as period_count
  from customer_cohort_intermediate_rows
), 
customer_cohort_agg_rows as (
  select cohort_group, p.period_number, sum(p.period_total_revenue) as total_revenue
  from customer_cohort_rows 
  CROSS JOIN UNNEST(periods) p
  -- TODO: This is where you would filter down customers based upon the traffic source, affiliate id or initial item id
  -- where utm_source = 'google'  
  group by cohort_group, p.period_number
  order by cohort_group, p.period_number
)
-- Output a pivoted result of the revenue for each cohort group over the first 12 months for those customers
select * 
from customer_cohort_agg_rows
PIVOT(sum(total_revenue) as period_total_revenue FOR period_number IN (
    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
))
-- Start from a particular month and go forward
where cohort_group >= cast('2021-01-01' as date)
order by cohort_group

Auto Order Cohort Active Count

Groups auto orders into monthly cohorts and pivots how many of each cohort are still active in each of the first 24 months.

with 
customer_bulk_rows as (
    -- everything is grouped by hashed email to count as a unique customer
    select auto_order_oid as customer_key,
    -- initial items on the order (except kit components)
    ARRAY(
        select as struct item.original_item_id from UNNEST(ao1.items) item
    ) as initial_item_ids,
    -- advertising sources
    (
    select value from UNNEST(ao1.original_order.properties) where name = 'ucasource'
    ) as utm_source,
    (
    select value from UNNEST(ao1.original_order.properties) where name = 'ucacampaign'
    ) as utm_campaign,
    ao1.original_order.affiliates[SAFE_OFFSET(0)].affiliate_oid,
    -- is the auto order still active
    DATE_TRUNC(coalesce(ao1.disabled_dts, ao1.canceled_dts), day) as churn_date,
    -- core values about each order that we want in an array.  
    ARRAY(
        select as struct ao1.original_order.order_id, ao1.original_order.summary.total.value as total_revenue, ao1.original_order.creation_dts, cast(ao1.original_order.creation_dts as date) as creation_date
        union all 
        select as struct order_id, summary.total.value as total_revenue, creation_dts, cast(creation_dts as date) as creation_date
        from ao1.rebill_orders
    ) as order_array,
    -- clever use of run numbering which will be filtered in the next query to prevent correlation error
    ROW_NUMBER() OVER ( PARTITION BY ao1.auto_order_oid ) AS rownum,
    from `my-data-warehouse.my_dataset.uc_auto_orders` ao1
    order by auto_order_oid
),
customer_rows as (
    select *,
    -- calculate their cohort startin group
    (
        select DATE_TRUNC(min(creation_date), MONTH) from UNNEST(order_array)
    ) as cohort_group,
    -- calculate the last period they had any activity
    DATE_TRUNC(CURRENT_DATE(), MONTH) as end_period_start_of_month
    from customer_bulk_rows 
    -- clever filtering to the first row number
    where rownum = 1
),
monthly_date_range_rows as (
  -- generate monthly periods since our first order
  SELECT first_of_month, DATE_SUB(DATE_ADD(first_of_month, INTERVAL 1 MONTH), INTERVAL 1 DAY) as end_of_month
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE_TRUNC((select cast(min(original_order.creation_dts) as date) from `my-data-warehouse.my_dataset.uc_auto_orders`), MONTH), CURRENT_DATE(), INTERVAL 1 MONTH)
  ) as first_of_month
),
customer_cohort_intermediate_rows as (
    -- grab the main data, but strip off columns we don't need to output
    select * except (order_array, end_period_start_of_month, rownum),
    -- roll up LTV across all the orders
    (
        select sum(total_revenue) from UNNEST(order_array)
    ) as ltv,
    -- generate the periods
    array (
        select as struct 
        -- nice incremently number by period
        ROW_NUMBER() OVER ( PARTITION BY customer_key ) AS period_number,
        -- date range of the period
        period.first_of_month, period.end_of_month, 
        -- number of orders in the period
        count(distinct(order_id)) as period_order_count,
        -- revenue in the period
        coalesce(sum(total_revenue), 0) as period_total_revenue,
        -- active still during this period
        case when churn_date is null then 1
        when churn_date < period.first_of_month then 0
        when churn_date >= period.first_of_month then 1
        else 0
        end as active_during_period,
        -- array of orders that occurred during this period
        (
            SELECT ARRAY_AGG(t)
            FROM (SELECT DISTINCT * FROM UNNEST(order_array) v where v.creation_date between period.first_of_month and period.end_of_month) t        
        ) as orders   
        from UNNEST(order_array) o
        RIGHT OUTER JOIN monthly_date_range_rows period on o.creation_dts between period.first_of_month and period.end_of_month 
        -- only look at periods that are between their first and last order
        where period.first_of_month >= cohort_group and period.first_of_month <= end_period_start_of_month
        group by period.first_of_month, period.end_of_month
    ) as periods
    from customer_rows
    order by cohort_group, customer_key
),
customer_cohort_rows as (
  select *,
  -- add in a count of the number of total periods
  ARRAY_LENGTH(periods) as period_count
  from customer_cohort_intermediate_rows
), 
customer_cohort_agg_rows as (
  select cohort_group, p.period_number, sum(p.active_during_period) as active_auto_order_count
  from customer_cohort_rows 
  CROSS JOIN UNNEST(periods) p
  -- TODO: This is where you would filter down customers based upon the traffic source, affiliate id or initial item id
  -- where utm_source = 'google'  
  group by cohort_group, p.period_number
  order by cohort_group, p.period_number
),
period_rows as (
    -- Output a pivoted result of the revenue for each cohort group over the first 24 months for those auto order cohorts
    select * 
    from customer_cohort_agg_rows
    PIVOT(sum(active_auto_order_count) as period_active_auto_order_count FOR period_number IN (
        1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24
    ))
    -- Start from a particular month and go forward
  where cohort_group >= cast('2021-01-01' as date)
  order by cohort_group
)
select * from period_rows

Auto Order Cohort Active Percentage

Groups auto orders into monthly cohorts and pivots the percentage still active in each of the first 24 months, so cohorts of different sizes compare directly.

with 
customer_bulk_rows as (
    -- everything is grouped by hashed email to count as a unique customer
    select auto_order_oid as customer_key,
    -- initial items on the order (except kit components)
    ARRAY(
        select as struct item.original_item_id from UNNEST(ao1.items) item
    ) as initial_item_ids,
    -- advertising sources
    (
    select value from UNNEST(ao1.original_order.properties) where name = 'ucasource'
    ) as utm_source,
    (
    select value from UNNEST(ao1.original_order.properties) where name = 'ucacampaign'
    ) as utm_campaign,
    ao1.original_order.affiliates[SAFE_OFFSET(0)].affiliate_oid,
    -- is the auto order still active
    DATE_TRUNC(coalesce(ao1.disabled_dts, ao1.canceled_dts), day) as churn_date,
    -- core values about each order that we want in an array.  
    ARRAY(
        select as struct ao1.original_order.order_id, ao1.original_order.summary.total.value as total_revenue, ao1.original_order.creation_dts, cast(ao1.original_order.creation_dts as date) as creation_date
        union all 
        select as struct order_id, summary.total.value as total_revenue, creation_dts, cast(creation_dts as date) as creation_date
        from ao1.rebill_orders
    ) as order_array,
    -- clever use of run numbering which will be filtered in the next query to prevent correlation error
    ROW_NUMBER() OVER ( PARTITION BY ao1.auto_order_oid ) AS rownum,
    from `my-data-warehouse.my_dataset.uc_auto_orders` ao1
    order by auto_order_oid
),
customer_rows as (
    select *,
    -- calculate their cohort startin group
    (
        select DATE_TRUNC(min(creation_date), MONTH) from UNNEST(order_array)
    ) as cohort_group,
    -- calculate the last period they had any activity
    DATE_TRUNC(CURRENT_DATE(), MONTH) as end_period_start_of_month
    from customer_bulk_rows 
    -- clever filtering to the first row number
    where rownum = 1
),
monthly_date_range_rows as (
  -- generate monthly periods since our first order
  SELECT first_of_month, DATE_SUB(DATE_ADD(first_of_month, INTERVAL 1 MONTH), INTERVAL 1 DAY) as end_of_month
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE_TRUNC((select cast(min(original_order.creation_dts) as date) from `my-data-warehouse.my_dataset.uc_auto_orders`), MONTH), CURRENT_DATE(), INTERVAL 1 MONTH)
  ) as first_of_month
),
customer_cohort_intermediate_rows as (
    -- grab the main data, but strip off columns we don't need to output
    select * except (order_array, end_period_start_of_month, rownum),
    -- roll up LTV across all the orders
    (
        select sum(total_revenue) from UNNEST(order_array)
    ) as ltv,
    -- generate the periods
    array (
        select as struct 
        -- nice incremently number by period
        ROW_NUMBER() OVER ( PARTITION BY customer_key ) AS period_number,
        -- date range of the period
        period.first_of_month, period.end_of_month, 
        -- number of orders in the period
        count(distinct(order_id)) as period_order_count,
        -- revenue in the period
        coalesce(sum(total_revenue), 0) as period_total_revenue,
        -- active still during this period
        case when churn_date is null then 1
        when churn_date < period.first_of_month then 0
        when churn_date >= period.first_of_month then 1
        else 0
        end as active_during_period,
        -- array of orders that occurred during this period
        (
            SELECT ARRAY_AGG(t)
            FROM (SELECT DISTINCT * FROM UNNEST(order_array) v where v.creation_date between period.first_of_month and period.end_of_month) t        
        ) as orders   
        from UNNEST(order_array) o
        RIGHT OUTER JOIN monthly_date_range_rows period on o.creation_dts between period.first_of_month and period.end_of_month 
        -- only look at periods that are between their first and last order
        where period.first_of_month >= cohort_group and period.first_of_month <= end_period_start_of_month
        group by period.first_of_month, period.end_of_month
    ) as periods
    from customer_rows
    order by cohort_group, customer_key
),
customer_cohort_rows as (
  select *,
  -- add in a count of the number of total periods
  ARRAY_LENGTH(periods) as period_count
  from customer_cohort_intermediate_rows
), 
customer_cohort_agg_rows as (
  select cohort_group, p.period_number, sum(p.active_during_period) as active_auto_order_count
  from customer_cohort_rows 
  CROSS JOIN UNNEST(periods) p
  -- TODO: This is where you would filter down customers based upon the traffic source, affiliate id or initial item id
  -- where utm_source = 'google'  
  group by cohort_group, p.period_number
  order by cohort_group, p.period_number
),
customer_cohort_agg_rows2 as (
  select cohort_group, period_number, ROUND(SAFE_DIVIDE(active_auto_order_count,
  (
    select max(active_auto_order_count) from customer_cohort_agg_rows ccar2 where ccar2.cohort_group = ccar1.cohort_group
  )) * 100.0, 1) as active_percentage
  from customer_cohort_agg_rows ccar1
  order by cohort_group, period_number
),
period_rows as (
    -- Output a pivoted result of the revenue for each cohort group over the first 24 months for those auto order cohorts
    select * 
    from customer_cohort_agg_rows2
    PIVOT(sum(active_percentage) as period_active_auto_order_percentage FOR period_number IN (
        1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24
    ))
    -- Start from a particular month and go forward
  where cohort_group >= cast('2021-01-01' as date)
  order by cohort_group
)
select * from period_rows