March 28th, 2013
Tips, Tools and Tutorials
It’s no surprise that Excel would be in the SEO toolbox given that Excel is versatile not only in storing data but also in providing calculations and visualizations of said data.
However, there are a lot of ways that you can use Excel as a tool for SEO. Though you’re likely to find some other great SEO related Excel tricks from Richard Baxter, Annie Cushing, and John Doherty, these are just a few of my personal favorite Excel SEO tools…
Some of the most commonly sought-after metrics (that I’ve noticed) that SEOs strive to track and report on are search engine rankings, traffic, and conversion data. Though there are plenty of other metrics that may be important to online marketers, including social media metrics, these three seem to be at the core of a lot of SEO reporting. The problem: how do you pull the data altogether to see a correlation of ranking, traffic, and conversions side-by-side?
To view your ranking, traffic, and conversion correlation, there are a few ways you could set up your data visualization. Nathan Safran at Conductor wrote a post about an Integrated Metrics View, which I have to admit is pretty cool. It gives you an overview of your traffic, conversions, revenue, and average rank over time. In the examples he provides, you get a pretty good picture of what’s going on with the correlation of your rank, traffic, and conversions (as well as revenue).
The purpose of all data visualizations should be to depict the story of what is going on with all your data in a simple, easy-to-understand graphic that gives you actionable insight at a glance. Months prior to stumbling upon this feature of Conductor’s searchlight report, I had been utilizing my own Excel data visualization that was slightly different but served a similar purpose: to show how rank influenced traffic and conversions over time. Here’s an example of how this worked:
As you can see, the Excel chart shows the traffic and conversions represented in line graphs over time.
I utilized line chart types for the traffic and conversions and a stacked column for ranking data, thereby allowing the ranking data to be broken up into different sections (in this case, it is broken up by the page in which the keywords are found in search engine results). Though average rank is helpful, in some cases, you may need a combination of qualitative and quantitative data which an average rank may not be able to provide. Here, you can clearly see how the number of keywords that were ranking in higher page “brackets” (pages 4 and 5) were slowly decreasing as the number of keywords in the lower page “brackets” (pages 1 and 2) were slowly increasing.
The other downside to referencing an average rank is that ranking should be inversely relative to your traffic and conversions (that is to say, the lower your rank, the higher your traffic/conversions and vice versa), so if you decide to build an average rank into your charts, keep in mind that it will likely require having to make some changes to your chart to accommodate the change in scale (e.g. adding formulas to subtract the rank from 100). There’s no one-size-fits-all approach to reporting so it really just depends on your focus.
I’m a bit biased and prefer my RT&C chart over the Integrated Metrics View chart, but the benefit to using the Integrated Metrics View is that the data is already being pulled into the report as part of the Conductor SaaS. Though the Excel method may seem free, there is technically a “cost” associated with using the Excel chart since there is time and effort to factor in when you’re gathering and organizing your Internet marketing data, so this method should only be used when other alternatives are not an option.
At some point, you’re probably going to be interested in doing some keyword research for your site. Usually this is done at the beginning of your campaign, but it doesn’t hurt to reassess your Internet marketing efforts periodically to keep up with trends in search queries. Though there are a lot of tools out there that can help you with your keyword research process, one of the more commonly used tools is the Google AdWords Keyword Tool.
Though the AdWords Keyword Tool is intended for PPC advertising research (for Google’s paying customers), the data can be used for organic search engine optimization research as well. You are limited to querying 100 keywords at a time, but you can collect a bunch of data and store it in a single workbook. From there, you can create formulas in an adjacent sheet (tab) to pull the data that you collected from the AdWords Keyword Tool to match up with specific keywords you wish to target (or you can keep it in the same tab like the example below). There’s a really simple way you can do this:
With an INDEX/MATCH formula (like the one below), you can devlop lists of just the keywords you want data for and the corresponding competition and search volume as well. This can help you save some time digging around for the matching search volume and competition values for keywords that you’re researching. In the example above, I used Exact Match keywords (which are surrounded by brackets).
Example Formula (this is what is found in cell G2 but you can copy this formula across to C2 and down as needed):
When you can’t do a data dump of your site’s database, you sometimes need to use tools to scrape your site to grab important information about your site’s pages that can influence your search engine optimization. There are two tools that I prefer to use for this purpose: Xenu Link Sleuth and Screaming Frog SEO Spider. They both do very similar things but there are just a few differences between the two:
If you want to scrape a bunch of content from a site, then Screaming Frog would be the better choice, but given that you’re limited to 500 URLs (unless you want to pay for an upgrade), you may find Xenu to be an acceptable alternative (there’s another way you can utilize another tool in conjunction with Xenu to pull some of the data that Screaming Frog provides, but more about that later).
One of the most important reasons I utilize Xenu or Screaming Frog to scrape a site is to identify broken links and/or HTTP Status codes. I will sometimes use it to identify file types as well (see graphic below).
To make a very simple pie chart displaying the distribution of HTTP codes from a Xenu or Screaming Frog export, all you have to do is use a COUNTIF formula for each response code. For example, to count all your URLs with a 200 (OK) HTTP status, you could use a formula like:
[where your HTTP status codes would be listed in Column C from rows 21 to 100]
If you only want to count the number of pages that have 200 OK status that are internal links (URLs from your own domain), then you would use a COUNTIFS formula like:
[where your URLs are listed in Column B from rows 21 to 100 and your HTTP status codes would be listed in Column C from rows 21 to 100]
Once you have your “table” structure, you can easily create a chart that pulls from that data source. You can also utilize COUNTIF and COUNTIFS functions to detect lengthy page titles, meta descriptions, or even missing titles and/or descriptions. You just create “helper columns” to count the character length (LEN function) of your page titles and meta descriptions and reference those in your tables which your charts pull from.
Remember way up there when I mentioned that there’s a “way around” not having all the lovely data that Screaming Frog provides that you wish you had with a Xenu scrape? You can run a Xenu scrape to gather the URLs for your entire site, which you would use to generate your list of the pages that you need to scrape page titles, meta descriptions, etc. for. You could then utilize the SEOTools Plugin for Excel to grab the page titles, meta descriptions, H1 tags, secondary H1 tags, meta keywords tags, rel=”canonical” tags, and meta robots tags for those corresponding pages. This takes more time because you have to scrape each URL individually, but if you’re in a pinch, this plugin allows you to pull that data straight into Excel for further analysis on a wide-scale. I can’t say enough good things about the SEOTools Plugin so please check out Niels’ site to learn more.
Rich snippets are where it’s at! Making your SERPs listings stand out against the crowd can help you bring in more visitors without having to fight so hard for that #1 spot. However, knowing what vocabulary to use for your structured data markup can be a bit confusing. Currently, Google is using the vocabulary from data-vocabulary.org for marking up code on your site to create rich snippets of your site’s breadcrumbs in the SERPs. I created a quick tool to create a mockup of that code. Here’s what it looks like:
The Output cell contains CONCATENATE functions to wrap the name and URLs of each of the breadcrumb values in structured data markup. To learn more about this feature and how to implement the code, check out Google’s Rich Snippet Breadcrumbs page.
On a similar note, I also created a tool with Excel to create a mockup of the code you would need to apply Local Business schema to content on your site. Much like the breadcrumb markup generator above, the Output cell wraps the location information listed in the cells above it with structured data markup (specifically Local Business Schema). You can also find some online Local Business Schema generators like the one here. And, if you need, you can also test the output of your schema with Google’s Rich Snippet Testing tool which works for testing the breadcrumb markup listed above (note: if you’re testing the code, make sure to select the HTML tab).
Last, but not least, I also created a tool to append UTM codes to URLs (back in the day when I was adding tracking codes to URLs for Google Shopping feeds back when Google was providing free Google Shopping listings). You don’t have to use UTM codes strictly for paid campaigns–you can use it for a lot of other purposes (like tracking your CTR of links you build out as part of an email marketing campaign for example). You can find more information on how to use UTM codes here.
Please don’t hesitate to share this post if you like it. Tweets, Likes and Google +1s are ALWAYS appreciated. Thanks!
There are no comments yet.