Tested recently, I suggest you discover the SEO Tools plugin for Excel, a natural referencing tool that is worth a look. The great interest of the tool is to have the integration in Excel of a set of tools intended for SEO service. In summary, this plugin allows you to have a mini–SEO Framework that will delight you if, like me, you handle Excel a little better than the code.
On the other hand, the tool is quickly limited in processing capacity on all its scraping functionalities (recovery of data on third-party sites) to carry out large mass processing. In most cases, its capabilities seem sufficient to me if you’re not a nasty Spammer, because for each of the features there are many equivalents that are more powerful, faster, more reliable… but also more complicated to learn.
INSTALL SEO TOOLS FOR EXCEL
Installing the SEO service Tools Plugin is fairly trivial so I won’t describe it precisely: you go to the author’s site ( @nielsbosma whom I salute by the way;” he” as they say back home in Sweden); Download this excellent Excel SEO Tools plugin and follow the instructions in the readme.txt file.
And there… miracle of creation! You get a new tab and a toolbar in Excel integrating the new functions of the SEO service Tools plugin.
SOME OF THE COOLEST FEATURES OF SEO TOOLS FOR EXCEL
It’s not enough to have installed your 114th (or more) SEO plugin, you will now have to ask what it will serve SEO service, you well… I suggest you after a quick tour of the owner to discover some of my finds.
“ON PAGE” CRITERIA ANALYSIS FUNCTIONS
The first set of functions available allow you to extract the value of the title, Meta description, H1, etc. from the value of a URL contained in a cell.
The advantage of this feature in Excel is to allow you to extract different URL information (e.g., benchmark of your homepage VS that of your competitors on the different “On Page” criteria). You will now have a whole range of functions allowing you to extract a particular element from a page (or several) directly from Excel: HTML Title () (extracts the title tag), HtmlMetaDescription () … I won’t go all the do you must probably imagine the rest…
COUNT THE NUMBER OF WORDS IN A CELL IN EXCEL
The Count Words () function is used to count the number of words. It’s a fairly basic function but which however does not exist (or is very well hidden) in Excel… You probably knew its little sister the NBCAR () function which allows you to count the number of characters in a cell, you will now be able to take advantage of the big sister function Count Words ().
For example: here is this generated block of text on which we use this function and which quickly returns 108 words contained in this cell. The feature is useful for quickly checking the amount of text produced word volume knowing that speaking in word count is more common than character count in the world of content production.
ANALYSIS FUNCTION FOR MEASURING SOCIAL INTERACTIONS OF WEB PAGES ON FACEBOOK, TWITTER AND GOOGLE +
3 functions Twitter Count(), GooglePlusCount() and Facebook Likes() allow you to quickly retrieve the number of Facebook Likes, Tweets and +1s made on a URL, for example, to quickly benchmark the social virality of competing pages.
To use these functions for measuring social engagement on a page of a website, simply refer to a cell containing a URL, the plugin is responsible for reporting the number of likes, tweets and +1 associated. If you are looking for a more powerful tool (large page volume) I would recommend Aymeric’s solution to retrieve the number of Tweet / Facebook like / Google +1 of the pages of a site from an XML sitemap.
DUPLICATE CONTENT ANALYSIS FUNCTION
Other interesting functions for SEO service that allow to evaluate the degree of content differentiation:
The LCS () (longest common substring) function compares 2-character strings and extracts the longest common character string found.
Example: LCS (“my name is nobody”; “my name is someone”) => will therefore return “my name is” which is the longest common character string found.
To calculate the similarity rate, you must then count the number of common words and relate it to the number of keywords used in the reference text.
Example: Count Words (“my name is”) / Count Words (“my name is nobody”) => 3/4 = 75% similarity between the 2 strings.
The FindDuplicateContent () function is an extension of the LCS () function which allows to output the longest common character string found in a reference text and a range of cells containing a set of variants. The operating principle is therefore identical except that we no longer compare 2 values but a reference value with a set of values.
In practice, I would rather advise you to evaluate the similarity between 2 contents to use an Excel macro based on the calculation of the Levenshtein distance which is more advanced.
FUNCTIONS FOR MANIPULATING CHARACTER STRINGS
Without going into the details of this feature, the “strings” menu allows you to extend the search and manipulation features of Excel’s character strings (including the search/replace function) with support for regular expressions. These functions therefore make Excel much more powerful.
For example: I am trying to define a site typology from a list of domains offering a link to my site (lists from Webmaster Tools, Majestic SEO service or other). I know from experience that most of the time, blogs contain the keyword “blog” in their domain name or are easily identifiable thanks to the name of a known blog management platform (type pad for example). I can therefore automatically test in a cell whether the site is a blog or not using the RegexpMatch (cell; “blog|typepad”) function which will look for the word blog or type pad in the tested cell and return true if it finds one of these 2 values.
DATA ANALYSIS FUNCTIONS FROM GOOGLE
The SEO Tools Plugin for Excel also offers some handy little analysis tools.
- GoogleIndexCount () which as its name lets guess proposes to recover the number of indexed pages of a domain.
- Google PageRank () which returns the popularity of a page.
- GoogleResultCount () to evaluate the degree of concurrency on a target expression.
For example, I am trying to assess the competition to make a choice in relation to 2 targeted expressions by calculating the number of pages using these expressions in the title (using the Google command “intitle:”) GoogleResultCount(intitle: “expression 1″) => 300,000 results and GoogleResultCount(intitle: “expression 2″) => 200,000 results.
I therefore deduce that expression 1 is more competitive than expression 2 since many pages on the web deliberately seek to position themselves on this expression by integrating it into their title.
GOOGLE ANALYTICS QUERY TOOL
Who says Excel necessarily says data processing and in terms of Google Analytics data is essential. Excel therefore allows you to “request” data from your GA account to work, for example, on a list of keywords generating visits to your home page (see example in screenshot).
Personally, for that I rather use Excellent Analytics, which seems more intuitive to me, but the capacities of the 2 tools seem very close to me (a priori).
THE LATEST FEATURES OF SEO service TOOLS FOR EXCEL IN BULK:
Finally, other interesting functions:
SpinText () to generate content variants that can be combined with the insertion of dynamic variables from other fields in an Excel database.
For example, in the case where you are looking to generate personalized descriptions (random change of the Call to Action, use of synonymous variants to diversify dynamically generated content).
DomainAge () to output the age of the domain name (in days).
For example, within the framework of the analysis of the seniority of the competitors on a request and the barrier to entry due to the history.
RegexpFindOnUrl () allowing you to have personalized content scraping functions using regular expressions (with Xpath or Json equivalents according to your preferences).
For example, you may wish to retrieve the description of a list of competitor’s product sheets (scraping function not available by default because you must define in which tag the content of your competitor that you want to retrieve is found) to evaluate the similarity rate with your product sheets (see duplicate analysis functions) the average volume of words CountWords () functions.