SQL for marketers: the eight queries that cover almost everything

The SQL a marketer actually needs is a much shorter list than any course will admit. Not joins across twelve tables, not window functions, not query planners. Eight shapes — filter a window, group by something, compare two periods, compute a rate correctly — and you can answer nearly every question a channel report raises. This is that list, written against real marketing data, plus the one mistake that quietly corrupts more marketing numbers than all the others combined.

Why marketers are suddenly writing SQL

Dashboards answer the questions someone anticipated. The useful question is always the next one. Spend is up 18% — fine, but up on which campaigns, and did the conversions follow? A dashboard tile can't answer that unless somebody already built a tile for it, and by the time you've filed a ticket for one the moment has passed.

SQL is what closes that gap, and the barrier to it collapsed in the last two years for a reason that has nothing to do with SQL getting easier: an AI agent will write it for you. What you need is no longer the ability to type the syntax from memory. It's the ability to read a query and know whether it's asking the right question — because a wrong query returns a confident number, not an error.

So treat what follows as a reading list, not a typing exercise.

The shape you're querying

Almost all marketing SQL runs against one tidy table where each row is one day of one thing on one platform. In TableBI that table is facts, and the vocabulary is small enough to hold in your head:

• Dimensions — the columns you slice by: date, platform, account, campaign, ad_group, ad, channel, country.
• Measures — the columns you add up: impressions, clicks, cost, conversions, conversion_value.

Two things follow from that grain. First, every rate you care about — CTR, CPA, ROAS — is derived, not stored, which is where the classic mistake lives (section eight). Second, "last 28 days" has to be anchored to the data, not to your calendar, because ad platforms and Search Console finalize days late. Every query below uses the same anchor:

-- anchor the window to the newest day that exists, not to today
WHERE date >= (SELECT MAX(date) FROM facts) - 28

Write CURRENT_DATE instead and the last few days of your window will be partial or empty, and every trend you draw will bend downward at the right edge for no reason at all.

The eight queries

1. Totals for a window

The one you run first, every time, to see whether the data is even there.

SELECT SUM(cost) AS spend, SUM(clicks) AS clicks, SUM(conversions) AS conv
FROM facts
WHERE date >= (SELECT MAX(date) FROM facts) - 28

2. The same totals, broken down

GROUP BY is the single highest-leverage keyword in marketing SQL. Swap the grouping column and the same query answers a different question: by platform it's a channel mix, by campaign it's a budget review, by country it's a geo audit.

SELECT platform, SUM(cost) AS spend, SUM(conversions) AS conv
FROM facts
WHERE date >= (SELECT MAX(date) FROM facts) - 28
GROUP BY platform
ORDER BY spend DESC

3. Filter to the slice you mean

WHERE narrows rows before aggregation. The trap is filtering on a value that doesn't exist — a campaign name you half-remember, a platform label spelled differently than you assumed — which returns zero rows and looks exactly like "that campaign had no spend."

SELECT campaign, SUM(cost) AS spend
FROM facts
WHERE date >= (SELECT MAX(date) FROM facts) - 28
  AND platform = 'google_ads'
  AND campaign LIKE '%brand%'
GROUP BY campaign ORDER BY spend DESC

Check the vocabulary before you filter on it — tablebi values --dimension campaign lists the values a dimension actually takes, so filters hit real data instead of your memory of it.

4. Top N, and the tail you're ignoring

ORDER BY … LIMIT is obvious. What's less obvious is that a top-10 view hides the shape of the distribution: ten campaigns at the top can be 90% of spend or 30% of it, and the difference completely changes what you should do next.

SELECT campaign, SUM(cost) AS spend,
       SUM(cost) * 100.0 / (SELECT SUM(cost) FROM facts
                            WHERE date >= (SELECT MAX(date) FROM facts) - 28) AS pct_of_spend
FROM facts
WHERE date >= (SELECT MAX(date) FROM facts) - 28
GROUP BY campaign ORDER BY spend DESC LIMIT 15

5. A trend line

Group by date instead of by a category and you get a time series — the input to every "is this getting better?" conversation.

SELECT date, SUM(clicks) AS clicks, SUM(cost) AS spend
FROM facts
WHERE date >= (SELECT MAX(date) FROM facts) - 90
GROUP BY date ORDER BY date

6. Compare two windows

Week-over-week is the most-requested number in marketing and the most frequently botched one, because people compare a complete week against a partial one. Label each row by which window it falls into, then group by the label — the arithmetic stays honest and both halves stay the same length.

SELECT CASE WHEN date >= (SELECT MAX(date) FROM facts) - 6
            THEN 'this_week' ELSE 'last_week' END AS window,
       platform, SUM(cost) AS spend, SUM(conversions) AS conv
FROM facts
WHERE date >= (SELECT MAX(date) FROM facts) - 13
GROUP BY 1, 2
ORDER BY platform, window

7. Drop to the raw layer when the unified one can't answer it

The cross-channel table exists so that spend is spend regardless of platform — which means it can only hold columns every platform has. Search Console has query and position; Meta doesn't. Those native fields live in per-platform raw tables underneath, at full granularity, nothing averaged away:

SELECT query, SUM(impressions) AS imp, SUM(clicks) AS clicks
FROM search_console_raw
WHERE date >= (SELECT MAX(date) FROM search_console_raw) - 28
GROUP BY query ORDER BY imp DESC LIMIT 30

Knowing which altitude a question belongs to is most of the skill. "How did paid do?" is a unified-table question. "Which queries lost impressions after the core update?" is a raw-table one. We walked that split in detail in Claude Code analytics.

8. Rate metrics — the one you must not get wrong

Here is the mistake. CTR is clicks divided by impressions. So the CTR of a group of campaigns is the group's total clicks divided by the group's total impressions. It is not the average of the individual campaigns' CTRs.

-- WRONG: averages an average. A campaign with 12 impressions
-- and a freak 50% CTR now outvotes one with 400,000 impressions.
SELECT campaign, AVG(ctr) FROM facts GROUP BY campaign

-- RIGHT: aggregate the parts, then divide.
SELECT campaign,
       ctr(SUM(clicks), SUM(impressions)) AS ctr,
       cpa(SUM(cost), SUM(conversions))   AS cpa,
       roas(SUM(conversion_value), SUM(cost)) AS roas
FROM facts
WHERE date >= (SELECT MAX(date) FROM facts) - 28
GROUP BY campaign ORDER BY roas DESC

The same trap has a specifically SEO-flavoured version: average position in Search Console must be weighted by impressions, or a keyword that showed up once at rank 3 drags your reported average up past a keyword that showed 40,000 times at rank 12. Those definitions are worth storing once rather than rederiving per query — TableBI exposes them as macros (ctr, cpc, cpm, cpa, cvr, roas, aov, avg_position) so the arithmetic is identical in every query, every dashboard, and every report. That's the same argument as defining KPIs once instead of rebuilding them, one layer down.

You don't have to type any of this

The reason to learn the eight shapes is to audit queries, not to author them. In practice you say what you want in English and your agent writes the SQL — as long as it has something to run it against:

# install the CLI, teach the agent it exists, connect a source
npm i -g @tablebi/cli
tablebi login
tablebi install
tablebi connect gsc --site sc-domain:example.com

After that, "which campaigns spent more than last week without adding conversions?" is a sentence you type into Claude Code, and what comes back is a query you can read — which is exactly the skill this article is for. Under the hood it's one command:

tablebi ask "SELECT platform, SUM(cost) AS spend,
             cpa(SUM(cost), SUM(conversions)) AS cpa
             FROM facts WHERE date >= (SELECT MAX(date) FROM facts) - 28
             GROUP BY platform ORDER BY spend DESC"

Two habits make agent-written SQL trustworthy. Have it interrogate the warehouse before it queries — tablebi schema for the fields that exist, tablebi values for the values a dimension takes, tablebi sample for the actual row shapes — so it filters on real data rather than plausible-sounding guesses. And read the trust block that comes back with every answer: how fresh each source is, and the caveats that matter, such as Search Console finalizing two to three days late and anonymizing rare queries, or GSC clicks and GA4 sessions being different populations that were never going to match.

When a query is worth keeping, it stops being a query and becomes a URL — the dashboard stores the SQL, not a snapshot, so it re-runs itself as data arrives. Here's a real one, built exactly this way from live Search Console data: