User story: roll up the marketing campaign report

The data

google = pd.read_csv("google_ads.csv")
meta   = pd.read_csv("meta_ads.csv")
linked = pd.read_csv("linkedin_ads.csv")

Normalise each to a common schema

def normalise(df, channel, mapping):
    return (df.rename(columns=mapping)
              .assign(channel=channel)
              [["channel","campaign","date","spend","impressions","clicks","conversions","revenue"]])

g = normalise(google, "Google",   {"Campaign":"campaign","Day":"date","Cost":"spend",
                                    "Impr.":"impressions","Clicks":"clicks",
                                    "Conv.":"conversions","Conv. value":"revenue"})
m = normalise(meta,   "Meta",     {"Ad set name":"campaign","Reporting starts":"date",
                                    "Amount spent (USD)":"spend","Impressions":"impressions",
                                    "Link clicks":"clicks","Results":"conversions",
                                    "Purchase conversion value":"revenue"})
l = normalise(linked, "LinkedIn", {"Campaign Name":"campaign","Start Date":"date",
                                    "Total Spent":"spend","Impressions":"impressions",
                                    "Clicks":"clicks","Conversions":"conversions",
                                    "Conversion Value":"revenue"})

all_ads = pd.concat([g, m, l], ignore_index=True)
all_ads["date"] = pd.to_datetime(all_ads["date"])

Compute ratios

all_ads["CTR"]  = (all_ads["clicks"]      / all_ads["impressions"]).fillna(0)
all_ads["CPC"]  = (all_ads["spend"]       / all_ads["clicks"]).replace([float("inf")], 0).fillna(0)
all_ads["CPA"]  = (all_ads["spend"]       / all_ads["conversions"]).replace([float("inf")], 0).fillna(0)
all_ads["ROAS"] = (all_ads["revenue"]     / all_ads["spend"]).replace([float("inf")], 0).fillna(0)

Roll up

by_channel = (all_ads.groupby("channel")
                       .agg(spend=("spend","sum"),
                            revenue=("revenue","sum"),
                            conversions=("conversions","sum"))
                       .assign(ROAS=lambda d: (d["revenue"]/d["spend"]).round(2),
                               CPA= lambda d: (d["spend"]/d["conversions"]).round(2))
                       .sort_values("ROAS", ascending=False))

by_campaign = (all_ads.groupby(["channel","campaign"])
                       .agg(spend=("spend","sum"),
                            revenue=("revenue","sum"),
                            conversions=("conversions","sum"))
                       .assign(ROAS=lambda d: (d["revenue"]/d["spend"]).round(2))
                       .sort_values("ROAS", ascending=False)
                       .reset_index())

with pd.ExcelWriter("campaign_report.xlsx") as w:
    by_channel.to_excel(w, sheet_name="By Channel")
    by_campaign.to_excel(w, sheet_name="By Campaign", index=False)