Recently I posted a Google Spreadsheet of all the live JISC funded project websites from the last 3 years. Not too long before that I also posted Google Spreadsheets as a lean mean social bookmark/share counting machine, which used Google Apps Script to query different social network providers for share/like counts for a specified url.
I thought it would be interesting to combine the two and see which JISC funded projects have been trickling through various social networks (the social engagement monitoring service PostRank did something similar with the TEDTalks, but since their purchase by Google the API, which would have made this a lot more easier, has been closed).
My starting point was the PIMS (2nd Pass) spreadsheet (I chose not to use PROD as I don’t think it has all of the JISC funded projects? – someone correct me if I’m wrong). I could have inserted cell formula for the custom Apps Script functions to getFacebookLike() etc. but as I mentioned in that post you can only use 100 of these before hitting timeouts, and with over 400 project websites it’s not straight forward.
The solution was two fold. Firstly, use Google Apps Script to iterate across the website urls fetching the results and recording it in the appropriate cell as a value. This saves the spreadsheet having to fetch the responses each time it is opened.
The second part of the solution was linked to this. As I was going to record values rather than use live data it made sense to try and aggregate the calls to individual social network services to avoid hitting urlfetch limits (I reckon you get about 400 of these a day?). As I mentioned in the original bookmark counting post I’d come across Yahel Carmon’s Shared Count API, which let you make one request and get a bunch of stats back for that url.
So here’s the code I used and the resulting Google Spreadsheet of JISC Project Social Favourites.
The most engaging JISC Project is…
And here is where the arguments start. The more accurate description is ‘the most engaging JISC funded project website index page is…’ and even then there is the caveat of (including established websites part funded by JISC). This also excludes all the blog posts, wiki pages, supplemental pages, repository submissions generated by JISC projects and also not forgetting other forms of engagement like other people writing and linking back to JISC project websites/resources. The list goes on. Sheila MacNeill at JISC CETIS has already posted some thoughts in Socially favoured projects, real measures of engagement?.
So is there any value is this data? I’ll let you decide. The important part for me was the process. I now have a method for returning social bookmark/shares for a bunch of websites and a framework using Google Apps Script to start automatically adding urls and collecting data.
So if the process was more important for me what did I learn along the way.
Shared Count API didn’t like some of the urls
For some reason the Shared Count API spat out the following 2 urls with 500 server errors.
- http://www.co-ode.org/ [Shared Count Call]
- https://www.wiki.ed.ac.uk/display/JISC/JISC+Learning+and+Teaching+Innovation [Shared Count Call]
I don’t know why it did this but my quick fix was to use the custom getFacebookLike formulas for these entries.
Twitter counts aren’t reliable
Twitter only recently started providing there own share/count button and as a result polling the official data isn’t always accurate. A separate service which has been monitoring the links people tweet for a lot longer is Topsy and fortunately for us Topsy have an API to pull similar data (these 3rd party APIs are becoming more scarce as the big boys buy up services and switch off APIs – I’m sure it will be a case that Topsy’s API will disappear soon as well 🙁
An example of the difference is the ticTOCs website which Twitter only thinks has been tweeted 30 times, but Topsy has 96 hits (the other advantage of Topsy is I can see what people said about ticTOCs – this data is also available via their API so I may be revisiting this source). When calculating ‘total engagement’ I took the maximum value between Twitter and Topsys (more about the total further down the page).
[As Topsy results aren’t included in the Shared Count API I grabbed these separately using the getTopsyCount function documented in my other bunch of other bookmark/share code snippets]
Hitting Apps Script urlFetch limits
Even using the Shared Count API (plus Topsy calls) I hit Apps Script urlFetch quota limits (I haven’t seen this documented anywhere but I’m guess its between 400-500). To get around this I shared the Spreadsheet and Script with another one of my Google accounts and was able to continue.
Stumble trip, stumble trip
I collected StumbleUpon stats mainly because they were part of the Shared Count API data, but unlike the other service details these are views rather than share counts so I didn’t include them in the totals as it’s a bit apple and pear-ish.
Buzz, Google’s second… no third… forth(?) attempt at social networking, is being eclipsed by Google+ but if like me you switched it on to automatically push updates from other services buzz counts potentially have a lot of noise in them.
For example, JISC funded projects which are hosted on Google Code (like Shuffl and meAggregator) end up with large Buzz counts (I’m guess each code commit generates a buzz) and not much other social activity. In the spreadsheet when I totalled the different service counts I also included a column excluding the Buzz counts.
Is a Like more valuable than a Tweet
This brings me back to to some of the questions around what does this all mean. I’ve already written/presented about how for services like Eventbrite there is more dollar value in a customer using a Facebook Like than Tweeting event information. So should a Facebook Like get more weighting than a tweet?
Umm not sure but if anyone has a collection of interesting urls they’d like a spreadsheet of social counts for get in touch 😉