System Admin and Configuration

Get Involved. Join the Conversation.

Topic

    Sergio Andres Barreto Valderrama
    Identify Duplicate ContactsAnswered
    Topic posted May 30, 2019 by Sergio Andres Barreto ValderramaBronze Medal: 1,250+ Points 
    69 Views, 2 Comments
    Title:
    Identify Duplicate Contacts
    Summary:
    Duplicate Contacts
    Content:

    Hello community!

    How can I from a report identify contacts that have the same document number but different registration ID?

    I need to identify duplicate contacts that have associated issues to move them to the correct contact.

    If anyone has any idea how to do it, I would appreciate it very much.

    I was thinking with some condition for the document number as an attachment in the "OSC" image

    Version:
    19A
    Image:

    Best Comment

    Levi D

    Hello Sergio,

    Considering just those two fields (document number and registration ID), this can be done to identify contacts that have the same document number but differing registration ID.  Here are the column definitions to make this work:

    column 1:  contacts.<document number>

    column 2:  count(distinct(contacts.<registration ID>))

    NOTE:  Due to the aggregate function use in column 2 this automatically causes the report to group results based on the contacts.<document number> field (see in the "Level" tab).  This is intended and needed to find the duplicates.

    Add a group filter (in the Level tab) that will be like this:  count(distinct(contacts.<registration ID>)) greater than 1

    The report could be run at this point and it would output all document Numbers that have more than 1 differing registration ID.  However, if would like to output more info like contacts.c_id or contacts.email, etc. this can be done.  However, fields should not be added as-is (e.g. contacts.c_id) as they will be added into the grouping and will effectively "break" the logic on the report.  If more fields are added they need to be done in a way where they will not be included in the grouping.  Using the "group_concat" function would probably be ideal for use to output additional data.  Below are examples on how do this for the c_id and email fields.

    group_concat(contacts.c_id, ',')

    group_concat(contacts.email, ',')

    Hope this helps you out.

    Comment

     

    • Levi D

      Hello Sergio,

      Considering just those two fields (document number and registration ID), this can be done to identify contacts that have the same document number but differing registration ID.  Here are the column definitions to make this work:

      column 1:  contacts.<document number>

      column 2:  count(distinct(contacts.<registration ID>))

      NOTE:  Due to the aggregate function use in column 2 this automatically causes the report to group results based on the contacts.<document number> field (see in the "Level" tab).  This is intended and needed to find the duplicates.

      Add a group filter (in the Level tab) that will be like this:  count(distinct(contacts.<registration ID>)) greater than 1

      The report could be run at this point and it would output all document Numbers that have more than 1 differing registration ID.  However, if would like to output more info like contacts.c_id or contacts.email, etc. this can be done.  However, fields should not be added as-is (e.g. contacts.c_id) as they will be added into the grouping and will effectively "break" the logic on the report.  If more fields are added they need to be done in a way where they will not be included in the grouping.  Using the "group_concat" function would probably be ideal for use to output additional data.  Below are examples on how do this for the c_id and email fields.

      group_concat(contacts.c_id, ',')

      group_concat(contacts.email, ',')

      Hope this helps you out.

    • Theresa Fandrei

      Our contact center team handles < 200K interactions annually.  This was amazingly helpful for identifying our duplicate contact records.  Thank you so much for sharing!