CiviCRM naar Salesforce: 23 veldmappings die stil bijten

Een success log die loog

Utrecht, woensdagavond 23:14. De migratieconsultant draait Salesforce Data Loader, kijkt hoe de teller doortikt naar 31.847. Nul fouten. Hij klapt de laptop dicht. De volgende ochtend stuurt het ledenteam zeventien 'adres onbekend'-antwoorden door uit hun nieuwsbriefplatform, tegen lunchtijd nog veertig. Het probleem is geen importfout. Het probleem is dat info+facturatie@vereniging-langnaam-zorg.nl stil werd afgehakt tot info+facturatie@vereniging-langn op het moment dat het het standaard Contact.Email-veld raakte, en daarna doorrolde naar elke downstream workflow.

Dit is de migratie die we vorig jaar deden voor een zorgvereniging die we hier de Vereniging noemen. 31.000 leden, vijftien jaar data, CiviCRM 4.7 op Drupal 7. De opdracht was helder: landen op Salesforce NPSP voordat Drupal 7 community end of life raakte, de lidmaatschapstiers behouden, de historische contributies behouden, geen enkele relatielink kwijtraken. De schemamapping was niet helder. Wat volgt is de spiekbrief die we op dag één hadden willen hebben.

Waar de stille truncaties wonen

Het datamodel van CiviCRM is een stapel netjes genormaliseerde tabellen: civicrm_contact, civicrm_email, civicrm_phone, civicrm_address, civicrm_membership, gekoppeld op contact_id. Salesforce wil één dikke rij per Contact, met email en telefoon gedenormaliseerd als primaire velden en de rest verdeeld over npe01__OppPayment__c, npe03__Recurring_Donation__c en de custom objects die met NPSP meekomen.

De vormverschillen zijn niet het probleem. Elke ETL'er kan rijen pivoteren. Het probleem is dat onderweg drie categorieën fouten optreden zonder dat iemand het je vertelt.

1. String overruns. CiviCRM laat een veld 254 tekens vasthouden. Het equivalent in Salesforce houdt er 80. Data Loader kapt af en gaat door.
2. Picklist drift. De OptionValue-tabel in CiviCRM bevat waarden als Lid - actief. De Salesforce picklist heeft Active member. Met strikte picklist-enforcement uit wordt het veld zonder foutmelding op null gezet.
3. Lookup misses. NPSP-relaties hebben een target Contact ID nodig, geen naam. Als de lookup mist, wordt de relatie gedropt, maar de parent record importeert prima.

De spiekbrief hieronder is de veldmapping voor de Vereniging-migratie, ruwweg geordend op hoeveel data we kwijtraakten toen we het probleem niet vooraf vingen. Severity is onze eigen schaal: S1 betekent data verloren zonder spoor, S2 betekent data verloren maar de rij wordt als failed gerapporteerd, S3 betekent data bewaard maar zo getransformeerd dat downstream workflows breken.

Tier S1, de stille afkappers

1. Email (primair). civicrm_email.email is varchar(254), volgens RFC 5321. De standaard Salesforce Contact.Email is 80. Eerste pass verloren we 412 adressen. Lange Nederlandse domeinen met plus-aliassen sneuvelen als eerste.
2. Display name. civicrm_contact.display_name is 128. SF Name wordt berekend uit FirstName (40) + LastName (80). Iedereen met een tussenvoegsel (van der, de) en een dubbele achternaam loopt over.
3. Contact source. civicrm_contact.source is 255. SF LeadSource picklist-waarden zitten op 40 tekens max. Map vrije tekst naar een picklist met strict mode uit en elke niet-gematchte waarde wordt null.
4. Notes. CiviCRM bewaart notes als rich HTML in civicrm_note.note. De NPSP ContentNote-route strijkt tags eruit en kapt content stil af op 128 KB. Lange ledenhistorie-notes verliezen alineabreuken en daarna alles voorbij de cap.
5. Salutation. CiviCRM heeft vrije tekst in prefix (varchar 64). SF Salutation is een restricted picklist. Prof. dr. matcht niet met Prof.
6. Custom long text. NPSP custom long-text velden staan default op 32.768 tekens. Alles langer wordt op API-niveau afgekapt zonder waarschuwing in de bulk job summary.

Tier S2, de type-conflicten die de rij laten falen

7. Geboortedatum. CiviCRM bewaart YYYY-MM-DD als DATE. Salesforce verwacht hetzelfde formaat, maar elke record met de legacy 0000-00-00 sentinel (MySQL strict mode uit) wordt geweigerd.
8. is_deceased. CiviCRM bewaart 1 en 0 als tinyint. SF verwacht TRUE of FALSE. De CSV-route van Data Loader accepteert beide. Bulk API v2 accepteert alleen het laatste.
9. Gender. civicrm_contact.gender_id is een foreign key naar civicrm_option_value. NPSP npsp__Gender__c is een picklist. Drie Vereniging-leden hadden een custom Anders-waarde die niet in de NPSP default set zat.
10. Phone. CiviCRM staat willekeurige formattering toe. SF Phone is 40 tekens en accepteert van alles bij insert, maar de validatieregels die NPSP meelevert weigeren niet-E.164-input bij een latere update.
11. Adres land. civicrm_country.iso_code is ISO 3166 alpha-2. Als SF State en Country picklists aanstaan (default voor nieuwe orgs), matcht NL, maar historische Nederlandse overzeese adressen getagd als AN (Nederlandse Antillen, ingetrokken in 2010) falen.
12. Currency. Multi-currency orgs hebben CurrencyIsoCode op elke rij nodig. De Vereniging had tweeëndertig CHF-contributies van een Zwitserse zustervereniging. Zonder de kolom importeerden ze als EUR.
13. Decimal precisie. SF Number(16,2) accepteert tot 18 cijfers maar kapt stil af op 2 decimalen. civicrm_contribution.total_amount in Civi is decimal(20,2). Driedecimale waarden ronden af zonder rij-fout.

Tier S3, de transformaties die downstream breken

14. Owner. Civi kent geen record ownership. SF defaultet naar de draaiende gebruiker. Als je integratiegebruiker het verkeerde profiel heeft, landt elk contact onder die gebruiker en duikt op in niemands list views.
15. RecordType. NPSP gebruikt Household_Account en Organization. Zonder expliciete RecordTypeId landt elk contact onder de default. Bulk re-assignment achteraf triggert de duplicate rules.
16. Tags. civicrm_tag is many-to-many via civicrm_entity_tag. SF heeft Topics, of je bouwt een junction object. De multi-select picklist semicolon-delimiter is een val: komma-gescheiden input wordt als één waarde opgeslagen.
17. Groups. CiviCRM smart groups (dynamisch) hebben geen equivalent in Salesforce. Statische groepen worden Campaigns of Public Groups, afhankelijk van of ze lidmaatschap-semantiek of sharing-semantiek hebben.
18. Relationships. NPSP npsp__Relationship__c heeft beide Contacts nodig die al bestaan. Importeer je in één pass, dan valt de helft van de relaties weg. Twee-pass import: eerst Contacts, daarna relationships, verankerd op external ID.
19. Memberships. civicrm_membership heeft status, type, datums. NPSP levert membership niet native; installeer het Membership Management package of bouw custom objects. Wij bouwden custom omdat de status engine van het package niet matchte met de tier-regels van de Vereniging.
20. Contributions. civicrm_contribution mapt op Opportunity met StageName="Closed Won" voor ontvangen betalingen. De default NPSP stage-waarden zijn Engels. Het Vereniging-team werkt in het Nederlands. Of vertaal de picklist, of accepteer Engelse stages intern.
21. Activities. civicrm_activity met activity_type_id mapt op Task of Event. Civi kan één activity aan meerdere contacten toewijzen. SF wil één Task per assignee, dus de rij-count groeit onderweg naar binnen.
22. Email opt-out. civicrm_contact.is_opt_out (tinyint) mapt op HasOptedOutOfEmail (boolean). De catch: het Civi-veld is de inverse van do_not_email, en het Vereniging-team had die twee een decennium lang inconsistent gebruikt.
23. IBAN. SF heeft geen native IBAN-type. Wij bewaren als Text(34) met een validatieregel. IBANs met een leading zero (bijvoorbeeld NL02ABNA...) worden door Excel tijdens de CSV-stop als nummer behandeld. Exporteer altijd als CSV met quoted strings, nooit via Excel.
24. External ID. Voeg op elk geïmporteerd object een custom veld CiviCRM_ID__c toe als External ID, Unique, Indexed. Dit is de enige mapping die je niet kunt overslaan. Zonder dit heeft de tweede-pass import voor relaties, activities en contributies geen anker.

De SQL waarmee we Civi platsloegen voor export

SELECT
  c.id                            AS civicrm_id,
  c.contact_type,
  c.first_name,
  c.last_name,
  c.middle_name,
  c.prefix_id,
  c.gender_id,
  c.birth_date,
  c.is_deceased,
  c.do_not_email,
  c.source                        AS contact_source,
  e.email                         AS primary_email,
  LENGTH(e.email)                 AS email_len,
  p.phone                         AS primary_phone,
  a.street_address,
  a.city,
  a.postal_code,
  co.iso_code                     AS country_iso
FROM civicrm_contact c
LEFT JOIN civicrm_email   e  ON e.contact_id = c.id AND e.is_primary = 1
LEFT JOIN civicrm_phone   p  ON p.contact_id = c.id AND p.is_primary = 1
LEFT JOIN civicrm_address a  ON a.contact_id = c.id AND a.is_primary = 1
LEFT JOIN civicrm_country co ON co.id = a.country_id
WHERE c.is_deleted = 0
  AND c.contact_type = 'Individual';

De email_len-kolom verdiende zijn plek. We sorteerden voor de eerste importpass aflopend op die kolom en vonden 412 rijen boven de 80 tekens. Het beleid van de Vereniging: lang adres in CiviCRM laten staan, een forwarder aanmaken, de forwarder in het Salesforce-veld zetten. Uiteindelijk geen enkel adres verloren. Zonder de lengtekolom waren alle 412 afgekapt geland en hadden we het een week later uit bounce reports moeten leren.

Het pre-flight script dat de tweede pass redde

Voor elke batch draaiden we een lengte-audit tegen het SF-schema, één keer opgehaald uit het tooling/sobjects/Contact/describe endpoint en lokaal gecached. Als een bronwaarde de doel-lengte overschreed, ging de rij naar een review-CSV in plaats van mee te gaan.

import csv

SF_LIMITS = {  # pulled once from describe(), cached locally
    "Email":             80,
    "FirstName":         40,
    "LastName":          80,
    "Phone":             40,
    "MailingStreet":    255,
    "MailingPostalCode": 20,
    "Salutation":        40,
    "LeadSource":        40,
}

with open("civi_export.csv")  as src, \
     open("ready.csv",  "w")  as ok, \
     open("review.csv", "w")  as bad:

    r     = csv.DictReader(src)
    w_ok  = csv.DictWriter(ok,  fieldnames=r.fieldnames)
    w_bad = csv.DictWriter(bad, fieldnames=r.fieldnames + ["overflow"])
    w_ok.writeheader(); w_bad.writeheader()

    for row in r:
        overflow = [
            f"{k}:{len(row[k])}>{SF_LIMITS[k]}"
            for k in SF_LIMITS
            if k in row and len(row[k]) > SF_LIMITS[k]
        ]
        if overflow:
            row["overflow"] = ", ".join(overflow)
            w_bad.writerow(row)
        else:
            w_ok.writerow(row)

Vijf minuten schrijven, drie dagen gewonnen aan de achterkant. De review-CSV ging naar het ledenteam, niet naar de developers; data-eigenaren beslisten wat ingekort werd, wat doorgestuurd, wat gedropt. Dat gesprek is de migratie. De bulk job is het saaie naspel.

Het kleinste dat je vandaag kunt doen

Als je op een CiviCRM-stack zit en een Salesforce-migratie staat op de roadmap, draai bovenstaande SQL vanavond tegen je eigen database. Sorteer op email_len DESC. Alles boven de 80 is je eerste gesprek met de data-eigenaren, voordat je überhaupt aan ETL-tooling denkt. De spiekbrief van 23 rijen is het tweede gesprek, niet het eerste.

Toen we de Vereniging-migratie bouwden, was het ding waar we steeds tegenaan liepen Tier S1: data verloren zonder log-regel. De fix die zichzelf terugverdiende was de pre-flight describe-vs-source diff hierboven, en alles downstream daarvan was zorgvuldige uitvoering. Wil je een hand bij een CiviCRM-naar-Salesforce of een andere legacy migratie, dan is die diff het bestand dat we als eerste sturen.