Posted by shannonskinner
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.
In this post, I will walk through that process, so hopefully you can do the same for your clients (or as an in-house SEO to get buy-in), too!
- Gather your data
- Analyze the Data by Keyword
- Calculate your potential opportunity
What you need
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.
- Keyword data
- list of keywords
- search volumes for each keyword
- preferred URLs on the site you’re estimating ROI
- current rank
- current ranking URL
- Strength of your preferred URLs
- De-duplicated list of preferred URLs
- Page Authorities for each preferred URL
- 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).
- Where the competition sits
- For each keyword, the sites that are ranking 1-10 in search currently
- Strength of the competition
- De-duplicated list of competing URLs
- Page Authorities, Domain Authorities,
- 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.
- Copy the preferred URL data from your Keyword Data into a new tab.
- Use the Remove Duplicates tool (Data>Data Tools in Excel) to remove any duplicated URLs
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.
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.
- Conversion Rate
- Average order value
- Previous year’s revenue (for the section you’re looking at)
I’ve set up my sample data in this spreadsheet that you can refer to or use to make your own calculations.
Each of the assumptions can be adjusted depending on the actual site data, or using estimates. I’m using very very generic overall CTR estimates, but you can select which you’d like and get as granular as you want! The main point for me is really getting to two numbers that I can stand by as pretty good estimates:
- Annual Impact (Revenue $$)
- Increase in Revenue ($$) from last year
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.
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!
- How to Build an Advanced Keyword Analysis Report in Excel
- Competitor Analysis and Benchmarking With the New Domain and Page Authorities
- From Keywords to Concepts: The Smart SEO’s System for Themed Keyword Research
- 6 Cool Ways to Supplement Your Open Site Explorer Data
- eCommerce Syndicated Content: How to Win
AMD’s stock price is down around 18 percent since the chip maker posted Q2 financial results
Sometimes it seems like AMD can’t catch a break. That’s certainly true of its second quarter performance — the Sunnyvale chip maker announced Q2 revenue of $1.44 billion, representative of a 3 percent sequential bump and a year-over-year jump of 24 percent. Not too shabby, though at the end of the day, AMD’s debt burden dragged the company down by contributing to a net loss of $36 million.
Total debt for the end of the quarter was $2.21 billion, up from $2.14 billion at the end of the previous quarter. Otherwise, AMD did well for itself, most notably in its Graphics and Visual Solutions segment, which saw revenue jump 5 percent sequentially and 141 percent year-over-year driven largely by its semi-custom SoC shipments for game consoles.
“The second quarter capped off a solid first half of the year for AMD with strong revenue growth and improved financial performance,” said Rory Read, AMD president and CEO. “Our transformation strategy is on track and we expect to deliver full year non-GAAP profitability and year-over-year revenue growth. We continue to strengthen our business model and shape AMD into a more agile company offering differentiated solutions for a diverse set of markets.”
Read’s optimism isn’t being shared by investors. Following AMD’s Q2 earnings report, the company’s stock price slid around 18 percent (at the time of this writing).
If you can read this, thank a teacher, But if the text and pictures show up all garbled and washed out, perhaps it’s time to toss that ancient monitor aside and grab a new one. In that case, you can thank us for pointing you to today’s top deal for an Asus VX238H Black 23-inch LCD Monitor for $120 with free shipping (normally $160 – use coupon code: [EMCPCPB44]; additional $20 mail-in-rebate). This LED-backlit panel sports a 1920×1080 resolution with a 1ms response time, 80,000,000:1 dynamic contrast ratio, 250 cd/m2 brightness, and built-in speakers.
G.Skill Ripjaws X Series 8GB (2 x 4GB) 240-Pin DDR3 2133 (PC3 17000) Desktop Memory for $75 with free shipping (normally $83 – use coupon code: [EMCPCPB29])
Intel Core i5-4430 Haswell Quad-Core 3.0GHz LGA 1150 84W Desktop Processor for $180 with free shipping (normally $190 – use coupon code: [EMCPCPB47])
WD Re 1TB 7200 RPM 64MB Cache SATA 6.0Gb/s 3.5-inch Enterprise Internal Hard Drive for $90 with free shipping (normally $105 – use coupon code: [EMCPCPB48])
Corsair RM Series RM750 750W ATX12V 80 Plus Gold Certified Full Modular Active for $90 with free shipping (normally $120 – use coupon code: [EMCPCPB23]; additional $20 Mail-in rebate)
- Newegg Daily Deals: Crucial Ballistix Sport XT 8GB DDR3-1600, Intel Core i5 3470, and More!
- Newegg Daily Deals: Asus DirectCU II Radeon R9 270X Graphics Card, AMD FX-6300, and More!
- Newegg Daily Deals: Windows 7 Home Premium SP1 64-Bit, AMD FX-6300 Vishera, and More!
- Newegg Daily Deals: 16GB Crucial Ballistix Tactical DDR3-1866, Cooler Master HAF Case, and More!
- Lenovo Unveils S Series Smartphones at Mobile World Congress