rpt_order_returns_v1 - SourceMedium

One row per order that has at least one return, with return counts, return economics, and the order’s own context from obt_orders.

This view contains only orders that have at least one return. To compute a return rate, join against obt_orders or count is_return_order = TRUE rows against total valid orders from obt_orders.

is_return_order and is_exchange_order are the preferred fields for return/exchange order counts. They require is_customer_order = TRUE, which filters to valid, gross-revenue-positive, non-EXC Shopify orders. Do not use has_refund_request or has_exchange_request alone for these metrics.

Example query

SELECT
  DATE_TRUNC(DATE(order_processed_at_local_datetime), MONTH) AS month,
  COUNTIF(is_return_order) AS return_orders,
  COUNTIF(is_exchange_order) AS exchange_orders,
  SUM(net_return_cost) AS net_return_cost
FROM `your_project.sm_views.rpt_order_returns_v1`
WHERE is_obt_order_matched = TRUE
  AND is_customer_order = TRUE
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY 1
ORDER BY 1

Columns

Column	Type	Description
`sm_customer_key`	STRING	SourceMedium canonical customer key associated with the returned order.
`sm_order_key`	STRING	SourceMedium canonical order key for the returned order.
`sm_store_id`	STRING	SourceMedium store identifier for the returned order.
`credit_return_count`	INT64	Count of associated return records that include a credit request.
`days_to_first_return`	INT64	Days between the order and the earliest associated return.
`days_to_latest_return`	INT64	Days between the order and the latest associated return.
`exchange_product_total`	NUMERIC	Exchange product amount across associated return records.
`exchange_return_count`	INT64	Count of associated return records that include an exchange request.
`exchange_total`	NUMERIC	Exchange total amount across associated return records.
`first_return_created_at`	TIMESTAMP	Earliest return-created timestamp associated with the order.
`gift_card_amount`	NUMERIC	Gift card amount across associated return records.
`handling_fee_amount`	NUMERIC	Handling fee amount across associated return records.
`has_any_return`	BOOL	True for orders with at least one associated return record.
`has_cr_fr_discount_code`	BOOL	True when the returned order includes a customer-return or final-return discount code.
`has_credit_request`	BOOL	True when any associated return record includes a credit request.
`has_exchange_request`	BOOL	True when any associated return record includes an exchange request.
`has_refund_request`	BOOL	True when any associated return record includes a refund request.
`has_upsell_return`	BOOL	True when any associated return record includes an upsell. NULL, rather than false, when the returns platform cannot report upsells at all, ReturnGO records them as transactions that carry no link back to a return.
`is_child_order`	BOOL	True when the returned order name indicates a child exchange order.
`is_customer_order`	BOOL	True when the returned order meets customer-order classification rules.
`is_exchange_order`	BOOL	True when the order is classified as a customer order with an exchange request.
`is_obt_order_matched`	BOOL	True when the returned order matched an order in the transformed orders table.
`is_order_only_gift_cards`	BOOL	True when the returned order only contains gift card products.
`is_order_sm_valid`	BOOL	True when the returned order satisfies SourceMedium valid-order rules.
`is_return_order`	BOOL	True when the order is classified as a customer order with a refund request.
`latest_return_created_at`	TIMESTAMP	Latest return-created timestamp associated with the order.
`net_return_cost`	NUMERIC	Net return cost across associated return records.
`order_cart_quantity`	NUMERIC	Cart quantity from the returned order.
`order_created_at`	TIMESTAMP	Timestamp when the returned order was created in the source system.
`order_discount_codes_csv`	STRING	Comma-separated discount codes applied to the returned order.
`order_gross_revenue`	NUMERIC	Gross revenue amount for the returned order.
`order_index`	INT64	Customer order index for the returned order across all orders.
`order_name`	STRING	Source order name associated with the returned order.
`order_net_revenue`	NUMERIC	Net revenue amount for the returned order.
`order_processed_at_local_datetime`	DATETIME	Local processed datetime for the returned order.
`order_sequence`	STRING	Customer order sequence label for the returned order across all orders.
`refund_amount`	NUMERIC	Refund amount across associated return records.
`refund_return_count`	INT64	Count of associated return records that include a refund request.
`return_count`	INT64	Count of return records associated with the order.
`return_discount_total`	NUMERIC	Discount amount across associated return records.
`return_product_total`	NUMERIC	Product subtotal amount across associated return records.
`return_tax_total`	NUMERIC	Tax amount across associated return records.
`return_total`	NUMERIC	Total return amount across associated return records.
`returns_source_system`	STRING	Returns platform that produced the return records for this order (loop_returns, returngo). A comma-separated list when an order carries returns from more than one platform.
`shopify_order_id`	STRING	Shopify order identifier associated with the returned order.
`sm_channel`	STRING	SourceMedium channel assigned to the returned order.
`sm_sub_channel`	STRING	SourceMedium sub-channel assigned to the returned order.
`sm_valid_order_index`	INT64	Customer valid-order index for the returned order.
`sm_valid_order_sequence`	STRING	Customer valid-order sequence label for the returned order.
`source_system`	STRING	Source system that produced the returned ORDER (always Shopify). For the returns platform, see returns_source_system.
`upsell_amount`	NUMERIC	Upsell amount across associated return records.
`upsell_return_count`	INT64	Count of associated return records known to include an upsell. Always 0 for orders whose returns come from a platform that does not report upsells (see returns_source_system).