SQL for Marketers: Core Patterns
Why marketers need to read SQL even if they don't write pipelines
You don't need to build dbt models to benefit enormously from being able to read and write SQL directly against a GA4 BigQuery export or a modeled mart. It removes the dependency on someone else to answer a one-off question, lets you verify a dashboard number is computed the way you think it is, and lets you catch modeling bugs (a join fanning out, a filter excluding the wrong rows) before they turn into a wrong decision. A handful of patterns cover the overwhelming majority of marketing analysis needs.
Window functions: the pattern marketers use most without knowing its name
Almost every 'compare this period to the prior period' or 'rank campaigns by spend' question is a window function. LAG()/LEAD() grab a prior/next row's value within a partition (e.g. last week's spend for the same campaign), RANK()/ROW_NUMBER() order rows within a group, and running totals use SUM() OVER (ORDER BY ...). These let you compute period-over-period comparisons without a self-join, which is both faster to write and far less error-prone.
SELECT
campaign_id,
week_start,
spend_usd,
LAG(spend_usd) OVER (
PARTITION BY campaign_id ORDER BY week_start
) AS prior_week_spend,
spend_usd - LAG(spend_usd) OVER (
PARTITION BY campaign_id ORDER BY week_start
) AS wow_change
FROM weekly_campaign_spend
ORDER BY campaign_id, week_startUNNEST: mandatory for GA4's nested export schema
GA4's BigQuery export stores event parameters and ecommerce items as nested, repeated fields rather than flat columns. You cannot filter or select a specific parameter (like page_location or campaign) with ordinary column syntax — you must UNNEST the event_params (or items) array and filter on its key field, pulling the matching value.string_value / value.int_value / etc. This is the single most common stumbling block for marketers new to querying GA4's raw export directly.
SELECT
user_pseudo_id,
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
FROM `project.analytics_123456789.events_20260101`
WHERE event_name = 'page_view'Joins that fan out silently inflate every downstream metric
CTEs over nested subqueries
Common Table Expressions (WITH x AS (...)) let you name and sequence intermediate steps instead of nesting nested subqueries five levels deep. Beyond readability, CTEs make debugging tractable — you can run just the spend_by_week CTE on its own to confirm it looks right before trusting the final query that joins against it. Any marketing query combining more than two data sources should almost always be structured as a chain of named CTEs rather than one monolithic nested statement.
COALESCE and safe division for messy real-world data
Marketing data is full of legitimate nulls (a campaign with zero conversions has no row in a conversions table at all under an inner join) and legitimate zeros (a campaign that ran but got no clicks). COALESCE(x, 0) after a LEFT JOIN turns 'no matching row' into an explicit zero so aggregates don't silently drop campaigns. SAFE_DIVIDE() (BigQuery) or a CASE WHEN denominator = 0 THEN NULL ELSE ... END guard (standard SQL) prevents division-by-zero errors from crashing a query the moment any campaign has zero spend or zero clicks — both near-certain to occur eventually in any real dataset.
What's Next?
These query patterns are the foundation for everything downstream — from the star-schema marts analysts build on top of raw data, to the dashboards and automated reports that non-technical stakeholders will eventually consume without ever writing a line of SQL themselves.
I build these systems professionally.
Whether it's a RAG pipeline, analytics migration, or AI workflow — let's talk.