Social Networking

2 Comments

Livros de Redes Sociais, SEO e Web 2.0Perhaps the worst SEO post title I could possibly use. If you are still wondering what SEO is ask Nitin Parmar ;)

Still lost? SEO is Search Engine Optimisation. I’ve had a long interest in SEO primarily in selfish terms to try and get this blog more read, but also in a wider ‘educational resource discovery’ context. I was the author of the ‘SEO and discoverability’ chapter in Into the wild – Technology for open educational resources, which highlights the importance and UKOER programme experiments with SEO.

So it’s perhaps no surprise that I agree with Tony:

Something I’m increasingly become aware of is SEO is not just about having the right metadata on your webpage, in fact arguably this is the least important aspect. The area I’m particularly interested in is the tools/techniques the SEO community use to gain ‘actionable insight’.

Ironically this is an area I’ve been inadvertently contributing to without really knowing it. Someone who spotted this early was Wil Reynolds founder of SEER Interactive:

SEER Interactive offer services in Paid Search Marketing and Search Engine Optimization but what’s particularly interesting is their commitment to being “an Analytics first company, and we will not take on projects where we can’t analyze our impact on your business”.

So what do I do that’s of interest to the SEO community? Well it seems like me SEOers like a good old-fashioned spreadsheet. They also like a good old-fashioned spreadsheet that they can hook into social network channels. A recent example of this is the work Richard Baxter (CEO and founder of SEOgadget) presented at MOZCon which extends TAGS (my Twitter Archiving Google Spreadsheet solution) demonstrating How To Use Twitter Data for Really Targeted Outreach. The general synopsis is:

an alternative method to find sites that our target audiences may be sharing on Twitter. With that data, you can build content strategy, understand your market a little better, and construct an alternative outreach plan based on what real people are sharing and engaging with, rather than starting with sites that just rank for guest post queries.

It was really interesting to read how Richard had used the output from TAGS, which was ingested into Excel where additional free Excel based SEO tools could be used to gain that all important ‘actionable insight’.

So ‘learning tech and library folk’ if you are planning your next phase of CPD maybe you should be looking at some SEO training and perhaps I’ll see you at MOZCon next year ;)

2 Comments

scapetate.com data model
scapetate.com data model
Originally uploaded by psd
I thought it would be useful to give a summary of some of the tools I use/developed at CETIS to monitor the pulse of the web around our and JISC work. All of these are available for reuse and documented to varying degrees. All of the tools also use Google Spreadsheets/Apps Script which is free for anyone to use with a Google account, and all the recipes use free tools (the exception being owning a copy of Excel, but most institutions have this as standard).

Tools

Hashtag archiving, analysis and interfacing

Hashtag archiving, analysis and interfacingUsed with: CETIS COMMS, OERRI Programme, #UKOER, …
What it does: It’s a Google Spreadsheet template which can be setup to automatically archive Twitter searches. The template includes some summaries to show top contributors and frequency or tweets. There are a number of add-on interfaces that can be used to navigate the data in different ways, including TAGSExplorer and TAGSArc.

More info: http://mashe.hawksey.info/2011/11/twitter-how-to-archive-event-hashtags-and-visualize-conversation/

Monitoring Twitter searches and combining with Google Analytics

Monitoring Twitter searches and combining with Google AnalyticsUsed with: CETIS COMMS
What it does: Archives all tweets linking to to the .cetis.ac.uk domain and combines with our Google Analytics data to monitor influential distributors of our work.

More info: http://mashe.hawksey.info/2012/03/combine-twitter-and-google-analytics-data-to-find-your-top-content-distributors/

RSS Feed Activity Data Monitoring

RSS Feed Activity Data MonitoringUsed with: CETIS COMMS, OERRI Programme
What it does: Gives a dashboard view of the total social shares from a range of services (Facebook, Twitter, Google+ for a single or combination of RSS feeds. At CETIS we also monitor the social popularity of blogs referencing .cetis.ac.uk by using a RSS feed from Google’s Blog Search e.g. http://www.google.com/search?q=link:cetis.ac.uk&hl=en&tbm=blg&output=rss&num=20

More info: http://mashe.hawksey.info/2012/06/rss-feed-social-share-counting/

Post Activity

Blog Activity Data Feed Template OverviewUsed with: CETIS COMMS
What it does: Gives more detailed activity data around socially shared urls combining individual tweets from Topsy, Delicious, and post comments.

More info: http://mashe.hawksey.info/2012/08/blog-activity-data-feed-template/

Post Directory

Post DirectoryUsed with: OERRI Programme
What it does: Dashboards all the project blogs from the OERRI Programme and monitors when they release blog posts with predefined tags/categories. The dashboard also combines the social monitoring techniques mentioned above so that projects and the programme support team can monitor social shares for individual blog posts.

More info: http://mashe.hawksey.info/2012/08/how-jisc-cetis-dashboard-social-activity-around-blog-posts-using-a-splash-of-data-science/

Automatic final report generation

OERRI DashboardUsed with: OERRI Programme
What is does: As an extension to the Post Directory this tool combines project blog posts from a predefined set of tags/categories into a final report as an editable MS Word/HTML document. Currently only the original post content, including images, is compiled in individual reports but it would be easy to also incorporate some of the social tracking and/or post comments data.

More info: http://mashe.hawksey.info/2012/09/converting-blog-post-urls-into-ms-word-documents-using-google-apps-script-oerri/

Recipes

As well as standalone tools I’ve documented a number of recipes to analysis monitoring data.

Twitter conversation graph

Twitter conversation graphUsed with: #moocmooc, #cfhe12
What it does: Using data from the Twitter Archiving Google Spreadsheet template (TAGS) this recipe shows you how you can use a free Excel add-on, NodeXL, to graph threaded conversations. I’m still developing this technique but my belief is there are opportunities to give a better overview of conversations within hashtag communities, identifying key moments.

More info: http://mashe.hawksey.info/2012/08/first-look-at-analysing-threaded-twitter-discussions-from-large-archives-using-nodexl-moocmooc/

Community blogosphere graph

Community blogosphere graphUsed with: #ds106
What it does: Outlines how data from blog posts (in this case a corpus collected by the FeedWordPress plugin used in DS106) can be refined and graphed to show blog post interlinking within a community. An idea explored in this recipe is using measures used in social network analysis to highlight key posts.

More info: http://mashe.hawksey.info/2012/10/visualizing-cmooc-data-extracting-and-analysing-data-from-feedwordpress-part-1-ds106-nodexl/

Activity data visualisation (gource)

Activity data visualisation (gource)Used with: #ukoer
What it does: Documents how data can be extracted (in this case records from Jorum) and cleaned using Google Refine (soon to be renamed OpenRefine). This data is then exported as a custom log file which can be played in an open source visualisation tool called Gource. The purpose of this technique is to give the viewer a sense of the volume and size of data submitted or created by users within a community.

More info: http://mashe.hawksey.info/2011/12/google-refining-jorum-ukoer/

So now go forth and reuse!

2 Comments

A little snippet of code I’m using to get share counts for a url from a number of services in one call. The idea is I pass a url and I get some json back with counts from Facebook, Twitter, Delicious, Pinterest and Google +1s (if it’s a bad url or nothing returned from the service then null value).

json returned 

<?php
$url = $_GET['url'];
$finfo = json_decode(file_get_contents('http://api.ak.facebook.com/restserver.php?v=1.0&method=links.getStats&urls='.$url.'&format=json'));
$tinfo = json_decode(file_get_contents('http://urls.api.twitter.com/1/urls/count.json?url='.$url));
$dinfo = json_decode(file_get_contents('http://feeds.delicious.com/v2/json/urlinfo/data?url='.$url));
$pinfo = json_decode(preg_replace('/^receiveCount\((.*)\)$/', "\\1",file_get_contents('http://api.pinterest.com/v1/urls/count.json?callback=receiveCount&url='.$url)));
$gplus = gplus_shares($url);


//http://papermashup.com/google-plus-php-function/
function gplus_shares($url){
    // G+ DATA
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "https://clients6.google.com/rpc?key=AIzaSyCKSbrvQasunBoV16zDH9R33D88CeLr9gQ");
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_POSTFIELDS, '[{"method":"pos.plusones.get","id":"p",
"params":{"nolog":true,"id":"' . $url . '","source":"widget","userId":"@viewer","groupId":"@self"},
"jsonrpc":"2.0","key":"p","apiVersion":"v1"}]');
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-type: application/json'));
    $result = curl_exec ($ch);
    curl_close ($ch);
    return json_decode($result, true);
}

$output = array(
    'facebook'=> isset($finfo[0]) ? $finfo[0]->total_count : NULL,
    'twitter'=> isset($tinfo->count) ? $tinfo->count : NULL,
    'delicious'=> isset($dinfo[0]) ? $dinfo[0]->total_posts : NULL,
    'pinterest'=> isset($pinfo->count) ? $pinfo->count : NULL,
    'googlePlus'=> isset($gplus[0]['result']) ? $gplus[0]['result']['metadata']['globalCounts']['count'] : NULL

);

header('Content-Type: text/javascript');

echo "[".json_encode($output)."]";
?>

Usually I use sharedcount.com for this, but was worried that give over 15k urls to get data on I might trip their server (although I was given assurances that it was fine). I’ve deployed this code on a local webserver (XAMPP Lite) and it’s working well in parallel with Google Refine.

I should say this post contains a lot of technical information, doesn't give much background and is mainly for my hard-core followers

This is a very lose sketch of an experiment I might refine which uses Jason Davies wordcloud script (add-on for d3.js) as a way to filter data hosted in a Google Spreadsheet. I was essentially interested in the Twitter account descriptions of the community using the the Social Media Week – Glasgow hashtag, but a minor detour has reminded me you can:

  • get json data straight from a Google Spreadsheet
  • you can build dynamic queries to get what you want

So I fired up NodeXL this morning and got this pretty graph of how people using the #smwgla hashtag at least twice follow each other.

people using the #smwgla hashtag at least twice follow each other

One of the features of NodeXL is to add stats columns to your data which includes friend/follower counts, location and profile descriptions.

NodeXL Stats

Uploading the data from NodeXL (Excel) to Google Spreadsheets allows me to render an interactive version of the community graph using my NodeXL Google Spreadsheet Graph Viewer.

interactive version of the #smwgla community graph

All this is doing is grabbing data from Google Spreadsheets using their Visualization API and rendering it visually using javascript/HTML5 canvas. You can use the query language part of this API to get very specific data back (if you want a play try Tony Hirst’s Guardian Datastore Explorer). Using Tony’s tool I got this query built. One thing you might notice is I’m selecting a column of twitter description WHERE it contains(‘’) <- a blank – if it’s a blank why did I bother with the WHERE statement?

Switching to Jason Davies wordcloud demo we can play with custom data sources if you have some JSON. In Tony’s tool you have options to get the data in html (tqx=out:html) and csv (tqx=out:csv). There is a third undocumented option for json tqx=out:json. Using this we can get a url for the wordcloud generator https://spreadsheets.google.com/tq?tqx=out:json&tq=select%20AH%20where%20AH%20contains%28%2727%29&key=0Ak-iDQSojJ9adGNUUXZnU2k3V1FRTjR3eFp0RmRNZWc&gid=118

To make the wordcloud interactive, so that when you click on a term it filters the data on that term was can use the option to include {word} in our source url e.g. https://spreadsheets.google.com/tq?tqx=out:json&tq=select%20AH%20where%20AH%20contains%28%27{word}%27%29&key=0Ak-iDQSojJ9adGNUUXZnU2k3V1FRTjR3eFp0RmRNZWc&gid=118

And here is the final result, an interactive wordcloud of #smwgla Twitter account descriptions [Note: you need to hit the Go button when you click-through]:

interactive wordcloud of #smwgla Twitter account descriptions

The end result useful? Not sure, but how the data is extracted is (to me anyway).

4 Comments

Warning: Very techie post, the noob version is here Google Spreadsheet Template for getting social activity around RSS feeds HT @dajbelshaw

In my last post announcing a Google Spreadsheet Template for getting social activity around RSS feeds I mentioned it was built from spare parts. It came out of on-going work at CETIS exploring activity data around CETIS blogs (including this one) and JISC funded projects. In this post I’ll highlight some of the current solutions we are using and how they were developed.

To put this work into context it aligns with my dabbling's as a data scientist.

A data scientist … is someone who: wants to know what the question should be; embodies a combination of curiosity, data gathering skills, statistical and modelling expertise and strong communication skills. … The working environment for a data scientist should allow them to self-provision data, rather than having to rely on what is formally supported in the organisation, to enable them to be inquisitive and creative. [See this post by Adam Cooper for the origins of this definition]

It’s perhaps not surprising that the main tool I use for these explorations with data is Google Spreadsheets/Google Apps Script. The main affordances are a collaborative workspace which can store and manipulate tablature data using built-in and custom functionality. To illustrated this let me show how easy it is for me to go from A to B.

Example 1: OER Rapid Innovation Projects – Blog post social activity

I’ve already posted how we are Using Google Spreadsheets to dashboard project/course blog feeds #oerri. For this we have a matrix of project blog feeds and a set predefined categories/tags. As projects make different posts a link to them appears on the dashboard.

OERRI Project Post Directory

As part of the programme completion JISC are piloting a new project survey which will include reporting the ‘reach of the papers/articles/newsitems/blogposts out of the project’. Out of curiosity I wanted to see what social share and other activity data could be automatically collected from project blogs. The result is the page shown below, which is also now part of the revised OERRI Project Post Directory Spreadsheet. The important bit is the columns on the right with social counts from 10 different  social network services. So we can see a post by Xenith got 29 tweets, a post by bebop got 5 comments, etc.

Caveat: these counts might not include modified urls with campaign tracking etc …

OERRI Post Social Activity

How it works

The three key aspects are:

  • Getting a single sheet of blog posts for a list of RSS feeds.
  • Getting social counts (Likes, Tweets, Saves) for a range of services.
  • Allowing columns sorting on arrays of results returned by custom

Getting a single sheet of blog posts
You can use built in formula like importFeed to do this, but you are limited to a maximum 20 post items and you need to do some juggling to get all the results from 15 projects in one page. An alternative, which I quickly ruled out was fetching the rss xml using Google Apps Script and parsing the feed. The issue with this one is RSS feeds are usually limited to the last 10 posts and I wanted them all.

The solution was to use the Google Feed API (can’t remember if this is on Google cull list). The two big pluses from this are it can pull historic results (up to 100 items), and results can be returned in JSON which is easy to work with in App Script.

Here’s what the function looks like which is called as a custom formula in cell Data!A4 (formula used looks like =returnPostLinks(Dashboard!A3:B17,"postData")):

function returnPostLinks(anArray,cacheName){
  var output = [];
  var cache = CacheService.getPublicCache(); // using Cache service to prevent too many urlfetch 
  var cached = cache.get(cacheName);
  if (cached != null) { // if value in cache return it
    output = Utilities.jsonParse(cached);
    for (i in output){
      output[i][3] = new Date(output[i][3]); // need to recast the date
    }
    return output; // if cached values just return them
  }
  // else make a fetch
  var options = {"method" : "get"};
  try { 
    for (i in anArray){ // for each feed url
      var project = anArray[i][0]; // exact the project name from 1st col
      if (project != ""){ // if it's not blank
        var url = "https://ajax.googleapis.com/ajax/services/feed/load?v=1.0&num=100&q="+anArray[i][1];
        var response = UrlFetchApp.fetch(url , options);
        var item = Utilities.jsonParse(response.getContentText()).responseData.feed.entries; // navigate to returned items
        for (j in item){
          // for each item insert project name and pull item title, link, date, author and categories (other available are content snippet and content)
          output.push([project,item[j].title,item[j].link,new Date(item[j].publishedDate),item[j].author,item[j].categories.join(", ")]);
        }
      }
    }
    // cache the result to prevent fetch for 1 day
    cache.put(cacheName, Utilities.jsonStringify(output), 86400); // cache set for 1 day
    return output; // return result array
  } catch(e) {
    //output.push(e);
    return output;
  }
}

 

Getting social share counts

Collecting social share counts is something I’ve written about a couple of times so I won’t go into too much detail. The challenge is this project was to get results back from over 100 urls. I’m still not entirely sure if I’ve cracked it and I’m hoping more result caching helps. The issue is because the custom formula used in this appears in each row of column H on the Data sheet, when the spreadsheet opens it simultaneously tries to do 100 UrlFetches at the same time which Apps Script doesn’t like (Err: Service has been invoked too many times). {I’m wondering if away around this would be to set a random sleep interval (Utilities.sleep(Math.floor(Math.random()*10000))… just tried it and it seems to work}

Sorting columns when arrays of results are returned by custom formula

Sort order selectionWhen you use spreadsheet formula that write results to multiple cells sorting becomes a problem because the cells are generated using the CONTINUE formula which evaluates a result from the initiating cell. When you sort this reference is broken. The solution I use is to pull the data into a separate sheet (in this case Data) and then use the SORT formula in the ‘Post SN Count’ sheet to let the user choose different sort orders. The formula used in ‘Post SN Count’ to do this is in cell A4: =SORT(Data!A4:Q,D1,IF(D2="Ascending",TRUE,FALSE)). To try and prevent this being broken an ‘Item from list’ data validation is used with the ‘Allow invalid data’ unchecked.

Time to hack together: functional in 2hrs, tweaks/cache fixes 4hrs

Example 2: JISC CETIS Blog Post Dashboard

Having got social share stats for other peoples feeds it made sense for the JISC CETIS blogs. At the same time because I keep my blog on my own domain and Scott (Wilson) is on wordpress.com it was also an opportunity to streamline our monthly reporting and put all our page views in one place.

Below is a screenshot of this de-page viewed version of the CETIS dashboard (the code is all there I’ve just haven’t authenticated it with our various Google Analytic accounts). With this version the user can enter a date range and get a filtered view of the posts published in that period. Sort and social counts make a re-appearance with the addition of an ‘Examine Post Activity’ button. With a row highlighted, clicking this gives individual post activity (shown in the second screenshot). The code for this part got recycled into my last post Google Spreadsheet Template for getting social activity around RSS feeds.

CETIS Post Dashboard

Individual post activity

Getting to B

To get to this end point there are some steps to pull the data together. First the ‘Sources’ sheet pulls all our blog urls from the CETIS contacts page using the importHtml formula. Next on the ‘FeedExtract’ sheet the array of blog urls is turned into a sheet of posts using the same code in example 1. Social counts are then collect on the ‘Data’ sheet which is read by the Dashboard sheet.

A detour via page views

Having some existing code to get stats from Google Analytics made it easy to pass a url to one of two GA accounts and get stats back. Because Scott is on wordpress.com another way was required. Fortunately Tony Hirst has already demonstrated a method to pull data from the WordPress Stats API into Google Spreadsheets. Following this method stats are imported to a ‘Scott’sStats’ sheet and tallied up with a code loop (WordPress stats come in on per post, per day).

WordPress Stats API import

Here’s the custom formula code to return page views for a url:

function getCETISPageViews(url, startDate, endDate){
  // format dates
  var startDate = Utilities.formatDate(startDate, "BST", "yyyy-MM-dd");
  var endDate = Utilities.formatDate(endDate, "BST", "yyyy-MM-dd") ;
  // extract domain and path
  var matches = url.match(/^https?\:\/\/([^\/:?#]+)(?:[\/:?#]|$)/i);
  var domain = matches && matches[0];
  var path = url.replace(domain,"/");
  // switch for google analytic accounts
  var ids = false;
  if (domain == "http://mashe.hawksey.info/"){
    ids = ""//ga-profile-id  
  } else if (domain == "http://blogs.cetis.ac.uk/"){
    ids = ""//ga-profile-id;
  } else if (domain == "http://scottbw.wordpress.com/"){
    // code to compily stats imported to sheet
    // get all the values
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName("Scott'sStats")
    var data = sheet.getRange(1, 4, sheet.getLastRow(), 2).getValues();
    var count = 0;
    // count on url match
    for (i in data){
      if (data[i][0] == url) count += data[i][1];
    }
    return parseInt(count);
  }
  if (ids){
    // GA get data using sub function
    return parseInt(getGAPageViews(startDate, endDate, path, 1, ids));
  }
}

Note: I use a custom Google Analytics function available in the spreadsheet script editor. There’s a new Analytics Service in Apps Script as an alternative method to connect to Google Analytics.

Time to hack together: functional in 3hrs, tweaks fixes 1hr

Summary

So there you go two examples of how you can quickly pull together different data sources to help us record and report the social reach of blog posts. You’ll notice I’ve conveniently ignored whether social metrics are important, the dangers of measurement leading to gaming, the low recorded social activity around OERRI projects. I look forward to your comments ;)

4 Comments

The 'Deprecated notice' is because the access to the Topsy API (which is used to extract Twitter activity) is now behind a developer key.

A post from Alan Levine (@cogdog) on Syndication Machines (plus syndication of syndication) which details how feeds for the cMOOC course ds106 are aggregated and resyndicated got me thinking if there was additional data around student blog posts that could be usefully captured. Previously for something like that I would have turned to PostRank Analytics, which allowed you to specify any rss feed and it would aggregate social activity like tweets, bookmarks from a wide range of services and let you see it all in one place. Unfortunately PostRank were bought by Google and while some of this data is now accessible from Google Analytics it’s restricted to your account and data from social networks Google isn’t directly competing with.

So I thought it would be useful/interesting to start looking at what social activity you could pull together without authenticated access or API keys. So far I’ve identified couple and given some overlap with other work (and some late night play time), I’ve come up with a Google Spreadsheet template which pulls data together from comment feeds, Twitter and Delicious (with social counts for these plus Facebook, LinkedIn and Google+). You can give the spreadsheet a try with the link below:

*** Blog Activity Data Feed Template ***

Blog Activity Data Feed Template Overview

Features

  • Collects last 10 posts from a RSS feed
  • Uses sharedcount.com to get overall post share counts from Facebook, Twitter, Google+, LinkedIn and Delicious
  • For supported blogs (mainly manila WordPress) extracts comment snippets
  • Uses Topsy to collect tweets mentioning the post
  • Fetches all the Delicious bookmarks for the post url
  • Summarises activity from comments, tweets and bookmarks on the dashboard

You can make as many copies of the template as you like to track other RSS feeds.

If you prefer a slightly different overview then the RSS Feed Social Share Counting Google Spreadsheet gives a table of share counts for a feed

*** RSS Feed Social Share Counter ***

Social share count matrix

Technical highlights

There are a couple of cool things under-the-hood for the Activity Data template worth noting.

Managed Library = Easy to add more sources

So far I’ve limited the template to sources that have easy access. Previously if an API disappeared, changed or I discover another service I would have to modify the template and users would have to make a fresh copy. With Managed Libraries the Google Spreadsheet template now only needs 11 lines of code (shown below) for 3 custom formulas (see cells Dashboard!C8, and #n!G2 and #n!B8).

function getSharedCountActivity(url){
  return socialActivity.getSharedCount(url);
}

function getItemFeed(url, num){
  return socialActivity.getItemRSSFeed(url, num)
}

function getActivityFeed(url){
  return socialActivity.getActivityFeed(url);
}

Because the template uses my own socialActivity library and because I’ve left the template in Development Mode when I update the code changes should (I hope) filter to all the existing templates in use. This means if I can add more services with open APIs details should start appearing on the post activity pages (denoted with # at the beginning).

Fun with sparklines

Activity SparklinesIf you were wondering how I did the activity sparklines, here’s how. Sparklines are a built-in Google Spreadsheet formula (here’s the  reference). To get them to work you need some data if you want some options. For the data all you need is an array of values and sparkline will plot them equally spaced.

In this use case I’ve got comments, tweets, and bookmarks with date/times. To plot the sparkline I need to know how many of these were made between specific time periods. Another function we can use to get this data is FREQUENCY. This lets us specify some intervals and source data and returns an array of the frequency of data points.

One issue is that for each post the activity range varies, so one post might only have activity in July, and another in August. There are other formula (MIN/MAX) to get the range values. Here’s how the final solution was pieced together.

On each post sheet (prefixed with ‘#’) in cells A1:A20 are some formula to calculate date intervals based on the minimum and maximum date ranges. Frequency data used for each sparkline is then calculated inside the sparkline formula by indirectly looking at the data in each post sheet. The indirectly part is handled by the INDIRECT formula which evaluates a cell reference from other cell values. So on row 9 of Dashboard INDIRECT(B9&"!D9:D") evaluates to ‘#1!D9:D’, row 10 is ‘#2!D9:D and so on. I could have hardcoded these cell references but I prefer the ‘clever’ way. Here’s part of the final formula with a breakdown of what it does:

SPARKLINE(FREQUENCY(INDIRECT(B9&"!D9:D"),INDIRECT(B9&"!A1:A20")),{"charttype","line";"ymin",-1;"ymax",MAX(FREQUENCY(INDIRECT(B9&"!D9:D"),INDIRECT(B9&"!A1:A20")))+1} )

  • INDIRECT(B9&"!D9:D") – Frequency data source built using INDIRECT which evaluates a cell reference from other cell values. On row 9 of Dashboard this evaluates to ‘#1!D9:D’, row 10 is ‘#2!D9:D
  • INDIRECT(B9&"!A1:A20") – similar trick to get an interval array for the FREQUENCY formula
  • FREQUENCY(INDIRECT(B9&"!D9:D"),INDIRECT(B9&"!A1:A20")) – gives our sparkline data array
  • MAX(FREQUENCY(… – get the maximum value from the frequency data just so we can add 1 to it to give a margin on the sparkline

Summary

Hopefully you’ll enjoy and if you can think of any improvements or other services to tap into just get in touch.

14 Comments

Sheila MacNeill recently shared Some thoughts on web analytics using our work on analytics in which she mentioned a tool I developed which allows us to quickly get social share and page view counts for out blog, category or project feeds. The solution was a quick add-on to Using Google Spreadsheets to combine Twitter and Google Analytics data to find your top content distributors, recycling some other bits of code I had lying around (mostly from Introducing a RSS social engagement tracker in Google Apps Script #dev8d, which has never really worked). The reworking is much simpler and more reliable so if you would like to extract social counts from your own (or other people’s) RSS feeds here’s:

*** RSS Feed Social Share Counter ***

If you are using this with your Google Analytics account you’ll need to authenticate access, otherwise you can just enter a RSS feed in cell B5 and see what you get back.

Social Counts/Page Views for OOH-ER

Limitations/usage notes

As JISC CETIS uses WordPress for it’s blogs it’s easy to get feeds for particular projects. For example I can get a RSS feed for the OER Visualisation Project using http://mashe.hawksey.info/category/ooh-er/feed/. The spreadsheet is using the importFeed formula you’re limited to 20 items. Usually this is enough for us as most project feeds fairly go over that.  How meaningful are the numbers? At the end of the day page views probably have the most weight (tweets are cheap ;), I do find social counts useful as a way to find posts worth further investigation to find out who said what when – further work is required though to turn that data into actionable decisions.

2 Comments

Reader Stats: From your 353 subscriptions, over the last 30 days you read 4,721 items, clicked 68 items, starred 9 items, and emailed 55 items.I’ve been a long time user of Google Reader. It’s been an up and down relationship even going through a trial separation, but I always come back. One of the things I’ve struggled with is a workflow that allows me to share and file interesting posts on the mobile version of Google Reader. I’ve previously experimented with custom Yahoo Pipes and services like ifttt.com but find these don’t give me the flexibility in editing tweet text or tags used to bookmark links. The slides below highlight the problem, if I want to personalise how a post is saved/shared you have to navigate out of Google Reader and then you are on reliant on share features of the source post or apps available on your phone.

Send email from Google Reader MobileHere’s my solution. As Google Reader mobile has an email option it’s possible to push posts to different services without navigating away and still let you have control over what is saved/tweeted. Initially I considered using existing services which let you create your own workflows by sending stuff to a dedicated email, example services which let you do this that I already use are Evernote and ifttt.com. Instead as there have been a number of Google Apps Script Gmail productivity solutions like Drew Csillag’s (sounds like cheese-log ;) Calorie Counting with Google Apps Script I thought I’d give that technique a go.

So I’ve come up with this:

*** GmailProductivitySheet v1.0 Spreadsheet ***

This solution uses the old Gmail trick of adding +whateveryouwant to your email address to allow you to do custom filtering. For example, if I email m.hawksey+twitter[at]gmail.com the message still ends up in my inbox but it makes it easy for me to setup a filter rule. So using Google Apps Script I can search my Gmail inbox for messages from me with +twitter, +delicious or +diigo and then using oAuth or basic authentication pass links/messages through those service’s API’s, I hope you agree Google Apps Script at it’s finest.

Setup/Usage

Here’s a video to help you with setup and usage (recorded in a Google Hangout On Air – still experimenting to get the best quality)

In summary, if I want to tweet something then I email the link to mygmailaccount[email protected], to save a bookmark either mygmailaccount[email protected] or mygmailaccount[email protected], or any combination of the three by sending the message to the email address.

Google Reader Productivity Flow

BTW if you are worried I’m wasting JISC CETIS time to make this fear not as this was an out of hours project. I also feel slightly annoyed with myself for spending so much time taking what was a functional solution (couple of hours work) and trying to make it more user-friendly for re-use particularly as the payback is minimal. Ho-hum.

3 Comments

Today in Capturing The Value Of Social Media Using Google Analytics Google announced some new features that will be appearing in Google Analytics. The post is mainly focused around 'social value’ of defining and monitoring goals for getting people coming to your site from social networks to do something on your site (click a button, view a certain page).

The bit that is really interesting (for me anyway) is the announcement on ‘activity streams’. These will include information on:

how people are engaging socially with your content off your site across the social web. For content that was shared publicly, you can see the URLs they shared, how and where they shared (via a “reshare” on Google+ for example), and what they said. Currently, activities are reported for Google+ and across a growing list of our Social Data Hub partners including recently signed brands Badoo, Disqus, Echo, Hatena and Meetup.

Example Activity Stream

There is obvious overlap here with some of my recent work extracting ‘activity data’ from social networks for sites and repositories, but before I pack my bags there are a number of things to consider.

Twitter and Facebook probably won’t come to the party
Google’s access to activity data is limited to those who want to join the Analytics Social Data Hub. While there are already some reasonably big names signed up given the Twitter/Facebook/Google+ social network war it’s unlikely that you are going to see individual tweet analytics as I achieved here in the near future.

Access to the data
It’ll be interesting if Google will make ‘activity stream’ data available for download or access via their API. There’s very little information on the Social Data Hub website about what 3rd party services are signing up to and if there is an compensation for make their data available. For a number of the existing signups they already have their own public APIs so they may be happy for this data to be made available. Only time will tell.

Not everyone uses Google Analytics
I’m also trying to take comfort in the fact that not everyone uses Google Analytics, so there is hopefully still value is surfacing and centralising activity data for non-Analytics users.

So interesting times, but does anyone actually care about this type of data yet?

5 Comments

For my session at Dev8D I got delegates building a RSS social engagement tracker similar to PostRank (Slides here) [Note to self: Too much coding for the room. Doh!]. Initially I was going to use my Fast-tracking feedback example for this session but forever wanting to make my life difficult decided late on to come up with something entirely new. Part of this decision was influenced by being featured in Mashable’s  5 Essential Spreadsheets for Social Media Analytics (yeah), combined with the fact that my PostRank daily email notifications are broken.

For those not familiar with PostRank their service (now owned by Google) would allow you to enter a blog RSS feed and then they would monitor ‘social engagement’ around that feed recording tweets, likes, saves etc.

Here is my solution that does something similar:

*** FeedRankSheet Google Spreadsheet v0.1 ***

This is still very beta and doesn’t entirely work as I’d like. I’ll be making improvements based on your feedback ;)

How to use it

Enter the RSS feed of the posts and comments you want to track, blog address and optionally a comma separated list of Twitter usernames you want to remove from the search. Then open the Script Editor and Run doFeedRank (you’ll need to authorise), finally add a trigger to run daily.

What it does

Example output [click to enlarge]Each time the script runs it gets the latest share counts for posts via the sharedcount.com, combines it with the comment feed and Twitter search results and generated an email to send to designated people (click on image for example output).

How it works

Most of the script is just data reading and writing. The clever bit is using Google Sites pages as a template for the email. Here is the page for the email wrapper and this page is the post share count template.

The thing that really surprised me is that SitesApp.getPageByUrl can get any public Google Sites page allowing you to do Page calls like .getHtmlContent() even if you don’t own it.

Things to improve

  • Exceeding maximum execution – I might need to optimise the code as I was getting timeouts when running as a trigger.
  • Deltas – It would be useful to include individual share count increases on daily updates (eg Twitter 9(+3))
  • I also have a sneaking suspicion that reading the posts from the spreadsheet rather than accessing the raw feed xml using apps script might be a problem. I need to run over a period of time to get data.