Within the ColaLife initiative, we started using Android tablets during support visits to Kit Yamoyo retailers in mid-2014 (see Take a Tablet). This did two things. Firstly, it helped standardise these support visits; to make sure that key things were checked and asked: Was the product displayed well? Did the retailer have a poster displayed? Secondly, it collected key information such as stock levels, sales, prices etc.
Data started accumulating at a rapid rate, but analyses took a long time to do and so were infrequent. Even when these analyses were done they tended to be presented to project management staff and not to the people on the frontline using the tablets and collecting the data. So, we sought help from DataKind who “harness the power of data science in service of humanity”. In short, DataKind link data scientists with organisations like ColaLife to help with data issues. DataKind introduced us to Abbott Katz who is an Excel genius and the only Spreadsheet Journalist I am aware of. Anyway, Abbott worked with me to produce our first dashboard which was released in Jul-15.
It took blood, sweat and tears to produce this first dashboard and get it out (see it full-size on Flickr). The equations in it had functions in them that I didn’t even know existed! For example:
=IFERROR((COUNTIFS(‘Raw Data’!L:L,O23,’Raw Data’!AP:AP,”>0″,’Raw Data’!AO:AO,1,’Raw Data’!AL:AL,1))-Z23,”–“)
!! However, with Abbott’s help these soon became second nature to me and we now had a mechanism for analysing key performance indicators at the click of a button – quite literally. Analyses that took days now took a few minutes (depending on the speed of your internet connection). This meant that we could easily feedback key information to the frontline. However, little notice was taken of these dashboards until two things happened:
- We made the dashboard more granular – the first dashboard analysed things by Province where multiple teams may be working – we needed to analyse by District where individual teams operated.
- We brought all fieldworkers together from across the country to discuss and agree ways of working, what success looked like and how we were going to measure this (key performance indicators)
Our marketing and distribution consultant – Ralf Siwiti – in a ‘retailer visit’ role play with the then newly recruited Charity who became one of the frontline workers supporting retailers in Lusaka District
The fieldworker meeting happened on 18-Feb-16 and was reported here. Looking back now this was a transformational event. For the first time, frontline staff became clear about their precise role and how their performance was to be assessed. To coincide with the workshop, Release 3.0 of the dashboard came out, showing performance by team (District). Click on the image to see Release 3.0 full-size on Flickr).
Release 3.0 of the Dashboard. Click here to view full-size on Flickr
Since then, the dashboard was produced every Friday for the duration of the initiative and they were published monthly online here: https://colalife.org/dashboards.
The dashboards had a huge motivational effect, with the different district teams comparing their performance and seeking to learn from others who were performing better than than they were.
A key feature of the tablet system was the ability to collect retailer location information and this enabled ‘route planning’ across all districts as a means of meeting retailer contact targets (meeting all retailers face to face every month, or every other month in Lusaka).
The release of the dashboard on Friday mornings has become ‘an event’ for everybody. Even the Lusaka driver comes into the office at around 10am and says, “Is the dashboard out? How did we do?”.
Over time the dashboard got more and more sophisticated. Release 5.0 was issued in Sep-16. This analysed the percentage of contact with retailers that is face-to-face (as opposed to by phone) and the presence (or not) of point of sales materials. Click on the image to see Release 5.0 full-size on Flickr).
Release 5.0 of the Dashboard. Click here to view full-size on Flickr
Although Release 5.0 (immediately above) is more sophisticated than Release 3.0 (above Release 5.0), the bottom two rows show the same KPIs. In both cases the blue lines are the targets. At a glance you can see how things have dramatically improved.
Thanks are due to the frontline staff who rose to the challenges given to them, to Ralf Switi for guiding us in what we needed to be doing and to Abbott Katz for providing the means to measure performance and feedback to those who can make the difference (the frontline staff).
I have gone on to produce dashboards for the Cotton Sector Support Programme in Tanzania and for Pharmanova – the manufacturer of Kit Yamoyo – in Zambia.
[A variant of this blog post was first published on colalife.org on 19-Sep-16.]