An SEO Opportunity Calculator to Find Low-Hanging Fruit on Big Sites

One of our SEO clients is an enterprise-level brand and manufacturer of consumer product goods in the food industry. Since they don't sell their products directly to consumers, their website strategy is to increase brand awareness and consideration through the publication of recipes, articles, and other cooking and baking resources.

They've been using their site in this way for years and have created thousands of unique recipes and articles. They have a strong backlink profile and domain authority. Over the past year, our agency focused primarily on repairing a lengthy list of technical SEO issues that were a result of a site relaunch by their previous agency where SEO was not a focus.

When the site recovered from this site relaunch and no major technical issues remained, we wanted to focus on identifying and optimizing individual pieces of existing content that have the greatest potential to bring in additional organic impressions and clicks.

As I mentioned, the site has thousands of pages, including almost 5,000 recipes and 2,000 articles. Our challenge here was to identify which pieces of content would bring greatest returns in terms of organic sessions if we performed manual improvements to content. We already know that improving organic visibility even for one recipe (moving it up on page one, or moving from page two to one) can make a huge impact toward reaching our organic search KPIs.

In December 2017, for example, 21.4% of all organic sessions entered the site through only 5 unique recipes. If we could duplicate this success for even one additional recipe, it would produce exponential progress toward our goals.

The process we developed for helping identify these high opportunity keyword-page pairs uses an excel tool we call the "opportunity calculator." This process can be replicated across any high authority, content-heavy site where a large number of indexed pages target queries with high enough search volume to make manual optimizations to unique pages worthwhile.

How We Built Our SEO Opportunity Calculator

1. First, we used the search analytics for google sheets add-on to export page and query data from the past 90 days.

2. Next, we added a new column to round the ranking position to the nearest whole number using the "=ROUND()" formula.

3. By filtering the sheet for each "position rounded" value, you then find the average CTR (click-through rate) for each ranking position and store data in separate tab. We only did this up to position 15 as we want to focus on pieces of content that already rank well but could greatly benefit our visibility by improving just few ranking positions.

4. Next, we pulled average monthly search volume for all keywords from Google Keyword Planner (segmenting search volume statistics by month) and added this data to a new tab in the same Google Sheets workbook.

5. Use the VLOOKUP formula to pull keyword planner monthly search volume data for each keyword into your original tab with the search analytics data. (GKP will likely not return data for all the keywords you originally entered—don’t let it stress you out, it likely dumped those keywords with missing data into a semantically similar keyword that was also included in your list).

6. Filter and delete any keywords that didn’t return data from your GKP data tab.

7. Add an "index" column for each month and calculate the monthly search volume index for each keyword by dividing monthly search volume by the average monthly search volume. A resulting value of greater than 1 means that historically that keyword sees a higher than average number of searches during that particular month. I added some conditional formatting to these columns for fun ☺. We decided to use monthly indices for our keywords because recipe searches are highly seasonal and we wanted the option to prioritize optimizations based on which keywords would be most popular in upcoming quarters. This step could be skipped if your content doesn't see seasonal trends in interest.

8. Add a “predicted clicks” and “opportunity clicks” column for each month in your original spreadsheet. Predicted clicks is a proxy for/estimate of the traffic you will receive for each keyword-page pair based on historical search volume, current average ranking position, and historical CTR for that ranking position. Opportunity clicks is a proxy for/estimate of the opportunity available or number of clicks you are potentially missing out on if you don’t improve your ranking position for each keyword-page pair.

9. Calculate predicted clicks for each keyword and month by multiplying historical monthly search volume by the average CTR for that keyword’s ranking position. Use a VLOOKUP formula to pull in the average CTR by position (example formula: =Historical Monthly Search Volume(Vlookup(Rounded Position,'Estimated CTR by Position'!$1:3436,2,FALSE))).

10. Calculate opportunity clicks for each keyword and month by subtracting predicted clicks from monthly search volume.

11. At this point, you can either continue working in Google Sheets or export the workbook to Excel (which I did because Sheets kept crashing every time I tried to sort a column -_-). Use your new metrics to discover pages that are currently ranking for high volume keywords, but have "high opportunity" to drive additional visits from organic search if optimized.

So if I wanted to use my newly created opportunity calculator to choose a handful of recipes to optimize ahead of January, I would first filter the January Index column for keywords with an index of 1 or above and an average ranking position that rounds to 15 or less. I would then sort the opportunity clicks column for January so the keyword-recipe pairs with the highest opportunity would rise to the top.

Obviously, next steps will depend on the industry, competition, content type, and existing SERP features for the content you are optimizing. For recipes, we could start with optimizing page titles and expanding page content to include the high opportunity keywords and increase the value of the content for the user, test new meta descriptions and images in recipe schema markup which could potentially improve CTRs, promote the recipes across other marketing channels like email and social to increase the number of recipe reviews and backlinks to these specific pages, and linking to these recipes from our highest authority pages in a way that makes contextual sense.

There you have it! If you have a process for identifying low-hanging SEO fruit on content-heavy sites or have questions about our opportunity calculator, we would love to hear from you in the comments!