Data Cleanup Automation

Automate data quality maintenance and cleanup tasks.

Overview

Data cleanup workflows maintain data quality by identifying stale records, merging duplicates, and enforcing data standards. These run on schedules to keep your CRM healthy without manual effort.

Common Cleanup Tasks

Stale Data Archiving

Automatically identify records with no recent activity:

Record Type	Stale Criteria	Action
Opportunities	No activity 90+ days	Flag for review
Contacts	No engagement 180+ days	Move to nurture list
Companies	No open deals 1+ year	Archive
Tasks	Overdue 30+ days	Notify owner

Duplicate Detection

Identify potential duplicates:

Field Match	Confidence	Action
Email exact	High	Flag for merge
Company + Name	Medium	Review required
Phone number	High	Flag for review

Data Validation

Enforce data standards:

• Email format: Valid format required
• Phone numbers: Normalized format
• Required fields: Can’t be empty
• Relationships: Must link to existing records

Building Cleanup Workflows

Workflow: Stale Opportunity Cleanup

Purpose: Flag opportunities with no activity

Steps:

1. Trigger: Weekly (Sunday 2am)

2. Search Records:

   Object: Opportunities
   Filter: 
     - Last activity date < 90 days ago
     - Stage ≠ "Closed Won"
     - Stage ≠ "Closed Lost"
   

3. Filter: Check if results exist

   Condition: Records found
   

4. Branch - If stale found:

   a. Iterator: Loop through each opportunity

   i. Update Record:

   Status: "Stale - Review Required"
   

   ii. Create Record: Task

   Title: "Review stale opportunity"
   Assigned to: Opportunity owner
   Due date: Today + 7 days
   

   b. Close Iterator

5. Send Email: Summary to sales manager

   Subject: Weekly Stale Opportunity Report
   Body: {{iterator.totalCount}} opportunities flagged for review
   

Workflow: Duplicate Contact Detection

Purpose: Find potential duplicate contacts

Steps:

1. Trigger: Daily at 3am

2. Search Records: All contacts created yesterday

3. Iterator: Process each new contact

   a. Search Records: Find contacts with same email

   Filter: Email = {{iterator.currentItem.email}}
   Limit: 10
   

   b. Filter: Check if duplicates found (>1 results)

   c. Branch - If duplicate:

   i. Update Record:

   Tag: "Potential Duplicate"
   

   ii. Create Record: Task

   Title: "Review duplicate contact"
   Assigned to: Data steward
   Description: "Duplicate of {{duplicate.email}}"
   

   d. Close branch

4. Close Iterator

5. Send Email: Daily duplicate report to data team

Advanced Cleanup Patterns

Cross-Object Validation

Ensure related records are consistent:

Example: Contact-Company Consistency

1. Trigger: Record updated (Contact)
2. Filter: Company field changed
3. Search Records: Find company by name
4. Filter: Check if found
5. Branch:
   • Found: Update contact with company reference
   • Not found: Create company, then update contact

Bulk Archive

Move old records to archive state:

1. Trigger: Monthly (1st of month)
2. Search Records: Records inactive 2+ years
3. Iterator: Process in batches
4. Update Record: Set status to “Archived”
5. Create Record: Archive log entry
6. Send Email: Archive summary

Archiving vs deleting: Always archive, never delete. Deleted records are unrecoverable.

Data Normalization

Standardize data formats:

Example: Phone Number Normalization

1. Trigger: Record created/updated (Contact)
2. Filter: Phone number field not empty
3. Code: Normalize phone format
   const phone = record.phoneNumber;
   const normalized = phone
     .replace(/\D/g, '')
     .replace(/^(\d{3})(\d{3})(\d{4})$/, '($1) $2-$3');
   
   return { normalizedPhone: normalized };
   
4. Update Record: Set phone to normalized format

Error Handling

Handling Empty Results

Always check if search returns records:

Search Records → Filter (records found?) → Branch
                      ↓ Yes              ↓ No
                Process records        Skip/Send "no issues" email

Handling Failures

Log cleanup failures:

1. Add Filter after each action
2. Check if action succeeded
3. On failure:
   • Create error log record
   • Send alert to admin
   • Continue with next record (don’t stop workflow)

Rate Limit Protection

Respect API limits in large cleanups:

// Code action to throttle
await new Promise(resolve => setTimeout(resolve, 100));
return { throttled: true };

Monitoring Cleanup

Success Metrics

Track your cleanup effectiveness:

Metric	How to Track
Records cleaned per run	Workflow run logs
Error rate	Failed runs / Total runs
Time to complete	Run duration in logs
Data quality score	Manual audit monthly

Cleanup Dashboard

Create a dashboard to monitor:

• Aggregate widget: Stale records count
• Aggregate widget: Duplicates flagged today
• Line chart: Cleanup volume over time
• Bar chart: Errors by cleanup type

Alerting

Set up alerts for issues:

1. Trigger: Workflow run fails
2. Filter: Error count > 5
3. Send Email: Alert to data team
4. Create Record: Incident ticket

Best Practices

Testing

Test cleanup workflows thoroughly:

1. Create test data: Duplicate contacts, stale opportunities
2. Run workflow manually: Check test run results
3. Verify: Review affected records
4. Check side effects: Ensure related records OK
5. Schedule: Only after thorough testing

Scheduling

Choose appropriate times:

• Off-peak hours: 2am-6am to avoid user impact
• Avoid maintenance windows: Don’t conflict with backups
• Stagger cleanups: Don’t run all cleanups simultaneously

Gradual Rollout

For large cleanups:

1. Start with small subset (100 records)
2. Review results
3. Increase batch size gradually
4. Full rollout after validation

Backups

Always backup before major cleanups:

1. Export data via API before cleanup
2. Document changes in changelog
3. Test restore procedure

Limitations

Search Limits

• Max 200 records per search
• For larger cleanups, use Code action with pagination

Update Limits

• Batch updates: 60 records max
• Individual updates: No limit but rate limited

Iterator Constraints

• Can process up to 200 records per run
• Must close iterator properly
• Loops inside iterators not supported

Error Recovery

• Workflow stops on unhandled errors
• Must use branches to handle failures
• No automatic retry logic

Examples

Example 1: Weekly Orphaned Contact Cleanup

Find contacts with no company and no activity:

1. Trigger: Weekly (Sunday 3am)
2. Search Records: Contacts
   • Company = empty
   • Last activity < 180 days ago
3. Filter: Check if found
4. Iterator: Process each
   • Update: Tag = “Orphaned”
   • Create: Task for data steward
5. Send Email: Summary count

Example 2: Daily Email Validation

Flag contacts with invalid email:

1. Trigger: Daily 4am
2. Search Records: Contacts created yesterday
3. Iterator: Process each
4. Code: Validate email format
   const email = record.email;
   const isValid = /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email);
   return { isValid };
   
5. Filter: Is valid = false
6. Branch:
   • Update: Tag = “Invalid Email”
   • Create: Task to correct
7. Send Email: Daily validation report

Example 3: Monthly Data Quality Score

Calculate and report data quality metrics:

1. Trigger: Monthly (1st, 9am)
2. Search Records: All contacts
3. Code: Calculate metrics
   const total = records.length;
   const withEmail = records.filter(r => r.email).length;
   const withPhone = records.filter(r => r.phone).length;
   
   return {
     emailFillRate: (withEmail / total * 100).toFixed(1),
     phoneFillRate: (withPhone / total * 100).toFixed(1)
   };
   
4. Update Record: Create monthly quality record
5. Send Email: Quality scorecard to leadership

Related

• Internal Workflows Overview
• Lead Scoring Setup
• Script Templates