As we all know Excel and Google Docs can be great tools for any SEO consultant. Hook Google Docs or Excel up to API’s from Google, SEOMoz, SEMRush or any other tool you like to pull in data and you can begin to automate some key tasks like keyword research, ideas for content generation, and competitive research, backlink analysis, rank monitoring, reports, etc. It’s important to understating how these spreadsheet tools work.
Distilled offers an awesome starting place for getting familiar with using excel to manipulate data. They have a handy guide – Excel for SEO that covers a wide range of tasks, tables, time savers, and shortcuts. Be prepared to spend a decent amount of time getting through the workbook and example pdf. This is time well spent as you will gain the knowledge to create your own tools and worksheets that suite your needs best.
AdWords API Key
This next tool will require you to sign up for an AdWords API Key in order to pull in data. It may take a few weeks for Google to grant access to an API Key. Once up and running this is a great tool for KW research as it grabs exact, broad, and phrase match keywords. You also can see local search volume for mobile and desktop and generate related queries using Google suggest all in an excel sheet.
Once you have boiled down the keyword traffic and selected the keywords that you are looking to rank for this tool will help you quickly figure out which websites are ranking most frequently for the terms you want to rank for. This tool is great for quickly finding out the top competing websites in your vertical. This tool requires access to SEOmoz’s api to work. Once you have figured out your competitors in the SERP you can start researching their backlinks.
This third example comes from SEER interactive. Once you identify the websites that rank for the terms you want to compete for it’s a good idea to reverse engineer their link patters and backlink profile. This excel example shows you how to break down large amounts of link data into actionable steps. Analyze anchor text, domain type, and link category. This is a great tool for figuring out the link profile of the top ranking competitors.
Bring GA and AdWords straight into Excel
After you begin any SEO campaign you are going to need to look at the effects of your efforts and one of the most popular web analytics software in existence is Google Analytics. My final link is a tool that can bring any data form GA and AdWords straight into Excel. This presents a great way to create a dashboard of your favorite metrics right in Excel that you can share with your client if desired. You can also make some nice 3D Bar graphs for some nice eye candy in your reports.