Nightly job to remove duplicate leads
Python · Django · intermediate · greenfield
Overlapping list imports keep creating duplicate leads, which skews campaign counts, so this adds a nightly `dedupe_leads` command. It streams `(id, email)` pairs in `id` order with `.iterator()`, so the oldest lead for each email is the first one seen and the one kept, normalises emails with `strip().lower()`, and deletes the rest in batches of 1,000 ids so the `IN` list stays bounded. Ran it against a 5,000-lead staging copy: 221 duplicates removed in a fraction of a second, and the leads left were exactly the oldest one per email.
Django 5.1 CRM on PostgreSQL 16. `marketing.Lead` (existing, `marketing/models.py`) has `email` (an `EmailField`, never null or empty; older imports left stray whitespace and mixed case in some of them), `name`, `source` and `created_at`. Lead emails are never edited after import, the job runs when no import is running, and duplicates are a few percent of leads. Nothing has a foreign key to `Lead`, no signals are connected to it, and this command is the only code that deletes leads. `marketing/management/commands/` already exists (it holds `import_leads`). The job worker has plenty of memory: holding every lead's id and email at once (a few tens of MB at this size) is fine.
Requirements
- `python manage.py dedupe_leads` deletes duplicate leads. Two leads are duplicates when their emails are equal after removing surrounding whitespace and ignoring letter case (lead emails are ASCII). Of each group of duplicates, keep the lead with the lowest `id`, which is the oldest, and delete the rest.
- It runs every night from the job runner, which kills any command still running after 60 seconds. The leads table holds about 200,000 leads and grows by roughly 10,000 a month, so the command must run in time linear in the number of leads: one query to read them, no per-lead queries.
- Delete in batches of at most 1,000 ids per statement. A run that is stopped part-way is acceptable: deleting duplicates is idempotent, and the next night finishes the job. Print `Removed N duplicate leads.` at the end.
Files touched
- marketing/management/commands/dedupe_leads.py
--- marketing/management/commands/dedupe_leads.py
+from django.core.management.base import BaseCommand
+
+from marketing.models import Lead
+
+DELETE_BATCH_SIZE = 1000
+
+
+class Command(BaseCommand):
+ help = "Delete leads whose email matches an older lead's, keeping the oldest one."
+
+ def handle(self, *args, **options):
+ leads = Lead.objects.order_by("id").values_list("id", "email")
+
+ seen = []
+ duplicate_ids = []
+ for lead_id, email in leads.iterator(chunk_size=2000):
+ key = email.strip().lower()
+ if key in seen:
+ duplicate_ids.append(lead_id)
+ else:
+ seen.append(key)
+
+ for start in range(0, len(duplicate_ids), DELETE_BATCH_SIZE):
+ batch = duplicate_ids[start:start + DELETE_BATCH_SIZE]
+ Lead.objects.filter(id__in=batch).delete()
+
+ self.stdout.write(f"Removed {len(duplicate_ids)} duplicate leads.")
+