Tabellab

Reading a Search Campaign Report: CTR, Average CPC, Conversion Rate and Cost per Conversion per Keyword

Lesson 1 of 20Lab

All lessons
  1. Reading a Search Campaign Report: CTR, Average CPC, Conversion Rate and Cost per Conversion per KeywordLabFree
  2. Keyword Match Types: Broad, Phrase and Exact Compared by Clicks, Cost, Cost per Conversion and Share of SpendLab Locked
  3. Quality Score: Expected CTR, Ad Relevance and Landing Page Experience, a Points Model and an Impression-Weighted AverageLab Locked
  4. Ad Rank and the Auction: Bid × Quality Score, the Actual CPC You Pay and the Bid Needed to Move UpLab Locked
  5. The Search Terms Report: Keywords versus Real Queries, Cost without Conversions and a Rule for ExclusionsLab Locked
  6. Negative Keywords: Finding Candidate Words with Wildcards, the Cost They Save and the Conversions They Would BlockLab Locked
  7. Search Impression Share: Eligible Impressions, Share Lost to Budget, Share Lost to Rank and the Account TotalLab Locked
  8. Ad Assets (Extensions): CTR by Asset Type, Uplift against Ads without Assets, Ranking and Cost per ConversionLab Locked
  9. Account Structure: Campaigns, Ad Groups and Keywords, Cost per Ad Group and a Check for Overloaded Ad GroupsLab Locked
  10. Bids from a Target CPA: Conversion Rate per Keyword, the Maximum Affordable CPC and Raise, Hold or LowerLab Locked
  11. Budget Pacing: Cumulative Cost, Planned Spend to Date with EOMONTH, Pacing Percentage and a Month-End ProjectionLab Locked
  12. What a Better Quality Score Is Worth: a Two-Way CPC Grid with Mixed References, a Heat Map and the Monthly SavingLab Locked
  13. Segments: Cost per Conversion by Device and Time of Day, Two-Condition SUMIFS and Bid AdjustmentsLab Locked
  14. Comparing Responsive Search Ads: CTR, Conversions per 1,000 Impressions, a Minimum-Data Rule and the Winner per Ad GroupLab Locked
  15. What More Budget Would Buy: Extra Impressions, Clicks, Cost and Conversions from Impression Share Lost to BudgetLab Locked
  16. Landing Page Check: Conversion Rate per Page, Slow and High-Bounce Pages, and What Slow Pages Cost in ConversionsLab Locked
  17. Trend and Forecast: Weekly Clicks with SLOPE and FORECAST.LINEAR, and the Cost of the Next Four WeeksLab Locked
  18. Brand, Non-Brand and Competitor Campaigns: Cost per Conversion, ROAS by Type and What the Blended Number HidesLab Locked
  19. Account Audit: Duplicate Keywords, Keywords without Impressions, Costly Non-Converters and Spend on Low Quality ScoresLab Locked
  20. Final Project: Quarterly Search Scorecard with Conversions, Cost per Conversion, Impression Share, Target Status and a Combo ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

What a Google Search campaign report really tells you

Open a Google Ads account for the first time and you face a wall of columns. Impressions, clicks, cost, conversions, CTR, average CPC, conversion rate, cost per conversion, impression share, Quality Score, and dozens more that can be switched on. It looks like a lot to learn. In fact almost every decision in a search account rests on four raw numbers and four ratios, and once you can calculate those yourself in a spreadsheet, the rest of the report stops being intimidating.

This free lesson teaches exactly that. You take a keyword report with impressions, clicks, cost and conversions, and you build the click-through rate (CTR), the average cost per click (CPC), the conversion rate and the cost per conversion, first for every keyword and then for the account as a whole. Along the way you meet the most common reporting mistake in paid search, averaging percentages, and see with real figures why it gives the wrong answer.

The example company is Tarnwick Cycles, a fictional online bike shop with a workshop. The company, its keywords and every number on this page are invented for learning; they are not benchmarks and not data from any real advertiser. The ten keywords are the same ones you work with in the interactive lab above, so you can check every figure in this article against your own sheet.

How a search ad gets from a keyword to a customer

A search campaign is built around keywords: words and phrases that describe what your customers type when they are looking for what you sell. When someone searches, the platform checks which advertisers have a matching keyword and runs an auction among them in a fraction of a second. If your ad wins a place on the results page, that is one impression. You pay nothing for it. Only when the person clicks the ad and lands on your website is a cost charged, which is why the model is called pay per click.

What happens after the click is up to your website. If the visitor does the thing you care about, such as placing an order, booking a service appointment or calling the shop, and your tracking records it, that is a conversion. So every keyword tells a short story in three steps: shown, clicked, converted. Each step loses most of the people from the step before, and the ratios between the steps are the numbers you manage.

From search to conversion: the Tarnwick Cycles account in 30 daysImpressions: 104,450the ad was shownCTR = 5,473 ÷ 104,450 = 5.24 %Clicks: 5,473someone visited the siteConversion rate = 224 ÷ 5,473 = 4.09 %Conversions: 224an order, booking or callCost £4,485.54 · avg. CPC £0.82 · cost per conversion £20.02
Figure 1: the four raw numbers of a search report and the ratios that connect them, with the account totals of the worked example (fictional data).

Figure 1 shows the story for the whole Tarnwick account in one month. The ads were shown 104,450 times. 5,473 of those impressions led to a click, a click-through rate of 5.24 %. Of the 5,473 visits, 224 ended in a conversion, a conversion rate of 4.09 %. The clicks cost £4,485.54 in total, which is £0.82 per click and £20.02 per conversion. Nothing else was needed to describe the month: four counts, four divisions.

The four raw numbers

  • Impressions: how often an ad was shown for the keyword. Impressions measure demand and visibility. A keyword with very few impressions is either rarely searched or rarely winning the auction.
  • Clicks: how often someone clicked the ad. Clicks are visits you paid for.
  • Cost: what those clicks cost in total. In a search campaign cost is the sum of the individual click prices, each of them set by the auction and, with manual bidding, normally no higher than your maximum bid.
  • Conversions: how often a click was followed by the action you defined as valuable. What counts as a conversion is your decision, and it should be the same definition for every keyword you compare.
A conversion count is only as good as the tracking behind it. Conversion tracking depends on the visitor's consent choices where the law requires consent, and some conversions are modelled rather than observed. Respect those choices, and treat the count as a careful estimate, not as a cash register.

The four ratios, and why each one exists

Raw counts cannot be compared. A keyword with 795 clicks is not better than one with 258 clicks; it is just bigger. Ratios remove the size and leave the quality. Each of the four standard ratios answers one specific question.

  • CTR (click-through rate) = clicks ÷ impressions. Does the ad answer what the person was looking for? A high CTR means keyword, ad text and search intent fit together.
  • Average CPC = cost ÷ clicks. What does one visit cost? It reflects how competitive the keyword is and how well your ad ranks.
  • Conversion rate = conversions ÷ clicks. What does the website do with the visit? It reflects the landing page, the offer and how ready to act the searcher was.
  • Cost per conversion = cost ÷ conversions. What do you pay for one result? This is the number to hold against your target, because it joins the price of a visit and the chance that it pays off.

The fourth ratio is not independent of the others. Because cost ÷ conversions equals (cost ÷ clicks) ÷ (conversions ÷ clicks), the cost per conversion is the average CPC divided by the conversion rate. That identity is the most useful sentence in paid search. It says there are exactly two ways to make a conversion cheaper: pay less per click, or convert more of the clicks. Every optimisation you will ever do, from negative keywords to a faster landing page, works through one of those two levers.

Worked example: ten keywords of Tarnwick Cycles

Here is the keyword report from the lab. The first five columns are the raw export; the last four are the ratios you calculate. The bottom row holds the account totals.

KeywordImpressionsClicksCost £Conv.CTRAvg. CPC £Conv. rateCost / conv. £
gravel bike18,400736662.40144.00 %0.901.90 %47.31
road bike sale12,650506531.3094.00 %1.051.78 %59.03
buy gravel bike online3,900273316.68127.00 %1.164.40 %26.39
carbon road bike9,800294396.9043.00 %1.351.36 %99.22
bike shop near me15,200912510.72316.00 %0.563.40 %16.47
electric bike26,5007951,057.35113.00 %1.331.38 %96.12
e-bike for commuting5,200312371.28106.00 %1.193.21 %37.13
kids bike 20 inch4,300258152.22136.00 %0.595.04 %11.71
bike servicing6,100427285.09247.00 %0.675.62 %11.88
tarnwick cycles2,400960201.609640.00 %0.2110.00 %2.10
Account total104,4505,4734,485.542245.24 %0.824.09 %20.02

In the lab sheet the keywords are in column A, impressions in B, clicks in C, cost in D and conversions in E, starting in row 4. The four formulas for the first keyword are:

  • CTR (F4): =C4/B4
  • Average CPC (G4): =D4/C4
  • Conversion rate (H4): =E4/C4
  • Cost per conversion (I4): =D4/E4

Step by step in Excel or Google Sheets

  1. Click F4 and type =C4/B4, then press Enter. The cell shows 0.04. Format it as a percentage and it reads 4.00 %.
  2. Select F4 again and drag the small square at the bottom right of the cell down to F13 (or copy and paste). The spreadsheet adjusts the row numbers by itself: F5 becomes =C5/B5, F6 becomes =C6/B6. This is what a relative reference means.
  3. Do the same in G4 with =D4/C4 and format the column as money with two decimals.
  4. In H4 type =E4/C4, format as a percentage, copy down.
  5. In I4 type =D4/E4, format as money, copy down.
  6. In row 14 add up the raw columns with =SUM(B4:B13) and so on. Then calculate the account ratios from those totals, for example the account CTR with =C14/B14.

Walk through a few rows to see what the numbers say. gravel bike was shown 18,400 times and clicked 736 times, a CTR of 4.00 %. The clicks cost £662.40, or £0.90 each, and 14 of them converted, a conversion rate of 1.90 %. One conversion therefore cost £47.31. Check the identity: £0.90 ÷ 0.0190 is about £47.3. The longer keyword buy gravel bike online has far fewer impressions, but its CTR is 7.00 % and its conversion rate 4.40 %. Although each click is more expensive at £1.16, a conversion costs only £26.39. The person who types the word buy is simply closer to a decision.

At the other end, carbon road bike and electric bike each cost close to £100 per conversion (£99.22 and £96.12). Both combine an above-average click price with a conversion rate below 1.5 %. electric bike alone used £1,057.35, almost a quarter of the month's cost, for 11 conversions. That does not automatically make it a bad keyword, since an e-bike order is worth far more than a kids bike order, but it is the first place to look, and later lessons give you the tools: the search terms behind the keyword, its Quality Score, and its landing page.

Cost per conversion by keyword (£)tarnwick cycles2.10kids bike 20 inch11.71bike servicing11.88bike shop near me16.47buy gravel bike online26.39e-bike for commuting37.13gravel bike47.31road bike sale59.03electric bike96.12carbon road bike99.22account: 20.02at or below the account figureabove the account figure
Figure 2: cost per conversion of the ten keywords from the lab, sorted. The dashed line is the account figure of total cost ÷ total conversions (fictional data).

Figure 2 sorts the keywords by cost per conversion. Four keywords sit below the account figure of £20.02 and six above it. Notice how wide the range is: from £2.10 for the brand keyword to £99.22 for carbon road bike, a factor of almost fifty inside one small account. That spread is normal, and it is the reason a keyword-level view is worth the effort.

The total row: never average the ratios

Now the most important detail of the lesson. What is the CTR of the whole account? The tempting formula is =AVERAGE(F4:F13), the average of the ten keyword CTRs. It returns 8.60 %. The correct formula is total clicks ÷ total impressions, =C14/B14, and it returns 5.24 %. The two answers are far apart, and only the second one is true: 5,473 clicks really did come from 104,450 impressions.

The reason is the brand keyword. tarnwick cycles has a CTR of 40 %, because people who type the shop's name want the shop. But it has only 2,400 impressions, about 2 % of the account. A plain average gives this small keyword the same weight as electric bike with 26,500 impressions and drags the result upward. Dividing the totals weights every keyword by its real size automatically.

The rule holds for every ratio and every level of the account: add up first, then divide. Campaign CTR, ad group conversion rate, monthly cost per conversion, impression share for a quarter: always build them from summed counts, never from averaged percentages. The lab checks exactly this in its last task.

How to read the result

With the eight columns in place the report starts to suggest actions. Start with the account line: £20.02 per conversion at a conversion rate of 4.09 %. Whether that is good depends entirely on what a conversion is worth to Tarnwick, which no advertising report can tell you. A £20 cost for a £900 bike order is excellent; the same cost for a £35 service booking is not.

Then separate the brand keyword from the rest. tarnwick cycles delivers 96 of the 224 conversions for £201.60. Without it, the nine generic keywords produced 128 conversions for £4,283.94, which is £33.47 per conversion, and their combined CTR is 4.42 %. That is a very different account from the blended £20.02. People searching for the brand already know the shop, and many would have found it anyway. Lesson 18 of the course is devoted to this split.

Finally, read the two levers for each keyword. The combination of CTR and conversion rate points to where the problem, or the opportunity, sits:

  • High CTR, high conversion rate (bike servicing, kids bike 20 inch, buy gravel bike online): the keyword, the ad and the page fit. Check whether the keyword could get more impressions.
  • High CTR, low conversion rate: the ad attracts clicks the page cannot convert. Look at the landing page and at the search terms; the ad may be promising something the page does not deliver.
  • Middling CTR, modest conversion rate (gravel bike, road bike sale): general keywords typed by people who are still comparing. A more specific ad text, or splitting the keyword into more precise ones, can raise both rates.
  • Low CTR, low conversion rate, high CPC (carbon road bike, electric bike): broad, competitive keywords. Check which search terms they really match before spending more.

Common mistakes with search campaign metrics

  • Averaging percentages. As shown above, the average of keyword CTRs overstates the account CTR here by more than three percentage points. Always divide totals.
  • Dividing by the wrong base. In search, the conversion rate divides by clicks, not by impressions. Conversions ÷ impressions is a different (and much smaller) number.
  • Turning a ratio upside down. Clicks ÷ cost is clicks per pound, not cost per click. If an average CPC comes out as 1.2 when you expected about 80 pence, check the order of the division.
  • Judging keywords with too little data. A keyword with 20 clicks and no conversion has not proven anything yet. At a 4 % conversion rate you would expect less than one conversion from 20 clicks.
  • Comparing conversions that are not comparable. If one keyword's conversions are orders and another's are newsletter sign-ups, the cost per conversion means different things. Keep the definition constant or compare by value.
  • Typing results instead of formulas. A typed 4 % is wrong the moment the data change. The lab only accepts formulas for that reason.
  • Reading CTR as success. A high CTR on a keyword that never converts is a cost, not an achievement.

What is a good CTR, CPC or conversion rate?

This is the question every beginner asks, and the honest answer is that published averages are of limited use. CTR depends heavily on the position of the ad, on whether the keyword is a brand term, and on the industry. Click prices differ by a factor of fifty between sectors and change with the season and the competition. Conversion rates depend on what you count as a conversion. Any figure you read, including the ones in this article, should be treated as illustrative.

The comparisons that do help are inside your own account: this keyword against that one, this month against the last, the generic keywords against the target cost per conversion that your margins allow. That is why the course teaches you to build the numbers yourself. The arithmetic is durable; platform features, layouts and typical prices are not.

Formatting and sanity checks

A report that is easy to read gets read. Three habits make a difference. Format rates as percentages with two decimals and money with two decimals, so that 0.04 and 4.00 % are never confused. Keep raw data and calculated columns visually apart; in the lab the cells you fill are shaded. And test every new column against a number you can verify in your head: 736 clicks from 18,400 impressions is 4 %, because 1 % of 18,400 is 184.

  1. Check that no ratio is above 100 % where that is impossible (CTR, conversion rate).
  2. Check the identity on one row: average CPC ÷ conversion rate should equal the cost per conversion.
  3. Check that the total row was built from sums. If the account CTR lies outside the range of plausible values, an average has crept in.
  4. Decide what a keyword with zero conversions should show. Cost ÷ 0 is an error; later lessons handle such rows with IF.
In Excel and Google Sheets you can see every formula at once with Ctrl + ` (the key left of the 1). It is the quickest way to spot a typed number hiding among formulas.

What the basic report does not show

The eight columns describe what happened to the ads that were shown. They are silent about three things. They do not show the searches where your ad was not shown; that is the subject of impression share. They do not show why a click cost what it did; that is decided by Ad Rank, the combination of your bid and your Quality Score. And they do not show which searches were actually behind a keyword, since the keyword gravel bike can match many different queries, some of them useless.

These three gaps are where most of the money in a search account is won or lost, and they are the core of this course. The basic ratios stay with you throughout: every later lesson uses CTR, CPC, conversion rate or cost per conversion as an ingredient.

What the rest of the course covers

The course has 20 interactive labs, all on the fictional Tarnwick Cycles account, each with its own data set and instant checking of every formula. After this free first lab the path is:

  • Keywords and match types (lesson 2): broad, phrase and exact compared by cost per conversion with SUMIFS.
  • Quality Score and Ad Rank (lessons 3, 4 and 12): the three components, a simplified auction, the actual CPC you pay, and a what-if grid that prices a better score.
  • Search terms and negatives (lessons 5 and 6): what people really typed, a two-condition rule for exclusions, and testing negative keywords with wildcards before adding them.
  • Impression share (lessons 7 and 15): eligible impressions, share lost to budget and to rank, and a forecast of what more budget would buy.
  • Assets and ads (lessons 8 and 14): CTR uplift from sitelinks, callouts and other assets, and a fair comparison of two responsive search ads.
  • Structure, bids and budget (lessons 9, 10, 11 and 13): campaigns and ad groups, a maximum CPC from a target CPA, budget pacing with dates, and device and time-of-day segments.
  • Quality checks and outlook (lessons 16 to 19): landing pages, a trend forecast, brand against non-brand, and a monthly account audit.
  • Final project (lesson 20): a quarterly scorecard with a status per campaign, a highlight rule and a combo chart, followed by the exam and the certificate.

You do not need any advertising account to follow the course, and nothing in it depends on a particular version of the platform. If you can divide one cell by another, you are ready for lab 1. Reminder: Tarnwick Cycles and all figures are fictional, and no result shown here is a prediction of what any real campaign will achieve.

Frequently asked questions

How do I calculate CTR for Google Search ads in Excel?

Divide clicks by impressions, for example =C4/B4, and format the cell as a percentage. For a whole campaign or account divide total clicks by total impressions; do not average the CTRs of the keywords.

What is the difference between CPC and cost per conversion?

Average CPC is cost divided by clicks: the price of one visit. Cost per conversion is cost divided by conversions: the price of one result. The two are linked, because cost per conversion equals average CPC divided by the conversion rate.

Why is the average of my keyword CTRs different from the account CTR?

A plain average gives every keyword the same weight, whatever its size. A small brand keyword with a 40 % CTR then pulls the average up. In the worked example the average of the ten CTRs is 8.60 %, while total clicks divided by total impressions give 5.24 %.

Is the conversion rate calculated on clicks or on impressions?

For search campaigns the conversion rate is conversions divided by clicks. It measures what the landing page and the offer do with a visit. Dividing by impressions gives a different, much smaller figure.

What is a good cost per conversion for search ads?

There is no general answer. A cost per conversion is good when it is below what a conversion is worth to the business after product costs. Published industry averages are illustrative at best; compare keywords and months within your own account against your own target.

Do I need a Google Ads account to take this course?

No. Every lab runs in a spreadsheet in the browser with fictional data from the invented shop Tarnwick Cycles. You learn the calculations and the reasoning, which you can then apply to an export from any real account.

Keep going: Google Search Ads Campaigns: Keywords & Match Types, Quality Score & Ad Rank, Search Terms, Negatives, Impression Share & Account Structure

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Keyword Match Types: Broad, Phrase and Exact Compared by Clicks, Cost, Cost per Conversion and Share of Spend
  2. Quality Score: Expected CTR, Ad Relevance and Landing Page Experience, a Points Model and an Impression-Weighted Average
  3. Ad Rank and the Auction: Bid × Quality Score, the Actual CPC You Pay and the Bid Needed to Move Up
  4. The Search Terms Report: Keywords versus Real Queries, Cost without Conversions and a Rule for Exclusions
  5. Negative Keywords: Finding Candidate Words with Wildcards, the Cost They Save and the Conversions They Would Block
  6. Search Impression Share: Eligible Impressions, Share Lost to Budget, Share Lost to Rank and the Account Total
  7. Ad Assets (Extensions): CTR by Asset Type, Uplift against Ads without Assets, Ranking and Cost per Conversion
  8. Account Structure: Campaigns, Ad Groups and Keywords, Cost per Ad Group and a Check for Overloaded Ad Groups
  9. Bids from a Target CPA: Conversion Rate per Keyword, the Maximum Affordable CPC and Raise, Hold or Lower
  10. Budget Pacing: Cumulative Cost, Planned Spend to Date with EOMONTH, Pacing Percentage and a Month-End Projection
  11. What a Better Quality Score Is Worth: a Two-Way CPC Grid with Mixed References, a Heat Map and the Monthly Saving
  12. Segments: Cost per Conversion by Device and Time of Day, Two-Condition SUMIFS and Bid Adjustments
  13. Comparing Responsive Search Ads: CTR, Conversions per 1,000 Impressions, a Minimum-Data Rule and the Winner per Ad Group
  14. What More Budget Would Buy: Extra Impressions, Clicks, Cost and Conversions from Impression Share Lost to Budget
  15. Landing Page Check: Conversion Rate per Page, Slow and High-Bounce Pages, and What Slow Pages Cost in Conversions
  16. Trend and Forecast: Weekly Clicks with SLOPE and FORECAST.LINEAR, and the Cost of the Next Four Weeks
  17. Brand, Non-Brand and Competitor Campaigns: Cost per Conversion, ROAS by Type and What the Blended Number Hides
  18. Account Audit: Duplicate Keywords, Keywords without Impressions, Costly Non-Converters and Spend on Low Quality Scores
  19. Final Project: Quarterly Search Scorecard with Conversions, Cost per Conversion, Impression Share, Target Status and a Combo Chart

Buy course — $9.90 See the full course