Customer, LTV, and Loyalty Queries

Overview

These queries answer questions about the customer rather than the order. Some read the uc_customers profile table, which holds loyalty and wholesale pricing tier data; the rest derive a customer by grouping uc_orders on the billing email or its hash.

Email is available as plain text only at Level 3 (Medium) data access and above. At lower levels use billing.email_hash, which groups identically without exposing the address.

Customer LTV Modeling

For each customer email, returns the monthly and running totals of order count and revenue counted from that customer's first order. Only orders that reached Shipping Department or Completed Order are included.

WITH email_first_rows as (
  SELECT billing.email,
  MIN(payment.payment_dts) as first_order_dts
  FROM `my-data-warehouse.my_dataset.uc_orders`
  WHERE billing.email <> '' and payment.payment_dts is not null 
  and current_stage in ('Shipping Department', 'Completed Order')
  group by billing.email
),
order_rows as (
  select 
  email_first_rows.email, 
  first_order_dts, 
  o2.payment.payment_dts,  
  o2.summary.total.value as total, 
  case when o2.payment.payment_dts <> first_order_dts then o2.summary.total.value else 0 end as additional_total_revenue,
  DATE_DIFF(o2.payment.payment_dts, email_first_rows.first_order_dts, MONTH) as month
  from email_first_rows
  INNER JOIN `my-data-warehouse.my_dataset.uc_orders` o2 on o2.billing.email = email_first_rows.email
  and o2.payment.payment_dts is not null and o2.current_stage in ('Shipping Department', 'Completed Order')
  ORDER BY email_first_rows.email, month
),
month_rows as (
  select email, month, min(payment_dts) as payment_dts, sum(additional_total_revenue) as additional_total_revenue, 
  sum(total) as total, count(*) as order_count from order_rows group by email, month
),
month_ltv as (
  select email, month,
  SUM(order_count) OVER (current_month_window) AS month_order_count,
  SUM(order_count) OVER (months_since_first_order) AS cumuliative_order_count,
  SUM(additional_total_revenue) OVER (current_month_window) AS month_additional_total_revenue,
  SUM(additional_total_revenue) OVER (months_since_first_order) AS cumulative_additional_total_revenue,
  SUM(total) OVER (current_month_window) AS month_total,
  SUM(total) OVER (months_since_first_order) AS cumulative_total
  from month_rows
  WINDOW months_since_first_order AS (
    PARTITION BY email ORDER BY payment_dts asc
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ),
  current_month_window AS (
    PARTITION BY email ORDER BY payment_dts asc
    ROWS BETWEEN CURRENT ROW AND CURRENT ROW
  )
  order by email, month
)
select * from month_ltv

Extract a Marketing List from Order History

Returns the mailing list opt-in flag along with the full billing and shipping blocks for every order, ready to export as a marketing list.

SELECT order_id, marketing.mailing_list, billing.*, shipping.* FROM `my-data-warehouse.my_dataset.uc_orders`

Projected Future Revenue Based Upon Initial Item Purchased (Non-Auto Orders)

For customers whose first purchase landed in the last 12 months, groups them by the highest-priced item on that first order and reports the average additional revenue they went on to spend. Items with fewer than 50 first orders are dropped.

WITH email_rows AS (
  -- Find emails that have made their first purchase in the last 12 months.
  select billing.email_hash, MIN(payment.payment_dts) as first_order_dts FROM `my-data-warehouse.ultracart_dw.uc_orders` orders 
  group by billing.email_hash 
  having first_order_dts >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
),
item_rows as (
  SELECT 
    billing.email_hash, 
    order_id, 
    items.merchant_item_id,
    RANK() OVER ( PARTITION BY billing.email_hash ORDER BY order_id, items.cost.value desc ) AS rank,
    1 as initial_order_count
  FROM `my-data-warehouse.ultracart_dw.uc_orders` orders        
    CROSS JOIN UNNEST(items) as items
    LEFT JOIN email_rows on email_rows.email_hash = orders.billing.email_hash
  WHERE 
    orders.partition_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), WEEK)
    AND payment.payment_dts between DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
    AND orders.auto_order is null 
    AND orders.channel_partner is null
    AND orders.billing.email_hash <> '' 
    AND orders.billing.email_hash is not null
    AND payment.test_order = false 
    AND current_stage NOT LIKE "%Rejected%"
    AND payment.payment_status NOT LIKE "Declined"
    AND payment.payment_status NOT LIKE "Unprocessed"
    AND payment.payment_status NOT LIKE "Skipped"
    AND billing.email_hash in (select email_rows.email_hash from email_rows)
    order by order_id desc
)
SELECT 
  item_rows.merchant_item_id, 
  ROUND(AVG(COALESCE(orders.summary.subtotal.value, 0) - COALESCE(orders.summary.subtotal_discount.value, 0)), 2) as additional_ltv,
  COUNT(orders.order_id) as additional_order_count,
  SUM(item_rows.initial_order_count) as initial_order_count,  
FROM item_rows 
  LEFT JOIN `my-data-warehouse.ultracart_dw.uc_orders` orders 
    on orders.billing.email_hash = item_rows.email_hash and orders.order_id <> item_rows.order_id 
    and orders.partition_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), WEEK)
    AND orders.auto_order is null
  where rank = 1
  group by item_rows.merchant_item_id
  having initial_order_count >= 50
  order by item_rows.merchant_item_id

New Customers in a Specific Date Range By Email

Lists customers whose first paid order falls inside a date range, with the items they bought, by excluding every email address that had ordered before the range began.

SELECT o.billing.email, o.billing.first_name, o.billing.last_name, o.order_id, creation_dts,
(
  select ARRAY_AGG(oi.merchant_item_id) from UNNEST(items) oi where oi.kit_component is false
) as item_ids
FROM `ultracart_dw_medium.uc_orders` o
WHERE o.billing.email is not null and o.payment.payment_dts between PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2023-04-01 00:00:00') and PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2023-04-21 23:59:59') 
and o.billing.email not in (
  select o2.billing.email FROM `ultracart_dw_medium.uc_orders` o2 where o2.payment.payment_dts < PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2023-04-01 00:00:00') and o2.billing.email is not null
)
order by o.billing.email, o.creation_dts

Repeat Customer Rate

Splits the last month of orders into first-time and repeat customers by email address and reports the count and percentage of each. Auto order rebills are excluded.

WITH email_first_order_row as (
  SELECT 
    billing.email, 
    min(creation_dts) as first_order_creation_dts
  FROM `ultracart_dw_medium.uc_orders` 
  where billing.email is not null
  group by 1
),
order_repeat_customer_rows as (
  select 
    order_id,
    case when order_rows.creation_dts = email_first_order_row.first_order_creation_dts then false else true end as repeat_customer
  from `ultracart_dw_medium.uc_orders` order_rows
  left join email_first_order_row on order_rows.billing.email = email_first_order_row.email
  where creation_dts between CURRENT_DATETIME - INTERVAL 1 month and CURRENT_DATETIME -- date range you're analyzing
  and (order_rows.auto_order is null or order_rows.auto_order.original_order_id = order_id) -- exclude auto order rebills
)
select 
  repeat_customer, 
  count(*) as cnt, 
  SAFE_DIVIDE(count(*), (select count(*) from order_repeat_customer_rows)) * 100 as percentage
from order_repeat_customer_rows 
group by repeat_customer

Wholesale Customer Profile LTV

Returns each wholesale customer profile that has a pricing tier, with its contact and company details and lifetime value over all time, the last year, and the last three years.

SELECT 
email, 
COALESCE(billing[SAFE_OFFSET(0)].first_name, '') as first_name, 
COALESCE(billing[SAFE_OFFSET(0)].last_name, '') as last_name, 
COALESCE(billing[SAFE_OFFSET(0)].company, '') as company, 
pricing_tiers[SAFE_OFFSET(0)].name as pricing_tier,
coalesce((
  select SUM(o.summary.total.value) from UNNEST(orders) o
), 0) as ltv,
coalesce((
  select SUM(o.summary.total.value) from UNNEST(orders) o where o.creation_dts >= DATETIME_SUB(CURRENT_DATETIME(), interval 1 year)
), 0) as ltv_last_year,
coalesce((
  select SUM(o.summary.total.value) from UNNEST(orders) o where o.creation_dts >= DATETIME_SUB(CURRENT_DATETIME(), interval 3 year)
), 0) as ltv_3_year
FROM `ultracart_dw_medium.uc_customers` 
where pricing_tiers[SAFE_OFFSET(0)].name is not null
order by email

Loyalty Balance (Points based Program)

Returns the current and pending point balance and the redemption count for every customer profile on a points-based loyalty program.

SELECT email, loyalty.current_points, loyalty.pending_points, ARRAY_LENGTH(loyalty.redemptions) as number_of_redemptions
FROM `ultracart_dw_medium.uc_customers` 
where email is not null
order by email

Loyalty Balance (Store Credit Based Program)

Returns the store credit balance and the redemption count for every customer profile on a store-credit loyalty program, by joining the profile to its internal gift certificate.

SELECT cp.email, remaining_balance as store_credit_balance,
(
  select count(*) from UNNEST(gc.ledger_entries) where reference_order_id is not null
) as number_of_redemptions
FROM `ultracart_dw_medium.uc_customers` cp
RIGHT JOIN `ultracart_dw_medium.uc_gift_certificates` gc on gc.gift_certificate_oid = loyalty.internal_gift_certificate_oid
where cp.email is not null and loyalty.internal_gift_certificate_oid is not null
order by cp.email