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