version: 2
models:
- name: obt_order_lines
description: >
Product-level analytics table for order line revenue, costs, and profitability. Grain: One row per sm_order_line_key. Date field: order_processed_at_local_datetime. Critical filters: is_order_sm_valid = TRUE (exclude test/cancelled orders); sm_channel for segmentation. Key joins: obt_orders via sm_order_key (many:1); dim_product_variants via sm_product_variant_key (many:1).
columns:
- name: customer_id
description: >
The ID of the customer who placed the order.
- name: customer_tags_csv
description: >
Comma-separated customer tags at order time (free-form; convenience string form of array field). Tags may change over time; value reflects state at order processing time.
- name: earliest_order_refund_date
description: >
The date of the first refund associated with the order line.
- name: is_order_line_subscription
description: >
Boolean convenience field for whether the order line is classified as subscription. Use this instead of string matching when you want a stable filter for subscription lines.
- name: is_order_only_gift_cards
description: >
Whether the order associated with the line item only contains gift cards.
- name: is_order_sm_valid
description: >
True if order is not voided, cancelled, uncollectible, draft, or refunded, and its cancellation reason is not fraud or declined (excluded even if still marked paid). Use WHERE is_order_sm_valid = TRUE to exclude test/invalid orders from revenue.
- name: is_product_gift_card
description: >
Whether the product is a gift card.
- name: latest_order_refund_date
description: >
The most recent date a refund was processed for an order.
- name: order_cart_quantity
description: >
The quantity of items that were originally purchased in an order.
- name: order_created_at_local_datetime
description: >
Order created timestamp converted to reporting timezone (from order_created_at UTC).
- name: order_id
description: >
Platform order identifier. Not globally unique across stores; pair with `sm_store_id` and `source_system` when needed for scoping.
- name: order_index
description: >
An ordered index that can be used to identify the sequential position of an order relative to a customer's order history.
- name: order_line_discount_codes_csv
description: >
A list of discount codes applied to an order.
- name: order_line_discounts
description: >
The amount of discounts applied to an order line.
- name: order_line_duty_refunds
description: >
The amount of duty refunds applied to an order line.
- name: order_line_fulfillment_cost
description: >
The blended cost to fulfill (pick and pack) orders for customers, considering that fulfillment cost is a variable order cost included in cost of goods sold (COGS). This data is recorded in the Financial Cost - Fulfillment tab of the financial cost configuration sheet.
- name: order_line_gross_duties
description: >
The amount the customer paid in duties for an order.
- name: order_line_gross_profit
description: >
Net order line revenue and shipping revenue minus order line product cost, order shipping cost, order fulfillment cost, and merchant processing fees.
- name: order_line_gross_revenue
description: >
The gross revenue for an order line. Gross revenue is calculated by multiplying the price of an order line by the quantity purchased. Gross revenue excludes revenue from gift card purchases.
- name: order_line_gross_shipping
description: >
The amount the customer paid in shipping for an order shipping line. This does not include shipping tax.
- name: order_line_gross_shipping_taxes
description: >
The gross shipping tax for an order shipping line.
- name: order_line_gross_taxes
description: >
The amount of taxes associated with an order line, after discounts and before returns.
- name: order_line_id
description: >
The ID of the order line.
- name: order_line_original_currency_code
description: >
Source currency used for the order line's original money fields.
- name: order_line_converted_currency_code
description: >
Workspace reporting currency used for the order line's converted money fields.
- name: order_line_currency_conversion_effective_date
description: >
Date used to select the exchange rate for this order line.
- name: order_line_currency_conversion_rate_applied
description: >
Exchange rate used to convert the order line from its source currency to the reporting currency.
- name: order_line_currency_conversion_status
description: >
Whether the order line was converted, already used the reporting currency, or could not be converted.
- name: order_line_original_total_revenue
description: >
Total order-line revenue before currency conversion.
- name: order_line_converted_total_revenue
description: >
Total order-line revenue converted to the workspace reporting currency.
- name: is_order_line_currency_canonicalized
description: >
True when the standard order-line money fields use the reporting-currency values.
- name: order_line_merchant_processing_fees
description: >
The payment processing fees for an order. This data is entered in the Financial Cost - Merchant Processing Fees tab of the SourceMedium financial cost configuration sheet.
- name: order_line_net_duties
description: >
The gross order line duties minus order duty refunds.
- name: order_line_net_quantity
description: >
The quantity of items that were originally purchased for an order line minus the quantity of items refunded.
- name: order_line_net_revenue
description: >
The gross order line revenue minus order line discounts and refunds.
- name: order_line_net_revenue_before_refunds
description: >
The gross order line revenue minus order line discounts.
- name: order_line_net_shipping
description: >
The gross shipping revenue for an order shipping line minus shipping discounts and shipping refunds.
- name: order_line_net_shipping_taxes
description: >
The shipping tax for an order shipping line minus shipping tax refunds.
- name: order_line_net_taxes
description: >
The gross order line tax minus the order line tax refunds.
- name: order_line_product_collection_handles_csv
description: >
Unique, human-readable strings for the collections a product belongs to automatically generated from their titles.
- name: order_line_product_collection_titles_csv
description: >
The titles of collections the product belongs to. Collections are groupings of products that merchants can create to make their stores easier to browse.
- name: order_line_product_cost
description: >
The landed cost of an order line as defined by the SourceMedium financial cost configuration sheet (or input into Shopify) multiplied by the quantity purchased. The SourceMedium financial cost configuration overrides any costs input into Shopify.
- name: order_line_product_gross_profit
description: >
Net order line revenue minus product cost.
- name: order_line_quantity
description: >
The quantity of order lines that were originally purchased in an order.
- name: order_line_refund_quantity
description: >
The quantity of items purchased for an order line that were refunded.
- name: order_line_refunds
description: >
The amount of refunds applied to an order line.
- name: order_line_return_cost
description: >
The blended cost per order to handle returned products from a customer, considering that the cost of returns is a variable order cost included in cost of goods sold (COGS). This data is entered in the Financial Cost - Shipping tab of the SourceMedium financial cost configuration sheet.
- name: order_line_shipping_cost
description: >
The blended cost per order to ship products to customers, considering that shipping costs are variable order costs included in cost of goods sold (COGS). This data is set in the Financial Cost - Shipping tab of the SourceMedium financial cost configuration sheet.
- name: order_line_shipping_discounts
description: >
The amount of discounts applied to an order shipping line.
- name: order_line_shipping_refunds
description: >
The amount of shipping refunds applied to an order shipping line.
- name: order_line_shipping_tax_refunds
description: >
The amount of shipping tax refunds applied to an order shipping line.
- name: order_line_tax_refunds
description: >
The amount of tax refunds applied to an order line.
- name: order_line_total_discounts
description: >
The amount of discounts for an order line.
- name: order_line_total_refunds
description: >
The amount of refunds for an order line.
- name: order_line_total_revenue
description: >
Total order line revenue after factoring in shipping revenue, taxes collected, discounts, and refunds.
- name: order_line_total_taxes
description: >
The amount of taxes for an order line minus tax refunds.
- name: order_line_type
description: >
Line-level order classification, such as subscription or one-time. For Shopify, SourceMedium prioritizes direct subscription-platform mappings first, then explicit line-item metadata, then limited legacy fallback signals such as order tags or app/source markers. Use this field for mixed-cart and product-level subscription analysis.
- name: order_number
description: >
The order's position in the shop's count of orders.
- name: order_payment_status
description: >
The financial status of an order, which indicates whether the order has been paid.
- name: order_processed_at
description: >
UTC timestamp from source system when the order was processed.
- name: order_processed_at_local_datetime
description: >
Order processed timestamp converted to reporting timezone (from order_processed_at UTC). Primary date field for order analytics and time-based filtering.
- name: order_sequence
description: >
Whether an order is the acquisition order or a repeat order.
- name: order_shipping_city
description: >
The city, town, or village of the shipping address.
- name: order_shipping_country
description: >
The country of the shipping address.
- name: order_shipping_country_code
description: >
The two-letter code (ISO 3166-1 format) for the country of the shipping address.
- name: order_shipping_state
description: >
The state of the shipping address.
- name: order_shipping_zip_code
description: >
Postal/ZIP code of the shipping address for the order (country-dependent format; not geo-normalized). Combine with country/state for region-based analysis.
- name: order_tags_csv
description: >
Comma-separated order-level tags (free-form strings set by merchant/apps). Prefer array fields for exact matching where available.
- name: order_to_refund_days
description: >
Days between order processing and first refund (non-negative; NULL when no refund). Calculated from order_processed_at_local_datetime to earliest_order_refund_date.
- name: order_to_refund_months
description: >
Months between order processing date and first refund date for the order/line; derived from days using average month length.
- name: order_to_refund_weeks
description: >
Weeks between order processing date and first refund date for the order/line; derived from days and rounded per model logic.
- name: primary_product_image_url
description: >
Primary product image URL (display).
- name: product_id
description: >
A unique identifier for the product generated by the source system. It can be null for Shopify if the order line data did not contain the product_id populated.
- name: product_tags_csv
description: >
Comma-separated product tags on the line's product (free-form; aggregated from product metadata). Use array for exact match; CSV for quick text filters.
- name: product_title
description: >
The title of the product.
- name: product_type
description: >
A categorization for the product used for filtering and searching for products.
- name: product_variant_compare_at_price
description: >
The original/compare-at price for the product variant (typically MSRP or pre-sale price). Used to show savings/discounts to customers; may be NULL if no compare-at price set.
- name: product_variant_id
description: >
A unique identifier for the product variant generated by the source system. It can be null for Shopify if the order line data did not contain the product_variant_id populated.
- name: product_variant_price
description: >
The price of the product variant at the time of purchase (per unit, before discounts). Use for pricing analysis and discount effectiveness calculations.
- name: product_variant_title
description: >
The title of the product variant.
- name: product_vendor
description: >
The vendor of the product.
- name: sku
description: >
The stock keeping unit (SKU) of the product variant.
- name: sm_channel
description: >
Sales channel via hierarchy: (1) exclusion tag 'sm-exclude-order' -> excluded; (2) config sheet overrides; (3) default logic (amazon/tiktok_shop/walmart.com, pos/leap -> retail, wholesale tags -> wholesale, otherwise online_dtc). Note: excluded channel is omitted from Executive Summary and LTV.
- name: sm_customer_key
description: >
Stable SourceMedium customer key. Unique per customer. Key joins: `dim_customers` (1:1); `dim_orders`/`obt_orders` (1:many). Platform caveat: TikTok Shop coverage may be limited. Foreign key to obt_customers (many:1 - multiple order lines per customer).
- name: sm_order_key
description: >
Stable SourceMedium order key. Unique per order. Key joins: `obt_order_lines` (1:many via `sm_order_key`); `dim_customers` (many:1 via `sm_customer_key`). Platform caveat: TikTok Shop coverage may be limited. Foreign key to obt_orders (many:1 - multiple order lines per order).
- name: sm_order_line_key
description: >
Stable SourceMedium order line key. Unique per line. Key joins: `dim_order_lines` (1:1); `dim_orders` (many:1 via `sm_order_key`). Platform caveat: TikTok Shop coverage may be limited.
- name: sm_order_sales_channel
description: >
The sales channel associated with an order, which is derived from the order source name and order tags.
- name: sm_order_type
description: >
Order classification (subscription vs one-time) derived from order attributes, tags, or subscription platforms. For Amazon Seller Central orders, SourceMedium can classify eligible Subscribe & Save activity using available Amazon order-item promotion and program signals.
- name: sm_product_variant_key
description: >
Stable SourceMedium join key for product variants to related tables.
- name: sm_store_id
description: >
SourceMedium's unique store identifier. For Shopify stores, derived from the myshopify.com domain; for other platforms (Amazon, TikTok Shop, Walmart.com), uses platform-specific identifiers.
- name: sm_sub_channel
description: >
The sales sub-channel associated with an order, which is derived from the order source name and order tags and factors in manual overrides as defined by the SourceMedium channel mapping configuration sheet.
- name: sm_utm_campaign
description: >
Last-click UTM campaign from the attribution source hierarchy (Shopify visits/landing/notes -> website events -> GA/GA4 -> referrer). Native UTM on Shopify/Chargebee; marketplaces (Amazon, TikTok Shop, Walmart) lack it. Enriched via GA4, Elevar, Blotout, Snowplow, Heap, Littledata.
- name: sm_utm_id
description: >
SourceMedium-generated unique identifier for UTM parameter combinations on this order line. Enables join to UTM attribution data for marketing campaign analysis.
- name: sm_utm_medium
description: >
Last-click UTM medium from the attribution source hierarchy (Shopify visits/landing/notes -> website events -> GA/GA4 -> referrer). Native UTM on Shopify/Chargebee; marketplaces (Amazon, TikTok Shop, Walmart) lack it. Enriched via GA4, Elevar, Blotout, Snowplow, Heap, Littledata.
- name: sm_utm_source
description: >
Last-click UTM source from the attribution source hierarchy (Shopify visits/landing/notes -> website events -> GA/GA4 -> referrer). Native UTM on Shopify/Chargebee; marketplaces (Amazon, TikTok Shop, Walmart) lack it. Enriched via GA4, Elevar, Blotout, Snowplow, Heap, Littledata.
- name: sm_utm_source_medium
description: >
A concatenation of sm_utm_source and sm_utm_medium.
- name: sm_valid_order_index
description: >
Ordered index identifying the sequential position of a valid order in a customer's valid order history. Only counts orders where is_order_sm_valid = TRUE. See order_sequence for all orders.
- name: sm_valid_order_index_reversed
description: >
An ordered index in reverse that can be used to identify the sequential position of a valid order relative to a customer's valid order history.
- name: sm_zero_party_attribution_source
description: >
Attributable source from post‑purchase survey tags (e.g., Fairing/Enquire).
- name: source_system
description: >
Originating platform for the record (e.g., Shopify, Amazon, TikTok Shop, Chargebee). Used for platform‑specific behavior and coverage.
- name: source_system_configured_product_cost
description: >
Landed cost from platform-configured product costs (not SourceMedium configuration sheet). For config-sheet costs, see user_configured_product_cost.
- name: subscription_id
description: >
Subscription identifier for the line when a reliable one is available. For Shopify, placeholder values known to be bad subscription signals can be normalized away before this field is populated.
- name: subscription_order_index
description: >
Ordered index identifying the sequential position of a subscription line within a customer's subscription history when a reliable subscription identifier or direct subscription-platform mapping is available.
- name: subscription_order_sequence
description: >
Line-level subscription lifecycle classification. For subscription lines, this reflects first-vs-recurring subscription behavior when a reliable subscription index is available. For non-subscription lines, the sequence should resolve to the non-subscription value. SourceMedium keeps this field aligned with is_order_line_subscription so line-level sequence does not contradict the line-level subscription classification.
- name: user_configured_product_cost
description: >
The landed cost of an order line as defined by the SourceMedium financial cost configuration sheet multiplied by the quantity purchased.
When reporting-currency conversion is enabled, standard order-line money fields such as
order_line_net_revenue and order_line_total_revenue use the workspace reporting currency. Use the order_line_original_* fields to reconcile against the source. See Reporting Currency.