Level 2 · Cost per conversion in pandas

Same question, same definitions

This is Jordan’s budget analysis in pandas, with the same definitions as the SQL level of this case: 2025 only on both tables, a conversion is a session with converted == True, and spend is matched to sessions by channel name. Organic and direct have sessions but no spend.

Step 1: counting True values

converted is a boolean column. In pandas True behaves as 1 and False as 0, so ("converted", "sum") counts conversions and ("converted", "mean") would give the rate directly. The task asks for a percentage rounded to 2, so compute it from the two counts:

by_source = ws.groupby("source", as_index=False).agg(
    sessions=("session_id", "count"), conversions=("converted", "sum")
)
by_source["conversion_rate_pct"] = (by_source["conversions"] * 100 / by_source["sessions"]).round(2)

"count" counts non-missing values of the named column. session_id is never missing, so it counts every session. Counting customer_id would be a quiet bug: anonymous sessions have NaN there and would not be counted.

Step 2: pre-aggregate, then merge

In SQL this takes two CTEs. In pandas each one is simply a DataFrame:

conv = ws.groupby("source", as_index=False).agg(conversions=("converted", "sum"))
spend = ms.groupby("channel", as_index=False).agg(spend=("amount", "sum")).rename(columns={"channel": "source"})
table = conv.merge(spend, on="source", how="left", validate="one_to_one")

marketing_spend calls its key channel while web_sessions calls it source. .rename(columns={"channel": "source"}) renames a column (old name on the left, new name on the right) so both frames share the key and on="source" works. left_on="source", right_on="channel" does the same job without renaming.

validate="one_to_one" is the pandas way of asserting what you reasoned about in SQL: after the groupby, each side has one row per key, so the merge cannot fan out. If someone later merges the raw tables by mistake, pandas raises a MergeError instead of handing back inflated spend.

how="left" from the sessions side keeps organic and direct, with NaN spend (pandas’ marker for a missing number). Any arithmetic with NaN gives NaN, so their cost per conversion is missing, which is correct: it is not zero, it is not applicable.

Division by zero

pandas silently returns inf (infinity) when you divide a number by zero. The fix is to turn zeros in the denominator into missing values first. .replace(0, np.nan) swaps every 0 in a Series for np.nan, numpy’s missing number, and anything divided by NaN is NaN:

pd.Series([5000, 5000]) / pd.Series([25, 0])                    # 200.0, inf
pd.Series([5000, 5000]) / pd.Series([25, 0]).replace(0, np.nan)  # 200.0, NaN

No 2025 channel or month has zero conversions in this data, but a monthly report that runs every month will meet one eventually, and inf in a chart is hard to explain.

Step 3: add month to both keys

Build a month label on each table with .dt.strftime("%Y-%m"), which turns a datetime into text like "2025-03" (%Y is the year, %m the two-digit month). Group each table by month, and merge on month. The grain is now one row per month on both sides.

SQL and pandas side by side

Step	SQL	pandas
conversions	COUNT(*) FILTER (WHERE converted)	("converted", "sum")
CTE	WITH conv AS (...)	conv = ws.groupby(...)
key names differ	ON s.channel = c.source	rename or left_on/right_on
assert no fan-out	reason about it	validate="one_to_one"
NULLIF(x, 0)	built in	x.replace(0, np.nan)

Pitfalls

• Merging raw sessions with raw spend. Even on ["source", "date"], each day’s spend repeats once per session that day.
• Rounding spend before dividing. Compute cost per conversion from the raw sum, then round.
• Missing spend days. Twelve outage rows are absent from marketing_spend; the totals are a slight undercount, the same in SQL and pandas.
• Dates as strings. The filters compare a datetime64 column with "2025-01-01"; pandas parses the string, so no conversion is needed.