Session 12 lab: Which campaigns deserve the budget?¶

# Marketing data setup (synthetic). Run this cell first.
# Downloads the course's synthetic marketing data from GitHub and checks it is the exact version this course uses.
import hashlib
import io
import os
import urllib.request

import pandas as pd

MARKETING_URL = "https://raw.githubusercontent.com/Aayush181509/AnalyticsLecture/main/data/synthetic/marketing-v1/"
MARKETING_SHA256 = {
    "marketing_daily.csv": "1cbfc8e1c91ae72d18364b2d13bdf6db513c4aec28c339ef8b91f87a60dc0a07",
    "ab_test.csv": "41a3662997111b66d7ab1eb899f4b32e26bd64c66284ce881db6b8a7300ad47f",
}
MARKETING_DIR = os.environ.get("COURSE_MARKETING_DIR", "")


def load_marketing(name):
    """Load one marketing-v1 file: 'marketing_daily.csv' or 'ab_test.csv'."""
    if MARKETING_DIR:
        with open(os.path.join(MARKETING_DIR, name), "rb") as f:
            data = f.read()
    else:
        data = urllib.request.urlopen(MARKETING_URL + name).read()
    if hashlib.sha256(data).hexdigest() != MARKETING_SHA256[name]:
        print("WARNING: this file differs from the course version. Ask your facilitator.")
    return pd.read_csv(io.BytesIO(data), parse_dates=["date"])


print("Marketing setup complete.")

Marketing setup complete.

import matplotlib.pyplot as plt
import numpy as np

pd.set_option("display.float_format", "{:,.2f}".format)
plt.rcParams.update({
    "figure.figsize": (8, 4),
    "axes.spines.top": False,
    "axes.spines.right": False,
    "axes.grid": True,
    "axes.axisbelow": True,
    "grid.color": "#e6e5e1",
    "axes.prop_cycle": plt.cycler(color=["#2a78d6", "#eb6834", "#1baf7a"]),
    "axes.titleweight": "bold",
    "axes.titlelocation": "left",
})
print("Plot style ready.")

Plot style ready.

COUNTS = ["spend", "impressions", "clicks", "sessions", "conversions", "attributed_revenue"]


def safe_div(numerator, denominator):
    """numerator / denominator, left blank (NaN) wherever the denominator is 0."""
    return numerator / denominator.where(denominator != 0)


def add_rates(totals):
    """Add the five funnel rates to a table of SUMMED counts (ratios of sums)."""
    t = totals.copy()
    t["CTR"] = safe_div(t["clicks"], t["impressions"])
    t["conv_rate"] = safe_div(t["conversions"], t["sessions"])
    t["CPC"] = safe_div(t["spend"], t["clicks"])
    t["CPA"] = safe_div(t["spend"], t["conversions"])
    t["ROAS"] = safe_div(t["attributed_revenue"], t["spend"])
    return t


def show(table):
    """Format a totals table for reading. Blank rates show as 'n/a' (not available)."""
    out = table.copy()
    for col in out.columns:
        if col in ("CTR", "conv_rate", "share of spend", "share of conversions"):
            out[col] = out[col].map(lambda v: "n/a" if pd.isna(v) else f"{v:.2%}")
        elif col in ("spend", "attributed_revenue", "CPC", "CPA", "ROAS"):
            out[col] = out[col].map(lambda v: "n/a" if pd.isna(v) else f"{v:,.2f}")
        elif pd.api.types.is_numeric_dtype(out[col]):
            out[col] = out[col].map(lambda v: "n/a" if pd.isna(v) else f"{v:,.0f}")
    return out


print("Helpers ready: safe_div, add_rates, show.")

Helpers ready: safe_div, add_rates, show.

daily = load_marketing("marketing_daily.csv")
print("Rows:", len(daily), "| columns:", list(daily.columns))
daily.head()

Rows: 2555 | columns: ['date', 'campaign_id', 'campaign_name', 'channel', 'spend', 'impressions', 'clicks', 'sessions', 'conversions', 'attributed_revenue']

# 1. Run the cell. Are all checks passing?
# 2. Change the funnel check to compare clicks with impressions the WRONG way round (clicks >= impressions).
#    How many rows "fail"? Why does a check need to be written carefully?
# 3. Add a check that spend and attributed_revenue are never negative.
days, campaigns = daily["date"].nunique(), daily["campaign_id"].nunique()
print("Days:", days, "| campaigns:", campaigns, "| expected rows:", days * campaigns, "| actual:", len(daily))
print("Duplicate (date, campaign_id) keys:", daily.duplicated(["date", "campaign_id"]).sum())
print("Missing values:", daily.isna().sum().sum())

funnel_ok = (
    (daily["impressions"] >= daily["clicks"])
    & (daily["clicks"] >= daily["sessions"])
    & (daily["sessions"] >= daily["conversions"])
    & (daily["conversions"] >= 0)
)
print("Rows breaking the funnel order:", (~funnel_ok).sum())

Days: 365 | campaigns: 7 | expected rows: 2555 | actual: 2555
Duplicate (date, campaign_id) keys: 0
Missing values: 0
Rows breaking the funnel order: 0

# 1. Run the cell. Which campaign has the most conversions? The lowest CPA? The highest ROAS?
# 2. Change group_by to ["channel"]. How many rows now?
# 3. Change group_by to [daily["date"].dt.month]. Which month had the highest ROAS?
group_by = ["channel", "campaign_id"]

totals = add_rates(daily.groupby(group_by)[COUNTS].sum())
show(totals)

by_campaign = add_rates(daily.groupby(["channel", "campaign_id"])[COUNTS].sum())
by_channel = add_rates(daily.groupby("channel")[COUNTS].sum())
company = add_rates(daily[COUNTS].sum().to_frame("All campaigns").T)

for col in COUNTS:
    same = np.isclose(by_campaign[col].sum(), company[col].iloc[0]) and np.isclose(by_channel[col].sum(), company[col].iloc[0])
    print(f"{col:20s} campaigns and channels add up to the company total: {same}")

show(pd.concat([by_channel, company]))

spend                campaigns and channels add up to the company total: True
impressions          campaigns and channels add up to the company total: True
clicks               campaigns and channels add up to the company total: True
sessions             campaigns and channels add up to the company total: True
conversions          campaigns and channels add up to the company total: True
attributed_revenue   campaigns and channels add up to the company total: True

# 1. Run the cell. Which campaigns have spend = 0 days? Which ones have review cases?
# 2. Change condition to daily["conversions"] == 0. Which campaign has most days with no conversions? Why might that be?
# 3. Change condition to daily["clicks"] == 0. What rate is not available on those days?
condition = daily["spend"] == 0

print("Rows matching:", condition.sum())
print(daily.loc[condition, "campaign_id"].value_counts().to_string())

review = daily[(daily["spend"] == 0) & (daily["conversions"] > 0)]
print("\nReview cases (spend = 0 but conversions > 0):", len(review))
review[["date", "campaign_id", "spend", "sessions", "conversions", "attributed_revenue"]]

Rows matching: 39
campaign_id
EML-01     12
SOC-02      6
SRCH-02     6
SOC-01      5
SRCH-01     5
DISP-01     3
VID-01      2

Review cases (spend = 0 but conversions > 0): 12

# 1. Run the cell. For which channel do the two methods differ most? Does the channel ranking change?
# 2. Change rate to "CTR", then to "ROAS". Does the same channel cause the difference?
# 3. Look at the "sessions" column of by_campaign for that channel's campaigns. Explain the difference in two sentences.
rate = "conv_rate"

compare = pd.DataFrame({
    "campaigns": by_campaign.groupby("channel").size(),
    "mean of campaign rates": by_campaign[rate].groupby("channel").mean(),
    "weighted rate (sum ÷ sum)": by_channel[rate],
})
compare["rank by mean"] = compare["mean of campaign rates"].rank(ascending=False).astype(int)
compare["rank by weighted"] = compare["weighted rate (sum ÷ sum)"].rank(ascending=False).astype(int)
shown = compare.sort_values("rank by weighted")
as_text = "{:.2%}".format if rate in ("CTR", "conv_rate") else "{:,.2f}".format    # percent, or plain number for CPC/CPA/ROAS
for col in ["mean of campaign rates", "weighted rate (sum ÷ sum)"]:
    shown[col] = shown[col].map(as_text)
shown

show(by_campaign.loc[["Search", "Social"], ["sessions", "conversions", "conv_rate", "spend", "ROAS"]])

# 1. Run the cell. Sort by "ROAS", then by "conversions". Which campaign moves the most?
# 2. Change min_share to 0.10. Which campaigns are now flagged "small"?
# 3. Which campaign would you call "efficient and big enough to matter"? Which is "large but expensive"?
sort_by = "conversions"
min_share = 0.05          # campaigns below this share of conversions are flagged "small"

scorecard = by_campaign[["spend", "conversions", "attributed_revenue", "CPA", "ROAS"]].copy()
scorecard["share of spend"] = scorecard["spend"] / scorecard["spend"].sum()
scorecard["share of conversions"] = scorecard["conversions"] / scorecard["conversions"].sum()
scorecard["flag"] = np.where(scorecard["share of conversions"] < min_share, "small", "")
email = scorecard.index.get_level_values("channel") == "Email"
scorecard.loc[email, "flag"] = "near-zero media cost: ROAS not comparable"

show(scorecard.sort_values(sort_by, ascending=False))

paid = scorecard.drop("Email", level="channel").reset_index().sort_values("CPA")

fig, ax = plt.subplots(figsize=(8, 4))
bars = ax.barh(paid["campaign_id"], paid["CPA"], color="#2a78d6")
ax.invert_yaxis()
for bar, conv in zip(bars, paid["conversions"]):
    ax.text(bar.get_width(), bar.get_y() + bar.get_height() / 2, f" {conv:,.0f} conversions",
            va="center", fontsize=8)
ax.set_title("Paid campaigns: cost per acquisition, with volume")
ax.set_xlabel("CPA in 2025 (currency units per conversion; lower is better)")
plt.show()

# 1. Run the cell. In which months does spend rise? Does CPA rise or fall in those months?
# 2. Change campaign to "SOC-01", then "VID-01". Is the pattern the same?
# 3. Could you tell from this chart whether the extra spend CAUSED the change in CPA? Why not?
campaign = "SRCH-01"

one = daily[daily["campaign_id"] == campaign]
monthly = add_rates(one.groupby(one["date"].dt.to_period("M"))[COUNTS].sum())

fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(9, 6), sharex=True)
months = monthly.index.strftime("%b")
ax1.bar(months, monthly["spend"], color="#2a78d6")
ax1.set_title(f"{campaign}: monthly spend")
ax1.set_ylabel("Spend (currency units)")
ax2.plot(months, monthly["CPA"], marker="o", color="#eb6834", label="CPA")
ax2.set_title(f"{campaign}: monthly CPA (ratio of sums)")
ax2.set_ylabel("CPA (currency units)")
fig.tight_layout()
plt.show()
show(monthly[["spend", "conversions", "conv_rate", "CPA", "ROAS"]])

# 1. Run the cell. Does CPA rise or fall from the lowest to the highest spend band? And the cost per 1,000 impressions?
# 2. Look at "share of days in Oct-Dec". Why might the highest band look cheap per conversion?
# 3. Change months to range(1, 10) (January to September only) and run again. What changes, and why?
campaign = "SRCH-01"
months = range(1, 13)

rows = daily[(daily["campaign_id"] == campaign) & (daily["spend"] > 0) & daily["date"].dt.month.isin(months)].copy()
rows["spend band"] = pd.qcut(rows["spend"], 4, labels=["lowest", "second", "third", "highest"])
rows["in Oct-Dec"] = rows["date"].dt.month >= 10

bands = add_rates(rows.groupby("spend band", observed=True)[COUNTS].sum())
bands["days"] = rows.groupby("spend band", observed=True).size()
bands["share of days in Oct-Dec"] = rows.groupby("spend band", observed=True)["in Oct-Dec"].mean()
bands["cost per 1,000 impressions"] = safe_div(bands["spend"], bands["impressions"]) * 1000
bands["spend per day"] = bands["spend"] / bands["days"]
bands["conversions per day"] = bands["conversions"] / bands["days"]

ax = bands.plot(x="spend per day", y="conversions per day", marker="o", legend=False)
ax.set_title(f"{campaign}: conversions per day by spend band (association only)")
ax.set_xlabel("Average daily spend in the band (currency units)")
ax.set_ylabel("Average daily conversions")
plt.show()
(bands[["days", "spend per day", "conversions per day", "cost per 1,000 impressions", "CPA", "share of days in Oct-Dec"]]
 .round(2).assign(**{"share of days in Oct-Dec": bands["share of days in Oct-Dec"].map("{:.0%}".format)}))

# Colleague's code (bug 1): do not copy this pattern.
paid_days = daily[daily["spend"] > 0].copy()
paid_days["daily_roas"] = paid_days["attributed_revenue"] / paid_days["spend"]
averaged = paid_days.groupby("campaign_id")["daily_roas"].mean()
averaged.round(2)

campaign_id
DISP-01    0.61
EML-01    27.37
SOC-01     1.32
SOC-02    12.69
SRCH-01    7.72
SRCH-02    5.97
VID-01     0.37
Name: daily_roas, dtype: float64

# Colleague's code (bug 2): do not copy this pattern.
all_days = daily.copy()
all_days["daily_roas"] = all_days["attributed_revenue"] / all_days["spend"]
print("Rows with infinite ROAS:", np.isinf(all_days["daily_roas"]).sum())
print("Rows with NaN ROAS:     ", all_days["daily_roas"].isna().sum())
all_days.groupby("campaign_id")["daily_roas"].mean().round(2)

Rows with infinite ROAS: 12
Rows with NaN ROAS:      27





campaign_id
DISP-01    0.61
EML-01      inf
SOC-01     1.32
SOC-02    12.69
SRCH-01    7.72
SRCH-02    5.97
VID-01     0.37
Name: daily_roas, dtype: float64

# 1. Run the cell. Which campaigns differ between the averaged daily ROAS and the ratio of sums? By how much?
# 2. Why does bug 2 give "inf" for one campaign? Which rows cause it, and what are they called?
# 3. Write the correct ROAS calculation in one line, and one sentence explaining why averaging days is wrong
#    (hint: is a day with spend 50 as important as a day with spend 2,000?).
fixed = by_campaign["ROAS"].droplevel("channel")

check = pd.DataFrame({
    "bug 1: mean of daily ROAS": averaged,
    "bug 2: mean incl. zero-spend days": all_days.groupby("campaign_id")["daily_roas"].mean(),
    "correct: sum of revenue ÷ sum of spend": fixed,
})
check["bug 1 minus correct"] = check["bug 1: mean of daily ROAS"] - check["correct: sum of revenue ÷ sum of spend"]
check.round(2)

# 1. Set candidate to the campaign you would test. Run the cell.
# 2. How much do weekly conversions vary from week to week (compare the standard deviation with the mean)?
# 3. If the budget change might add a tenth more conversions, would a two-week test show it clearly? What about eight weeks?
candidate = "SRCH-02"

one = daily[daily["campaign_id"] == candidate].set_index("date")
weekly = one.resample("W-SUN")[["spend", "conversions"]].sum().loc["2025-01-12":"2025-09-28"]   # complete weeks, Jan-Sep

print(f"{candidate}: {len(weekly)} complete weeks, January to September")
print(f"Weekly conversions: mean {weekly['conversions'].mean():,.0f}, standard deviation {weekly['conversions'].std():,.0f}")
print(f"Weekly spend:       mean {weekly['spend'].mean():,.0f}")

ax = weekly["conversions"].plot(marker="o", markersize=3)
ax.set_title(f"{candidate}: weekly conversions outside the end-of-year season")
ax.set_xlabel("Week ending")
ax.set_ylabel("Conversions per week")
plt.show()

SRCH-02: 38 complete weeks, January to September
Weekly conversions: mean 228, standard deviation 21
Weekly spend:       mean 4,482