Improve Your SEO CTR with An Audit Using Your Own Data
If you manage a website or a blog with a lot of pages, one of the ways you can potentially gain organic traffic without improving your organic rank is from auditing your site’s Meta Titles to identify different words and characters which tend to produce a higher Click Through Rate (CTR) from the SERPs (Search Engine Results Pages).
While you could take on an intern to audit your meta titles one by one, the simplest (and fastest) way to do this is by exporting Search Console Data, adding it to Excel or Google Sheets (I prefer this method because you can directly import all of the Meta Title text via xPath - I’ll walk you through the process in a moment), and then enter different text and symbols into a column of cells and let custom formulas do the hard work for you.
While you could take on an intern to audit your meta titles one by one, the simplest (and fastest) way to do this is by exporting Search Console Data, adding it to Excel or Google Sheets (I prefer this method because you can directly import all of the Meta Title text via xPath - I’ll walk you through the process in a moment), and then enter different text and symbols into a column of cells and let custom formulas do the hard work for you.
Here are the Steps for Your Meta Title Optimization Audit
- Export Search Analytics Data from Search Console.
- Add that data to Google Sheets.
- Use XPath to pull the current meta title from each of the pages included in your Search Console Data.
- Create a list of “If Title Contains” text and characters that you want to audit for performance
- Use formulas to look up the % of Meta Titles which have a high (or low) SEO CTR and contain these components.
Export Search Analytics Click, Impression & SEO CTR Data
You can export this in Search Console under Search Traffic > Search Analytics or if you’ve already linked your Google Analytics and Search Console Accounts, you can get the data from Google Analytics (nice, because you can include transaction data as well).
Once you have that data, paste it into column D of a sheet labeled “Data” in a Google Sheets Doc:
Once you have that data, paste it into column D of a sheet labeled “Data” in a Google Sheets Doc:
The next step is to be able to automatically pull the Meta Title for each of the URIs so that we can associate the meta title with the CTR of each page. There are two steps to do this in Google Sheets.
First we’ll use Concatenate to turn the Landing Page URI into the full URL of the page. I'd suggest adding this formula to column C (just to the left of the imported URI). The formula to do this is:
First we’ll use Concatenate to turn the Landing Page URI into the full URL of the page. I'd suggest adding this formula to column C (just to the left of the imported URI). The formula to do this is:
Which will produce the full URL of that landing page in cell C2. It’s important to note that if your site uses https vs. http that you should update the formula accordingly. Once you add that formula to C2 you can autofill the rest of column C so that the full URL is generated for every landing page URI.
Next you’ll want to use ImportXML to get each page’s Meta Title via Xpath. If you want to get the Meta Title of the landing page in cell C2 you’d enter the following formula in A2:
Next you’ll want to use ImportXML to get each page’s Meta Title via Xpath. If you want to get the Meta Title of the landing page in cell C2 you’d enter the following formula in A2:
This will import the Meta Title of that landing page in cell A2.
You can then autofill that formula into the other cells in column A. Note that if you have a lot of URLs it may take some time to import all of those Meta Title values.
In column B I usually just grab the character count of the title I’ve imported. Do this by adding the following to B2 and then autofill:
You can then autofill that formula into the other cells in column A. Note that if you have a lot of URLs it may take some time to import all of those Meta Title values.
In column B I usually just grab the character count of the title I’ve imported. Do this by adding the following to B2 and then autofill:
This gives you another data point you can use to evaluate your Meta Titles (more on that in a second).
Now your “Data” sheet is all set. It’s time to add the characters and text you want to analyze and add some custom formulas so you can analyze the impact different text has on the CTR of your landing pages.
Here’s what this looked like in my sheet:
Now your “Data” sheet is all set. It’s time to add the characters and text you want to analyze and add some custom formulas so you can analyze the impact different text has on the CTR of your landing pages.
Here’s what this looked like in my sheet:
Let's Break This Screenshot Down a Bit...
Column A (beginning in A5) contains the characters or text I want to evaluate. You can add as many different words, phrases or symbols as you want … looking at things like “%” symbols or looking for text like “guaranteed” or “best” or “sale” to get a sense for what characters or words are included in titles with a higher CTR more often (or to identify words that don’t perform well and show up more frequently in pages with a lower CTR). You can then apply conditional formatting to highlight what's most important to you to make interpreting these percentages easier.
Column B and C includes a CTR percentage to evaluate. These are fields you can update manually … so if you want to see the percentage of pages with a CTR > 4% that contain the word Registered Trademark symbol you’d update 2% (shown in the screenshot) to 4%. Similarly, Column C looks up the % of pages which have a CTR lower than the percentage you enter. In the screenshot above we’re looking up the % of values in meta titles with CTR >2% and we’re also looking up the % of the same values in the Meta Titles with CTR <1%.
You’ll also note that I’m including the Meta Title length as an average for pages which qualify, so we can see that our Meta Titles in both groups are pretty similar in length.
Column B and C includes a CTR percentage to evaluate. These are fields you can update manually … so if you want to see the percentage of pages with a CTR > 4% that contain the word Registered Trademark symbol you’d update 2% (shown in the screenshot) to 4%. Similarly, Column C looks up the % of pages which have a CTR lower than the percentage you enter. In the screenshot above we’re looking up the % of values in meta titles with CTR >2% and we’re also looking up the % of the same values in the Meta Titles with CTR <1%.
You’ll also note that I’m including the Meta Title length as an average for pages which qualify, so we can see that our Meta Titles in both groups are pretty similar in length.
Here are the Formulas Used in the Example Above
For the B3 formula (Average Meta Title Character Count we’re just using AverageIf to calculate the average if the CTR meets the criteria in B2:
And in C3 we’re doing the same, if the CTR is less than the percentage entered in C2:
For the Percentage formulas we’re using a wildcard lookup on the text or symbols in column A.
The formula needed for this in B5 is:
The formula needed for this in B5 is:
Once you enter this in B5 you can just autofill down column B. A modification of the same formula can be used in C5 to look up the A5 symbol/text in cells where the CTR is less than the percentage specified in C2:
And that’s pretty much all there is to setting up this very basic Meta Title Audit, although I should also add two important notes:
- In order to gain the most meaningful insights you should try to only evaluate titles of a similar organic position. This can be easily accomplished by adding an additional criteria to the formulas above, specifying a range of average positions which you want to examine (you might find that a certain symbol or piece of text has a relatively large impact on CTR at positions 6-10, but virtually no impact on the SEO CTR of organic positions 1-5, which is important to know).
- The results will be most accurate if the Meta Titles haven’t been changed regularly during the measured period. For example if you updated all your Meta Titles 40 days ago, you should narrow the data set to evaluate CTR performance during the past 35 days or so (allowing for time for Google to have refreshed the cached version in the SERPs). If you have a large dataset (say, you have a script to export the search console query data each month), I would just create a filter to only examine pages with a large number of impressions and avg. organic position greater than or equal to 5. I would also exclude any pages which get the bulk of their organic traffic from branded search (those people are looking for your result in the SERP).
Get Started on Your SEO CTR Audit Right Now - Here's a Sample Sheet
Make a Copy For Yourself
To save you time here’s a sample version of the basic audit sheet I’ve mentioned. Please feel free to open it up, make a copy and play around in your copy with your own data and let me know what you think!
I hope this is helpful to you as you evaluate your site’s Meta Titles to see which text or symbols seem to contribute to a higher CTR in your particular niche. Remember … you can use the same principles of the wildcard lookup audit detailed above to evaluate your PPC headlines or email marketing subject lines (what words tend to generate the highest open rate for your campaigns?), so get creative! My advice is to always strive to use your data and learn from what you’ve done in the past so you can narrow your focus to replicate success and eliminate any strategies which haven’t performed as well as others.
I hope this is helpful to you as you evaluate your site’s Meta Titles to see which text or symbols seem to contribute to a higher CTR in your particular niche. Remember … you can use the same principles of the wildcard lookup audit detailed above to evaluate your PPC headlines or email marketing subject lines (what words tend to generate the highest open rate for your campaigns?), so get creative! My advice is to always strive to use your data and learn from what you’ve done in the past so you can narrow your focus to replicate success and eliminate any strategies which haven’t performed as well as others.