How to calculate CPM in SQL

Why marketing analysts compute CPM in SQL

CPM, cost per mille, is the foundation metric for any brand or awareness campaign, and it shows up in the first slide of a marketing review at companies like Meta, Snap, Netflix, and Stripe. Performance marketers will tell you they only care about CPA or ROAS, but the moment a CFO asks why awareness spend grew thirty percent year over year, the answer is some flavor of CPM going up.

The metric looks trivial — total spend, divided by impressions, times one thousand. The trap is what counts as an impression, what counts as spend, and whether you slice by served, viewable, or unique reach. A dashboard built on the wrong definition will tell a brand manager the auction is cheap when viewable CPM has actually doubled.

This post is a playbook of seven SQL recipes for the CPM questions a marketing analyst will see in interviews at Stripe, DoorDash, Snowflake, and Vercel. Every query runs in Postgres and ports to Snowflake, BigQuery, and Redshift with minor edits.

Formula and data model

The base definition fits on one line.

CPM = spend / impressions * 1000

The thousand multiplier comes from "per mille" — cost per thousand readers in print media. Auction platforms inherited it because individual impression prices are too small to compare cleanly. Two- and three-cent impressions look identical on a chart; twenty- and thirty-dollar CPMs do not.

Every example below assumes a single fact table populated by the ad platform connectors.

ad_performance (
  date,
  channel,           -- 'meta', 'google', 'linkedin', 'tiktok', 'ctv', 'programmatic'
  placement,         -- 'feed', 'stories', 'reels', 'search', 'display', 'pre_roll'
  campaign_id,
  spend,             -- in account currency, already normalized to USD
  impressions,       -- served impressions from the platform
  viewable_impressions,  -- MRC-compliant viewable count
  clicks,
  reach,             -- unique users exposed (when the platform supplies it)
  conversions
)

Real warehouses have one row per campaign per placement per day. If your modeling layer aggregates earlier, swap ad_performance for the daily rollup — the math is identical.

Base weekly CPM

Most awareness dashboards start with a single rollup by ISO week. Daily CPM is too noisy because budgets are often weekly and impression delivery is lumpy by hour of day.

SELECT
    DATE_TRUNC('week', DATE) AS week,
    SUM(spend) AS spend,
    SUM(impressions) AS impressions,
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0) AS cpm
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Two pieces matter here. The NULLIF(SUM(impressions), 0) guards against weeks with zero delivery, which happens during platform outages or budget pauses. The explicit NUMERIC cast forces Postgres to do floating-point division rather than truncating to an integer when spend is stored as cents. In Snowflake, cast spend to NUMBER(20,6); in BigQuery, an explicit CAST(... AS FLOAT64) is clearer at code review time.

CPM by channel and placement

The single most common follow-up question in a Meta marketing analyst loop is "show me CPM by surface for the last thirty days, with CTR next to it." The answer is a one-pass aggregation.

SELECT
    channel,
    placement,
    SUM(spend) AS spend,
    SUM(impressions) AS impressions,
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0) AS cpm,
    SUM(clicks) AS clicks,
    SUM(clicks)::NUMERIC * 100 / NULLIF(SUM(impressions), 0) AS ctr_pct
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY channel, placement
ORDER BY cpm;

Sorting by cpm ascending lets the analyst spot cheap inventory first, which is what a performance-leaning brand team wants. For a CMO who cares about premium reach, sort descending instead.

A subtle bug to watch for: when one channel runs on a CPC bid and another on a CPM bid, comparing raw CPM across them flatters the CPC channel. Performance buyers only pay on the click, so impressions are effectively a free byproduct. The next section handles this with effective CPM.

Effective CPM for CPC campaigns

When a campaign is bought on a CPC model but the brand team needs to compare it to CPM-bought awareness inventory, the right normalization is effective CPM, written eCPM. The formula is the same — total spend per thousand impressions — but the bid model is different, so the number means something different.

SELECT
    campaign_id,
    SUM(spend) AS spend,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks,
    SUM(spend)::NUMERIC / NULLIF(SUM(clicks), 0) AS cpc,
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0) AS ecpm
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY campaign_id
ORDER BY ecpm DESC;

A search campaign with a four-dollar CPC and a two percent click-through rate has an eCPM of eighty dollars, which sounds expensive next to a five-dollar display CPM. The right read is that search pays eighty dollars per thousand views for clicks that convert at ten times the rate of display. The numbers are not directly comparable unless you also blend in conversion rates.

CPM trend with a 4-week moving average

Auction CPMs are noisy week to week. Apple changes ATT prompts, Google rolls out a new bidding default, a competitor pauses, and CPM jumps fifteen percent. A four-week moving average smooths the spike enough to show the underlying drift.

WITH weekly AS (
    SELECT
        DATE_TRUNC('week', DATE) AS week,
        SUM(spend)::NUMERIC AS spend,
        SUM(impressions)::NUMERIC AS impressions
    FROM ad_performance
    WHERE DATE >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    week,
    spend * 1000 / NULLIF(impressions, 0) AS cpm,
    AVG(spend * 1000 / NULLIF(impressions, 0)) OVER (
        ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS cpm_ma4w
FROM weekly
ORDER BY week;

The CTE pattern matters. Computing the ratio inside the window function on raw fact rows would average ratios of ratios, which is a different metric. Aggregating to weekly first and then taking the moving average of the weekly ratio keeps the math defensible. For a year-over-year view, add a second window with LAG(cpm, 52) and compute the percent delta in the outer SELECT.

Frequency-adjusted CPM and CPM per unique reach

Raw CPM treats the tenth impression to the same person as equal to the first impression to a new person. For a brand awareness campaign, the tenth is worth a fraction of the first. The standard adjustment is CPM per unique reach, sometimes called cost per reach point.

SELECT
    campaign_id,
    SUM(spend) AS spend,
    SUM(impressions) AS impressions,
    SUM(reach) AS reach,
    SUM(impressions)::NUMERIC / NULLIF(SUM(reach), 0) AS frequency,
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(impressions), 0) AS cpm,
    SUM(spend)::NUMERIC * 1000 / NULLIF(SUM(reach), 0) AS cost_per_reach_point
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
  AND reach IS NOT NULL
GROUP BY campaign_id
HAVING SUM(reach) > 0
ORDER BY cost_per_reach_point;

The frequency column is the average number of impressions per unique user. Brand teams target three to five for upper-funnel awareness; anything above seven is usually wasted spend on people who already remember the ad. Pair this with a frequency histogram if the warehouse has user-level exposure logs.

One caveat: cross-platform reach is hard. Reach numbers from Meta, Google, and CTV cannot be summed without double-counting users who saw the ad on more than one. If a marketing mix model is the source of truth, use its deduplicated reach rather than summing platform reach in SQL.

Common pitfalls

The first trap is mixing served impressions with viewable impressions in the same denominator. Platforms report both, and they differ by twenty to forty percent depending on placement. A dashboard that quietly switches from served to viewable mid-quarter will show CPM jumping for no real reason. The fix is to pick one denominator at the modeling layer and document it in the table comment.

The second trap is forgetting the thousand multiplier and ending up with cost per impression instead of cost per mille. The numbers look reasonable at first glance, and the bug survives until a finance partner asks why benchmarks look off by three orders of magnitude. A defensive habit is to keep the multiplier in the column expression rather than precomputing it, so anyone reading the SQL can audit the unit.

The third trap is treating bot traffic as real impressions. Invalid traffic, or IVT, can account for ten to thirty percent of programmatic impressions on long-tail sites. If the warehouse loads raw platform numbers without an IVT filter, CPM looks artificially low. The mitigation is either to filter on a verification vendor flag at the modeling layer, or to compute a separate clean_cpm that excludes flagged inventory.

The fourth trap is comparing CPM across very different formats without normalization. A six-second pre-roll, a static feed image, and a thirty-second connected-TV spot all report impressions, but they cost wildly different amounts and deliver wildly different attention. Slicing CPM by placement is the minimum; for an executive view, attach a quality score from the measurement vendor.

The fifth trap is averaging weekly CPMs to report a quarterly number. Averaging ratios drops the spend-weighting and inflates cheap, low-volume weeks. The right move is to sum spend and impressions across the quarter and then divide, which is what the base queries above already do at the daily-to-weekly rollup.

Optimization tips

The fact table grows fast — a mid-size advertiser produces one to ten million rows per month — so partition ad_performance on date and cluster on channel. Postgres handles this with declarative partitioning and a BRIN index on date; Snowflake and BigQuery do the equivalent automatically once the table is clustered on (channel, date). Range scans on the last ninety days will then read a fraction of the table.

Pre-aggregate to weekly or daily granularity for any dashboard that does not need campaign-level slicing. A materialized view keyed on (week, channel, placement) is twenty to a hundred times faster than the raw fact table. Schedule the refresh after the ad platform ETL completes — typically eight hours after midnight UTC — and dashboards stay fresh without ad-hoc reaggregation.

For interview settings, mention window functions explicitly. CPM-trend, year-over-year, and exponential smoothing all use the same OVER (ORDER BY week ROWS BETWEEN ...) pattern, and showing that you understand row-frame versus range-frame windowing is a senior signal.

Related reading

• How to calculate CAC in SQL
• How to calculate CAC by channel in SQL
• SQL window functions interview questions

If you want to drill marketing-analytics SQL questions like this every day, NAILDD is launching with hundreds of SQL problems built exactly around these patterns.

FAQ

What is a typical CPM range by channel?

Display and programmatic clears at one to ten dollars CPM in 2026, depending on geo and audience. Meta feed and Reels run five to fifteen for broad targeting and twenty to forty for narrow B2B audiences. LinkedIn sits at twenty to sixty because the targeting graph is unique. Premium video and connected TV land between fifteen and fifty, with sports and news at the top. Pull last quarter's numbers from your own warehouse before quoting any of these.

What is the difference between CPM, CPC, and CPA?

CPM is cost per thousand impressions, CPC is cost per click, CPA is cost per acquisition. The metric and bid model are different — a campaign can bid on CPC and still be measured in eCPM. The auction risk sits with the advertiser at CPM (you pay regardless of click), and with the platform at CPA (the platform must deliver an outcome).

Why does eCPM exist if it is just CPM?

The term CPM is reserved by convention for inventory bought on a CPM model. When a CPC-bid or CPA-bid campaign needs to be compared against CPM-bid inventory on the same axis, calling the derived number "CPM" causes confusion in cross-team reviews. Effective CPM signals that the metric is back-derived from spend and impressions rather than purchased directly. Auction platforms also use eCPM internally to rank bids across mixed bid types.

Why is my CPM increasing month over month?

Three causes dominate. Auction competition rises when a major advertiser doubles its budget on the same audience. Targeting tightness raises price because narrower audiences mean less inventory and the platform serves the highest-bid slot first. Seasonality is the third — Q4 retail CPMs are routinely two to three times Q1 rates because every consumer brand floods the auction. Diagnose by holding audience and creative constant and looking at week-over-week delta.

When should I use cost per reach instead of CPM?

Use cost per reach when the campaign objective is brand awareness rather than message frequency. Awareness campaigns hit diminishing returns above a frequency of five to seven impressions per user, so additional impressions to the same person dilute CPM but do nothing for the objective. Performance campaigns, by contrast, often benefit from frequency because conversion lifts with repeated exposure. The decision rule is simple — if the brief is "how many people saw this," use cost per reach; if it is "how many times did people see this," use CPM.

Is lower CPM always better?

No, and treating it that way is the most common mistake juniors make. Cheap impressions on long-tail inventory often have low viewability, high IVT, and weak attention. A two-dollar CPM that delivers half-second views on a made-for-advertising site is worse than a twenty-dollar CPM on premium video where viewers watch the full ad. The correct frame is cost per unit of attention or cost per qualified outcome.