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_screen_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). It reads ultracart_dw_medium or higher, because event parameter values are hashed in the lower access levels.

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.ultracart_dw_medium.uc_screen_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

StoreFront experiment funnel analysis

The StoreFront Experiment Statistics query above reports the same top-line numbers as the Experiments screen. The three queries below go further: an experiment header with the p-value and the sessions still needed for 95% confidence, a funnel by variation that includes every upsell and downsell step with gross profit, and a mobile vs desktop split with the order-page item mix.

All three leave out sessions that saw more than one experiment (a shopper can enter experiments on several StoreFronts in one visit, because the analytics visitor ID covers the whole account) and orders placed by your own staff. Their counts can therefore differ slightly from the Experiments screen.

Notes for running them:

• Each script starts with DECLARE lines for the experiment ID, date range, and device. Change them and run the whole script.
• Always keep the date parameters. uc_analytics_sessions is partitioned by date, so the dates control how much data the query scans.
• Queries 2 and 3 take the experiment's 32-character id from uc_storefront_experiments.id, not the numeric storefront_experiment_oid. Query 1 lists both.
• Visits on the order page are sessions that were shown a variation. Visits on an upsell or downsell step are the number of times that offer was shown. Orders on an upsell step are acceptances.
• Order-page revenue and profit are the order total minus accepted upsells, so they include the front-end item's shipping and any tax. The "Order page cell" rows show each front-end item's own price and profit. A free-trial item shows negative gross profit, because it has a cost of goods sold (COGS) and a $0 price.
• Gross profit is based on the COGS set on each item, pricing.cogs in uc_items. It is not based on the item's cost, which in UltraCart is the selling price. Items with no COGS value overstate profit.
• Query 2 hides upsell steps with fewer than report_min_step_views views, which removes stray views from other upsell paths.
• Query 3 counts two front-end item IDs (ITEM-A, ITEM-B). Replace them with the items in your test.

Experiment header: storefront, page, dates, confidence

Reports the storefront, page, dates, confidence, and the engine's own per-variation totals for one or more experiments, by OID.

-- Experiment header: storefront, page, dates, confidence and the engine's own per-variation totals.
SELECT
  e.storefront_experiment_oid,
  e.id AS experiment_id,
  e.name,
  s.host_name AS storefront_host,
  e.uri AS page_url,
  p.storefront_page_oid AS page_id,
  e.experiment_type,
  e.status,
  e.start_dts,
  e.end_dts,
  e.equal_weighting,
  e.objective,
  e.optimization_type,
  e.p_value,
  e.p95_sessions_needed,
  v.variation_number,
  v.variation_name,
  v.winner,
  v.traffic_percentage,
  v.session_count,
  v.order_count,
  v.revenue,
  v.conversion_rate,
  v.average_order_value
FROM `my-data-warehouse.ultracart_dw.uc_storefront_experiments` e
CROSS JOIN UNNEST(e.variations) v
LEFT JOIN `my-data-warehouse.ultracart_dw.uc_storefronts` s USING (storefront_oid)
LEFT JOIN `my-data-warehouse.ultracart_dw.uc_storefront_pages` p
  ON p.storefront_oid = e.storefront_oid
  AND p.path = REGEXP_REPLACE(e.uri, r'\.html$', '/')
WHERE e.storefront_experiment_oid IN (12345, 12346)  -- replace with your experiment OIDs
ORDER BY e.storefront_experiment_oid, v.variation_number;

Funnel by variation

Reports visits, orders, conversion, revenue, and gross profit per variation, broken out for the order page and every upsell and downsell step that met the minimum view threshold.

-- Funnel by variation for one StoreFront experiment.
-- Set the parameters, then run the whole script.
DECLARE report_experiment_id STRING DEFAULT 'your-32-character-experiment-id';  -- uc_storefront_experiments.id
DECLARE report_start DATE DEFAULT '2026-01-01';
DECLARE report_end DATE DEFAULT '2026-01-31';
DECLARE report_device STRING DEFAULT 'all';  -- 'all', 'mobile' or 'desktop'
DECLARE report_min_step_views INT64 DEFAULT 10;  -- hide upsell steps from other paths with only a few views

WITH sessions AS (
  SELECT
    client_session_oid,
    order_id,
    (SELECT MAX(h.experiment.variation) FROM UNNEST(hits) h
      WHERE LOWER(h.experiment.experiment_id) = LOWER(report_experiment_id)) AS variation,
    (SELECT COUNT(DISTINCT LOWER(h.experiment.experiment_id)) FROM UNNEST(hits) h
      WHERE h.experiment.experiment_id IS NOT NULL) AS experiments_in_session,
    (SELECT LOGICAL_OR(REGEXP_CONTAINS(h.session_start.user_agent, r'Mobi|Android|iPhone|iPad')) FROM UNNEST(hits) h) AS mobile,
    EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE h.ecommerce_placed_order.placed_by_user IS NOT NULL) AS staff_placed,
    (SELECT SUM(h.ecommerce_placed_order.total) FROM UNNEST(hits) h) AS order_revenue,
    (SELECT SUM(h.ecommerce_placed_order.profit) FROM UNNEST(hits) h) AS order_profit,
    ARRAY(SELECT AS STRUCT h.ecommerce_ordered_product.item_id, h.ecommerce_ordered_product.quantity,
            h.ecommerce_ordered_product.unit_cost, h.ecommerce_ordered_product.profit
          FROM UNNEST(hits) h WHERE h.ecommerce_ordered_product.item_id IS NOT NULL) AS items
  FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions`
  WHERE partition_date BETWEEN report_start AND report_end
),
-- Sessions in this experiment only: one experiment per session, no staff-placed orders, device filter.
exp AS (
  SELECT * FROM sessions
  WHERE variation IS NOT NULL
    AND experiments_in_session = 1
    AND NOT staff_placed
    AND (report_device = 'all' OR (report_device = 'mobile' AND mobile) OR (report_device = 'desktop' AND NOT mobile))
),
-- One row per offer shown. Accepted = the event carries an item.
upsell_events AS (
  SELECT e.order_id, o.name AS offer, o.path_name,
    SUM(e.view_count) AS views,
    COUNTIF(e.item_id IS NOT NULL) AS takes,
    SUM(IF(e.item_id IS NOT NULL, e.revenue, 0)) AS revenue,
    SUM(IF(e.item_id IS NOT NULL, e.profit, 0)) AS profit
  FROM `my-data-warehouse.ultracart_dw.uc_storefront_upsell_offer_events` e
  LEFT JOIN `my-data-warehouse.ultracart_dw.uc_storefront_upsell_offers` o USING (storefront_upsell_offer_oid)
  WHERE e.partition_date BETWEEN report_start AND DATE_ADD(report_end, INTERVAL 1 DAY)
  GROUP BY 1, 2, 3
),
upsell_by_order AS (
  SELECT order_id, SUM(revenue) AS revenue, SUM(profit) AS profit, SUM(takes) AS takes
  FROM upsell_events GROUP BY 1
),
visits AS (
  SELECT variation, COUNT(*) AS visits FROM exp GROUP BY 1
),
order_page AS (
  SELECT x.variation,
    COUNT(DISTINCT x.order_id) AS orders,
    SUM(x.order_revenue) - SUM(COALESCE(u.revenue, 0)) AS revenue,
    SUM(x.order_profit) - SUM(COALESCE(u.profit, 0)) AS profit,
    SUM(x.order_revenue) AS total_revenue,
    SUM(x.order_profit) AS total_profit,
    SUM(COALESCE(u.revenue, 0)) AS upsell_revenue
  FROM exp x LEFT JOIN upsell_by_order u USING (order_id)
  WHERE x.order_id IS NOT NULL
  GROUP BY 1
),
-- Order-page offer cells: every item that was not added by an upsell.
cells AS (
  SELECT x.variation, i.item_id,
    COUNT(DISTINCT x.order_id) AS orders,
    SUM(i.quantity * i.unit_cost) AS revenue,
    SUM(i.profit) AS profit
  FROM exp x, UNNEST(x.items) i
  WHERE x.order_id IS NOT NULL
    AND NOT EXISTS (SELECT 1 FROM `my-data-warehouse.ultracart_dw.uc_storefront_upsell_offer_events` e
                    WHERE e.partition_date BETWEEN report_start AND DATE_ADD(report_end, INTERVAL 1 DAY)
                      AND e.order_id = x.order_id AND e.item_id = i.item_id)
  GROUP BY 1, 2
),
steps AS (
  SELECT x.variation, u.path_name, u.offer,
    SUM(u.views) AS visits, SUM(u.takes) AS orders, SUM(u.revenue) AS revenue, SUM(u.profit) AS profit
  FROM exp x JOIN upsell_events u USING (order_id)
  GROUP BY 1, 2, 3
  HAVING SUM(u.views) >= report_min_step_views
),
funnel_rows AS (
  SELECT variation, 1 AS sort_group, 0 AS sort_value, 'Order page' AS step, CAST(NULL AS STRING) AS detail,
    v.visits, o.orders, o.revenue, o.profit
  FROM visits v LEFT JOIN order_page o USING (variation)
  UNION ALL
  SELECT variation, 2, orders, 'Order page cell', item_id, NULL, orders, revenue, profit
  FROM cells
  UNION ALL
  SELECT variation, 3, visits, offer, path_name, visits, orders, revenue, profit
  FROM steps
  UNION ALL
  SELECT variation, 4, 0, 'Totals', NULL, v.visits, o.orders, o.total_revenue, o.total_profit
  FROM visits v LEFT JOIN order_page o USING (variation)
)
SELECT
  r.variation,
  r.step,
  r.detail,
  r.visits,
  r.orders,
  ROUND(SAFE_DIVIDE(r.orders, r.visits) * 100, 2) AS conversion_pct,
  ROUND(r.revenue, 2) AS revenue,
  ROUND(r.profit, 2) AS gross_profit,
  IF(r.step IN ('Order page', 'Totals') OR r.sort_group = 3,
     ROUND(SAFE_DIVIDE(r.profit, t.total_profit) * 100, 2), NULL) AS gp_contribution_pct,
  IF(r.step = 'Totals', ROUND(SAFE_DIVIDE(t.total_profit, t.visits), 2), NULL) AS gp_per_visitor,
  IF(r.step = 'Totals', ROUND(SAFE_DIVIDE(t.total_revenue, t.orders), 2), NULL) AS aov,
  IF(r.step = 'Totals', ROUND(t.upsell_revenue, 2), NULL) AS upsell_revenue
FROM funnel_rows r
JOIN (SELECT v.variation, v.visits, o.orders, o.total_revenue, o.total_profit, o.upsell_revenue
      FROM visits v LEFT JOIN order_page o USING (variation)) t USING (variation)
ORDER BY r.variation, r.sort_group, r.sort_value DESC, r.step;

Mobile vs desktop and order-page item mix by variation

Splits visits, orders, conversion, revenue, and gross profit by device for one experiment, and counts how often each of two front-end items appears on the order.

-- Mobile vs desktop, and order-page offer mix, by variation for one experiment.
DECLARE report_experiment_id STRING DEFAULT 'your-32-character-experiment-id';
DECLARE report_start DATE DEFAULT '2026-01-01';
DECLARE report_end DATE DEFAULT '2026-01-31';

WITH sessions AS (
  SELECT
    order_id,
    (SELECT MAX(h.experiment.variation) FROM UNNEST(hits) h
      WHERE LOWER(h.experiment.experiment_id) = LOWER(report_experiment_id)) AS variation,
    (SELECT COUNT(DISTINCT LOWER(h.experiment.experiment_id)) FROM UNNEST(hits) h
      WHERE h.experiment.experiment_id IS NOT NULL) AS experiments_in_session,
    (SELECT LOGICAL_OR(REGEXP_CONTAINS(h.session_start.user_agent, r'Mobi|Android|iPhone|iPad')) FROM UNNEST(hits) h) AS mobile,
    EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE h.ecommerce_placed_order.placed_by_user IS NOT NULL) AS staff_placed,
    (SELECT SUM(h.ecommerce_placed_order.total) FROM UNNEST(hits) h) AS order_revenue,
    (SELECT SUM(h.ecommerce_placed_order.profit) FROM UNNEST(hits) h) AS order_profit,
    ARRAY(SELECT DISTINCT h.ecommerce_ordered_product.item_id FROM UNNEST(hits) h
          WHERE h.ecommerce_ordered_product.item_id IS NOT NULL) AS item_ids
  FROM `my-data-warehouse.ultracart_dw.uc_analytics_sessions`
  WHERE partition_date BETWEEN report_start AND report_end
)
SELECT
  variation,
  CASE WHEN mobile THEN 'mobile' WHEN mobile IS NULL THEN 'unknown' ELSE 'desktop' END AS device,
  COUNT(*) AS visits,
  COUNTIF(order_id IS NOT NULL) AS orders,
  ROUND(SAFE_DIVIDE(COUNTIF(order_id IS NOT NULL), COUNT(*)) * 100, 2) AS conversion_pct,
  ROUND(SUM(order_revenue), 2) AS revenue,
  ROUND(SUM(order_profit), 2) AS gross_profit,
  ROUND(SAFE_DIVIDE(SUM(order_revenue), COUNTIF(order_id IS NOT NULL)), 2) AS aov,
  ROUND(SAFE_DIVIDE(SUM(order_profit), COUNT(*)), 2) AS gp_per_visitor,
  COUNTIF('ITEM-A' IN UNNEST(item_ids)) AS item_a_orders,
  COUNTIF('ITEM-B' IN UNNEST(item_ids)) AS item_b_orders
FROM sessions
WHERE variation IS NOT NULL AND experiments_in_session = 1 AND NOT staff_placed
GROUP BY 1, 2
ORDER BY 1, 2;

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