- Grain: One row per (sm_store_id, sm_channel, sm_sub_channel, date).
- Date field: date.
- Filters to start with: sm_channel for channel-specific KPIs; date for time windows.
- Joins: none; drill down to obt_orders using (date, sm_channel) filters.
Unfiltered SQL sums every delivered channel, including
excluded, draft_orders, exchanged and any *_via_shopify rows. The column dictionary’s note that excluded is omitted does not apply a filter to your query. Start with sm_channel = 'online_dtc' to match the Looker Studio dashboards’ default, or explicitly choose the channels you want. All-channel totals can include overlapping channel representations.Query examples
Columns
| Column | Type | Description |
|---|---|---|
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. |
active_subscriber_count | INT64 | The number of customers who have at least one active subscription. |
active_subscription_count | INT64 | The number of active subscriptions with a store. |
ad_clicks | INT64 | The number of times ads were clicked. |
ad_impressions | INT64 | The number of times ads were shown. |
ad_platform_reported_conversions | FLOAT64 | The number of conversions attributable to ads, as reported by the advertising platform. |
ad_platform_reported_revenue | FLOAT64 | The amount of revenue attributable to the ads, as reported by the advertising platform. Google Ads, Meta, TikTok, AppLovin, and Snapchat use the workspace reporting currency when reporting-currency conversion is enabled. Amazon Ads revenue is always in the store currency (Currency & region). Other sources stay in the account currency. A total across channels can mix these currencies. |
ad_spend | NUMERIC | The amount spent on ads. Google Ads, Meta, TikTok, AppLovin, and Snapchat use the workspace reporting currency when reporting-currency conversion is enabled. Amazon Ads spend is always in the store currency (Currency & region). Other sources stay in the account currency. A total across channels can mix these currencies. |
cancelled_passive_subscription_count | INT64 | The number of subscriptions that were terminated without active customer initiation, often due to reasons like the natural expiration of the subscription period. |
cancelled_subscriber_count | INT64 | The number of customers who cancelled their final subscription (they do not have any active subscriptions). |
cancelled_subscriber_count_daily_snapshot | INT64 | The cumulative number of customers who have cancelled their final subscription (daily snapshot). |
cancelled_subscription_count | INT64 | The number of subscriptions voluntarily cancelled by customers. |
cancelled_subscription_count_daily_snapshot | INT64 | The cumulative number of subscriptions that have been cancelled (daily snapshot). |
contribution_profit | FLOAT64 | Gross profit minus ad spend. |
customer_count | INT64 | The number of unique customers who made a purchase. |
date | DATE | The date in YYYY-MM-DD format. |
gross_profit | FLOAT64 | Net order revenue and shipping revenue minus order product cost, order shipping cost, order fulfillment cost, and merchant processing fees. |
net_profit | FLOAT64 | Gross profit minus ad spend and operating expenses. |
new_customer_count | INT64 | The number of unique customers who made their first purchase. Special Considerations: Platform coverage varies; tests allow for low coverage thresholds in some cases. |
new_customer_order_count | INT64 | The number of orders from new customers. |
new_customer_order_gross_revenue | NUMERIC | The gross order revenue from new customers. |
new_customer_order_net_revenue | NUMERIC | The net order revenue from new customers. |
new_subscriber_count | INT64 | The number of customers who converted on their first subscription program. |
new_subscription_count | INT64 | The number of new subscriptions started by customers. |
operating_expenses | FLOAT64 | The cost the business incurs while performing its normal operational activities. This data is entered into the Financial Cost - Operating Expenses tab of the SourceMedium financial cost configuration sheet. |
order_count | INT64 | The number of orders placed by customers. |
order_discounts | NUMERIC | The total amount of discounts applied to orders. |
order_fulfillment_cost | NUMERIC | 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. |
order_gross_revenue | NUMERIC | The gross revenue for orders, based on order 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. |
order_gross_revenue_after_discounts | NUMERIC | The gross order revenue minus order discounts. |
order_merchant_processing_fees | FLOAT64 | The payment processing fees for orders. This data is entered in the Financial Cost - Merchant Processing Fees tab of the SourceMedium financial cost configuration sheet. |
order_net_revenue | NUMERIC | The gross order revenue minus order discounts and refunds. Uses the workspace reporting currency when reporting-currency conversion is enabled. |
order_net_shipping_revenue | NUMERIC | The gross shipping revenue for orders minus shipping discounts and shipping refunds. |
order_product_cost | NUMERIC | The landed cost of orders 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_refunds | NUMERIC | The total amount of refunds applied to orders. |
order_return_cost | NUMERIC | The blended cost 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. |
order_shipping_cost | NUMERIC | The blended cost 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. |
order_total_revenue | NUMERIC | Total order revenue after factoring in shipping revenue, taxes collected, discounts, and refunds. Uses the workspace reporting currency when reporting-currency conversion is enabled. |
order_total_taxes | NUMERIC | The amount of order and shipping taxes minus order and shipping tax refunds. |
product_gross_profit | NUMERIC | Net order line revenue minus product cost. |
repeat_customer_count | INT64 | The number of unique customers who made at least their second purchase. |
repeat_customer_order_count | INT64 | The number of orders from repeat customers. |
repeat_customer_order_gross_revenue | NUMERIC | The gross order revenue from repeat customers. Special Considerations: Platform aggregation rules can differ; see tests for permitted thresholds. |
repeat_customer_order_net_revenue | NUMERIC | The net order revenue from repeat customers. |
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_sub_channel | STRING | Sub-channel from source/tags with config overrides (e.g., Facebook & Instagram, Google, Amazon FBA/Fulfilled by Merchant). |
target_ad_spend | FLOAT64 | The ad spend target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_average_order_value | FLOAT64 | The average order value target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_customer_acquisition_cost | FLOAT64 | The customer acquisition cost target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_order_conversion_rate | FLOAT64 | The order conversion rate target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_order_count | FLOAT64 | The order count target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_order_gross_revenue | FLOAT64 | The gross order revenue target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_order_net_revenue | FLOAT64 | The net order revenue target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_return_on_ad_spend | FLOAT64 | The return on ad spend target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_total_order_revenue | FLOAT64 | The total order revenue target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
target_website_sessions | FLOAT64 | The website sessions target for the store. This data is entered in the Targets tab of the SourceMedium configuration sheet. |
website_sessions | INT64 | The number of sessions on the website. |
Supported order metrics use the workspace reporting currency when reporting-currency conversion is enabled. Manually configured costs and targets should be entered in that same currency. Ad spend and platform-reported ad revenue on this table come from ad performance. Google Ads, Meta, TikTok, AppLovin, and Snapchat use the workspace reporting currency when that conversion is enabled. Amazon Ads spend and revenue stay in the store currency (Currency & region), including when that conversion is off, and they are not converted again. Your store currency can differ from the workspace reporting currency. Other advertising sources stay in their account currency. A total across channels can mix these currencies. Contribution profit subtracts ad spend from gross profit on the same row, so an Amazon row can mix store-currency ad spend with order gross profit in the workspace reporting currency. See Reporting Currency.