Generating destination URLs can be a pain. Depending on your tracking requirements, a destination URL can be as simple as copying your display URL… or it can stretch on in an unending parade of UTM parameters, category IDs, and referral identifiers. Today, we’re going to talk about a few easy tricks you can use to streamline your own URL generation efforts.
This is a pretty timely post for me, as we’ve been doing some large-scale keyword implementation for one of our clients. One of our requirements is to generate unique keyword-level destination URLs for each of them. With hundreds of thousands of keywords, that can be a daunting proposition – unless you make use of these simple steps with Excel.
Keep a master list of all unique identifiers for each campaign, ad group and/or keyword.
For example – if you have location-specific destination URLs, keeping a list of your campaigns and their corresponding locations (in URL form) can be invaluable – it’ll allow you to find them, in a usable form, in a matter of seconds with a simple VLOOKUP.
In the example below, we’ve got six ad groups selling six different products. As such, each ad group needs to go to it’s corresponding product page. To make this process as painless as possible, we’ve created the following list:
Use Excel formulas to help generate your required tracking parameters.
So we have our master list from above, but how will this assist us in a practical sense?
To answer that question, we go to our hypothetical keyword list:
Using our master list, and the above information, we have all the tools we need to generate our unique destination URLs in a matter of minutes – no copying or pasting required!
We have four missing fields, so we’ll take them one-by-one.
URL: We can generate each product-specific root URL by cross-referencing our ad groups with root URLs on our master list. This is done with the VLOOKUP function. It’ll look like so:
The only downside is that your ad group titles *have* to match up across each sheet – capitalization, spelling, and punctuation included. Otherwise, VLOOKUP won’t identify the correct ad group, leaving you with a load of “#N/A!” URLs to fix.
With our root URLs handled, we can turn to our tracking tags. For this example, we’ll stick with Analytics UTM tags.
Note: This system is also applicable to other tracking parameters – I’ve used it regularly for keywords integrating with both Kenshoo and Salesforce. It just takes a few extra master lists!
In our hypothetical keyword list, we have source and medium already handled. Our two variables follow the “utm_campaign=” and “utm_term=” fields. As URLs don’t generally play well with spaces, we can’t just use the campaigns and keywords as-is. To populate those fields, you can make use of the Substitute formula.
In the above formula, we’re replacing all spaces with a “+” symbol. You also have the option of using an underscore, or removing the spaces altogether.
Alternatively, you could just use Find and Replace, now that I think about it.
One caveat: if you make use of non-standard characters in your campaign naming convention, you’ll need to get rid of those anyway. I’ve inherited some campaigns that make use of ampersands in their name, and that has tended to break stuff unless I remove it.
Once you have both your campaign and keyword parameters handled, your sheet should look like so:
So with these two formulas in place, we’re ready for the final step in this process.
Concatenate!
If you didn’t already know, Concatenate is a handy tool that allows you to combine two fields of text in to one. You can add additional text in the middle, or just add it as is – which is what we’ll do here. We’ll be concatenating every field in a row in the following formula:
=CONCATENATE(G2,H2,I2,J2,K2)
Doing so allows us to generate fully functional (albeit fictional) destination URLs that are unique to each keyword:
Don’t let the simplistic example fool you, though: you can do some crazy stuff with this. We’ve been able to generate millions of unique destination URLs with each of the following requirements:
- Unique geographic locations (done with VLOOKUP at the campaign level)
- Ad group-specific category ID’s (done with VLOOKUP of a partial segment of the ad group title, segmented via Text to Columns)
- Keyword-specific parameters, designed to interact with the site search functionality (done with Text to Columns, VLOOKUP, and/or the Substitute function)
All concatenated together (or VLOOKUP’d) in a matter of minutes. Once you get comfortable enough with these formulas, you can even automate this process by creating your own URL builders!
What about you, PPC Heroes and Heroines? Any amazing tips and tricks you’ve found to help ease your Destination URL pain? Let us know in the comments and, as always, thanks for reading!
