SEO and UX strategist at From The Future. I believe organic success is about building the right User Experience, both in design and keyword architecting.
The average CTR for position 1 in Google is 28%. For position 2, it’s 14% (source).
Scaled across thousands of pages on an enterprise site, that small change = millions of visits.
The difference between position 1 and 2 (or even 6 and 3) can be attributed to user engagement metrics like CTR. The fastest way to manipulate CTR is through changes in page titles and meta descriptions.
Changing your titles and metas are easy – it’s SEO 101. However, tracking these changes (across 1000s of pages) is a challenge.
Our consultants developed a solution – a Google Sheets file that tracks the impact of page titles and meta description changes.
The file is built to evaluate:
Enter your email and get FREE access to the Google Sheets automation file!
This report is best used for to measure mass amounts of changes in bulk on large, enterprise websites. In particular, websites with a large number of pages that you can group together.
It operates by pulling in data from Google Search Console to measure the impact of rankings / SERP CTR over periods of time.
In order to get the most out of this report, you’ll need to use proper testing techniques by setting a hypothesis, control and test.
Let’s look at some potential examples…
Let’s imagine we’re working with a health / supplement brand like GNC.
Shop for high density Whey Protein Powder by GNC. Vegan, multiple flavors and delicious. Enjoy FREE Shipping today only.
Shop for high density Whey Protein Powder by GNC. Vegan, multiple flavors and delicious.
Let’s imagine we’re working with a national gym like 24 hour fitness.
24 hour gym in Miami, 33127 – 1st Month Free | 24 Hour Fitness
24 hour gym in Miami – 1st Month Free | 24 Hour Fitness
Again, this works best on larger websites where you can dynamically make mass changes to templates (page titles and metas) to measure the impact on a large scale. While you can use this on any site, the data will likely be inconsistent due to the lack of volume.
We hacked together a Google Sheets file that does all the legwork for you. The instructions below will run you through tab by tab how to build the report.
We’ll go over the detailed step by step process you should follow to build each of these sheets.
We recommend to group the pages you want to test by page-type (product pages, location pages, etc) before start running the report. This way you’ll obtain more consistent results.
For the purpose of this demonstration, we’ll be testing the location pages of a national franchise.
Pulling current titles and metadata
By doing this, the On-page strategy tab will be populated with the corresponded target urls, title tags and meta descriptions.
Your On-page Variations tab should look like this:
Now we’re ready to define the “Nomenclature Strategies”, aka the variances we’ll use to test new titles and metas.
We came up with 3 different Nomenclatures to test page titles.
Each of these would be tacked on to the beginning of the page’s existing title.
20+ Cruz Bay Boat Rentals & Yacht Charters | BRAND NAME
Cruz Bay Boat Rentals & Yacht Charters | BRAND NAME
Fill out column B, D and F with your nomenclature strategies, new titles and new meta description correspondingly.
After you get all your metadata written, it’s time to get your control groups setup. We created 2 categories to easily manage this distinction:
Evaluate how many pages you actually have per “nomenclature strategy” and divide each group in two. Assign “Control” to 50% of the pages and a “Test” to the rest 50%.
In other words, you’ll be only changing titles and meta descriptions on half of your pages. The rest of the pages will remain as is.
Your sheet should be looking like this:
Since clients’ in-house SEO teams or devs are usually the ones updating pages, it’s always best practice to verify that all your recommendations were properly implemented.
To make this process easier, we created a sheet called “Implementation Review” which, based on custom build formulas, will do this verification for you.
This new data will populate the Implementation review tab automatically. The formulas on the Title implemented on site? columns will “verify” if changes were implemented by comparing current vs. recommended titles and finding a match.
These columns will display:
Note: you’ll need to expand down the formula on these columns in order to activate all cells with the formula.
Once you verify that all the metadata recommendations were successfully implemented, you can start running the actual report by analyzing the performance breakdown of the pages that were changed.
We recommend to wait until you have a full 3 months since making the changes before running this report.
Important Note: if you skip the “Implementation Review” step, then you need to:
The identification of your date periods for analysis will depend on your business sales activity; is it seasonal, cyclical, or neutral?
In this specific case we were working with a seasonal client, which is why we decide that our reporting will be done year over year (Y/Y).
Ex. Report Y/Y:
If the changes were implemented on July 1st, set your results window (period after the changes were made) to be from July 15th (we usually give a 10-15 days window for Google to digest and index the new page changes) to Sep 15th 2019.
Then, your comparable window (time before changes were made) should be the same dates but from the past year: July 15th – Sep 15th 2018.
Ex. Reporting Period over Period OR Before/After:
If you were working with a neutral client (sales do not depend on seasons or specific sale cycle) you could easily just compare the performance of your pages on a before and after basis depending on when the changes were made.
For this part of the process we’ll be working with a reporting automation plugin for Google Sheets called Supermetrics. It will really make your life easier if you get access to it, however, in case you don’t, you can still download the data manually from Google Search Console and follow the same format below.
Setting up _GSC Data sheets
_GSC data #1: this sheet will have the data from your comparable window.
_GSC data #2: this sheet will have the data from your results window.
Make sure all Supermetrics settings are set as follow:
After a few minutes (depending on the amount of data), your sheet should look like this:
Note: formulas are placed at the first cell of each column (H3 and K3). Make sure you DO NOT delete these formulas. Without them you’ll only have empty columns.
Once your Results Data sheet is complete, you can start playing with your data to better visualize your strategy performance. We’ve built two Pivot Tables & Charts default setups that look at the performance of each of your Nomenclature Strategies:
However, if you are familiar with these data tools, feel free to customize them to your taste.
You’ll find an aggregate analysis of CTR and Position % change average from both Control and Test pages together per strategy.
This tab shows a comparison of the CTR and Position % change average of the performance of each strategy between the Control pages (pages as is) and the Test pages (updated pages). The following pivot table will feed that chart analysis:
Test Pages Results Chart
Control Pages Results Chart
A large majority of the page’s rankings decreased as a result of our tests – this is not a bad thing.
When done in a controlled environment it’s easy to revert the changes. More importantly, it reinforces the importance of testing.
If you want to grow, you have to be willing to take risks and make changes.
Our data driven approach to keyword research.