Mass Update - Lead to Contact

[hectorhrb]

Does anyone know how i can convert 300 leads at once into contacts. I tried Mass Update right below it and selected converted. It only seems to have changed the Leads status to converted but not over to a Contact.

[donl]

There is no GUI way to do it. The process below is what I pieced together from the Sugar University documents and from some info from Sugar. It is tedious. I used this process to import ACT contact and Note information into Sugar and it worked well.

The concept is this:

Every object in Sugar—lead, contact, account, note, call, etc. has an ID associated with it. Sugar uses the ID numbers to link data. A Contact is linked to an Account’s ID in order to associate the Contact with the Account. What you need to do is export your Leads, import the Account part of the Lead data and get the new ID number. You then import the Leads as Contacts, using the ID from the Accounts you just created to link the new contact properly. You then export the Contacts you just created to get their new ID numbers and use the new ID numbers to link Notes, Calls, etc. to the Contact. Then you should probably delete the old Leads, Calls, Contacts, etc. to avoid a duplication.

Anyway, I bet that a MySQL guru could do it all by manipulating the MySQL database, but I am not that person. I did the ACT import in Excel and while it will be tedious to do this to mass convert my leads, it will be far less annoying than converting 4400 leads one by one.

Before you do this, view all the related Sugar University material on importing, exporting of data and related records. And, back up your database before you start.

1. It is a good idea to export 1 Account, Contact, Note, Call, and every other object you want to create so that you know what fields exist in each record.
2. Export your leads into a “Leads Exported” file.
3. Create the Account Import file with data from the Leads Exported file.
4. Save as an “Accounts to be Imported” file.
5. Import the Accounts to be Imported file and verify.
6. Export the new Accounts into an “Accounts Created” file.
7. Add the Account ID from the Accounts Created file to the Leads Exported file. I assume you’d build a lookup table in Excel or a use a database, depending on what you’re comfortable with. Make sure to change the type of data from Leads to Contacts and, if you are using Excel and have New England data, fix your zipcodes before you import.
8. Save the Leads with the new Accounts IDs as a “Contacts to Be Imported” file.
9. Import the Contacts to Be Imported file and verify.
10. Export the new Contacts into a “Contacts Created” file.
11. Export whatever Notes need to be converted into a “Notes Exported” file.
12. Link the new contact ID number in the Contacts Created file to the Contact ID field in the Exported Notes file.
13. Save that as a “Notes to be Imported” file.
14. Import and verify.
15. Repeat steps 9-12 for whatever Activities, Calls, Meetings, etc. need to be converted.
16. You probably want to delete the old data to avoid having duplicates in the system.

[donl]

In speaking with Sugar, they warn against putting the Account ID into the Contacts import file, as in step 7. They say the account names should be automatically assigned when you update the contacts. i am testing it and will post a reply. T

They say you will hose your installation if you add the Account ID number. They should know so i will follow their advice.

[semr]

I am facing a similar situation where I need to do a mass conversion of leads and have them be both an account and an opportunity. Is exporting and then importing really the only way to do this?

[datasponge]

This can be done thruogh MySQL directly, but it's not for the faint of heart. But if you're the very adventurous sort, read on...

PLEASE NOTE I AM NOT AN EXPERIENCED SUGARCRM USER. I am experienced with MySQL and the table structure is fairly clear, but make sure you know what you're doing or get the steps reviewed by an expert before proceeding on direct database changes.

We are not yet in production, so I move over 20,000 records from our old CRM system into Sugar with a script every night blowing away and replacing all the contact data from the prior days updates. The script has only about 25 SQL queries to populate Sugar and it has been working great for a few weeks while we test and customize Sugar.

Based on that experience, here are the basic SQL steps I would take

First, note that Sugar uses UUID's as their primary key, so this provides the benefit that for practical purposes, each UUID is guaranteed unique. That means that a UUID is unique across multiple tables. Since you are converting the leads into contacts you should be able to use the same id value from Leads in Contacts. That makes it much simpler to link up to Notes, Emails, etc. It also means that you have a stable data that you can always get to the Lead using the contact id.

1. insert leads fields directly into contacts with an insert into query selecting data from leads
2. insert new uuids along with the contact_id into accounts_contacts using the UUID() function and a join between contacts and leads and the same leads selection criteria. Note we do accounts_contacts first to save the relationship between the tables so we can use this table to get to either set of data and id's.
3. insert new records into accounts from a join between accounts_contacts and leads to select the new UUIDs entered into accounts_contacts
4. insert new records into accounts_cstm and/or contacts_cstm in the same way if you have custom fields
5. update the contact_id field in notes from leads.id with the same selction criteria. Note that there is a separate field in notes to map specifically to contact_id.
6. update the parent_type and parent_id in notes if desired to point to the accounts record if you want to see the notes for the account as well as the contact
7. insert new records into email_addr_bean_rel to map any email addresses to the new contacts
8. update leads to set the converted field so that it is no longer displayed as a lead. Note that the converted field should be used in the selection criteria so that you don't try to convert already converted leads.

Review and make sure any other data you have connected to leads gets updated as well.

The queries can be written in a text file saved as sugarcrm.sql (the database name) and run as input to the mysql command. You can run it against a replicated copy of your database and point sugar to the database copy temporarily in config.php to see that all the data was mapped properly.

As always, and particularly when you're going through the back door, make a backup copy of everything. I use mysqldump to make a full copy of the entire sugarcrm database. I also backup all sugarcrm files in the entire folder tree using cp -R -p

Also, if this mass conversion is something you will do often I recommend structuring your queries to select records based on a separate temporary table of lead ids. That way you can simply populate the table with the lead ids and use the same script over and over for a different set of leads to convert. This is where the effort pays off. For a one-time conversion, it's proabably not worth the effort of writing all the queries and testing everything.

Phil

[ecole]

This does need A GUI fix. Is there a request for enhancement in? I am a newbie, looking to use this software seriously in my business. All these "Smilies" and "Post Icons" do make me wonder however; is this serious software for Business or just a bunch of geeks having a laugh??