Use an SEO spreadsheet to properly plan website structure, record keyword themes and have an overview of page URLs, redirects, meta titles and descriptions.
Video Transcript
Hi there, this video talks about an Excel spreadsheet for planning your website. This is a website optimisation spreadsheet. It deals with both search engine optimisation and user experience.
It's certainly not a silver bullet or magic formula to high-ranking websites, but it certainly helps, if not for the user experience, it's a better directory structure. It uses what you call a virtual "silo" layout.
So, let's take a look at a silo layout, and you can see that we've got this colour-coded image here.
Green represents the top level domain or homepage and then you've got the first level, second level and third level denoted by red, orange and yellow. In this example you can't actually see the yellow because we didn't go that far, but if we look at this, you can see here we've got Ilkeston Web Design as the top level page and then below that, you've got a sub-page and below that, sub-pages and so on and so on.
That's what you call a "silo" and a silo is basically... it's laid out, almost like you know, a dissertation? You know like when you write an academic essay or something like that? You'd have a table of contents and then in each section you would have headings and sub-headings and so on.
That's how the search engines work. They like to be able to crawl a page and then find a page on that page and then another page on that page and it's basically about grouping your themes.
So, if we go back to the other example that we were just looking at, let's say you've got a website about fruit juice. On the front page you might have links about the different types of fruits.
You might have apples, oranges, pineapples and so on, and then on each page, say on the apples page, you might have a couple of links which point to pages – one about apple juice and one about how to grow an apple tree. And then on the apple tree page you might have a landing page to a place where you can purchase apple seeds. So, that's a directory structure and that is how this spreadsheet has been laid out.
Each row represents a page. The top level page is green and then you've got red for the first level and you've got orange for the second level and then yellow denotes the third level. That shows you how your website is structured.
It's recommended to use that structure if you can. There's two types of silo. Physical silo and a virtual silo.
So, this spreadsheet is really about design, marketing, development and strategy. Not necessarily in that order. It helps to organise your content, to optimise your content.
It's all for on-page optimisation. Where it does deal with search engine optimisation, it's set up in favour of the Google search engine and it's meant to be that web designers, graphic designers, developers and copywriters can all refer to this spreadsheet. So, if you've got a team of people who are somewhat compartmentalised – like, not necessarily working in the same room – this means that this spreadsheet can be passed between the team.
You can email a copy of this spreadsheet to each member of the team so everybody is on the same page. When changes are made they can be saved and then updated. It's about being able to have a team work together.
Say, for example you've got this page, "Planning Your Website". On "Planning Your Website" we've got a picture of an ebook. So let's say you've got a graphic designer who needs to come up with graphics. Well what they can do is refer to this page and they can look at what the keyword themes are. They can see what the page title is at a glance. They can see what the URL of the website is and so on and so on and they might be able to make better decisions about how they would make their graphics.
It's also worth pointing out that this spreadsheet doesn't use the meta keywords because Google no longer uses them. Let's just take a quick look at the meta keywords for this page. They have been used. You can see their "meta name = 'keywords'" but I know that Google doesn't actually use these anymore. They just ignore them.
So, let's take a look at each column.
So, you've got location which is the first column here on the left and you can see that the homepage – the top level page, the green row – that is set to "0" because that is the main page. And then below that you've got "1.0" which represents the first level. Below that you've got "1.1", "1.2", "1.3", "1.4", "1.5". That's the second level and then below that you've got "1.5.1", "1.5.2" and so on and it goes on.
The next column is "page name". And this is the name of each page. Not necessarily the page title but just the name of the page. In this example the name of the page is also the anchor text and in this Excel spreadsheet I've actually frozen these two columns so when you scroll, when you scroll left and right, these two columns are sticky, so you can always see what page you're actually on.
So if I scroll right to the end here you can still see that we're on, that we've got the first row, the green row highlighted which is the top level page. So the next two, excuse me, columns are "keyword theme 1" and "keyword theme 2". That's the primary keyword and the secondary keyword. And these keywords act as running themes for each page, and they dictate the occurrence of the keywords in the subsequent columns.
So you want the same running theme throughout each page. Not necessarily the exact same keywords all the time – maybe variations of keywords. Perhaps if you're using the broad match feature in the Google AdWords Keyword Planner that can give you ideas for how you can vary your keywords on the subsequent on-page attributes like page title, meta description, alt text and so on.
And it's important to point out that we're not stuffing keywords. We're actually erring on the side of caution if anything. We don't like to go over the top.
That's not good and that won't stand up in the future of search engine optimisation. It's certainly not going to favour keyword stuffing so try to avoid doing that. The next column is page title and as of June 2014 studies have shown that 55 characters is the ideal and maximum recommended length for titles.
Titles are probably one of the most important on-page factors of your website and if we take a look at this page, they provide a handy tool where you can preview what your titles look like in the search engine results. So let me just grab this page title. Let's highlight that.
Let's copy it and then paste and this gives you an idea of what it looks like in the search engines. Now if you were to go over the recommended character limit you'd find that this title is being cut off which means that the user – when somebody is browsing the search engine results pages – they're not going to see your page title in its entirety.
So let's take an example of a long title which let's take this one for example because I know for a fact that this gets cut off, not that that bothers me, because this page is not necessarily targeting the search engines, but you can see look "How to add managers to your google plus", but you can just about fit on the entire title but the Ilkeston Web Design bit gets cut off. But you, know that's fine because this page isn't necessarily meant to rank in search and this page is really for the user. Google can understand it. I mean there's no loss of search engine optimisation value. Google can still read the entire title even if it gets cut off.
So the next column is like I said title tag length 58 max is roughly the recommended length. Obviously, if you're using capital letters in your page title that takes up more space.
You can see that some of these do in fact go way over 58 characters. Some of these are 79 and what have you. It doesn't matter. I mean that's not something that bothers me. Like I say these pages are not necessarily meant for search engines. They're meant for the user.
Your strategy shouldn't necessarily be to rank every single page in the search engines. It should be to rank a few key pages and then when people land on those pages they find their way to these other pages.
The next column is 301 redirects. So, if for example you've set up a 301 redirect which forwards to a longer url, it's handy to make a note of that here because you might need to add some canonicalisation to these urls or you might want to de-index these from search altogether. So for example this short url forwards to this slightly longer url.
Then we've got the page url. So, typically you would use a keyword rich url separated by hyphens.
The next column is whether or not each page is indexed. By default they're all indexed but in some cases we might manually remove them from search because we might not want certain pages to rank. The reason for that being if you've got a landing page with not much on it, it might be a little bit devoid of content, in which case Google would look upon that as a low quality page and to have that indexed... that could have a negative effect on the rest of the site.
Any low quality pages which are rather thin on content you can manually de-index them and then just put "no" here so you know that that is not going to show up in search, and that it is only for people to land on on the website.
Next we've got the meta description column and this also has a recommended length of 160 characters. So, the meta descriptions are in there and I think they all come under... I mean there's one there that's 387, so clearly that needs to change, and that's a great thing about the spreadsheet – it can show you where you need to improve things and what work needs to be done on the website.
Also if the meta descriptions are too short that could have a negative effect, so you always want to try and aim for about 160 characters in length for your meta descriptions.
Then we've got the h1 tags because of the way WordPress is set up we've actually got two h1 tags but generally speaking you're best off having just one h1 tag. Then we've got h2, h3, and so on, then you've got image file names, image alt, so for each image that we've got on each page we'll have image one file name so we know what the file name is and then the alt text that's the alternative text attribute that you can add to each image.
So that's pretty much it. I mean you can get an idea of what this is all about. It's a work in progress – when something on the website changes, the changes also need to occur on this spreadsheet. It allows you to do an audit of your website. It just gives you that bird's eye view of what's going on on each page.
Website SEO Spreadsheet Planner
This is the blueprint of your virtual property. For example, an architect, design engineer and surveyor would need some common point of reference if they were working on a building project.
By the same token, a website design/development team needs to be on the same page for the sake of clear communication, division of responsibilities and progress reports.
Overview
- Top down overview of your keyword themes throughout your site
- Lets you record exact META data (titles, descriptions)
- Gives overview of how your webpage results look in search engine results pages (SERPs)
- Helps prevent your site becoming conceptually disjointed
- Uses the “silo” website architecture
- Keep a record of any redirects
- On page optimisation criteria only
- Nothing to do with off page optimisation such as backlinks
Search Engine Results Pages (SERPS)
This is not a bag of tricks or a secret SEO formula for high rankings. Search engines will always present problems due to factors such as backlinks, competitors, content quality etc. This spreadsheet is a common sense system for organising pages and content.
You would need to test your titles, descriptions and URLs in a search engine simulator such as Mangools to make sure they look good.
Copy Your Titles, Descriptions and URL to Your Website
Once you’re happy with how you know the pages will look within Google results, you would copy the details to your website pages (I use the Yoast SEO plugin) as well as to your spreadsheet for safe keeping.
If you’re not using any specialised Open Graph plugins or code, social media sites will defer to your chosen search engine Titles and Descriptions whenever links to your web pages are posted on their platforms.
It might be wise to have add a column in the spreadsheet for “social”. You would take the SEO titles and descriptions, and tweak the wording so that any page links posted on social media sites show up in a way that’s more appropriate for a social media audience.
What is the “Silo” Website Structure?
The silo structure is a method for organising your website content into specific pages that use a family tree structure. This is similar in principle to the way you would organise a dissertation or academic essay.
There are two types of silo: virtual and physical. The example below is a “virtual” silo.
A “physical” silo uses a hard coded directory structure of folders within folders all using relevant keywords. This is a recommended structure if you are building a site with lots and lots of content and pages.
The URL of a physical silo might be structured like this:
fruitjuice.com/apples/apple-juice/apple-tree/purchase-seeds/
Website Spreadsheet Columns
The spreadsheet has a series of vertical columns. These are generally resizable if you want expand the columns by clicking and dragging with your mouse cursor.
Click this image to view a larger uncropped screenshot
Location of the Page Within the Website Map
Each row denotes a page, and has a numeric value in the left column assigned representing the hierarchy of the website.
These numbers are meant to reflect pages and sub pages as mentioned earlier when we discussed “silos”. If you wanted your website navigation to go deeper you would need to add new rows with a different colour to represent the corresponding page level.
Web Page Name
This is what each page is about in one or two words. If you wanted, you could use the anchor text of your website navigation links here. But do not confuse this “page name” column with the page title which we’ll get to in a minute.
Web Page Keyword Focus
Primary and secondary keywords for each page should be included. These keywords act as running theme for each page and help you steer your page content and remain focused.
Web Page Title
This is the title of the page as seen in the very top of the browser window as well as what is seen in the SERPs.
It is best to use some kind of naming convention structure where each phrase is separated by a vertical divider keyboard character. Your brand/business name would go at the end of the title.
Page Title Character Limit
There is a recommended Page Title maximum character count. Because there is only so much room in the SERPs for your title tag, there’s a cut off point (truncation). An Excel formula in the spreadsheet will tell you if you go over the recommended character limit but you can use the Mangools tool to actually test how it will look in the SERPs.
One more thing: although Google cuts off characters in their title tag area, it only just that human visitors cannot see all of the title. Google still understands all of the words and uses those words to help rank relevant pages for user search queries.
- In summary: it seems 55 – 60 is a safe number to go with.
- Test your Page Title with this handy free SERP simulator tool Mangools SERP simulator
301 Redirect URL
A “301 redirect” a technical server term which means there is one web page URL that forwards users another web page URL.
- This means you can create a much shorter URL that is easy to remember which forwards to the hyphenated URL
- It’s for an easier user experience but is also for if you want to retire an older page but forward the “old” link to a “new” link
- If you advertise your website URL on the radio or in a podcast, for example, people will not remember complicated page names
Page URL
Also known as permalinks, a page URL is the subname of each folder and file displaying your web pages under your main domain.
For example:
https://www.ilkestonwebdesign.com/category/video/
These file are normally HTML or PHP files. The keywords making up this URL are usually separated by hyphens in order to be search engine friendly, as well as allowing a human website visitor to glance at this URL and gain a fast understanding of the subject of the page content.
Long or awkward URLs can have a shorter 301 redirect created for them as discussed in the previous section.
Page URL Indexed?
By default, all web pages are indexed if you upload a sitemap to Google Webmaster Tools. Otherwise the Google bot will try follow links on your site and discover new pages itself.
Pages that have been deindexed from Google’s search database (either via Google Webmaster tools or robots.txt in the server root) are marked “No.” See below:
Page URL Indexed?
No
There are many reasons for deindexing webpages and therefore hiding these pages from search engine bots. It might be that you have pages that need to remain private or password protected. It could be you do not want certain landing pages showing up in Google search results for strategic reasons.
Meta Description
The description snippets is the part that shows in the Google search results (SERPS) below the page title.
This is your chance to elaborate a little on the Page Title.
- This snippet also is often (but not always) used when posting links to your webpages on social media sites.
- If you leave it blank, Google will automatically try to fill in the Meta Description data itself on the fly, and users browsing search results will see Google’s best efforts to provide a relevant snippet.
- It is better to control this description of yourself and write it out first in the spreadsheet, and then copy/paste the words into the HTML meta box in your website. If you update your website, remember to copy those changes back to your spreadsheet. Keep it all syncronised.
Meta Description Character Limit
Once again, there is a character limit in the Google SERPs for the description of each page.
320 characters is recommended as a maximum, as of December 2017.
Heading 1: <h1></h1>
This is for both search engines and human visitors.
Heading 1 HTML markup formats and styles your font to a larger size according to your CSS style sheet. You should try to use your primary key words or key phrases here since it is like hanging a lantern on your web page.
Google sees the keywords used in a heading tag as an important summary of the content on that page.
The Heading 1 tag should be generally used once per page, though WordPress templates tend to ignore this.
However, according to Matt Cutts and Google Webmaster Guidelines you do not need to worry about two or three H1 tags. Just do not use, say, twenty of these tags on one page. And do not stuff keywords!
The search engines will reward the web page it if it is genuinely useful enough for people to want to share and link to it. While it is good SEO and web design practice to use the Heading tags, this needs to be seen as part of the bigger picture in the overall success of a website.
Heading 2: <h2></h2>
The Heading 2 tags are slightly smaller than the previous heading tag and with less Google love placed on the keywords. In terms of SEO juice it is downhill from here but if you are worrying about this too much you’re focusing on the wrong thing.
It’s acceptable to use two of these Heading 2 tags on a single page, but again, do not get anal about this. Many people DO use a dozen or so headings on a page without ill effect and they probably don’t even care about SEO.
Heading 3: <h3></h3>
These headings are used often as subheadings to the Heading 2 tags. It promotes a dissertation like parent structure for the content of the page. As you can see, each of the bold headings for each section of this article are using the Heading 3 tag.
Image Filenames
Images can sometimes rank well in search engines. There is a column in the spreadsheet for these, but it might be overkill to record this amount of detail.
By all means, give a meaningful file name to images for the purpose of possibly appearing in Google Image search. It’s common sense to take the time to write meaningful filenames (easier to find in your website dashboard media library, for example), but you might want to skip this in the spreadsheet.
Also if people download images to their computer, a logical file name helps for when they save the image to their machine.
Summary: Handy Way to Stay Organised and Project Manage
With so many teams working remotely, having the right approach to project management is key. You can modify the spreadsheet however you like, but keep in mind this is just to help streamline communication between colleagues/associates as well as having a written record of what’s actually on your website.
It’s worth mentioning that you should set up something like Github or Trello to easily manage website-related tasks. Relying on email for daily back and forth becomes tiresome and stressful.
If your website is already launched, you can scan it with Screaming Frog SEO Spider and grab the information from the website crawl, and then copy that to your spreadsheet.
The spreadsheet is used on most Ilkeston Web Design projects to help manage the project. I will supply this to you in .xls format. This can be read by Microsoft Excel and Apache Open Office.
