Page Comparison

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