A Crash Course in Excel URL Generation
February 28, 2014
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.
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:
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!
Browse By Category
A Buyer Persona Based Approach to Mapping Your Customer Journey
Explore how a persona-based approach is used to enhance customer experience, improve targeting accuracy, and harness behavioral analytics to their business objectives.
The Rise of The No-Click Search Results
Searching for answers and data has never been easier thanks to the help of search engines. Here we’ll discuss the effect that no-click cost is having on ppc, including the growing popularity of voice search.
Becoming Allies With The Sales Team: A Lead Gen Solution
Collaborating with your client’s sales team can provide a treasure trove of insights. Explore how PPC tactics are utilized to help shorten the sales cycle and improve close rates!
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
The Opportunities Tab: AdWords vs Bing, A Friendly Match-Up
Dive into AdWords and Bing opportunities tabs and see how they compare. This includes accessibility, features, and performance insights.
How to Drive Calls that Actually Convert
Our experts are here to explain how gaining call tracking data can help you close the gap in your customer journey and tailor their experience so they actually convert.
New Facebook Analytics Updates from F8
Learn about journeys and automated insights, new tools announced at Facebook's F8 conference for the Facebook Analytics tool.