All of PPC is very detail-intensive. For me, tracking codes and tagging URLs can be one of the most time consuming details. Make a mistake and you have no data, the URL doesn’t work or sometimes a combination of the two. Add too many tracking codes together and your data ends up murky or unusable. I think many PPC pros would agree it can be a necessary evil depending on the client and capabilities.
For those clients who haven’t graduated to dynamic tracking, UTM parameters are likely the way that you track your online marketing efforts. Several of my clients use UTM parameters exclusively, but one in particular can be a hot mess each and every time we change out the landing pages or ad copy. That’s because this particular client has many campaigns with many, many ad groups and they want all of our efforts tracked at the keyword level. We’re talking thousands of ads; so many that sometimes AdWords Editor revolts and just shuts down completely. I’ve known for a long time that there must be an easier way to do this, but I haven’t had the time to figure out how that would be possible. I knew that you can use the concatenate function to build out URLs, but the keyword-level portion really threw a wrench in that plan for this client.
Then I realized: you just have to use more Excel functions than concatenate! Let me show you how with screenshots.
Start With a List of Ad Groups or Keywords
You’ll want a list of either ad groups or keywords in the first column of Excel that you can convert into the UTM term. Side note for future understanding: The way I manage this particular client, each keyword is its own ad group, minus plural variations and slight variations on word order. In my screenshots, you’ll see ad groups as the starting portion of this but you could also use a list of keywords. You’ll just want to make sure to use the “remove duplicates” feature in Excel so you only have one instance of each before beginning.
For the sake of the season, I have chosen the Big Ten conference schools as my example (Go Boilers!).
Record a Macro
This is the key! If you record this the first time you do this, you can use it over and over by just starting with a list of keywords or ad groups in A column. You might have to do a separate spreadsheet for each client depending on how detailed you get later, but this can really help speed up the process.
You can find the “Record Macro” option on the Developer tab. Click that and name your Macro for easy reference later.
If you’re unfamiliar with Macros, there’s a lot of how-to tutorials out there, so I would recommend checking one of those out if this is confusing.
Use the Lower Function
In the column beside your starting words, use the lower function and apply all the way down. This will make all of your starting words lowercase.
Paste the Values
After you have the lowercase words in the B column, copy and paste the values into the C column. It’s important to make sure you right click and select “Paste Values”, otherwise it will copy over the formula which you don’t want.
Use the Find/Replace Function
Once you have your lowercase words, free of any formulas, hit Ctrl+F to find and remove the spaces between the words. This will make keywords or ad groups with more than one word into one string of text, which is key for inserting into your URLs.
After:
My client likes to use shorthand for certain keywords in their UTM parameters. If yours don’t, or if you’re fine with just having all of those characters, since there’s not really a character limit on UTM parameters, you can skip this next step. If you want to keep yours short and sweet, stay with me. It will be worth it!
Use the find/replace function to shorten your words. This can be as much or as little as you want. In my example, I just shortened university to univ. Since you’re recording this as a Macro, you can put your effort in once into cutting down the words and reap the benefits of just applying the changes the next time.
Use the Concatenate Function
In the next column, simply add the URL language (everything for the UTM term minus the actual keyword itself). Simply the “&utm_term=” portion part of the URL and drag down the column. In the next blank column, use the concatenate function to join your shortened ad group/keyword list with the utm_term.
It would look like this: =concatenate(D2,C2)
It’s important to get these in the correct order. This function will not add any spaces between the two cells, which is exactly what you want.
Put It All Together… With Concatenate Again
Once this column is finished, I typically stop recording my Macro because forming the remainder of the URLs will just be dragging and using the concatenate function again.
The next 4 columns will be the landing page, the UTM source, UTM Term and UTM campaign (and UTM channel if you need it). You’ll want to make sure you start with the ‘/?’ that starts off the UTM parameters in your first UTM parameter column.
After that, you’ll want to make sure you include the ampersands and underscores. Just remember – include everything in the cells that you would need for that portion of the URL. You can mirror the UTM term portion over by just mirroring it with the “=(cell)” formula. Below is how the spreadsheet should look if you have followed all of my instructions.
Now for the final step in creating your URLs. Simply use the concatenate function to join all of the cells together. It would look like this: =concatenate(F2,G2,H2,I2)
Your final product will look like the URLs below:
If you started with a list of ad groups, your URL will still be associated with your original ad group column, meaning you can copy and paste the values into another Excel workbook or spreadsheet to easily write ads.
If you recorded a Macro, you’ll want to make sure that you save your file as Macro-enabled, otherwise you’ll have to redo it next time. To use your Macro again, simply start with your basic list of keywords or ad group names in the A column and then select your Macro from the Developer column.
I hope this helps some of you with tagging your URLs at the keyword level!
How do you tag your URLs with UTM parameters? We’d love to hear your suggestions in the comments!
