Here I am going got talk through some of the Excel plugins I have found very useful for SEO tasks. Best thing I can advise is to get them installed and give them a go for yourself. The three plugins to look through today are;
- Regular expressions plugin for Excel
- SEO Tools plugin for Excel
- Excellent Analytics plugin for Excel
Regular Expressions Plugin for Excel
Link: Regex Plugin Excel
This can be a great plugin when working with large data sets within Excel as it allows you to use all of your normal regular expressions to find the data you need. See this site for a regular expressions cheat sheetfor reference, alternatively if you aren’t too familiar with regular expressions then I suggest reading the following tutorial.
There is not much more to say about the regular expressions plugin for Excel apart from that you can filter out a lot more than you can using the normal string matches which Excel has be default. It can be a lot easier to get the information you require.
SEO Tools Plugin for Excel
I briefly mentioned this in an earlier post about why programming is an essential SEO skill but I wanted to expand on the amazing excel plugin which is SEO Tools by Neils Bosma and why you need to be using this if you aren’t already.
Below is a screenshot of the additional tab you get in Excel once you have installed the plugin (60 second install, follow the instructions in the ‘read me’ file!)
So what are some of the cool things you can do with SEO Tools once it is installed? Well an awful lot actually.
Check the HTTP Status of pages
Simply add a lot of different URLs in column A then use the function=HttpStatus(A2) to find out if it is returning a 200, 404, 503 etc. This can be a really useful tool for both SEO since Google Webmaster Tools reports on errors for your site for a long time, often when these pages are actually no longer showing any errors. So it can be a good method to list all of the URLs which Google Webmaster Tools is showing as errors and double checking these to spot the real pages that are showing as errors.
To find this function you can simple click on the ‘onpage’ button highlighted and select the HttpStatus() option from the list
Filter through back link targets
Lets say you have a list of websites / webpages you want to target for back links but it isn’t easy to figure out which ones to target first. From a large list, it is important to be able to filter through these to identify the best websites to contact first. There is no point in contacting poor quality websites first!
Maybe you want to prioritise websites to contact who have “SEO” in their meta title and have a page rank of at least 1
Identify popular content on competitor websites
How about trying to identify which content is the best on competitor websites? Maybe a competitor has a blog or a content section which you can easily scrape all of their URLs from. Simply go to Google, view 100 results and search for “site:blog.website.com” then use Scrape Similar to get all of the URLs where you can paste these into Excel.
Once you have all of these URLs then you can check how popular they have been on the social media channels. In the screenshot below I have pulled a few of my blog posts and identified how many tweets they have had recently using the =TwitterCount(A2) function from SEO Tools.
Whilst I am not a massive fan of copying off competitors (I believe you should be doing your own thing far better so they are copying off you!) this can be a useful tool to get a rough idea of different content that people are interested in. It could even be a good guide to identifying your own ‘linkable content’ since Google Analytics can only show the amount of traffic that each of the pages gets.
So looking at the above screenshot, if those websites were for competitors then I would think about doing a post about how to see competitors social media link statistics since this has gained the most traction.
Website Scraping with XPathOnURL
This is one of the most powerful bits of SEO Tools and one of the best bits with this Excel plugin. I am not going to go into too much detail here about XPathOnURL since this is a full blog post on its own. To give you an idea of what it can do though it basically scrapes certain bits of the HTML that you need such as “get me all the links on this page” which would translate to “get me all the HREF attributes within all of the <a> tags on the page”.
This tool can be extremely useful when scraping websites for certain pieces of content / information etc. Have a play, see what you can do.
Link: Excellent Analytics
This can be a really useful Excel plugin for SEO since it allows you to export all the data you need from Google Analytics straight into Excel. So no more exporting large data sets then matching up the relevant data with a =VLOOKUP() function instead, just get the precise data you need from GA straight into the adjacent column in Excel where your other data is.
I am not going to cover this in a large amount since it is quite self explanatory, it pulls in data from Google Analytics based on all the normal segments, filters and visitor data you are used to using via the normal Google Analytics dashboard. Simply link up your GA account with the plugin and away you go. Below is a screenshot showing one of the user interfaces.
On larger websites which 10’s of millions of page views per month you may have noticed how slow Google Analytics is to do anything, so this can help speed this process up for extracting the data you need.
These are the three essential Excel plugins SEO I use on a regular basis. I have found them to be real time savers for various tasks as outlined above so go and give them a go.
If you have any other excel plugins that you use for SEO then please leave a comment.