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.
rpt_order_returns_v1 is not yet deployed to customer Managed Data Warehouses. Use fct_returns for direct access to Loop Returns data today. This page documents the intended schema for when the view is deployed.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 correct fields for TPJ-style return/exchange order counts — they require is_customer_order = TRUE, which filters to valid, revenue-positive, non-EXC Shopify orders. Do not use has_refund_request or has_exchange_request alone for these metrics.version: 2
models:
- name: rpt_order_returns_v1
description: >
Order-level Loop Returns report enriched with Shopify order context from obt_orders and obt_order_lines.
Grain: one row per order that has at least one associated Loop return (sm_store_id + sm_order_key).
Source: Shopify only. Requires Loop Returns integration.
Key filters: is_return_order = TRUE for return order counts; is_exchange_order = TRUE for exchange order counts.
Key joins: obt_orders via sm_order_key (1:1 when is_obt_order_matched = TRUE); fct_returns via sm_order_key (1:many).
columns:
- name: sm_store_id
description: >
SourceMedium store identifier.
- name: sm_order_key
description: >
Stable SourceMedium order key. Unique per order. Key joins: obt_orders (1:1); fct_returns (1:many).
- name: sm_customer_key
description: >
Customer key from fct_returns when the return matched an order.
- name: source_system
description: >
Always Shopify for this view.
- name: shopify_order_id
description: >
Shopify order id from Loop Returns.
- name: order_name
description: >
Shopify order display name (e.g. #1001).
- name: order_created_at
description: >
Order created timestamp from obt_orders when matched.
- name: order_processed_at_local_datetime
description: >
Order processed datetime in reporting timezone from obt_orders when matched.
- name: sm_channel
description: >
SM channel on the order from obt_orders.
- name: sm_sub_channel
description: >
SM sub-channel on the order from obt_orders.
- name: order_index
description: >
Customer order index from fct_returns (1 = first order for that customer).
- name: order_sequence
description: >
First vs repeat order sequence. Values: 1st_order, repeat_order.
- name: is_obt_order_matched
description: >
True when the returned order was found in obt_orders.
- name: sm_valid_order_index
description: >
Customer order index counting only valid orders (is_order_sm_valid = TRUE, non-gift-card, positive revenue).
- name: sm_valid_order_sequence
description: >
First vs repeat sequence based on sm_valid_order_index.
- name: order_gross_revenue
description: >
Gross revenue of the original order from obt_orders.
- name: order_net_revenue
description: >
Net revenue of the original order from obt_orders.
- name: order_cart_quantity
description: >
Cart quantity of the original order from obt_orders.
- name: order_discount_codes_csv
description: >
Comma-separated discount codes on the original order.
- name: is_order_sm_valid
description: >
True if the original order is valid per SourceMedium logic (not voided, cancelled, or refunded).
- name: is_order_only_gift_cards
description: >
True when the order contained only gift card line items.
- name: is_child_order
description: >
True when the Loop order name starts with EXC-, indicating an exchange child order.
- name: has_cr_fr_discount_code
description: >
True when the order discount code matches CR- or FR- patterns (typically Loop-generated credits).
- name: is_customer_order
description: >
True when the order meets customer order criteria: is_order_sm_valid, non-EXC order name, positive gross revenue and cart quantity, no CR-/FR- discount codes, and not gift-card-only. Use this as the denominator when computing return rates.
- name: has_any_return
description: >
Always true. All rows in this view have at least one Loop return.
- name: return_count
description: >
Number of Loop returns associated with this order.
- name: refund_return_count
description: >
Number of returns with a refund outcome.
- name: exchange_return_count
description: >
Number of returns with an exchange outcome.
- name: credit_return_count
description: >
Number of returns with a store credit outcome.
- name: upsell_return_count
description: >
Number of returns with an upsell outcome.
- name: has_refund_request
description: >
True when at least one return on this order has a refund outcome.
- name: has_exchange_request
description: >
True when at least one return on this order has an exchange outcome.
- name: has_credit_request
description: >
True when at least one return on this order has a credit outcome.
- name: has_upsell_return
description: >
True when at least one return on this order has an upsell outcome.
- name: is_return_order
description: >
True when is_customer_order = TRUE and has_refund_request = TRUE. Use for TPJ-style Return Order counts.
- name: is_exchange_order
description: >
True when is_customer_order = TRUE and has_exchange_request = TRUE. Use for TPJ-style Exchange Order counts.
- name: first_return_created_at
description: >
Timestamp of the earliest return initiation across all returns on this order.
- name: latest_return_created_at
description: >
Timestamp of the most recent return initiation across all returns on this order.
- name: days_to_first_return
description: >
Calendar days from order_created_at to first_return_created_at.
- name: days_to_latest_return
description: >
Calendar days from order_created_at to latest_return_created_at.
- name: return_total
description: >
Sum of return_total across all returns on this order.
- name: return_product_total
description: >
Sum of returned product subtotals across all returns on this order.
- name: return_discount_total
description: >
Sum of discounts on returned items across all returns on this order.
- name: return_tax_total
description: >
Sum of tax on returned items across all returns on this order.
- name: refund_amount
description: >
Sum of cash refunded to customer across all returns on this order.
- name: exchange_total
description: >
Sum of exchange merchandise totals across all returns on this order.
- name: exchange_product_total
description: >
Sum of exchange product subtotals across all returns on this order.
- name: upsell_amount
description: >
Sum of additional amounts collected (upsell) across all returns on this order.
- name: gift_card_amount
description: >
Sum of store credit or gift card amounts issued across all returns on this order.
- name: handling_fee_amount
description: >
Sum of handling fees charged across all returns on this order.
- name: net_return_cost
description: >
Sum of net return costs across all returns on this order (refund_amount + gift_card_amount - upsell_amount).