I built a URL builder, complete with link shortener, in Google Sheets.
Among my love for data and web analytics is my love for Spreadsheets. I’m a huge evangelist for Google Sheets, thanks to features like the =filter() and =sort() formulas.
When it comes to web tracking and URL/UTM parameters, I find two things to be really helpful:
- Organization
- Shortening
So, over time I’ve built for myself a spreadsheet that helps me build URLs and and shorten them via Bitly API integration. I decided I’d clean this up and make it a bit more user friendly to share out for others to copy, use, and iterate on.
To get started, make a copy of this Google Spreadsheet: URL Builder with Link Shortener
URL Builder
The URL builder is pretty straight forward. Enter a date, name your link, and paste the landing page URL you want to link in your social media, blog post, or paid campaign.
Then you’ll enter information about your campaign to help you stay organized in your web analytics tool, like Google Analytics or Adobe Analytics. My URL builder has support for all 5 default UTM parameters, as well as a Custom Tag option.
Custom Tag
The Custom Tag option lets you add a query parameter to your URL that isn’t part of your normal UTM parameters. Maybe you want a tag that identifies your target audience for the link placement, the date, or you want to identify links in different versions of an A/B test. We all have our reasons!
The Custom Tag fields in this URL Builder work as a pair. You need to name your Custom Tag in one column, and then assign a value to that tag in the adjacent column.
For instance, lets say you want to build a URL like this:
https://www.example.com/page/?audience=womenyou would put “audience” in the custom_tag column and “women” in the custom value column.
UTM Parameters
These five columns are more intuitive. Just enter the values you want for each UTM parameter in their respective column.
You don’t have to use all five parameters for the builder to work. You can build a URL with any combination of parameters, but Google Analytics likely won’t recognize them if utm_source is not included. I also recommend using utm_medium. Personally, I rarely use utm_content or utm_term. You can leave them blank if you don’t need them. Here’s a quick guide to UTM Parameters if you need a crash course.
As you add custom parameters and URL parameters to the sheet, the Final URL column will update accordingly. Look over it carefully to make sure it’s got what you need. Try not to edit this field.
Once you’re satisfied with the Final URL, take the link and add it to your campaigns, social media posts, or wherever you’ve planned according to your needs. If you want to shorten the URL to make it prettier, keep reading!
Bitly Shortener
Sometimes a long URL with lots of query parameters is messy and hard to wrangle. If you can’t hide it behind anchor text, you might want to shorten it up with a URL shortener. I’ve elected to tap into Bitly’s API for Spreadsheets to both shorten the link, yes, report on clicks!
To use this functionality in the Spreadsheet, you’ll need a Bitly account and an associated Generic Access Token for that account.
- Once you’re logged into your account, click the Hamburger Menu in the top right
- Select Your Username/Email > Generic Access Token
- Type in your password and click Generate Token
- Copy this token into cell B4 of the Admin tab in the URL Builder spreadsheet
Back on the URL Builder tab, make sure you’ve completely built the URL from the previous section of this post. Any changes you make to your parameters will change the Bitly URL. Happy with the way it looks? Toggle “Yes” in the in the “Shorten?” column. After a few seconds, a Bitly link should appear in the next column. Voila! The last column will tally Clicks to your shortened Bitly URL (only the bitly, not other versions of the Final URL).
It’s worth noting here that sometimes Google Sheets doesn’t play nicely with its own Import URL formulas. If your bitly link or click count start showing “N/A” errors in their cells, try closing out the browser tab, waiting a few minutes, and opening it up. The Bitly still exists (and can always be viewed in your Bitly account), but Google Sheets might just need to rest a bit.
