Google Analytics campaign URL builder using Google Docs

If you’re using Google Analytics and you’re not taking advantage of its built-in campaign tracking, you’re missing out on important insights. Campaign tracking uses URL query strings to determine where your traffic is coming from specifically. URL query strings are the funny “?something=this&somethingelse=that” text that you see on the end of URLs (try a Google search and look in your address bar). By using this tracking method, you can understand your traffic better and learn what’s working and what isn’t. It’s particularly beneficial for people who are promoting their site across several different channels like email, Twitter, and Google Adwords. Here is a little more in-depth description from Google.

The most annoying part about using tracking codes, however, is creating the URL. You need to append at least two (and up to five) query strings to your URL to use this function in Analytics and making this happen while tracking what codes you’re actually using is frustrating enough to make you rethink this whole thing in the first place. Google provides a cute little tool to help you but it’s still a pain in the ass.

Google Docs to the rescue! Using similar code as my simple Google Docs CMS, I created a script that parses a spreadsheet full of tracking codes and turn them into usable URLs that you can then copy and paste. It makes it easy to keep track of the campaigns you have while quickly creating error-free URLs. See the Google Docs UTM appender in action.

Download the UTM URL builder (updated 8/31/2010)
What you’ll need:

1. The file from the big, green link above
2. A Google Docs account (it’s free, you can sign up here)
3. Access to a web server or a local host that can run a PHP program
4. A site using Google Analytics

Step 1 – Create a new Google Docs Spreadsheet

Easy enough… go to docs.google.com and log in. Click the Create New button on the top left and select “Spreadsheet.”

1. When the spreadsheet loads, look for a Share button on the top right. If it prompts you to save the document, type in a nice descriptive name, click OK, and click Share again.
2. In the menu that appears, select “Publish as a web page.”

1. Select “Sheet1″ from the top drop-down, then click Start Publishing.
2. Under “Get a link to the published data,” select “RSS” from the first drop down then click the Cells radio button.
3. In the text box at the bottom, you should have a URL like “http://spreadsheets.google.com/feeds/cells/tFjKofPSUgX5PSPJhEuH7Dw/od6/public/basic?alt=rss.” Highlight the whole thing and copy it, then click Close on the window.

Step 2 – Add your Google Doc link to the script and upload

The config.txt file that this package comes with points to the URL of the feed that creates the sample page above. Simple open config.txt and paste the URL you got in Step 1 in between the quotes (replacing what’s there).

rss feed “[paste your feed here]“

If the link is correct, the script should be ready to display the Google Doc. Unless you have a server instance installed locally, you’ll need to upload this script to a public web server. If you have your own website, just upload this file to any public folder. If you’re confused, email your IT department or your tech-savvy niece/nephew.

Once the file is uploaded, you’ll want to test it out to make sure everything is working. Go to the file you uploaded (http://[your domain]/[the path to the folder you used]/link-append-util.php) and you should see “UTM coded URLs” at the top. If you see “Invalid feed!” then the link to the Google Doc was not made. Make sure the URL is correct and in between the tick marks after $feedName=.

Step 3 – Update your spreadsheet with the tracking codes

Once the script is functioning, you’re ready to input your URLs and tracking codes.

The spreadsheet needs to by laid out properly to make sure that the script can read and display the information in the right order. Here are the rules and you can see a sample of a functioning Google Doc here (it’s the one I use to create the example here).

• Cells A1, B1, C1, D1, and E1 will not be read by the script.
• Cells F1, G1, H1, and I1 determine the name of 4 additional custom references that can be added.
• Column A after row 1 is the name of the link. This can be anything.
• Column B needs to be a valid URL without pre-existing query strings (meaning that there can’t already be a “?” in it)
• Column C is your utm_source code
• Column D is your utm_medium code
• Column E is your utm_campaign code
• Columns F through I can be any other reference code you’d like. Again, the name will be taken from the column header. Leave these blank if you don’t want to use them

A few things to keep in mind:

• The source, medium, and campaign codes are required meaning that they are always pulled. If you leave one blank, it messes up the rest so make sure you always have a value in there. If enough people want to eliminate this requirement, I’m happy to offer an alternative, just comment below.
• If you’re not using the custom reference codes, you can leave them blank. If you are using them and each URL will have one, then you’re good to go. But if some URLs need one, others need another, make sure to put an “x” where you don’t need one. For example, if the first URL is using reference2 but not reference1 and the second URL is using reference1 but not reference2, make sure there is an “x” under reference1 for the first URL and under reference2 for the second. Hopefully that makes sense. Again, comment if you’re confused!

Step 4 – Reload the script and fire away

Once your codes are loaded, refresh the script and you’ll see them listed nicely in a row. In order for each one:

1. The first thing listed is the name from column A for that link.
2. Next you see “Paste into form field or if in doubt.” This is the not-validated version of the URL . This should work everywhere and is the safest choice. If you’re not sure what the hell I’m talking about, use this one. There is a “test it” link that tries it out.
3. Next is a field with the URL. Just click once to highlight, copy it, and paste it where you need it.
4. After that you’ll see “Paste into code.” Use this for HTML and PHP that is not validated (i.e. within an application file or static page).
5. The validated version of the URL appears in the text field. Click to highlight, etc.

Step 5 – Comment, Share, or Tweet if you like it!

That’s all I ask!

Please leave comments, questions, and suggestions below. I hope this saves you as much time as it save me!

Share this article:

Will you follow me on Twitter? You can also subscribe to my posts via RSS here or via email here.