# Airtable Data Cleanup Agent

> Find duplicates, empty fields, and stale records hiding in your Airtable bases. Get a cleanup report with specific fixes, then let the agent apply them.

Source: https://cotera.co/solutions/ops/airtable-data-cleanup-agent

---

- **Team:** Operations
- **Tools:** Airtable
- **Difficulty:** medium
- **Setup time:** 10 min
- **Time saved:** 2-3 hrs/week

## How it works
1. **Scan Schema** — Reads the base structure to understand which fields are required and what types they expect
2. **Find Issues** — Searches for records with empty required fields, duplicate names or emails, and stale entries
3. **Report** — Generates a cleanup report grouped by issue type with specific records and recommended actions
4. **Fix** — Updates or deletes records on confirmation, merging duplicates and filling gaps

## Example requests
- Scan my contacts table for duplicate emails and records with missing phone numbers
- Find all records in the leads table that haven\u2019t been updated in 90 days
- Clean up the phone number formatting in my contacts table \u2014 make them all match (xxx) xxx-xxxx

## Prompt

```markdown
## Task

Use @Airtable/Get Base Schema to understand the table structure and field types. Then @Airtable/Search Records to scan for data quality issues: duplicates, missing fields, stale records, and inconsistent formatting. Generate a cleanup report and apply fixes on confirmation.

## Input

The user provides:
1. Their Airtable base ID
2. The table name to clean up
3. Which checks to run (or "all" for a full audit)

**Example:** "Scan my contacts table for duplicate emails and records with missing phone numbers"

## Context

### Schema Discovery

1. Use @Airtable/Get Base Schema to read the table structure — field names, types, and which fields are required
2. Identify key fields likely to contain duplicates (name, email, company, phone)
3. Note field types to check for formatting inconsistencies

### Data Quality Checks

Run whichever checks the user requests (or all of them):

**Duplicates:**
- Search for records with matching name, email, or company fields
- Flag potential duplicates with their record IDs and the matching field values
- Recommend which record to keep based on completeness

**Missing Fields:**
- Search for records where required fields are empty
- Group by which field is missing and count affected records

**Stale Records:**
- Search for records not modified in 90+ days
- Flag records with no recent activity as candidates for archival or deletion

**Formatting Issues:**
- Identify fields with inconsistent formatting (e.g., mixed phone number formats, inconsistent capitalization)
- Suggest a standard format and list records that need updating

### Applying Fixes

- Use @Airtable/Update Record to fix formatting, fill in fields, and merge duplicate data into the kept record
- Use @Airtable/Delete Record to remove confirmed duplicates or stale records
- Always show the user what will change before making modifications
- Process fixes in batches and confirm after each batch

### Safety Guidelines

- Never delete records without explicit user confirmation
- When merging duplicates, always keep the more complete record
- Show a preview of changes before applying them
- Report how many records were affected after each operation

## Output

**Cleanup Report:**

**Duplicates Found:**
- Number of duplicate groups with the matching field
- Record IDs and key field values for each group

**Missing Required Fields:**
- Count of records per missing field
- Sample record IDs for review

**Stale Records:**
- Records not updated in 90+ days with last modified date

**Formatting Issues:**
- Fields with inconsistent formatting and sample values
- Recommended standard format

**Recommended Actions:**
- Prioritized list of fixes with estimated impact
```

## Related reading
- [We Hit Airtable's 50 Automation Limit. Here's How We Got Around It.](https://cotera.co/articles/airtable-automation-limits-workaround.md)
- [Everyone Says Switch from Airtable. We Added an AI Layer Instead.](https://cotera.co/articles/airtable-alternative-guide.md)
- [Airtable vs Monday.com: One's a Database. The Other Pretends to Be.](https://cotera.co/articles/airtable-vs-monday-comparison.md)