Ad Spend

This SQL example helps you find ad cost, impressions and clicks

SELECT
  SUM(cost) AS Cost,
  SUM(impressions) AS Impressions,
  SUM(clicks) AS Clicks
FROM
  `table.spend`
WHERE
  timestamp >= '2025-05-01'AND timestamp < '2025-06-01'

This SQL example helps you find ad cost by channel and the % of total

SELECT
  adNetwork AS Channel,
  SUM(cost) AS Cost,
  ROUND(SUM(cost) / SUM(SUM(cost)) OVER () * 100, 2) AS Percentage
FROM
  `table.spend`
WHERE
  timestamp >= '2025-05-01' AND timestamp < '2025-06-01'
GROUP BY
  adNetwork

This SQL example helps you find ad cost, impressions, clicks, CPM, CTR and CPC per Channel

  SELECT
 adNetwork AS Channel,
 SUM(cost) AS Cost,
 SUM(impressions) As Impressions,
 SUM(clicks) AS Clicks,
 SAFE_DIVIDE(SUM(cost), SUM(impressions)/1000) AS CPM,
	SAFE_DIVIDE(SUM(clicks), SUM(impressions)/100)AS CTR,
	SAFE_DIVIDE(SUM(cost), SUM(clicks))AS CPC
FROM
  `table.spend`
WHERE
 timestamp >= '2025-05-01' AND timestamp < '2025-06-01'
GROUP BY
 adNetwork
ORDER BY cost DESC

This SQL example helps you find ad cost, impressions, clicks, CPM, CTR and CPC per Channel over time

SELECT
 adNetwork AS Channel,
 FORMAT_TIMESTAMP('%Y-%m-%d', timestamp) AS Month,
 SUM(cost) AS Cost,
 SUM(impressions) As Impressions,
 SUM(clicks) AS Clicks,
 SAFE_DIVIDE(SUM(cost), SUM(impressions)/1000) AS CPM,
	SAFE_DIVIDE(SUM(clicks), SUM(impressions)/100)AS CTR,
	SAFE_DIVIDE(SUM(cost), SUM(clicks))AS CPC
FROM
 `table.spend`
WHERE
 timestamp >= '2025-05-01' AND timestamp < '2025-06-01'
GROUP BY
 adNetwork,timestamp
ORDER BY month DESC