Do you know if you are wasting money by sending traffic to not found (404) pages?

Over time, URLs are changed, sites are redesigned, landing pages change, and yet finding all of these broken links can be difficult. If you are sending traffic to an error page via a broken link – you’re losing money.

Here is a step-by-step guide to find all of those broken links within your AdWords account. I’m going to walk through how to do this for AdWords; this method can be adopted for any PPC account.

Step One – Make Sure Your Site Shows 404 Errors

Navigate to a page on your site that does not exist. For instance, certifiedknowledge.org/ThisPageDoesNotExist

Copy the URL. Then find a ‘Server Header Checker Tool’.

Paste the URL into the tool and check the server headers.

The ‘HTTP Status Code’ Should show 404 Not Found.

If you see a 200 OK response code, or other non-error page, you need to fix your website to display 404 errors. This will not only help in finding error pages, but is a SEO best practice.

Once you’ve verified that your website returns 404 errors, continue to step two.

Step Two – Export Your Account to Excel

The first step is exporting your entire account into an Excel file. This is easily accomplished in AdWords with the AdWords editor.

Follow these steps:

• Open your account in the editor
• Go to File > Export Spreadsheet (csv)
  • If you want to check your entire account, choose ‘Export Whole Account’
  • If you want to check just one campaign, choose ‘Export Selected Campaigns’

If you wish to check a Microsoft adCenter or Yahoo Search Marketing campaign, export those campaigns to a CSV file instead

Step Three – Change All Destination URLs to Hyperlinks

Open the file you just saved.

Column ‘T’ should be labeled ‘Destination URLs’. This column holds both keyword level and ad copy destination URLs.

Change the column T header to URLs (or any single word). The macro we’re about to run breaks if there is a space between words. As every other cell in column T should be http://something, there should not be any other spaces in the column.

Next, we need to turn all of the URLs in column T to hyperlinks. We’re going to accomplish this with a macro. Don’t be frightened by using a macro, this is pretty easy – just follow the steps.

In Excel 2007, navigate to the Developer tab and click ‘Macros. Type in “ConvertToHyperlink” and click ‘Create’.

You should now see a box to input some code:

Copy the below code and page it between “Sub ConvertToHyperlink ()’ and “End Sub”

Dim rng As Range
Set rng = Range("T1:T" & Cells _
(Rows.Count, 1).End(xlUp).Row)
'Set rng = ActiveSheet.UsedRange
For Each cell In rng
cell.Select
If cell.Value <> "" Then
If Left(cell.Value, 7) = "http://" Then
URL = cell.Value
Else
URL = "http://" + cell.Value
End If
ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:=URL, TextToDisplay:=cell.Value
End If
here:
Next

Special thanks to Yahoo Answers for helping with this code. Source Code Credit.

Now run the code. You can accomplish this in two ways.

1. Hit the play icon in the Excel Ribbon.
2. Return to the excel document
   • Click on the macros button
   • Select the macro
   • Click Options
   • Assign a shortcut key to the macro
   • Close out the boxes
   • Hit the shortcut key (cntl-what you selected)

As you hit this, in the background, everything in Column T should be converted to a hyperlink. You may see a runtime error when it’s done (essentially the macro got to a cell it couldn’t change). If the cells are now hyperlinks, ignore the error. If the cells aren’t changed to hyperlinks, odds are that you didn’t change the label in Column T to a single word. Change the label to not include spaces and try again.

The last thing to change is the copy/paste. As WordPress and browsers do strange things. You can view the text file here.

If you are running this macro for Yahoo or adCenter, there’s an easy way to change any column to hyperlinks. In the code, the third row contain ‘T1:T’ – just change those values to the row you want to change to hyperlinks. For instance, if you wanted to convert column A to hyperlinks, the code would look like:

Set rng = Range("A1:A" & Cells _

Step Four – Saving the Document to HTML

Save the document as ‘webpage’ which is a .html or .htm file for Excel.

In Excel 07, go to ‘Save As’ > Other formats > and Choose webpage.

You will most likely see an error that says some features will be incompatible with the new format. Go ahead and click ‘Yes’ and save the document as an HTML file.

Now we have a file that contains all of our PPC destination URLs. It’s time to check for errors.

Step Five – Spider Your File for Errors

The very first thing to do is make sure you have Xenu Link Sleuth on your computer. Xenu is a free spidering program. If you don’t have it, download it and install it now – it’ll only take a couple minutes.

Next, open Xenu.

Go to File > Check URL

In the next box, click on ‘local file’ and select the HTML file you just saved from Excel.

Make sure ‘’Check external links’ is checked. Now just click the ‘OK’ button and go. Depending on how large your PPC account is, Xenu may take a few minutes to a few hours (I once had it run for 19 hours getting around 850k destination URLs. The bonus? It didn’t time out.

Once Xenu finishes, you will now have a report of every URL in your PPC account and if it’s broken or OK. You will see one broken link, which is the name of Column T. You can either save the file, view the full report, or export to a tab delineated file (which can be opened in Excel).

You can click on the below picture to see a larger view. Look for ‘Not Found’ in the Status column. Every red line is a link where you’re sending traffic to broken pages – lost money.

Step Six – Stop Losing Money

If you have a website where multiple people or divisions make changes to your site – make sure you coordinate the activity. It’s very common to see traffic driven to broken pages. Don’t waste your money.

Even in companies with great communication, I’ve still seen old projects get released, prior sections of a website rolled back, etc.  All it takes is one person to make a quick change for you to start driving traffic to money-wasting pages.

Once you run this report once, you’ll see how easy it is to actually complete. While it looks complex, on a site with less than a thousand destination URLs, this entire process usually takes less than 5-10 minutes. That’s a small price of time to pay to stop wasting money.

Broken Link Finder Tool

If this seems like too much work; you can also use the Broken Link Finder Tool, which will do all this work for you. It’s part of the premium member package. You can take a free 7 day trial to see how the tools and lessons can help you improve your marketing. Take a 7 day Free Trial.