Global Health
From Scattered Spreadsheets to Automated Multisector Monitoring in Ethiopia
When UNICEF Ethiopia needed to track more than 55 humanitarian activities across five sectors and three drought-affected regions, they turned to Ona Insights to replace manual reporting with an automated pipeline that runs from an offline field form to a live dashboard.
Introduction
UNICEF Ethiopia is leading a multisectoral response to the pressures created by prolonged drought, internal displacement, and economic shocks. With support from KfW Development Bank, the programme runs more than 55 activities across child protection, education, health, nutrition, and WASH in the Afar, Somali, and Oromia regions. Every one of those activities generates numbers that somebody needs to see.
Those numbers were arriving through a patchwork of spreadsheets, exports, and a Power BI dashboard that was slow to change and difficult to access. Ona Insights was contracted to build a digital system covering the full journey of a data point, from offline-capable field forms through automated pipelines to dashboards stakeholders can open themselves. The result is a pipeline built on InForm, Airbyte, PostgreSQL, dbt, and Akuko, with three live forms and sector dashboards that update on a schedule rather than on request.
The problem: good data that could not travel
Programmes of this size rarely lack data. They lack data that can move. Indicator results were reported by field teams, consolidated manually into spreadsheets, then rebuilt again into dashboards. Each hop added delay and the possibility of error, and nobody could be certain that the figure on a slide matched the figure in the source file.
Three things made this harder than a standard reporting problem. Connectivity in the field is intermittent, so any system that assumed a live connection at the moment of capture would fail where it mattered most. Five sectors that share almost nothing structurally still needed to be seen side by side. And years of prior sector reporting had to survive the transition, or every trend line would break.
Our approach
Ona Insights did not start with technology. Over June and July 2025 the two teams held weekly sessions bringing together ICT, WASH, programme coordination, and monitoring colleagues, with fourteen people across both organisations contributing to requirements. We reviewed the existing indicator list and the outgoing dashboards so the new system built on prior work rather than discarding it.
Three principles came out of those sessions:
- Build on what UNICEF already owns. InForm is UNICEF's own collection platform, already independently security audited, so we extended it rather than introducing another tool.
- Choose open standards. XLSForm, Airbyte, dbt, and PostgreSQL are all open, which means everything written for this engagement transfers to UNICEF at handover.
- Keep the layers separate. A form update should not require a dashboard rebuild, and a new indicator should not require a new database.
The technology
The system moves data through four stages, each independent of the others:
| Stage | Tool | What happens |
|---|---|---|
| Collection | XLSForm on InForm | Three forms capture sector indicators, site registrations, and construction checks. Validation runs at the point of entry, and forms work offline. |
| Ingestion | Airbyte | Connectors pull submissions into a PostgreSQL database on AWS on a set schedule. No manual exports. |
| Transformation | dbt | SQL models clean the raw data, reshape it for analysis, and join it to indicator labels and targets. |
| Visualisation | Akuko | Interactive dashboards show indicator results by sector, filterable by region, zone, and reporting period. |
A second Airbyte connection carries the historical sector reporting from before the pipeline existed, so dashboards can show trends across the changeover. Region, zone, and woreda selections cascade in the forms and use official administrative codes, which keeps the data joinable and mappable.
The layer that does not appear on an architecture diagram is the operational one. Form field names become database columns, which feed the models, which feed the charts, so a careless rename can break a dashboard. A short set of standing rules covers this, and it is the difference between a pipeline that survives its first year and one that quietly stops updating.
Challenges and how we adapted
Live forms behave like production databases. The biggest risk was never infrastructure failure; it was a well-intentioned edit to a form that already had submissions. We wrote an explicit change procedure ending in a single test submission, which catches most problems while they are still small.
Bridging old and new reporting. Rather than migrating history, we brought the legacy reporting into the same warehouse as a parallel stream and joined it in transformation. Charts span the changeover without anyone needing to know where each number came from.
Making the system extensible by the client. A system only Ona Insights can update has a limited life. Adding an indicator is now a form field, a mapping row, and a target, all handled by the UNICEF team. Only complex derivations need us.
Agreeing what to measure took longer than building it. Before a form could be finalised, five sectors had to settle on structure, indicator definitions, and targets. Questions that looked resolved in a workshop reopened once a sector lead saw them in a draft form, and targets needed several rounds of their own: what level they sat at, which period they covered, and who signed them off.
Results and impact
- Five sectors on one platform, replacing sector-by-sector manual consolidation.
- Manual exports eliminated. Reporting lag is now the time it takes to submit a form, not the time it takes to compile a spreadsheet.
- Three live forms feeding analysis-ready tables, with sites and construction checks linked so analysis joins across forms.
- Offline-first collection, so data quality in low-connectivity woredas no longer depends on the mobile signal.
- Self-service access for programme staff, management, and stakeholders under a viewer role.
- Full ownership transfers to UNICEF, including forms, transformation scripts, pipeline configurations, and documentation, with one year of post-deployment support.
Key takeaways
The hardest part of a data pipeline is rarely the pipeline. Agreeing indicator definitions across five sectors took more effort than any of the tooling.
Field names are infrastructure. Naming discipline at form authoring saves far more downstream than it costs upfront.
Design the extension path, not just the system. Making new indicators a configuration change rather than a code change decides whether the client can grow the system or has to wait for a vendor.
Bring the history with you. Preserving prior reporting as a parallel stream cost little and protected every trend line the programme depended on.
What is next?
The system now moves into handover. The focus over the coming months is transferring day-to-day ownership to the UNICEF Ethiopia team, with Ona Insights alongside rather than in front. That means admin-level training on form updates, pipeline runs, and dashboard edits, followed by a period of handholding where the team leads the work and we support from behind.
We are treating handover as a process rather than an event, and one worth learning from. Everything built for this engagement, the forms, transformation scripts, pipeline configurations, and documentation, transfers to UNICEF, along with the requirements should they choose to self-host.
Working on something similar?
If you are running a multisector programme where the data is collected but is not reaching the people who need it, we would like to hear about it. Ona Insights designs data systems around the realities of the context they operate in, including patchy connectivity, deep administrative hierarchies, and teams who need to own the system after the consultants leave.
Get in touch to discuss how we can help design a solution for your context.