One of the biggest challenges for SEO is proving its worth. We all know it's valuable, but it's important to convey its value in terms that key stakeholders (up to and including CEOs) understand. To do that, I put together a process to calculate an estimate of ROI for implementing changes to keyword targeting.
There are quite a few parts to this recipe, and while the calculation part is pretty easy, gathering the data to throw in the mix is the challenging part. I'll list each section here, including the components of each, and then we can go through how to retrieve each of them.
BONUS: External & Internal Links for each URL. You can include any measure you like here, as long as it's something that can be compared (i.e. a number).
BONUS: External & Internal Links, for each competing URL. Include any measure you've included on the Strength of Your Preferred URLs list.
There has been quite a lot written about keyword research, so I won't go into too much detail here. For the Keyword data list, the important thing is to get whatever keywords you'd like to assess into a spreadsheet, and include all the information listed above. You'll have to select the preferred URLs based on what you think the strongest-competing and most appropriate URL would be for each keyword.
For the Preferred URLs list, you'll want to use the data that's in your keyword data under the preferred URL.
Once you have the list of de-duplicated preferred URLs, you'll need to pull the data from Open Site Explorer for these URLs. I prefer using the Moz API with SEOTools. You'll have to install it to use it for Excel, or if you'd like to take a stab at using it in Google Docs, there are some resources available for that. Unfortunately, with the most recent update to Google Spreadsheets, I've had some difficulty with this method, so I've gone with Excel for now.
Once you've got SEOTools installed, you can make the call "=MOZ_URLMetrics_toFit([enter your cells])". This should give you a list of URL titles, canonical URLs, External & Internal links, as well as a few other metrics and DA/PA.
For the Where the competition sits list, you'll first need to perform a search for each of your keywords. Obviously, you could do this manually, or if you have exportable data from a keyword ranking tool and you've been ranking the keywords you'd like to look at, you could use either of these methods. If you don't have those, you can use the hacky method that I did--basically, use the ImportXML command in Google Spreadsheets to grab the top ranking URLs for each query.
I've put a sample version of this together, which you can access here. A few caveats: you should be able to run MANY searches in a row--I had about 850 for my data, and they ran fine. Google will block your IP address, though, if you run too many, and what I found is that I needed to copy out my results as values into a different spreadsheet once I'd gotten them, because they timed out relatively quickly, but you can just put them into the Excel spreadsheet you're building to make the ROI calculations (you'll need them there anyway!).
From this list, you can pull each URL into a single list, and de-duplicate as explained for the preferred URLs list to generate the Strength of the Competition list, and then run the analysis you did with the preferred URLs to generate the same data for these URLs as you did for the preferred URLs with SEOTools for Excel.
Once you've got these lists, you can use some VLOOKUP magic to pull in the information you need. I used the Where the competition sits list as the foundation of my work.
From there, I pulled in the corresponding preferred URL and its Page Authority, as well as the PAs and DAs for each URL currently ranking 1-10. I then was able to calculate an average PA & DA for each query, and could compare the page I want to rank to this. I estimated the chances that the page I wanted to rank (given that I'd already determined these were relevant pages) could rank with better keyword targeting.
Here's where things get interesting. You can be rather conservative, and only sum search volumes of keywords you're fairly confident your site can rank, which is my preferred method. That's because I use this method primarily to determine if I'm on the right track--whether making these recommendations are really worth the time to get implemented. So I'm going to move forward assuming I'm counting only the search volumes of terms I think I'm quite competitive for, AND that I'm not yet ranking for on page 1.
I've set up my sample data in this spreadsheet that you can refer to or use to make your own calculations.
This is because, for higher-up folks, money talks. Obviously, this won't be something you can promise, but it gives them a metric that they understand to really wrap their head around the value that you're potentially brining to the table if the changes you're recommending can be made.
There are some great tools for estimating this kind of stuff on a smaller scale, but for a massive body of keyword data, hopefully you will find this process useful as well. Let me know what you think, and I'd love to see what parts anyone else can streamline or make even more efficient.
I can appreciate the reasons why businesses need this type of information. For the types of products that I am familiar with I am going to need one more person doing pick and pack, 1500 square feet of floor space and a $200,000 inventory investment for a $1,000,000/year increase in sales.
So, before we go launching a big retail initiative, I need to know if we are ready to to bring on staff, acquire more space and buy more inventory.
I like this approach of estimating the impact of a new initiative. Anybody who is attacking a new project needs to have some starting point of staff, space, inventory and investments. It is good to have better than a hunch if you project is going to be worthwhile.
My concerns with the calculations are about where I am going to get keyword volume, clickthrough rate and conversion rate data that are specific to my project. This type of data can vary wildly - even when you have direct experience in the same industry. Then I am going to be worried about the SEO team being able to deliver the rankings that the keyword assumptions are based upon. All of these can change dramatically after the project is launched as Google starts to change the design of their site to keep as much of the CTR happening on their domain as is possible and new competitors enter the same space.
I have launched a few retail projects and each time I was surprised at the number of sales that have been produced (both on the high and low side). So, my recommendation to anyone who is launching a new project is to: go in lean.... but be in a position where you can quickly get more space, have access to money, and have suppliers who can help you if the demand exceeds your wildest projections.
Fortunately, with SEO, your results usually build over time and that gives you time to ramp up with hard data. But, it is really risky when you are going into a seasonal or holiday product where you only get one shot at it in a calendar year and have to order months in advance.
So, my bottom line question with this type of projection is where can I get good keyword, conversion and SERP clickthrough rate data that will minimize risk?
I definitely do NOT recommend presenting this information as if it is guaranteed in any way. In fact, as I mention, I don't typically share this kind of calculation with a client, but rather use it as a way to determine whether making and pushing for the recommendation's I've made will actually have the kind of impact for the client that is worth doing. Depending on the client, it may make sense to share, or at least provide some insight ("I think we might be able to see a 10 to maybe even 15% increase in annual revenue if we make these changes"), and this does allow me to say something like that with a straight face.
Shannon, this is a great post -- definitely worth saving for the future. Great idea for a spreadsheet.
I'd be curious to hear any client stories from you or anyone else because one part of this "equation" would greatly worry me if I were to use it. (Or perhaps my concerns are way overblown!) Specifically, I'd be very hesitant in using any estimated CTR for a given SERP at all.
All of the studies out there give conflicting SERP CTRs. Even a difference of one percent can affect projected ROI very, very much when using the process that you describe. Secondly, it seems that we're rapidly approaching a point where it's impossible -- or at least useless -- to state that a company ranks X for given keyword Y for such a precise purpose as ROI calculation.
It's impossible to take into account personalized search (logged into a Google account) and myriad other factors. There are countless other ways that companies and sites can appear in search results outside of the traditional ten-spot. Some queries show fewer than ten results -- what's the CTR on those? What about carousels that appear at the top in Google local search? Desktop versus mobile devices? I could go on, but I'm sure everyone knows what Google's been up to. The only thing that we can state with any degree of certainty anymore (and this is still questionable) is the rank of a site for a term in non-personalized search for a given location on a desktop/mobile device. Just in my opinion, we're reaching a point where a search for X by 100,000 people may show a different result for every single person.
The reason: Say that I state that a ranking of "second" for keyword X would deliver $1 million in revenue. Then, we reach "second" (however that's defined, I guess), but the revenue is only $500,000. I would look really, really bad. And the lack of precision would come from the fact that we increasingly don't know exactly how our sites (or clients' sites) are appearing in search results to most people.
Of course, maybe I'm just overly paranoid about using such a process -- I'd love to hear any stories from the field from anyone who's done such estimations. Hopefully, I'm wrong and we can all benefit from such an innovative spreadsheet!
I don't think you're paranoid Sam, and you didn't even mention the Knowledge graph that adds more and more data at the expense of the organic results.
Shannon, I believe that when working for clients things are a bit more complicated, and as you said -- for higher-up folks, money talks. As SERPs are changing too often and CTRs are different between niches, I personally can't see how I would trust such assumptions if I would be that client.
I still believe that SEOs should focus on general growth trends and not on what will each individual keyword bring.
But on the other hand, I guess that some clients would simply ask for it, and then your spreadsheet is exactly what they would want to see.
And here's a question, if you're working with a client that takes his conversion rate for granted, how do you measure the additional value that the client gets from his future customers? I'm talking about the customers that get initially exposed to the brand through the SERPs (thanks to your work) but only buy later, perhaps directly. Isn't this something that you would want your client to be aware of?
You're absolutely right that SERPs are shifting, and there's not a 100% reliable way to guarantee results. I completely agree with you that SEOs should focus on general growth trends, which is part of why I developed this--it isn't meant to look at one specific keyword, but more a macro look at a body of keywords, but still allowing you do provide real, tangible numeric (revenue & growth) estimates.
It is critical as a consultant to provide the disclaimer to clients that none of this is guaranteed, it's simply a measure to indicate what kind of potential there may be. You're right that there is absolutely additional value in terms of brand building to showing up in SERPs, and it's important to stress the more general value in SEO. However, as you point out, money talks. Particularly for SEOs building internal cases, I do think it's important to provide monetary terms. Typically, that isn't expected or understood to be a guarantee, but rather a way to weigh and prioritize the work needed to implement recommended changes.
I agree in some aspects. You aren't going to hit the mark all the time and optimistic figures will lead to a heavy amount of risk. On the other hand, I've used exactly the same methodology to spur clients into taking action and seeing the broader picture. Especially when I'm pursuing greater spend and attention towards improving CTRs and Conversion in general.
You can mitigate the risk by using max / min figures and giving an approximate range. Plus, the majority of business leaders aren't dumb. They know it's speculative. Furthermore, if you delivered $500K in additive revenue to a company with a $20K investment you would look like a marketing genius, even if you hit half your predictive goal. Anyone that thinks you are bad / dumb is well ... special and likely a poor business leader.
Exactly :-). Thanks for reading, Alan!
Thanks for reading! I definitely understand where you're coming from, and this estimate is really not made to be used as any kind of hard evidence. The idea is to know what ranges you can expect, given the keywords you're aiming to target--that's why I've made the assumptions in the spreadsheet 100% transparent & also editable! When I'm looking at my estimates, I change the CTR & conversion rates to see what kind of range of impact I could expect. I average out the CTR for the first page at about 13% so that it doesn't need to be so specific as to say "this keyword will rank 1st," with the idea that this is an overall snapshot for either an entire site or a section of a site that's focused on particular keywords. The idea of this process is that while you can do very in-depth analysis for 1-10 particular keywords, it's tough to do it for more (800+), but this gives a way to estimate impact when you're dealing with a lot of keywords.
Thanks for reading! Yes, absolutely margin is the point. I see this method as a way to provide key stakeholders a "value" on the work that they're investing in so that they can prioritize. I always am aware that I do not know all the current happenings in a business I'm working with, so I'm really just trying to provide my contacts with the support and information they need to make smart business decisions. That may or may not mean implementing my recommendations, I just don't want them making that decision without understanding what kind of value they might be giving up!
Good tool to prioritizing work. Thanks!
Great post I'll be adding a few of your ideas to the data I already collect and report on.
Good Post Shannon. VLOOKUP is something I really like! Great formula!
I love excel so bloody much.
For those looking to acquaint themselves better with Excel, there's a great website called ExcelExposure, which has video lessons and guides on how to use all of its tools. Go through the vids one by one and be amazed!
This section of how to use Excel for SEO is all about VLOOKUP. It basically just allows you to match data--so (for example), if you have keywords on one list with preferred URLs and you'd like to pull in the search volumes from a CSV that you have from AdWords, you can copy in the table on a separate tab, and then use VLOOKUP to "link" the keywords from your main table with the preferred URLs to the table with search volumes, and pull in each volume so that it's all in one place.
APIs are a different story--I'm still working on that one myself :-).
<3 VLOOKUP. Also...Access, which is like VLOOKUP on steroids. Thanks for reading!
I am also in the process of constructing a way to calculate SEO ROI, and it is near to impossible, if it has to be accurate and relatively promising.
Thank you for some good tips, especially with the Excel tools.
nice spreadsheet, even if a little complicate :) thanks for sharing thou!
It's short but very clear. It is always helpful to see and understand the impact that each change brings. At keywords is crucial!Of course, it will be assumed, but it allows us to act accordingly.
Was initially intimidated by the list of contents within the overview but was pleased to find a succinct and compellingly simple model to think about. Detractors can obviously pile in but top-level analysis such as this certainly have their usage in assessing a website's performance. Thanks Shannon!
This is just one aspect of it. For many business's you can compound that ROI since you will be able to key it into developing a full long-term ROI as the Customer Lifetime Value.
http://upload.wikimedia.org/math/3/b/7/3b7300e1845... -- follow this for the formula. Don't be scared by the math, it's actually fairly easy to do in Excel.
It makes it a lot nicer to key into budgets and have other conversations about customer service and overall client / customer satisfaction. Plus, as a marketer it puts an emphasis on nurture marketing and segmenting past customers.
Awesome! I love that idea! Yea, I really like the idea of considering acquisition as the first step of a lifetime customer, and understanding how that's related to other aspects of the business (customer satisfaction, etc). I think it's always important that whatever you're bringing a customer to is a good experience to really optimize for value.
It's been a while since I've looked at a formula like that...would you mind explaining what all the variables are? Thanks for sharing :-).
Many times this raw data is slightly different from the truth but thanks a lot for this info for calculating the estimated ROI!
Thanks for reading! I definitely understand where you're coming from--you're right that this is definitely not really scientific (since we just don't have real information about CTRs...), but there are some studies, and I think so long as it is contextualized by the caveat that this is not meant to be rock solid, the goal of my calculations are to provide a range, really, of potential impact you could expect so that the work can be prioritized appropriately.
You're absolutely right that WMTs can be helpful for a few keywords, but the idea behind my method is that it can be used for a whole batch of specific keywords, which is quite tricky in WMTs. I'm also not sure how you'd be able to estimate CTRs when a keyword hasn't been at the position you're hoping to rank for, but looking at your own site's average CTR from different average rankings could actually be a fantastic way to determine what to estimate as the CTR for this formula!
first of all hats off for you, article is very well written. Practically your article is very helpful to create strategies. Many time we get confused with one website while creating strategy for that but i hope this article will help lots of people.
Impressive approach to track ROI estimates for keywords - Defined metrics play important role in order to get results within a fixed time period. Good work Shannon!
Great post in not only gathering data but also using the Moz tools to the fullest for conducting research. For me the more sources the better when finding keywords that work for a client and another area to check is to find out what search engine most of their traffic is coming from to then dive down even deeper to see if certain words are stronger on different search engines.
I have to do keyword analysis reports for our clients once a month at times and adjust depending on how the current trends are evolving, and this post will most definitely help out with that thank you for the time to put this together.
Was a Table of Contents really necessary? I'd appreciate that on some of the longer posts on this site, but this one...?