Building URLs is a common pain point for marketers of all experience levels. Tagging isn’t something you do every day, making it easy to miss out on common tips and tricks. Without knowing the standard tools, you are often left with the daunting process of building URLs piece by piece in the Google URL Builder or one by one in the interface. I wouldn’t wish that on anybody.
Thankfully, we can take advantage of Excel or your choice of spreadsheet to efficiently implement new URL tags. You are also welcome to use the interface as well, but I’d recommend getting nice and cozy with your spreadsheet.
Text To Columns
The text to columns tool will immediately blow away unfamiliar users. If you haven’t learned about it in some other context, you’d never think to look for it at all. While crucial for breaking apart CSV files, it is often overlooked in spreadsheet files already segmented by rows and columns. There just isn’t a reason to use it until you urgently need it. Text to columns makes it simple to strip all of your URL tags from a link, preserving the link and removing everything else with one click.
Step 1: Select your column and open the wizard.
Step 2: Choose delimited, the default.
Step 3: Choose your delimiter. You will usually start with “?”
The most common method you’ll use is breaking the string on the “?” that divides the actual URL and the additional tags. Note that using text to columns will replace any columns to the right of the original. Consider moving your URL column to the rightmost edge or inserting a buffer column.
It is as simple as that. You could get a little more creative and break the string on the “&” as well to break apart easy parameter into new columns. Next time you run across an account with completely broken parameters, you can quickly clean them up and start fresh.
Find And Replace
The easiest way to change your URL values is through find and replace. This is a tried and true favorite that looks for a string and replaces it with another. For example, need to change utm_source from Google to Bing? Simply press CTRL+F/CMD+F and open the dialog box. Check off any matching boxes and you are good to go.
Be careful about matching to certain words. Replacing red for blue could turn Reddit into Bluedit if you are not careful. Doing replacements one by one can be tedious, but thankfully we can speed that up as well with the substitute formula.
Substitute
Find and replacing piece by piece is maddening. It is great for a few replacements but once you have to do more than two or three, tedium sets in and the errors become too easy to make.
In these situations, substitute is the better tool for the job. Rather than typing out your find and replace strings you can consolidate and rely on cell references to keep everything tidy. You can also nest formulas to make multiple changes at once and give your pinky a break from bashing CTRL or CMD.
In this example, we’ll encode a two special characters, a set of square brackets. We want the brackets in our campaign name to carry over to our tracking system so we’ll encode them to make sure it is interpreted correctly.
In this example, we will look for the two square brackets can swap them for “%5B” and “%5D.” Rather than going through multiple clicks through find and replace, we’ve knocked it all out in one fell swoop. If you need a quick reference, you should bookmark this Wikipedia page to check your codes.
Concatenate/&
Concatenate and ampersands are vital in putting all the pieces back together. Both combine two or more strings into one. This allows you to take multiple columns with URLs and tags and combine them into a ready to upload URL.
In the example above, we’ve broken down the URL parameters into separate columns for readability and used the “&” to put them all back together. You’ll notice quite a few “&” symbols in here. The plain “&” symbol is used to join strings while the “&” is inserted as a string value to tie the parameters together in the URL. Both options are almost identical. Use whichever one you are more comfortable with. I find myself using the ampersand more often, but it is only personal preference.
Vlookup/ Index Match
What would an excel post be without mentioning a lookup? While this won’t build your URL, you can make use of the lookups to insert additional data such as unique IDs and special codes. Vlookup and Index/Match make it simple to reference data from another source. This makes them perfect for specific ID codes you use in your tracking process.
Imagine you have a special ID for French travel. If you used a placeholder in the URL such as travel_code= [replace]. You could filter through your URLs to only show the French campaigns, and CTRL+F/CMD+F your way through it for every location.
Alternatively, you can use a custom identifier, label, or campaign name as a lookup reference. You can then insert your function into your concatenate function, dynamically inserting the ID for every ad.
This will make quality assurance checks much simpler and make it easy to implement last minute changes before launch. Get word from the client that you’ll be using 198723 instead of 109283? Rather than rebuilding you can simply swap the values in your table and update the ads.
