Microsoft Excel Tips for SEO – Extracting Domain Names

Being a bit of a whizz with spreadsheet packages such as Excel is a very important skill to possess for SEO’s. The industry is becoming more and more data orientated thanks mainly to the great link analysis software that is now available in the marketplace from places such as SEOMoz, MajesticSEO and SEO Spyglass.

This series of blog posts is designed for webmasters to help analyse their backlink profiles and help carry out some basic competitor backlink analysis using Microsoft Excel.

Extracting the Domain Name from a URL

When using a link analysis tool such as Open Site Explorer you will inevitably return lots of line data. This can often be overwhelming especially if you are analysing an expansive list of backlink urls. The quickest way to return the domain name is to use the text to columns tool.

Simply highlight the URL’s column and choose data and then text to columns.

Now using the Convert Text to Columns wizard choose delimited

And insert / into other under delimiters and select treat consecutive delimiters as one.

Click next and then finish, you will now have all your data split across several columns but more importantly you will now have your domains in one column. You can now use the remove duplicates tool to remove any repeated domain names so you now have a list of websites to monitor or target for new links.

Another way to do this is using the Excel text functions LEFT & FIND

The formula I use is =LEFT(A1, FIND(“/”,A1,8)) (assuming Cell A1 is the cell containing the URL)

This formula basically says find the first “/” in the URL, after letter 8, and return everything to the left of it.

I hope this post was useful and I’ll be posting up some more Microsoft Excel SEO tips.

How to Get Your WordPress Content Indexed – Fast!

Getting your new content indexed quickly by Google is now more important than ever following the new Google Fresh Update. The recent Google algorithm changes put more importance on being the newest or latest. Below are the techniques I use to get new blog posts indexed quickly, sometimes in less than an hour, for my clients and my own sites.

Firstly you need to make sure that Googlebot can “crawl” your website quickly and easily, the best way to do this is by setting up and XML sitemap. For WordPress users I recommend the Google XML Sitemaps plugin. Sitemaps allow Googlebot to find new content quickly and easily within your site and will also ensure that most of your site pages are indexed.

Next if you are using WordPress for your blog you want to Ping as many services as possible to let the search engines know that your site has been updated; a ping is an XML-RPC-based push mechanism by which a weblog notifies a server that its content has been updated.

I ping numerous services at all once automatically, simply add this list to your Ping Services in your WordPress installation:

http://api.moreover.com/RPC2

http://bblog.com/ping.php

http://blogsearch.google.com/ping/RPC2

http://ping.weblogalot.com/rpc.php

http://ping.feedburner.com

http://ping.syndic8.com/xmlrpc.php

http://ping.bloggers.jp/rpc/

http://rpc.pingomatic.com/

http://rpc.weblogs.com/RPC2

http://rpc.technorati.com/rpc/ping

http://topicexchange.com/RPC2

http://www.blogpeople.net/servlet/weblogUpdates

http://xping.pubsub.com/ping

In testing I have also found that giving your new content a site-wide link will help get it indexed. This again can be easily achieved with WordPress by using a Recent Posts Widget in your Sidebar or Footer to make sure your new post is linked to by the majority of your web pages. This will show Google that this new piece of content is very important.

Another great way to let the search engines know that your site has been changed is to build links to your new content a few social bookmarks on services such as Digg, Folkd and Reddit, will get your site crawled quickly, but remember being crawled is not a guarantee that your new content will be indexed by the major search engines so make sure you have good quality on-page SEO and that your blog post is original and contains at least 400 words.

Finally you want to submit your Feed to a number of Feed Aggregators. If you List your RSS feed in these feed aggregators Feed-Squirrel, OctoFinder & FeedAdage. By submitting your Blog feed to these RSS sites, they will keep track of your newly published posts and index them within their site, providing more backlinks to your site.

If you have any more indexing hints and tips please leave a comment below.

Update: after posting this blog  I submitted the post to Digg and sent out a Tweet. I then checked Google and I was indexed in 2 minutes…

Obviously I doubt you will always be indexed this quickly but it just goes to show how easy it actually is!

Chris Dyson+

My Comprehensive Guide to Guest Blogging

I often receive emails and tweets from my followers on Twitter who ask me what is the best way to get backlinks to their sites. All too often I see people recommending blackhat or ineffective link building techniques that will often see little return on the time or money invested by the individual concerned. So this brings us to guest blogging or guest posting, it is a great form of link building because it’s organic, it also gives exposure to you and your brand. I can’t recommend guest blogging as a scalable and long lasting link building technique enough.

So in order to help people find out more about Guest blogging I have taken the time to write this guide to guest blogging so that hopefully I can quell a few myths and help make the internet a better place with fewer spammy articles and more quality content.

So what is Guest Blogging?

Guest blogging is the process of you writing for another website as a guest author – it is that simple.

It is very important to know that guest blogging isn’t only for promoting a blog but it can also be used to promote your website and business. You can get a professional copy writer to help write the content for you but to be honest it’s much better to write the article yourself so that you can put across a bit of your personality and possibly explain your industry knowledge a little better.

Why Should I Guest Blog?

Quite simply I always answer with the same 4 things to businesses. Business owners want to see figures attributed to any investment so I always quote the following which can be measured quantitatively:

  • Web Traffic – Let’s be honest the most important factor in running a website is traffic. You can be top of the SERP’s for lots of keywords but if those keywords aren’t bringing people to your site then what’s the point in them! Guest blogging allows you to get in front of a new audience and often on very busy websites and some of these people will read your article and come to your blog/site to learn more about you.
  • More Customers – It is very important that once you get a person on to your website you convert them into a customer or a prospect whether that means they buy your product/service, join your mailing list or simply just subscribe to your RSS feed.
  • High Quality Links – If you want your blog or website to get good rankings in the search engine results pages, then you need backlinks. Guest blogging is one of the simplest and most legitimate ways to get your keyword rich anchor text links to your website from high authority sites. I am yet to come across a simpler and more effective white hat method for getting good rankings.
  • Exposure – As any business owner knows it is all about getting your business or brand name out in to the world. Building a good reputation in your field will help you achieve so much more. Opportunities may well arise for you to speak at business conferences to share your knowledge with other people in your community.

Guest Blogging offers a real win-win-win opportunity for all involved – the reader gets some great content from a new perspective, the blogger gets a day off as well as some content to enrich their own work and you get access to an audience as well as a good quality link.

The benefits of Guest Blogging vs. Article Marketing

One thing I have noticed is that many people compare guest blogging to article marketing, this is such a great misconception. I have yet to see an article directory that has sent over 1,000 visitors to a website in 24 hours.

Many article directories have been downgraded following the Google Panda update and with good reason too as many had become a haven for dodgy SEO practitioners who spun content until it was unreadable. Article directories are not designed for people and offer very little SEO value.

Guest Blogging Strategies

Guest blogging has principles and one of the principles of guest blogging is that you must set goals before getting started. Don’t burn yourself out writing lots of articles for too many sites, so make sure you stick to your goals to get the most “bang for your buck”. This is why you need to also think more strategically.

I see guest blogging opportunities as 2 key tiers:

  1. Contributing to high-quality; high-authority sites
  2. Contributing to good quality, less authoritative niche sites

If a guest bloging opportunity does not fit in to the top 2 types then I don’t bother with it, after all we are looking for decent links that will provide our sites with traffic and good rankings. I don’t write articles for lower quality sites simply because a) they are more likely to disappear and b) blog posts become more and more hidden over time so if the blog I am contributing to does eventually hit the big time my article will be under layers and layers of pages and have very little link juice.

Write your post first – I always recommend writing the article first before pitching your ideas to the blog owner. There is nothing worse than pitching an idea, agreeing a deadline and then struggling to hit it.

Respond to Comments – Because guest blogging is about writing on relevant websites, it’s important to stay professional and friendly with the influencers in your niche that you interact with. Not only should you try to keep in email contact with the blogger you’re writing for, but respond to as many comments as you can once your guest post goes live. This will show the blogger (and their site readers) that you genuinely want to give people genuine advice on whatever it is you write about.

Don’t forget about your blog! – After all let’s not forget that once you have had lots of new visitors to your website you need to keep them coming back. So make sure that you write some great articles in advance of any guest blogging campaign so that any new RSS / email subscribers will stick with you.

How To Find Guest Blog Opportunities

There are lots of great ways to find guest blogging opportunities and I recommend that you start out by reaching out to your business contacts, friends and social networks to see if they would be prepared to let you write an article on their site. But as I said above it is important to find websites with lots of traffic and good authority so I recommend the below techniques too:

Google Search – Here are some Google search tips that will let you find valuable guest posting opportunities in your niche.

Firstly do a search online for Guest blogging opportunities. Here’s more or less complete list of phrases bloggers use to invite people to guest post:

  • “Submit a guest post” / “Submit post” / “Submit blog post”
  • “Add blog post”
  • “Submit an article”
  • “Suggest a guest post”
  • “Send a guest post”
  • “Write for us”;
  • “Become an author”;
  • “Guest bloggers wanted”
  • “Contribute to our site”;
  • “Become a contributor”;
  • “Become guest writer”
  • “(Write for our) guest column”.

Now, you may have noticed that all the phases are used in the quotation marks – this is to force Google to search for the exact match because otherwise you will get too general results randomly mentioning “post”, “contribute” and “guest”.

For example type into Google:  “Submit an article” credit cards

Now if you are a little more Google search savvy you can try some more advanced search strings.

More often than not it is hard to describe your blog topic with just one keyword. For example, your blog may be about money, credit cards, loans, finance etc. The more words you use, the lesser results you will get, because Google will do its best to show you only those pages that have all of those words listed.

To get broader but relevant results, we should instruct Google to search for any of the words – for that we can use OR operator between the words: “money OR loans OR finance OR credit cards” because, essentially we are interested in guest blogging on sites covering any of these topics.

To go even further in this direction, we can use ~ operator before a word that may have many synonyms and related words and thus instruct Google to look for all those synonyms. For example, “~money” search will include words like “currency”, “finance”, “financial”, etc. Also there is the wild card operator “*” as many bloggers may use a phrase differently e.g  “submit a guest post”, “submit your guest post”, “submit us a guest post”.

So if we were to combine all the above tips you would get

“submit * guest post” ~Money OR Credit Cards OR Loans

Guest Blogging Community –  There are various blogging communities out there. MyBlogGuest is my personal favourite guest blogging community as it has a wide variety of niche blogging opportunities and a very healthy number of members. Simply visit their forum and post that you are looking for guest blogging opportunities in your niche.

Technorati  – A great way to find the top blogs in your niche is by making use of the technorati blog directory. Even though this isn’t a great way to find smaller blogs, it is one of the most effective ways to discover the big blogs in your niche should you decide to start guest blogging on big blogs.

Guest blogging is a numbers game so always aim to write good quality blog posts on 10 great blogs rather than on 50 lower authority blogs.

Approaching Blog Owners

Once you have found the blog you want to write for there are some basic steps you have to take to make sure your request is noticed:

  1. Read the Guidelines: it may sound obvious but big blog owners are busy people so make sure you follow their submission requirements.
  2. Get to know them: read their previous articles and guest articles, comment on the website and join in the conversation, follow them on Twitter – play to their ego
  3. Make it easy for them: most of the time I send an email with the blog post in a .txt notepad file with my copyright free images attached. I use a .txt file so that the html stays intact and my links are exactly how I want them.

Writing Great Guest Blog Posts

After all the work you have put in so far you want to make sure that your guest blog is accepted and published on the site you are targeting. So I guess the following tips will help you to make sure you write a great post:

Research -  use as many tools as you can to write a great article. Use Google Insights, Blog Comments, popular blog posts on other websites, Yahoo Answers etc. to research your topic. Try to find out what questions people are asking. I always find it is better to write an article that people want rather than just to throw up an article that “I think” they want!

Headline – make sure your headline is catchy and clearly describes your article. Remember you want people to read your guest post so be sure to spend some time thinking of great ideas.

Write your best content – This goes without saying, take your time and put some effort in to making the blog post look great and read well. Stay on topic and don’t waffle.

Images and sub headers – Split up your article with headings and images

Word Count – not too short and not too long… I have found that most people want to read a guest post around 600-1200 words.

Proofread – I can’t stress this enough; make sure you take the time to read and re-read your guest blog post for silly spelling mistakes and grammatical errors. At the end of the day you don’t want your work to be rejected after you’ve spent hours researching, writing and flirting befriending the blog owner.

What to Do If You Get Rejected

You have to accept now that you will get rejected. But don’t feel bad, move on some bloggers may give you a reason others may not. The main thing to do is keep trying and learning. I would urge that you don’t keep pestering the blogger if you are rejected and take any feedback they give you on board.

Go and Take Action

Now that you have read this article I want you to identify some key blogs in your business area and write some great guest blogs for your website. I hope this guide has been useful and please leave some comments below to share your thoughts and experiences of guest blogging.

I would also be grateful if you could share this article with your friends too :)

+Chris Dyson

How to Use 301 Redirects

There will come a time when you may decide to obtain a new domain name for your website or you may even want to delete some pages in your website or you may want to change the URL of a web page to a more SEO friendly one e.g rather than www.mysite.com/123.html to www.mysite.com/my-product.html. The problem is you have probably spent weeks, months even years building links to these pages and the second you change the url structures of these pages they disappear in to the “404 – Not Found” abyss and losing you your precious search engine positions.

Thankfully there is a way to make sure your SEO is not undone and that is by using 301 Redirects. A 301 redirect is the most efficient and Search Engine Friendly method for web page redirection. It’s not that hard to implement and it should preserve your search engine rankings for that particular page. If you have to change file names or move pages around, it’s the safest option. The code “301″ is interpreted as “moved permanently”.

You will need access to your .htaccess file to modify or add the redirects to your pages. The .htaccess file is a control file that allows server configuration changes on a per-directory basis. It controls that directory and all of the sub-directories contained within. In most cases, this file will be placed in the root Web folder for your Web site. If there’s no .htaccess file present, create one.

  1. Open your .htaccess file in notepad
  2. In your .htaccess file on a new page add the line:
     RedirectPermanent /old-file.html http://www.domain.com/new-file.html

    or

     Redirect 301 /old-file.html http://www.domain.com/new-file.html

    But what if you have dozens of pages to redirect because you have changed the domain name; well this can be done by adding the below code to your .htaccess file:

    Options +FollowSymLinks
     RewriteEngine on
     RewriteRule (.*) http://www.newdomain.com/$1 [R=301,L]

    This would require that the old pages you have redirected have the same URL structure as the new URL.

  3. Save your .htaccess file and re-upload to the root directory.
  4. To test your changes load the old page in your browser and see if it is redirected to the new page.

If you are using a cPanel hosting account then you can make redirects in your account.

  1. Log into your cPanel, and look for “Redirects” under Site Management
  2. Put in the current directory into the first box
  3. Put the new directory in the second box
  4. Choose the type (temporary or permanent) temporary=302 and permanent=301
  5. Click “Add” and you’re done

 

+Chris Dyson

How to add a website to Google Webmaster Tools

What are Google Webmaster Tools?

Google Webmaster Tools (WMT) is an important resource for any webmaster. It is designed to inform web owners about how “Google bot” is interacting with a website. It gives detailed information on important SEO factors such as broken website links, popular search terms (keywords) and how often Google is stopping by. It’s Google’s way of giving site owners the information that they need to be successful.

Google Webmaster Tools let you access data about the indexing of your website. This does the following:

  • Displays Google’s view of your site
  • Helps you diagnose site issues
  • Lets you share information with Google to improve your site’s visibility in search engine results
  • Provides you with crawl statistics, recent queries, errors, and other information that Google collects about your site

How to add your website

Getting started with Google Webmaster Tools is not to difficult. The first thing you’ll need is a Google account. Since all Google services use the same login, you may already be setup. If you have Gmail, Adwords, Analytics, Google+ or one of the many other services, then you are ready to go. If not, sign up for a free Google Account.

Once you have created or if you already have a Google Account you should be able to use that to login and then create your WMT’s account.

Then click on the “Add a site” link and enter your full website address. Site owners can add in as many sites as they want. Once you have done this it will ask you to “Verify ownership” of the website. To do this you need to add a meta tag to the home page or upload a file to your website that WMT’s will provide. We’ve found the easier and quicker method is to add the meta tag into your website code.

Verifying the site takes only a few seconds but access to the web server is necessary. Verification ensures that you are the site administrator/owner and can get the insight. If you don’t own the site, and you can’t verify it, then Google won’t give you the detailed information.

If you have difficulty setting up your webmaster tools you may need your web designer to help you.

Next you want to submit a sitemap to your new Webmaster tools, you can create and upload a Sitemap and use this page to submit it to Google. Just click Add a Sitemap and then follow the instructions.

Google Webaster Tools are a great set of resources to see how Google is interacting with your site. You can see issues, your site’s linking structure and how people are finding your site. It’s much more than other search engines are doing and something that is highly valuable for any site owner.