How To Quickly Generate Google Analytics Tracking URLs with Excel

Google Analytics is a fantastic system for tracking and analyzing your website traffic and promotional campaigns. One of the key features that we can use is the option to generate special tracking links that provide us more data for measuring traffic sources and conversions. The problem is these tracking URLs are complicated to put together and it can often be difficult to keep a record of which you have used, when and where. Wouldn't it be great if we could generate and keep a record of our tracking links in a spreadsheet? Well, now we can!

Generating Your GA Tracking Links

Google helpfully provides us a list of parameters here:

http://www.google.com/support/analytics/bin/answer.py?hl=en&answer=55578

We can replicate these fields in our spreadsheet, and then we can combine the values we enter in the correct format to create our tracking URL.

So first we have the target website address that we want the visitor to go to, then the Campaign Source, which is essentially where the visitor will be coming from. Next we have the medium, so in my case it will be an advert, an email or a social media click such as a status update. You can have a campaign term, for example if you bidding or optimizing for keywords, Content in case you are split testing or using different creative choices, and a name for your Campaign such as "My Awesome New Years Promotion" and so on.

The User Defined Function

Excel has various string manipulation features we could use in a formula, but to keep the spreadsheet clean and easy to maintain we can create a UDF instead and call it thus:

Our UDF is extremely simple. It takes the parameters and checks to see which have been used before concatenating a result string and returning it.

Function GenerateURL(strURL As String, strSource As String, strMedium As String, strTerm As String, strContent As String, strName As String) If strURL <> "" And strSource <> "" And strMedium <> "" And strTerm <> "" And strContent <> "" And strName <> "" Then GenerateURL = strURL & "?utm_source=" & strSource & "&utm_medium=" & strMedium & "&utm_term=" & strTerm & "&utm_content=" _ & strContent & "&utm_campaign=" & strName Else If strURL <> "" And strSource <> "" And strMedium <> "" And strTerm = "" And strContent = "" And strName <> "" Then GenerateURL = strURL & "?utm_source=" & strSource & "&utm_medium=" & strMedium & "&utm_campaign=" & strName Else If strURL <> "" And strSource <> "" And strMedium <> "" And strTerm <> "" And strContent = "" And strName <> "" Then GenerateURL = strURL & "?utm_source=" & strSource & "&utm_medium=" & strMedium & "&utm_term=" & strTerm & "&utm_campaign=" & strName Else If strURL <> "" And strSource <> "" And strMedium <> "" And strTerm = "" And strContent <> "" And strName <> "" Then GenerateURL = strURL & "?utm_source=" & strSource & "&utm_medium=" & strMedium & "&utm_content=" & strContent & "&utm_campaign=" & strName Else MsgBox "Required Campaign Missing!", vbOKOnly End If End If End If End If End Function

Summary

By using Excel, not only can we generate large numbers of these tracking links, we can also keep a record of the links and apply other data, perhaps starting with a date stamp, the conversions and click-throughs, and so on.

Perhaps you could connect this spreadsheet to source data from your content or campaign management system?

Please let us know if you use something like this and how it works out for you...