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(ucas.utms[SAFE_OFFSET(0)].utm_source, '') as utm_source_1,
coalesce(ucas.utms[SAFE_OFFSET(0)].utm_medium, '') as utm_medium_1,
coalesce(ucas.utms[SAFE_OFFSET(0)].utm_campaign, '') as utm_campaign_1,
coalesce(ucas.utms[SAFE_OFFSET(0)].utm_term, '') as utm_term_1,
coalesce(ucas.utms[SAFE_OFFSET(1)].utm_source, '') as utm_source_2,
coalesce(ucas.utms[SAFE_OFFSET(1)].utm_medium, '') as utm_medium_2,
coalesce(ucas.utms[SAFE_OFFSET(1)].utm_campaign, '') as utm_campaign_2,
coalesce(ucas.utms[SAFE_OFFSET(1)].utm_term, '') as utm_term_2,
coalesce(ucas.utms[SAFE_OFFSET(2)].utm_source, '') as utm_source_3,
coalesce(ucas.utms[SAFE_OFFSET(2)].utm_medium, '') as utm_medium_3,
coalesce(ucas.utms[SAFE_OFFSET(2)].utm_campaign, '') as utm_campaign_3,
coalesce(ucas.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(ucas.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(ucas.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(ucas.utms) as utm WITH OFFSET click_offset
where ARRAY_LENGTH(ucas.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
Meta Ad Reconciliation
Ad platforms report the conversions they credit to themselves, including view-through conversions that UltraCart cannot observe. These three queries count what UltraCart can observe for a month: orders that carried a Meta ad click, Meta ad visitors and how many of them bought, and the same count with a lookback that matches Meta's default 7-day click window. Compare the results with the 7-day click figure in Meta Ads Manager, as described in Why Facebook Reports More Conversions Than UltraCart.
The examples cover October 2026 in Eastern time. To run another period, change the dates in each query:
-
The
DATE(...) BETWEENlines set the period you are reporting on. Warehouse timestamps are stored in UTC, so the queries convert them toAmerica/New_Yorkbefore comparing dates. Change the time zone to match your store. -
The
partition_datelines limit how much ofuc_analytics_sessionsis scanned. Each weekly partition is keyed by the Sunday that starts the week, so start the range on the Sunday on or before your first date and end it on the Sunday on or before your last date. A range that starts on the first of the month skips the days before the first Sunday.
To match other ad platforms, change the click ID and utm_source pattern in each query, for example gclid and google for Google Ads.
Orders With a Meta Ad Click
Counts the month's orders, how many carried any campaign tag, and how many carried a Meta ad click.
WITH o AS (
SELECT
order_id,
summary.total.value AS order_total,
EXISTS (
SELECT 1 FROM UNNEST(utms) u
WHERE u.fbclid IS NOT NULL
OR u.facebook_ad_id IS NOT NULL
OR REGEXP_CONTAINS(LOWER(IFNULL(u.utm_source, '')), r'facebook|^fb|instagram|^ig$|meta')
) AS had_meta_click,
ARRAY_LENGTH(utms) > 0 AS had_any_campaign_tag
FROM `my-data-warehouse.ultracart_dw.uc_orders`
WHERE DATE(TIMESTAMP(creation_dts), 'America/New_York') BETWEEN '2026-10-01' AND '2026-10-31'
AND current_stage != 'Rejected'
)
SELECT
COUNT(*) AS total_orders,
ROUND(SUM(order_total), 2) AS total_revenue,
COUNTIF(had_any_campaign_tag) AS orders_with_any_campaign_tag,
COUNTIF(had_meta_click) AS orders_with_meta_click,
ROUND(SUM(IF(had_meta_click, order_total, 0)), 2) AS revenue_with_meta_click
FROM o;
Meta Ad Visitors Who Bought
Counts visits and visitors that arrived from a Meta ad during the month, and how many of those visitors placed an order in the same month.
WITH s AS (
SELECT
client_id,
order_id,
EXISTS (
SELECT 1 FROM UNNEST(utms) u
WHERE u.fbclid IS NOT NULL
OR u.fb_ad_id IS NOT NULL
OR REGEXP_CONTAINS(LOWER(IFNULL(u.utm_source, '')), r'facebook|^fb|instagram|^ig$|meta')
) AS from_meta_ad
FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions`
WHERE partition_date BETWEEN '2026-09-27' AND '2026-10-25' -- weeks covering Oct 1 to 31
AND DATE(TIMESTAMP(session_dts), 'America/New_York') BETWEEN '2026-10-01' AND '2026-10-31'
)
SELECT
COUNT(DISTINCT client_id) AS all_visitors,
COUNTIF(from_meta_ad) AS meta_ad_visits,
COUNT(DISTINCT IF(from_meta_ad, client_id, NULL)) AS meta_ad_visitors,
COUNT(DISTINCT order_id) AS orders_tracked,
COUNT(DISTINCT IF(order_id IS NOT NULL
AND client_id IN (SELECT client_id FROM s WHERE from_meta_ad),
order_id, NULL)) AS orders_from_meta_ad_visitors
FROM s;
Meta Ad Visitors Who Bought, With a 7-Day Lookback
Counts the month's orders from anyone who clicked a Meta ad during the month or in the 7 days before it, matching Meta's default click window.
WITH meta_visitors AS (
SELECT DISTINCT client_id
FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions`, UNNEST(utms) u
WHERE partition_date BETWEEN '2026-09-20' AND '2026-10-25' -- weeks covering Sept 24 to Oct 31
AND DATE(TIMESTAMP(session_dts), 'America/New_York') BETWEEN '2026-09-24' AND '2026-10-31'
AND (u.fbclid IS NOT NULL
OR u.fb_ad_id IS NOT NULL
OR REGEXP_CONTAINS(LOWER(IFNULL(u.utm_source, '')), r'facebook|^fb|instagram|^ig$|meta'))
),
month_orders AS (
SELECT DISTINCT client_id, order_id
FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions`
WHERE partition_date BETWEEN '2026-09-27' AND '2026-10-25' -- weeks covering Oct 1 to 31
AND DATE(TIMESTAMP(session_dts), 'America/New_York') BETWEEN '2026-10-01' AND '2026-10-31'
AND order_id IS NOT NULL
)
SELECT COUNT(DISTINCT o.order_id) AS orders_from_meta_ad_clickers
FROM month_orders o
JOIN meta_visitors USING (client_id);
UltraCart identifies a visitor by a browser cookie, so these counts only include purchases made in the same browser as the ad click. A shopper who clicks an ad on a phone and buys on a laptop appears as two unrelated visitors here, while Meta can still connect them.
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`
-- This table is partitioned weekly. The interval controls both the date range returned and the cost of the query.
where partition_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH), WEEK)
-- The session needs to have at least one page view. Ignore sessions unrelated to website traffic
and 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 reached_checkout_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
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.
Sales Page Before the Order Page
When several sales pages send buyers to one shared order page, the order page alone does not say which sales page produced the order. You do not need a separate order page per sales page. Every page view is recorded in the visitor's analytics session, and a session that ends in an order carries the order ID, so you can read the sales page from the page view history.
Both queries below match each order to a sales page in this order:
- The last sales page viewed in the same session before the order page.
- If there is none, the last sales page the same visitor (
client_id) viewed in an earlier session within the lookback window (30 days by default). - If neither exists, the order is marked
direct to order page. Its UTMs show where it came from, such as email, SMS, or affiliate links that go straight to the order page.
Before you run either query:
- Edit the two regular expressions to match your URL paths. The examples assume sales pages under
/funnels/myproduct/sp-*(orpp-*for pre-sell pages) and the order page under/funnels/myproduct/op-*. uc_analytics_sessionsanduc_ordersare partitioned weekly, each partition starting on a Sunday. Always filter onpartition_date. Setorder_endto thepartition_date(the Sunday) of the last week you want to include.- The order total includes upsells taken after the order page, because they become part of the same order.
- Rejected orders stay in the counts but are excluded from revenue in the rollup.
One Row per Order
Returns one row for each order with its order page, the matched sales page, how the match was made, and the most recent UTM set recorded on the order.
DECLARE order_start DATE DEFAULT '2026-09-27';
DECLARE order_end DATE DEFAULT '2026-10-18'; -- partition_date (Sunday) of the last week to include
DECLARE lookback_days INT64 DEFAULT 30;
WITH sessions AS (
SELECT s.client_id, s.client_session_oid, s.order_id, s.session_dts, s.hits, s.utms
FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions` s
WHERE s.partition_date BETWEEN DATE_SUB(order_start, INTERVAL lookback_days + 7 DAY) AND order_end
),
page_views AS (
SELECT s.client_id, s.client_session_oid, s.order_id, h.ts,
h.page_view.url,
REGEXP_EXTRACT(h.page_view.url, r'^https?://[^/]+(/[^?#]*)') AS path
FROM sessions s, UNNEST(s.hits) h
WHERE h.page_view.url IS NOT NULL
),
order_sessions AS (
SELECT pv.order_id, pv.client_id, pv.client_session_oid,
MIN(pv.ts) AS op_ts,
ARRAY_AGG(pv.path ORDER BY pv.ts LIMIT 1)[OFFSET(0)] AS order_page
FROM page_views pv
WHERE pv.order_id IS NOT NULL
AND REGEXP_CONTAINS(pv.path, r'^/funnels/myproduct/op-')
GROUP BY 1, 2, 3
),
same_session_sp AS (
SELECT os.order_id,
ARRAY_AGG(pv.path ORDER BY pv.ts DESC LIMIT 1)[OFFSET(0)] AS sales_page
FROM order_sessions os
JOIN page_views pv
ON pv.client_session_oid = os.client_session_oid
AND pv.ts <= os.op_ts
AND REGEXP_CONTAINS(pv.path, r'^/funnels/myproduct/(sp|pp)-')
GROUP BY 1
),
prior_session_sp AS (
SELECT os.order_id,
ARRAY_AGG(pv.path ORDER BY pv.ts DESC LIMIT 1)[OFFSET(0)] AS sales_page
FROM order_sessions os
JOIN page_views pv
ON pv.client_id = os.client_id
AND pv.client_session_oid != os.client_session_oid
AND pv.ts < os.op_ts
AND pv.ts >= DATETIME_SUB(os.op_ts, INTERVAL lookback_days DAY)
AND REGEXP_CONTAINS(pv.path, r'^/funnels/myproduct/(sp|pp)-')
GROUP BY 1
),
orders AS (
SELECT o.order_id, o.creation_dts, o.current_stage,
o.checkout.storefront_host_name AS storefront,
o.summary.total.value AS total,
(SELECT AS STRUCT u.utm_source, u.utm_medium, u.utm_campaign, u.utm_content
FROM UNNEST(o.utms) u ORDER BY u.click_dts DESC LIMIT 1) AS last_utm
FROM `my-data-warehouse.ultracart_dw.uc_orders` o
WHERE o.partition_date BETWEEN order_start AND order_end
)
SELECT o.order_id, o.creation_dts, o.storefront, o.current_stage, o.total,
os.order_page,
COALESCE(ss.sales_page, ps.sales_page) AS sales_page,
CASE WHEN ss.sales_page IS NOT NULL THEN 'same session'
WHEN ps.sales_page IS NOT NULL THEN 'prior session'
ELSE 'direct to order page' END AS sales_page_match,
o.last_utm.utm_source, o.last_utm.utm_medium, o.last_utm.utm_campaign, o.last_utm.utm_content
FROM orders o
JOIN order_sessions os USING (order_id)
LEFT JOIN same_session_sp ss USING (order_id)
LEFT JOIN prior_session_sp ps USING (order_id)
ORDER BY o.creation_dts;
Example output, with placeholder values:
| order_id | storefront | order_page | sales_page | sales_page_match | utm_source | utm_campaign |
|---|---|---|---|---|---|---|
| ORDER-1001 | shop.example.com | /funnels/myproduct/op-main | /funnels/myproduct/sp-offer-a | same session | spring-launch | |
| ORDER-1002 | shop.example.com | /funnels/myproduct/op-main | /funnels/myproduct/pp-story | prior session | brand-search | |
| ORDER-1003 | shop.example.com | /funnels/myproduct/op-main | direct to order page | weekly-newsletter |
Orders and Revenue by StoreFront and Sales Page
Rolls the same match up by StoreFront and sales page. Trailing .html and / are removed so URL variants of one page combine, and direct orders are grouped by utm_source instead. The query is written out in full, so you can paste it and run it on its own. In one account, four weeks of orders scanned about 0.5 GB.
DECLARE order_start DATE DEFAULT '2026-09-27';
DECLARE order_end DATE DEFAULT '2026-10-18'; -- partition_date (Sunday) of the last week to include
DECLARE lookback_days INT64 DEFAULT 30;
WITH sessions AS (
SELECT s.client_id, s.client_session_oid, s.order_id, s.session_dts, s.hits, s.utms
FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions` s
WHERE s.partition_date BETWEEN DATE_SUB(order_start, INTERVAL lookback_days + 7 DAY) AND order_end
),
page_views AS (
SELECT s.client_id, s.client_session_oid, s.order_id, h.ts,
h.page_view.url,
REGEXP_EXTRACT(h.page_view.url, r'^https?://[^/]+(/[^?#]*)') AS path
FROM sessions s, UNNEST(s.hits) h
WHERE h.page_view.url IS NOT NULL
),
order_sessions AS (
SELECT pv.order_id, pv.client_id, pv.client_session_oid,
MIN(pv.ts) AS op_ts,
ARRAY_AGG(pv.path ORDER BY pv.ts LIMIT 1)[OFFSET(0)] AS order_page
FROM page_views pv
WHERE pv.order_id IS NOT NULL
AND REGEXP_CONTAINS(pv.path, r'^/funnels/myproduct/op-')
GROUP BY 1, 2, 3
),
same_session_sp AS (
SELECT os.order_id,
ARRAY_AGG(pv.path ORDER BY pv.ts DESC LIMIT 1)[OFFSET(0)] AS sales_page
FROM order_sessions os
JOIN page_views pv
ON pv.client_session_oid = os.client_session_oid
AND pv.ts <= os.op_ts
AND REGEXP_CONTAINS(pv.path, r'^/funnels/myproduct/(sp|pp)-')
GROUP BY 1
),
prior_session_sp AS (
SELECT os.order_id,
ARRAY_AGG(pv.path ORDER BY pv.ts DESC LIMIT 1)[OFFSET(0)] AS sales_page
FROM order_sessions os
JOIN page_views pv
ON pv.client_id = os.client_id
AND pv.client_session_oid != os.client_session_oid
AND pv.ts < os.op_ts
AND pv.ts >= DATETIME_SUB(os.op_ts, INTERVAL lookback_days DAY)
AND REGEXP_CONTAINS(pv.path, r'^/funnels/myproduct/(sp|pp)-')
GROUP BY 1
),
orders AS (
SELECT o.order_id, o.creation_dts, o.current_stage,
o.checkout.storefront_host_name AS storefront,
o.summary.total.value AS total,
(SELECT AS STRUCT u.utm_source, u.utm_medium, u.utm_campaign, u.utm_content
FROM UNNEST(o.utms) u ORDER BY u.click_dts DESC LIMIT 1) AS last_utm
FROM `my-data-warehouse.ultracart_dw.uc_orders` o
WHERE o.partition_date BETWEEN order_start AND order_end
),
detail AS (
SELECT o.order_id, o.storefront, o.current_stage, o.total,
COALESCE(ss.sales_page, ps.sales_page) AS sales_page,
CASE WHEN ss.sales_page IS NOT NULL THEN 'same session'
WHEN ps.sales_page IS NOT NULL THEN 'prior session'
ELSE 'direct to order page' END AS sales_page_match,
o.last_utm.utm_source
FROM orders o
JOIN order_sessions os USING (order_id)
LEFT JOIN same_session_sp ss USING (order_id)
LEFT JOIN prior_session_sp ps USING (order_id)
)
SELECT storefront, sales_page_match,
IF(sales_page_match = 'direct to order page',
CONCAT('utm_source=', IFNULL(utm_source, '(none)')),
REGEXP_REPLACE(sales_page, r'(\.html|/)$', '')) AS source,
COUNT(*) AS orders,
COUNTIF(current_stage != 'Rejected') AS non_rejected,
ROUND(SUM(IF(current_stage != 'Rejected', total, 0)), 2) AS revenue
FROM detail
GROUP BY 1, 2, 3
ORDER BY storefront, orders DESC;
Tips
- If the share of order page sessions with no sales page looks high, count orders instead. Single-page-view sessions from bots, email link scanners, and bounces inflate the session count and rarely order.
- Do not add
utm_parameters to links between your own pages. A newutm_value in the middle of a visit can be recorded as a new campaign touch and take credit from the ad that brought the visitor. - For email, SMS, and affiliate links that go straight to the order page, use
utm_contentto tell messages and creatives apart.