Jonathan Cartu Reports: How to Calculate ROI for SEO When Targeting a Set of Keywor... - Jonathan Cartu - Advertisement & Marketing Agency.
post-template-default,single,single-post,postid-18532,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.2,qode-theme-bridge,wpb-js-composer js-comp-ver-5.2.1,vc_responsive

Jonathan Cartu Reports: How to Calculate ROI for SEO When Targeting a Set of Keywor…

How to Calculate ROI for SEO When Targeting a Set of Keywor...

Jonathan Cartu Reports: How to Calculate ROI for SEO When Targeting a Set of Keywor…

Calculating ROI for SEO is one of the biggest struggles most SEO professionals face.

SEO pros have the benefit of keyword volume data that provides insight into what people are interested in (data that other channels may not have).

But being able to understand what the lift would be if you make changes for SEO can be a bit of a conundrum.

In this article, I will walk you through how I have perfected the art of estimating lift for SEO.

Note: I will be using my own WordPress site that I developed to help visitors and residents of the island I live on. It’s a site I use to test out SEO ideas on, and a good use for examples in this article.

How to Calculate ROI for SEO From Keywords You’re Targeting

The foundation for most SEO work begins with a keyword analysis.

Google, and most SEO tools, provide data around how often users are searching for specific terms.

This data is used most often for paid search to estimate the performance of ads, but SEO professionals will tend to use this data to help them identify where the opportunities lie.

1. Complete a Keyword Analysis

Gather your keyword estimates from your keyword analysis.

You can use Google’s Keyword Planner if you have a Google Ads account, or there are many free or paid tools that use the same data you find in Keyword Planner.

Basically, all you need are the words you want to target with their estimated search volume data.

Your data should look something like this:

2. Download All of Your Keyword Data

When you know what terms you are going to target for a project, the next step is to calculate what the difference would be from your current performance against what your estimated lift is.


Continue Reading Below

Let’s say I want to do a project to target terms that contain “Vashon” (which the site already ranks for) or “things to do” type of terms for the island, and maybe try for additional searches for islands and places to visit around Seattle (since this island is just a ferry ride away from West Seattle).

I want to compare this list of terms and estimated volume with what we are currently ranking for.

Ideally, I want to see current impressions, clicks, click-through rate (CTR), and average position as reported in Google Search Console.

The trick is getting 12 months of data for all keywords to get to that ROI for SEO.

Unfortunately, Google limits the data for keywords and pages to under 1,000.

How to Calculate ROI for SEO When Targeting a Set of Keywords

A workaround for this is to use a tool that can access all of your keywords and allow you to download this data.


Continue Reading Below

SEMrush, BrightEdge, Conductor, and other tools that require payment will provide you with that data.

However, if you have a limited budget, or want to just download your keywords, you can use a free trial of Keylime Toolbox.

I highly recommend using Keylime Toolbox to store your keyword data, and some of their premium features come in pretty handy.

For this exercise, I’ll walk you through how to set up a free account and collect your data.

Start by selecting the “Free Trial” link in the top navigation

How to Calculate ROI for SEO When Targeting a Set of Keywords

Set up contact information and then add your properties.


Continue Reading Below

If you “Sign in with Google” using the same account that you manage your Google Search Console under, then the system will automatically recognize your GSC properties.

Keylime Toolbox Registration

You’ll need to add [email protected] as a new user to your GSC (make sure you set it as “Restricted”).

GSC Add User

Then select which Google Analytics property your website is under and allow Keylime Toolbox to authorize the user.


Continue Reading Below

Keylime takes a day or two to populate your account with all of the data, so be prepared to wait a bit for the next step.

3. Calculate Current CTR per Average Position

Once you have all of your keyword data populated in Keylime Toolbox, you’ll want to download 12 months’ worth of all of your keywords and data into a CSV.

I like to copy and paste the data into Google Sheets so that I can share it with my team easily, but you can use Microsoft Excel as well.

The data that is most important for the purpose of this exercise is the click-through rate (CTR) and the Position (average ranking position).

Download of all Keyword data from Keylime Toolbox

Next, we are going to set up a sheet that holds the average CTR based on the average position using the “AVERAGEIFS” formula to lookup the position and average out the CTR across all of the keywords.


Continue Reading Below

It should look something like this:

Keyword data CTR based on average position formula

When targeting a specific set of terms (for example branded terms) I like to break out the average CTR by position for those terms.

By using the “AVERAGEIFS” formula, we can focus on the average CTR for the specific position for any term including “vashon”, or “things to do.”

CTR data based on avg position and terms

This is particularly helpful because branded CTRs tend to be higher than non-branded searches for most organizations.


Continue Reading Below

4. Identify Keyword Gaps & Opportunities

Now that we have our identified keyword opportunities from our Keyword Analysis, we need to see:

  • Which keywords we currently rank for.
  • Where we rank.
  • How many impressions we’re seeing.
  • What the CTR is.
  • And ultimately how many clicks.

Using the same Google Sheet set up with the current Keywords data (export from Keylime Toolbox), CTR data that we calculated (in separate tabs).

How to Calculate ROI for SEO When Targeting a Set of Keywords

We’re going to use the tab that has our Keyword Analysis (keywords and volume) in a new tab (I called it “Keywords to Target” in my example here).


Continue Reading Below

In the columns next to “Volume,” I added the current clicks, impressions, CTR, and average position.

Using the SUMIF and AVERAGEIF formulas I set up a lookup for each term to get a view into how each term is currently performing.

Keyword gap analysis

Note that this should all be average per month numbers from 12 months of data.

So your totals for impressions when in the first few positions should round up/down to match the “volume” numbers. Keyword impressions match volume for 12 months of data

5. Summarize Your Keywords

When my keyword analysis brings back a long list of keywords to target, I…

Uninstall AiroAV

No Comments

Post A Comment