Pro warehouses only. This table is not delivered on Foundation warehouses. On Foundation, start from
obt_orders. See Foundation and Pro.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 reason an order was cancelled, as reported by your sales platform. A reason of fraud or declined marks the order invalid (is_order_sm_valid = false), even if it still shows as paid.
- 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 in which the order was placed (e.g., USD, EUR, GBP).
- name: order_original_currency_code
description: >
Source currency used for the order's original values.
- name: order_converted_currency_code
description: >
Workspace reporting currency selected for the order.
- name: order_currency_conversion_effective_date
description: >
Date used to select the exchange rate for this order.
- name: order_currency_conversion_rate_applied
description: >
Exchange rate used to convert the order from its source currency to the reporting currency.
- name: order_currency_conversion_status
description: >
Whether the order was converted, already used the reporting currency, or could not be converted.
- 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 `sm_store_id` 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 `sm_store_id` 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_referrer_url
description: >
The URL of the site that referred the customer to the shop.
- name: order_sequence
description: >
Customer lifecycle classification: '1st_order' for new customers, 'repeat_order' for returning customers. Includes all orders (valid + invalid). Use for cohort analysis and retention reporting. See sm_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: source_system_sales_channel
description: >
Original source reported by the platform (e.g., Shopify sales channel/app name).
- 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_referrer_url.
- 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: is_subscription_order
description: >
Whether the order is a subscription order.
- 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 source hierarchy (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 source hierarchy (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 source hierarchy (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 source hierarchy (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 source hierarchy (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: '1st_sub_order' for initial subscription purchase, 'recurring_sub_order' for renewals, 'one_time_order' for non-subscription orders. Based on subscription order index when available, otherwise inferred from order tags. Use for subscription cohort analysis.
Use these currency fields to interpret the matching revenue values in
obt_orders. See Reporting Currency.