MailChimp to Salesforce Integration: Contact.Primary Affiliation mapped to MC Org shows up as ID numbers in MailChimp reports - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Elizabeth GilbertElizabeth Gilbert 

MailChimp to Salesforce Integration: Contact.Primary Affiliation mapped to MC Org shows up as ID numbers in MailChimp reports

We are using NPSP and recently, MailChimp to Salesforce.  NPSP uses Primary Affilation for mapping a contact's organization and it is a standard lookup. 

So, our "contacts.primary affilation" is mapped to MC organizations.  For leads, it is mapped to "lead.company".  When we run reports in Mailchimp, the lead records show the organization name, however the contact records show the SF ID value. The ID is annoying at best and we want it to show the actual name of the primary affiliation.   

I contacted MC on this as it seems to me that every SF client has a simular issue, even if it is not NPSP for the lookup to accounts.   They referenced me to acceptable fields for MailChimp https://mailchimp.com/help/about-mailchimp-for-salesforce/#Which_data_takes_priority_when_Salesforce_and_Mailchimp_are_synced%3F which seems to not even include lookups.

Wondering if anyone has had a simular issue and did they find a workaround? 
Best Answer chosen by Elizabeth Gilbert
Elizabeth GilbertElizabeth Gilbert
Follow-on:  In the initial mapping, NPSP Primary Affliation mapped to MailChimp Organization builds a database of Primary Affliation Organization ID's.  Unlike a Contact record lookup, MailChimp uses the exact data in the field.  The problem is all of the Contact Organization Report in MailChimp will show an ID, not the Primary Affliation. 

The answer is to build a Primary Affliation Name field for each record.  This is a simple formula as text that includes this formula "npsp__Primary_Affiliation__r.Name".  Then map the MailChimp Organization name to the Primary Affiliation Name. 

If this is done before activating the Audience, you're golden.  If it is done after activating the audiance, the only way is to 1. start over, delete the MailChimp database and reimport the data or 2. remap the field, then update all contact records and MailChimp, which will update a contact record if it has been updated in the last 24 hours, will update it's database.  The choice depends on if the MC audiance Campaign data is more valuable than the LastModifiedDate.  MailChimp cannot initiate an update independent of this the 24 hour rule.  Unfortunatly, if you followed the MailChimp guide on building this out, it does not include this concept for NPSP.  
 

All Answers

Amnon KruviAmnon Kruvi
Hi Elizabeth, 

Anu lookup or master detail field actually contains the relevant record IDs, and not the name of the record they're related to. It's funny that MC said it's a Salesforce problem, because that is quite literally how all relational databases work - in fact, it's what makes them work. Literally the most important aspect of data storage :) 

What you want to do is map the affiliation's name to MC. I've never used the integration, but one easy way that I can think of would be to create a new formula field on your object, that retrieves the record name. Use the field picker (insert field button) to go to Primary Affiliation > Name. Once you have the formula, map that field to the MC company. 
Elizabeth GilbertElizabeth Gilbert
Amnon, thank you for the idea.  There is no setup guidance on how to map the contact organization to a MC organization.  So, it seems logical to map it to a lookup field.   I had a simular idea about a formula text field, the issue is MailChimp only syncs daily to records that have changed in the last 24 hours.  I'm still waiting for feedback from MC and maybe we'll see some other solutions.
 
Elizabeth GilbertElizabeth Gilbert
Follow-on:  In the initial mapping, NPSP Primary Affliation mapped to MailChimp Organization builds a database of Primary Affliation Organization ID's.  Unlike a Contact record lookup, MailChimp uses the exact data in the field.  The problem is all of the Contact Organization Report in MailChimp will show an ID, not the Primary Affliation. 

The answer is to build a Primary Affliation Name field for each record.  This is a simple formula as text that includes this formula "npsp__Primary_Affiliation__r.Name".  Then map the MailChimp Organization name to the Primary Affiliation Name. 

If this is done before activating the Audience, you're golden.  If it is done after activating the audiance, the only way is to 1. start over, delete the MailChimp database and reimport the data or 2. remap the field, then update all contact records and MailChimp, which will update a contact record if it has been updated in the last 24 hours, will update it's database.  The choice depends on if the MC audiance Campaign data is more valuable than the LastModifiedDate.  MailChimp cannot initiate an update independent of this the 24 hour rule.  Unfortunatly, if you followed the MailChimp guide on building this out, it does not include this concept for NPSP.  
 
This was selected as the best answer