Why this matters
A connector delivers spend, clicks and conversions. It does not deliver the ratios people actually read, and the easiest way to build them gives the wrong answer at every level above a single row. A dashboard with a wrong CPACost of each conversion. on the front page will be trusted until someone checks it by hand, and then nothing on the page will be trusted again.
The data sources and connectors guide explained where fields live and how their types are set. The calculated fields tutorial showed how to write ratios as a sum divided by a sum, group values with CASE and test against a figure you trust. The sample campaign dataset gives you material with known answers and a planted fault or two. This exercise uses all three.
The retailer and its data
Trailmark Outdoor Gear is an invented online retailer of tents, boots and walking kit. Its paid media export has the same columns as the sample dataset, so its new analyst practises on the sample first and connects the real accounts once every field passes its test. The six rows below are the sample rows the dataset page publishes; the campaign names belong to the dataset's own invented retailer.
Six rows from the sample campaign_daily table
| Row | date | channel | spend | impressions | clicks | conversions | revenue |
|---|---|---|---|---|---|---|---|
| A | 2026-01-14 | paid_search | 112.40 | 6,180 | 243 | 9 | 1,386.00 |
| B | 2026-01-14 | paid_social | 148.75 | 41,900 | 402 | 6 | 744.00 |
| C | 2026-01-14 | display | 60.00 | 118,300 | 171 | 2 | 230.00 |
| D | 2026-02-19 | paid_search | 109.90 | 6,020 | 236 | 0 | 0.00 |
| E | 2026-02-19 | video | 95.00 | 52,700 | 158 | 3 | 372.00 |
| F | 2026-03-21 | paid_social | 150.10 | 40,600 | 611 | 7 | 868.00 |
Trailmark's report needs four groups: Search, Social, Display and video, and Other.
Writing the calculated fields
Create every field in the data source, not in a chart, so each metric is defined once. Write each ratio as the summed numerator over the summed denominator, and guard the denominator so a row with nothing to divide by shows blank instead of an error. Finish every CASE with an ELSE so unexpected channel values stay visible. Then put each field in a table beside the raw columns and compare it with your own arithmetic.
Task
Write your answer before reading the worked answer.
- Write the formulas for CPA, return on ad spend (ROASRevenue generated per unit of ad spend.), click-through rate (CTRShare of impressions that result in a click.) and the channel grouping.
- Calculate by hand the CPA, ROAS and click-through rate for the six rows taken together.
- Calculate what a field written row by row and averaged would show instead, and explain the difference.
- Calculate CPA and ROAS for paid search and say what row D means for them.
- Write a field register entry for each field: name, formula, where it lives and the test you ran.
Worked answer
The formulas. Each ratio divides sums, so it recomputes at whatever grain the chart uses.
Trailmark's field register
| Field | Formula | Test |
|---|---|---|
| CPA | SUM(spend) / NULLIF(SUM(conversions), 0) | Six rows: £676.15 ÷ 27 = £25.04 |
| ROAS | SUM(revenue) / NULLIF(SUM(spend), 0) | Six rows: £3,600 ÷ £676.15 = 5.32 |
| CTR | SUM(clicks) / NULLIF(SUM(impressions), 0), formatted as a percentage | Six rows: 1,821 ÷ 265,700 = 0.69% |
| Channel group | See the CASE below | Row C and row E both show "Display and video" |
CASE
WHEN channel = "paid_search" THEN "Search"
WHEN channel = "paid_social" THEN "Social"
WHEN channel IN ("display", "video") THEN "Display and video"
ELSE "Other"
END
The hand check and the wrong method. Summing each column across the six rows and dividing gives the figures in the register. A field written as revenue divided by spend on each row, then averaged by the chart, gives every row equal weight regardless of spend, and the gap is large.
Ratio of sums against average of row ratios
The six rows total £676.15 of spend, 265,700 impressions, 1,821 clicks, 27 conversions and £3,600 of revenue.
| Metric | Ratio of sums | Average of the six row ratios |
|---|---|---|
| ROAS | 5.32 | 5.14 (12.33, 5.00, 3.83, 0.00, 3.92, 5.78) |
| CTR | 0.69% | 1.79% (3.93%, 0.96%, 0.14%, 3.92%, 0.30%, 1.50%) |
| CPA | £25.04 | £24.08 (row D has no conversions, so only five rows average) |
The click-through rate is out by more than double because the two search rows, with few impressions and a high rate, count as much as the display row with over a hundred thousand impressions.
Paid search and row D. Row D has normal spend and clicks and no conversions, which is the dataset's planted tracking gap.
Paid search with and without the tracking gap
Rows A and D together: £222.30 of spend, 9 conversions and £1,386 of revenue, so CPA £24.70 and ROAS 6.23. Row A alone: CPA £12.49 and ROAS 12.33. Including the gap day roughly doubles the channel's CPA and halves its ROAS, although row D's £109.90 of spend and 236 clicks look like any other day. Only the zero in the conversions column gives the fault away, which is why the table view beside the raw columns matters.
The field is right; the data is wrong. The report should flag the gap and show the figure with and without the
affected days, rather than letting a broken tag look like a collapse in performance. The channel grouping
uses the channel column, not the campaign name, so the dataset's planted rename cannot split a group.
Field register. Each field lives in the data source, with its formula, the hand check above and the date it was tested. When Trailmark connects its real accounts, the same tests run against one campaign's figures from each platform's interface.
Common mistakes
- Averaging a ratio. The chart's average of row-level ROAS or click-through rate is wrong at every level above a single row.
- No guard on the denominator. A day with no conversions returns an error instead of a blank.
- No ELSE branch. A new channel value disappears from every total.
- Fields built in charts. Each copy drifts; define the metric once in the source.
- Trusting a field because it compiles. Only a hand check against known figures shows it is right.
- Reading a tracking gap as performance. Spend and clicks with no conversions is a measurement fault.
Next, bring the whole level together in the first marketing report capstone, or return to the beginner marketing analytics level.
