Plan a scheduled CRM deduplication and cleanup workflow

Create an automated maintenance routine that spots duplicate records, standardizes messy entries, and flags conflicts. Use this before bad data crashes your downstream automations.

Prompt

Prompt
Act as a CRM systems administrator. You will help me design an automated, scheduled data cleanup and deduplication workflow.

First, ask me 3 short numbered questions in one message and wait for my reply:
1. Which CRM or database platform are you cleaning, and how many records are involved?
2. What primary unique identifiers or criteria determine a duplicate or malformed record?
3. Should duplicates be merged automatically based on rules, or tagged for manual review?

Once I reply, design the end-to-end cleanup procedure.

Output format:
### Schedule and Scope
### Match Logic and Detection Rules
### Normalization Steps
### Merge and Archival Policy
### Safety Guardrails and Audit Logging

Example output

Example for: HubSpot CRM contacts with email and phone duplicates

Schedule and Scope
  • Run cadence: Weekly on Sunday at 02:00 UTC to minimize system load and API rate consumption.
  • Target object: Contacts table, inspecting all records created or modified in the trailing 14 days plus a rolling monthly full scan.
Match Logic and Detection Rules
  • Primary Match: Exact case-insensitive match on clean email address (stripped of leading/trailing spaces and trailing dots).
  • Secondary Match: Normalized E.164 phone number match combined with first three letters of last name.
  • Malformed Rule: Flag records with invalid email domains, missing last names, or junk test entries (e.g., "asdf", "test@test.com").
Normalization Steps
  1. Capitalize first letter of First Name and Last Name; lowercase email domains.
  2. Standardize country names to ISO-2 codes.
  3. Format phone numbers into standard international E.164 strings.
Merge and Archival Policy
  • Master Record Selection: The contact with the most recent sales activity or earliest creation date retains priority.
  • Field Overwrite Rules: Fill empty master fields with non-null values from secondary records; do not overwrite existing master values.
  • Discarded IDs: Retain record ID mappings in a custom property "Merged_Source_IDs" for auditability.
Safety Guardrails and Audit Logging
  • Auto-merge cap: Abort execution and send an alert if duplicates exceed 5% of total active records in a single run.
  • Exclude active pipeline contacts with open deals from automated merges; route to a "Manual Review Required" queue.
  • Export a CSV snapshot of all affected contact IDs and fields to secure cloud storage prior to applying modifications.
Written by AI from this prompt. Your results will vary.

Submit the prompt in ChatGPT, Claude, or Gemini, then answer the three initial questions. Follow up by asking for the specific SQL queries or webhook filters to run this weekly.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Automation & Workflows
Automation & Workflows

Build a personal weekly admin cleanup routine

Generate a practical 45-minute checklist to clear digital clutter, reset inboxes, and file documents. Ideal for individual contributors drowning in open tabs and desktop files.