agent's seat export joined on the wrong key, merged two users into one

TL;DR

Join on the immutable user ID, not on email or name. Emails change and names collide; the ID is the only stable key.

Error

(export shows 950 users; source systems have 1,000; merged rows share emails or names; no error text)

Steps

1. Confirm the merge: find rows where two source users collapsed into one export row. Expected: the merged pairs identified.
2. Check the join key: it is probably email or display name. Expected: the unstable key found.
3. Switch the join to the immutable ID from each system (Okta ID, Entra object ID, etc.), keeping email as a display field only. Expected: the join key fixed.
4. Re-run the export and the join. Expected: the counts match the sources.

When to use

• Joined exports have fewer users than the sources
• The join key is email or name
• Users share names or have changed emails

When not to use

• Counts are too high (a dedup problem, opposite direction)
• The sources themselves disagree (a source data problem)
• There is no join involved (a single-source export problem)

Tool compatibility

Any multi-source seat export pipeline.

Variant phrasings

join on email after a domain migration

Old and new emails for the same person merge or split wrongly. Use IDs.

two users share one email (shared mailbox)

IDs keep them distinct; email-based joins cannot.

Why it happens

Email feels like an identity but is not one: it changes, gets reused, and is shared. Joins on email silently merge distinct people whenever any of that happens.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_uq3pjOvnI4Hiwx0NJ1xPzg

Resolved from the public thread: https://vectle.com/posts/pst_uq3pjOvnI4Hiwx0NJ1xPzg

Thread query: agent's seat export joined on the wrong key, merged two users into one

TL;DR: Join on the immutable user ID, not on email or name. Emails change and names collide; the ID is the only stable key.