Daniel Setzermann

A Better Campaign URL Builder for UTM Tracking Links

This is a free google spreadsheet based campaign url builder for utm tracking links to be used with Google Analytics. I’ve created this tool with years of experience working in marketing, giving it all the features our team needed when it came to tracking links.

→ Grab a copy of the tool

What it offers

  • Enforced Naming Convention
  • Strict Hierarchy – a hierarchy from top channel to more specific information
  • Campaign Name that is stringed together out of different hierarchy levels (to get almost all information right out of the campaign-identifier).
  • Team transparency
  • History for later reference
  • Options to include dynamic variables (e.g. dates)
  • The possibility to include data that isn’t saved to analytics but rather stays in the sheet for later reference (e.g. who created the link?)

How to use the tool

  1. Get a google account or login using your existing account.
  2. Open the google sheet
  3. Make a copy of it (File -> Make a copy)
  4. Rename the file to your liking
  5. Tap one of the buttons (e.g. “Get user”)
  6. Give the necessary permissions to the script (If you do find that shady, you could copy all the code in the script from the end of the article)
  7. Start using it

Setup your tracking tree

The provided google sheet has a base-setup included that fits an online marketing case. It can easily be expanded and altered. Here’s a short explanation of the top level channels I use.

  • SMM Social Media Marketing
  • SEA Search Engine Advertising
  • EXM External Marketing – Paid Marketing provided by external partners, e.g. a paid blog advertorial or an advert in a magazine.
  • DIS Display Advertising – Display Advertising e.g. through banner ads.
  • CRM Customer Relationship Marketing – All efforts to keep your retention low, e.g. sending out triggered emails.
  • AFF Affiliate Marketing
  • REF Referral Unpaid Traffic from external sources e.g. Platforms like Reddit.

Think of all your Main Traffic sources as a Tree with lot’s of branches. One set of main branches is listed above, this could vary for your business. Starting from each main branch are smaller branches – e.g. sub-channels. E.g. for Social Media Marketing there are different Social Media Platforms that you could use e.g. Facebook, Twitter, Pinterest …

Build your own tracking tree and alter the excel sheet to your special set of main traffic sources.

If you are happy with the tracking options I supplied you could probably get started right away. If not, see the FAQ.

What is a UTM Tracking Link for Google Analytics

By adding utm campaign parameters to the URLs you can tag all visitors that enter your site via this URL. The information in the tracking tags ends up in Google Analytics and makes it a lot easier to measure the profitability of each of your main traffic sources. Example:  If you have two link-posts on facebook without utm parameters attached to each URL you won’t be able to see which of your posts was more successful within Google Analytics. Google Analytics would only “see” that there is a bunch of traffic from facebook but not those two posts you posted.

An example of a complete tracking link looks like this:

http://technicalmarketing.guide/?utm_source=ref&utm_medium=reddit&utm_campaign=ref_reddit_utmsheet_2017081&utm_term=text-post&utm_content=none

There are 5 parameters you can add to your URLs:

  • utm_source This is the first level of your utm tracking links. Use it to specify the top level channel of the campaign. (e.g. SMM for Social Media Marketing)
  • utm_medium This is the second level of your tracking parameters. Use it to specify specify a sub-channel of your first level channel (e.g. Facebook in the case you had Social Media Marketing as first level channel)
  • utm_campaign This is your campaign parameter – It should be unique and show as much information as possible about the campaign. It makes sense to combine source, medium and even a date with a free input campaign identifier.
  • utm_term This is the fourth level of your tracking link, it is optional. Use it to specify the kind of advertisement you used in this campaign (e.g. Link-Post in the Facebook-Example)
  • utm_content The last parameter, also optional. Leave it empty if you don’t need it or use it to split your campaign e.g. for an A/B-Test or add another information that didn’t fit the definition of the other parameters.

FAQ

  • Editing the options
 If you want to change the options you’ll have to adjust the “tracking tree” in the according tab of the sheet. Just be sure to provide the options exactly like I did in my example – you’ll see quickly if it works or not.
  • Adding more input fields
 First of all add all the fields you want to add and “design” the sheet to your liking. After that the button “Save to Database” will probably fail. Head over to Tools > Script Editor and correct the script. It should be pretty self-explanatory – if not, ask a friend who knows a bit of coding.
  • Some characters get deleted/changed in the tracking link
 Yes, that’s totally on purpose. I just don’t want some characters to enter my analytics data. You can change those unwanted characters in the settings tab.
  • I don’t like the time format in the URL 
 Just head over to the settings tab and change it according to the formatting options of the help link provided.
  • I don’t like the campaign identifier
 This is also adjustable in the settings tab. It might take a while until you figure everything out but you can totally configure the identifier to your liking
  • What is save_values and why does it ask for permission to edit my sheets?
 In order to be able to save the data to the database tab there is a little script at work to make that possible. Just head over to Tools > Script Editor to check it out. There is no shady stuff going on – nobody other than you sees your data.


The Code

If you don’t trust the script that I wrote just copy this code to your script editor within google sheets. No shady things going on here.

function save_values() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // ss is now the spreadsheet the script is associated with
  var sheet = ss.getSheets()[0]; // sheets are counted starting from 0
  // sheet is the first worksheet in the spreadsheet

  // define all the values you want to save to the database
  var time_start = sheet.getRange("D29").getValue();
  var creator = sheet.getRange("D30").getValue();
  var website_url = sheet.getRange("D31").getValue();
  var utm_source = sheet.getRange("D24").getValue();
  var utm_medium = sheet.getRange("D25").getValue();
  var utm_campaign = sheet.getRange("D26").getValue();
  var utm_term = sheet.getRange("D27").getValue();
  var utm_content = sheet.getRange("D28").getValue();

  var d = new Date();
  var timeStamp = d.getTime();
  timeStamp = timeStamp/86400000 + 25569

  // build your array – all fields from above should be listed here
  var values = [
  [ timeStamp, utm_source, utm_medium, utm_campaign, utm_term, utm_content, time_start, creator, website_url ]
  ];

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  // if you rename your database sheet, change the name here accordingly
  SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Database'));

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values_2 = range.getValues();
  var row = 0;
  for (var row=0; row<values_2.length; row++) {
if (!values_2[row].join("")) break;
  }

  // if you add more values above be sure to expand the reference here as well. The column count has to match the count of different values above.
  var cell = sheet.getRange("A1:I1");
  var range = cell.offset(row, 0);

  range.setValues(values);
}

function getuser() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet()
var email = Session.getActiveUser().getEmail();
sheet.getRange('E11').setValue(email);
}


// This function ensures that the values that can be altered by the user are URL compatible
function encode(value) {
  return encodeURIComponent(value);
}

function onOpen() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  var sheet = spreadsheet.getActiveSheet()

  // gets the current date to quickly set the campaign start. Make sure that the reference matches your sheet.
  var d = new Date();
  var timeStamp = d.getTime();  // Number of ms since Jan 1, 1970
  timeStamp = timeStamp/86400000 + 25569
  sheet.getRange('C6').setValue(timeStamp);
}

In

,