Blog

The Digital Agency for International Development

Case Study: Creating a custom M&E solution

By Chris Wilson on 12 February 2014

I attended an interesting webinar by Npoki, where IPAS presented their multi-year project to implement a complete M&E system from scratch.

Unfortunately the NPOKI website doesn't allow me to link to individual events, so here's their summary:

Creating a custom M&E solution has very unique risks and rewards that are different from the approach of selecting a pre-built, solution out-of-the-box. We will explore the decision process and possible risk factors in executing this custom development plan. We will hear from Ipas, which in early 2012 selected implemented Microsoft CRM as the software platform for both its traditional business development functions as well as its monitoring and evaluation functions. The Ipas M&E staff will present the results and major challenges of the two year M&E project.

You can download the presentation and recording of the webinar.

Here are my notes and learnings:

IPAS was previously storing most of their M&E data in spreadsheets. Their main complaint about this system was that it "wasn't project-based". I gather from questions that they wanted to be able to create relationships between entities (e.g. projects and trainings) in their data, and spreadsheets wouldn't allow them to do that easily. Also, some essential reports took a very long time to produce (e.g. weeks of manual work).

At the start of the project, they worked with consultants for a long time (6 months?) to map their existing processes and data, redesign everything, and create the entity-relation diagram.

I was initially surprised that they decided to use a CRM system, Microsoft Dynamics CRM, to do their M&E. I've been thinking of M&E as tracking targets and progress towards them. However, they see it quite differently.

The M&E system (which they call Terra) is basically their business database. It allows them to track everything they do: sites, people, groups, meetings, trainings, etc. It combines information that used to be held in separate spreadsheets, and overlaps to some extent with their grant management system and another internal database.

Their reasoning for this, and one of the benefits that they get from the new M&E system, is that it gives them live information about their entire business at all levels. Project managers can and do use this information to help manage their projects in real time. So there's much more incentive for the people who enter the data to enter it, because they can see and use the results immediately. It's also part of their core daily business.

This is both much more powerful and much more integrated than the kind of M&E systems we've been thinking about so far, which are based on Logical Frameworks (log frames).

Dynamics facilitates this by being very flexible. They've customised it extensively, adding new tables, fields and forms, with a lot of help from consultants and a lot of learning how the system works, to collect the data that they want and need. They also built a custom grants management system using it.

Now that their data is in Dynamics, they can see simple graphs of every table automatically. For example they can click on the trainings table, and while browsing the raw data, they can see a graph of trainings by year on the right. They can also change the breakdown parameters of the graph (e.g. trainings by region) instead, and drill down through the data.

Access to live data has some pitfalls: sometimes people use data that hasn't been checked and cleaned yet, and either draw wrong conclusions or ask useful questions that help to spot errors. (Some quality indication flags to attach to the data might be useful here? E.g. draft/checked/public). They also had to define strong security rules to limit access within the organisation to sensitive data relating to individuals and their health. They appreciated the flexible security model of Dynamics.

They opted to develop the system using a phased, waterfall approach. I'm not sure how they obtained buy-in and feedback from all the people who would be using the system eventually. They also took a top-down approach to designing forms for data collection. One of the questions that I asked, that they didn't have time to answer during the webinar, was to compare this approach to agile, and whether they were happy with the results they obtained.

The flexibility of Dynamics mitigates one risk of top-down design, which is that the resulting design doesn't meet the needs of all the users, by allowing users to create their own tables, fields and forms, to collect data unique to their specific project.

The implementation was challenging because it took a long time. In particular, the data migration period, during which they ran both systems in parallel (the old and the new) was supposed to take 1 month but actually took 10. This parallel period is difficult for any organisation: it offers opportunities to double-check the data in the new system against the old, but also scope for errors and frustration, as users must deal with the disadvantages of both systems and spend more time entering data.

IPAS had a separate user testing phase during the implementation. They gave us some of their learnings from this process: pilot the system, pilot your training programme, write scripts for user testing.

Apparently Dynamics has limited reporting and graphing features built-in, and they recommend using SQL Reporting Services for more advanced reports. The data can also be exported to Excel, and even linked live, so people can create reports using spreadsheets and have them update automatically as the data changes.

IPAS is starting to experiment with mobile data collection. Some of their forms use JavaScript for automatic totals, and this isn't always compatible with mobile devices, but the next version of Dynamics will apparently remove the need for this custom JavaScript, and better support mobile devices.

The system also has an offline mode using "Outlook client", where users can replicate all or part of the Dynamics database into a local SQL Server installation, and access it offline through Outlook. Apparently they have used this feature a bit. It's not clear whether it's usable in situations with no usable Internet connection, or what features might be missing when using this mode compared to online Dynamics.

Summary

  • It's a big and complex database with a web interface
  • They use it every day for project management
  • Access to live data and graphs across the organisation are very popular
  • They spent significant time and money to get it right
  • The flexibility of the system allows them to customise it further in future
  • Data migration (while running both systems in parallel) took 10 times longer than they expected
  • They will be using offline mode and mobile data collection on future