Reference

Auto Order Queries

Overview

These queries read the uc_auto_orders table. Each row holds the original order, the array of subscription items with their schedules, and the array of rebill orders placed so far, so a single auto order row already contains the whole subscription history.

Adding Calculations to Auto Orders

The following query shows how to add:

Count of the number of rebills
Life time value of the customer
Average order value of the customer
How long the customer was active before the auto order was canceled or disabled

You can run this query to see the results. Then save this query as a view named “uc_auto_orders_with_stats”.

SELECT 
-- Count number of rebills
ARRAY_LENGTH(rebill_orders) as number_of_rebills, 
-- Life time value by adding original order plus rebill values
COALESCE(original_order.summary.total.value, 0) - COALESCE(original_order.summary.total_refunded.value, 0) + 
COALESCE((
    select SUM(COALESCE(r.summary.total.value, 0) - COALESCE(r.summary.total_refunded.value, 0)) from UNNEST(rebill_orders) as r
), 0) as life_time_value,
-- Average order value by adding original order plus rebill values then dividing by total number of orders
ROUND(
    COALESCE(original_order.summary.total.value, 0) - COALESCE(original_order.summary.total_refunded.value, 0) + 
    COALESCE((
        select SUM(COALESCE(r.summary.total.value, 0) - COALESCE(r.summary.total_refunded.value, 0)) from UNNEST(rebill_orders) as r
    ), 0) / (1 + ARRAY_LENGTH(rebill_orders)) 
,2) as average_order_value,
-- How long did the auto order last?  Value will be null if still active
DATE_DIFF(COALESCE(canceled_dts, disabled_dts), original_order.creation_dts, DAY) as duration_days,
-- Retain all the other columns on uc_auto_orders
*
FROM `my-data-warehouse.my_dataset.uc_auto_orders`

Active Auto Order Next Rebill

Lists every enabled auto order with its next scheduled shipment, the customer's billing and shipping contact details, and the most recent failure reason. Rows come back in shipment-date order, so the result doubles as a rebill work queue.

SELECT 
auto_order_oid,
auto_order_code,
status,
enabled,    
original_order.order_id as original_order_id,
orders.current_stage as original_order_current_stage,    
original_order.billing.first_name,
original_order.billing.last_name,
original_order.billing.email,
original_order.shipping.day_phone_e164,
original_order.shipping.state_region,
original_order.shipping.postal_code,
original_order.shipping.country_code,
item_offset,
future_schedules.item_id as future_schedule_item_id,
future_schedules.shipment_dts as future_schedule_shipment_dts,
future_schedules.unit_cost as future_schedule_unit_cost,
future_schedules.rebill_count as future_schedule_rebill_count,
REGEXP_EXTRACT(failure_reason, '^[^\n]+') as failure_reason,    
FROM `my-data-warehouse.my_dataset.uc_auto_orders` as auto_orders
JOIN `my-data-warehouse.my_dataset.uc_orders` as orders on orders.order_id = auto_orders.original_order_id,
UNNEST(auto_orders.items) as items WITH OFFSET AS item_offset,
UNNEST(items.future_schedules) as future_schedules WITH OFFSET AS future_schedule_offset
WHERE enabled = true
and future_schedule_offset = 0
and orders.payment.payment_dts is not null
and orders.payment.payment_method <> 'Quote Request'
and orders.current_stage <> 'Rejected'
and (items.no_order_after_dts is null or items.no_order_after_dts >= current_date())
ORDER BY future_schedules.shipment_dts ASC

Auto Orders with a Pre-shipment Notice More Than a Week Before Shipment

Finds enabled auto orders whose pre-shipment notice is scheduled more than seven days ahead of the shipment itself, which usually means the notice offset is misconfigured.

SELECT ao.original_order_id, DATE_DIFF(item.next_preshipment_notice_dts, item.next_shipment_dts, DAY) as days_diff
FROM `my-data-warehouse.my_dataset.uc_auto_orders` as ao
CROSS JOIN UNNEST(items) as item
WHERE enabled = True and item.next_preshipment_notice_dts is not null 
and DATE_DIFF(item.next_preshipment_notice_dts, item.next_shipment_dts, DAY) < -7

Auto Orders with Missed Pre-shipment Notices

Finds enabled auto orders whose pre-shipment notice came due in the past but was never sent.

SELECT original_order_id, aoi.original_item_id, aoi.next_preshipment_notice_dts, aoi.preshipment_notice_sent, aoi.next_shipment_dts
FROM `my-data-warehouse.my_dataset.uc_auto_orders` ao
CROSS JOIN UNNEST(items) aoi
where aoi.preshipment_notice_sent = false 
and aoi.next_preshipment_notice_dts is not null 
and aoi.next_preshipment_notice_dts < aoi.next_shipment_dts 
and aoi.next_preshipment_notice_dts < CURRENT_DATETIME()
and (aoi.no_order_after_dts is null or aoi.no_order_after_dts > CURRENT_DATETIME())
and ao.enabled is true

Auto Order Logs Messages After Pre-shipment Notice and Before Shipment Should Take Place

For auto orders whose pre-shipment notice has been sent, returns the log entries written after that notice went out. Use it to see what happened between the notice and the shipment.

SELECT original_order_id, aoi.original_item_id, aoi.next_preshipment_notice_dts, aoi.preshipment_notice_sent, aoi.next_shipment_dts, 
ARRAY(
    select AS STRUCT log_dts, log_message 
    from UNNEST(ao.logs)
    where log_dts > aoi.next_preshipment_notice_dts
) as logs
FROM `my-data-warehouse.my_dataset.uc_auto_orders` ao
CROSS JOIN UNNEST(items) aoi
where aoi.preshipment_notice_sent = true 
and aoi.next_preshipment_notice_dts is not null 
and aoi.next_preshipment_notice_dts < aoi.next_shipment_dts 
and aoi.next_preshipment_notice_dts < CURRENT_DATETIME()
and (aoi.no_order_after_dts is null or aoi.no_order_after_dts between DATETIME_TRUNC(CURRENT_DATETIME(), DAY) AND CURRENT_DATETIME())
and ao.enabled is true

Auto Order Future Predictions

Projects the next 90 days of scheduled auto order shipments into daily revenue and quantity per item. Swap the final select for future_item_month_rows or future_email_rows to get the same projection by month or by customer.

WITH future_shipment_rows as (
  SELECT auto_order_code, fs.item_id, fs.unit_cost, coalesce(i.arbitrary_quantity, i.original_quantity) as qty, fs.shipment_dts, 
  original_order.billing.email_hash
  FROM `my-data-warehouse.my_dataset.uc_auto_orders` 
  CROSS JOIN UNNEST(items) as i
  CROSS JOIN UNNEST(i.future_schedules) fs
  where fs.shipment_dts < DATETIME_ADD(CURRENT_DATE(), INTERVAL 90 day) and enabled is true
),
future_email_rows as (
  select email_hash, sum(unit_cost * qty) as future_item_revenue
  from future_shipment_rows
  group by email_hash
  order by email_hash
),
future_item_day_rows as (
  select item_id,DATETIME_TRUNC(shipment_dts, day) as day, sum(qty) as future_quantity, sum(unit_cost * qty) as future_item_revenue
  from future_shipment_rows
  group by item_id, day
  order by item_id, day
),
future_item_month_rows as (
  select item_id,DATETIME_TRUNC(shipment_dts, month) as month, sum(qty) as future_quantity, sum(unit_cost * qty) as future_item_revenue
  from future_shipment_rows
  group by item_id, month
  order by item_id, month
)
select * from future_item_day_rows

Auto Order LTV Calculation

Returns one row per auto order started in a date range with its rebill count, whether it is still active, and lifetime value calculated four ways: on total, on total net of refunds, on subtotal after discounts, and on subtotal after discounts and refunds.

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

Auto Order Percentages with CC vs PayPal 30 Day Moving Averages

Tracks what share of new orders start an auto order and how that share splits between credit card and PayPal, reporting 30-day moving averages alongside the daily figures. Rebills and channel partner orders are excluded.

with order_rows as (
  SELECT order_id, auto_order.original_order_id, 
  DATETIME_TRUNC(creation_dts, day) as creation_date,
  payment.payment_method as payment_method
  FROM `ultracart_dw.uc_orders` 
  WHERE creation_dts >= DATETIME_SUB(CURRENT_DATETIME(), interval 12 month)
  and (auto_order.original_order_id is null or auto_order.original_order_id = order_id) -- filter out rebills
  and channel_partner is null -- filter out channel partners
),
stat_rows as (
  select 
  creation_date,
  case when original_order_id is not null then 1 else 0 end as auto_order_count,
  case when original_order_id is not null and payment_method = 'Credit Card' then 1 else 0 end as cc_auto_order_count,
  case when original_order_id is not null and payment_method = 'PayPal' then 1 else 0 end as paypal_auto_order_count,
  case when original_order_id is  null then 1 else 0 end as regular_order_count,
  1 as order_count
  from order_rows
),
rolled_up_rows as (
  select 
  creation_date,
  sum(auto_order_count) as auto_order_count,
  sum(regular_order_count) as regular_order_count,
  sum(order_count) as order_count,
  sum(cc_auto_order_count) as cc_auto_order_count,
  sum(paypal_auto_order_count) as paypal_auto_order_count
  from stat_rows
  group by creation_date order by creation_date asc
),
rolled_up_30d_rows as (
SELECT
  *,
  SUM(regular_order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS regular_order_count_30d,
  SUM(order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS order_count_30d,
  SUM(auto_order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS auto_order_count_30d,
  SUM(cc_auto_order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS cc_auto_order_count_30d,
  SUM(paypal_auto_order_count) OVER (ORDER BY creation_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS paypal_auto_order_count_30d
  FROM rolled_up_rows
  ORDER BY creation_date
)
select *,
ROUND(SAFE_DIVIDE(auto_order_count, order_count) * 100, 2) as auto_order_percentage,
ROUND(SAFE_DIVIDE(cc_auto_order_count, auto_order_count) * 100, 2) as cc_auto_order_percentage,
ROUND(SAFE_DIVIDE(paypal_auto_order_count, auto_order_count) * 100, 2) as paypal_auto_order_percentage,
ROUND(SAFE_DIVIDE(auto_order_count_30d, order_count_30d) * 100, 2) as auto_order_percentage_30dma,
ROUND(SAFE_DIVIDE(cc_auto_order_count_30d, auto_order_count_30d) * 100, 2) as cc_auto_order_percentage_30dma,
ROUND(SAFE_DIVIDE(paypal_auto_order_count_30d, auto_order_count_30d) * 100, 2) as paypal_auto_order_percentage_30dma
from rolled_up_30d_rows order by creation_date desc

Was this page helpful?

Overview​

Adding Calculations to Auto Orders​

Active Auto Order Next Rebill​

Auto Orders with a Pre-shipment Notice More Than a Week Before Shipment​

Auto Orders with Missed Pre-shipment Notices​

Auto Order Logs Messages After Pre-shipment Notice and Before Shipment Should Take Place​

Auto Order Future Predictions​

Auto Order LTV Calculation​

Auto Order Percentages with CC vs PayPal 30 Day Moving Averages​