De-duplicate leads by normalising Indian mobile numbers first

Why a plain "Remove Duplicates" misses most of them

Excel's Remove Duplicates, a pandas drop_duplicates, or SQL DISTINCT compare text exactly. To them, "+91 98765 43210" and "9876543210" are different people, so a lead list merged from a website form, a social media lead export and a walk-in register can hold the same person three times. The sales team calls them three times, the dashboard counts three leads, and the cost per lead looks better than it is.

The fix is to compare a normalised key, not the raw text, and to keep the raw text beside it so nobody loses what the lead actually typed.

The normalisation rules, in order

1. 1Treat the value as text. Never let it be read as a number.
2. 2If the text is in scientific notation (for example 9.87654321E+09), stop. The digits after the eighth or ninth place are already lost; mark it for recovery from the source system.
3. 3Remove every non-digit: spaces, plus signs, hyphens, brackets, dots.
4. 4Twelve digits starting with 91: drop the 91.
5. 5Eleven digits starting with 0: drop the 0.
6. 6Anything other than ten digits now is invalid.
7. 7Ten-digit mobile numbers in India currently begin with 6, 7, 8 or 9. A ten-digit value starting with anything else is usually a landline with an STD code or a placeholder like 1234567890. Flag it rather than keeping it as a mobile.

Rule 7 is a practical filter, not a legal validation. The ten-digit mobile format comes from the Department of Telecommunications' National Numbering Plan, and TRAI's 2020 numbering recommendations list mobile series allocated at levels 6, 7, 8 and 9. Allocations change over time, so if a filter would silently drop real customers, use it to flag instead of to delete.

Normalise, then mark repeats

All leads, numbers and timestamps are synthetic.

import re
import pandas as pd

# Synthetic leads from three sources: website form, Meta export, walk-in register.
leads = pd.DataFrame({
    "lead_id": range(101, 111),
    "source": ["web", "meta", "walkin", "web", "meta", "web", "walkin", "meta", "web", "walkin"],
    "created": pd.to_datetime(["2026-09-01 10:05", "2026-09-01 10:40", "2026-09-03 17:20",
                               "2026-09-04 09:10", "2026-09-04 09:11", "2026-09-05 12:00",
                               "2026-09-06 15:30", "2026-09-07 11:00", "2026-09-07 11:02",
                               "2026-09-08 16:45"]),
    "phone_raw": ["98765 43210", "+919876543210", "09876543210", "91-7012345678",
                  "7012345678", "9.87654321E+09", "0120-4567890", "+91 88001 23456",
                  "880012345", "1234567890"],
})

def normalise_mobile(raw) -> tuple[str | None, str]:
    """Return (10-digit mobile or None, reason)."""
    if raw is None or pd.isna(raw):
        return None, "blank"
    s = str(raw).strip()
    if re.fullmatch(r"\d(?:\.\d+)?E\+\d+", s, flags=re.I):
        return None, "scientific notation: digits lost, recover from source"
    digits = re.sub(r"\D", "", s)
    if len(digits) == 12 and digits.startswith("91"):
        digits = digits[2:]                      # +91 / 91 country code
    elif len(digits) == 11 and digits.startswith("0"):
        digits = digits[1:]                      # trunk prefix 0
    if len(digits) != 10:
        return None, f"{len(digits)} digits"
    if digits[0] not in "6789":
        return None, "does not look like a mobile (landline or junk)"
    return digits, "ok"

res = leads["phone_raw"].map(normalise_mobile)
leads["mobile"] = res.str[0]
leads["phone_status"] = res.str[1]

# Keep the earliest lead per mobile; later ones become duplicates pointing at it.
valid = leads.dropna(subset=["mobile"]).sort_values(["mobile", "created"], kind="stable")
first = valid.groupby("mobile")["lead_id"].transform("first")
leads.loc[valid.index, "duplicate_of"] = first.where(first != valid["lead_id"])
leads["duplicate_of"] = leads["duplicate_of"].astype("Int64")

print(leads[["lead_id", "source", "phone_raw", "mobile", "phone_status", "duplicate_of"]]
      .to_string(index=False))
print("\nunique valid mobiles:", leads["mobile"].nunique(),
      "| duplicates:", leads["duplicate_of"].notna().sum(),
      "| needs fixing:", leads["mobile"].isna().sum())

Ten rows become three people plus four problems. Leads 102 and 103 are the same person as 101, written with a country code and a trunk zero; lead 105 is a repeat of 104 one minute later, the kind of double submit a slow form produces. Lead 106 was damaged by Excel, 107 is a Noida landline, 109 is missing a digit and 110 is a placeholder.

Mark duplicates, do not delete them

The script adds a duplicate_of column rather than dropping rows. That choice matters for three reasons. Attribution reports still see that the same person enquired through two channels, which is useful information about the funnel. A counsellor who spoke to lead 102 can find their notes. And if the rule turns out to be wrong for a case, say, two family members sharing one phone, the row is still there to restore.

For the dashboard, count leads[leads["duplicate_of"].isna() & leads["mobile"].notna()] as unique leads, and report the other two groups separately so their size is visible.

A spreadsheet version with LET

With the raw number in B2 (formatted as Text), a helper column can do most of the work in Microsoft 365:

Digits    =CONCAT(IFERROR(MID(B2,SEQUENCE(LEN(B2)),1)*1,""))
Mobile    =LET(d,C2, n,LEN(d),
               k,IF(AND(n=12,LEFT(d,2)="91"),RIGHT(d,10),IF(AND(n=11,LEFT(d,1)="0"),RIGHT(d,10),d)),
               IF(AND(LEN(k)=10,ISNUMBER(FIND(LEFT(k,1),"6789"))),k,"CHECK"))
First?    =IF(D2="CHECK","",IF(COUNTIFS(D:D,D2,A:A,"<"&A2)=0,"first","duplicate"))

The First? column assumes column A holds the created timestamp. Sort by the mobile column and read the duplicates before acting on them.

Stop the problem at the source

Normalising after the fact is necessary, but a web form that accepts only ten digits, shows a fixed "+91" prefix, and disables the submit button after the first click prevents most of what this script cleans up. Share the fix-list with whoever owns each lead source, grouped by source, so they can see which channel produces the damage.

Once phones are clean they make a strong second key for matching customer records by name; see find duplicate customers when names differ by M/s, Shri or Pvt Ltd. Lead and CRM data preparation of this kind, including the reporting that sits on top of it, is practised with realistic messy files in our Data Analyst programme.