Free tool

CTR Calculator

CTR, or click-through rate, is clicks divided by impressions, times 100. For several campaigns, the total CTR is all their clicks divided by all their impressions, never the average of their CTRs. Work out one campaign or several below, then copy the Google Sheets formula into your report.

Solve for
CTR
1.50%

360 clicks ÷ 24,000 impressions × 100 = 1.50%

Clicks in column B, impressions in column C. Format the cell as a percent.

How to calculate average CTR across campaigns

The average CTR a client means is the total: add up the clicks, add up the impressions, then divide. Don’t average the CTR column. A plain average counts a campaign with 1,000 impressions as much as one with 30,000, so the small campaign pulls the number toward its own rate. It’s an easy mistake to make: the top result for this search, Omni’s CTR calculator, defines average CTR as exactly that plain average.

Take the two campaigns pre-filled under Several campaigns. Campaign A got 50 clicks from 1,000 impressions, a 5.00% CTR. Campaign B got 300 clicks from 30,000 impressions, 1.00%. Average the two CTRs and you get 3.00%. Together, though, they got 350 clicks from 31,000 impressions, which is 1.13%. Report 3.00% to a client and you’ve told them their ads get clicked more than two and a half times as often as they do.

The average of two CTRs vs their total CTRCampaign A has 1,000 impressions and a 5.00% CTR. Campaign B has 30,000 impressions and a 1.00% CTR. Each campaign is drawn as wide as its impressions. The plain average of the two CTRs is 3.00%. All 350 clicks over all 31,000 impressions give a total CTR of 1.13%.Two campaigns: the average of their CTRs vs their total CTR0%1%2%3%4%5%Average of the two CTRs: 3.00%Total CTR: 1.13%A: 1,000 impressions, 5.00%B: 30,000 impressions, 1.00%Width = impressions, height = CTR
The total sits close to campaign B’s 1.00% because B has 30,000 of the 31,000 impressions.

In Google Sheets it’s one formula, with clicks in B and impressions in C:

=IFERROR(SUM(B2:B1000)/SUM(C2:C1000), "")

The formula reads rows 2 to 1000. If your data goes further, change 1000 to your last row in both places. Then format the cell as a percent.

Count the same clicks and impressions in every row

Meta reports more than one CTR for its ads. CTR (all) divides clicks (all) by impressions. CTR (link click-through rate) divides link clicks by impressions. Clicks (all) can include link clicks, reactions, comments and shares, clicks on your Page profile or profile picture, and clicks that expand an image to full screen. So on the same ad, CTR (all) is at least as high as link CTR.

One Facebook ad, two CTRsExample numbers. An ad gets 10,000 impressions and 180 clicks (all), of which 95 are link clicks and 85 are other clicks such as reactions or expanding the image. CTR (all) is 180 divided by 10,000, or 1.80%. Link CTR is 95 divided by 10,000, or 0.95%.One Facebook ad, two CTRsEXAMPLE NUMBERS · 10,000 IMPRESSIONSClicks (all): 18095 link clicks85 other clicksCTR (all) = 180 ÷ 10,000 = 1.80%Link CTR = 95 ÷ 10,000 = 0.95%Other clicks can include reactions, comments, shares, Page profile clicks and expanding the image.
Same ad, same impressions. Which clicks you count decides the CTR.

Before you add rows together, check that every row counts the same clicks. Link clicks in one row and clicks (all) in the next give a total that measures nothing. Meta’s unique CTR (all) is different again: it divides by reach, not impressions, so it doesn’t belong in a total over impressions either.

Search Console has the same trap with impressions. Its chart counts one impression when two of your pages show for the same search, while its table of pages counts each page, so Google says the chart and table totals can differ. Take clicks and impressions from the same view. Or, as our help center puts it: “Make sure the dimensions and metrics match.”

CTR formulas for Google Sheets and Excel

These use the same columns as the calculator above: clicks in B, impressions in C, and CTR in D, formatted as a percent.

  • CTR: =IFERROR(B2/C2, "")
  • Clicks from CTR and impressions: =IFERROR(ROUND(D2*C2, 0), "")
  • Impressions from clicks and CTR: =IFERROR(ROUND(B2/D2, 0), "")

IFERROR matters here. Google Ads reports can include rows for ads with 0 impressions and 0 clicks, and 0 divided by 0 is an error, so IFERROR leaves those cells blank. It blanks out any other error as well, so a blank CTR next to real clicks is worth a second look. The same formulas work in Excel, where IFERROR needs both arguments, and these formulas give both.

If you report CTR to clients every month, this is the part worth automating. SyncWith pulls clicks and impressions into Google Sheets and refreshes them on a schedule, from every 5 minutes to monthly, so you write the total formula once and it’s right after every refresh. Google Ads reports bring Clicks, Impressions and CTR for campaigns, ad groups or keywords. The Facebook Ads Performance & Clicks preset has Clicks, Link Clicks and Impressions side by side, so you can build either CTR. Search Console reports carry clicks, impressions and CTR too.

You can start on the free Hobby plan, with 35 report refreshes a month. Paid plans start at $24.99 a month (Google Sheets pricing).

Is 1.2% a good CTR?

Only next to your own numbers. Google’s help page on CTR says “a good CTR is relative to what you’re advertising and on which networks,” and gives no figure. So compare 1.2% with the same campaign last month, or another campaign on the same network, measured with the same CTR.

That last part is where published benchmarks break. Calculator pages that rank for this search put the average Google search CTR anywhere from 1.9% to 5%, and the average Facebook CTR anywhere from 0.9% to 1.73%. None of them says whether its Facebook figure is link CTR or CTR (all). On the example ad above, those two are 0.95% and 1.80%.

Pull clicks and impressions into Google Sheets on a schedule

Install SyncWith free, connect Google Ads, Facebook Ads or Search Console, and pick how often the sheet refreshes.

Install the add-on

To cite this calculator: SyncWith CTR Calculator, https://syncwith.com/tools/ctr-calculator.