Find insights in campaign results

Do the maths in your spreadsheet and the interpreting with AI. Turn campaign results into insights written as observation, hypothesis and how to check, then pick your next experiment.

Step by step

1. 1

   Agree on the metrics first

   Before you show any numbers to an assistant, agree on the definitions as a team. Even conversion rate can use a different denominator depending on the platform — "interactions" such as video views rather than clicks — and some tools use other names, as Google Ads does with "cost per conversion" for CPA and "conversion value per cost" for ROAS.

   This guide uses the September ads for the 10-minute home workout app from the earlier guides. A conversion is a paid subscription starting, revenue is the first payment, and all the numbers are made up.

   The metric definitions used in this guide

   Metric	Formula	What it tells you
   CTR (clickthrough rate)	Clicks ÷ impressions	How often an ad impression turns into a click
   CVR (conversion rate)	Conversions ÷ clicks	How often a click turns into a subscription
   CPA (cost per acquisition)	Cost ÷ conversions	Ad spend per new subscription
   ROAS (return on ad spend)	Revenue ÷ cost	Revenue for each unit of ad spend (100% means revenue equals spend)

   Check it

   • Write the definitions down in a shared team doc and make sure every report uses them.
2. 2

   Calculate in the sheet, and get the formulas too

   Assistants sometimes get sums wrong. Calculate every number you'll report in the sheet, and ask the assistant for both the results and the formulas so you can compare them with the sheet.

   Paste the data as CSV (values separated by commas) so the assistant doesn't mix up the columns. Include the date range and how conversions were counted.

   September ad results (example, 1–28 September)

   	A	B	C	D	E	F
   1	Channel	Impressions	Clicks	Conversions	Cost (₩)	Revenue (₩)
   2	Search ads	40,000	1,600	96	1,440,000	3,024,000
   3	Social feed A (time-saving)	250,000	3,000	75	1,500,000	2,100,000
   4	Social feed B (myth-busting)	250,000	2,000	70	1,500,000	2,310,000
   5	Video	180,000	1,260	25	900,000	750,000
   6	Display	300,000	900	3	300,000	90,000
   	Total	1,020,000	8,760	269	5,640,000	8,274,000

   Here are our September ad results as CSV. Every channel ran from 1 to 28 September, and conversions (paid subscriptions started) and revenue (first payments) were counted in our analytics tool on a last-click basis.
   Channel,Impressions,Clicks,Conversions,Cost,Revenue
   Search ads,40000,1600,96,1440000,3024000
   Social feed A (time-saving),250000,3000,75,1500000,2100000
   Social feed B (myth-busting),250000,2000,70,1500000,2310000
   Video,180000,1260,25,900000,750000
   Display,300000,900,3,300000,90000
   Calculate CTR (clicks ÷ impressions), CVR (conversions ÷ clicks), CPA (cost ÷ conversions) and ROAS (revenue ÷ cost) for each channel as a table, with a total row at the end. Round CTR and CVR to one decimal place as percentages, CPA to the nearest won and ROAS to a whole percentage. Assume the data sits in Excel range A1:F6, and give me the formula for each metric.

   Put these formulas in row 2 of columns G to J and fill them down to row 6. Format CTR and CVR as percentages with one decimal place and ROAS as a whole percentage, and the sheet will match the table below.

   For the total row (row 7), add up columns B to F with SUM, then divide the totals — rather than averaging the rates.

   =C2/B2

   Column G, CTR: clicks ÷ impressions

   =D2/C2

   Column H, CVR: conversions ÷ clicks

   =E2/D2

   Column I, CPA: cost ÷ conversions

   =F2/E2

   Column J, ROAS: revenue ÷ cost

   =C7/B7

   Total-row CTR: total clicks ÷ total impressions. Do the other metrics the same way within row 7.

   The metrics the assistant calculated (example)

   Channel	CTR	CVR	CPA	ROAS
   Search ads	4.0%	6.0%	₩15,000	210%
   Social feed A (time-saving)	1.2%	2.5%	₩20,000	140%
   Social feed B (myth-busting)	0.8%	3.5%	₩21,429	154%
   Video	0.7%	2.0%	₩36,000	83%
   Display	0.3%	0.3%	₩100,000	30%
   Total	0.9%	3.1%	₩20,967	147%

   Check it

   • Put the same formulas in your sheet and compare the results with the assistant's table cell by cell. Where they differ, use the sheet's value.
   • Total-row rates must divide totals. Averaging the five channel CTRs gives 1.4%, but the real overall CTR is 8,760 ÷ 1,020,000 ≈ 0.9%.
3. 3

   Write insights as observation → hypothesis → check

   An insight isn't a conclusion; it's a hypothesis to check. Asking for an observation (numbers from the table), a hypothesis (why it might be so) and a check (how to find out) keeps convincing interpretations from passing as facts.

   The sums are done, so hand the assistant only the interpreting. Give it a rule for channels with very few conversions, so it doesn't draw conclusions from them.

   Using the raw data and the metrics table above, write three insights. Give each one as observation → hypothesis → how to check, and use only numbers from the tables in the observations. For any channel with fewer than 10 conversions, draw no conclusion and just say the sample is too small.

   Here are three insights. Treat each one as a hypothesis to check.

   1) Observation: Social feed B has a lower CTR than A (0.8% vs 1.2%) but a higher CVR (3.5% vs 2.5%) and ROAS (154% vs 140%). Hypothesis: the myth-busting message may draw fewer clicks but more committed subscribers. Check: compare the two ads' mix of monthly and annual plans, and their 30-day retention.

   2) Observation: search ads have the highest CVR (6.0%) and ROAS (210%) but the fewest impressions (40,000). Hypothesis: these are people already looking for a workout app, and because only so many people search, a bigger budget may not keep the same efficiency. Check: raise the budget by 20% for two weeks and watch how impressions and CPA change.

   3) Observation: video's ROAS is 83% — its revenue (₩750,000) is below its cost (₩900,000). Hypothesis: people who saw the video and later subscribed through a search ad were credited to search under last click. Check: in your analytics tool's attribution reports, see how often video appears on the paths to a subscription.

   Display has only 3 conversions, so the sample is too small to judge.

   Check it

   • Match every number in the observations against the tables. If one isn't in them, ask for it to be removed.
   • Give each check a date and an owner, so the hypothesis actually gets tested instead of just sitting in a report.
4. 4

   Avoid the traps that mislead

   An assistant can read the numbers convincingly, but it doesn't know the conditions behind them. Before you report an insight, check it against the four traps below.

   Take the conversion window: how many days after an ad click a conversion still counts. A shorter window records fewer conversions, so numbers from platforms with different settings can't be compared as they are.

   Traps when reading campaign results

   Trap	In this campaign	How to avoid it
   Tiny samples	Display has 3 conversions. One more would move its CPA from ₩100,000 to ₩75,000	Run it longer, or judge it together with similar channels
   Different periods or budgets	Same dates here, but budgets range from ₩300,000 to ₩1,500,000 — five times apart	Compare like with like, and note the budget next to each result
   Platforms counting differently	Attribution models and conversion windows differ by platform, so adding up each ad platform's own conversion counts may not match your real subscriptions	Recount everything on one basis, such as your analytics tool or payment data
   Averages hiding gaps	Overall ROAS of 147% looks healthy, but video (83%) and display (30%) are below 100%	Break results down by channel, device or age group

   Check it

   • Run each of your three insights past the four traps. In the example, insight 2 (a bigger search budget) runs straight into "different periods or budgets".
5. 5

   Report in three lines and pick the next test

   Keep the report short and narrow the next step down to one. Use only numbers you've checked in the sheet, and label any unconfirmed interpretation as a hypothesis.

   You can draft the next variations with the method in "Draft A/B ad copy with AI". When the results come in, calculate and interpret them again in the order of this guide.

   Write a three-line report for my manager from these insights: line 1 the overall result, line 2 the hypothesis to check first, line 3 the next experiment. Keep each line to 20 words or fewer, and label anything unconfirmed as a hypothesis.

   1. September: ₩5.64M in ad spend brought 269 subscriptions and about ₩8.27M in revenue (ROAS 147%).

   2. Hypothesis: ad B draws fewer clicks but more committed subscribers.

   3. Next test: run A and B two more weeks on equal budgets and compare plan types.

   Check it

   • Check the report's numbers against the sheet: ₩5,640,000 is ₩5.64M and ₩8,274,000 is about ₩8.27M.
   • Change one thing per experiment. Change the ad and the budget together and you can't tell which one made the difference.