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

A screenshot of the Google Analytics dashboard

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 1. (Skip if you already have an account with Bitly)

Sign up for a free Bitly account

Step 2.

Log in to your Bitly account, and go to your account settings page. Scroll down to where you see “API Key”, and copy the entire string in the text box just below. Location of the API key in Bitly

Step 3.

Open the URL tagging and shortening spreadsheet template and make a copy (File > Make a Copy).

Step 4.

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

Settings area of Bulk URL Tagger and Shortener spreadsheet

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 Fialkoff

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.

Twitter - More Posts

25 comments

  1. 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?

    • Joshua Fialkoff

      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.

    • Joshua Fialkoff

      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.

  2. Joshua, thanks so much for sharing this…it’s going to be incredibly useful!

    I have a quick question and hopefully you can shed some light on it. I just set this up and everything worked perfectly and the short url shows up in the “Short URL” column.

    In Step 8. above you reference sharing the short urls with our team, but when I right click–>copy in that field, the url that is copied is not the short url, but rather the Long URL.

    Can I assume that what you mean by “share the urls” is perhaps exporting this doc to a CSV and sharing that way?

    • Joshua Fialkoff

      Hey Adam!

      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.

  3. 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?

    • Joshua Fialkoff

      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!

        • Joshua Fialkoff

          Hey Steven! Thanks for keeping me updated. Yes, please feel free to link out to your post when you write it, and reference ours if you don’t mind. Thanks!

      • Currently trying to work out if I make similar requests using Excel. I can submit one whole query to the bitly API and return the shortened link, but i don’t know how to do it for a list.

  4. I CAN’T PUT MY api KEY YOUR EXCEL CELL HAS PROTECTION THANK YOU

    • Joshua Fialkoff

      Hi. Please make a copy of the sheet (one of the steps in the directions). From that point you should be good to go!

  5. 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

    =concatenate(D3,”?&utm_medium=”,lower(G3),IF((LEN(F3)>0),CONCATENATE(“&utm_source=”,(SUBSTITUTE(F3,” “,”%20″))),”"), IF((LEN(E3)>0),CONCATENATE(“&utm_campaign=”,(SUBSTITUTE(E3,” “,”%20″))),”"), IF((LEN(H3)>0),CONCATENATE(“&utm_content=”,(SUBSTITUTE(H3,” “,”%20″))),”")

    • Joshua Fialkoff

      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.

  6. This is brilliant, thanks for sharing. We’ve been using the Google Analytics URL Builder Chrome add-on, but it doesn’t do bulk shortening. You just cut my work by 80% today! :)

  7. 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?

    • Joshua Fialkoff

      Hey Jeremy! Happy to hear that the you’re finding the spreadsheet to be useful. As far as getting the number of clicks in there, the bit.ly api does provide that information. Take a look at this: http://dev.bitly.com/link_metrics.html#v3_link_clicks

      • 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…

    • Joshua Fialkoff

      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.

      • Thank you it works fine now, I dont know what I did wrong but it’s ok now! Thanks for this nice spreadsheet!

        Btw, when I download the excel file to make it work on my pc (on local), it does not work anymore, is that normal?

        thanks, have a nice day

        • Joshua Fialkoff

          Yea Matt. I wouldn’t expect this spreadsheet to translate to Excel.

          • ok well noted. It’s a pity I cant make it run on my excel, is it possible to sell me the script eventually, to make it run on my excel? ^^

Leave a Reply to Adam W. Warner Cancel reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>