Case 2 · 2 levels
Marketing cost per conversion
Put web sessions and marketing spend side by side: conversion rate by traffic source, cost per conversion by channel and a monthly view of paid search, in SQL and in pandas.
StartThis case unlocks when both chapters are done. The levels are readable now.
Marketing budgets get defended with numbers, and the numbers usually live in two places that were never designed to be joined: a sessions table from the website and a spend table from the ad platforms. This case puts them side by side for Harbor & Pine’s 2025.
The analysis is simple arithmetic. The skill is in the joins: both tables are at a different grain from the answer, so each has to be aggregated to the answer’s grain first. Skip that and every dollar of spend is multiplied by every session it touches. You solve it in SQL and in pandas and the figures must match.
Levels
- Cost per conversion in SQL
from Jordan Reyes, Head of Marketing
cte, pre aggregate, left join, nullif, conversion rate, cost per conversion
- Cost per conversion in pandas
from Jordan Reyes, Head of Marketing
groupby, named aggregation, pre aggregate, left merge, conversion rate, cost per conversion