Ad Spend
Also known as: Paid Media Spend, Media Spend, Advertising Cost, Ad Cost
Total paid media cost incurred across advertising platforms in the period, in reporting currency. Covers platform-billed media cost; excludes agency fees, creative production and staff costs.
Business Use Case
1️⃣ Performance marketing: when channel ROAS falls below target, move next week's budget to higher-return campaigns. 2️⃣ Finance: at month close, reconcile spend to ERP invoices and flag overruns against the approved media budget. 3️⃣ CMO: in quarterly planning, set channel budgets from spend versus new-user and ad-click trends.
Business Criticality
High
Journey / Navigation Area
Generic
Owning Function
Marketing
Scope
Global
Formula
Calculation Notes
1️⃣ Sum daily cost from each ad platform's cost export (Google Ads, Meta, Microsoft Ads, TikTok) on the date the media was served. 2️⃣ Exclude agency fees, creative production, martech licences, salaries and affiliate commissions; report those as separate cost lines. 3️⃣ Exclude VAT/GST and other recoverable taxes billed on top of media cost. 4️⃣ Convert each account currency to reporting currency at the spend-date rate, not the invoice-date rate. 5️⃣ Net invalid-traffic refunds and platform credits in the period issued; do not restate closed months. 6️⃣ Platform cost settles within about 3 days; re-load a trailing 7-day window on every refresh to pick up adjustments. 7️⃣ Reconcile monthly totals to ERP invoices; variances beyond the agreed tolerance go to Finance before month-end reporting.
Technical Details
Events
SQL Queries
Google Analytics 4 (BigQuery)
-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE reporting_currency STRING DEFAULT 'USD';
WITH media_cost AS (
-- Platform media cost excluding taxes and non-media cost lines, converted at the spend-date rate, bucketed by served date
SELECT
c.spend_date AS report_date,
SUM((c.cost_amount - COALESCE(c.tax_amount, 0)) * IF(c.account_currency = reporting_currency, 1, fx.rate)) AS amount
FROM `your-project.ads.ad_platform_cost_daily` AS c
LEFT JOIN `your-project.erp.fx_rates` AS fx
ON fx.rate_date = c.spend_date
AND fx.from_currency = c.account_currency
AND fx.to_currency = reporting_currency
WHERE c.spend_date BETWEEN start_date AND end_date
AND c.cost_line_type NOT IN ('agency_fee', 'creative_production', 'martech_licence', 'salary', 'affiliate_commission')
GROUP BY report_date
),
credits AS (
-- Invalid-traffic refunds and platform credits excluding taxes, counted on the date issued (closed months are not restated)
SELECT
r.issued_date AS report_date,
SUM((r.credit_amount - COALESCE(r.tax_amount, 0)) * IF(r.account_currency = reporting_currency, 1, fx.rate)) AS amount
FROM `your-project.ads.ad_platform_credits` AS r
LEFT JOIN `your-project.erp.fx_rates` AS fx
ON fx.rate_date = r.issued_date
AND fx.from_currency = r.account_currency
AND fx.to_currency = reporting_currency
WHERE r.issued_date BETWEEN start_date AND end_date
AND r.credit_type IN ('invalid_traffic_refund', 'platform_credit')
GROUP BY report_date
)
-- Daily ad spend: media cost minus credits and refunds, in reporting currency
SELECT
report_date,
ROUND(COALESCE(m.amount, 0) - COALESCE(cr.amount, 0), 2) AS ad_spend
FROM media_cost AS m
FULL OUTER JOIN credits AS cr USING (report_date)
ORDER BY report_date;Adobe Experience Platform (AEP Query Service)
-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
-- Relative reporting window and reporting currency
SELECT
DATE_SUB(CURRENT_DATE, 90) AS start_date,
DATE_SUB(CURRENT_DATE, 1) AS end_date,
'USD' AS reporting_currency
),
media_cost AS (
-- Platform media cost excluding taxes and non-media cost lines, converted at the spend-date rate, bucketed by served date
SELECT
c.spend_date AS report_date,
SUM((c.cost_amount - COALESCE(c.tax_amount, 0)) * CASE WHEN c.account_currency = p.reporting_currency THEN 1 ELSE fx.rate END) AS amount
FROM aep_backend_dataset_ad_platform_cost_daily AS c
CROSS JOIN params AS p
LEFT JOIN aep_backend_dataset_erp_fx_rates AS fx
ON fx.rate_date = c.spend_date
AND fx.from_currency = c.account_currency
AND fx.to_currency = p.reporting_currency
WHERE c.spend_date BETWEEN p.start_date AND p.end_date
AND c.cost_line_type NOT IN ('agency_fee', 'creative_production', 'martech_licence', 'salary', 'affiliate_commission')
GROUP BY c.spend_date
),
credits AS (
-- Invalid-traffic refunds and platform credits excluding taxes, counted on the date issued (closed months are not restated)
SELECT
r.issued_date AS report_date,
SUM((r.credit_amount - COALESCE(r.tax_amount, 0)) * CASE WHEN r.account_currency = p.reporting_currency THEN 1 ELSE fx.rate END) AS amount
FROM aep_backend_dataset_ad_platform_credits AS r
CROSS JOIN params AS p
LEFT JOIN aep_backend_dataset_erp_fx_rates AS fx
ON fx.rate_date = r.issued_date
AND fx.from_currency = r.account_currency
AND fx.to_currency = p.reporting_currency
WHERE r.issued_date BETWEEN p.start_date AND p.end_date
AND r.credit_type IN ('invalid_traffic_refund', 'platform_credit')
GROUP BY r.issued_date
)
-- Daily ad spend: media cost minus credits and refunds, in reporting currency
SELECT
COALESCE(m.report_date, cr.report_date) AS report_date,
ROUND(COALESCE(m.amount, 0) - COALESCE(cr.amount, 0), 2) AS ad_spend
FROM media_cost AS m
FULL OUTER JOIN credits AS cr
ON cr.report_date = m.report_date
ORDER BY report_date;Source Data
ERP
Dependencies
Prerequisite: List the dependencies required for this Metric
Dimensions:
Report Attributes
Channel Ad Platform Campaign Date Country Currency
Recommended Reporting Views
Segment Eligibility
false
Governance
Derived KPIs
Governance
GitHub
catalog/metrics/ad-spend.yml