The Reality of M&E Data

You've just completed baseline data collection across five countries for a livelihoods program. Eight hundred household surveys are in hand, collected via phone using SurveyCTO. When you open the data file, you see a familiar nightmare:

• "Farmer/farmer/FARMER" entered three different ways
• Numeric fields containing "N/A" instead of numbers
• "Kampala/kampala/KAMPALA/Kampla" as four separate location values
• Missing values coded as "N/A", "---", "missing", and blank cells
• Duplicate records from respondents accidentally entered twice

Inconsistent labels, mixed types, and duplicate records can distort analysis. Assess the actual dataset before changing it, and verify suspected errors against source records rather than treating every unusual value as wrong.

The Solution: Structured Data Cleaning

OpenRefine can help standardize fields and record repeatable transformations. AI can suggest checks when approved for the inputs, but it cannot validate a location, eligibility rule, or outlier without appropriate evidence. Time required depends on dataset size and the ambiguity of the errors.

Common M&E Data Quality Issues

| Issue | Example | Impact | |-------|---------|--------| | Inconsistent text formatting | "Farmer/farmer/FARMER" | Analysis counts as 3 values instead of 1 | | Typos and misspellings | "Kampal" instead of "Kampala" | Loses geographic data | | Duplicate records | Same respondent entered twice | Skewed statistics | | Inconsistent missing values | "N/A", "---", "missing", blank | Can't analyze patterns | | Mixed data types | "123" and "One Hundred" in same field | Prevents calculation | | Extra spaces | " Farmer " vs "Farmer" | Doesn't match filter criteria |

Step-by-Step Cleaning Workflow

Step 1: Initial Assessment

Before cleaning, understand the scope of the problem:

1. Open your dataset in OpenRefine (File → Create Project → Select CSV/Excel)
2. Use text facets on categorical columns to see all unique values. This instantly reveals inconsistencies
3. Document what you find: how many unique values per column, what patterns of error exist
4. Create a backup of the original file before any changes

Step 2: Standardize Text Formatting

The fastest wins come from basic text operations:

• Trim whitespace: Edit cells → Common transforms → Trim leading and trailing whitespace
• Normalize case: Edit cells → Common transforms → To titlecase (for names/locations)
• Remove special characters: Use GREL expressions for custom transformations

Step 3: Cluster Similar Values

OpenRefine's clustering feature is where the magic happens. It automatically identifies values that are likely the same thing spelled differently:

1. Click the column dropdown → Edit cells → Cluster and edit
2. Review suggested clusters: "Kampala", "kampala", "Kampla" → merge to "Kampala"
3. Try different clustering methods (key collision, nearest neighbor) for different types of errors

Step 4: Handle Missing Values

Standardize how missing data is represented:

1. Read the codebook: distinguish missing, unknown, refused, not asked, and not applicable. Do not collapse these reasons.
2. Preserve raw values and reason codes: retain an untouched original and a separate missing-reason field or documented analysis codes.
3. Normalize only equivalent codes: after confirming that "N/A", "---", and "missing" mean the same thing in this dataset, a GREL example is if(or(value == "N/A", value == "---", value == "missing"), null, value). Preview the operation and verify the reason field before applying. If their meanings differ, map each separately.

Step 5: Remove Duplicates

1. Flag duplicates: compare the intended unique key in a copied review field. Keep the original identifiers; repeated records may represent different rounds, visits, or household members.
2. Review before deleting: not all apparent duplicates are actual duplicates (different household members may share an address)
3. Document removals: keep a count of records removed and why

Using AI for Validation

After mechanical cleaning, ask AI for candidate checks using synthetic rows or a minimized sample approved for the service and purpose. Review indirect identifiers and the applicable access, retention, and transfer controls before sharing any actual rows:

Prompt for ChatGPT/Claude:

I have a cleaned dataset with the following columns: [LIST COLUMNS].
Here are synthetic or approved minimized sample rows. Do not infer population patterns from this sample. Please identify:
1. Any remaining inconsistencies in categorical fields
2. Values that seem implausible (out of expected range)
3. Patterns that suggest data entry errors
4. Any rows where the combination of values seems contradictory

Expected value ranges:
- Age: 18-65
- Household size: 1-15
- Income (monthly, USD): 0-2000
- [ADD YOUR RANGES]

Verify flagged ages, household roles, and currency against the instrument and source. An unusual value may be real. Correct only supported errors and keep unresolved values visibly flagged.

When to Use What

• OpenRefine: Best for text standardization, clustering, deduplication across 100+ records
• AI tools: Best for pattern detection, validation logic, anomaly identification
• Manual review: Still necessary for ambiguous cases, cultural context, and final quality check
• Python/R: Better choice when you need reproducible cleaning scripts for repeated data collection rounds

Key Takeaways

1. Always keep the original file untouched. Work on a copy.
2. Document every transformation. OpenRefine creates an audit trail automatically.
3. Clean before analyzing. Cleaning after analysis means redoing work.
4. Build cleaning templates for repeated surveys. OpenRefine operations can be exported and reapplied.
5. Budget time for cleaning in your M&E plan. It's not optional overhead; it's essential quality assurance.

Expression source: OpenRefine GREL functions, including the or and if functions. Test each transformation on the actual project copy before applying it broadly.