Introduction
A US-based non-profit philanthropic organization — working to reduce poverty and build resilience in rural Ethiopia by investing in economic empowerment through agricultural training and loans — sought to:
- Track the outcomes and impact of their programmatic work
- Improve operational efficiency
- Optimize costs
- Allow donors to see the full impact of their donations
The client initiated a project with HCLTech to make the necessary developments to their data management and reporting practices.
The Challenge
Inability to leverage a large volume of data to support effective decision-making The client generates a large volume of data during its day-to-day operation, but the management team was dissatisfied with their ability to take full advantage of this data to support decision-making. The client’s legacy reporting system — slow, manual and Excel-based — made it inefficient to monitor the activities of field workers and gain insights about the loans and recipients they were monitoring.
Related programmatic data from implementation partners was captured in a separate internal system built on the Drupal platform, which didn’t natively support data aggregation, reporting or visualization.
The Objective
Data platform modernization
To achieve the desired operational improvements, the client required a better data infrastructure, more effective data visualization tools and user training to familiarize staff with best practices for self-service analytics. The new reporting platform also needed to be able to dynamically accommodate new survey categories — e.g., baseline/midline/endline assessments — and incorporate additional source systems in future phases of work with minimal additional development.
The Solution
Extensive digital transformation
HCLTech first reviewed the client’s data architecture and business requirements, and then conducted a tool assessment for the organization’s data analytics technology stack. During this work, HCLTech suggested adopting various technologies that would allow the organization to gain access to the desired insights.
The HCLTech team also proposed a comprehensive data and analytics framework for fast and accurate data visualizations. The client accepted HCLTech’s plan for a project comprising a cycle of ETL and data engineering development and dashboard building, followed by multiple rounds of optimization.
- Delivered a highly scalable cloud-based solution comprising Azure as the cloud platform, a Snowflake-hosted reporting data mart and Matillion as the ETL tool
- Deployed custom Python code to complement out-of-box Matillion features to enable the dynamic adoption of new questions and categories during future iterations of the data collection process
- Introduced Tableau in place of Excel sheets
- Duplicated and isolated dashboards using a new access control system to reduce costs by eliminating the need to build dashboards for every team of users.
- Trained staff to work autonomously and effectively with Tableau Server and Desktop, Azure, Snowflake and Matillion
- Introduced dashboard-building best practices to further enable autonomous work and cost savings
The Impact
An integrated system for strategic planning
The team used a cyclical development process which enabled the client to gain access to new and deeper insights after just a few days of work. By the end of the handover period, the solution enabled the granular monitoring of the performance of multi-year loan programs in multiple geographical regions, with needed insight into the activities of field workers and the loans and households they support.
In addition, the solution enabled data aggregation, reporting and visualization of project-based data captured in the client’s Drupal database. This has greatly helped the client to optimize their operations by increasing the capacity for program evaluation and insight generation and empowered the organization to detect operational anomalies, which will help ensure better support for local communities.
