Google+

Thursday, March 12, 2015

How to create an M&E dashboard using Google Apps

Last year we did a fair amount of work with IDRC to set up a KM platform for a new collaborative research program. In the follow up to that project, we developed an M&E system for the program, using the same technology infrastructure used to build the platform itself - the Google Apps for Business.

After last week’s case study on building the R4D dashboard with Tableau public, in this post I’m presenting how to set up a M&E system and dashboard using a combination of various Google Apps and free third party tools. This post is very much an overview of the process and the final product we delivered. In the next blog posts in this series I’ll look at the specific tools used from a more technical perspective.
M&E Dashboard - Click to enlarge

Who needs a dashboard, and why? 


This IDRC program is made of 4 different research consortia and the IDRC program team in Canada. Further, each consortium works on a specific issue related to climate change and adaptation. In doing so, it brings together different organizations geographically dispersed. So as collaboration is the basis of the program the M&E system had to follow this principle. So our brief was to “design platform-based, collaborative tools to collect monitoring data on up to eight key indicators in the Monitoring Framework.”

Ultimately, these data had to be brought together into a M&E dashboard that could be easily shared with donors and program leads as a link or quickly printed in PDF at regular intervals. As for the R4D dashboard, also this dashboard had to provide a “snapshot of progress against key indicators in the program's monitoring framework using data entered by consortia and the IDRC Team.”

So what are these indicators?

What to measure? Theory of change and monitoring framework 

The program M&E working group had already produced a solid Theory of Change with three clear objectives; for each they had defined the dimensions and potential metrics to be included in the M&E system. This made our job easier as it was clear from the outset what had to be measured and for what purposes. So we just had to help the team unpack a bit the various metrics and dimensions, and define the exact indicators and values to be tracked in the system:
  • Research outputs and pilots, including indication of type of outputs, authorship (gender and country), quality of outputs and their accessibility (whether peer-reviewed and/or openly accessible on the web), etc... 
  • Web traffic, social media and engagement data, such as web sessions and downloads, Twitter followers and number of conversations and Tweeps around specific accounts and Hashtags, media tracking, number of events and participants rating, etc. 
  • Grants and awards distributed, including gender and location of recipients.
M&E Dashboard - Click to enlarge

When and where? Data collection process and storage 

While the system (and resulting dashboard) was planned to be updated quarterly, we agreed on the principle that data collection would be automated when possible, and manual when other solutions were not at hand. As a result:
  • Data around web traffic and social media are automated or semi automated, using a series of third party tools and applications (I’ll talk about this specifically in the next blog post) 
  • Data around research outputs, pilots, grants and awards are entered via users’ submission forms, using Google Forms. While forms can (potentially) be submitted by anyone who has a user account on the KM platform, in reality specific users for each consortium are responsible for this process, while others are responsible for quality control, to ensure that entries are complete and there are no duplicates. 
Regardless of how the data are collected - manually, automatically or semi-automatically - they all feed into one of the 3 separate log files set up for the the three objectives in the Theory of Change. Google Spreadsheet are used for these log files, and the appropriate sharing and editing permissions are in place.

How to display the data? Platform, design, prototype and production 

On the basis of an initial sketch of the dashboard produced by the IDRC team, we populated the log files with dummy data and produced two different prototypes, one using Tableau Public and one using Google Charts and publishing them into a Google Site. We agreed to use Google tools to avoid adding another layer of complexity to the system and keep it all inside Google Apps. Additionally, as Charts are generated by the log files, when the log files are updated so are the charts on the live site, which is a great short-cut, cutting down work on updating the dashboard.

Similarly to the R4D dashboard, this program dashboard presents a tabbed navigation at the top, with one tab for each of the objectives monitored in the framework. This way we could present objective-specific charts, tables and figures in a clean, uncluttered interface.

Additionally, the main tab of the dashboard presents what we called ‘curated content’, such as a selection of recent publications, blog posts or key events that are hand picked by the dashboard administrators to highlight specific information.
M&E Dashboard - Click to enlarge

What next? Possible platform iteration and next blog posts 

This dashboard became at the beginning of 2015 and its second update is planned for the end of this quarter, so it’s too soon to evaluate it and think about possible iterations. However, feedback received from users has been positive so far and the system delivers the required information to the different target users.

In my opinion, a possible way to improve it would be to add filters and controls to the charts currently published on the dashboard, so that users can interact with them, browse for specific period of time, make comparisons, and get more out of this visual representation of data.

To do this requires working with Google Apps Scripts, JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services. I’m not a programmer but I like learning new things and findings solutions that others have already implemented. So also in the current version of this dashboard I’ve made use of Google Apps Scripts to collect data and to copy them from one spreadsheet into another.

If you are interested in what Apps Scripts I’ve been using and what they can do for you, subscribe to the blog and sit back till you’ll get my next post in this series - or share your experience in the comments below here.