Recently I received a request to pull some metrics for our use of bit.ly in social media and display them in a Tableau dashboard. While Tableau doesn't have a native connection to bit.ly there is a great example from Andy Cotgreave on how to do this here: Analysing click counts from bit.ly using Python and Tableau
I gave that a shot however it requires the use of ‘bundles' in bit.ly which my social media team was not using. So what I have done is create a separate github repository with a script that will pull click counts by country and by date for an explicit list of links or using the bit.ly user_popular_links() API call.
Here are the setup instructions if you'd like to just jump right in:
- download my code from github here: github.com/bsullins/bitly-to-csv
- download and install python (python.org/download/)
- install ez_setup.py (pypi.python.org/pypi/setuptools)
- rename config-local.py as config.py and edit values with your own
- edit pop_links array and date range inside bitly_countries_csv.py
- run bitly_countries_csv.py
- (optional) setup nightly job to run bitly_countries_csv.py
I also want to mention that I chose to write this data out to a Comma Separated Values (CSV) file because the Tableau Data Extract (TDE) API does not support performing incremental updates. This means that if you wanted to port this data directly into a TDE you would need to regenerate the entire data set every time, when all you really need are the latest updates. bit.ly also has a rate limit on their API which I ran into often so doing a full refresh of the TDE every day wasn't an option. This way you just write out the most recent data to the CSV file then build your TDE off that and setup a nightly incremental update on your Tableau Server.
If you're new to python I recommend the following tutorials to get started:
- www.learnpython.org/
- Tableau Extract API Video Tutorial
- Building Extract files with Python by @ryrobes
Enjoy!