In my last post I spoke about how much I rely on Excel to effectively and efficiently run paid search campaigns, and gave an example of one particular piece of actionable analysis. Today I want to focus specifically on how you can leverage the SeoTools for Excel add-in: Not for SEO, but for PPC.

Introducing Niels Bosma’s SeoTools

If you aren’t aware of this fantastic add-in, then I’m delighted to be able to introduce you to an essential addition to your search marketing tool kit. The tool itself was developed by developer named Niels Bosma, based in Sweden, and essentially extends the powers of Excel by allowing it to reach out to the web and grab useful pieces of information.

From a user perspective it does this by arming you with a range of new formulas. For example, once installed you will find that =HtmlTile() function has appeared and will allow you to grab the title for the url in the cell referenced:

Further functions include, but are not limited to:

• LinkCount
• HtmlCanonical
• HtmlMetaDescription
• CountWords
• FindDuplicateContent

For more information and to download and install SeoTools, visit the project website

SeoTools? What’s that got to do with PPC?

Feeling a bit left out by all this SEO love, I soon discovered that there are plenty of useful ways I could use the add-in to help me with the task of managing and optimising paid search campaigns, so have since integrated it quite heavily into my work flow. Today I wanted to share a couple of the techniques and tools that I have built out since starting to work with SeoTools, which hopefully provide a starting point for your own adventures.

Example 1: Finding & Correcting Destination URL Errors

Websites are not static in anyway, especially when it comes to pages and urls – url structures and formats can change, pages can be retired, pages can be redirected by 3xx rules, pages can drop off the face of the earth for any number of reasons! As a PPC account manager it is vital that you don’t accept you will always be kept in the loop, so should always be checking that your traffic is still going to the correct pages, and most importantly, isn’t going to an error page of any sort.

This sheet takes a raw export from AdWords Editor, uses the HttpStatus() function to lookup the url, and then:

a) provides the correct url for errors (including for 301′s as, although this is not major, it’s good housekeeping to not rely on redirects) and

b) outputs a list of urls and details the error.

Most of the work is done in the hidden ‘URL lookup’ sheet – this essentially references your urls, looks up the http status first instance (to save making unnecessary server requests) and provides the correct url for redirects or highlights errors.

Example of the URL lookup sheet searching for http responses:

 To use the workbook, simply:

1) Copy the advert view from AdWords Editor and paste into sheet 1

2) On the tab ’2 – New Adverts’ adjust filter in column G to exclude ‘No Change’ cells

3) On the last sheet 3 – Highlighted Urls, exclude cells matching ’0′ in the filter on column B

Download the full Excel workbook

Example 2: Price, Product & Stock Reference

When running an ecommerce campaign having the correct details in your adverts is crucial for ensuring a relevent experience is provided to the user (this improving your conversion rates and ROI). Again, as the marketer, it’s best not to assume that all stocking and price details are passed on to you as a priority.

Using the swiss army knife of the tools, the XpathOnUrl you can again paste in your campaign export and use it as the basis for a look-up.

What we are going to utilise is the html class or id which identifies certain bits of information – for example, if you inspect the html of an Amazon product page, you will find that prices are displayed within the class name ‘priceLarge’

So to utilise this with the XpathOnUrl, we need to find the Xpath (if you are not familiar with the language check out the wikipedia page). This can be done incredibly simply by following these steps:

 1) In Chrome, install the subtly monikered PsychoXPath plugin

2) Open the page you type you want to get the info from and highlight it

3) Right-click the highlight section and select ‘PsychoXPath > Element (Short)’

You should now have the correct element on your clipboard, so just paste this into cell B2 on the ‘Details’ worksheet, this becomes the reference for the sheet.

Example of XPath elements for Amazon product pages:

Similarly, grab the ‘in stock’ message from the site your are working with, and paste into cell B3

Once done, add an example url (one with products currently in stock) to B6 – this just gives us the exact text with which to reference stock statuses.

[Note: to ensure we are not overloading the servers by pasting in huge lists of urls, I'm using the built-in BuildHttpDownloaderConfig to create a pause between events. It will mean sheets take longer to populate, but is just good manners in my opinion]

Now it’s just a case of once again pasting in an export of your ads direct from AdWords Editor and this will give you a list of incorrect prices on price-led adverts, and also highlight any items which are out of stock.

Example of sheet output:

Download the full Excel workbook

The sheets provided are essentially simplified guides to highlight the potential of the plug-in, but should still be useful for running these tasks. Hopefully these examples will give you plenty of inspiration for creating your own custom sheets which take advantage of the excellent functionality that SeoTools adds.