...
| Code Block |
|---|
|
SELECT
original_order.billing.email,
-- Subscription start date
original_order.creation_dts,
-- How many rebills occurred
ARRAY_LENGTH(rebill_orders) as rebill_count,
-- Is it still active?
enabled as still_active,
-- Calculate the LTV of this auto order based upon total
original_order.summary.total.value +
coalesce((
select sum(r.summary.total.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
), 0)
as ltv_total,
-- Calculate the LTV of this auto order based upon total after refunds
original_order.summary.total.value +
coalesce((
select sum(r.summary.total.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
), 0) -
coalesce(original_order.summary.total_refunded.value, 0) -
coalesce((
select sum(r.summary.total_refunded.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null and r.summary.total_refunded.value is not null
), 0)
as ltv_total_after_refunds,
-- Calculate the LTV of this auto order based upon subtotal after discount
(original_order.summary.subtotal.value - original_order.summary.subtotal_discount.value) +
coalesce((
select sum(r.summary.subtotal.value - r.summary.subtotal_discount.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
), 0)
as ltv_subtotal_after_discount,
-- Calculate the LTV of this auto order based upon subtotal after discount and refunds
(original_order.summary.subtotal.value - original_order.summary.subtotal_discount.value) +
coalesce((
select sum(r.summary.subtotal.value - r.summary.subtotal_discount.value) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null
), 0) -
(coalesce(original_order.summary.subtotal_refunded.value, 0) - coalesce(original_order.summary.subtotal_discount_refunded.value, 0)) -
coalesce((
select sum(coalesce(r.summary.subtotal_refunded.value, 0) - coalesce(r.summary.subtotal_discount_refunded.value, 0)) from UNNEST(rebill_orders) r where r.payment.payment_dts is not null and r.summary.total_refunded.value is not null
), 0)
as ltv_subtotal_after_discount_and_refunds
FROM `ultracart_dw_medium.uc_auto_orders`
where
-- Standard file of test orders
original_order.payment.test_order is false
-- Make sure the order has been paid for
and original_order.payment.payment_dts is not null
-- Filter out orders placed by UC users
AND original_order.internal.placed_by_user is null
-- Make sure the email is populated
and original_order.billing.email is not null
-- Grab the starting orders on this date range
and original_order.creation_dts BETWEEN '2023-07-23'AND '2023-10-24'
order by email |
Upsell Path Statistics
| Code Block |
|---|
|
-- 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 |
Related Documentation
https://towardsdatascience.com/how-to-monitor-repeat-purchase-behavior-using-google-data-studio-and-bigquery-c2b5adbe8ebb