January 5, 2011
For most paid search marketers, using some sort of analytics tracking program is the difference between a highly successful PPC campaign versus one that returns a low ROI. As more and more companies take advantage of analytics programs such as Google Analytics, you’re likely to see more and more URLs with various tracking parameters attached the end of any given URL.
When it comes to PPC management and tracking, paid search marketers may use a PPC Management Tool or Google Analytics to measure how effective their efforts are. Many PPC Management Tools will automatically append each destination URL with their own tracking parameters. If you’re using Google Analytics to track Bing/Yahoo! PPC efforts, you might use the Google URL Builder to create proper URL tracking parameters so that you can see your results in Google Analytics. In some cases, you might use both your PPC Management AND Google Analytics URL tracking parameters.
While it’s fairly easy to do both, what about removing those URL parameters when you’re no longer in need of them, or moving to a different system? Recently, I was presented with a challenge where I was asked to strip out all the old legacy parameters and return them to their original “naked” stage. For a small account, this could probably done manually without too much effort. If all the URLs are simple and the parameters are the same, a simple “find and replace” in either AdWords Desktop Editor or Microsoft adCenter Desktop Editor makes that task fairly simple.
However in this case, each tracking parameter was unique to each ad destination URL, and there were almost 6000 different ads to change. Manually doing this was not an option, nor would even the “advanced URL changes” in Google AdWords Editor work. So what to do now?
The process is almost identical for both Google and Bing/Yahoo! using their respective desktop editors, so we’ll just use Google for this example.
Step 1: Export your entire account to a csv file:
Step 2: Once you have exported your account to a csv file, open it with Microsoft Excel and find the destination URL column. For my example, it’s column V, but yours may vary. Create a new column just to the right of it.
Step 3: Starting with the first row of your destination URLS in column V, create a formula in Column W just to the right. Remember, this is the blank column you just created. Paste this formula into the cell:
=LEFT(V2,FIND(“?”,V2,1) – 1)
Of course, your column name might be different, so change the V2, etc to your specifications.
Step 4: Hit “enter” and drag the formula down your spreadsheet.
Step 5: Copy your new column with the correct URLs and copy it over the old column. But MAKE SURE to “paste special” and select “text” or “value”, otherwise, it will just copy over the formula, which will give you an error.
Step 6: Delete the formula column once you’ve verified that all the newly stripped URLs look correct and save your spreadsheet.
Step 7: Import your csv file back into Google AdWords and double check your work! If it doesn’t look right, you can always back out and try it again before posting it live to your Google AdWords account.
Written by Nick Perez.