- Dataset:
sm_views - Table type:
Report - Primary use case:
Reporting
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.Query Example
SELECT *
FROM `your_project.sm_views.rpt_order_returns_v1`
LIMIT 100;
Schema
version: 2
models:
- name: rpt_order_returns_v1
description: >
Order-level returns summary generated for tenant reporting. Combines return request counts, return economics, order enrichment, and customer-order classification fields from transformed v2 models.
columns:
- name: sm_store_id
data_type: STRING
meta:
is_nullable: true
description: >
SourceMedium store identifier for the returned order.
- name: sm_order_key
data_type: STRING
meta:
is_nullable: true
description: >
SourceMedium canonical order key for the returned order.
- name: sm_customer_key
data_type: STRING
meta:
is_nullable: true
description: >
SourceMedium canonical customer key associated with the returned order.
- name: source_system
data_type: STRING
meta:
is_nullable: true
description: >
Source system that produced the returned order and return records.
- name: shopify_order_id
data_type: STRING
meta:
is_nullable: true
description: >
Shopify order identifier associated with the returned order.
- name: order_name
data_type: STRING
meta:
is_nullable: true
description: >
Source order name associated with the returned order.
- name: order_created_at
data_type: TIMESTAMP
meta:
is_nullable: true
description: >
Timestamp when the returned order was created in the source system.
- name: order_processed_at_local_datetime
data_type: DATETIME
meta:
is_nullable: true
description: >
Local processed datetime for the returned order.
- name: sm_channel
data_type: STRING
meta:
is_nullable: true
description: >
SourceMedium channel assigned to the returned order.
- name: sm_sub_channel
data_type: STRING
meta:
is_nullable: true
description: >
SourceMedium sub-channel assigned to the returned order.
- name: order_index
data_type: INT64
meta:
is_nullable: true
description: >
Customer order index for the returned order across all orders.
- name: order_sequence
data_type: STRING
meta:
is_nullable: true
description: >
Customer order sequence label for the returned order across all orders.
- name: is_obt_order_matched
data_type: BOOL
meta:
is_nullable: true
description: >
True when the returned order matched an order in the transformed orders table.
- name: sm_valid_order_index
data_type: INT64
meta:
is_nullable: true
description: >
Customer valid-order index for the returned order.
- name: sm_valid_order_sequence
data_type: STRING
meta:
is_nullable: true
description: >
Customer valid-order sequence label for the returned order.
- name: order_gross_revenue
data_type: NUMERIC
meta:
is_nullable: true
description: >
Gross revenue amount for the returned order.
- name: order_net_revenue
data_type: NUMERIC
meta:
is_nullable: true
description: >
Net revenue amount for the returned order.
- name: order_cart_quantity
data_type: NUMERIC
meta:
is_nullable: true
description: >
Cart quantity from the returned order.
- name: order_discount_codes_csv
data_type: STRING
meta:
is_nullable: true
description: >
Comma-separated discount codes applied to the returned order.
- name: is_order_sm_valid
data_type: BOOL
meta:
is_nullable: true
description: >
True when the returned order satisfies SourceMedium valid-order rules.
- name: is_order_only_gift_cards
data_type: BOOL
meta:
is_nullable: true
description: >
True when the returned order only contains gift card products.
- name: is_child_order
data_type: BOOL
meta:
is_nullable: true
description: >
True when the returned order name indicates a child exchange order.
- name: has_cr_fr_discount_code
data_type: BOOL
meta:
is_nullable: true
description: >
True when the returned order includes a customer-return or final-return discount code.
- name: is_customer_order
data_type: BOOL
meta:
is_nullable: true
description: >
True when the returned order meets customer-order classification rules.
- name: has_any_return
data_type: BOOL
meta:
is_nullable: true
description: >
True for orders with at least one associated return record.
- name: return_count
data_type: INT64
meta:
is_nullable: true
description: >
Count of return records associated with the order.
- name: refund_return_count
data_type: INT64
meta:
is_nullable: true
description: >
Count of associated return records that include a refund request.
- name: exchange_return_count
data_type: INT64
meta:
is_nullable: true
description: >
Count of associated return records that include an exchange request.
- name: credit_return_count
data_type: INT64
meta:
is_nullable: true
description: >
Count of associated return records that include a credit request.
- name: upsell_return_count
data_type: INT64
meta:
is_nullable: true
description: >
Count of associated return records that include an upsell return.
- name: has_refund_request
data_type: BOOL
meta:
is_nullable: true
description: >
True when any associated return record includes a refund request.
- name: has_exchange_request
data_type: BOOL
meta:
is_nullable: true
description: >
True when any associated return record includes an exchange request.
- name: has_credit_request
data_type: BOOL
meta:
is_nullable: true
description: >
True when any associated return record includes a credit request.
- name: has_upsell_return
data_type: BOOL
meta:
is_nullable: true
description: >
True when any associated return record includes an upsell return.
- name: is_return_order
data_type: BOOL
meta:
is_nullable: true
description: >
True when the order is classified as a customer order with a refund request.
- name: is_exchange_order
data_type: BOOL
meta:
is_nullable: true
description: >
True when the order is classified as a customer order with an exchange request.
- name: first_return_created_at
data_type: TIMESTAMP
meta:
is_nullable: true
description: >
Earliest return-created timestamp associated with the order.
- name: latest_return_created_at
data_type: TIMESTAMP
meta:
is_nullable: true
description: >
Latest return-created timestamp associated with the order.
- name: days_to_first_return
data_type: INT64
meta:
is_nullable: true
description: >
Days between the order and the earliest associated return.
- name: days_to_latest_return
data_type: INT64
meta:
is_nullable: true
description: >
Days between the order and the latest associated return.
- name: return_total
data_type: NUMERIC
meta:
is_nullable: true
description: >
Total return amount across associated return records.
- name: return_product_total
data_type: NUMERIC
meta:
is_nullable: true
description: >
Product subtotal amount across associated return records.
- name: return_discount_total
data_type: NUMERIC
meta:
is_nullable: true
description: >
Discount amount across associated return records.
- name: return_tax_total
data_type: NUMERIC
meta:
is_nullable: true
description: >
Tax amount across associated return records.
- name: refund_amount
data_type: NUMERIC
meta:
is_nullable: true
description: >
Refund amount across associated return records.
- name: exchange_total
data_type: NUMERIC
meta:
is_nullable: true
description: >
Exchange total amount across associated return records.
- name: exchange_product_total
data_type: NUMERIC
meta:
is_nullable: true
description: >
Exchange product amount across associated return records.
- name: upsell_amount
data_type: NUMERIC
meta:
is_nullable: true
description: >
Upsell amount across associated return records.
- name: gift_card_amount
data_type: NUMERIC
meta:
is_nullable: true
description: >
Gift card amount across associated return records.
- name: handling_fee_amount
data_type: NUMERIC
meta:
is_nullable: true
description: >
Handling fee amount across associated return records.
- name: net_return_cost
data_type: NUMERIC
meta:
is_nullable: true
description: >
Net return cost across associated return records.