How to learn Pulling Spend Clicks Impressions Conversions for SQL in Marketing Analyst for free

Why this matters

As a Marketing Analyst, you answer daily questions like: How much did we spend? How many clicks and impressions did we get? Did those clicks convert? SQL lets you pull these core metrics reliably, across platforms and date ranges, so you can calculate CPC, CTR, CPA, and more. Getting this right avoids broken dashboards and wrong decisions.

Real tasks: daily channel performance, campaign pacing, weekly rollups, platform comparisons, and KPI alerts.
You will regularly join multiple tables, handle missing data, and compute safe ratios.

Concept explained simply

Think of each metric as a daily “fact” about a campaign. You’ll build a base grid of (date, campaign), then bring in spend, clicks, impressions, and conversions with left joins. Finally, compute ratios safely.

Mental model

Anchor set: build the complete set of (date, campaign) rows you expect to report on.
Left joins: attach each metric, allowing zeros where data is missing.
COALESCE: replace NULLs with 0 where appropriate (counts and amounts), but keep NULL for undefined ratios.
Safe division: protect against divide-by-zero for metrics like CPC and CTR.

The data model we'll use

Assume daily tables with one row per campaign and date, plus a campaign dimension:

campaigns (campaign_id, campaign_name, platform)
daily_spend (date, campaign_id, spend)
daily_clicks (date, campaign_id, clicks)
daily_impressions (date, campaign_id, impressions)
daily_conversions (date, campaign_id, conversions, revenue)

Sample rows (for examples)

campaigns
1, Brand Search, Search
2, Prospecting Social, Social

daily_spend
2024-04-01, 1, 120.00
2024-04-01, 2, 80.00
2024-04-02, 1, 100.00

daily_clicks
2024-04-01, 1, 300
2024-04-01, 2, 150
2024-04-02, 1, 260

daily_impressions
2024-04-01, 1, 15000
2024-04-01, 2, 12000
2024-04-02, 1, 14000

daily_conversions
2024-04-01, 1, 12, 600.00
2024-04-01, 2, 4, 160.00
2024-04-02, 1, 10, 520.00

Worked examples

Example 1: Pull daily spend, clicks, impressions, conversions (robust base)

Goal: return one row per (date, campaign), even when some metrics are missing.

WITH all_events AS (
  SELECT campaign_id, date::date AS dt FROM daily_spend
  UNION
  SELECT campaign_id, date::date AS dt FROM daily_clicks
  UNION
  SELECT campaign_id, date::date AS dt FROM daily_impressions
  UNION
  SELECT campaign_id, date::date AS dt FROM daily_conversions
),
filtered AS (
  SELECT ae.campaign_id, ae.dt
  FROM all_events ae
  WHERE ae.dt BETWEEN '2024-04-01' AND '2024-04-07'
),
joined AS (
  SELECT
    f.dt,
    f.campaign_id,
    COALESCE(s.spend, 0) AS spend,
    COALESCE(c.clicks, 0) AS clicks,
    COALESCE(i.impressions, 0) AS impressions,
    COALESCE(v.conversions, 0) AS conversions,
    COALESCE(v.revenue, 0) AS revenue
  FROM filtered f
  LEFT JOIN daily_spend s ON s.campaign_id = f.campaign_id AND s.date = f.dt
  LEFT JOIN daily_clicks c ON c.campaign_id = f.campaign_id AND c.date = f.dt
  LEFT JOIN daily_impressions i ON i.campaign_id = f.campaign_id AND i.date = f.dt
  LEFT JOIN daily_conversions v ON v.campaign_id = f.campaign_id AND v.date = f.dt
)
SELECT j.dt, j.campaign_id, j.spend, j.clicks, j.impressions, j.conversions, j.revenue
FROM joined j
ORDER BY j.dt, j.campaign_id;

What to notice

UNION builds the anchor set from any metric table.
COALESCE ensures zeros for missing numeric facts.
Between is inclusive of both dates in most SQL dialects.

Example 2: Compute CTR, CPC, CPA, and CVR safely

WITH base AS (
  -- use the Example 1 'joined' result
  SELECT j.*
  FROM (
    SELECT f.dt, f.campaign_id,
           COALESCE(s.spend, 0) AS spend,
           COALESCE(c.clicks, 0) AS clicks,
           COALESCE(i.impressions, 0) AS impressions,
           COALESCE(v.conversions, 0) AS conversions,
           COALESCE(v.revenue, 0) AS revenue
    FROM (
      SELECT campaign_id, date::date AS dt FROM daily_spend
      UNION SELECT campaign_id, date::date AS dt FROM daily_clicks
      UNION SELECT campaign_id, date::date AS dt FROM daily_impressions
      UNION SELECT campaign_id, date::date AS dt FROM daily_conversions
    ) f
    LEFT JOIN daily_spend s ON s.campaign_id=f.campaign_id AND s.date=f.dt
    LEFT JOIN daily_clicks c ON c.campaign_id=f.campaign_id AND c.date=f.dt
    LEFT JOIN daily_impressions i ON i.campaign_id=f.campaign_id AND i.date=f.dt
    LEFT JOIN daily_conversions v ON v.campaign_id=f.campaign_id AND v.date=f.dt
  ) j
)
SELECT
  b.dt,
  b.campaign_id,
  b.spend,
  b.clicks,
  b.impressions,
  b.conversions,
  b.revenue,
  CASE WHEN b.impressions > 0 THEN b.clicks::numeric / b.impressions ELSE 0 END AS ctr,
  CASE WHEN b.clicks > 0 THEN b.spend / b.clicks ELSE NULL END AS cpc,
  CASE WHEN b.conversions > 0 THEN b.spend / b.conversions ELSE NULL END AS cpa,
  CASE WHEN b.clicks > 0 THEN b.conversions::numeric / b.clicks ELSE 0 END AS cvr
FROM base b
ORDER BY b.dt, b.campaign_id;

Why NULL vs 0?

For ratios like CPC and CPA, 0 can be misleading when the denominator is 0. Use NULL to signal “not defined.” For rates like CTR and CVR, returning 0 is often acceptable.

Example 3: Add campaign names/platforms and roll up by platform

WITH daily AS (
  -- Build base daily metrics
  SELECT
    f.dt,
    f.campaign_id,
    COALESCE(s.spend, 0) AS spend,
    COALESCE(c.clicks, 0) AS clicks,
    COALESCE(i.impressions, 0) AS impressions,
    COALESCE(v.conversions, 0) AS conversions,
    COALESCE(v.revenue, 0) AS revenue
  FROM (
    SELECT campaign_id, date::date AS dt FROM daily_spend
    UNION SELECT campaign_id, date::date AS dt FROM daily_clicks
    UNION SELECT campaign_id, date::date AS dt FROM daily_impressions
    UNION SELECT campaign_id, date::date AS dt FROM daily_conversions
  ) f
  LEFT JOIN daily_spend s ON s.campaign_id=f.campaign_id AND s.date=f.dt
  LEFT JOIN daily_clicks c ON c.campaign_id=f.campaign_id AND c.date=f.dt
  LEFT JOIN daily_impressions i ON i.campaign_id=f.campaign_id AND i.date=f.dt
  LEFT JOIN daily_conversions v ON v.campaign_id=f.campaign_id AND v.date=f.dt
  WHERE f.dt BETWEEN '2024-04-01' AND '2024-04-07'
)
SELECT
  d.dt,
  ca.platform,
  SUM(d.spend) AS spend,
  SUM(d.clicks) AS clicks,
  SUM(d.impressions) AS impressions,
  SUM(d.conversions) AS conversions,
  SUM(d.revenue) AS revenue,
  CASE WHEN SUM(d.impressions) > 0 THEN SUM(d.clicks)::numeric / SUM(d.impressions) ELSE 0 END AS ctr,
  CASE WHEN SUM(d.clicks) > 0 THEN SUM(d.spend) / SUM(d.clicks) ELSE NULL END AS cpc,
  CASE WHEN SUM(d.conversions) > 0 THEN SUM(d.spend) / SUM(d.conversions) ELSE NULL END AS cpa
FROM daily d
JOIN campaigns ca ON ca.campaign_id = d.campaign_id
GROUP BY d.dt, ca.platform
ORDER BY d.dt, ca.platform;

Tip

Aggregate first, then compute ratios from aggregated totals. This avoids averaging ratios which can mislead.

Exercises you can try

These mirror the tasks below so you can check your work. Use the schema given above.

Exercise 1 — Daily metrics with names

Return daily rows for 2024-04-01 to 2024-04-07 with columns: date, campaign_name, platform, spend, clicks, impressions, conversions, revenue. Include dates even if some metrics are missing.

Checklist:
- Build anchor set from all metric tables (UNION).
- Left join each metric table.
- Join campaigns for campaign_name and platform.
- COALESCE to 0 for numeric metrics.

Hints

Use UNION (not UNION ALL) to deduplicate (campaign_id, date).
Date filter belongs in a CTE or WHERE on the anchor set.

Show solution

WITH all_events AS (
  SELECT campaign_id, date::date AS dt FROM daily_spend
  UNION
  SELECT campaign_id, date::date AS dt FROM daily_clicks
  UNION
  SELECT campaign_id, date::date AS dt FROM daily_impressions
  UNION
  SELECT campaign_id, date::date AS dt FROM daily_conversions
),
filtered AS (
  SELECT * FROM all_events WHERE dt BETWEEN '2024-04-01' AND '2024-04-07'
)
SELECT
  f.dt AS date,
  ca.campaign_name,
  ca.platform,
  COALESCE(s.spend, 0) AS spend,
  COALESCE(c.clicks, 0) AS clicks,
  COALESCE(i.impressions, 0) AS impressions,
  COALESCE(v.conversions, 0) AS conversions,
  COALESCE(v.revenue, 0) AS revenue
FROM filtered f
JOIN campaigns ca ON ca.campaign_id = f.campaign_id
LEFT JOIN daily_spend s ON s.campaign_id=f.campaign_id AND s.date=f.dt
LEFT JOIN daily_clicks c ON c.campaign_id=f.campaign_id AND c.date=f.dt
LEFT JOIN daily_impressions i ON i.campaign_id=f.campaign_id AND i.date=f.dt
LEFT JOIN daily_conversions v ON v.campaign_id=f.campaign_id AND v.date=f.dt
ORDER BY date, campaign_name;

Exercise 2 — Platform rollup with ratios

Aggregate 2024-04-01 to 2024-04-07 by platform and date. Return: date, platform, spend, clicks, impressions, conversions, revenue, ctr, cpc, cpa, cvr. Compute ratios safely.

Checklist:
- Aggregate raw metrics first.
- Compute ratios from totals.
- Guard against divide-by-zero.

Hints

GROUP BY date and platform.
Use CASE WHEN SUM(...) > 0 THEN ... END for safe division.

Show solution

WITH base AS (
  SELECT
    f.dt,
    f.campaign_id,
    COALESCE(s.spend, 0) AS spend,
    COALESCE(c.clicks, 0) AS clicks,
    COALESCE(i.impressions, 0) AS impressions,
    COALESCE(v.conversions, 0) AS conversions,
    COALESCE(v.revenue, 0) AS revenue
  FROM (
    SELECT campaign_id, date::date AS dt FROM daily_spend
    UNION SELECT campaign_id, date::date AS dt FROM daily_clicks
    UNION SELECT campaign_id, date::date AS dt FROM daily_impressions
    UNION SELECT campaign_id, date::date AS dt FROM daily_conversions
  ) f
  LEFT JOIN daily_spend s ON s.campaign_id=f.campaign_id AND s.date=f.dt
  LEFT JOIN daily_clicks c ON c.campaign_id=f.campaign_id AND c.date=f.dt
  LEFT JOIN daily_impressions i ON i.campaign_id=f.campaign_id AND i.date=f.dt
  LEFT JOIN daily_conversions v ON v.campaign_id=f.campaign_id AND v.date=f.dt
  WHERE f.dt BETWEEN '2024-04-01' AND '2024-04-07'
), agg AS (
  SELECT
    b.dt AS date,
    ca.platform,
    SUM(b.spend) AS spend,
    SUM(b.clicks) AS clicks,
    SUM(b.impressions) AS impressions,
    SUM(b.conversions) AS conversions,
    SUM(b.revenue) AS revenue
  FROM base b
  JOIN campaigns ca ON ca.campaign_id = b.campaign_id
  GROUP BY b.dt, ca.platform
)
SELECT
  date,
  platform,
  spend,
  clicks,
  impressions,
  conversions,
  revenue,
  CASE WHEN impressions > 0 THEN clicks::numeric / impressions ELSE 0 END AS ctr,
  CASE WHEN clicks > 0 THEN spend / clicks ELSE NULL END AS cpc,
  CASE WHEN conversions > 0 THEN spend / conversions ELSE NULL END AS cpa,
  CASE WHEN clicks > 0 THEN conversions::numeric / clicks ELSE 0 END AS cvr
FROM agg
ORDER BY date, platform;

Expected output (shape)

Exercise 1 — sample rows

date        | campaign_name        | platform | spend | clicks | impressions | conversions | revenue
------------+----------------------+----------+-------+--------+-------------+-------------+--------
2024-04-01  | Brand Search         | Search   | 120   | 300    | 15000       | 12          | 600
2024-04-01  | Prospecting Social   | Social   | 80    | 150    | 12000       | 4           | 160

Exercise 2 — sample rows

date        | platform | spend | clicks | impressions | conversions | revenue | ctr    | cpc  | cpa  | cvr
------------+----------+-------+--------+-------------+-------------+---------+--------+------+------ +-----
2024-04-01  | Search   | 120   | 300    | 15000       | 12          | 600     | 0.0200 | 0.40 | 10.0 | 0.04
2024-04-01  | Social   | 80    | 150    | 12000       | 4           | 160     | 0.0125 | 0.53 | 20.0 | 0.027

Common mistakes and self-check

Using INNER JOIN and losing rows with zero metrics. Fix: use LEFT JOIN and COALESCE.
Dividing by zero (e.g., CPC when clicks = 0). Fix: CASE WHEN clicks > 0 THEN ... ELSE NULL END.
Aggregating ratios incorrectly. Fix: sum raw numerators/denominators first, then compute ratios.
Forgetting date filters on the anchor set. Fix: filter early on the base (date, campaign) rows.
Duplicated rows after joins. Fix: ensure one row per (date, campaign) in each table; aggregate if necessary before joining.

Self-check quick list

Do you preserve rows with zero spend/clicks?
Do all counts/amounts default to 0, but ratios use safe division?
Does grouping level match your intended granularity?
Do totals match the platform UI within expected tolerance?

Mini challenge

Add a column called spend_share that shows, per day, each platform’s share of total daily spend. Then rank platforms by spend per day.

Hint

-- Use a window over daily totals
SUM(spend) OVER (PARTITION BY date) AS daily_total_spend
spend / NULLIF(daily_total_spend, 0)
RANK() OVER (PARTITION BY date ORDER BY spend DESC)

Who this is for

Aspiring and practicing Marketing Analysts
Performance marketers who need reliable daily reporting
Analysts transitioning from spreadsheets to SQL

Prerequisites

Basic SQL SELECT, WHERE, GROUP BY
Comfort with JOINs and simple aggregates
Familiarity with marketing KPIs (spend, clicks, impressions, conversions)

Learning path

Anchor sets: build (date, campaign) with UNION.
Left join metrics and apply COALESCE.
Aggregate correctly, then compute ratios.
Add dimensions (campaign_name, platform).
Validate vs. known totals and round sensibly.

Practical projects

Daily performance dashboard: day, campaign, platform, spend, clicks, impressions, conversions, revenue, CTR, CPC, CPA.
Platform rollup: daily totals per platform with week-over-week change.
Budget pacing: month-to-date spend vs. target with required daily run-rate.

Next steps

Introduce a date dimension table for complete date coverage.
Add device and geo breakdowns; practice multi-key grouping.
Automate validation checks (e.g., no negative metrics, no NULL dates).

Quick test and saving progress

The quick test is available to everyone for free. If you log in, your progress and results will be saved.

Pulling Spend Clicks Impressions Conversions

Table of Contents