Spreadsheet-ready evidence base covering 120 case studies
Back to Case Studies
Child Poverty Evidence Workflow for a UNICEF Report Project in Zambia
A UNICEF-linked report project in Zambia needed to turn 120 narrative case studies into consistent, traceable, report-ready evidence through a spreadsheet-first qualitative synthesis workflow.
I built a spreadsheet-first qualitative synthesis workflow that standardised extraction across ten themes, used AI only inside a guarded review process, and left the team with outputs they could query, check, and reuse.
What this project delivered
Ten-theme schema and data dictionary for standardised extraction
AI-assisted coding workflow with quote-per-claim guardrails
Reporting-ready tables, summaries, and draft-ready outputs
SOP plus two compact handover training sessions
Three-month engagement
Workflow delivered and handed over
Evidence database, data dictionary, AI-assisted coding workflow, reporting tables, handover SOP
Cut analysis time to about 15 minutes per case and saved an estimated 120 analyst hours across the study.
Project overview
The problem
A primary contractor on a UNICEF child poverty report project in Zambia needed to process 120 narrative case studies on female-headed households. The material was useful, but it was too slow to review manually at the level needed for reporting. Each case had to be read, coded, compared, summarised, and prepared for use in report sections. The real problem was not only analysis time. It was consistency, traceability, and handover across a large qualitative evidence base.
Context
The project supported a UNICEF report focused on female-headed households in Mongu and Kasama, where the research team had rich narrative material but needed a faster way to extract, structure, and reuse evidence across a multi-theme study. The engagement ran for three months and had to support credible reporting inside a review-heavy environment.
Before and after
- Before: The team had rich narrative case material but no fast, consistent way to turn every case into comparable evidence. Theme handling could vary between analysts, report writers still had to search through long case material, and useful claims needed a clearer route back to quotes and case IDs.
- After: Each case moved through the same ten-theme structure. Claims, quotes, missing values, ambiguity flags, coded fields, location comparisons, and reporting outputs sat inside one spreadsheet-first evidence base. Writers could work from structured tables and plain-English queries without losing the link back to source material.
Constraints
The work had to speed up analysis without weakening evidence quality. Each case needed to map to the same ten-theme schema, useful claims needed quote and case ID support, missing or ambiguous information had to be handled consistently, and the workflow had to be practical for a spreadsheet-based team rather than built around specialist tools only analysts could run.
What the team needed
- A shared schema and data dictionary that could standardise extraction across ten themes
- A coding workflow that captured only supported claims, used nulls for missing information, and flagged ambiguity for review
- A spreadsheet-based evidence base that non-technical team members could query, review, and reuse
- Reporting outputs that matched the study format and could be traced back to case ID, quote, and cell range
- A handover process with SOPs and training so the team could run the workflow independently
If your team is losing time finding, checking, and reusing evidence, model the search and review time savings before deciding whether the fix is structure, retrieval, or analysis capacity.
Build
What I built
A spreadsheet-first qualitative synthesis workflow with a ten-theme schema, AI-assisted coding support, quote-per-claim guardrails, reporting tables, a plain-English insight interface, QA checks, SOPs, and compact handover training.
Named systems and workflow pieces
- A ten-theme schema with defined fields, naming rules, formats, and guidance for missing values
- An AI-assisted case coding workflow for first-pass extraction and querying, with quote-per-claim guardrails and human review points
- A clean evidence database built in Excel and Google Sheets
- Spreadsheet logic for rollups, location comparisons, ranked lists, and filtered analysis
- A reporting-focused Custom GPT for plain-English querying, summary support, table outputs, and formula visibility
- QA checks, reconciliation checks, traceability rules, versioning logic, and access-control guidance
- A simple SOP and two compact training sessions for handover
Where this connects to the services
This case study sits mainly under Traceable Evidence Workflow Support because the project already had narrative case material and needed a traceable evidence base with consistent fields, themes, quote links, review checks, and reporting outputs. It also connects to Data Use, Reporting & Communication Systems because the coded evidence had to become tables, summaries, draft-ready sections, plain-English queries, and handover material for the report team.
Visual proof
One spreadsheet-first workflow standardised 120 case studies
The system moved from schema definition to coding, querying, and handover in one controlled chain instead of leaving analysts to improvise theme handling case by case.
Standardise
Defined a ten-theme schema, field rules, and data dictionary that every case had to follow.
Code
Processed cases through an AI-assisted first-pass extraction workflow with quote-per-claim guardrails.
Query
Turned the coded evidence into spreadsheet rollups, reporting tables, and plain-English querying.
Handover
Wrapped the workflow with QA checks, SOP guidance, and training so the team could keep running it.
Narrative case study to coded record to reporting table to handover-ready workflow
Execution
How it worked
The workflow moved from raw material to usable output through a short sequence of controlled steps.
Process
- 01
Defined the ten-theme schema, field rules, and data dictionary for the full study scope
- 02
Processed cases one at a time through an AI-assisted first-pass extraction workflow with explicit guardrails
- 03
Flattened structured outputs into Excel and Google Sheets to create a query-ready evidence base
- 04
Added spreadsheet formulas and rollups for comparisons, ranked lists, and reporting tables
- 05
Built a plain-English querying layer for report writers on top of the evidence base
- 06
Added governance checks and handover guidance so the team could keep using the workflow
Deliverables
Outputs
These were the named assets, dated deliverables, and working materials left behind by the project.
Working outputs
- Standardised evidence base in Excel and Google Sheets covering 120 case studies
- Ten-theme data dictionary and schema documentation
- AI-assisted first-pass qualitative coding workflow with review guardrails
- Reporting-ready tables, summaries, and draft-ready sections
- Plain-English Custom GPT insight interface
- SOP and training materials for team handover
Outcome
Result
Cut analysis time to about 15 minutes per case and saved an estimated 120 analyst hours across the study.
Main result
- Cut processing time from 60 to 90 minutes per case to about 15 minutes
- Saved an estimated 120 analyst hours across the full dataset
- Improved consistency across themes and locations with less interpretation drift
- Made it easier for non-technical writers to generate and verify reporting-ready summaries on demand
- Improved auditability by linking claims back to source excerpts, case IDs, and exact spreadsheet locations
- Left behind an SOP and handover process the team could keep using after delivery
AI is most useful in qualitative evidence work when it sits on top of a clear schema, source traceability, and human review.
What this proves
- Qualitative case study synthesis across a large narrative evidence set
- Schema-first extraction using a ten-theme structure and data dictionary
- Spreadsheet evidence architecture that stays usable for non-technical writers
- AI-assisted coding with quote-per-claim checks and human review points
- Reporting-ready tables, summaries, and plain-English evidence querying
- QA checks, reconciliation logic, SOPs, training, and workflow handover
Qualification
Best fit
These are the situations where this kind of evidence workflow tends to be the strongest fit.
Who this is best for
- Research teams, evaluation firms, and donor-funded contractors with many interviews, case studies, focus group notes, or open-text responses
- Projects where different analysts may code qualitative material differently
- Report teams that need summaries, tables, and source-linked findings
- Teams that want AI support but cannot risk unchecked outputs
- Workflows that need to run in Excel, Google Sheets, or another familiar tool
- Assignments that need handover material, not a black-box analysis process
Relevant services
Service stack connected to this case study
This case study sits inside the same delivery work, service logic, and practical outcomes shown across the site.
Traceable Evidence Workflow Support
Turn interviews, submissions, case studies, survey comments, documents, and field notes into coded evidence, quote banks, synthesis tables, findings, recommendations, and report-ready outputs.
Data Use, Reporting & Communication Systems
Use structured data in reports, dashboards, internal tools, public microsites, applications, presentations, annual reports, and decision-support workflows.
Similar case studies
Similar case studies
These are the closest delivery examples on the site, based on the same service mix, adjacent workflow logic, or a very similar problem shape.
Calculators
See what a similar workflow could do for your team
Use these calculators if narrative case studies, manual coding, quote checks, or analyst capacity are slowing your evidence workflow.
FAQ
Case study questions
Short answers for readers checking whether this delivery example matches their own project.
What problem did this case study address?
A primary contractor on a UNICEF child poverty report project in Zambia needed to turn 120 narrative case studies into consistent, traceable, report-ready evidence. The existing process was too slow, theme handling could vary between analysts, and writers needed outputs they could use without manually reviewing every raw case again.
What was built?
A spreadsheet-first qualitative evidence workflow with a ten-theme schema, data dictionary, AI-assisted coding process, quote-per-claim guardrails, Excel and Google Sheets evidence base, reporting tables, a Custom GPT insight interface, QA checks, SOP, and handover training.
Which service does this best connect to?
The primary service is Traceable Evidence Workflow Support. The case also connects to Data Use, Reporting & Communication Systems because the structured evidence was turned into reporting tables, summaries, draft-ready sections, and a plain-English querying layer.
Did AI replace the analysts?
No. AI supported first-pass extraction, coding suggestions, summaries, and evidence querying inside a controlled workflow. Claims still needed source links, quotes, case IDs, review checks, and human judgement.
What was the practical result?
The workflow reduced processing time from roughly 60 to 90 minutes per case to about 15 minutes per case and saved an estimated 120 analyst hours across the study. It also improved consistency, traceability, and handover.
Who is this case study most relevant for?
This case is most relevant for research teams, evaluation firms, donor-funded contractors, UNICEF and UN-linked project teams, report writers, and lead consultants working with large qualitative evidence sets.
Let's talk
Need a similar workflow?
If your team is dealing with the same kind of information, reporting, or evidence bottleneck, send a short brief and I can assess fit quickly.