The Story
In Latin America, MCP (https://mcpelsalvador.org.sv/) is a collegiate body, with a multisectoral public-private composition, responsible for coordinating joint efforts in terms of financing from the Global Fund or other financing bodies on the subject of HIV/AIDS, tuberculosis and malaria.
Because of it's complex composition and existing internal processes, KPI creation, monitoring and strategic decisions were hard to come by and weren't really standardized. The Global Fund requested unified data processing, monitoring and public facing reports to comply with their regulations and the MCP had to step up.
The Problem
Fragmented decision making.
No experience with databases, statistical analysis or real time KPI monitoring.
3 different institutions as data sources segmented into several sub data sources.
Outdated Microsoft Excel versions as their only data procesing tool.
No agreement on data presentation, branding or any institutional distinction.
No definition of target audience for report creation and interpretation.
No budget for recurring billing tools.
Process
Gathered all the stakeholders to get a feeling of what they needed aside from the Global Fund's requirements.
Measured the lowest level of KPI understanding and experience to shape the dashboards based on it.
Segmented each project, data source and visualization to easy to disgest charts and tables.
Unified the data gathering process across all the institutions with free tools.
Trained all stakeholders on data input, interpretation and follow up actions.
Challenges
No agreement on how data visualization should be in any institution.
Inmense scope creep due to unexpected bureaucracy related changes.
Replace pen and paper and Excel culture with new systems.
Long waiting time between thanks to cross-institutional decision making processes.
Lack of any tech, math or statistical understanding of some of the decision making stakeholders.
Samples
Final version published on the web: https://mcpelsalvador.org.sv/tablerodemandolookerstudio/
Results
Consolidation of all HIV/AIDS and Tuberculosis KPIs into a single dashboard.
Unified data gathering and data cleanup processes for all institutions.
Modernized all data inputs.
Managed to complete the project with a 100% use of free tools.
Dashboard approved by all the MCP governing bodies.
Self service tech support after extensive training on dashboard logic and troubleshooting.
Our Role
Involved in all high level decisions.
Designed the data input and cleanup process.
Identified the target audience for the reports at a high level view.
Managed the organization’s cultural transition to technology.
Designed and tested all the tools to be almost fool proof.
Helped all the organizations to make the dashboards public.
Platforms
Looker Studio, Google Spreadsheets, Google Forms, Website