Skip to main content
Use the same query standards from the SQL Query Library overview: is_order_sm_valid = TRUE for order analyses, sm_store_id scoping for multi-store setups, and your_project placeholders.
When reporting-currency conversion is enabled, ad_spend and platform-reported revenue fields use your workspace reporting currency for Google Ads, Meta, TikTok, Snapchat, AppLovin, MNTN, Vibe, AdRoll, Outbrain, Taboola, Pepperjam, and Criteo. Accounts with different source currencies are converted separately before totals are combined. Amazon Ads amounts are always in the currency on your SourceMedium store (Currency & region), whether or not that conversion is enabled, and they are not converted a second time. Other advertising sources stay in their account currency. A total across channels can mix these currencies. See Reporting Currency.
Click/impression-based ratios such as CPC, CTR, and CPM are only meaningful over rows that report clicks or impressions. TikTok LIVE GMV Max campaigns, and Product GMV Max campaigns before 2024-09-27, carry spend with empty clicks and impressions. Divide only the spend on rows that report them, as the SourceMedium cpm and cpc metrics do.
What you’ll learn: Ad spend per new customer, split by where the sale happened (sm_channel is the commerce sales channel: online DTC, Amazon, retail, wholesale, TikTok Shop). This is blended CAC: it divides all ad spend by all new customers, including customers who arrived with no ad spend behind them. For per-platform efficiency, use the ROAS template below.
What you’ll learn: Which ad platform and campaign type combinations are generating the best return on ad spend. Helps you decide where to allocate more budget and which underperforming campaigns to optimize or cut.
What you’ll learn: Which TikTok campaigns have spend, clicks, and impressions available. Use this before calculating CPC, CTR, or CPM across TikTok so spend without clicks or impressions does not distort them.
For CPC and CPM that include GMV Max, divide SUM(IF(ad_impressions IS NOT NULL OR ad_clicks IS NOT NULL, ad_spend, 0)) instead of SUM(ad_spend). That keeps rows that report impressions or clicks, including rows that report 0. For TikTok Ads only, add AND ad_campaign_type IS DISTINCT FROM 'gmv_max' to the WHERE clause (IS DISTINCT FROM keeps rows with no campaign type).

Back to the SQL Query Library.

Messaging

Compare paid performance with owned-channel outcomes.

Orders & Revenue

Connect media efficiency to order and revenue quality.

Attribution & Data Health

Validate tracking quality before channel-level decisions.