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
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.
