obt_orders

version: 2

models:
  - name: obt_orders
    description: >
      Order analytics table for revenue, profitability, refunds, and channel performance analysis. Grain: One row per sm_order_key. Date field: order_processed_at_local_datetime. Critical filters: is_order_sm_valid = TRUE (exclude test/cancelled orders); sm_channel for segmentation. Platform caveat: TikTok Shop coverage may be limited. Key joins: obt_order_lines via sm_order_key (1:many); obt_customers via sm_customer_key (many:1); dim_orders via sm_order_key (1:1).
    columns:
      - name: customer_device_type
        description: >
          Device type derived from user agent (e.g., mobile, desktop, tablet). Coverage depends on website tracking; limited for marketplaces.

      - name: customer_email_hashed
        description: >
          A hashed version of the customer's email address.

      - name: customer_id
        description: >
          The ID of the customer who placed the order.

      - name: customer_tags_array
        description: >
          Array of all tags associated with a customer. Preferred for robust filtering (use UNNEST) and programmatic tag operations. Snapshot at time of order (free-form, set by merchants/apps). Tags reflect state at processing time; use ARRAY_TO_STRING for filtering.

      - name: customer_tags_csv
        description: >
          Comma-separated customer tags at time of order; string version of customer_tags_array. Prefer array field for robust matching; CSV can include commas in tag values.

      - name: earliest_refund_date
        description: >
          The earliest date when a refund was processed for an order.

      - name: gross_profit
        description: >
          Net order revenue and shipping revenue minus order product cost, order shipping cost, order fulfillment cost, and merchant processing fees.

      - name: is_first_subscription_order
        description: >
          Whether a subscription order is the first subscription order based on the subscription order index or order tag indicators when an index is not available.

      - name: is_latest_order
        description: >
          Whether an order has an order_index_reversed that equals 1.

      - name: is_order_recurring_subscription
        description: >
          Whether a subscription order is a repeat subscription order based on the subscription order index or order tag indicators when an index is not available.

      - name: is_order_sm_valid
        description: >
          True if order is not voided, cancelled, uncollectible, draft, or refunded. Use WHERE is_order_sm_valid = TRUE to exclude test/invalid orders from revenue.

      - name: is_price_tax_inclusive
        description: >
          Whether taxes are included in the order subtotal.

      - name: is_subscription_order
        description: >
          Whether the order is a subscription order.

      - name: latest_refund_date
        description: >
          The most recent date when a refund was processed for an order.

      - name: order_cancellation_reason
        description: >
          The customer's reason for the order's cancellation.

      - name: order_cancelled_at
        description: >
          UTC timestamp when the order was cancelled. Null if order has not been cancelled.

      - name: order_cancelled_at_local_datetime
        description: >
          Order cancelled timestamp converted to reporting timezone (from order_cancelled_at UTC). Null if order has not been cancelled.

      - name: order_cart_net_quantity
        description: >
          The quantity of items that were originally purchased in an order minus the quantity of items refunded.

      - name: order_cart_quantity
        description: >
          The quantity of items that were originally purchased in an order.

      - name: order_checkout_id
        description: >
          The ID of the checkout that the order is associated with.

      - name: order_created_at
        description: >
          UTC timestamp when the order was created.

      - name: order_created_at_local_datetime
        description: >
          Order created timestamp converted to reporting timezone (from order_created_at UTC).

      - name: order_currency_code
        description: >
          Three-letter ISO 4217 currency code used to price and settle the order (e.g., USD, CAD, EUR). Multi-currency brands should normalize for FX when comparing revenue across currencies.

      - name: order_customer_street_address
        description: >
          Customer's billing street address associated with the order. May differ from shipping address; use for billing analysis and fraud detection.

      - name: order_discount_codes_csv
        description: >
          A list of discount codes applied to an order.

      - name: order_discounts
        description: >
          The total amount of discounts applied to an order.

      - name: order_duty_refunds
        description: >
          The amount of duty refunds applied to an order.

      - name: order_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_gross_duties
        description: >
          The amount the customer paid in duties for an order.

      - name: order_gross_revenue
        description: >
          The gross revenue for an order, based on an order's lines. Gross revenue is calculated by multiplying the price of an order's lines by the quantity purchased. Gross revenue excludes revenue from gift card purchases.

      - name: order_gross_shipping
        description: >
          The amount the customer paid in shipping for an order. This does not include shipping tax.

      - name: order_gross_shipping_taxes
        description: >
          The amount of taxes associated with shipping, after discounts and before returns.

      - name: order_gross_taxes
        description: >
          The amount of taxes associated with an order's lines, after discounts and before returns. This does not include shipping taxes.

      - name: order_id
        description: >
          Platform order identifier. Not globally unique across stores; pair with `smcid` and `source_system` when needed for scoping.

      - name: order_index
        description: >
          Ordered index identifying the sequential position of an order in a customer's order history.

      - name: order_index_reversed
        description: >
          Ordered index identifying the sequential position of an order in a customer's order history, in reverse order.

      - name: order_latency_days
        description: >
          The number of days between the order_created_at and the order_processed_at for an order.

      - name: order_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_name
        description: >
          Platform-rendered name (human‑readable or platform‑specific). Not a stable join key.

      - name: order_net_duties
        description: >
          The gross order duties minus duty refunds.

      - name: order_net_revenue
        description: >
          The gross order revenue minus order discounts and refunds.

      - name: order_net_revenue_before_refunds
        description: >
          The gross order revenue minus order discounts.

      - name: order_net_shipping
        description: >
          The gross shipping revenue for an order minus shipping discounts and shipping refunds.

      - name: order_net_shipping_taxes
        description: >
          The order shipping tax minus shipping tax refunds.

      - name: order_net_taxes
        description: >
          The order tax minus order tax refunds. This does not include shipping taxes.

      - name: order_number
        description: >
          Shop-scoped sequence number assigned by the platform. Not globally unique; pair with `smcid` for scoping.

      - name: order_payment_status
        description: >
          Financial status of the order (e.g., paid, partially_paid, partially_refunded, authorized, pending, refunded, voided, draft). Platform‑defined.

      - name: order_processed_at
        description: >
          UTC timestamp 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_processing_method
        description: >
          Method used to process the order (e.g., 'manual', 'direct', 'offsite'). Platform-defined strings; some methods may only appear for specific integrations.

      - name: order_product_tags_array
        description: >
          Array of unique product tags included in the order (de-duplicated from order lines). Prefer array for exact matching; CSV for quick text filters.

      - name: order_product_tags_csv
        description: >
          Comma-separated list of product tags from items in the order (free-form, merchant-defined). Tags can be inconsistent across platforms; prefer normalized dimensional attributes when available.

      - name: order_product_titles_array
        description: >
          Array of product titles included in the order (aggregated from order lines). Product titles can change; prefer product/variant IDs for stable joins.

      - name: order_product_titles_csv
        description: >
          A list of product titles included in an order.

      - name: order_product_variant_titles_array
        description: >
          Array of product variant titles in the order (e.g., size/color; aggregated from order lines). Variant titles can change; use stable variant IDs for joins when available.

      - name: order_product_variant_titles_csv
        description: >
          A list of product variant titles included in an order.

      - name: order_referring_site
        description: >
          The URL of the site that referred the customer to the shop.

      - name: order_refund_quantity
        description: >
          The quantity of order lines originally purchased in an order that were refunded.

      - name: order_refunds
        description: >
          The total amount of refunds applied to an order.

      - name: order_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_sequence
        description: >
          Customer lifecycle classification: 'First Order' for new customers, 'Repeat Order' for returning customers. Includes all orders (valid + invalid). Use for cohort analysis and retention reporting. See valid_order_index for valid-only ordering.

      - name: order_session_browser_type
        description: >
          Browser type derived from user agent (e.g., chrome, safari, firefox). Coverage depends on website tracking; limited for marketplaces.

      - name: order_session_user_agent
        description: >
          Raw user agent string. Used to derive customer_device_type and order_session_browser_type.

      - name: order_shipping_city
        description: >
          The city, town, or village of the shipping address.

      - name: order_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_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_discounts
        description: >
          The amount of discounts applied to shipping.

      - name: order_shipping_refunds
        description: >
          The amount of shipping refunds applied to an order.

      - name: order_shipping_state
        description: >
          Province/state of the shipping address; format varies by country and platform.

      - name: order_shipping_tax_refunds
        description: >
          The amount of shipping tax refunds applied to an order.

      - name: order_shipping_zip_code
        description: >
          Postal/ZIP code from the shipping address (alphanumeric, varies by country). Use with country and state to avoid ambiguity; not geo-normalized.

      - name: order_skus_csv
        description: >
          A list of SKUs included in an order.

      - name: order_source_name
        description: >
          Original source reported by the platform (e.g., Shopify sales channel/app name).

      - name: order_tags_array
        description: >
          Array of tags that the shop owner has attached to the order. Preferred for robust filtering (use UNNEST) and programmatic tag operations.

      - name: order_tags_csv
        description: >
          Comma-separated list of tags that the shop owner has attached to the order. Use for simple filtering; beware that individual tag values may contain commas.

      - name: order_tax_refunds
        description: >
          The amount of tax refunds applied to an order. This does not include shipping tax refunds.

      - name: order_to_refund_days_earliest
        description: >
          The number of days between the process date and the first refund date for an order.

      - name: order_to_refund_days_latest
        description: >
          The number of days between the process date and the most recent refund date for an order.

      - name: order_to_refund_months_earliest
        description: >
          The number of months between the process date and the first refund date for an order.

      - name: order_to_refund_months_latest
        description: >
          The number of months between the process date and the most recent refund date for an order.

      - name: order_to_refund_weeks_earliest
        description: >
          The number of weeks between the process date and the first refund date for an order.

      - name: order_to_refund_weeks_latest
        description: >
          The number of weeks between the process date and the most recent refund date for an order.

      - name: order_total_discounts
        description: >
          The amount of order and shipping discounts for an order.

      - name: order_total_refunds
        description: >
          The amount of order and shipping refunds.

      - name: order_total_revenue
        description: >
          Total order revenue after factoring in shipping revenue, taxes collected, discounts, and refunds.

      - name: order_total_taxes
        description: >
          The amount of order and shipping taxes minus order and shipping tax refunds.

      - name: order_updated_at
        description: >
          UTC timestamp from source system when the order was last updated.

      - name: order_updated_at_local_datetime
        description: >
          Order last updated timestamp converted to reporting timezone (from order_updated_at UTC).

      - name: order_vendors_csv
        description: >
          Comma-separated list of product vendors in the order, aggregated from line items. Vendor names may vary by platform; use for vendor mix analysis at order level.

      - name: primary_order_payment_gateway
        description: >
          The technology or service that securely transmitted payment information between the customer, the business, and the payment processor.

      - name: product_cost
        description: >
          The landed cost of an order as defined by the SourceMedium financial cost configuration sheet (or by costs input directly into Shopify) multiplied by the quantity purchased.

      - name: product_gross_profit
        description: >
          Net order revenue minus product cost.

      - 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 orders per customer).

      - name: sm_default_channel
        description: >
          Default channel before overrides, from source name and tags: amazon/tiktok_shop/walmart.com; pos/leap -> retail; wholesale tags -> wholesale; otherwise online_dtc. See sm_channel for final channel.

      - name: sm_fbclid
        description: >
          Facebook Click Identifier (FBCLID) from Meta/Facebook ad campaigns, used to track social media conversions. Present when order originated from Facebook/Instagram ad click-through.

      - name: sm_gclid
        description: >
          Google Click Identifier (GCLID) from Google Ads campaigns, used to track paid search conversions. Present when order originated from Google Ads click-through.

      - 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. Primary key (grain: one row per sm_order_key). Join to obt_order_lines via sm_order_key (1:many), obt_customers via sm_customer_key (many:1), dim_orders via sm_order_key (1:1).

      - name: sm_order_landing_page
        description: >
          The URL for the page where the buyer landed when they entered the shop.

      - name: sm_order_referrer_domain
        description: >
          Domain derived from order_referring_site.

      - name: sm_order_sales_channel
        description: >
          Raw sales channel from source system (e.g., 'TikTok Shop', 'Instagram Shop'). Used as input dimension for sm_channel mapping. See sm_channel for final classification.

      - name: sm_order_type
        description: >
          Order classification (subscription vs one-time) derived from order attributes, tags, or subscription platforms (Shopify Subscription Contract, ReCharge, Skio, Loop, Retextion).

      - 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: >
          Sub-channel from source/tags with config overrides (e.g., Facebook & Instagram, Google, Amazon FBA/Fulfilled by Merchant).

      - name: sm_utm_campaign
        description: >
          Last-click UTM campaign from attribution waterfall (Shopify visits/landing/notes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.

      - name: sm_utm_content
        description: >
          Last-click UTM content from attribution waterfall (Shopify visits/landing/notes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.

      - name: sm_utm_id
        description: >
          SourceMedium-generated unique identifier for UTM parameter combinations on this order. Used to link orders to specific marketing campaigns via UTM tracking.

      - name: sm_utm_medium
        description: >
          Last-click UTM medium from attribution waterfall (Shopify visits/landing/notes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.

      - name: sm_utm_source
        description: >
          Last-click UTM source from attribution waterfall (Shopify visits/landing/notes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.

      - name: sm_utm_source_medium
        description: >
          Concatenation of source / medium (e.g., 'google / cpc'). Shows '(none) / (none)' when null.

      - name: sm_utm_term
        description: >
          Last-click UTM term from attribution waterfall (Shopify visits/landing/notes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.

      - 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 that can be used to identify the sequential position of a valid order relative to a customer's valid order history, in reverse order.

      - name: sm_valid_order_latency_days
        description: >
          The number of days between the process date of a valid order and the process date of the most recent preceding valid order for a customer.

      - name: sm_valid_order_sequence
        description: >
          Whether a valid order is the first valid order or a repeat valid order.

      - 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: subscription_order_index
        description: >
          Ordered index identifying the sequential position of a subscription order in a customer's subscription order history.

      - name: subscription_order_sequence
        description: >
          Subscription lifecycle classification: 'First Subscription Order' for initial subscription purchase, 'Repeat Subscription Order' for renewals. Based on subscription order index when available, otherwise inferred from order tags. Use for subscription cohort analysis.