Formulas

CTR formula

Click-through rate (CTR) is the share of people who saw your ad and clicked it.

About the Google Ads connector

The formula has two inputs and one division. The mistake is almost always in the totalling. What counts as a good CTR depends on the network, the ad's position and whether the search was for your own brand name, so judge it against your own history before anyone else's (see what is a good CTR).

The formula

CTR = clicks ÷ impressions

In Google Sheets, with impressions in column B and clicks in column C:

=IF(B2=0, "", C2/B2)

Format the column as a percentage. The IF stops an ad group with no impressions returning a #DIV/0! error.

A worked example

The total row divides total clicks by total impressions:

=IF(SUM(B2:B4)=0, "", SUM(C2:C4)/SUM(B2:B4))

The brand ad group is shown to people who already typed your name, which is why its rate is so far above the others. It is also 2% of the impressions.

The common mistake

Averaging CTR across ad groups.

=AVERAGE(D2:D4) returns 6.00%. The real figure is 1.67%. The brand ad group has 2,000 impressions and the prospecting one has 60,000, but an average treats them as equals, so a small group with a high rate pulls the account figure up by a factor of three and a half. Divide total clicks by total impressions instead. The same rule applies to every ratio, ROAS and CPA included: total the inputs first, then divide once.

The second version of this mistake is comparing CTR across networks. A search ad meets someone who just typed a query; a display ad interrupts someone reading something else. The two rates will never sit in the same range. Keep them on separate rows and compare each with its own past.

What counts as good

Compare a CTR with the same ad group's rate last period. A rising CTR on the same keywords, in the same position, means the ad copy got better or the targeting got tighter. A rising CTR because the budget moved towards brand terms means only that the mix changed.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Clicks and Impressions from Google Ads up to date in their own tab, for example one called Google Ads, with one row per ad group or campaign.

Fresh Sheets replaces that tab's contents on every refresh, so keep your formulas on a separate tab. This version finds each column by its heading, so it still works if you add a field and the columns move:

=SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Clicks", 'Google Ads'!1:1, 0))) / SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Impressions", 'Google Ads'!1:1, 0)))

Work out your CTR with the free calculator