Field PDFs stalled board reviews — Azure AI extracts to Airtable & Power BI

Overview

A nonprofit’s new program struggled to measure impact because field data arrived as PDFs and email attachments with inconsistent formats. Analysts retyped figures into spreadsheets, reconciled conflicting entries, and redacted sensitive details before sharing. Intelligex implemented document automation to extract metrics into Airtable, added lightweight validations and a human-in-the-loop review, and rolled results into a board dashboard with narrative context. The board received a consistent view of outcomes backed by source documents, which reduced manual reconciliation and supported clearer funding decisions.

Client Profile

• Industry: Nonprofit (community development and public health)
• Company size (range): Multi-program organization with regional field partners
• Stage: Scaling a new program with early funder reporting requirements
• Department owner: Strategy, Analytics & Executive Leadership (Impact & Strategy Office)
• Other stakeholders: Program Directors, Field Operations, Grants & Development, Finance, IT/Data, Legal & Compliance

The Challenge

Field teams submitted monthly reports as PDFs, scans, and spreadsheets attached to emails. Formats varied by partner. Some used templates; others pasted tables into body text with totals and notes mixed together. Analysts printed or copy-pasted numbers into a master spreadsheet, chased missing fields, and reconciled duplicates when multiple team members reported the same activity. When the board asked for a consolidated view, the team spent days stitching inputs into a deck, and small transcription errors slipped through.

Evidence and narratives were scattered. Photos, attendance sheets, and beneficiary stories lived in shared drives and personal folders without stable links. Sensitive personally identifiable information occasionally appeared in attachments, so distribution required ad hoc redactions. Funders requested consistency across grantees and periods, but the program lacked a central data contract and a repeatable way to capture context without leaking private details.

The organization did not want to replace field tools or introduce a heavy case management system. They needed a way to treat incoming documents as the source of truth, extract structured metrics safely, and present a roll-up with consistent definitions and narrative context that could stand up to funder and board scrutiny.

Why It Was Happening

Inputs were unstructured and standards were informal. PDFs and spreadsheets arrived with different labels, time windows, and beneficiary categories. There was no schema to enforce required fields or guard against out-of-range entries. Duplicates and version drift were common because multiple partners submitted overlapping reports without unique identifiers.

Governance lived at the end. Review happened in the final deck, not at ingestion. Sensitive fields were redacted manually, if at all, and no audit trail tied a board metric to the exact document and extraction decision that produced it. The team optimized for speed under deadline rather than a process that prevented rework.

The Solution

We built a document-to-dataset pipeline that turned field submissions into structured records and wrapped it in a light governance model. Documents were ingested into a secure folder, extracted by an AI document service, and mapped into Airtable with validations for schema, ranges, and duplicates. A human reviewer resolved exceptions with the original document in view and captured rationale. Power BI rendered a board dashboard with filters, narrative context, and links back to evidence. Nothing was replatformed: partners kept sending documents; Airtable became the governed store; and the board used familiar views backed by a consistent snapshot.

• Document extraction using an AI-powered service to parse PDFs and scans, outputting structured fields for program, location, period, and metrics (Azure AI Document Intelligence)
• Airtable as the central dataset with tables for submissions, metrics, partners, locations, and narratives; validations for required fields, picklists, and range checks (Airtable Help)
• Deduplication logic using partner, period, and activity keys; flags for overlapping submissions and late corrections
• Human-in-the-loop review queue within Airtable Interfaces for low-confidence extractions and policy-sensitive fields
• Redaction and masking rules for sensitive data before storage in the analytics tables
• Power BI board dashboard with roll-ups by program, geography, and time; drill-through to submission records and narrative excerpts (Power BI)
• Snapshot policy that freezes the dataset prior to board meetings, with version tags and change logs
• Role-based permissions: field reviewers see submissions; Strategy sees validated metrics; the board sees aggregated views with curated narratives
• Lightweight definitions catalog for key metrics and cohort rules, owned by Program and Strategy
• Evidence links from dashboard tiles back to the source document and review notes for auditability

Implementation

• Discovery: Collected representative field reports and attachments; cataloged common metrics, labels, and narrative elements; identified sensitive fields; mapped current board reporting cadence; and reviewed typical reconciliation challenges with Program and Development teams.
• Design: Defined the Airtable schema for submissions, metrics, partners, and narratives; authored validation and deduplication rules; selected extraction models and confidence thresholds; designed the human review flow; and outlined the Power BI dashboard structure with narrative context and evidence links.
• Build: Configured document intake to a secure folder and wired extraction to produce normalized JSON; implemented Airtable tables, validations, and interfaces for reviews; added masking for sensitive fields; set up Power BI models and the board dashboard with drill-through; and built a snapshot routine with version tags and change logs.
• Testing and QA: Ran historical submissions through the pipeline; compared extracted metrics to prior board decks; tuned validations and dedupe rules to reduce noise; verified redaction behavior; and rehearsed the review process with Program staff on low-confidence extractions.
• Rollout: Operated in observe-only mode for one cycle while the legacy spreadsheet process continued; after validation, switched to the Airtable dataset as the source for the board dashboard; kept a manual override path for unusual submissions with documented review notes.
• Training and hand-off: Delivered quick guides for field reviewers on exception handling and narrative capture, for Strategy on definitions and snapshots, and for the board on reading the dashboard and accessing evidence links. Assigned stewardship for the metric catalog and intake templates.

Results

Board materials drew from a single, governed dataset. Metrics rolled up consistently across regions and partners, and each tile linked to the underlying submission and review notes. Narrative context accompanied the numbers, so discussions balanced quantitative progress with on-the-ground realities. Fewer meetings were spent reconciling spreadsheets or debating labels, and funding decisions referenced evidence with clear provenance.

Operationally, analysts moved from transcription and email chasing to reviewing exceptions and improving templates. Sensitive fields were handled consistently, and the snapshot policy gave everyone confidence that the board review reflected a controlled cut of the data. Program teams saw where submissions needed coaching, and partners received clearer templates aligned to the data contract.

What Changed for the Team

• Before: Metrics were retyped from PDFs and emails. After: Document automation extracted fields into Airtable with validations and review notes.
• Before: Duplicates and late corrections caused confusion. After: Deduplication keys and change logs clarified which record counted and why.
• Before: Sensitive details required manual redaction. After: Masking rules applied at ingestion kept analytics tables safe to share.
• Before: Board decks stitched screenshots and anecdotes. After: A Power BI dashboard presented governed metrics with curated narratives and evidence links.
• Before: Definitions drifted by partner. After: A lightweight catalog standardized labels and cohorts across submissions.

Key Takeaways

• Treat field documents as sources; automate extraction and validate against a shared schema to reduce rework.
• Capture exceptions with a human-in-the-loop review next to the source document; decisions should be visible and reusable.
• Mask sensitive fields at ingestion and share aggregates by role to keep impact conversations safe and productive.
• Freeze a snapshot before board cycles and link metrics to evidence so choices rest on verifiable inputs.
• Keep tools simple—Airtable for structure, a document AI for extraction, and a familiar BI front end—layered with light governance.

FAQ

What tools did this integrate with?
We used an AI document service to parse PDFs and scans into structured fields (Azure AI Document Intelligence), stored and validated records in Airtable, and delivered roll-ups and narratives in Power BI. Documents continued to arrive via email or shared folders; the pipeline met the process where it was.

How did you handle quality control and governance?
Airtable enforced required fields, picklists, and range checks. Deduplication keys prevented double counting. Low-confidence extractions and sensitive fields routed to a reviewer who recorded decisions next to the source. A metric definitions catalog aligned labels across partners, and a snapshot policy froze the dataset before board meetings with change logs for transparency.

How did you roll this out without disruption?
We ran the pipeline alongside the existing spreadsheet workflow for one cycle, reconciled differences, and tuned validations. Once the team was comfortable, the Airtable dataset became the source for the board dashboard. Partners kept using their current templates while we provided light guidance to reduce edge cases.

How were PDFs processed and exceptions handled?
Documents were ingested into a secure folder and processed by the extraction service. The output populated Airtable with a confidence score per field. Records below threshold or with policy-sensitive content entered a review queue. Reviewers saw the original document side by side with proposed values, made corrections, and captured rationale that traveled with the record.

How did you protect sensitive information?
Extraction applied masking to sensitive fields before they entered the analytics tables. Access to submissions and detailed records was limited by role, and the board dashboard exposed only aggregated views and curated narratives. Evidence links pointed to redacted copies, and access was logged for audit.