SELECT 
-- Count number of rebills
ARRAY_LENGTH(rebill_orders) as number_of_rebills, 
-- Life time value by adding original order plus rebill values
COALESCE(original_order.summary.total.value, 0) - COALESCE(original_order.summary.total_refunded.value, 0) + 
COALESCE((
    select SUM(COALESCE(r.summary.total.value, 0) - COALESCE(r.summary.total_refunded.value, 0)) from UNNEST(rebill_orders) as r
), 0) as life_time_value,
-- Average order value by adding original order plus rebill values then dividing by total number of orders
ROUND(
    COALESCE(original_order.summary.total.value, 0) - COALESCE(original_order.summary.total_refunded.value, 0) + 
    COALESCE((
        select SUM(COALESCE(r.summary.total.value, 0) - COALESCE(r.summary.total_refunded.value, 0)) from UNNEST(rebill_orders) as r
    ), 0) / (1 + ARRAY_LENGTH(rebill_orders)) 
,2) as average_order_value,
-- How long did the auto order last?  Value will be null if still active
DATE_DIFF(COALESCE(canceled_dts, disabled_dts), original_order.creation_dts, DAY) as duration_days,
-- Retain all the other columns on uc_auto_orders
*
FROM `my-data-warehouse.my_dataset.uc_auto_orders`

select 
DATETIME(TIMESTAMP(o.creation_dts), "America/New_York") as creation_datetime_est,
DATETIME(TIMESTAMP(o.payment.payment_dts), "America/New_York") as payment_datetime_est,
DATETIME(TIMESTAMP(o.shipping.shipping_date), "America/New_York") as shipping_datetime_est,
DATETIME(TIMESTAMP(o.refund_dts), "America/New_York") as refund_datetime_est,
DATE(TIMESTAMP(o.creation_dts), "America/New_York") as creation_date_est,
DATE(TIMESTAMP(o.payment.payment_dts), "America/New_York") as payment_date_est,
DATE(TIMESTAMP(o.shipping.shipping_date), "America/New_York") as shipping_date_est,
DATE(TIMESTAMP(o.refund_dts), "America/New_York") as refund_date_est,
o.*
from `my-data-warehouse.my_dataset.uc_orders` as o

SELECT 
auto_order_oid,
auto_order_code,
status,
enabled,    
original_order.order_id as original_order_id,
orders.current_stage as original_order_current_stage,    
original_order.billing.first_name,
original_order.billing.last_name,
original_order.billing.email,
original_order.shipping.day_phone_e164,
original_order.shipping.state_region,
original_order.shipping.postal_code,
original_order.shipping.country_code,
item_offset,
future_schedules.item_id as future_schedule_item_id,
future_schedules.shipment_dts as future_schedule_shipment_dts,
future_schedules.unit_cost as future_schedule_unit_cost,
future_schedules.rebill_count as future_schedule_rebill_count,
REGEXP_EXTRACT(failure_reason, '^[^\n]+') as failure_reason,    
FROM `my-data-warehouse.my_dataset.uc_auto_orders` as auto_orders
JOIN `my-data-warehouse.my_dataset.uc_orders` as orders on orders.order_id = auto_orders.original_order_id,
UNNEST(auto_orders.items) as items WITH OFFSET AS item_offset,
UNNEST(items.future_schedules) as future_schedules WITH OFFSET AS future_schedule_offset
WHERE enabled = true
and future_schedule_offset = 0
and orders.payment.payment_dts is not null
and orders.payment.payment_method <> 'Quote Request'
and orders.current_stage <> 'Rejected'
and (items.no_order_after_dts is null or items.no_order_after_dts >= current_date())
ORDER BY future_schedules.shipment_dts ASC

SELECT i.merchant_item_id, sum(i.total_cost_with_discount.value) as revenue_for_item, sum(i.quantity) as units_sold
FROM `my-data-warehouse.my_dataset.uc_orders` 
CROSS JOIN UNNEST(items) as i 
WHERE creation_dts between "2021-07-01" and "2021-08-01" and i.kit_component = false 
group by i.merchant_item_id order by i.merchant_item_id

select 
table_id, ROUND(sum(size_bytes) / (1024 * 1024), 2) as size_in_gb
from
`my-data-warehouse.my_dataset.__TABLES__`
group by table_id

SELECT order_id, 
(
    select params.value.text from UNNEST(events.params) as params where params.name = 'expid'
) as experiment_id,
(
    select params.value.num from UNNEST(events.params) as params where params.name = 'expvid'
) as experiment_variation_number
FROM `my-data-warehouse.my_dataset.uc_storefront_recordings`
CROSS JOIN UNNEST(page_views) as page_views
CROSS JOIN UNNEST(page_views.events) as events
WHERE order_id is not null and events.name = 'experiment'

SELECT distinct order_id
FROM `my-data-warehouse.my_dataset.ultracart_orders` 
CROSS JOIN UNNEST(items) as i
WHERE DATE(TIMESTAMP(shipping.shipping_date), "America/New_York") = "2021-12-09" 
and i.shipped_by_user = 'bob' 
and i.merchant_item_id in (
  'PRODUCT-A',
  'PRODUCT-B'
) order by order_id

SELECT ao.original_order_id, DATE_DIFF(item.next_preshipment_notice_dts, item.next_shipment_dts, DAY) as days_diff
FROM `my-data-warehouse.my_dataset.uc_auto_orders` as ao
CROSS JOIN UNNEST(items) as item
WHERE enabled = True and item.next_preshipment_notice_dts is not null 
and DATE_DIFF(item.next_preshipment_notice_dts, item.next_shipment_dts, DAY) < -7

SELECT 
order_id, 
payment.payment_status, 
creation_dts, 
checkout.custom_field3, 
checkout.custom_field4, 
checkout.custom_field5, 
checkout.custom_field6, 
checkout.custom_field7
FROM `my-data-warehouse.my_dataset.uc_orders` 
WHERE checkout.custom_field4 = '1234'
ORDER BY order_id

SELECT path.name as path_name, variation.name as variation_name, offer.name as offer_name
FROM `my-data-warehouse.my_dataset.uc_storefront_upsell_paths` as path
CROSS JOIN UNNEST(variations) as variation
CROSS JOIN UNNEST(visibility_ordered_offer_oids) as variation_offer
INNER JOIN `my-data-warehouse.my_dataset.uc_storefront_upsell_offers` as offer on 
            offer.storefront_upsell_offer_oid = variation_offer.value
where path.storefront_oid = 1234 -- Replace with your StoreFront OID
ORDER BY path.path_order

SELECT 
sf.host_name, path.name as path_name, 
path_visibility_offer_offset + 1 as path_visibility_offer_offset, 
variation.name as variation_name, 
offer.name as offer_name, 
offer.screenshot_small_full_length_url, 
offer.screenshot_large_full_length_url
FROM `my-data-warehouse.my_dataset.uc_storefront_upsell_paths` as path
CROSS JOIN UNNEST(variations) as variation
CROSS JOIN UNNEST(visibility_ordered_offer_oids) as variation_offer WITH OFFSET AS path_visibility_offer_offset
INNER JOIN `my-data-warehouse.my_dataset.uc_storefront_upsell_offers` as offer on 
            offer.storefront_upsell_offer_oid = variation_offer.value
INNER JOIN `my-data-warehouse.my_dataset.uc_storefronts` as sf on sf.storefront_oid = path.storefront_oid
where offer.active = true and sf.locked = false
ORDER BY sf.host_name, path.path_order, path_visibility_offer_offset

SELECT original_order_id, aoi.original_item_id, aoi.next_preshipment_notice_dts, aoi.preshipment_notice_sent, aoi.next_shipment_dts
FROM `my-data-warehouse.my_dataset.uc_auto_orders` ao
CROSS JOIN UNNEST(items) aoi
where aoi.preshipment_notice_sent = false 
and aoi.next_preshipment_notice_dts is not null 
and aoi.next_preshipment_notice_dts < aoi.next_shipment_dts 
and aoi.next_preshipment_notice_dts < CURRENT_DATETIME()
and (aoi.no_order_after_dts is null or aoi.no_order_after_dts > CURRENT_DATETIME())
and ao.enabled is true

SELECT original_order_id, aoi.original_item_id, aoi.next_preshipment_notice_dts, aoi.preshipment_notice_sent, aoi.next_shipment_dts, 
ARRAY(
    select AS STRUCT log_dts, log_message 
    from UNNEST(ao.logs)
    where log_dts > aoi.next_preshipment_notice_dts
) as logs
FROM `my-data-warehouse.my_dataset.uc_auto_orders` ao
CROSS JOIN UNNEST(items) aoi
where aoi.preshipment_notice_sent = true 
and aoi.next_preshipment_notice_dts is not null 
and aoi.next_preshipment_notice_dts < aoi.next_shipment_dts 
and aoi.next_preshipment_notice_dts < CURRENT_DATETIME()
and (aoi.no_order_after_dts is null or aoi.no_order_after_dts between DATETIME_TRUNC(CURRENT_DATETIME(), DAY) AND CURRENT_DATETIME())
and ao.enabled is true

WITH email_first_rows as (
  SELECT billing.email,
  MIN(payment.payment_dts) as first_order_dts
  FROM `my-data-warehouse.my_dataset.uc_orders`
  WHERE billing.email <> '' and payment.payment_dts is not null 
  and current_stage in ('Shipping Department', 'Completed Order')
  group by billing.email
),
order_rows as (
  select 
  email_first_rows.email, 
  first_order_dts, 
  o2.payment.payment_dts,  
  o2.summary.total.value as total, 
  case when o2.payment.payment_dts <> first_order_dts then o2.summary.total.value else 0 end as additional_total_revenue,
  DATE_DIFF(o2.payment.payment_dts, email_first_rows.first_order_dts, MONTH) as month
  from email_first_rows
  INNER JOIN `my-data-warehouse.my_dataset.uc_orders` o2 on o2.billing.email = email_first_rows.email
  and o2.payment.payment_dts is not null and o2.current_stage in ('Shipping Department', 'Completed Order')
  ORDER BY email_first_rows.email, month
),
month_rows as (
  select email, month, min(payment_dts) as payment_dts, sum(additional_total_revenue) as additional_total_revenue, 
  sum(total) as total, count(*) as order_count from order_rows group by email, month
),
month_ltv as (
  select email, month,
  SUM(order_count) OVER (current_month_window) AS month_order_count,
  SUM(order_count) OVER (months_since_first_order) AS cumuliative_order_count,
  SUM(additional_total_revenue) OVER (current_month_window) AS month_additional_total_revenue,
  SUM(additional_total_revenue) OVER (months_since_first_order) AS cumulative_additional_total_revenue,
  SUM(total) OVER (current_month_window) AS month_total,
  SUM(total) OVER (months_since_first_order) AS cumulative_total
  from month_rows
  WINDOW months_since_first_order AS (
    PARTITION BY email ORDER BY payment_dts asc
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ),
  current_month_window AS (
    PARTITION BY email ORDER BY payment_dts asc
    ROWS BETWEEN CURRENT ROW AND CURRENT ROW
  )
  order by email, month
)
select * from month_ltv

SELECT order_id FROM `my-data-warehouse.my_dataset.uc_orders` 
CROSS JOIN UNNEST(payment.transactions) t
CROSS JOIN UNNEST(t.details) d
WHERE d.value = 'abc123'

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

WITH future_shipment_rows as (
  SELECT auto_order_code, fs.item_id, fs.unit_cost, coalesce(i.arbitrary_quantity, i.original_quantity) as qty, fs.shipment_dts, 
  original_order.billing.email_hash
  FROM `my-data-warehouse.my_dataset.uc_auto_orders` 
  CROSS JOIN UNNEST(items) as i
  CROSS JOIN UNNEST(i.future_schedules) fs
  where fs.shipment_dts < DATETIME_ADD(CURRENT_DATE(), INTERVAL 90 day) and enabled is true
),
future_email_rows as (
  select email_hash, sum(unit_cost * qty) as future_item_revenue
  from future_shipment_rows
  group by email_hash
  order by email_hash
),
future_item_day_rows as (
  select item_id,DATETIME_TRUNC(shipment_dts, day) as day, sum(qty) as future_quantity, sum(unit_cost * qty) as future_item_revenue
  from future_shipment_rows
  group by item_id, day
  order by item_id, day
),
future_item_month_rows as (
  select item_id,DATETIME_TRUNC(shipment_dts, month) as month, sum(qty) as future_quantity, sum(unit_cost * qty) as future_item_revenue
  from future_shipment_rows
  group by item_id, month
  order by item_id, month
)
select * from future_item_day_rows

SELECT order_id, marketing.mailing_list, billing.*, shipping.* FROM `my-data-warehouse.my_dataset.uc_orders`

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

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

WITH 
email_affiliate_owner_rows as (
    select billing.email_hash,
    creation_dts,
    affiliates[SAFE_OFFSET(0)].affiliate_oid
    from `my-data-warehouse.my_dataset.uc_orders`
    where 
    affiliates[SAFE_OFFSET(0)].affiliate_oid is not null and billing.email_hash is not null
    -- and payment.payment_dts >= @start_date and payment.payment_dts <= @end_date
    ORDER BY creation_dts desc
),
repeat_customer_orders as (
    select 
        o1.order_id,
        o1.billing.email_hash,
        o1.summary.total.value - o1.summary.tax.value as gross_revenue,
        COALESCE(o1.summary.total_refunded.value, 0) - COALESCE(o1.summary.tax_refunded.value, 0) as gross_refunded,
        (
            select sum(cogs * quantity) from UNNEST(o1.items)
        ) as item_cogs,
        COALESCE(o1.summary.actual_shipping.value, 0) as actual_shipping,
        COALESCE(o1.summary.actual_fulfillment.value, 0) as actual_fulfillment,
        o1.items[SAFE_OFFSET(0)].merchant_item_id as primary_item_id,
        email_affiliate_owner_rows.affiliate_oid,
        COALESCE((
            select sum(transaction_amount)
            from UNNEST(o1.affiliates[SAFE_OFFSET(0)].ledger_entries) 
        ), 0) as affiliate_commission,
        0 as active_subscription,    
        o1.creation_dts,
        'repeat customer' as order_type,
        row_number() over (partition by order_id order by email_affiliate_owner_rows.creation_dts desc) as rn
    from `my-data-warehouse.my_dataset.uc_orders` o1
    join email_affiliate_owner_rows on email_affiliate_owner_rows.email_hash = o1.billing.email_hash and email_affiliate_owner_rows.creation_dts <= o1.creation_dts
    where 
    -- not an auto order and no affiliate associated
    o1.auto_order is null and o1.affiliates[SAFE_OFFSET(0)].affiliate_oid is null    
    --and o1.payment.payment_dts >= @start_date and o1.payment.payment_dts <= @end_date
),
-- Calculate values per individual orders
order_rows as (
    -- Straight sale or original rebills
    SELECT
    order_id,
    billing.email_hash,
    summary.total.value - summary.tax.value as gross_revenue,
    COALESCE(summary.total_refunded.value, 0) - COALESCE(summary.tax_refunded.value, 0) as gross_refunded,
    (
        select sum(cogs * quantity) from UNNEST(items)
    ) as item_cogs,
    COALESCE(summary.actual_shipping.value, 0) as actual_shipping,
    COALESCE(summary.actual_fulfillment.value, 0) as actual_fulfillment,
    o.items[SAFE_OFFSET(0)].merchant_item_id as primary_item_id,
    o.affiliates[SAFE_OFFSET(0)].affiliate_oid as affiliate_oid,
    COALESCE((
        select sum(transaction_amount)
        from UNNEST(o.affiliates[SAFE_OFFSET(0)].ledger_entries) 
    ), 0) as affiliate_commission,
    -- If this is the original order then see if it's an active subscription
    case 
      when order_id = auto_order.original_order_id and auto_order.enabled is true then 1
      else 0
    end as active_subscription,
    creation_dts,
    case when auto_order is null then 'straight sale' else 'auto order original' end as order_type
    FROM `my-data-warehouse.my_dataset.uc_orders` o
    WHERE current_stage in ('Shipping Department', 'Completed Order')
    and (auto_order is null or order_id = auto_order.original_order_id)
    --and payment.payment_dts >= @start_date and payment.payment_dts <= @end_date
    union all
    -- rebill orders
    select 
        o1.order_id,
        o1.billing.email_hash,
        o1.summary.total.value - o1.summary.tax.value as gross_revenue,
        COALESCE(o1.summary.total_refunded.value, 0) - COALESCE(o1.summary.tax_refunded.value, 0) as gross_refunded,
        (
            select sum(cogs * quantity) from UNNEST(o1.items)
        ) as item_cogs,
        COALESCE(o1.summary.actual_shipping.value, 0) as actual_shipping,
        COALESCE(o1.summary.actual_fulfillment.value, 0) as actual_fulfillment,
        o1.items[SAFE_OFFSET(0)].merchant_item_id as primary_item_id,
        o2.affiliates[SAFE_OFFSET(0)].affiliate_oid as affiliate_oid,
        COALESCE((
            select sum(transaction_amount)
            from UNNEST(o1.affiliates[SAFE_OFFSET(0)].ledger_entries) 
        ), 0) as affiliate_commission,
        0 as active_subscription,    
        o1.creation_dts,
        'auto order rebill' as order_type
    from `my-data-warehouse.my_dataset.uc_orders` o1
    join `my-data-warehouse.my_dataset.uc_orders` o2 on o1.auto_order.original_order_id = o2.order_id
    where o1.auto_order.original_order_id != o1.order_id
    --and o2.payment.payment_dts >= @start_date and o2.payment.payment_dts <= @end_date
    and o2.affiliates[SAFE_OFFSET(0)].affiliate_oid is not null
    union all
    select * except (rn) from repeat_customer_orders where rn = 1
    ORDER BY creation_dts desc
),
-- Join in the affiliate information and calculate the profit
order_by_affiliate_rows as (
  select order_rows.*, aff.email_hash as aff_email_hash,
  gross_revenue - gross_refunded - item_cogs - actual_shipping - actual_fulfillment - affiliate_commission as profit
  from order_rows
  LEFT OUTER JOIN `my-data-warehouse.my_dataset.uc_affiliates` aff on aff.affiliate_oid = order_rows.affiliate_oid
),
-- Per affiliate/customer email sum up the revenue, profit and count of orders
order_by_affiliate_email_rows as (
    select 
    affiliate_oid, 
    email_hash, 
    sum(gross_revenue) as gross_revenue, 
    sum(profit) as profit, 
    count(*) as order_count,
    sum(active_subscription) as active_subscriptions
    from order_by_affiliate_rows
    group by affiliate_oid, email_hash
),
-- Per item sum up the revenue, profit and count of orders
order_by_primary_item_rows as (
    select 
    primary_item_id, 
    sum(gross_revenue) as gross_revenue, 
    sum(profit) as profit, 
    count(*) as order_count,
    sum(active_subscription) as active_subscriptions
    from order_by_affiliate_rows
    group by primary_item_id
    order by primary_item_id
),
-- Calculate the metrics per affiliate
affiliate_stat_rows as (
    select 
    affiliate_oid, 
    ROUND(avg(gross_revenue),2) as avg_gross_revenue_per_customer, 
    ROUND(avg(profit),2) as avg_profit_per_customer, 
    ROUND(avg(order_count),2) as avg_orders_per_customer,
    sum(order_count) as sum_order_count,
    sum(gross_revenue) as sum_gross_revenue,
    sum(profit) as sum_profit,
    sum(active_subscriptions) as sum_active_subscriptions
    from order_by_affiliate_email_rows
    where affiliate_oid is not null
    group by affiliate_oid
    order by affiliate_oid
),
-- Calculate the metrics per affiliate / item id
affiliate_primary_item_id_stat_rows as (
    select 
    affiliate_oid,
    primary_item_id, 
    ROUND(avg(gross_revenue),2) as avg_gross_revenue_per_customer, 
    ROUND(avg(profit),2) as avg_profit_per_customer,
    count(*) as order_count,
    sum(gross_revenue) as sum_gross_revenue,
    sum(profit) as sum_profit,
    sum(active_subscription) as sum_active_subscriptions
    from order_by_affiliate_rows
    where affiliate_oid is not null
    group by affiliate_oid, primary_item_id
    order by affiliate_oid, primary_item_id
),
-- Calculate the metrics overall
overall_stat_rows as (
    select 
    COUNT(DISTINCT affiliate_oid) as number_of_affiliates,
    ROUND(avg(gross_revenue),2) as avg_gross_revenue_per_customer, 
    ROUND(avg(profit),2) as avg_profit_per_customer, 
    ROUND(avg(order_count),2) as avg_orders_per_customer,
    sum(order_count) as sum_order_count,
    sum(gross_revenue) as sum_gross_revenue,
    sum(profit) as sum_profit,
    sum(active_subscriptions) as sum_active_subscriptions
    from order_by_affiliate_email_rows
    where affiliate_oid is not null    
)
-- Five different data sets that can be viewed
select * from overall_stat_rows
-- select * from affiliate_stat_rows
-- select * from affiliate_primary_item_id_stat_rows
-- select * from order_by_primary_item_rows
-- select * except (email_hash, aff_email_hash) from order_by_affiliate_rows where affiliate_oid is not null

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

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

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

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

WITH path_offers as (
  select sup.name as path_name, supvo.value as storefront_upsell_offer_oid 
  from `ultracart_dw.uc_storefront_upsell_paths` sup
  CROSS JOIN UNNEST(variations) supv
  CROSS JOIN UNNEST(visibility_ordered_offer_oids) supvo
),
order_upsell_rows as (
  select
  po.path_name,
  suo.name as offer_name, 
  upsell_item_ids[SAFE_OFFSET(0)].value as upsell_item_id, suoe.order_id,
  case 
    when successful_charge = 1 then true
    else false
  end as took_upsell,
  revenue,
  profit,
  screen_size,
  quantity,
  refund_quantity,
  o.creation_dts as order_dts
  from `ultracart_dw.uc_storefront_upsell_offer_events` suoe
  LEFT JOIN `ultracart_dw.uc_storefront_upsell_offers` suo on suo.storefront_upsell_offer_oid = suoe.storefront_upsell_offer_oid
  LEFT JOIN `path_offers` po on po.storefront_upsell_offer_oid = suo.storefront_upsell_offer_oid
  LEFT JOIN `ultracart_dw.uc_orders` o on o.order_id = suoe.order_id
  where suoe.order_id is not null
  order by order_id, event_dts
)
select * From order_upsell_rows where order_dts >= DATE_SUB(CURRENT_DATE(), interval 1 year)