> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Orders & Revenue

> Order, revenue, refunds, and channel mix analysis queries.

<Tip>
  Use the same query standards from the [SQL Query Library overview](/data-activation/template-resources/sql-query-library): `is_order_sm_valid = TRUE` for order analyses, `sm_store_id` scoping for multi-store setups, and `your_project` placeholders.
</Tip>

<AccordionGroup>
  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>
</AccordionGroup>

***

<AccordionGroup>
  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>
</AccordionGroup>

## Related Categories

Back to the [SQL Query Library](/data-activation/template-resources/sql-query-library).

<CardGroup cols={2}>
  <Card title="Products" icon="bag-shopping" href="/data-activation/template-resources/sql-query-library/products">
    Break down order and revenue trends by product mix.
  </Card>

  <Card title="Customers & Retention" icon="users" href="/data-activation/template-resources/sql-query-library/customers-and-retention">
    Segment order outcomes by first-time vs repeat behavior.
  </Card>

  <Card title="Marketing & Ads" icon="chart-line" href="/data-activation/template-resources/sql-query-library/marketing-and-ads">
    Compare channel investment to realized revenue outcomes.
  </Card>
</CardGroup>