Orders & Revenue

Use the same query standards from the SQL Query Library overview: is_order_sm_valid = TRUE for order analyses, sm_store_id scoping for multi-store setups, and your_project placeholders.

Average order value (AOV) by marketing channel (last 30 days)

What you’ll learn: Which marketing channels drive higher-value orders. Channels with high AOV may warrant more budget even if volume is lower; low-AOV channels might need different offer strategies.

-- Assumptions: timeframe=last_30_days | metric=AOV=SUM(order_net_revenue)/orders | grain=sm_utm_source_medium | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS marketing_channel,
    sm_order_key,
    sm_customer_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  marketing_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  COUNT(DISTINCT sm_customer_key) AS customers,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS aov
FROM base
GROUP BY 1
HAVING orders >= 50
ORDER BY aov DESC
LIMIT 50;

Revenue in the last 30 days from customers who have ever had a subscription

What you’ll learn: How much of your recent revenue comes from customers with subscription history (even if they’re not currently subscribed). Helps quantify the long-term value of your subscription program.

-- Assumptions: timeframe=last_30_days | metric=net_revenue=SUM(order_net_revenue) | grain=overall | scope=customers_with_any_subscription_history
WITH subscription_customers AS (
  SELECT DISTINCT
    sm_customer_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND is_subscription_order = TRUE
    AND sm_customer_key IS NOT NULL
),
last_30_valid_orders AS (
  SELECT
    sm_order_key,
    sm_customer_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND sm_customer_key IS NOT NULL
)
SELECT
  SUM(CASE WHEN sc.sm_customer_key IS NOT NULL THEN o.order_net_revenue ELSE 0 END) AS revenue_from_customers_with_subscription_history,
  SUM(o.order_net_revenue) AS net_revenue_last_30_days,
  SAFE_DIVIDE(
    SUM(CASE WHEN sc.sm_customer_key IS NOT NULL THEN o.order_net_revenue ELSE 0 END),
    NULLIF(SUM(o.order_net_revenue), 0)
  ) AS pct_of_revenue_from_subscription_history_customers
FROM last_30_valid_orders o
LEFT JOIN subscription_customers sc
  ON o.sm_customer_key = sc.sm_customer_key;

Refund rate by marketing channel (last 90 days)

What you’ll learn: Which marketing channels have higher refund rates—by order count and by revenue. High refund rates may indicate mismatched expectations from certain ad campaigns or audiences.

-- Assumptions: timeframe=last_90_days | metric=refund_rate | grain=sm_utm_source_medium | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS marketing_channel,
    order_total_refunds,
    order_net_revenue_before_refunds,
    sm_order_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  marketing_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  COUNTIF(ABS(order_total_refunds) > 0) AS refunded_orders,
  SAFE_DIVIDE(COUNTIF(ABS(order_total_refunds) > 0), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS refund_rate_orders,
  ABS(SUM(order_total_refunds)) AS refund_amount,
  SUM(order_net_revenue_before_refunds) AS revenue_before_refunds,
  SAFE_DIVIDE(ABS(SUM(order_total_refunds)), NULLIF(SUM(order_net_revenue_before_refunds), 0)) AS refund_rate_revenue
FROM base
GROUP BY 1
HAVING orders >= 50
ORDER BY refund_rate_revenue DESC
LIMIT 50;

Distribution of orders and revenue by sales channel (last 30 days)

What you’ll learn: How your orders and revenue are distributed across different sales channels (online, POS, wholesale, etc.). Useful for understanding channel mix and identifying growth opportunities.

-- Assumptions: timeframe=last_30_days | metric=orders+net_revenue+share | grain=sm_channel | scope=valid_orders_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS sm_channel,
  COUNT(*) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(COUNT(*), NULLIF(SUM(COUNT(*)) OVER (), 0)) AS pct_orders,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(SUM(SUM(order_net_revenue)) OVER (), 0)) AS pct_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_cancelled_at IS NULL
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY orders DESC;

Return rate by month (Loop Returns)

What you’ll learn: The share of valid Shopify orders that had a return, by month. Loop Returns integration required. Joins fct_returns against obt_orders for the full order population denominator.

-- Assumptions: source=Shopify | grain=month | metric=return_rate=return_orders/valid_orders | filter=is_order_matched
WITH all_orders AS (
  SELECT
    FORMAT_DATE('%Y-%m', DATE(order_processed_at_local_datetime)) AS month,
    COUNT(DISTINCT sm_order_key) AS total_valid_orders
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND LOWER(source_system) = 'shopify'
  GROUP BY 1
),
returned_orders AS (
  SELECT
    FORMAT_DATE('%Y-%m', DATE(order_processed_at_local_datetime)) AS month,
    COUNT(DISTINCT sm_order_key) AS return_orders,
    COUNT(DISTINCT CASE WHEN has_exchange THEN sm_order_key END) AS exchange_orders
  FROM `your_project.sm_transformed_v2.fct_returns`
  WHERE is_order_matched = TRUE
  GROUP BY 1
)
SELECT
  a.month,
  a.total_valid_orders,
  COALESCE(r.return_orders, 0) AS return_orders,
  COALESCE(r.exchange_orders, 0) AS exchange_orders,
  SAFE_DIVIDE(COALESCE(r.return_orders, 0), a.total_valid_orders) AS return_rate
FROM all_orders AS a
LEFT JOIN returned_orders AS r USING (month)
ORDER BY 1 DESC;

Net return cost by channel (Loop Returns)

What you’ll learn: Which marketing channels drive the highest return costs. Helps identify whether certain acquisition channels attract customers with higher return propensity. Loop Returns integration required.

-- Assumptions: source=Shopify | grain=sm_channel | metric=net_return_cost | filter=is_order_matched
SELECT
  sm_channel,
  COUNT(DISTINCT sm_order_key) AS return_orders,
  SUM(net_return_cost) AS total_net_return_cost,
  SUM(refund_amount) AS total_refund_amount,
  SUM(gift_card_amount) AS total_gift_card_amount,
  SUM(upsell_amount) AS total_upsell_amount,
  SAFE_DIVIDE(SUM(net_return_cost), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS avg_net_return_cost_per_order
FROM `your_project.sm_transformed_v2.fct_returns`
WHERE is_order_matched = TRUE
GROUP BY 1
ORDER BY total_net_return_cost DESC;

Return outcome breakdown (Loop Returns)

What you’ll learn: How customers resolve returns — refund, exchange, store credit, or upsell. Exchange-heavy return programs retain more revenue than refund-heavy ones. Loop Returns integration required.

-- Assumptions: source=Shopify | grain=outcome_type | metric=return_count | filter=is_order_matched
SELECT
  CASE
    WHEN has_refund AND has_exchange THEN 'refund + exchange'
    WHEN has_refund THEN 'refund only'
    WHEN has_exchange THEN 'exchange only'
    WHEN has_credit THEN 'store credit'
    WHEN has_upsell THEN 'upsell'
    ELSE 'other'
  END AS return_outcome_bucket,
  COUNT(*) AS returns,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(net_return_cost) AS total_net_return_cost
FROM `your_project.sm_transformed_v2.fct_returns`
WHERE is_order_matched = TRUE
GROUP BY 1
ORDER BY returns DESC;

Return-order exposure by product (Loop Returns)

What you’ll learn: Which products appear most often on orders that later had a Loop return. This is an order-level exposure view, not returned-item attribution; use it to find products worth deeper returns review.

-- Assumptions: source=Shopify | grain=product_variant | metric=return_order_rate | filter=valid_orders+is_order_matched
WITH valid_product_orders AS (
  SELECT
    sm_store_id,
    sm_product_variant_key,
    product_title,
    product_variant_title,
    sku,
    sm_order_key,
    SUM(order_line_net_quantity) AS units_sold
  FROM `your_project.sm_transformed_v2.obt_order_lines`
  WHERE is_order_sm_valid = TRUE
    AND LOWER(source_system) = 'shopify'
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_product_variant_key IS NOT NULL
  GROUP BY 1, 2, 3, 4, 5, 6
),
returned_orders AS (
  SELECT DISTINCT
    sm_store_id,
    sm_order_key
  FROM `your_project.sm_transformed_v2.fct_returns`
  WHERE is_order_matched = TRUE
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  v.product_title,
  v.product_variant_title,
  v.sku,
  COUNT(DISTINCT v.sm_order_key) AS orders_containing_product,
  COUNT(DISTINCT r.sm_order_key) AS returned_orders_containing_product,
  SUM(v.units_sold) AS units_sold,
  SAFE_DIVIDE(COUNT(DISTINCT r.sm_order_key), NULLIF(COUNT(DISTINCT v.sm_order_key), 0)) AS return_order_rate
FROM valid_product_orders AS v
LEFT JOIN returned_orders AS r
  ON v.sm_order_key = r.sm_order_key
  AND v.sm_store_id = r.sm_store_id
GROUP BY 1, 2, 3
HAVING orders_containing_product >= 25
ORDER BY return_order_rate DESC, returned_orders_containing_product DESC;

Back to the SQL Query Library.

Products

Break down order and revenue trends by product mix.

Customers & Retention

Segment order outcomes by first-time vs repeat behavior.

Marketing & Ads

Compare channel investment to realized revenue outcomes.

Managed BI (Dashboards)

Managed Data Warehouse

Templates

Common Analyses

Products

Customers & Retention

Marketing & Ads

​Related Categories

Products

Customers & Retention

Marketing & Ads

Related Categories