obt_order_lines

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.
Filters to start with: is_order_sm_valid = TRUE (exclude test/cancelled orders); sm_channel for segmentation.
Joins: obt_orders via sm_order_key (many:1); dim_product_variants via sm_product_variant_key (many:1).

When to use this table

Use obt_order_lines for product questions: best sellers, units sold, revenue by SKU, product type, or vendor, and what customers buy together. Each row is one line on one order, so summing order_line_net_revenue across a store gives the same total as order_net_revenue in obt_orders. Use obt_orders when the question is about whole orders. Always filter is_order_sm_valid = TRUE unless you deliberately want cancelled, test, or excluded orders.

Example query

Top products by net revenue over the last 90 days:

SELECT
  sku,
  ANY_VALUE(product_title) AS product_title,
  SUM(order_line_quantity) AS units,
  SUM(order_line_net_revenue) AS net_revenue
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1
ORDER BY net_revenue DESC
LIMIT 25

More query examples

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 obt_customers (many:1 - multiple order lines per customer).
`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. Foreign key to obt_orders (many:1 - multiple order lines per order).
`sm_order_line_key`	STRING	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.
`sm_product_variant_key`	STRING	Stable SourceMedium join key for product variants to related tables.
`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.
`customer_id`	STRING	The ID of the customer who placed the order.
`customer_tags_csv`	STRING	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.
`earliest_order_refund_date`	DATE	The date of the first refund associated with the order line.
`is_order_line_currency_canonicalized`	BOOL	True when canonical order-line money fields use the converted values for this row.
`is_order_line_subscription`	BOOL	Whether the order line is a subscription line. Use this instead of string matching when you want a stable filter for subscription lines.
`is_order_only_gift_cards`	BOOL	Whether the order associated with the line item only contains gift cards.
`is_order_sm_valid`	BOOL	True if order is not voided, cancelled, uncollectible, draft, or refunded, and not cancelled for fraud or a declined payment (excluded even if still marked paid). Use WHERE is_order_sm_valid = TRUE to exclude test/invalid orders from revenue.
`is_product_gift_card`	BOOL	Whether the product is a gift card.
`latest_order_refund_date`	DATE	The most recent date a refund was processed for an order.
`order_cart_quantity`	NUMERIC	The quantity of items that were originally purchased in an order.
`order_created_at_local_datetime`	DATETIME	Order created timestamp converted to reporting timezone (from order_created_at UTC).
`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	An ordered index that can be used to identify the sequential position of an order relative to a customer’s order history.
`order_line_allocated_shipping`	NUMERIC	Net shipping revenue allocated across product lines by quantity. The summary row receives 0, or the full amount if no line can carry a share, preserving the per-order sum. Used by order_line_gross_profit and the legacy product_performance allocated_shipping column.
`order_line_converted_currency_code`	STRING	Target reporting currency resolved by the currency conversion runtime for this order line. For excluded sources, this can represent the desired target currency while money fields remain unchanged.
`order_line_converted_discounts`	NUMERIC	Order-line discounts converted using the applied FX rate.
`order_line_converted_duty_refunds`	NUMERIC	Duty refunds assigned to the line converted using the applied FX rate.
`order_line_converted_gross_duties`	NUMERIC	Gross duties assigned to the line converted using the applied FX rate.
`order_line_converted_gross_revenue`	NUMERIC	Gross order-line revenue converted using the applied FX rate.
`order_line_converted_gross_shipping`	NUMERIC	Gross shipping revenue on the line converted using the applied FX rate.
`order_line_converted_gross_shipping_taxes`	NUMERIC	Gross shipping taxes on the line converted using the applied FX rate.
`order_line_converted_gross_taxes`	NUMERIC	Gross order-line taxes converted using the applied FX rate.
`order_line_converted_net_duties`	NUMERIC	Net duties assigned to the line converted using the applied FX rate.
`order_line_converted_net_revenue`	NUMERIC	Net order-line revenue converted using the applied FX rate.
`order_line_converted_net_revenue_before_refunds`	NUMERIC	Gross line revenue less discounts, before refunds, converted using the applied FX rate.
`order_line_converted_net_shipping`	NUMERIC	Net shipping revenue on the line converted using the applied FX rate.
`order_line_converted_net_shipping_taxes`	NUMERIC	Net shipping taxes on the line converted using the applied FX rate.
`order_line_converted_net_taxes`	NUMERIC	Net order-line taxes converted using the applied FX rate.
`order_line_converted_refunds`	NUMERIC	Order-line refunds converted using the applied FX rate.
`order_line_converted_shipping_discounts`	NUMERIC	Shipping discounts on the line converted using the applied FX rate.
`order_line_converted_shipping_refunds`	NUMERIC	Shipping refunds on the line converted using the applied FX rate.
`order_line_converted_shipping_tax_refunds`	NUMERIC	Shipping tax refunds on the line converted using the applied FX rate.
`order_line_converted_tax_refunds`	NUMERIC	Order-line tax refunds converted using the applied FX rate.
`order_line_converted_total_discounts`	NUMERIC	Total discounts assigned to the line converted using the applied FX rate.
`order_line_converted_total_refunds`	NUMERIC	Total refunds assigned to the line converted using the applied FX rate.
`order_line_converted_total_revenue`	NUMERIC	Total line revenue including shipping and taxes converted using the applied FX rate.
`order_line_converted_total_taxes`	NUMERIC	Total line and shipping taxes, net of refunds, converted using the applied FX rate.
`order_line_currency_canonicalization_status`	STRING	Safety status for replacing canonical order-line money fields with converted values.
`order_line_currency_conversion_currency_code`	STRING	Currency code that describes the current canonical order-line money fields. For excluded sources, this remains the original currency where known.
`order_line_currency_conversion_effective_date`	DATE	Date used to choose the daily FX rate for order-line currency conversion readiness.
`order_line_currency_conversion_mapping_from_currency`	STRING	Configured original currency from the mapping that applied to this order line, or NO_MAPPING.
`order_line_currency_conversion_mapping_id`	STRING	Source/account mapping identifier used for this order line, or NO_MAPPING.
`order_line_currency_conversion_mapping_source`	STRING	Source of the mapping used for this order line, such as operator override or generated mapping.
`order_line_currency_conversion_mapping_to_currency`	STRING	Configured target currency from the mapping that applied to this order line, or NO_MAPPING.
`order_line_currency_conversion_rate_applied`	FLOAT64	Multiplicative rate that would be applied by the currency conversion runtime. Canonical obt_order_lines money fields are not converted by this metadata-only slice.
`order_line_currency_conversion_resolution_method`	STRING	Method used to resolve original currency for this order line, or NO_MAPPING.
`order_line_currency_conversion_settings_version`	STRING	Published store-level currency conversion settings version used for this order line, or NOT_ENABLED.
`order_line_currency_conversion_status`	STRING	Runtime status explaining whether the order line is convertible, passthrough, not enabled, blocked by missing configuration/FX coverage, or excluded from current runtime scope.
`order_line_currency_mismatch_flag`	BOOL	True when source-provided order currency conflicts with the configured mapping currency.
`order_line_discount_codes_csv`	STRING	A list of discount codes applied to an order.
`order_line_discounts`	NUMERIC	The amount of discounts applied to an order line.
`order_line_duty_refunds`	NUMERIC	The amount of duty refunds applied to an order line.
`order_line_fulfillment_cost`	NUMERIC	Fulfillment COGS allocated from the order to product lines by quantity. The summary row receives 0, or the full amount if no line can carry a share, preserving the per-order sum. Sourced from the Financial Cost - Fulfillment tab of the SourceMedium financial cost configuration sheet.
`order_line_gross_duties`	NUMERIC	The amount the customer paid in duties for an order.
`order_line_gross_profit`	FLOAT64	Net order line revenue and shipping revenue minus order line product cost, order shipping cost, order fulfillment cost, and merchant processing fees.
`order_line_gross_revenue`	NUMERIC	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.
`order_line_gross_shipping`	NUMERIC	The amount the customer paid in shipping for an order shipping line. This does not include shipping tax.
`order_line_gross_shipping_taxes`	NUMERIC	The gross shipping tax for an order shipping line.
`order_line_gross_taxes`	NUMERIC	The amount of taxes associated with an order line, after discounts and before returns.
`order_line_id`	STRING	The ID of the order line.
`order_line_merchant_processing_fees`	FLOAT64	Payment fees allocated to product lines by gross-sales share. The summary row receives 0, or the full amount if no line can carry a share, preserving the per-order sum. Sourced from the Financial Cost - Merchant Processing Fees tab of the SourceMedium financial cost configuration sheet.
`order_line_net_duties`	NUMERIC	The gross order line duties minus order duty refunds.
`order_line_net_quantity`	NUMERIC	The quantity of items that were originally purchased for an order line minus the quantity of items refunded.
`order_line_net_revenue`	NUMERIC	The gross order line revenue minus order line discounts and refunds.
`order_line_net_revenue_before_refunds`	NUMERIC	The gross order line revenue minus order line discounts.
`order_line_net_shipping`	NUMERIC	The gross shipping revenue for an order shipping line minus shipping discounts and shipping refunds.
`order_line_net_shipping_taxes`	NUMERIC	The shipping tax for an order shipping line minus shipping tax refunds.
`order_line_net_taxes`	NUMERIC	The gross order line tax minus the order line tax refunds.
`order_line_original_currency_code`	STRING	Original order currency resolved by the currency conversion runtime for this order line. This metadata does not imply the canonical order-line money fields have been converted.
`order_line_original_discounts`	NUMERIC	Order-line discounts expressed in the original order currency.
`order_line_original_duty_refunds`	NUMERIC	Duty refunds assigned to the line in the original order currency.
`order_line_original_gross_duties`	NUMERIC	Gross duties assigned to the line in the original order currency.
`order_line_original_gross_revenue`	NUMERIC	Gross order-line revenue expressed in the original order currency.
`order_line_original_gross_shipping`	NUMERIC	Gross shipping revenue on the line expressed in the original order currency.
`order_line_original_gross_shipping_taxes`	NUMERIC	Gross shipping taxes on the line expressed in the original order currency.
`order_line_original_gross_taxes`	NUMERIC	Gross order-line taxes expressed in the original order currency.
`order_line_original_net_duties`	NUMERIC	Net duties assigned to the line in the original order currency.
`order_line_original_net_revenue`	NUMERIC	Net order-line revenue expressed in the original order currency.
`order_line_original_net_revenue_before_refunds`	NUMERIC	Gross line revenue less discounts, before refunds, in the original currency.
`order_line_original_net_shipping`	NUMERIC	Net shipping revenue on the line expressed in the original order currency.
`order_line_original_net_shipping_taxes`	NUMERIC	Net shipping taxes on the line expressed in the original order currency.
`order_line_original_net_taxes`	NUMERIC	Net order-line taxes expressed in the original order currency.
`order_line_original_refunds`	NUMERIC	Order-line refunds expressed in the original order currency.
`order_line_original_shipping_discounts`	NUMERIC	Shipping discounts on the line expressed in the original order currency.
`order_line_original_shipping_refunds`	NUMERIC	Shipping refunds on the line expressed in the original order currency.
`order_line_original_shipping_tax_refunds`	NUMERIC	Shipping tax refunds on the line expressed in the original order currency.
`order_line_original_tax_refunds`	NUMERIC	Order-line tax refunds expressed in the original order currency.
`order_line_original_total_discounts`	NUMERIC	Total discounts assigned to the line in the original order currency.
`order_line_original_total_refunds`	NUMERIC	Total refunds assigned to the line in the original order currency.
`order_line_original_total_revenue`	NUMERIC	Total line revenue including shipping and taxes in the original currency.
`order_line_original_total_taxes`	NUMERIC	Total line and shipping taxes, net of refunds, in the original currency.
`order_line_product_collection_handles_csv`	STRING	Unique, human-readable strings for the collections a product belongs to automatically generated from their titles.
`order_line_product_collection_titles_csv`	STRING	The titles of collections the product belongs to. Collections are groupings of products that merchants can create to make their stores easier to browse.
`order_line_product_cost`	NUMERIC	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.
`order_line_product_gross_profit`	NUMERIC	Net order line revenue minus product cost.
`order_line_quantity`	NUMERIC	The quantity of order lines that were originally purchased in an order.
`order_line_refund_quantity`	NUMERIC	The quantity of items purchased for an order line that were refunded.
`order_line_refunds`	NUMERIC	The amount of refunds applied to an order line.
`order_line_return_cost`	NUMERIC	Return-handling COGS allocated from the order to product lines by quantity. The summary row receives 0, or the full amount if no line can carry a share, preserving the per-order sum. Sourced from the Financial Cost - Shipping tab of the SourceMedium financial cost configuration sheet.
`order_line_shipping_cost`	NUMERIC	Shipping COGS allocated from the order to product lines by quantity. The summary row receives 0, or the full amount if no line can carry a share, preserving the per-order sum. Sourced from the Financial Cost - Shipping tab of the SourceMedium financial cost configuration sheet.
`order_line_shipping_discounts`	NUMERIC	The amount of discounts applied to an order shipping line.
`order_line_shipping_refunds`	NUMERIC	The amount of shipping refunds applied to an order shipping line.
`order_line_shipping_tax_refunds`	NUMERIC	The amount of shipping tax refunds applied to an order shipping line.
`order_line_tax_refunds`	NUMERIC	The amount of tax refunds applied to an order line.
`order_line_total_discounts`	NUMERIC	The amount of discounts for an order line.
`order_line_total_refunds`	NUMERIC	The amount of refunds for an order line.
`order_line_total_revenue`	NUMERIC	Total order line revenue after factoring in shipping revenue, taxes collected, discounts, and refunds.
`order_line_total_taxes`	NUMERIC	The amount of taxes for an order line minus tax refunds.
`order_line_type`	STRING	The order line classification, such as a subscription or one-time order, which is derived from order attributes, order tags, or subscription platform data, if a subscription platform has been integrated.
`order_number`	STRING	The order’s position in the shop’s count of orders.
`order_payment_status`	STRING	The financial status of an order, which indicates whether the order has been paid.
`order_processed_at`	TIMESTAMP	UTC timestamp from source system 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_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_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	The state of the shipping address.
`order_shipping_zip_code`	STRING	Postal/ZIP code of the shipping address for the order (country-dependent format; not geo-normalized). Combine with country/state for region-based analysis.
`order_tags_csv`	STRING	Comma-separated order-level tags (free-form strings set by merchant/apps). Prefer array fields for exact matching where available.
`order_to_refund_days`	INT64	Days between order processing and first refund (non-negative; NULL when no refund). Calculated from order_processed_at_local_datetime to earliest_refund_date.
`order_to_refund_months`	INT64	Months between order processing date and first refund date for the order/line; derived from days using average month length.
`order_to_refund_weeks`	INT64	Weeks between order processing date and first refund date for the order/line; derived from days and rounded per model logic.
`primary_product_image_url`	STRING	Primary product image URL (display).
`product_id`	STRING	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.
`product_tags_csv`	STRING	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.
`product_title`	STRING	The title of the product.
`product_type`	STRING	A categorization for the product used for filtering and searching for products.
`product_variant_compare_at_price`	NUMERIC	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.
`product_variant_id`	STRING	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.
`product_variant_price`	FLOAT64	The price of the product variant at the time of purchase (per unit, before discounts). Use for pricing analysis and discount effectiveness calculations.
`product_variant_title`	STRING	The title of the product variant.
`product_vendor`	STRING	The vendor of the product.
`sku`	STRING	The stock keeping unit (SKU) of the product variant.
`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_order_sales_channel`	STRING	The sales channel associated with an order, which is derived from the order source name and order tags.
`sm_order_type`	STRING	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.
`sm_sub_channel`	STRING	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.
`sm_utm_campaign`	STRING	Last-click UTM campaign from the attribution waterfall (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.
`sm_utm_id`	STRING	SourceMedium-generated unique identifier for UTM parameter combinations on this order line. Enables join to UTM attribution data for marketing campaign analysis.
`sm_utm_medium`	STRING	Last-click UTM medium from the attribution waterfall (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.
`sm_utm_source`	STRING	Last-click UTM source from the attribution waterfall (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.
`sm_utm_source_medium`	STRING	A concatenation of sm_utm_source and sm_utm_medium.
`sm_valid_order_index`	INT64	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.
`sm_valid_order_index_reversed`	INT64	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.
`sm_zero_party_attribution_source`	STRING	Attributable source from post‑purchase survey tags (e.g., Fairing/Enquire).
`source_system`	STRING	Originating platform for the record (e.g., Shopify, Amazon, TikTok Shop, Chargebee). Used for platform‑specific behavior and coverage.
`source_system_configured_product_cost`	NUMERIC	Landed cost from platform-configured product costs (not SourceMedium configuration sheet). For config-sheet costs, see user_configured_product_cost.
`subscription_id`	STRING	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.
`subscription_order_index`	INT64	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.
`subscription_order_sequence`	STRING	Whether a subscription order is the first subscription order or a repeat subscription order based on the subscription order index or order tag indicators when an index is not available.
`user_configured_product_cost`	NUMERIC	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.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

When to use this table

Example query

More query examples

Columns

​When to use this table

​Example query

​More query examples

​Columns

When to use this table

Example query

More query examples

Columns