fct_returns

net_return_cost = refund_amount + gift_card_amount - upsell_amount. A positive value represents a net cost to the brand. All three components are coalesced to zero when null.

is_child_order_return = TRUE (EXC- order names) does not mean the return has an exchange outcome — use has_exchange instead. Multiple returns per order are common; when counting Return Orders, use COUNT(DISTINCT sm_order_key) rather than counting rows.

When to use this table

Use fct_returns when you need Loop Returns data at the return-event grain. It is best for return outcomes, return timing, refund/store-credit/upsell economics, and order-level return counts after deduplicating by sm_order_key. Common questions this table can answer:

What share of valid orders had at least one return?
How much net return cost is tied to each channel or campaign?
Are customers choosing refunds, exchanges, store credit, or upsells?
How long after purchase do returns usually start?

Recommended filters

For return analysis tied to known Shopify orders, start with:

WHERE is_order_matched = TRUE

Use COUNT(DISTINCT sm_order_key) for returned-order counts. Use COUNT(*) only when you intentionally want return-event counts.

Common joins

Join to obt_orders on sm_order_key when you need the full valid-order denominator for return-rate calculations.
Join to obt_order_lines on sm_order_key for product-level analysis of orders that had returns.
Join to dim_customers on sm_customer_key for customer-level return behavior.

For ready-to-run templates, see the Orders & Revenue SQL Query Library.

version: 2

models:
  - name: fct_returns
    description: >
      Loop Returns fact enriched with Shopify order context from dim_orders.
      Grain: one row per Loop return (sm_store_id + return_id).
      Requires Loop Returns integration.
      Join: left join dim_orders on shopify_order_id = order_id, sm_store_id, and source_system = Shopify.
      Critical filter: is_order_matched = TRUE to scope to returns tied to known Shopify orders;
      days_to_return and return_week_bucket are null when unmatched.
      Key joins: dim_orders via sm_order_key (many:1); dim_customers via sm_customer_key (many:1).
    columns:
      - name: sm_store_id
        description: >
          SourceMedium store identifier. Unique per tenant.

      - name: sm_return_key
        description: >
          Surrogate key for the return event (sm_store_id + return_id). Unique per row.

      - name: return_id
        description: >
          Loop Returns internal return id.

      - name: shopify_order_id
        description: >
          Shopify order id; join key to dim_orders.order_id for Shopify rows.

      - name: sm_order_key
        description: >
          Order key from dim_orders when the return matched a Shopify order.
          Null when is_order_matched = FALSE.

      - name: sm_customer_key
        description: >
          Customer key from dim_orders when matched. Null when is_order_matched = FALSE.

      - name: return_state
        description: >
          Loop return state. Values: open, closed, expired.

      - name: return_outcome
        description: >
          Loop return-level outcome. Values: exchange, refund, upsell, credit, or combined
          (e.g., exchange+refund). Use has_refund / has_exchange / has_upsell / has_credit
          flags for reliable filtering.

      - name: return_type
        description: >
          Loop return type.

      - name: loop_order_name
        description: >
          Loop order display name. Standard returns use a # prefix; exchange child orders use EXC- prefix.

      - name: is_child_order_return
        description: >
          True when loop_order_name starts with EXC-, indicating an exchange child order.
          Not a proxy for has_exchange — use has_exchange to determine exchange outcome.

      - name: customer_email
        description: >
          Email on the return record.

      - name: origin_country_code
        description: >
          Return origin country code.

      - name: return_currency
        description: >
          Currency for return financials.

      - name: return_total
        description: >
          Total return value from Loop.

      - name: return_product_total
        description: >
          Returned product subtotal.

      - name: return_discount_total
        description: >
          Discounts on returned items.

      - name: return_tax_total
        description: >
          Tax on returned items.

      - name: refund_amount
        description: >
          Cash refunded to customer.

      - name: exchange_total
        description: >
          Exchange merchandise total.

      - name: exchange_product_total
        description: >
          Exchange product subtotal.

      - name: upsell_amount
        description: >
          Additional amount collected on the return (upsell).

      - name: gift_card_amount
        description: >
          Store credit or gift card amount issued on the return.

      - name: handling_fee_amount
        description: >
          Handling fee charged on the return.

      - name: net_return_cost
        description: >
          Net cost of the return to the brand: refund_amount + gift_card_amount - upsell_amount
          (all components coalesced to zero). Positive value = net cost to brand.

      - name: order_created_at
        description: >
          Matched Shopify order created_at from dim_orders. Null when is_order_matched = FALSE.

      - name: order_processed_at_local_datetime
        description: >
          Matched order processed datetime in reporting timezone. Null when is_order_matched = FALSE.

      - name: sm_channel
        description: >
          SM channel on the matched order. Null when is_order_matched = FALSE.

      - name: sm_sub_channel
        description: >
          SM sub-channel on the matched order. Null when is_order_matched = FALSE.

      - name: order_name
        description: >
          Shopify order name from dim_orders when matched. Null when is_order_matched = FALSE.

      - name: order_index
        description: >
          Customer order index from dim_orders when matched (1 = first order). Null when is_order_matched = FALSE.

      - name: order_sequence
        description: >
          First vs repeat order sequence from dim_orders when matched.
          Values: 1st_order, repeat_order. Null when is_order_matched = FALSE.

      - name: return_created_at
        description: >
          Timestamp when the customer initiated the return.

      - name: return_updated_at
        description: >
          Timestamp of the last update on the return in Loop.

      - name: days_to_return
        description: >
          Calendar days from order_created_at to return_created_at.
          Null when is_order_matched = FALSE. Non-negative when present.

      - name: return_week_bucket
        description: >
          Bucketed days from order to return initiation.
          Values: 0-7 days, 8-14 days, 15-21 days, 22-30 days, 31-45 days, 46-60 days, 60+ days.
          Null when is_order_matched = FALSE.

      - name: has_refund
        description: >
          True when return_outcome contains "refund" (including exchange+refund).

      - name: has_exchange
        description: >
          True when return_outcome contains "exchange" (including exchange+refund).

      - name: has_upsell
        description: >
          True when return_outcome is exactly "upsell".

      - name: has_credit
        description: >
          True when return_outcome contains "credit".

      - name: is_order_matched
        description: >
          True when a dim_orders row was found for shopify_order_id + sm_store_id + Shopify.
          When false, all order-context fields are null.

Getting Started

Quick Reference

Table Documentation

Schema Standards

Platform Raw Data

When to use this table

Recommended filters

Common joins

​When to use this table

​Recommended filters

​Common joins

When to use this table

Recommended filters

Common joins