ATTENTION:
The OWNER USER is the only user that can grant access to the Data Warehouse (BigQuery) integration.

Merchant’s should never attempt to query against the tables in the streaming data set. Streaming data sets contain one row per mutation of the object and will lead to unexpected results.

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)

with
click_order_id as (
  -- Table of click id to order id
  select distinct ac.affiliate_click_oid, al.order_id
  from `my-data-warehouse.ultracart_dw.uc_affiliate_clicks` ac
  LEFT JOIN `my-data-warehouse.ultracart_dw.uc_affiliate_ledgers` al on ac.affiliate_click_oid = al.affiliate_click_oid
  group by ac.affiliate_click_oid, al.order_id
),
click_order_commissions as (
  -- Figure out the commissions associated with those orders so we have
  -- click -> order id + commissions
  select c.*, SUM(ale.transaction_amount) as commissions 
  from click_order_id c
  LEFT JOIN `my-data-warehouse.ultracart_dw.uc_orders` o on o.order_id = c.order_id
  CROSS JOIN UNNEST(o.affiliates) a
  CROSS JOIN UNNEST(a.ledger_entries) ale
  group by c.affiliate_click_oid, c.order_id
),
click_data_raw as (
    -- Add in the affiliate, landing page and subid information
    select 
    DATE(TIMESTAMP(ac.click_dts), "America/New_York") as click_date,
    ac.affiliate_oid, aff.company_name,
    acoi.order_id,
    ac.landing_page,
    coalesce(ac.sub_id, '') as sub_id,
    coalesce(acoi.commissions, 0) as commissions
    FROM `my-data-warehouse.ultracart_dw.uc_affiliate_clicks` ac
    LEFT JOIN `click_order_commissions` acoi on acoi.affiliate_click_oid = ac.affiliate_click_oid
    JOIN `my-data-warehouse.ultracart_dw_medium.uc_affiliates` aff on ac.affiliate_oid = aff.affiliate_oid
),
click_data_int1 as (
    -- Roll things up by date, landing page and subid.  Collect the order ids in an array
    select click_date, count(*) as clickcnt, affiliate_oid, landing_page, sub_id, company_name, count(distinct(order_id)) as order_count,
    (
        ARRAY_AGG(order_id ignore nulls)
    ) as order_ids,
    sum(commissions) as commissions
    from `click_data_raw`
    group by affiliate_oid, landing_page, sub_id, company_name, click_date
),
click_data_int2 as (
  -- Figure out the revenue off those orders.
  select * except(order_ids),
  coalesce((
    select sum(summary.total.value) from UNNEST(order_ids) orderid
    JOIN `my-data-warehouse.ultracart_dw.uc_orders` o on o.order_id = orderid
  ), 0) as revenue
  from `click_data_int1`
)
-- Output the fields with some calculated metrics like AOV, CR, AOC as examples
select *,
coalesce(safe_divide(revenue, order_count), 0) as average_order_value,
coalesce(safe_divide(order_count, clickcnt), 0) as conversion_rate,
coalesce(safe_divide(commissions, order_count), 0) as average_order_commissions,
from `click_data_int2` `
order by click_date desc

SELECT ucas.order_id, 
orders.summary.total.value as total,
coalesce(utms[SAFE_OFFSET(0)].utm_source, '') as utm_source_1, 
coalesce(utms[SAFE_OFFSET(0)].utm_medium, '') as utm_medium_1, 
coalesce(utms[SAFE_OFFSET(0)].utm_campaign, '') as utm_campaign_1, 
coalesce(utms[SAFE_OFFSET(0)].utm_term, '') as utm_term_1, 
coalesce(utms[SAFE_OFFSET(1)].utm_source, '') as utm_source_2, 
coalesce(utms[SAFE_OFFSET(1)].utm_medium, '') as utm_medium_2, 
coalesce(utms[SAFE_OFFSET(1)].utm_campaign, '') as utm_campaign_2, 
coalesce(utms[SAFE_OFFSET(1)].utm_term, '') as utm_term_2, 
coalesce(utms[SAFE_OFFSET(2)].utm_source, '') as utm_source_3, 
coalesce(utms[SAFE_OFFSET(2)].utm_medium, '') as utm_medium_3, 
coalesce(utms[SAFE_OFFSET(2)].utm_campaign, '') as utm_campaign_3, 
coalesce(utms[SAFE_OFFSET(2)].utm_term, '') as utm_term_3
FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions` as ucas
LEFT JOIN `my-data-warehouse.ultracart_dw.uc_orders` as orders on orders.order_id = ucas.order_id
where ARRAY_LENGTH(utms) > 0 and ucas.order_id is not null
and payment.payment_dts is not null
order by session_dts desc

WITH email_rows AS (
  -- Find emails that have made their first purchase in the last 12 months.
  select billing.email_hash, MIN(payment.payment_dts) as first_order_dts FROM `my-data-warehouse.ultracart_dw.uc_orders` orders 
  group by billing.email_hash 
  having first_order_dts >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
),
item_rows as (
  SELECT 
    billing.email_hash, 
    order_id, 
    items.merchant_item_id,
    RANK() OVER ( PARTITION BY billing.email_hash ORDER BY order_id, items.cost.value desc ) AS rank,
    1 as initial_order_count
  FROM `my-data-warehouse.ultracart_dw.uc_orders` orders        
    CROSS JOIN UNNEST(items) as items
    LEFT JOIN email_rows on email_rows.email_hash = orders.billing.email_hash
  WHERE 
    orders.partition_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), WEEK)
    AND payment.payment_dts between DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
    AND orders.auto_order is null 
    AND orders.channel_partner is null
    AND orders.billing.email_hash <> '' 
    AND orders.billing.email_hash is not null
    AND payment.test_order = false 
    AND current_stage NOT LIKE "%Rejected%"
    AND payment.payment_status NOT LIKE "Declined"
    AND payment.payment_status NOT LIKE "Unprocessed"
    AND payment.payment_status NOT LIKE "Skipped"
    AND billing.email_hash in (select email_rows.email_hash from email_rows)
    order by order_id desc
)
SELECT 
  item_rows.merchant_item_id, 
  ROUND(AVG(COALESCE(orders.summary.subtotal.value, 0) - COALESCE(orders.summary.subtotal_discount.value, 0)), 2) as additional_ltv,
  COUNT(orders.order_id) as additional_order_count,
  SUM(item_rows.initial_order_count) as initial_order_count,  
FROM item_rows 
  LEFT JOIN `my-data-warehouse.ultracart_dw.uc_orders` orders 
    on orders.billing.email_hash = item_rows.email_hash and orders.order_id <> item_rows.order_id 
    and orders.partition_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), WEEK)
    AND orders.auto_order is null
  where rank = 1
  group by item_rows.merchant_item_id
  having initial_order_count >= 50
  order by item_rows.merchant_item_id

with order_rows as (
    SELECT DATE(TIMESTAMP(creation_dts), "America/New_York") as creation_date_est,
    order_id,
    summary.subtotal.value as subtotal_before_discounts,
    coalesce(summary.subtotal_discount.value, 0) as subtotal_discount,
    summary.subtotal.value - coalesce(summary.subtotal_discount.value, 0) as subtotal,
    summary.total.value,
    payment.payment_status,
    checkout.storefront_host_name,
    (
        select p.value from UNNEST(properties) p where p.name = 'ucasource' LIMIT 1
    ) as utm_source,
    (
        select p.value from UNNEST(properties) p where p.name = 'ucacampaign' LIMIT 1
    ) as utm_campaign,
    (
        select p.value from UNNEST(properties) p where p.name = 'ucaUtmTerm' LIMIT 1
    ) as utm_term,
    (
        select p.value from UNNEST(properties) p where p.name = 'ucaUtmContent' LIMIT 1
    ) as utm_content,
    (
        select p.value from UNNEST(properties) p where p.name = 'ucaUtmMedium' LIMIT 1
    ) as utm_medium,
    (
        select p.value from UNNEST(properties) p where p.name = 'ucaUtmId' LIMIT 1
    ) as utm_id,
    FROM `my-data-warehouse.ultracart_dw.uc_orders`
    order by creation_dts desc
)
select 
CAST(DATE_TRUNC(order_rows.creation_date_est, WEEK) as STRING) as sheet_partition_key,
CONCAT(coalesce(utm_source, ''), ' - ', coalesce(utm_campaign, '')) as utm_source_campaign,
count(*) as order_count,
sum(order_rows.subtotal)  as subtotal_revenue
from order_rows 
where payment_status = 'Processed'
group by sheet_partition_key, utm_source, utm_campaign
order by sheet_partition_key desc, subtotal_revenue desc

with session_rows as (
  select client_id, session_dts, order_id, 
  ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY session_dts DESC) AS session_index,
  utms
  FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions` as ucas1
  WHERE 
  partition_date >= DATE_TRUNC(datetime_sub(current_datetime(), interval 2 month), week) and
  ARRAY_LENGTH(utms) > 0 and session_dts >= datetime_sub(current_datetime(), interval 2 month)
  order by client_id, session_index
),
filtered_session_rows as (
  select * 
  from session_rows
  where (session_index = 1 or order_id is null)
  and (
    (session_index = 1 and session_dts >= datetime_sub(current_datetime(), interval 1 month)) or 
    (session_index > 1 and session_dts >= datetime_sub(current_datetime(), interval 2 month))
  )
),
concat_session_rows as (
  select client_id,
  max(session_dts) as session_dts, 
  max(order_id) as order_id,
  ARRAY_CONCAT_AGG(utms) as utms
  from filtered_session_rows
  group by client_id
),
trimmed_session_rows as (
  select client_id, order_id, session_dts,
  ARRAY(
    select as struct * EXCEPT(OFFSET)
    from concat_session_rows.utms WITH OFFSET
    WHERE OFFSET < 5
  ) as utms
  from concat_session_rows
),
click_rows as (
  select COALESCE(orders.summary.total.value, 0) as total, click_offset + 1 as click_index, ARRAY_LENGTH(utms) as total_clicks, utm.*, 
  FROM trimmed_session_rows as ucas
  LEFT JOIN `my-data-warehouse.ultracart_dw.uc_orders` as orders on orders.order_id = ucas.order_id
  CROSS JOIN UNNEST(utms) as utm WITH OFFSET click_offset
  where ARRAY_LENGTH(utms) > 0 
  and session_dts >= DATETIME_SUB(CURRENT_DATETIME, interval 1 month)
  order by session_dts desc  
),
weighted_click_rows as (
  select *,
  case 
  when total_clicks = 1 then 1 
  when total_clicks = 2 and click_index = 1 then 0.5
  when total_clicks = 2 and click_index = 2 then 0.5 
  when total_clicks = 3 and click_index = 1 then 0.4
  when total_clicks = 3 and click_index = 2 then 0.2 
  when total_clicks = 3 and click_index = 3 then 0.4
  when total_clicks = 4 and click_index = 1 then 0.4 
  when total_clicks = 4 and click_index = 2 then 0.1
  when total_clicks = 4 and click_index = 3 then 0.1 
  when total_clicks = 4 and click_index = 4 then 0.4  
  when total_clicks = 5 and click_index = 1 then 0.4 
  when total_clicks = 5 and click_index = 2 then 0.066
  when total_clicks = 5 and click_index = 3 then 0.066
  when total_clicks = 5 and click_index = 4 then 0.066
  when total_clicks = 5 and click_index = 5 then 0.4
  else 0  
  end as click_weight
  from click_rows
)
select utm_source, utm_campaign, ROUND(sum(coalesce(total,0) * click_weight), 2) as weighted_revenue, count(*) as total_click_count,
sum (
  case when click_index = 1 then 1 else 0 end
) as final_click_count,
sum (
  case when click_index > 1 then 1 else 0 end
) as assist_click_count 

from weighted_click_rows
group by utm_source, utm_campaign
order by utm_source, utm_campaign

WITH session_experiment_rows as (
  SELECT client_session_oid, order_id, exp.name as experiment_name, hit.experiment.storefront_oid, hit.experiment.experiment_id, hit.experiment.variation, var.variation_name, var.traffic_percentage, var.winner,
  FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions` 
  CROSS JOIN UNNEST(hits) hit
  LEFT JOIN `my-data-warehouse.ultracart_dw.uc_storefront_experiments` exp on exp.id = hit.experiment.experiment_id
  CROSS JOIN UNNEST(variations) var   
  where hit.type = 'experiment' and var.variation_number = hit.experiment.variation
),
session_experiment_order_rows as ( 
  select session_experiment_rows.* except (order_id), 
  case when orders.payment.payment_dts is not null then orders.order_id else null end as order_id,
  case when orders.payment.payment_dts is not null then orders.summary.total.value else 0 end as revenue,
  (
    select count(hit.type) from UNNEST(full_session.hits) hit
    where hit.type = 'pageview'    
  ) as page_view_count,
  case when (
    select count(hit.type) from UNNEST(full_session.hits) hit
    where hit.type = 'pageview'    
  ) = 1 then 1 else 0 end as bounce_count,
  (
    select count(hit.type) from UNNEST(full_session.hits) hit
    where hit.type = 'checkout' and hit.action = 'add items'
  ) as add_item_count,
  (
    select count(hit.type) from UNNEST(full_session.hits) hit
    where hit.type = 'checkout' and hit.action = 'initiate'
  ) as initiate_checkout_count,
  case when orders.order_id is null or orders.payment.payment_dts is null then 0 else 1 end as order_count,
  coalesce((
    select sum(quantity) from UNNEST(orders.items) where kit_component is false
  ), 0) as item_count,
  case when orders.marketing.cell_phone_opt_in is not null and orders.marketing.cell_phone_opt_in is not false then 1 else 0 end as sms_opt_in_count,
  coalesce((select sum(hit.page_view.time_on_page) from UNNEST(full_session.hits) hit where hit.type = 'pageview'), 0) as session_duration
  from session_experiment_rows
  left join `my-data-warehouse.ultracart_dw.uc_analytics_sessions` full_session on full_session.client_session_oid = session_experiment_rows.client_session_oid
  left join `my-data-warehouse.ultracart_dw.uc_orders` orders on orders.order_id = session_experiment_rows.order_id
)
select storefronts.host_name as storefront, experiment_name, experiment_id, variation, variation_name, traffic_percentage, winner,
count(*) as session_count,
sum(bounce_count) as bounce_count,
sum(page_view_count) as page_view_count,
sum(add_item_count) as add_item_count,
sum(initiate_checkout_count) as initiate_checkout_count,
sum(order_count) as order_count,
sum(item_count) as item_count,
sum(sms_opt_in_count) as sms_opt_in_count,
ROUND(SAFE_DIVIDE(sum(order_count), count(*)) * 100, 3) as conversation_rate,
sum(revenue) as revenue,
ROUND(AVG(session_duration), 0) as average_duration_seconds,
COALESCE(ROUND(SAFE_DIVIDE(SUM(revenue), COUNT(*)), 2), 0) as average_revenue_per_session,
COALESCE(ROUND(SAFE_DIVIDE(SUM(revenue), SUM(order_count)), 2), 0) as average_revenue_per_order,
--ARRAY_AGG(order_id ignore nulls order by order_id) as order_ids
from session_experiment_order_rows
left join `my-data-warehouse.ultracart_dw.uc_storefronts` storefronts on storefronts.storefront_oid = session_experiment_order_rows.storefront_oid
group by storefront, experiment_name, experiment_id, variation, variation_name, traffic_percentage, winner
order by storefront, experiment_name, experiment_id, variation, variation_name

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

with order_rows as (
  SELECT order_id, payment.payment_dts,
  case 
  when checkout.browser.os.family  = 'iOS' then 'Apple Pay'
  when checkout.browser.os.family  = 'Android' then 'Google Pay'
  when checkout.browser.user_agent.family  = 'Safari' then 'Apple Pay'
  when checkout.browser.user_agent.family  = 'Mobile Safari' then 'Apple Pay'
  when checkout.browser.user_agent.family  = 'Chrome' then 'Google Pay'
  when checkout.browser.user_agent.family  = 'Chrome Mobile iOS' then 'Apple Pay'
  when checkout.browser.user_agent.family  = 'Chrome Mobile' then 'Google Pay'
  when checkout.browser.user_agent.family  = 'Edge' then 'Microsoft Pay'
  when checkout.browser.os.family is not null then checkout.browser.os.family
  else 'unknown'
  end as payment_provider
  FROM `ultracart_dw.uc_orders` 
  CROSS JOIN UNNEST(payment.transactions) pt
  CROSS JOIN UNNEST(pt.details) ptd
  where payment.payment_method = 'Credit Card' and ptd.name = 'payment_intent'
)
select payment_provider, count(*) from order_rows group by payment_provider

SELECT o.billing.email, o.billing.first_name, o.billing.last_name, o.order_id, creation_dts,
(
  select ARRAY_AGG(oi.merchant_item_id) from UNNEST(items) oi where oi.kit_component is false
) as item_ids
FROM `ultracart_dw_medium.uc_orders` o
WHERE o.billing.email is not null and o.payment.payment_dts between PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2023-04-01 00:00:00') and PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2023-04-21 23:59:59') 
and o.billing.email not in (
  select o2.billing.email FROM `ultracart_dw_medium.uc_orders` o2 where o2.payment.payment_dts < PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2023-04-01 00:00:00') and o2.billing.email is not null
)
order by o.billing.email, o.creation_dts

WITH session_rows as (
  SELECT 
  client_id,
  DATETIME_TRUNC(DATETIME(TIMESTAMP(session_dts), "America/New_York"), day) as session_date_est,
  DATETIME(TIMESTAMP(session_dts), "America/New_York") as session_dts_est,
  coalesce((
    select 1 from UNNEST(hits) h
    where h.checkout_add_items is not null or 
    -- if they reached the checkout then they added an item.  This accounts to return to cart or direct cart marketing
    (
      h.page_view is not null and (h.page_view.url like '%/checkout/single%' or h.page_view.url like '%/UCEditor%')
    )
    LIMIT 1
  ), 0) as add_to_cart,
  coalesce((
    select 1 from UNNEST(hits) h
    where h.page_view is not null and (h.page_view.url like '%/checkout/single%' or h.page_view.url like '%/UCEditor%')
    -- if you fire this later event then it has to count here as well so we can handle custom checkouts
    or h.checkout_initiate is not null
    LIMIT 1
  ), 0) as reached_checkout,
  coalesce((
    select 1 from UNNEST(hits) h
    where h.checkout_initiate is not null
    LIMIT 1
  ), 0) as initiate_checkout,
  coalesce((
    select 1 from UNNEST(hits) h
    where h.ecommerce_payment is not null or h.ecommerce_placed_order is not null
    LIMIT 1
  ), 0) as placed_order,
  FROM `ultracart_dw.uc_analytics_sessions`
  -- The session needs to have at least one page view.  Ignore sessions unrelated to website traffic
  where exists (
    select 1 from UNNEST(hits) h
    where h.page_view is not null
    LIMIT 1
  )
),
stat_rows as (
  select 
  session_date_est, 
  count(*) as session_count, 
  sum(add_to_cart) as add_to_cart, 
  sum(reached_checkout) as reached_checkout, 
  sum(initiate_checkout) as initiate_checkout,
  sum(placed_order) as placed_order
  from session_rows group by session_rows.session_date_est
)
select *,
ROUND(SAFE_DIVIDE(add_to_cart, session_count) * 100, 2) as add_to_cart_percentage,
ROUND(SAFE_DIVIDE(reached_checkout, session_count) * 100, 2) as add_to_cart_percentage,
ROUND(SAFE_DIVIDE(initiate_checkout, session_count) * 100, 2) as initiate_checkout_percentage,
ROUND(SAFE_DIVIDE(placed_order, session_count) * 100, 2) as placed_order_percentage,
from stat_rows 
order by session_date_est desc

with order_rows as (
  SELECT order_id, auto_order.original_order_id, 
  DATETIME_TRUNC(creation_dts, day) as creation_date,
  payment.payment_method as payment_method
  FROM `ultracart_dw.uc_orders` 
  WHERE creation_dts >= DATETIME_SUB(CURRENT_DATETIME(), interval 12 month)
  and (auto_order.original_order_id is null or auto_order.original_order_id = order_id) -- filter out rebills
  and channel_partner is null -- filter out channel partners
),
stat_rows as (
  select 
  creation_date,
  case when original_order_id is not null then 1 else 0 end as auto_order_count,
  case when original_order_id is not null and payment_method = 'Credit Card' then 1 else 0 end as cc_auto_order_count,
  case when original_order_id is not null and payment_method = 'PayPal' then 1 else 0 end as paypal_auto_order_count,
  case when original_order_id is  null then 1 else 0 end as regular_order_count,
  1 as order_count
  from order_rows
),
rolled_up_rows as (
  select 
  creation_date,
  sum(auto_order_count) as auto_order_count,
  sum(regular_order_count) as regular_order_count,
  sum(order_count) as order_count,
  sum(cc_auto_order_count) as cc_auto_order_count,
  sum(paypal_auto_order_count) as paypal_auto_order_count
  from stat_rows
  group by creation_date order by creation_date asc
),
rolled_up_30d_rows as (
SELECT
  *,
  SUM(regular_order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS regular_order_count_30d,
  SUM(order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS order_count_30d,
  SUM(auto_order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS auto_order_count_30d,
  SUM(cc_auto_order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS cc_auto_order_count_30d,
  SUM(paypal_auto_order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS paypal_auto_order_count_30d
  FROM rolled_up_rows
  ORDER BY creation_date
)
select *,
ROUND(SAFE_DIVIDE(auto_order_count, order_count) * 100, 2) as auto_order_percentage,
ROUND(SAFE_DIVIDE(cc_auto_order_count, auto_order_count) * 100, 2) as cc_auto_order_percentage,
ROUND(SAFE_DIVIDE(paypal_auto_order_count, auto_order_count) * 100, 2) as paypal_auto_order_percentage,
ROUND(SAFE_DIVIDE(auto_order_count_30d, order_count_30d) * 100, 2) as auto_order_percentage_30dma,
ROUND(SAFE_DIVIDE(cc_auto_order_count_30d, auto_order_count_30d) * 100, 2) as cc_auto_order_percentage_30dma,
ROUND(SAFE_DIVIDE(paypal_auto_order_count_30d, auto_order_count_30d) * 100, 2) as paypal_auto_order_percentage_30dma
from rolled_up_30d_rows order by creation_date desc

SELECT 
c.base_coupon_code, sum(summary.subtotal.value - summary.subtotal_discount.value) as subtotal, count(*) as usage
FROM `ultracart_dw.uc_orders` 
CROSS JOIN UNNEST(coupons) c
where DATETIME(TIMESTAMP(creation_dts), "America/New_York") between DATETIME('2023-05-01 00:00:00', 'America/New_York') and DATETIME('2023-06-30 23:59:59', 'America/New_York')
and payment.test_order is false
group by c.base_coupon_code
order by c.base_coupon_code

WITH order_rows as (
SELECT 
    c.base_coupon_code, UPPER(c.coupon_code), order_id, DATETIME(TIMESTAMP(creation_dts), "America/New_York") as creationdate,
    summary.subtotal.value - summary.subtotal_discount.value as subtotal,
    billing.first_name,
    billing.last_name,
    billing.email,
    checkout.customer_ip_address,
    checkout.storefront_host_name,
    i.merchant_item_id,
    ROW_NUMBER() OVER (PARTITION BY c.base_coupon_code, order_id ORDER BY i.discount.value DESC, i.item_index) AS item_index
    FROM `ultracart_dw_medium.uc_orders` 
    CROSS JOIN UNNEST(coupons) c
    CROSS JOIN UNNEST(items) i
    where DATETIME(TIMESTAMP(creation_dts), "America/New_York") between DATETIME('2023-05-01 00:00:00', 'America/New_York') and DATETIME('2023-06-30 23:59:59', 'America/New_York')
    and payment.test_order is false
)
select * from order_rows where item_index = 1 
order by base_coupon_code, order_id

WITH order_rows as (
  SELECT 
  DATETIME(TIMESTAMP(payment.payment_dts), "America/New_York") as payment_datetime_est, -- Convert the payment date from EST
  summary.total.value as total
  FROM `ultracart_dw.uc_orders` 
  where payment.payment_dts is not null -- make sure the payment has been processed
)
select
EXTRACT(hour from payment_datetime_est) as hour, -- Extract the hour
COUNT(*) as order_count, -- Count the orders
SUM(total) as revenue -- Sum the revenue
from order_rows 
where order_rows.payment_datetime_est >= DATETIME_SUB(CURRENT_DATETIME(), interval 1 year) -- where clause to filter to a particular time period
group by hour
order by hour

WITH job_rows as (
SELECT 
  query,
  jobs.total_bytes_processed/1024/1024/1024.0 AS gb_processed, 
  jobs.total_bytes_billed/1024/1024/1024/1024 * 5.000 AS job_cost_usd
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT jobs
  WHERE jobs.creation_time >= '2023-08-01' AND jobs.creation_time < '2023-09-01'
  AND job_type = 'QUERY'     # remove this condition for all job types
  AND project_id = 'my-project-name-here' # adjust this to your project name
  ORDER BY jobs.total_bytes_billed DESC
)
select query, sum(gb_processed) as gb_processed, ROUND(sum(job_cost_usd), 2) as job_cost, count(*) as execution_count From job_rows group by query order by sum(job_cost_usd) desc LIMIT 100

SELECT 
email, 
COALESCE(billing[SAFE_OFFSET(0)].first_name, '') as first_name, 
COALESCE(billing[SAFE_OFFSET(0)].last_name, '') as last_name, 
COALESCE(billing[SAFE_OFFSET(0)].company, '') as company, 
pricing_tiers[SAFE_OFFSET(0)].name as pricing_tier,
coalesce((
  select SUM(o.summary.total.value) from UNNEST(orders) o
), 0) as ltv,
coalesce((
  select SUM(o.summary.total.value) from UNNEST(orders) o where o.creation_dts >= DATETIME_SUB(CURRENT_DATETIME(), interval 1 year)
), 0) as ltv_last_year,
coalesce((
  select SUM(o.summary.total.value) from UNNEST(orders) o where o.creation_dts >= DATETIME_SUB(CURRENT_DATETIME(), interval 3 year)
), 0) as ltv_3_year
FROM `ultracart_dw_medium.uc_customers` 
where pricing_tiers[SAFE_OFFSET(0)].name is not null
order by email

-- Generate a date range of the prior starting 12 months
WITH date_range AS (
  SELECT
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AS start_date,
    CURRENT_DATE() AS end_date
),
months as (
    SELECT DATE_TRUNC(DATE_ADD(start_date, INTERVAL n MONTH), MONTH) as month
    FROM date_range, UNNEST(GENERATE_ARRAY(0, DATE_DIFF(end_date, start_date, MONTH))) AS n
),
-- for each item, create the base table with the month
item_dc_months_base as (
  select distinct month, merchant_item_id, distribution_center_code
  from months
  LEFT JOIN `ultracart_dw.uc_item_inventory_history` iih on 1=1
  group by merchant_item_id, distribution_center_code, month
),
-- for each item, figure out the most recent history record before the start of the month
item_dc_months as (
  select item_dc_months_base.month, item_dc_months_base.merchant_item_id, item_dc_months_base.distribution_center_code, max(history_dts) as most_recent_history_dts
  from item_dc_months_base
  LEFT JOIN `ultracart_dw.uc_item_inventory_history` iih on iih.history_dts <= item_dc_months_base.month and iih.merchant_item_id = item_dc_months_base.merchant_item_id and iih.distribution_center_code = item_dc_months_base.distribution_center_code
  group by merchant_item_id, distribution_center_code, month
),
-- for each item, figure out the inventory level associated with the most recent history record before the start of the month
item_dc_month_level as (
  select item_dc_months.* except (most_recent_history_dts), iih.after_inventory_level as inventory_level
  from item_dc_months
  LEFT JOIN `ultracart_dw.uc_item_inventory_history` iih on iih.merchant_item_id = item_dc_months.merchant_item_id
  and iih.distribution_center_code = item_dc_months.distribution_center_code
  and iih.history_dts = item_dc_months.most_recent_history_dts
  where item_dc_months.merchant_item_id is not null
),
-- create a table that has the first inventory level ever seen to back fill nulls
item_dc_first_level_base as (
  select merchant_item_id, distribution_center_code, inventory_level, ROW_NUMBER() OVER (PARTITION BY merchant_item_id, distribution_center_code ORDER BY month ASC) AS finish_rank 
  from item_dc_month_level 
  where inventory_level is not null
  union all
  select merchant_item_id, distribution_center_code, inventory_level, 999 as finish_rank
  from `ultracart_dw.uc_items`  
  CROSS JOIN UNNEST(shipping.distribution_centers) dc 
  where dc.handles is true
),
item_dc_first_level_base2 as (
  select * except(finish_rank), 
  ROW_NUMBER() OVER (PARTITION BY merchant_item_id, distribution_center_code ORDER BY finish_rank ASC) as finish_rank2
  from item_dc_first_level_base
),
item_dc_first_level as (
  select * except (finish_rank2) from item_dc_first_level_base2 where finish_rank2 = 1
),
-- backfill item_dc_month_level
item_dc_month_level_backfill as (
  select item_dc_month_level.* except (inventory_level), coalesce(item_dc_month_level.inventory_level, item_dc_first_level.inventory_level) as inventory_level
  from item_dc_month_level
  left join item_dc_first_level on item_dc_first_level.merchant_item_id = item_dc_month_level.merchant_item_id and item_dc_first_level.distribution_center_code = item_dc_month_level.distribution_center_code
),
-- figure out which DCs for a particular item had ANY activity in the time period
active_item_dc as (
  select merchant_item_id, distribution_center_code, cast(max(inventory_level) as int) as max_inventory_level 
  from item_dc_month_level_backfill
  group by merchant_item_id, distribution_center_code
  having max_inventory_level > 0
),
-- filter down to active items and bring in the cogs.  This is not stored historically so it's the active value as configured on the item currently
items_with_cogs as (
  select item_dc_month_level_backfill.*, 
  COALESCE((
    select cogs from UNNEST(shipping.distribution_centers) dc where dc.distribution_center_code = item_dc_month_level_backfill.distribution_center_code
  ), i.pricing.cogs) as cogs
  from item_dc_month_level_backfill
  right join active_item_dc on active_item_dc.merchant_item_id = item_dc_month_level_backfill.merchant_item_id and active_item_dc.distribution_center_code = item_dc_month_level_backfill.distribution_center_code
  left join `ultracart_dw.uc_items` i on i.merchant_item_id = item_dc_month_level_backfill.merchant_item_id
)
-- Bring it all together and calculate the value of the inventory
select *, inventory_level * cogs as inventory_value
from items_with_cogs
order by merchant_item_id, distribution_center_code, month

SELECT 
original_order.billing.email,
-- Subscription start date
original_order.creation_dts,
-- How many rebills occurred
ARRAY_LENGTH(rebill_orders) as rebill_count,
-- Is it still active?
enabled as still_active,
-- Calculate the LTV of this auto order based upon total
original_order.summary.total.value +
coalesce((
  select sum(r.summary.total.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
), 0)
as ltv_total,
-- Calculate the LTV of this auto order based upon total after refunds
original_order.summary.total.value +
coalesce((
  select sum(r.summary.total.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
), 0) -
coalesce(original_order.summary.total_refunded.value, 0) - 
coalesce((
  select sum(r.summary.total_refunded.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null and r.summary.total_refunded.value is not null
), 0)
as ltv_total_after_refunds,
-- Calculate the LTV of this auto order based upon subtotal after discount
(original_order.summary.subtotal.value - original_order.summary.subtotal_discount.value) +
coalesce((
  select sum(r.summary.subtotal.value - r.summary.subtotal_discount.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
), 0)
as ltv_subtotal_after_discount,
-- Calculate the LTV of this auto order based upon subtotal after discount and refunds
(original_order.summary.subtotal.value - original_order.summary.subtotal_discount.value) +
coalesce((
  select sum(r.summary.subtotal.value - r.summary.subtotal_discount.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
), 0) -
(coalesce(original_order.summary.subtotal_refunded.value, 0) - coalesce(original_order.summary.subtotal_discount_refunded.value, 0)) -
coalesce((
  select sum(coalesce(r.summary.subtotal_refunded.value, 0) - coalesce(r.summary.subtotal_discount_refunded.value, 0)) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null and r.summary.total_refunded.value is not null
), 0)
as ltv_subtotal_after_discount_and_refunds
FROM `ultracart_dw_medium.uc_auto_orders` 
where 
-- Standard file of test orders
original_order.payment.test_order is false 
-- Make sure the order has been paid for
and original_order.payment.payment_dts is not null
-- Filter out orders placed by UC users
AND original_order.internal.placed_by_user is null
-- Make sure the email is populated
and original_order.billing.email is not null
-- Grab the starting orders on this date range
and original_order.creation_dts BETWEEN '2023-07-23'AND '2023-10-24'
order by email

-- Note there are dates below for the event range that will need to be adjusted.  If you use hard coded dates in the query those are in UTC whereas what you see in UltraCart's UI will be in EST/EDT
-- so there will be slight variations in calculated numbers
with upsell_path_stat_rows as (
  SELECT sf.host_name, sfup.name as path_name, sfupv.name as variation_name, 
  -- rolled up path variant stats
  coalesce((
    select sum(sfuoe.view_count) 
    from UNNEST(sfupv.visibility_ordered_offer_oids) vo
    -- look at the offer events for the visible offers in this path variant for a given date range
    join `ultracart_dw.uc_storefront_upsell_offer_events` sfuoe on sfuoe.storefront_upsell_offer_oid = vo.value
    where sfuoe.event_dts between '2024-03-01' and '2024-04-01'
  ), 0) as view_count,
  coalesce((
    select count(distinct(sfuoe.order_id)) 
    from UNNEST(sfupv.visibility_ordered_offer_oids) vo
    -- look at the offer events for the visible offers in this path variant for a given date range
    join `ultracart_dw.uc_storefront_upsell_offer_events` sfuoe on sfuoe.storefront_upsell_offer_oid = vo.value
    where sfuoe.event_dts between '2024-03-01' and '2024-04-01'
  ), 0) as transactions,
  coalesce((
    select sum(sfuoe.revenue) 
    from UNNEST(sfupv.visibility_ordered_offer_oids) vo
    -- look at the offer events for the visible offers in this path variant for a given date range
    join `ultracart_dw.uc_storefront_upsell_offer_events` sfuoe on sfuoe.storefront_upsell_offer_oid = vo.value
    where sfuoe.event_dts between '2024-03-01' and '2024-04-01'
  ), 0) as revenue,
  coalesce((
    select sum(sfuoe.profit) 
    from UNNEST(sfupv.visibility_ordered_offer_oids) vo
    -- look at the offer events for the visible offers in this path variant for a given date range
    join `ultracart_dw.uc_storefront_upsell_offer_events` sfuoe on sfuoe.storefront_upsell_offer_oid = vo.value
    where sfuoe.event_dts between '2024-03-01' and '2024-04-01'
  ), 0) as profit,
  -- start with the storefronts
  FROM `ultracart_dw.uc_storefronts` sf
  -- find all the upsell paths
  left join `ultracart_dw.uc_storefront_upsell_paths` sfup on sfup.storefront_oid = sf.storefront_oid
  -- loop through each path variation
  CROSS JOIN UNNEST(variations) sfupv
  order by sf.host_name, sfup.path_order
)
-- add the averages into the result set
select *, ROUND(COALESCE(SAFE_DIVIDE(revenue, view_count), 0), 2) as average_visitor_revenue, ROUND(COALESCE(SAFE_DIVIDE(profit, view_count), 0), 2) as average_visitor_profit  
from upsell_path_stat_rows