Five years of impact data, finally in one place

The problem

Every year, conservation projects across Fauna & Flora’s portfolio report on what they achieved. Analysts pull those reports into a large Excel workbook, validate the numbers, and feed them into the organisation’s Conservation Impact Report. It works. But each year stood alone. The export changed shape from one year to the next, the same project could appear under slightly different names, and much of the context behind the numbers sat in cell comments or in people’s heads.

The question wasn’t whether the data was good. It was whether anyone could ask it a question that spanned more than one year.

What we did

We started with scoping – mapping the existing process and appraising the options – before building anything. The recommendation was a relational database in Airtable: familiar enough for analysts who live in spreadsheets, structured enough to hold five years of linked records.

The build brought 2021 to 2025 together across nine linked tables, with core records for projects and sites at the centre and each year’s reports hanging off them. Most project codes carried cleanly from year to year. The ones that didn’t were the interesting part: projects that had split in two, codes that appeared to have been reused, and site names that drifted in spelling and formatting. Rather than force an automatic match, we built in a deliberate reconciliation step, so a person makes the call where the data is genuinely ambiguous.

Two lessons shaped the schema. Where people are counted, totals, men, and women are held as three independent fields, because many projects report a headcount without a gender split, and a derived total would quietly misrepresent them. And detecting narrative answers by their content rather than their column headers recovered around 6,000 text responses that a header-based import had missed.

Every field was then validated against the original source files for all five years before handover.

The outcome

Fauna & Flora now has a single, linked record of five years of conservation impact, with a visualisation portal on top and a written handover so its own team can extend the system safely. It is the foundation for what comes next: cleaner data entry, forms that carry last year’s answers forward, and in time a public-facing impact hub.

Our approach, in brief

Scope before building. Keep complexity proportionate. Fix data issues as they surface rather than parking them. And write the handover so the client’s own team can maintain the system after we step back.