MLMartin Oliver Lucero

Home/Case Studies

PYTHON · POWER AUTOMATE · PROCESS REDESIGN

Workflow Automation

A recurring gift-acknowledgment workflow was redesigned after the complete manual process was mapped and its business rules were separated from repetitive handling. Python and Power Automate tools reduced the process from 76 steps to 11 while preserving categorization, reconciliation, document controls, user-selected paths, validation, and human review.

PythonPower AutomateProcess redesignDocumentation
01

Operating Context

  • The gift-acknowledgment process moved repeatedly among Raiser's Edge exports, spreadsheets, Word templates, folders, PDFs, and business-office outputs.
  • Nine acknowledgment categories followed different rules for signing authority, donation purpose, membership, event participation, and gift amount.
  • Staff had to prepare data, categorize gifts, create documents, organize files, reconcile outputs, and complete quality review for each batch.
  • The work was recurring, time-sensitive, and important to donor stewardship, but much of the effort consisted of repeating the same handling steps.
02

Constraints & Failure Points

  • The manual workflow contained 76 individual steps, including repeated sequences across multiple acknowledgment categories.
  • Important business knowledge lived inside a sequence of clicks, file names, folder paths, and manual decisions rather than in a visible, documented system.
  • Every transfer among spreadsheets, templates, folders, and outputs created another opportunity for a category, amount, path, format, or document to be handled inconsistently.
  • The automation could not remove human judgment where category rules or quality review required it.
  • The tools needed to work with user-selected source and output paths rather than depend on one person's computer structure.
  • Errors had to remain traceable. A failed output needed to be distinguishable as an issue with data, templates, software/scripting configuration, or automation failure.
  • The redesigned process had to remain understandable and usable by staff who had not written the code.
03

Success Criteria

  • Reduce repeated manual actions while preserving the existing business constraints and review controls.
  • Separate data parsing, categorization, document generation, reconciliation, and business-office outputs into accessible components.
  • Allow users to select their own source files and output locations.
  • Generate consistent Excel, PDF, and Word outputs from the correct data and templates.
  • Provide logs, validation points, elapsed-time reporting, and clear completion or error messages.
  • Document what remained a human responsibility, what the tools handled, and where users should verify the result.
  • Demonstrate measurable reductions in time, steps, and labor cost.
04

Analysis & Decision Process

  • The complete manual workflow was documented before any automation decision was made. This exposed repeated actions, category rules, handoffs, dependencies, and review controls.
  • Human decisions were separated from deterministic operations. Categorization logic and output rules could be automated, while final quality assurance and exceptional cases would remain under staff control.
  • The workflow was divided into distinct tools rather than one opaque script: batch export processing, acknowledgment export parsing, and automated mail merge.
  • Python was used for structured data handling, categorization, formatting, Excel and PDF generation, validation, and Word document production.
  • Power Automate was used for repeatable desktop actions such as folder creation, file movement, and supporting steps that fit naturally in the local workflow.
  • User-selected paths reduced dependence on a single workstation and made the tools more transferable.
  • Logs, elapsed-time reporting, validation messages, and completion notices were added so the automation reported what it had done.
  • Tools were tested against realistic batches, and outputs were compared with the established manual process.
  • Instructions explained the revised responsibilities in plain language, including where staff still needed to review, save, reconcile, or resolve exceptions.
05

Outcome

  • The process was reduced from 76 individual steps to 11, eliminating 65 steps and approximately 85.5% of the manual sequence.
  • In a controlled 12-letter test using the same data and operator, processing time fell from 47 minutes to 7 minutes, an approximate 85% reduction in manual effort.
  • Estimated labor cost fell from approximately $2.04 per letter to $0.30 per letter, a savings of about $1.74 per acknowledgment, an 85.29% reduction in cost.
  • The business rules became more visible because they were documented and represented in distinct components rather than hidden inside repeated clicks.
  • Logs and validation points made problems easier to trace and reduced uncertainty about whether an issue originated in the data, template, workflow, or tool.
06

Lasting Value

The automation improved more than speed. It converted an individual, click-dependent routine into a documented and transferable operating system that preserved judgment, reduced repetitive handling, and made both responsibilities and errors easier to see.