Как посчитать CPC в SQL

Зачем CPC

CPC = Cost Per Click. Базовый input для CAC, ROAS. Auction-based рекламные системы (Google, Meta) бьют по CPC bids. Понимание CPC tracking = понимание marketing economics.

Формула

CPC = total_ad_spend / total_clicks

Базовый расчёт

SELECT
    DATE_TRUNC('day', DATE) AS day,
    SUM(spend) AS total_spend,
    SUM(clicks) AS total_clicks,
    SUM(spend)::NUMERIC / NULLIF(SUM(clicks), 0) AS cpc
FROM ad_performance
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

По каналам

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

Blended CPC

«Blended» = total spend / total clicks across all channels including organic:

WITH paid AS (
    SELECT SUM(spend) AS spend, SUM(clicks) AS clicks
    FROM ad_performance
    WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
),
organic AS (
    SELECT 0 AS spend, SUM(visits) AS clicks
    FROM web_traffic
    WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
      AND source = 'organic'
)
SELECT
    (paid.spend + organic.spend) AS total_spend,
    (paid.clicks + organic.clicks) AS total_clicks,
    (paid.spend + organic.spend)::NUMERIC / NULLIF(paid.clicks + organic.clicks, 0) AS blended_cpc
FROM paid, organic;

Blended CPC показывает true cost per visitor (учётом «free» organic).

Trend

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

CPC растёт = либо competition outbidding, либо CTR падает (Quality Score).

Частые ошибки

Ошибка 1. Auction-by-auction vs aggregate. Average CPC ≠ what you bid. You bid max, pay second-price.

Ошибка 2. Click definition. «All clicks» vs «link clicks». Diff на Meta.

Ошибка 3. Click fraud. Filter bot clicks. Inflated CPC denominator.

Ошибка 4. Tax / fees. Some platforms charge VAT on spend. Add or no?

Ошибка 5. Multi-currency. USD spend в RUB campaign. Convert correctly.

Связанные темы

• Как посчитать CTR в SQL
• Как посчитать CAC в SQL
• Как посчитать ROAS в SQL
• Как посчитать marketing ROI в SQL

FAQ

Какой CPC ok?

Зависит от vertical. SaaS B2B: $5-50. E-com: $0.5-3. Tech keywords: $20+.

CPC растёт — что делать?

1. Improve CTR (Quality Score → lower CPC). 2) Refine targeting. 3) Different creative.

CPC vs CPM?

CPC — pay per click. CPM — pay per 1000 impressions. CPM × CTR / 100 = CPC.

Blended CPC зачем?

Включает organic «cost» (контент, SEO investment). True marketing efficiency.

Avg CPC vs max bid?

Max bid — ceiling. Actual CPC обычно lower (second-price auction).