Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics and Reporting › Beginner

Building Calculated Fields for an Outdoor Gear Retailer

A practice run at turning raw campaign columns into metrics a report can trust. Working on the synthetic campaign dataset on behalf of an invented outdoor gear retailer, you write calculated fields for cost per acquisition, return on ad spend, click-through rate and a channel grouping, then check each one by hand.
ExerciseBeginner levelModule 3: Working with data in Looker Studio5 minUpdated

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 CPA 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.

Illustrative example — not a real result

Six rows from the sample campaign_daily table

Rowdatechannelspendimpressionsclicksconversionsrevenue
A2026-01-14paid_search112.406,18024391,386.00
B2026-01-14paid_social148.7541,9004026744.00
C2026-01-14display60.00118,3001712230.00
D2026-02-19paid_search109.906,02023600.00
E2026-02-19video95.0052,7001583372.00
F2026-03-21paid_social150.1040,6006117868.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.

  1. Write the formulas for CPA, return on ad spend (ROAS), click-through rate (CTR) and the channel grouping.
  2. Calculate by hand the CPA, ROAS and click-through rate for the six rows taken together.
  3. Calculate what a field written row by row and averaged would show instead, and explain the difference.
  4. Calculate CPA and ROAS for paid search and say what row D means for them.
  5. 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.

Illustrative example — not a real result

Trailmark's field register

FieldFormulaTest
CPASUM(spend) / NULLIF(SUM(conversions), 0)Six rows: £676.15 ÷ 27 = £25.04
ROASSUM(revenue) / NULLIF(SUM(spend), 0)Six rows: £3,600 ÷ £676.15 = 5.32
CTRSUM(clicks) / NULLIF(SUM(impressions), 0), formatted as a percentageSix rows: 1,821 ÷ 265,700 = 0.69%
Channel groupSee the CASE belowRow 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.

Illustrative example — not a real result

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.

MetricRatio of sumsAverage of the six row ratios
ROAS5.325.14 (12.33, 5.00, 3.83, 0.00, 3.92, 5.78)
CTR0.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.

Illustrative example — not a real result

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.

Pages this lesson applies

Terms used on this page

Also related

Related