Combine Bitly, Google Analytics, and Google Sheets for Winning Metrics Analysis

Warning: this content is older than 365 days. It may be out of date and no longer relevant.

I was asked recently by Social Media Examiner for my top social media tool recommendation for 2015, as a lead up to my talk at Social Media Marketing World. Here’s what I came up with:

When you combine Google Spreadsheets with the Google Analytics plugin and Bit.ly’s out-of-the-box integration, you have a powerful, free analytics dashboard solution. Everything’s in one place, and as a bonus, you can customize the reporting to incorporate more than one Google Analytics profile. That’s a significant help for consultants with more than one client.

James McCarthy followed up to ask:

“I just watched a Google video and saw how to enable the Analytics plug-in with Spreadsheets. Seems straightforward enough. How to go the next step and do the Bitly integration? Thank you.”

Bitly’s role is to determine how many things you’ve shared that get clicks. While Google Analytics will tell you what social posts drove traffic to your website that you’ve shared, it won’t tell you about the overall engagement of your shared content, which presumably contains links to other sites as well as your own. Bitly helps to solve this by reporting on how many clicks any Bitly link gets, regardless of when it’s shared, or who it’s shared by.

This is driven by Bitly’s integration with Google Spreadsheets, the documentation for which can be found here. You’ll need a free access token from Bitly’s developer page to make the magic happen.

For example, in this spreadsheet, I’ve pasted all of the Bitly links I’ve shared in the last couple of weeks:

The formula in the cell V5 above is:
=IF(ISURL(B5), IMPORTDATA(CONCATENATE(“https://api-ssl.bitly.com/v3/
link/clicks?format=txt&unit=day&units=-1&rollup=true&access_token=”, ACCESSTOKEN, “&link=”, B5)), “”)

If I can’t remember what a link is, I can use the expander function in the Bitly API to re-lengthen the shortened URLs:

The formula in the cell above is:
=IF(ISURL(B5), IMPORTDATA(CONCATENATE(“https://api-ssl.bitly.com/v3/
expand?format=txt&access_token=”, ACCESSTOKEN, “&shortUrl=”, B5)), “”)

And if I want to see what’s been re-shared and on which social network, I can use the Shares function in the Bitly API (admittedly not real clean, since it spits back raw JSON):

The formula in the cell above is:
=IF(ISURL(B5), IMPORTDATA(CONCATENATE(“https://api-ssl.bitly.com/v3/
link/shares?access_token=”, ACCESSTOKEN, “&link=”, B5)), “”)

Anyone who’s got a method for cleaning up raw JSON inside a Google Sheet, please leave it in the comments!

With the Bitly API and Google Spreadsheets, you can construct a fairly impressive dashboard and identify things like the most reshared content, the links that get the most engagement, and so much more. The entire reason for using Google Sheets is so that you can have tabs for all your Google Analytics data and Bitly data, then roll up just key analysis points onto a separate sheet or even workbook.

I can sort my Bitly links by click and identify the topics, by link, that get the most engagement vs. the topics that get crickets. From there, I can make a decision whether to focus more on certain topics to boost overall social media engagement. Blended with Google Analytics data, I can also see whether the most popular topics are resulting in actual conversions down-funnel or not, a critical point!

Thanks for the great question, James.

Disclosure: Bitly is a client of my employer, SHIFT Communications. I receive indirect financial benefit derived from them being a paying client. Bitly did not provide any assistance or resources for this blog post, nor did they ask me to write about them.