Keyword Research tool using Excel Macros

The project consists of 3 Excel Macros. Each Macro gathers data related to a set of inputs using web services from google and yahoo. The input for Macro 1 is a set of keywords on Tab 1 on the file. The output of Macro 1 is placed on Tab 2 and used as input for Macro 2. The output of Macro 2 goes to tab 3 and is used there as input for Macro 3. ## Deliverables ## Summary of Tool Purpose The idea is that these tools should be able to take low 10s of keywords as a seed in tool 1 and follow through to complete the whole set of tools. Do a keyword expansion on 10's of keywords then analyze those expanded keywords to see how many websites and which websites are competing for them in natural search on Google and finally the tool should output who links to those websites. All of this data should be easy to summarize with pivot tables by domain, position, seed keyword and so on and so forth. Each section that queries a different URL should be plug and play so that should that URL or component stop working we can have another macro built to fill in that gap without recreating the entire tool. For tool 1 it should be possible to look at 100s of keywords using that tool even though that won't flow through well into the other tools ## Tool 1: Keyword List Generation This project should be built as an excel macro and should be well commented: 1. Read as input a flat text file with a list of keywords (one keyword per line) as an input, you must respect spaces at the end of this line and assume this file was created in windows notepad 2. For each keyword in this file pull the list of Google suggestions from the Google suggestion tool via one of the URLs below (whichever works most robustly or of your own making) * [login to view URL] * [login to view URL] * you should do some research around this to see if there needs to be a certain wait time between queries and/or header sent in the request so that Google does not rate limit or block this scraper if there are no obvious issues please put in a time delay of 2seconds between calls to Google and allow us to configure that time limit * if you can find a better way to do this than referencing the above URLs then do so but the urls should be built in as configurable in the macro since Google may change these in future * this needs to include URL encoding for special characters 3. Create a tab in the excel file with each of the responses from Google in column B and the keyword used to create them in column a (so if you have 24 keywords to call google for and each returns on average 10 responses then there will be 240 output rows) * when producing this resulting keyword list you need to strip out any of the formatting that google includes so it's just a pure output keyword list ## Tool 2: Volume and competition analysis tool This project should be built as an excel macro again which takes the data from the previous analysis and uses that to produce the next data set. This too should be well commented. 1. Read in the keywords from the previous macro in column B and pull the google search results for them (top 10) * use this google URL and adapt it: [login to view URL] * needs to include URL encoding for special characters 2. Note in column C next to each word how many search results there were for each google search 3. Create a new tab in the spreadsheet with a header row in row 1 with appropriate names for each column * column a place the original seed keyword * column b place the derived keyword which was searched for on google * column c place the number of search results for that keyword * column d place each of the urls actually returned only the first 10 * column e place the domain of each of the urls returned only the first 10 * column f place the position of each of the urls returned (i.e. was it 1st in the results page from Google or 10th) <!-- --> * this should be expected to produce 2400-5000 rows 4. As above this tool should have a customizable query url to be called and between each query a 1second gap should be left which is also easily configurable ## Tool 3: Figure out who is linking to them This project should be built as an excel macro which again takes data from the previous query 1. Use the yahoo site explorer API to run through each of the URLS produced by tool 2 in column d. The apis are in the file below * [login to view URL] * pull the maximum (100) backlinks each time you call this - but have this number as configurable in the spreadsheet * only pull links to this url FROM OUTSIDE the domain of the url 2. In the original tab from which you are reading the data enter the total number of backlinks detected in column g for each url in column d 3. Create a new tab in the spreadsheet with a header row in row 1 with appropriate names for each column and the same data in columns a-g that exist in the tab from which you are reading the data (including the data you are entering in column g in step 2 here) * in column h enter each of the (so 100 for each row in the previous tab) urls linking to the url in column d * in column i enter the domain of each of those links * in column j enter the order in which the link was returned (i.e. the first link returned would get 1 the last would get 100) * often there will be less than 100 links returned