Practical Example from 3ie’s Grant Evaluation:

They needed to extract from hundreds of research proposals:

• Simple variables: Title, publication year, author names
• Complex variables requiring judgment: “Stakeholder engagement described” (yes/no), “Transparency practices reported” (yes/no)

Cost estimate: OpenAI API costs approximately $0.01-0.03 per 1,000 pages for extraction. Transcription approximately $0.006 per minute (Whisper API). Test with 5-10 files first.

Security Warning: Never hardcode API keys in production code. Use environment variables or Colab Secrets for secure API key storage.

Google Colab Python script (copy-paste ready)

# Install and import
!pip install openai pandas -q
import openai
import pandas as pd
import os
from google.colab import files, userdata
# Securely load API key (use Colab Secrets)
try:
    openai.api_key = userdata.get('OPENAI_API_KEY')
    print("API key loaded from Colab Secrets.")
except:
    openai.api_key = "your-key-here"  # Replace for testing only
    print("WARNING: Using hardcoded API key. Do not share this notebook.")
# Initialize the OpenAI client
client = openai.OpenAI(api_key=openai.api_key)
# Upload text files or transcripts
print("Upload .txt files (one per document)")
uploaded = files.upload()
# Define extraction prompt
prompt_template = """
You are an M and E data extraction assistant.
Extract the following variables from the document below.
Return ONLY valid JSON with no extra text.
Variables to extract:
- "document_type": (proposal/report/policy_brief/minutes/other)
- "themes_mentioned": (list key themes as array)
- "stakeholders_mentioned": (list organizations/actors)
- "outcomes_described": (list outcome statements)
- "theory_of_change_reference": (yes/no)
Document text:
{document_text}
"""
results = []
for filename, content in uploaded.items():
    text = content.decode('utf-8')[:10000]
    prompt = prompt_template.replace("{document_text}", text)
    
    response = client.chat.completions.create(
        model="gpt-3.5-turbo",
        messages=[{"role": "user", "content": prompt}],
        temperature=0.2,
        max_tokens=500
    )
    
    extraction = response.choices[0].message.content
    results.append({"filename": filename, "extraction_json": extraction})
df = pd.DataFrame(results)
df.to_csv("extracted_data.csv", index=False)
print("Done. Download extracted_data.csv from file browser.")

Copy

Pro tip: For multilingual audio, use Whisper API with the language parameter: response = client.audio.transcriptions.create(model="whisper-1", file=audio_file, language="es") for Spanish.

Quality assurance protocol (adapted from 3ie):

• Ground truth benchmark: Have two human coders analyze 50-100 documents independently. Calculate inter-rater reliability.
• AI accuracy check: Compare AI extraction to human coding on the benchmark set. Aim for over 90 percent agreement for simple variables.
• Spot-check 10-15 percent: Randomly review a subset of AI outputs. Flag systematic errors.
• Edge case review: Manually examine documents where AI expressed low confidence.