...
| Code Block | ||
|---|---|---|
| ||
-- Generate a date range of the prior starting 12 months
WITH date_range AS (
SELECT
DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AS start_date,
CURRENT_DATE() AS end_date
),
months as (
SELECT DATE_TRUNC(DATE_ADD(start_date, INTERVAL n MONTH), MONTH) as month
FROM date_range, UNNEST(GENERATE_ARRAY(0, DATE_DIFF(end_date, start_date, MONTH))) AS n
),
-- for each item, figure out the most recent history record before the start of the month
item_dc_months as (
select month, merchant_item_id, distribution_center_code, max(history_dts) as most_recent_history_dts
from months
LEFT JOIN `ultracart_dw.uc_item_inventory_history` iih on iih.history_dts <= months.month
group by merchant_item_id, distribution_center_code, month
),
-- for each item, figure out the inventory level associated with the most recent history record before the start of the month
item_dc_month_level as (
select item_dc_months.* except (most_recent_history_dts), iih.after_inventory_level as inventory_level
from item_dc_months
LEFT JOIN `ultracart_dw.uc_item_inventory_history` iih on iih.merchant_item_id = item_dc_months.merchant_item_id
and iih.distribution_center_code = item_dc_months.distribution_center_code
and iih.history_dts = item_dc_months.most_recent_history_dts
where item_dc_months.merchant_item_id is not null
),
-- figure out which DCs for a particular item had ANY activity in the time period
active_item_dc as (
select merchant_item_id, distribution_center_code, max(inventory_level) as max_inventory_level
from item_dc_month_level
group by merchant_item_id, distribution_center_code
having max_inventory_level > 0
),
-- filter down to active items and bring in the cogs. This is not stored historically so it's the active value as configured on the item currently
items_with_cogs as (
select item_dc_month_level.*,
COALESCE((
select cogs from UNNEST(shipping.distribution_centers) dc where dc.distribution_center_code = item_dc_month_level.distribution_center_code
), i.pricing.cogs) as cogs
from item_dc_month_level
right join active_item_dc on active_item_dc.merchant_item_id = item_dc_month_level.merchant_item_id and active_item_dc.distribution_center_code = item_dc_month_level.distribution_center_code
left join `ultracart_dw.uc_items` i on i.merchant_item_id = item_dc_month_level.merchant_item_id
)
-- Bring it all together and calculate the value of the inventory
select *, inventory_level * cogs as inventory_value
from items_with_cogs
order by month, merchant_item_id, distribution_center_code
|
Auto Order LTV calculation
| 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 |