Level 1 · Cost per conversion in SQL

How an analyst reads this ticket

Jordan needs three numbers per channel: how many visits it brought, how many of those converted, and what it cost. Two tables hold them, and neither is at the grain of the answer:

• web_sessions has one row per visit. source says where the visit came from, converted says whether it ended in a purchase.
• marketing_spend has one row per day per channel. Its channel values (paid, email, social) match web_sessions.source, but organic and direct have no spend at all, because nobody pays for them.

The answer is one row per source (or per source and month). So the plan is: aggregate each table to that grain on its own, then join the two small results.

Step 1: conversion rate

One table, one GROUP BY. DuckDB’s COUNT(*) FILTER (WHERE converted) counts only the converted rows; SUM(CASE WHEN converted THEN 1 ELSE 0 END) is the portable spelling. Multiply by 100.0 before dividing so the division is decimal.

Be careful how you phrase the result. Sessions are not linked to orders in this dataset, so this is a session conversion rate as the web analytics tool reported it, not a count of orders.

Step 2: the fan-out trap, and the fix

The tempting query joins the raw tables:

SELECT w.source, SUM(m.amount) ...
FROM web_sessions AS w
JOIN marketing_spend AS m ON m.channel = w.source

Every paid session matches every paid spend day, so spend is multiplied by the number of sessions. The numbers come out in the billions and nobody notices until the CFO does. Even joining on the date as well still repeats each day’s spend once per session that day.

The fix is the pattern from the CTE chapter: pre-aggregate before you join. One CTE reduces sessions to one row per source, another reduces spend to one row per channel, and the join is then one-to-one. Start from the sessions side with a LEFT JOIN so organic and direct stay in the answer with NULL spend. Leaving them out would hide the fact that they bring a large share of conversions for free.

NULLIF(a, b) returns NULL when a equals b, and a otherwise. So NULLIF(c.conversions, 0) turns a zero into NULL, and anything divided by NULL is NULL:

SELECT 5000 / NULLIF(25, 0) AS per_conversion,   -- 200.0
       5000 / NULLIF(0, 0)  AS no_conversions;   -- NULL, not an error

A channel with spend and no conversions then gives NULL cost per conversion. Postgres and most warehouses raise a division-by-zero error without it, so write it explicitly.

Step 3: month by month for paid

Same recipe, with month added to the grain on both sides. strftime(spend_date, '%Y-%m') makes the text month label ('2025-03'), exactly as in the holiday case; build it the same way on both CTEs so the join keys match. Both CTEs have one row per month, so an inner join is safe here. Monthly figures for paid are noisy because conversions per month are small numbers; look at the direction, not the decimals.

SQL and pandas side by side

Idea	SQL	pandas
count where true	COUNT(*) FILTER (WHERE converted)	("converted", "sum")
pre-aggregate	a CTE with GROUP BY	groupby(...).agg(...) into a new frame
keep sources without spend	LEFT JOIN	merge(how="left")
safe division	NULLIF(x, 0)	dividing by 0 gives inf; replace it with NaN

Pitfalls

• Joining raw tables at different grains. Always ask “how many rows per key does each side have?” before a join.
• Missing spend days. Twelve day/channel rows are missing from marketing_spend because of tracking outages. Totals are slightly low; say so in the write-up rather than inventing values.
• Filtering the year on one side only. Both tables need the 2025 filter, or spend from three years is divided by conversions from one.
• Attribution. “Source of the session” is last-touch attribution. It is the usual starting point and the usual first question from Finance; mention it.