Get correct weighted CTR and ROAS totals in an ad report

Five campaigns have CTRs of 7.50, 1.50, 0.20, 1.20 and 0.30 %. If the report's "overall CTR" reads 2.14%, someone averaged the averages. A campaign with 42,000 impressions and one with 1,250,000 got the same weight, so the number drifts far from reality. An overall rate is the sum of the numerators divided by the sum of the denominators.

[1] Build the test data: two-week totals for five made-up campaigns, with cost and revenue in dollars.

campaign,impressions,clicks,cost,conversions,revenueBrand Search,42000,3150,1260,210,8400Generic Search,380000,5700,4560,190,9120Display Prospecting,1250000,2500,2250,50,2750Retargeting,160000,1920,1152,96,5760Video,600000,1800,1500,18,900

[2] Start with a short request that names the metrics and says nothing about how to compute them. The command is claude -p --model claude-sonnet-5-5 --max-turns 1 --tools "" --setting-sources "" --strict-mcp-config < prompt.txt.

Write a weekly summary from the campaign performance CSV below. Be sure to include overall CTR, CPA and ROAS. {paste the CSV}

Both runs got the overall figures right: CTR 0.62%, CPA 19.01, ROAS 2.51x. This model did not fall into the simple-average trap. What it added is the problem.

• Metrics nobody asked for. One run added a spend-share column; the other added "Blended CPC is 0.71 and click-to-conversion rate is 3.74%".
• Percentages that sit in no table, such as "22.5% of spend and 52.6% of revenue" or "35% of spend but 13.6% of revenue". They happened to be right, but there is nowhere to check them.
• One of those extra numbers was wrong. The first run ended with "Without Video, blended ROAS would be about 2.6x (26,030 / 9,222)". 26,030 / 9,222 is 2.82.

The totals were fine and the surrounding numbers were not reliable, and the next run, model or longer CSV may get the totals wrong too.

[3] State the formulas and the output shape. This is the "give the logic explicitly" advice in OpenAI's prompt engineering guide applied to a report: give the formulas, print the sums under the table, and put the simple average beside the correct figure.

The CSV inside <data> holds two-week totals for 5 ad campaigns. Columns: impressions, clicks, cost (USD), conversions, revenue (USD, conversion value). Rules:- Compute overall metrics from sums, not by averaging campaign metrics. Overall CTR = sum of clicks / sum of impressions, overall CPA = sum of cost / sum of conversions, overall ROAS = sum of revenue / sum of cost.- Table 1 first: CTR (%), CPA (USD) and ROAS per campaign, with a final "Overall" row. Under it, list the five sums you used (impressions, clicks, cost, conversions, revenue).- Table 2: overall CTR, CPA and ROAS computed two ways, "from sums" and "simple average of campaign values", with the difference in one column.- End with at most 3 summary sentences. Do not use any number that is not in the tables.- CTR to 2 decimals, ROAS to 2 decimals, CPA to the cent. <data>{paste the CSV}</data>

The line "Do not use any number that is not in the tables" is what stops the shares and the what-if figures. In both runs the answer was two tables and a short summary.

[4] The result (first run). Table 2 put the from-sums figures 0.62, 19.01, 2.51 next to the simple averages 2.14, 34.07, 3.10; the second run gave the same values.

**Table 1: Campaign metrics** | Campaign | CTR (%) | CPA (USD) | ROAS ||---|---|---|---|| Brand Search | 7.50 | 6.00 | 6.67 || Generic Search | 1.50 | 24.00 | 2.00 || Display Prospecting | 0.20 | 45.00 | 1.22 || Retargeting | 1.20 | 12.00 | 5.00 || Video | 0.30 | 83.33 | 0.60 || **Overall** | **0.62** | **19.01** | **2.51** | Sums used for the Overall row:- Impressions: 2,432,000- Clicks: 15,070- Cost: $10,722- Conversions: 564- Revenue: $26,930

[5] Check it in a spreadsheet. Paste the CSV so that column A holds the campaign and columns B to F hold impressions, clicks, cost, conversions and revenue. Put the sums in row 7.

CTR   =SUM(C2:C6)/SUM(B2:B6)CPA   =SUM(D2:D6)/SUM(E2:E6)ROAS  =SUM(F2:F6)/SUM(D2:D6)

You should get 0.62%, 19.01 and 2.51. AVERAGE of the per-row CTR (=C2/B2) gives 2.14%, the same as the simple-average column in Table 2.

[6] If you do this weekly, script the check of the "Overall" row. It finds the line with "Overall", recomputes the figures from the CSV sums and compares.

#!/usr/bin/env python3"""usage: check.py ads.csv out.txt"""import csv, re, sysr = list(csv.DictReader(open(sys.argv[1], encoding="utf-8")))s = lambda k: sum(float(x[k]) for x in r)want = {"CTR": s("clicks")/s("impressions")*100, "CPA": s("cost")/s("conversions"), "ROAS": s("revenue")/s("cost")}row = next(l for l in open(sys.argv[2], encoding="utf-8") if "|" in l and re.search("Overall", l))got = [float(c.strip(" *").replace(",", "")) for c in row.strip().strip("|").split("|")[1:4]]bad = 0for (k, w), g in zip(want.items(), got):    ok = abs(g - w) <= (0.5 if w > 1000 else 0.005)    bad += not ok    print("OK  " if ok else "DIFF", k, g, f"{w:,.4f}")sys.exit(bad > 0)

$ python3 -I check.py ads.csv out.fixed.en.1.txt; echo "exit $?"OK   CTR 0.62 0.6197OK   CPA 19.01 19.0106OK   ROAS 2.51 2.5117exit 0

The second run passed as well. For a failing case, the Overall row of the first result was replaced by hand with the simple averages (2.14, 34.07, 3.10). The model did not make this error.

$ python3 -I check.py ads.csv out-bad.en.txt; echo "exit $?"DIFF CTR 2.14 0.6197DIFF CPA 34.07 19.0106DIFF ROAS 3.1 2.5117exit 1

Things still slipped. The figures were right, but the explanation of why the simple average comes out high was loose, and both English summaries ran to four sentences despite "at most 3". The script checks neither the reasoning nor the length, so read them.

With thousands of CSV rows the sums themselves can go wrong, so total by campaign in the sheet first and paste the totals. Do not skip the formula check in step [5] when the budget is large. The same recompute-and-compare habit is in Recompute the averages and ratios an AI states from the CSV.