Affiliate, UTM, and Analytics Queries

Overview

These queries read uc_affiliates, uc_affiliate_clicks, uc_affiliate_ledgers, and uc_analytics_sessions, and join them back to uc_orders to attribute revenue.

uc_analytics_sessions is the largest table in the warehouse and is partitioned weekly. Always include a partition_date filter wider than your reporting range, or a query against it will scan the full history and bill accordingly.

Affiliate Metrics

Builds five related affiliate data sets from orders attributed to an affiliate: an overall summary, per-affiliate statistics, per-affiliate-and-item statistics, and two order-level breakdowns. The final select chooses which one comes back; the other four are commented out below it.

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

Affiliate Click to Order Metrics

Joins affiliate clicks to the orders and commissions they produced, then reports clicks, orders, revenue, and commissions by click date, with average order value, conversion rate, and average commission per order calculated on top.

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

All UTMs for an Order

Returns the first three UTM sets recorded on the analytics session for each paid order, flattened into columns alongside the order total.

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

UTM Sales By Week

Rolls paid orders up by week in Eastern time and by UTM source and campaign, reporting order count and subtotal revenue after discounts.

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

UTM Clicks and Weighted Revenue

Weights every UTM click in the two months of sessions before an order by its position, giving most of the credit to the final click and the earliest click and little to the ones in between. It then reports weighted revenue, total clicks, final clicks, and assist clicks per source and campaign.

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

Conversion Rate from Analytics Sessions

Reports daily session counts and the funnel through them: add to cart, reaching checkout, initiating checkout, and placing an order, with each stage also expressed as a percentage of sessions.

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

Page View History

Page view history will be located with the analytics session table, but this table is MASSIVE and must be queried carefully to keep costs in check.

SELECT
hit.page_view.url,
count(*)
FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions`
CROSS JOIN UNNEST(hits) hit
where
-- This table is partitioned weekly so you should set this to something wider than your range by at least a week
partition_date >= CURRENT_DATE - INTERVAL 2 month
-- Look at a time period that is contained within the partitioned data set
and hit.ts between '2025-02-01' and '2025-03-01'
and hit.type = 'pageview'
group by 1
order by 2 desc
LIMIT 1000

info

The critical thing is to make sure that you always have the partition_date within your query so that you are only looking at a subset of the data within that table. If you're going to be doing a lot querying on this table, you may want to consider using a tool like DBT ( https://www.getdbt.com/ ) to create a schedule materialized view of the data you're interested in.