Using Excel for Search Engine Optimisation (part 1)

Excel, for all its shortcomings, is an ok tool for handling lots of data - databases are better than spreadsheets, but then you have to be a proper data geek to use them. Excel, on the other hand, is fairly ubiquitous and accessible to beginners. Search engines, particularly Google and latterly Bing, collect vast amounts of data, some of which they share with webmasters and SEO types (through tools like Google Analytics and Webmaster Tools), and which we can then use for search engine optimisation.

But, although Excel is easy to pick up, because there's so many different functions within it, it can be easy to miss efficient ways of handling large volumes of data. You may come across a way of improving your workflow slightly and stop there, without realising how much more time you could save with a few extra tweaks.

Here I'd like to offer some of the experience I've gleaned over the last 11 years of working with spreadsheets. ¹

Generating lists of backlinks

You're one of two kinds of people:

I'm in a hurry and just want a way to generate a list of backlinks.

Download this spreadsheet from here on Cushtie.com, and put all the search terms you've got into column A, from row 4 onwards, and then put all the topics and URLs you have into the top two rows, from column C onwards.  This will work for up to 500 different search terms, and 25 different topics.  If you want more, you'll need to keep reading, or experiment. Once you've got that spreadsheet and filled it in, click here to finish up.

I'd like to know what you're doing before I start downloading strange files from the internet.

For this example, I'll use my own blog, Comments (0), because all the URLs have a structure that's easy to understand, but this should be readily applicable to other websites.

First, get a list of all the keywords that people have used to search for your site. This is available via Google Analytics in the Keywords page. Handily, Google will give you that data as a CSV export, which will play nicely with Excel. Don't just copy and paste from the web page! You'll get all sorts of messy things in there like formatting and hyperlinks that you don't really want.

So here's some keywords that people used to get to Comments (0):

jokes about volcanoes
cushtie
bing maps google kml
bloomberg free food
cafe milan shaftesbury avenue
jokes about vegetarians
and then felt like death on sunday. on 3 pints. this is quite tragic.""
canteen prince's building
make mine dean street
bing kml
dislocated shoulder
iceland volcano jokes
luke haines
bing google earth
polar night half marathon
tromso half marathon
black rain
butch bradley hong kong
funny jokes about volcanoes
how to make 7a conrod stranger
jason wood
joke about volcanoes
jokes about volcano
jokes for volcanoes
jokes of volcanoes
just felafs
lyndhurst terrace
only boring people get bored
tromso halfmarathon
tromso marathon
volcano jokes
volcanoes jokes

So we sling this into Excel - now we have a list of all the things that people have been searching for that ended up with people arriving on the site. (Note that I'm not going to give you all the keywords that have been used, just a taster).

Now, have a think about what content people have been looking at: go back to Google Analytics, and download the Top Content report. Put that into Excel as well, and look to see which pages are popular. (We hope that pages are popular because they're the ones that people want to look at, and that you should get more traffic by driving more people to these pages. Once you've got them, then you can do all sorts of good things to keep people on your site, but for now, let's concentrate on acquisition.)

This gives us two spreadsheets - one of the keywords people use to get to you, and one of the pages they look at. I'll show you how to use Excel to match them up.

One way you could use would be the Find and Replace functions, as mentioned here. If you know that you want to look for the word "joke" in the list, you can just use Replace and change the format of all the keywords that include "joke" to be a different colour.

Which is nice, but we're talking about automating things here, and without a huge army of unpaid interns, it's rather inefficient to do this one word at a time. Plus you don't have a quick way to summarise how many keywords included the word "joke", without, well, counting them. Which is a bit dull.

Here comes your first formula.

Create a new spreadsheet, and paste all the keywords into the first column, starting at the fourth row from the top. Then in the third column, fourth row down, paste this unfriendly looking formula:

=IF(ISERROR(FIND(C$1, $A4)),0,1)

Copy that formula down as far as the end of your list of keywords, then come back here again.

If you want to know how the formula works, follow this footnote: ². If you just want to know what it does, carry on.

The formula looks at the keyword, and if it finds the word you're interested in, it shows a 1, and otherwise a zero. If you highlight the whole column in Excel, the total will show up at the bottom of your window, which saves you having to count how many cells match.

But you may be wondering what we're interested in, because right now our spreadsheet is full of uninteresting looking zeroes. But you should have some idea what you're interested in, so start typing words into the top row of your spreadsheet, from the third column onwards. You'll then need to copy that formula over, so it's in all the cells beneath each of the words you're interested in.

All of a sudden things should look more interesting - now you have a count for each topic for how many keywords are generating traffic for it. Now you can filter the data for all the keywords pertaining to a particular topic, or for several different topics.

... except that would be manual too, and we shouldn't propose manual approaches to solving data-related problems. Instead, we'll change the formula slightly.  Put this into cell C4 and then copy it down and across:

=IF(ISERROR(FIND(C$1, $A4)), "",C$2)

Then put the URL for each topic into the second row, underneath the appropriate topic. Now the formula will start to show blank cells if the topic and keyword don't match, and the appropriate URL when you do have a match.

We're not done just yet. Scroll down past all your keywords to cell C505, and paste this formula:

=IF(D505="", Relationships!C4, D505)

before copying it out to the right as far as your topics go, and then down 500 rows to cover all the keywords you've entered.

Now if you copy your keywords again into the cells from A505 downwards, you'll see each keyword against the most appropriate URL from your site that the spreadsheet could select.

We're almost done. The spreadsheet gives you a list of keywords and URLs, and now you can pass that to your army of subservient interns / start building backlinks on other sites, focussing on all the appropriate keywords to use.

There's some obvious ways to improve this process, and some glaring gaps. If you notice some of those, let me know, and hopefully in the next part of this series, I'll offer some more useful things for you, as well as adding in improvements / fixes to the most egregious errors.

¹No, I do have a glorious and fulfilling life. Really.
² Start at the middle and read outwards. FIND(C$1, $A4) looks to see if the contents of cell C1 (the topic) is anywhere in cell A4 (the keyword). The $ before 1 and before A make sure when you copy that formula to other cells, it keeps looking at the top row and left column, rather than the references veering off everywhere.
If FIND can't locate the topic anywhere in the keyword, it returns an error value. ISERROR looks for that - if it's true, then the IF statement will output 0, and if it isn't true (there is not an error) then it will output 1.