HOWTO Find Duplicates Btwn Leads and Contacts in a Campaign

[GerryKey]

I have to believe this question has been asked before, but various keyword searches have failed to provide an answer.

QUESTION: Is there a way in Sugar to find and remove duplicates between target lists drawn from Leads and those drawn from Contacts? We are using Sugar Professional 6.1.3.

I've read that in an email campaign Sugar will not send more than one email to the same email address, but that which list is chooses to send from - Contacts, Leads, Targets - is random. We want to force it to send from the list of our choosing.

Because Sugar retains the Leads record for a person who we elevate to a contact, this creates the situation where the same person may appear in a list drawn from Contacts and one drawn from Leads. Since we stop updating Leads records for people who have been elevated to Contacts, the more current information will be in the Contacts record and we want Sugar to use the information in that record, and not the Leads record, to send the email.

The only way I can see to do this is to create the reports to generate the list of Contacts and the list of Leads, export them to .CSV files, concatenate them in Excel, sort them on various fields (email, name, etc.) looking for duplicates, then go into the target lists created from the Leads report and selectively remove the records for anyone who also appears in the Contacts list.

The same would also be true for a Targets list we imported from an external source that may contain people who already appear on the Leads or Contacts lists.

Very time-consuming and error-prone. There has to be a better way.

I've looked at NetSearch for SugarCRM, but it seems to be overkill, and while there have been >5K downloads, there are no reviews ... which makes me nervous.

--Gerry

[davidboris]

Hello,

If I am not mistaken, your Leads will be converted to Contacts, which are having same email address.

As you are using Professional Edition of SugarCRM, you have an option to use Reports module to filter the records of Leads/Contacts module, and add filtered data into a Target List. There is a button, Select from Reports button in subpanel.

[GerryKey]

>If I am not mistaken, your Leads will be converted to Contacts, which are having same email address.

We've already converted the Leads to Contacts that we want to: Now we want to select some of those Leads and some of those Contacts and add them to a target list for a campaign, without duplication between the lists.

As you are using Professional Edition of SugarCRM, you have an option to use Reports module to filter the records of Leads/Contacts module, and add filtered data into a Target List. There is a button, Select from Reports button in subpanel.

Yes, that's how we generated the reports that will want to Select to include in the target lists for the campaign. The problem - and the reason for my original posting - is that the same person may appear in both the Leads report and the Contacts report, and I know of no way in Sugar to:

a) Write a report that queries (filters) against both the Leads and Contacts modules in the same query, since the first choice you have to make in creating a filter is the module to be queried, and consequently ...

b) How to write a query that would exclude Contacts that also appear in the Leads module.

--Gerry

[dcolley]

b) How to write a query that would exclude Contacts that also appear in the Leads module.
--Gerry

What about using two lists - one for contact and one for leads. Then use the leads list as an exclude list?

[GerryKey]

What about using two lists - one for contact and one for leads. Then use the leads list as an exclude list?

We don't want to exclude all Leads, only those that are also on the Contacts list.

I think the way to do what we want is to include Status <> "Converted" among the filter criteria for the reports that generate the lead lists. This will require us to go back and make sure that is the case for all previously converted leads. Even though Sugar inserts Status = "Converted" when you convert a lead to a contact, over the years people have mucked with those leads values so we cannot guarantee the results at this point. But this should be a one-time clean-up - assuming that going forward everyone understands to not make changes to leads records if they have been converted to leads.