dim_orders

Pro warehouses only. This table is not delivered on Foundation warehouses. On Foundation, start from obt_orders. See Foundation and Pro.

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.
Filters to start with: sm_channel for channel segmentation; source_system for platform-specific behavior.
Platform caveat: TikTok Shop coverage may be limited.
Joins: dim_order_lines via sm_order_key (1:many); dim_customers via sm_customer_key (many:1).

Columns

Column	Type	Description
`sm_customer_key`	STRING	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.
`sm_order_key`	STRING	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).
`sm_store_id`	STRING	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.
`currency_conversion_mapping_from_currency`	STRING	Configured original currency from the mapping that applied to this order. Uses NO_MAPPING when no mapping applies.
`currency_conversion_mapping_id`	STRING	Source/account mapping identifier used to resolve the original and converted currency. Uses NO_MAPPING when no mapping applies.
`currency_conversion_mapping_source`	STRING	Source of the mapping used for this order, such as operator override or dynamically generated mapping. Uses NO_MAPPING when no mapping applies.
`currency_conversion_mapping_to_currency`	STRING	Configured target currency from the mapping that applied to this order. Uses NO_MAPPING when no mapping applies.
`currency_conversion_resolution_method`	STRING	Method used to resolve original currency for this order, such as record currency or operator override. Uses NO_MAPPING when no mapping applies.
`currency_conversion_settings_version`	STRING	Published store-level currency conversion settings version used for the order. Uses NOT_ENABLED when the store does not have active conversion settings.
`customer_device_type`	STRING	Device type derived from user agent (e.g., mobile, desktop, tablet). Coverage depends on website tracking; limited for marketplaces.
`customer_id`	STRING	The ID of the customer who placed the order.
`is_pending`	BOOL
`is_price_tax_inclusive`	BOOL	Whether taxes are included in the order subtotal.
`is_subscription_order`	BOOL	Whether the order is a subscription order.
`order_cancellation_reason`	STRING	The reason an order was cancelled (sourced from the platform, e.g. Shopify’s cancel_reason). A reason of ‘fraud’ or ‘declined’ marks the order as not sm-valid (is_order_sm_valid = false) regardless of payment status.
`order_cancelled_at`	TIMESTAMP	UTC timestamp when the order was cancelled. Null if order has not been cancelled.
`order_cancelled_at_local_datetime`	DATETIME	Order cancelled timestamp converted to reporting timezone (from order_cancelled_at UTC). Null if order has not been cancelled.
`order_cart_quantity`	NUMERIC	Total number of items (quantity across all line items) in the order cart. Sum of all order_line_quantity values for the order.
`order_checkout_id`	STRING	The ID of the checkout that the order is associated with.
`order_converted_currency_code`	STRING	Target reporting currency resolved by the currency conversion runtime. For stores without active conversion settings, this falls back to the original order currency.
`order_created_at`	TIMESTAMP	UTC timestamp when the order was created.
`order_created_at_local_datetime`	DATETIME	Order created timestamp converted to reporting timezone (from order_created_at UTC).
`order_currency_code`	STRING	Three-letter ISO 4217 currency code for the order (e.g., USD, EUR, GBP). Used for multi-currency analysis and reporting.
`order_currency_conversion_currency_code`	STRING	Currency code that should describe order-level money fields after conversion is applied. This is the converted currency for converted or passthrough rows, and the original currency for unresolved rows.
`order_currency_conversion_effective_date`	DATE	Date used to choose the daily FX rate for order currency conversion. Defaults to the order processed date when no active conversion mapping exists.
`order_currency_conversion_rate_applied`	FLOAT64	Multiplicative rate used to convert order-level money from original currency into converted currency. A value of 1 indicates passthrough, disabled conversion, or unresolved conversion.
`order_currency_conversion_status`	STRING	Runtime status explaining whether the order was converted, passed through, not enabled, or blocked by missing configuration, missing FX coverage, or a source excluded from the current runtime scope.
`order_currency_mismatch_flag`	BOOL	True when a row-level source currency is available and conflicts with the configured mapping currency. This is diagnostic only; row-level source currency still wins where available.
`order_customer_street_address`	STRING	Customer’s billing street address associated with the order. May differ from shipping address; use for billing analysis and fraud detection.
`order_id`	STRING	Platform order identifier. Not globally unique across stores; pair with `sm_store_id` and `source_system` when needed for scoping.
`order_index`	INT64	Ordered index identifying the sequential position of an order among the orders present in SourceMedium’s data for a customer (1..N, oldest = 1). Counts only ingested orders and is fully self-contained; for workspaces with incomplete ingested history (see int_shopify__incomplete_history_flags) this can understate the customer’s true lifetime position.
`order_index_reversed`	INT64	Ordered index identifying the sequential position of an order in a customer’s order history, in reverse order (newest = 1). Ranks the orders present in SourceMedium’s data.
`order_name`	STRING	Platform-rendered name (human‑readable or platform‑specific). Not a stable join key.
`order_number`	STRING	Shop-scoped sequence number assigned by the platform. Not globally unique; pair with `sm_store_id` for scoping.
`order_original_currency_code`	STRING	Original order currency resolved by the currency conversion runtime. For enabled Shopify stores, this prefers the row-level source currency; otherwise it falls back to order_currency_code.
`order_payment_status`	STRING	Financial status of the order (e.g., paid, partially_paid, partially_refunded, authorized, pending, refunded, voided, draft). Platform‑defined.
`order_processed_at`	TIMESTAMP	UTC timestamp when the order was processed.
`order_processed_at_local_datetime`	DATETIME	Order processed timestamp converted to reporting timezone (from order_processed_at UTC). Primary date field for order analytics and time-based filtering.
`order_processing_method`	STRING	The processing method used for the order (e.g., checkout, manual, express). Indicates how the order was created and processed in the source system.
`order_referrer_url`	STRING	The URL of the site that referred the customer to the shop.
`order_sequence`	STRING	Customer lifecycle classification: `1st_order` for the customer’s first order, `repeat_order` for every later order. Counts all orders; see `sm_valid_order_sequence` for valid orders only.
`order_session_browser_type`	STRING	Browser type derived from user agent (e.g., chrome, safari, firefox). Coverage depends on website tracking; limited for marketplaces.
`order_session_user_agent`	STRING	Raw user agent string. Used to derive customer_device_type and order_session_browser_type.
`order_shipping_city`	STRING	The city, town, or village of the shipping address.
`order_shipping_country`	STRING	The country of the shipping address.
`order_shipping_country_code`	STRING	The two-letter code (ISO 3166-1 format) for the country of the shipping address.
`order_shipping_state`	STRING	Province/state of the shipping address; format varies by country and platform.
`order_shipping_zip_code`	STRING	The postal code of the shipping address.
`order_tags_csv`	STRING	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.
`order_updated_at`	TIMESTAMP	UTC timestamp from source system when the order was last modified.
`order_updated_at_local_datetime`	DATETIME	Order last modified timestamp converted to reporting timezone (from order_updated_at UTC).
`order_vendors_csv`	STRING	Comma-separated list of vendors associated with products in the order. Used for multi-vendor order analysis and vendor performance tracking.
`primary_order_payment_gateway`	STRING	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.
`sm_channel`	STRING	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.
`sm_default_channel`	STRING	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.
`sm_fbclid`	STRING	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. Note: For Shopify V3 orders, click IDs in `customAttributes` are used for fallback `sm_utm_source` inference only and are not persisted.
`sm_gclid`	STRING	Google Click Identifier (GCLID) from Google Ads campaigns, used to track paid search conversions. Present when order originated from Google Ads click-through. Note: For Shopify V3 orders, click IDs in `customAttributes` are used for fallback `sm_utm_source` inference only and are not persisted.
`sm_order_landing_page`	STRING	Path of the page where the buyer landed when they entered the shop (`/products/x`). Never a scheme+host URL. Checkout, invoice and cart URLs collapse to `/checkouts`, `/invoices` and `/cart`.
`sm_order_referrer_domain`	STRING	Domain derived from order_referring_site.
`sm_order_sales_channel`	STRING	Raw sales channel from Shopify specifically (e.g., ‘TikTok Shop’, ‘Instagram Shop’). Some values are used as input dimension for sm_channel mapping. See sm_channel for final classification. Others are used for analysis.
`sm_order_type`	STRING	Order classification (subscription vs one-time) derived from order attributes, tags, or subscription platforms (Shopify Subscription Contract, ReCharge, Skio, Loop, Stay AI).
`sm_sub_channel`	STRING	Sub-channel from source/tags with config overrides (e.g., Facebook & Instagram, Google, Amazon FBA/Fulfilled by Merchant).
`sm_ttclid`	STRING	TikTok Click Identifier (TTCLID) captured from the selected pre-purchase event or order referring URL. The original case-sensitive token is preserved for downstream click-log joins.
`sm_utm_campaign`	STRING	Last-click UTM campaign from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.
`sm_utm_content`	STRING	Last-click UTM content from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.
`sm_utm_id`	STRING	SourceMedium-generated unique identifier for UTM parameter combinations on this order. Used to link orders to specific marketing campaigns via UTM tracking.
`sm_utm_medium`	STRING	Last-click UTM medium from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.
`sm_utm_source`	STRING	Last-click UTM source from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). For Shopify, order-level attribution includes allowlisted order `customAttributes` and legacy `note_attributes`, including parsing aggregate `utmParams`/`GE_utmParams`; if explicit `utm_source` is missing, `sm_utm_source` may be inferred from click IDs (e.g., `gclid` → google, `fbclid` → meta). When order-level attribution is set via Shopify order `customAttributes` (V3) using `sm_utm_` or `utm_` / `(sm_)utmParams`, it is treated as an explicit override and takes precedence over the default waterfall; keys and query-string parameters are parsed in a snake/camel-case agnostic way. Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.
`sm_utm_source_medium`	STRING	Concatenation of source / medium (e.g., ‘google / cpc’). Shows ‘(none) / (none)’ when null.
`sm_utm_term`	STRING	Last-click UTM term from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces.
`source_system`	STRING	Originating platform for the record (e.g., Shopify, Amazon, TikTok Shop, Chargebee). Used for platform‑specific behavior and coverage.
`source_system_sales_channel`	STRING	Original source reported by the platform (e.g., Shopify sales channel/app name).
`subscription_order_index`	INT64
`subscription_order_sequence`	STRING	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.

Use these currency fields to interpret the matching revenue values in obt_orders. See Reporting Currency.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

Columns

​Columns

Columns