They said it couldn’t be done, a spreadsheet that quickly creates campaign URLs for Google Analytics UTM tracking AND shortens them with Bit.ly (Note-we aren’t really sure who ‘they’ are, or frankly if they really said this). After creating hundreds of campaign URLs with different UTM builders and spreadsheets we had a huge issue with Bit.ly’s API that broke when we tried to shorten too many URLs in the same spreadsheet. We contacted their team and they said you can’t have over X # of requests per session through the API – basically saying ‘stop that because we’re not fixing it’. So we hacked a solution in our Google Spreadsheet for one of our clients, that allowed them to quickly create campaign URLs and then shorten them with your Bit.ly API without running into the limit. Since we think that secrets secret are no fun, we’ve shared the spreadsheet and tips for successful campaign URL creation.
First, how to use the UTM builder spreadsheet
Ok, so it isn’t the easiest thing in the world but it is SOOOO worth it once you see how much time it will save you from creating a URL, saving it, then going to Bit.ly and shortening it.
- Add your Bit.ly API in the AdminBitly sheet. Here is where we got the Bit.ly code for this spreadsheet and here is where you can find you Bit.ly access code. Note that this requires that you have a Bit.ly account – but you probably already know that if you’re reading this… We have Whole Whale’s open token there as a placeholder to show it works.
- Start making campaign URLs. Make a note in the description and date column to help you remember some details about the URL for your records.
- Columns C-G allow you to add the details about the campaign, similar to the way Google’s UTM builder lets you create URLs, and of course the destination URL you want traffic to go to goes in column H.
- Generate the short URL. Column J will show FALSE until you delete the contents of column L which then triggers the API call to Bit.ly. This is how we avoid the request limit that breaks the Bit.ly API with larger spreadsheets.
- Save the URL. Copy over the newly generated Bit.ly URL to the corresponding column L to save the URL in a way that won’t continue to trigger the API call to Bit.ly.
- Rinse, lather and repeat! The column K will work off and on because that does run into API call limits. But pro tip —you can add a “+” to the end of any Bit.ly URL to get the stats on it.
Get the UTM builder on Whole Whale University, and explore more tips on best practices surrounding campaign URL tagging.
Template
UTM Builder & Shortener
This FREE spreadsheet will help you quickly create campaign URLs for Google Analytics AND shorten them with Bit.ly!