One of our clients asked us to help them migrate some of their existing marketing list data from the older CRM 4.0 into another CRM 2011 environment. This process would not be necessary if the system was upgraded but unfortunately they are migrating between on-premise to an online environment.
Marketing lists act like N:N relations to contacts/accounts/leads – this can be difficult to export and import into another CRM system.
This blog will show you one way to accomplish the export/import – it will require a little extra work but can be done without the need of 3rd party tools.
I would call this a “Flagging” type approach and it would involve the following (we’ll use a contact-based marketing list as an example):
1. First, we will need to create a new text field “Associated Marketing List” to contacts.
This field will be used to record all the MarketingLists a contact belongs to with a series of strings. Since contacts can belong to many marketing lists and vice versa (N:N), insure you have enough space to track all of them.
2. If you haven’t imported records yet, then just export the list of contacts from CRM 4.0 you wish to add into CRM2011. Otherwise, from CRM2011, export the contacts; make sure to check the box at the bottom to “Make this data available for re-importing…” save this for re-importing later. Also modify the columns to include the field created in step1.
3. This third step can be the most tedious, but it must be done. What we need is to use Excel to help populate a string that will go into that “Associated Marketing List” field we created from step1. Use Excel to create a master list of the records, contacts in this case, with the marketing lists they are associated with. This list will help identify which contacts will belong to which marketing lists structurally:
a. One column should be used as the contact’s unique identifier. We will be using Excel’s VLOOKUP* function later to help map between this spreadsheet and the one we need to import with. In the above example, I used a combination of fullname + email
b. The following columns should represent the series marketing lists these contacts may be associated with.
c. The purpose of this spreadsheet is to create a string that lists all the MarketingLists each contact is associated with so add one more additional column at the end called “Associated Marketing List”.
d. There are many ways to populate this last column but what you want is a string representing all the different marketing lists the contact is associated with – one way is to use excel to concatenate all the marketing lists separated by an empty space (ie: ML01 ML03 to represent a contact that belongs to Marketinglist #1 and Marketinglist #3).
4. Now add the “Associated Marketing List” column into the spreadsheet you want to import into CRM. You will need to use the VLOOKUP* function to help map between the spreadsheet created in the previous step and the spreadsheet you want to import with using a common unique identifier.
5. Now re-import the updated spreadsheet back into CRM. The records that belong to marketing lists should have the “Associated Marketing List” field populated.
8. Now the final step is to add those contacts to your marketing lists. Navigate to the marketing list and click the “Manage Members” button from the Marketing List Members frame and choose to “Use Lookup to add members” – you should be able to search by the terms you’ve added into the “associated marketing list” field. Use the wildcard character * before your searching string to search the entire string.
9. After everything is complete, you can remove and delete the field we’ve created or keep it for reference temporarily to insure everything has been assigned correctly.
*VLOOKUP: we use this excel function quite a lot in data migration – it helps to correctly map data between two difference sources that contain the same reference to records. More information here: http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
Contact me if you need help with your Microsoft CRM. Free Estimate.
FREE Microsoft CRM Online 30 Day Trial access with our Professional Assistance!
Harold Lee, Microsoft CRM Consultant