Use Excel CONCATENATE Formula to Customize PPC Urls

WHY you need to customize keyword urls is another post. But for now I want to share a great way how to quickly customize keyword level urls for ppc campaigns. You'll need excel or your favorite spreadsheet program. We'll be using the =CONCATENATE formula. At the end of this post I have included a link to download the spreadsheet I use as examples and view the formula from google docs.

A typical situation where this technique comes in handy is when you're creating a google adwords campaign (only google has this type of keyword edit interface) and have just finished researching a list of keywords and have saved them to your campaign. At this point your keywords are saved, but they do not have keyword level urls. Without a keyword level url, the destination urls default to the default ad urls. That's fine if you're a rookie. But we need to track every click and know which keywords are getting the clicks. Ultimately we need each keyword to have a unique keyword level url with a variable called "keyword" and dynamically adjust the value of this variable so it matches each keyword.

Here's how it works.

Basically after you have created your keyword list in the keyword tab -> click Edit Keywords:

and you'll see this page:

The above shot is when the keywords do not have a unique url. The problem is that each url will default to the ad url. Within this screen you can paste in the results of the excel formula and instantly add urls for each keyword. The shot below is how the keywords look when each has it's own url. The trick is to get excel to mimic this layout - Keyword ** URL.

Here's what the spreadsheet looks like. It's 3 columns: keyword, ** and formula.

The formula CONCATENATES (combines) the values of column A with values of column B in one new cell C. The cool part is that you can also add static information to the formula so the export includes all the information. In my example this information is "http://www.tattoosite.com/landingpage.htm?site=8".

Here's the formula: ="http://www.tattoosite.com/landingpage.htm?site=8" & "&keyword=" & SUBSTITUTE(A1," ","-")

The value of column A1 will be added to the static variable name "keyword". It will replace blank spaces " ", between the keywords with dashes "-".

So the tattoo keywords added to that spreadsheet will look like this:

Once you format the first row you can drag the corner of the cell down you list of keywords and create a list of customized urls. Use this data in your weblogs, google analytics or your own internal reporting system.

You can download the spreadsheet from google docs here.