Skip to main content
net_return_cost = refund_amount + gift_card_amount - upsell_amount. A positive value represents a net cost to the brand. The result is NULL when all three money inputs are unknown; otherwise, unknown components are treated as zero.
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 returns data at the return-event grain across Loop Returns, ReturnGO, or native Shopify returns. It is best for return outcomes, reasons, timing where available, refund/store-credit/upsell economics, and order-level return counts after deduplicating by sm_order_key.
Native Shopify returns do not currently include return-level timestamps or complete return money. For those rows, timing fields and net_return_cost can be NULL. Use source_system to account for source-specific coverage.
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?
For return analysis tied to known Shopify orders, start with:
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.

​
Columns