DEV Community

Cover image for How to catch wrong-parent Contacts after a Salesforce migration
Richard C
Richard C

Posted on

How to catch wrong-parent Contacts after a Salesforce migration

#salesforce #data #csv #tutorial

By Richard C · AIL Studio · 7x certified Salesforce professional

A Contact can keep its name and email after a Salesforce migration and still sit under the wrong Account. Free CSV compare tools miss that case, because the field values still match — you have to check the parent relationship.

This post walks through that check on a tiny example, first with pandas and then with spreadsheet formulas you can use in Google Sheets.

Why you can't just compare AccountId

Record IDs usually change between the source and target org. A Contact's AccountId of SA-01 in the source might legitimately become TA-91 in the target. So "the IDs are different" tells you nothing.

What you need is a stable key: a value that identifies the same record in both exports, such as an external ID field. The check is then:

  1. Source Contact → its source AccountId → that Account's stable key.
  2. Target Contact (matched by the Contact's stable key) → its target AccountId → that Account's stable key.
  3. If the two Account keys differ, the Contact has the wrong parent.

The example data

Example data: four small, made-up CSVs. All records are invented, and the email uses the reserved example.invalid domain.

source-accounts.csv

ExternalKey Id Name Industry
AC-001 SA-01 Aster, Labs Science
AC-002 SA-02 Blue Orchard Retail

target-accounts.csv

ExternalKey Id Name Industry
AC-001 TA-91 Aster, Labs Science
AC-002 TA-92 Blue Orchard Retail

source-contacts.csv

ExternalKey Id AccountId LastName Email
CT-001 SC-01 SA-01 Renée, Example renee@example.invalid

target-contacts.csv

ExternalKey Id AccountId LastName Email
CT-001 TC-91 TA-92 Renée, Example renee@example.invalid

Look at it by hand first. Source CT-001 points to SA-01, which is AC-001. In the target, AC-001 is TA-91, so that's where CT-001 should point. It actually points to TA-92, which is AC-002. Same name, same email, wrong Account.

Check 1: pandas 

import pandas as pd

def load(name):
    # keep IDs as text; the files have a UTF-8 BOM
    return pd.read_csv(f"{name}.csv", dtype=str, encoding="utf-8-sig", keep_default_na=False)

sa, ta = load("source-accounts"), load("target-accounts")
sc, tc = load("source-contacts"), load("target-contacts")

# lookups only work if keys and IDs are unique on each side
for df in (sa, ta):
    assert df["Id"].is_unique and df["ExternalKey"].is_unique

src_key = dict(zip(sa["Id"], sa["ExternalKey"]))   # SA-01 -> AC-001
tgt_key = dict(zip(ta["Id"], ta["ExternalKey"]))   # TA-92 -> AC-002
tgt_id  = dict(zip(ta["ExternalKey"], ta["Id"]))   # AC-001 -> TA-91

m = sc.merge(tc, on="ExternalKey", how="left", suffixes=("_src", "_tgt"))
m["expected_parent_key"] = m["AccountId_src"].map(src_key)
m["actual_parent_key"]   = m["AccountId_tgt"].map(tgt_key)
m["expected_target_id"]  = m["expected_parent_key"].map(tgt_id)
m["result"] = (m["expected_parent_key"] == m["actual_parent_key"]).map(
    {True: "PARENT_MATCH", False: "WRONG_PARENT"})

print(m[["ExternalKey", "expected_target_id", "AccountId_tgt", "result"]])
print("wrong parents:", (m["result"] == "WRONG_PARENT").sum())
Enter fullscreen mode Exit fullscreen mode

Output on the example data:

  ExternalKey expected_target_id AccountId_tgt        result
0      CT-001              TA-91         TA-92  WRONG_PARENT
wrong parents: 1
Enter fullscreen mode Exit fullscreen mode

So there is 1 wrong-parent Contact (CT-001: expected TA-91, actual TA-92), and 0 differences in LastName or Email. On real data, also check for Contacts whose parent doesn't resolve at all (a NaN key here). Report those as unresolved, not as matches.

Check 2: Google Sheets formulas

Import each CSV into its own tab named SourceAccounts, TargetAccounts, SourceContacts and TargetContacts, with headers in row 1. Keep IDs as text. "Aster, Labs" and "Renée, Example" each belong in one cell.

First, confirm the lookups are safe. Each Account Id and ExternalKey must be unique:

=SUMPRODUCT((COUNTIF(SourceAccounts!$B$2:$B$3,SourceAccounts!$B$2:$B$3)=1)*1)   → 2 (both IDs unique)
=SUMPRODUCT((COUNTIF(TargetAccounts!$A$2:$A$3,TargetAccounts!$A$2:$A$3)=1)*1)   → 2 (both keys unique)
Enter fullscreen mode Exit fullscreen mode

Then add a Check tab. Put the labels in row 1 and these formulas in row 2:

A2  Contact key:                =SourceContacts!A2
B2  Source Account ID:          =SourceContacts!C2
C2  Source Account key:         =INDEX(SourceAccounts!$A$2:$A$3,MATCH(B2,SourceAccounts!$B$2:$B$3,0))
D2  Expected target Account ID: =INDEX(TargetAccounts!$B$2:$B$3,MATCH(C2,TargetAccounts!$A$2:$A$3,0))
E2  Actual target Account ID:   =INDEX(TargetContacts!$C$2:$C$3,MATCH(A2,TargetContacts!$A$2:$A$3,0))
F2  Actual target Account key:  =INDEX(TargetAccounts!$A$2:$A$3,MATCH(E2,TargetAccounts!$B$2:$B$3,0))
G2  Relationship check:         =IF(EXACT(C2,F2),"PARENT_MATCH","WRONG_PARENT")
H2  Source LastName:            =SourceContacts!D2
I2  Target LastName:            =INDEX(TargetContacts!$D$2:$D$3,MATCH(A2,TargetContacts!$A$2:$A$3,0))
J2  LastName check:             =IF(EXACT(H2,I2),"MATCH","DIFFERENCE")
K2  Source Email:               =SourceContacts!E2
L2  Target Email:               =INDEX(TargetContacts!$E$2:$E$3,MATCH(A2,TargetContacts!$A$2:$A$3,0))
M2  Email check:                =IF(EXACT(K2,L2),"MATCH","DIFFERENCE")
B4  Wrong-parent count:         =COUNTIF(G2:G2,"WRONG_PARENT")
Enter fullscreen mode Exit fullscreen mode

Expected results:

Cell Value
A2 CT-001
B2 SA-01
C2 AC-001
D2 TA-91
E2 TA-92
F2 AC-002
G2 WRONG_PARENT
J2 MATCH
M2 MATCH
B4 1

I recalculated this exact workbook and got these values, matching the pandas result. As a sanity check, change TargetContacts!C2 to TA-91. F2 becomes AC-001 and G2 becomes PARENT_MATCH.

A few notes for real exports:

  • MATCH is case-insensitive, so EXACT does the final comparisons. Keep keys consistently cased.
  • Extend ranges deliberately. For more Contacts, fill row 2 down and widen every range ($A$2:$A$5000 and so on), then change B4 to cover the whole G column range.
  • A lookup error (#N/A) means unresolved. Investigate it. Don't treat it as a match or hide it with IFERROR.
  • This checks one relationship on the files you load. It doesn't certify a whole migration.

If you'd rather skip the setup

I packaged these four example CSVs, the README and the exact Check-tab formulas into a free guide ($0). It includes the expected output, so you can confirm your sheet: RecordBridge Free Guide: CSV Example.

For full exports, RecordBridge (US$49, one-time) runs the same check across Accounts, Contacts and Opportunities.

Related tool: if you'd rather not build these checks by hand, RecordBridge runs them on your exported CSVs.
RecordBridge by AIL Studio

RecordBridge by AIL Studio is not affiliated with Salesforce.

Top comments (0)