Tag and Shorten Your URLs in Bulk for Better Google Analytics Data

OK, you wrote a killer new article for your company, and you want to share it with the world. So, you tweet it, you post it on Facebook, and you copy it to LinkedIn. Then you email it to everyone else in your company, because you want them to read it and share it with people as well. One week later, you check out your analytics, and you see a spike in traffic. Great, people like your article.

Then you think, “I wonder how many people got to my article via Facebook,” and “I wonder how many people found the link via my Facebook page, and how many found it via my colleagues’.” With proper UTM tagging, you’ll be able to answer these questions.

Unfortunately, tagging is a little cumbersome, especially when you want to tag your article differently for different sources and media. Let’s say you have 10 people on your team that you’ll ask to help get the word out, and you want your posts to go out to Facebook, LinkedIn, and Twitter. In that case, you have 30 different tagged URLs to create. Not fun. To make matters worse, your URLs all have all the UTM values and parameters in plain sight. Nothing says “I’m watching you” like a tagged URL.

Fortunately, we’ve got a little tool that will make these problems go away. The steps below show you how to create your very own Google Docs spreadsheet for URL tagging and shortening based on a template we created to make it nice and easy. With the template and a Bitly account, you can quickly tag any number of URLs in no time.

Step 3.

Step 4.

On the settings sheet of your spreadsheet, enter your Bitly username and the API key you copied in step 2.

Step 5.

Switch to the shortener section of the spreadsheet by clicking on “Shortener” in the bottom left side of the page. Replace the values on row 3, columns B through F, with the values you want to use. You’ll probably want to keep row 2 (which has example values) as is, so you can refer back to it.

Step 6.

Select row 3, columns B through I, and drag the selection down for as many rows as URLs you need (hint: multiply the number of people you want to help you share the article by the number of places you want to encourage them to share it)

Step 7.

Right now, all the rows are exactly the same. Go through the rows and update the Last Name, First Name, and Source columns with the appropriate values. Source should be the sharing method (e.g., Facebook). Last Name and First Name are the names of the people who’ll be sharing the article. You’ll probably want to leave the Campaign and URL values the same for every row.

Step 8.

Send the URLs out to your team and ask them to use the appropriate URL for sharing.

Step 9.

See who’s clicking! Go to your analytics page, click Traffic Sources > Sources > Campaigns. Once people start clicking your URLs, assuming you left the campaign name as “blog,” you’ll see a blog link and the related statistics for the campaign. You can drill down further into the analytics to see which accounts are getting the best results, by clicking the blog link.

And that’s all…well, except for writing awesome articles. Let us know how it goes.

Joshua’s tech and management expertise has been honed over time by the vast array of products he has helped architect and create. Since the dot com boom, he has worked as a manager, developer, graphic designer and entrepreneur. Today he shares his experience and knowledge with others in pursuit of great products and successful businesses.

As a proud and ambitious Setaris co-founder, Joshua is constantly on the lookout for ways to improve culture, generate excitement and make Setaris the best technology and marketing firm on the market. Joshua holds a Bachelor's in Electrical Engineering from Cooper Union and a Master's Degree in Electrical Engineering from Rensselaer Polytechnic Institute with an emphasis on pattern recognition and signal estimation.

25 comments

Hi Josh, thanks for the article! I’ve been blessed with the task to figure out how to better track campaigns, etc, and this seems like the easiest way, so far, to do so. The tool is super simple. However, after I enter all the information, the short URL shows INVALID_LOGIN. I’ve checked my username and API and they seem to be correct. What else could be wrong?

Hi Heather, I confirmed that the sheet does work as long as you have a correct username and api key in there. Please make sure that you also include the “http://” in the webpage URL. Bitly will object to URLs that don’t start that way.

Heather told me via e-mail that she signed up using twitter and assumed that her twitter username was her bitly username. As she discovered, this isn’t the case. So, if you signed up in that manner, look for your username here: http://bitly.com/a/your_api_key.

Great to hear that the article’s going to good use. As far as sharing it with your team, we always just do a standard copy and paste from the document to an email. Whether or not that works, however, probably depends on your operating system, and a host of other issues. If you’re trying to copy between spreadsheets, you’ll want to use “Edit > Paste Special > Paste Values Only” from the google docs menu (not your browser’s menu).

If you want to get the links into an email, and standard copy and paste isn’t doing the trick, you can use “Paste Values Only” to paste into another column in the spreadsheet, and then use standard copy and paste. That’s sure to work.

Hope one of these helps. Let me know if there’s anything else we can do to assist you. Cheers.

This can also be used to generate shortened links of multiple URLs – I’ve just tried it with 4 or 5 urls. I actually have about 40,000 unique URLs to shorten and I’m wondering what the API limit is. Have you tried it this way?

Hey Steven, yep, you can use it for as many URLs as you like. As you mentioned, you’re only limited by Bit.ly, and I’m not really sure what their policy is. I’m sure you can find out with a bit of Googling.

Ah well now that I actually tried it, it would seem the limitation is Google Docs, rather than bitly. The Google Docs ImportData function will import a maximum of 50 rows – so I can’t just drag down the 40,000 list. AFAIK there is no bit.ly API limit.

So I’m trying to work out if I can get around the Google Doc limitation now. So close!

Had trouble getting it started because I didn’t realise it had to be in google docs. Could you show us how it could work in excel as it only takes a small number of entries.
great idea, thanks for sharing. I have changed code a little to stop my staff making mistakes with spaces and blank fields

Hi LouLou,
Happy you found it useful. As far as getting it working in excel, the major obstacle is interfacing with Bitly. Do a quick google search for “bitly api excel” and I think you’ll find the information you need. Good luck.

Joshua, this spreadsheet is AWESOME. I actually downloaded it to excel because Excel allows you to do more than 50 imports. My next question is, how would I pull in a new column that shows the number of clicks through each specific bit.ly link? This doc is great for quickly creating those links, but I’d love to be able to utilize this as a one-stop place to view the results as well as create the links. Any suggestions?

I can see from looking at the Short URL field in your doc how you used the API to create the shortened URL, but I don’t think I understand enough about how to utilize different API fields to pull in the click stats…I’ll have to keep working to figure it out…

Hey Matt, it’s hard for me to tell from what you included in your post, but it looks like you forgot to add authorization information to the spreadsheet. Please follow the steps to a tee. You see that by the other comments that many people have this working. So, unless Bitly changed, the steps I outlined should work.