Brad Geddes / PPC Geek
Official AdWords Seminar Leader.
Author of Advanced Google AdWords.
Co-Founder, Adalysis.
(312) 884-9017
Brad Geddes's Theories on Marketing Step by Step Guide to Checking your Entire PPC Account...

Step by Step Guide to Checking your Entire PPC Account for Broken Links

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.

Check Server Headers Tool - HTTP Status Codes Checker_1232112628826

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’

1162009_82417 AM

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:

1162009_84510 AM

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

1162009_84735 AM

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.

1162009_90110 AM

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.

1162009_92823 AM

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.

No Comments

  1. David Szetela
    January 21, 2009 at 9:08 pm · Reply

    Brilliant, Brad – as usual.

  2. Anne H
    January 22, 2009 at 12:32 am · Reply

    Great article. There’s a nice Excel add-on you can get called ASAP Utilities (asap-utilities.com) that will convert addresses to hyperlinks. It has 300 functions and that’s just one. There’s a free version and paid version.

  3. tonyr
    January 10, 2013 at 11:21 am · Reply

    Super Brad – thank you. I had earlier today sent my destination URLs to our IT guy who uses a tool called Dead Link Checker to perform a similar exercise.
    Its a linux tool so not really accessible to non linux users. I will be following your instructions and scheduling this task to do it myself regularly as each time its run there is always money saved!!

  4. ppcproz
    September 22, 2013 at 3:35 am · Reply

    Thanks, great article. Shocked that a big advertiser would be “happy” with “only 30%” broken links, OMG.

    The convertohtml macro I think would be a bit advanced for most people though, it was hard for me, I have to admit. A simple and elegant solution could be as follows: 1. download last 30 days online or in Adwords Editor. 2. copy and paste into excel 3. remove all columns except dest url 4. data tab / remove duplicates 5. copy dest urls 5. email yourself this list 6. open each dest url from your gmail for example, a manual solution of sorts.

    In the end i preferred to use xenu. free download at http://download.cnet.com/Xenu-s-Link-Sleuth/3001-10248_4-10020826.html?spi=1b585754d4d244f35facf740c406605e&dlm=0
    be sure to change options on xenu: 1. uncheck the check external urls. 2. options: change maximum depth to 0 3. not sure what parallel threads is, but i changed it to 1. 4. check treat redirects as errors (we should not be sending traffic to redirect pages. not as bad as a broken page, but I do believe this affects your usage tracking in GA) 5. copy your dest urls, as i described before but this time paste them into notepad, and save as .txt file. 6. in xenu… in menu.. file / check url (test) open this txt file and watch it work its magic.
    7.export to tab delimited in excel and look for the errors and apply corrections.

    In addition, xenu is a great tool for checking duplicate metatitles and metadescriptions.

    I ran this today on a big account and found 4 dest urls that had 202 errors, permanent redirects. Fortunately, these were redirecting to the correct urls.

    From now on, I will be using this workflow for checking dest urls once a month.

    Thanks for a great article and reminder of the importance of this vital task.

  5. Jordan McClements
    November 14, 2013 at 3:51 am · Reply

    Thanks for the handy article.

    You would think that since Google alce so much emphasis on user experience, that they would automatically pause ads that throw 404 errors. Obviously they don’t want to do themselves out of millions of dollars (I can picture the board meeting where this was discussed).

  6. Jordan McClements
    November 14, 2013 at 3:51 am · Reply

    Sorry – I can’t spell… I meant ‘place’ not ‘alce’.

  7. Stan
    September 21, 2014 at 1:54 pm · Reply

    Thanks for sharing your knowledge as usual, Brad

    Can we get the same task done by running a destination URL report, and then crawl the URL list with Screaming Frog program?

    Best,
    Stan

    • brad
      October 4, 2014 at 8:44 am · Reply

      Yep, you can use xenu, screaming frog, the CK tools, etc.

      If the program lets you import a file and then gives you status codes, you can use it to check your URLs.

      -brad

Leave a Reply