Build a Weekly Marketing Report in Python

Last updated: August 2026

In this tutorial you build the report you currently rebuild by hand every Monday. We start with a messy multi-channel campaign export (Google, Meta, LinkedIn, TikTok, with inconsistent channel names, blank rows, and dollar signs stuck to the numbers), clean it in pandas, and produce a weekly performance summary: spend, conversions, cost per acquisition, and ROAS per channel, plus a bar chart you can paste into a deck. By the end you will have a script that turns a raw CSV into a finished summary in under a second, every week, with no copy-paste.

Everything below runs standalone. Step 1 generates a realistic sample export so you can run the whole thing before you ever touch your real data.

What you need before starting

• Python 3.10 or newer installed locally
• pandas and matplotlib (pip install pandas matplotlib)
• A code editor or Jupyter notebook (VS Code, Colab, anything)
• Comfort with running a .py file or a notebook cell
• No prior pandas experience required; every line is explained

Step 1: Generate a realistic messy export

Real campaign exports are never clean. Channel names come in three spellings, some rows are totals or blanks, spend arrives as text like "$1,240.50", and dates are strings. We will recreate that mess on purpose so the cleaning code you write actually earns its keep. Save this as weekly_report.py and run it once.

import pandas as pd
import numpy as np

# Build a deliberately messy multi-channel export and save it as a CSV.
rows = [
    ["2026-05-04", "Google Ads",  "$1,240.50", "84",  "Search - Brand"],
    ["2026-05-04", "google_ads",  "$610.00",   "33",  "Search - Generic"],
    ["2026-05-04", "Meta",        "$980.25",   "61",  "Prospecting"],
    ["2026-05-04", "meta ads",    "$430.00",   "0",   "Retargeting"],
    ["2026-05-04", "LinkedIn",    "$1,510.00", "12",  "ABM"],
    ["2026-05-04", "TikTok",      "$725.75",   "40",  "Awareness"],
    ["",           "",            "",          "",    ""],            # blank spacer row
    ["2026-05-04", "TOTAL",       "$5,496.75", "230", "All"],         # summary row to drop
    ["2026-05-04", "tiktok ads",  "$0.00",     "0",   "Paused"],      # zero-spend row
]

raw = pd.DataFrame(rows, columns=["date", "channel", "spend", "conversions", "campaign"])
raw.to_csv("campaign_export.csv", index=False)
print(raw)

You should see a 9-row table printed, including the blank row and the TOTAL row. A file named campaign_export.csv now sits next to your script.

Checkpoint: campaign_export.csv exists and contains a TOTAL row, a blank row, and dollar signs in the spend column.

Step 2: Load it and inspect the damage

Now read the file back the way you would read a real export, with pandas.read_csv. We keep spend and conversions as text for now, because the dollar signs and commas would otherwise force pandas to read them as strings anyway. Inspecting before cleaning is the habit that separates a reliable report from a flaky one.

df = pd.read_csv("campaign_export.csv")

print(df.shape)        # (9, 5)
print(df.dtypes)       # spend and conversions arrive as object (text)
print(df["channel"].unique())

The printout of df["channel"].unique() is the important one. You will see Google Ads, google_ads, Meta, meta ads, LinkedIn, TikTok, tiktok ads, plus TOTAL and a blank. That inconsistency is exactly what we fix next.

Checkpoint: df.shape prints (9, 5) and channel.unique() shows the same platform under multiple spellings.

Step 3: Clean the rows and the numbers

This is the heart of the job. We drop blank and summary rows, normalize the four channels to canonical names, and convert spend and conversions into real numbers. The mapping dictionary is the part you will edit for your own export; everything else carries over unchanged.

# Drop blank rows and the TOTAL summary row.
df = df.dropna(subset=["channel"])
df = df[df["channel"].str.upper() != "TOTAL"]

# Normalize channel names to four canonical buckets.
def canonical(name):
    n = name.strip().lower()
    if "google" in n:   return "Google"
    if "meta" in n:     return "Meta"
    if "linkedin" in n: return "LinkedIn"
    if "tiktok" in n:   return "TikTok"
    return name.strip()

df["channel"] = df["channel"].apply(canonical)

# Strip "$" and "," then convert to numbers.
df["spend"] = (df["spend"].str.replace(r"[$,]", "", regex=True).astype(float))
df["conversions"] = pd.to_numeric(df["conversions"], errors="coerce").fillna(0).astype(int)

print(df[["channel", "spend", "conversions"]])
print(df.dtypes)

After this runs, spend is a float, conversions is an int, and the channel column holds only Google, Meta, LinkedIn, and TikTok. The errors="coerce" argument is your safety net: anything that cannot become a number turns into NaN instead of crashing the script, and fillna(0) handles it.

Checkpoint: df.dtypes shows spend as float64 and conversions as int64, and no TOTAL row remains.

Step 4: Summarize spend and conversions per channel

Now collapse the campaign-level rows into one row per channel using groupby. Google had two campaigns and Meta had two; this folds them into single totals so leadership sees channels, not line items.

summary = (
    df.groupby("channel")
      .agg(spend=("spend", "sum"),
           conversions=("conversions", "sum"))
      .reset_index()
)

print(summary)

You should get four rows. Google sums to 1850.50 spend and 117 conversions, Meta to 1410.25 and 61, LinkedIn to 1510.00 and 12, TikTok to 725.75 and 40. That is your raw weekly performance, deduped across campaigns.

Checkpoint: summary has exactly four rows, one per channel, with spend and conversions summed.

Step 5: Add the metrics leadership actually asks for

Spend and conversions are inputs. What gets you taken seriously in the Monday meeting is cost per acquisition (CPA) and return on ad spend (ROAS). We compute CPA as spend divided by conversions, and ROAS assuming an average order value of $120 (change that constant to your real number). Guarding the divide-by-zero case matters, because a paused channel will have zero conversions.

AVG_ORDER_VALUE = 120  # set this to your real average order value

summary["cpa"] = (summary["spend"] / summary["conversions"].replace(0, np.nan)).round(2)
summary["revenue"] = summary["conversions"] * AVG_ORDER_VALUE
summary["roas"] = (summary["revenue"] / summary["spend"]).round(2)

summary = summary.sort_values("roas", ascending=False).reset_index(drop=True)
print(summary[["channel", "spend", "conversions", "cpa", "roas"]])

The sorted output tells the story at a glance. Google leads on ROAS, LinkedIn lags badly (12 conversions on $1,510 of spend is a CPA north of $125), and you now have the numbers to defend a budget shift. The .replace(0, np.nan) step is what stops a paused, zero-conversion channel from throwing a ZeroDivisionError and killing the run.

Checkpoint: summary now has cpa and roas columns and is sorted with the best ROAS channel on top.

Step 6: Chart it and save the report

A table is fine in a notebook; a chart is what lands in the deck. We plot ROAS by channel with matplotlib, save it as a PNG, and write the clean summary back out as a CSV you can attach to the weekly email. If you want a cross-tabbed view (channel by week once you have more dates), pivot_table is the natural next tool.

import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(7, 4))
ax.bar(summary["channel"], summary["roas"])
ax.set_title("Weekly ROAS by Channel (week of 2026-05-04)")
ax.set_ylabel("ROAS")
ax.axhline(1.0, linestyle="--", linewidth=1)  # break-even line
plt.tight_layout()
plt.savefig("weekly_roas.png", dpi=150)

summary.to_csv("weekly_summary.csv", index=False)
print("Saved weekly_roas.png and weekly_summary.csv")

Open weekly_roas.png and you have a bar chart with a dashed break-even line: any channel under it is losing money on the order-value assumption. weekly_summary.csv is the clean table, ready to attach.

Checkpoint: weekly_roas.png and weekly_summary.csv both exist, and the chart shows four bars with a break-even line.

Where this breaks

Your real channel names will not match my four buckets. The canonical function handles the spellings I invented, not the ones your ad platforms export. Run df["channel"].unique() on your actual file first, then add the substrings you see. The fix is always a one-line addition to that function, never a rewrite.

Conversions and spend rarely live in the same export. In the real world, spend comes from the ad platform and conversions come from your analytics or CRM, on different keys and often different date windows. When you merge two files, attribution mismatches show up as ROAS that looks too good or too grim. Reconcile the date ranges before you trust the number, and state your attribution window in the report so nobody argues about it later.

The average order value is a blunt instrument. A single AVG_ORDER_VALUE constant flatters channels that drive cheap, high-volume conversions and punishes ones that drive rare, expensive ones. Once the pipeline works, replace the constant with real per-conversion revenue joined in from your orders data. The structure of the script does not change; only the revenue line does.

Pasting raw exports straight in will eventually feed you a surprise column. Ad platforms rename and reorder columns without warning. Read the file, print df.columns, and select the columns you need by name rather than position, so a reordered export fails loudly instead of silently charting the wrong field.

What to build next

The obvious extension is to stop running this by hand: schedule the script weekly and have it email or Slack the CSV and PNG automatically. From there, layer in an LLM to draft the two-sentence commentary that goes above the chart, which pairs naturally with the workflow in how to use Claude at work. If the pandas in Step 3 felt shaky, the same cleaning patterns underpin every analyst workflow in Excel to Python, and if you are starting from zero, Python for adults maps the full path. You can absolutely lean on an assistant while you build; can ChatGPT teach me Python covers where that helps and where it quietly leads you wrong.

If you want to build this with someone watching your screen and catching mistakes in real time, that is literally what my sessions are. Book a free Discovery Call and bring your own campaign export to it; we will clean it together in the first session.

These tutorials come from the actual curriculum I teach 1-on-1. Every code block is tested before it ships.