Level 6 · Marketing spend per channel

The whole summary in one agg

marketing_spend has one row per day and channel: the date, the channel (paid, email or social) and the amount spent. Jordan’s three questions are three aggregations of the same groups, so one named agg answers them all:

marketing_spend.groupby("channel", as_index=False).agg(
    days=("spend_date", "count"),
    total_spend=("amount", "sum"),
    avg_daily=("amount", "mean"),
)

Each keyword is an output column and each tuple says which column to aggregate and how. With as_index=False, channel stays an ordinary column, ready to sort and share.

Reading the numbers

channel	days	total_spend	avg_daily
paid	1091	888,774.13	814.64
social	1093	345,764.76	316.34
email	1092	100,187.71	91.75

Paid spend is about 2.6 times social and nearly 9 times email. That’s normal for an online store: paid search and shopping ads cost money per click, while email costs little once the list exists.

The days column holds a small surprise. 2023 to 2025 has 1,096 days, yet no channel has 1,096 rows. A handful of days are missing for each channel (tracking outages). For this summary that’s fine: the mean is the average of the days we have. Just don’t divide the total by 1,096 and call that the same number. Checking a count against the number you expect is a habit worth keeping, because gaps like these change totals and averages.

Sorting a summary

The ticket asks for the biggest channel first, so sort by total_spend with ascending=False and reset the index. Sorting by the rounded or the unrounded total gives the same order here, because no two channels are close. Rounding with a dictionary, .round({"total_spend": 2, "avg_daily": 2}), rounds only the money columns and leaves the day counts untouched.

Common mistakes

• Using "size" or "count" on amount and calling it spend.
• Rounding amount before summing, which adds up rounding errors over 3,000 rows.
• Forgetting as_index=False, so channel becomes the index and the column check fails.
• Dividing the total by the number of calendar days instead of taking the mean of the rows.