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:
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.
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…
Yoav co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software. For more Excel tips, join him on Facebook or Twitter