dim_orders

version: 2

models:
  - name: dim_orders
    description: >
      Order dimension with stable keys and descriptive attributes for joining and exploration. Grain: One row per sm_order_key. Date field: order_processed_at_local_datetime. Critical filters: sm_channel for channel segmentation; source_system for platform-specific behavior. Platform caveat: TikTok Shop coverage may be limited. Key joins: dim_order_lines via sm_order_key (1:many); dim_customers via sm_customer_key (many: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_id
        description: >
          The ID of the customer who placed the order.

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

      - 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_quantity
        description: >
          Total number of items (quantity across all line items) in the order cart. Sum of all order_line_quantity values for the 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 for the order (e.g., USD, EUR, GBP). Used for multi-currency analysis and reporting.

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

      - 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: >
          The processing method used for the order (e.g., checkout, manual, express). Indicates how the order was created and processed in the source system.

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

      - 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_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: >
          Province/state of the shipping address; format varies by country and platform.

      - name: order_shipping_zip_code
        description: >
          The postal code of the shipping address.

      - 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_updated_at
        description: >
          UTC timestamp from source system when the order was last modified.

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

      - name: order_vendors_csv
        description: >
          Comma-separated list of vendors associated with products in the order. Used for multi-vendor order analysis and vendor performance tracking.

      - name: primary_order_payment_gateway
        description: >
          The technology or service that securely transmitted payment information between the customer, the business, and the payment processor. Special Considerations: For marketplaces (e.g., Amazon), gateway naming and presence may vary from direct-to-consumer platforms.

      - 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 dim_customers (many:1 - multiple orders per customer). Special Considerations: Some platforms (e.g., TikTok Shop) may provide limited linkage, resulting in NULLs for certain orders.

      - 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 dim_order_lines via sm_order_key (1:many), dim_customers via sm_customer_key (many: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: 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_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.