StoreFront and Upsell Queries

Overview

These queries read the StoreFront tables: uc_storefronts, uc_storefront_upsell_paths, uc_storefront_upsell_offers, uc_storefront_upsell_offer_events, uc_storefront_experiments, and uc_storefront_recordings.

An upsell path holds variations, and each variation holds an ordered array of offer OIDs, so almost every query here unnests variations and then visibility_ordered_offer_oids before joining to the offer itself.

Order ID Experiment Variations

This query will show which experiment and variation each order went through (this requires screen recordings).

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'

List All Upsell Paths, Variation and Upsells

Lists every upsell path on one StoreFront with its variations and the offers each variation shows, in path order. Set path.storefront_oid to your StoreFront OID before running it.

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

Upsell Screenshot List

Lists the small and large screenshot URLs for every active upsell offer across your unlocked StoreFronts, ordered by host name, path, and offer position.

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

Order Upsells

Which upsells were seen by an order and what were the outcomes?

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)

StoreFront Experiment Statistics

Reports one row per experiment variation with its sessions, checkout events, orders, items, SMS opt-ins, conversion rate, revenue, average session duration, and revenue per session and per order.

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

Upsell Path Statistics

Reports views, conversions, revenue, and profit for each upsell path variation over a date range, plus average revenue and profit per visitor. The dates in the query are UTC while the UltraCart screens show Eastern time, so the totals differ slightly from the UI.

-- 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

Find Upsell Offers Containing Text

Searches the upsell offer container JSON across all linked accounts for a string, such as a domain name, and returns the StoreFront, path, variation, and offer that contain it.

SELECT sf.merchant_id, sf.host_name, path.name as path_name, variation.name as variation_name, offer.name as offer_name
FROM `ultracart_dw_linked.uc_storefront_upsell_paths` as path
CROSS JOIN UNNEST(variations) as variation
CROSS JOIN UNNEST(visibility_ordered_offer_oids) as variation_offer
INNER JOIN `ultracart_dw_linked.uc_storefronts` as sf on 
            sf.storefront_oid = path.storefront_oid
INNER JOIN `ultracart_dw_linked.uc_storefront_upsell_offers` as offer on 
            offer.storefront_upsell_offer_oid = variation_offer.value
where offer.offer_container_cjson like '%.com%' -- Between the % should be the domain you're looking for
ORDER BY sf.merchant_id, sf.host_name, path.path_order

Upsell Statistics by Offer and Path (across all linked accounts)

Reports visitors, conversions, revenue, profit, and conversion rate for every upsell path variation across all linked accounts. Swap the final select for the commented one to get the same statistics per offer instead.

WITH offer_rows as (
  SELECT sf.merchant_id, sf.host_name, path.path_order, path.name as path_name, variation.name as variation_name, variation_order, offer.name as offer_name,
  offer.storefront_upsell_offer_oid
  FROM `ultracart_dw_linked.uc_storefront_upsell_paths` as path
  CROSS JOIN UNNEST(variations) as variation
  CROSS JOIN UNNEST(visibility_ordered_offer_oids) as variation_offer with offset variation_order
  INNER JOIN `ultracart_dw_linked.uc_storefront_upsell_offers` as offer on 
            offer.storefront_upsell_offer_oid = variation_offer.value
  INNER JOIN `ultracart_dw_linked.uc_storefronts` as sf on
            sf.storefront_oid = offer.storefront_oid  
),
offer_stat_rows as (
  SELECT 
  oe.storefront_upsell_offer_oid,
  coalesce(sum(view_count),0) as offer_view_count,
  coalesce(sum(successful_charge), 0) as offer_conversion_count,
  LEAST(coalesce(sum(decline_count),0), coalesce(sum(view_count),0) - coalesce(sum(successful_charge), 0)) as offer_decline_count,
  GREATEST(coalesce(sum(view_count),0) - coalesce(sum(successful_charge), 0) - coalesce(sum(decline_count),0), 0) as offer_abandon_count,
  coalesce(sum(revenue), 0) as offer_revenue,
  coalesce(sum(profit), 0) as offer_profit
  FROM `ultracart_dw_linked.uc_storefront_upsell_offer_events` oe
  where oe.event_dts between '2024-05-01' and '2024-05-29' -- TODO: This is where the date range for the statistics is specified
  group by oe.storefront_upsell_offer_oid
),
offers_with_stats_rows as (
  select offer_rows.* except(path_order, storefront_upsell_offer_oid), offer_rows.storefront_upsell_offer_oid,
  coalesce(offer_stat_rows.offer_view_count, 0) as offer_view_count,
  coalesce(offer_stat_rows.offer_conversion_count, 0) as offer_conversion_count,
  coalesce(offer_stat_rows.offer_decline_count, 0) as offer_decline_count,
  coalesce(offer_stat_rows.offer_abandon_count, 0) as offer_abandon_count,
  coalesce(offer_stat_rows.offer_revenue, 0) as offer_revenue,
  coalesce(offer_stat_rows.offer_profit, 0) as offer_profit
  from offer_rows
  LEFT JOIN offer_stat_rows on offer_stat_rows.storefront_upsell_offer_oid = offer_rows.storefront_upsell_offer_oid
  ORDER BY offer_rows.merchant_id, offer_rows.host_name, offer_rows.path_order, offer_rows.variation_name
),
path_variation_rows as (
  SELECT sf.merchant_id, sf.host_name, path.path_order, path.name as path_name, variation.name as variation_name, variation_order, visibility_ordered_offer_oids
  FROM `ultracart_dw_linked.uc_storefront_upsell_paths` as path
  CROSS JOIN UNNEST(variations) as variation with offset variation_order
  INNER JOIN `ultracart_dw_linked.uc_storefronts` as sf on
            sf.storefront_oid = path.storefront_oid  
),
path_variation_offer_event_rows as (
  select * except (visibility_ordered_offer_oids),
  ARRAY (
    select as struct value as storefront_upsell_offer_oid, oe.* 
    from UNNEST(visibility_ordered_offer_oids)
    INNER JOIN `ultracart_dw_linked.uc_storefront_upsell_offer_events` oe on oe.storefront_upsell_offer_oid = value
    where oe.event_dts between '2024-05-01' and '2024-05-29' -- TODO: This is where the date range for the statistics is specified
  ) as offers
  from path_variation_rows
),
path_variation_stat_intermediate_rows as (
  select * except (offers),
  (
    select coalesce(sum(revenue), 0) FROM UNNEST(offers) 
  ) as revenue,
  (
    select coalesce(sum(profit), 0) FROM UNNEST(offers) 
  ) as profit,
  (
    select count(distinct(order_id)) FROM UNNEST(offers) where successful_charge > 0
  ) as conversions,
  (
    select count(distinct(session_id)) FROM UNNEST(offers)
  ) as visitors
  from path_variation_offer_event_rows  
),
path_variation_stat_rows as (
  select *, 
  ROUND(coalesce(SAFE_DIVIDE(revenue, visitors), 0), 2) as average_visitor_revenue,
  ROUND(coalesce(SAFE_DIVIDE(profit, visitors), 0), 2) as average_visitor_profit,
  ROUND(coalesce(SAFE_DIVIDE(conversions, visitors), 0), 5) as converion_rate,
  ROUND(coalesce(SAFE_DIVIDE(conversions, visitors), 0) * 100, 5) as converion_rate_percentage
  from path_variation_stat_intermediate_rows
  order by merchant_id, host_name, path_order, variation_order
)
-- Use one of these two final queries
select * from path_variation_stat_rows where visitors > 0 -- Only show path variations with traffic
--select * from offers_with_stats_rows where offer_stat_rows.offer_view_count > 0 -- Only show the offers with traffic