Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics › Business Intelligence for Marketing

SQL for Marketers

The SQL a marketer needs: selecting and filtering, aggregating by date and channel, joining spend to outcomes, window functions for cohorts, and pitfalls.
Guideintermediate11 min readUpdated

What SQL lets you ask

Platform reports answer the questions the platform designed in. SQL answers the question you have, phrased as a table: which rows, grouped how, measured by what. The questions marketers reach SQL for are the ones that cross a boundary reports cannot cross: spend from one system against outcomes from another, this year's cohort against last year's, a definition of "customer" that the platform does not share. The reporting versus BI comparison explains why those questions mark the point where a team crosses from one to the other.

The guide uses four generic tables that most marketing warehouses contain in some form. ad_spend_daily holds one row per day, channel and campaign with spend, clicks and impressions. sessions holds one row per session with its date, user, channel, campaign and the conversions counted in it. orders holds one row per order with its date, customer, revenue and the channel credited for it. customers holds one row per customer with their first order date and acquisition channel. Real schemas are messier, and the BigQuery export basics guide explains the nested shape the GA4 export arrives in and how to flatten it into tables like these. Every query below is standard SQL in the BigQuery dialect; the ideas transfer to any warehouse with small changes in function names.

One habit before any syntax: write the question as a sentence with its grain in it. "Spend and conversions per day per channel for the quarter" names the rows (day and channel), the measures (spend, conversions) and the filter (the quarter). A query that starts from that sentence is usually right; one that starts from a table rarely is.

Selecting, filtering and aggregating

The basic shape is SELECT the columns, FROM the table, WHERE the rows, GROUP BY the dimensions, ORDER BY the result. Aggregation turns many rows into one per group: SUM for additive measures like spend and revenue, COUNT for rows, COUNT(DISTINCT …) for unique things like users, and AVG only when the thing being averaged is itself a per-row value rather than a ratio.

SELECT
  channel,
  SUM(spend) AS spend,
  SUM(clicks) AS clicks,
  SAFE_DIVIDE(SUM(spend), SUM(clicks)) AS cost_per_click
FROM ad_spend_daily
WHERE spend_date BETWEEN @start_date AND @end_date
GROUP BY channel
ORDER BY spend DESC

Two things in that query matter more than they look. The cost per click is computed from the sums, not averaged across rows; an average of daily ratios weights a quiet day the same as a busy one and gives a number nobody can reconcile. And SAFE_DIVIDE returns a null rather than an error when clicks are zero; other warehouses use NULLIF on the denominator to the same effect. Filtering with WHERE happens before grouping; to filter on an aggregate, such as channels that spent more than a threshold, use HAVING after GROUP BY.

Illustrative example — not a real result

Quarterly spend by channel for a furniture retailer

A furniture retailer wants spend, clicks and cost per click by channel for the first quarter, and only for channels that spent more than £1,000.

SELECT
  channel,
  SUM(spend) AS spend,
  SUM(clicks) AS clicks,
  SAFE_DIVIDE(SUM(spend), SUM(clicks)) AS cost_per_click
FROM ad_spend_daily
WHERE spend_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY channel
HAVING SUM(spend) > 1000
ORDER BY spend DESC

The result has four rows: paid search at £48,200 and 61,400 clicks (£0.79 per click), paid social at £31,500 and 84,000 clicks (£0.38), shopping at £22,900 and 19,100 clicks (£1.20), and display at £4,300 and 52,000 clicks (£0.08). An earlier version of the report had averaged a daily cost-per-click column instead, and showed paid search at £0.91 because a handful of low-volume days with expensive clicks counted as much as the busy ones.

Dates and time grains

Most marketing questions have a time grain: by day, week, month or quarter. The pattern is to truncate the date to the grain and group by the truncated value. In BigQuery DATE_TRUNC(spend_date, MONTH) returns the first day of the month; WEEK and ISOWEEK differ in which day starts the week, and ISOWEEK is the safer default because it does not depend on a locale setting.

SELECT
  DATE_TRUNC(order_date, MONTH) AS order_month,
  channel,
  COUNT(DISTINCT order_id) AS orders,
  SUM(revenue) AS revenue
FROM orders
GROUP BY order_month, channel
ORDER BY order_month, channel

Three traps live in dates. Time zones: platforms report in the account's time zone, the GA4 export in the property's, and the warehouse often stores timestamps in UTC; a daily join between two tables in different zones will be off at the edges of every day. Partial periods: the current month is always smaller than the last one until it ends, and a chart that does not mark it will be read as a decline. Calendar against fiscal periods: finance may close on a different day from the calendar month, and a reconciliation that ignores it will never tie. Define the grain, the zone and the period boundaries once in the metrics layer and reuse them, rather than deciding afresh in every query.

Comparisons against a prior period are either a join of a table to itself on a shifted date or a window function (below) over an ordered series. Both work; the window version is shorter and avoids the fan-out risk of a self-join.

Illustrative example — not a real result

Month-on-month revenue with a lag for a subscription service

A subscription service wants monthly revenue by channel with the previous month beside it, for the twelve months to the end of June.

WITH monthly AS (
  SELECT
    DATE_TRUNC(order_date, MONTH) AS order_month,
    channel,
    SUM(revenue) AS revenue
  FROM orders
  WHERE order_date BETWEEN '2025-07-01' AND '2026-06-30'
  GROUP BY order_month, channel
)
SELECT
  order_month,
  channel,
  revenue,
  LAG(revenue) OVER (PARTITION BY channel ORDER BY order_month) AS previous_month_revenue
FROM monthly
ORDER BY channel, order_month

For paid search the June row shows £84,600 against £79,100 in May; for email it shows £22,300 against £26,800, which is the first fall in eight months and the reason the query was written. The LAG function reads the previous row within the channel's ordered series, so there is no self-join and no risk of duplicated rows.

Joining spend to outcomes

The question BI is usually built to answer is "what did we spend and what did we get", and it needs rows from two systems. The join key is the shared dimension: usually date and channel, sometimes campaign, occasionally a click id when both sides carry it. The reliable pattern is to aggregate each side to the join grain first, in its own common table expression, and only then join. Joining raw rows and aggregating afterwards is where most wrong totals come from.

WITH spend AS (
  SELECT spend_date AS activity_date, channel, SUM(spend) AS spend
  FROM ad_spend_daily
  GROUP BY activity_date, channel
),
outcomes AS (
  SELECT session_date AS activity_date, channel,
    COUNT(DISTINCT session_id) AS sessions,
    SUM(conversions) AS conversions
  FROM sessions
  GROUP BY activity_date, channel
)
SELECT
  s.activity_date,
  s.channel,
  s.spend,
  o.sessions,
  o.conversions,
  SAFE_DIVIDE(s.spend, o.conversions) AS cost_per_conversion
FROM spend AS s
LEFT JOIN outcomes AS o
  ON o.activity_date = s.activity_date
  AND o.channel = s.channel
ORDER BY s.activity_date, s.channel

The join type matters. A LEFT JOIN from spend keeps days with spend and no sessions, which is a real condition worth seeing; an INNER JOIN would hide them. A FULL OUTER JOIN keeps sessions on days without spend as well, which matters for channels like organic that never have spend. Whichever is chosen, the choice is part of the metric's definition and belongs in the metrics layer, not in the analyst's head.

The channel value on both sides must mean the same thing, and usually does not until someone makes it so. Platforms name themselves one way, analytics derives channel from UTM parameters another way, and the join silently drops every row where the two differ. A channel mapping table, maintained by hand and keyed on the raw values from each source, is the unglamorous fix, and the naming conventions guide explains the convention that keeps it small.

The guide to designing a marketing data model sets out the fact tables, shared dimensions and campaign mapping table that turn joins like these into a matter of keys rather than matching.

Cost metrics follow from this join: cost per conversion, return on ad spend (ROAS) when the outcomes table carries revenue, and customer acquisition cost (CAC) when it carries new customers rather than all conversions. Each is the same shape with a different denominator, which is why defining the denominator precisely is most of the work.

Window functions: cohorts and running totals

A window function computes a value for each row using other rows in a defined group, without collapsing the rows the way GROUP BY does. The group is the PARTITION BY; the order within it is the ORDER BY. Three shapes cover most marketing uses: ROW_NUMBER to find the first of something, LAG or LEAD to compare with the previous or next row, and SUM … OVER for a running total.

SELECT
  customer_id,
  order_id,
  order_date,
  revenue,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_sequence,
  SUM(revenue) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_revenue
FROM orders

order_sequence marks each customer's first, second and later orders, which is how new customers are separated from returning ones without a flag in the source. cumulative_revenue is the running value of each customer over time, which is the raw material of lifetime value (LTV). Cohort analysis combines the two: assign every customer to the month of their first order, then measure what each cohort did in the months after. The customer analytics overview covers what to do with the result; the query below produces it.

Illustrative example — not a real result

Retention by acquisition cohort for an online pharmacy

An online pharmacy wants the share of each monthly acquisition cohort that placed another order in each of the six months after their first.

WITH first_orders AS (
  SELECT
    customer_id,
    DATE_TRUNC(MIN(order_date), MONTH) AS cohort_month
  FROM orders
  GROUP BY customer_id
),
activity AS (
  SELECT
    o.customer_id,
    f.cohort_month,
    DATE_DIFF(DATE_TRUNC(o.order_date, MONTH), f.cohort_month, MONTH) AS months_since_first
  FROM orders AS o
  JOIN first_orders AS f USING (customer_id)
)
SELECT
  cohort_month,
  months_since_first,
  COUNT(DISTINCT customer_id) AS active_customers
FROM activity
WHERE cohort_month >= '2026-01-01'
  AND months_since_first BETWEEN 0 AND 6
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first

The January cohort starts with 2,140 customers; 610 order again in month one, 455 in month two and 390 by month six, a six-month retention of roughly 18 percent. The March cohort, acquired during a discount campaign, starts larger at 3,320 but holds only 9 percent by month six. Dividing each row by the month-zero count gives the retention curve; comparing the curves by acquisition channel, by joining customers, shows which channel bought customers who stayed.

The same ordering pattern, applied to each user's sessions before a conversion, is the core of the tutorial on building an attribution model in SQL from the GA4 export.

Pitfalls: fan-out, nulls and double counting

Fan-out. When a join key is not unique on one side, every matching row on the other side is repeated. Two campaigns for a channel on a day in the spend table, joined to three session rows for that channel and day, produce six rows, and summing spend over them triples it. The symptom is a total that exceeds the platform's own total; the cause is almost always aggregating after joining instead of before. The cure is the common-table-expression pattern above: one row per key on each side before the join.

Nulls. A LEFT JOIN produces nulls where the right side has no match, and most aggregate functions ignore nulls silently: SUM of a column with nulls is the sum of the rest, COUNT(column) counts only non-null rows, AVG divides by the non-null count. A division with a null denominator returns null, which a chart renders as a gap rather than an error. Decide, in the definition, whether a missing outcome is zero or unknown, and make the query say so explicitly with COALESCE where zero is meant.

Double counting. Sessions can carry more than one conversion; users have many sessions; orders can be refunded and reissued. COUNT(*) counts rows, COUNT(DISTINCT user_id) counts people, and the two differ by exactly the amount that confuses a meeting. The same applies to summing a user-level value from a session-level table, which multiplies it by sessions per user. Know the grain of the table being summed and match the measure to it.

Filters that move the baseline. A WHERE on the right-hand table of a LEFT JOIN turns it into an inner join, because the unmatched rows have nulls and fail the filter. Put conditions on the right side into the ON clause, or into the CTE that builds it.

Illustrative example — not a real result

A fan-out that doubled the reported spend

An agency reporting for a travel client joined the raw spend table to the raw sessions table on date and channel, then summed. On 14 May paid social had two campaign rows totalling £600 and three session rows; the join produced six rows and the dashboard reported £1,800 of spend for the day. Across the month the channel showed £47,000 against the platform's £19,800, and the client's finance team noticed before the agency did. Rebuilding the query to aggregate each side to day and channel first brought the total back to £19,800 exactly, and a reconciliation check comparing the modelled spend to the platform's monthly invoice was added to the model, with an alert when the two differ by more than 1 percent.

Query patterns

QuestionPatternKey clauses
How much did each channel spend this period?Aggregate one table by a dimension with a date filterWHERE, GROUP BY, SUM
How is a metric trending by month?Truncate to the grain, then aggregateDATE_TRUNC, GROUP BY
What did we spend and what did we get?Aggregate each side to the join grain, then joinCTEs, LEFT JOIN on date and channel
Which campaigns exceed a cost threshold?Aggregate, then filter the aggregateGROUP BY, HAVING
How many new customers did a channel bring?Rank orders within customer; keep the firstROW_NUMBER over customer, filter on the first
How does this month compare with last?Ordered series with a lagLAG over the time dimension
How well does each cohort retain?First-order month per customer, months since, distinct countMIN, DATE_DIFF, COUNT(DISTINCT …)
What is the running total to date?Cumulative sum within a partitionSUM … OVER (ORDER BY …)
Which rows did the join lose?Outer join and look for nulls on one sideFULL OUTER JOIN, WHERE … IS NULL
Do our totals tie to the platform?Aggregate the model and the source to the same grain; compareTwo CTEs and a difference column

The patterns are few because the questions are few. The hard part of each is deciding the grain and the definition, which is why the queries that matter belong in the metrics layer rather than in a notebook. When an AI tool writes the query, the routine for checking AI-written SQL uses these patterns to prove the answer before anyone acts on it. The BI hub shows where SQL sits among the other layers of the stack.

Common mistakes

  • Averaging ratios. Cost per click, conversion rate and return on spend are computed from sums, never averaged across rows.
  • Joining before aggregating. The source of almost every inflated total.
  • Ignoring the join type. Inner joins hide spend without outcomes and outcomes without spend; both are findings.
  • Assuming channel means the same thing in both tables. It does not until a mapping table says so.
  • Mixing time zones. Daily joins between tables in different zones are wrong at every midnight.
  • Counting rows when the question is about people. COUNT(*) and COUNT(DISTINCT user_id) answer different questions.
  • Leaving a WHERE on the outer side of a left join. It quietly becomes an inner join.
  • Keeping the definition in the query. A KPI computed slightly differently in every analyst's notebook is the problem BI exists to end.

Frequently asked questions

Do I need to learn SQL if the BI tool has a visual query builder?

Learning enough to read a query is worth it even if you never write one. Visual builders generate SQL, and when a number looks wrong the only way to find out why is to read what was generated: the join, the grain, the filters. Writing the handful of patterns on this page takes a few weeks of practice and removes the wait for someone else to do it.

Which SQL dialect should a marketer learn?

The one the team's warehouse speaks, which for most marketing teams is BigQuery because the GA4 export lands there. The concepts transfer: select, filter, group, join and window functions exist everywhere, and the differences are mostly in function names for dates and safe division. Learn the ideas in one dialect and the second takes days.

How do I check a query is right?

Reconcile it to something independent. Total the modelled spend and compare it to the platform's own figure for the same period; count distinct orders and compare to the commerce platform. Then check the grain by looking for duplicate keys in the output. A query that ties to its sources at the total level and has unique keys at its grain is usually right; one that does not should not reach a dashboard.

Terms used on this page

Where this page is used

Part of these learning paths

Also related

Related