Updated over 6 years ago by Jon Goldberg

Exporting Salsa for CiviCRM

Do you use Pentaho Kettle?

If so, you can use my Salsa to CiviCRM transforms, available here: https://github.com/PalanteJon/civicrm_kettle_transforms

Before you begin

Exporting Supporter Data

When logged in as a Manager, go to "Supporter Management" tab, and "Query/Export" will give you a full export of contact data. There's a "Select My Entire List" button. There's also an "include my deleted supporters" checkbox, which you may want to check (but probably not). This will NOT get you groups/tags, but WILL get you the equivalent of civicrm_contact plus all custom fields attached to the contact.

Exporting Data not on the Contact Entity

You need to get to custom reports. If you have the Reports tab, great; if not, you can get there by clicking "Supporter Management" tab, then "Built-in Reports", then "Clone and Edit" a report, then select "List Your Custom Reports". Or go straight here: https://hq-org.salsalabs.com/dia/hq/reports/list.jsp?table=report




As above, but no conditions, and report on the "Event" table. If you're only importing legacy events, this is a good list of easy fields to import:
Event KEY
Reference Name
Event Name
Maximum Attendees
Date Created (is a useful proxy when "Start" isn't defined)


As above. Report on tables "Supporter Event", then "Event". Note that there are a lot of tables that look like "Supporter Event", like "Supporterevent" and "Supporter Events"!

Note that if you're matching on a unique field from "Events", you can just export "Supporter Event" and not the "Event" table.
Here are the fields I exported and what I matched them to:
Supporter KEY external_identifier
Event KEY event_key
Status status
Type role
Date Created register_date
Last Modified register_date

(I didn't actually map both the date fields to register_date. I use "Date Created" unless it's NULL.

Recurring Donations

Export on table "Recurring Donation". Here's the field mapping:

Recurring Donation Key -> trxn_id
Supporter Key -> external_identifier
Transaction date -> create_date
Start Date -> start_date
RPREF -> invoice_id
RESULT (exclude 37, they're tests)
Amount -> amount
Pay Period -> use to remap to frequency_unit and frequency_interval
TERM -> installments

Note that in Salsa, there's no clear-cut way to tell whether a contact's recurring donation is still working or not - "Status" isn't a complete indicator, and thus doesn't cleanly map into Civi.


It's important to link the recurring donations to the donations when exporting if you need to identify the first recurring donation (e.g. to generate thank-you letters if it's a first donation and recurring).

Under "Conditions", filter by "Supporter KEY is not empty" - when Salsa imports legacy data into their system, sometimes they don't do such a hot job.

Export on table "Donation" linked to table "Donate Page". You'll want these fields (more or less):

Donation.donation KEY
Donation.supporter KEY
donate_page.Reference_Name (contribution_page_id)
Donation.Transaction Date(donation)
Donation.Transaction Type(donation) (financial_type)
Donation.RESULT (only 0 and -1 should be accepted, others are tests)
Donation.Tracking Code(donation) (source)
Donation.Designation Code(donation) (source - not sure what the differentiation between designation and tracking codes is)
Donation.In Honor Name(donation)
Donation.In Honor Email(donation)
Donation.In Honor Address(donation)
Recurring donation.Transaction Date(recurring_donation)
Donation.Form of Payment (payment_instrument)

Special Note: Salsa seems to keep donations that are tied to supporters that don't exist (that is, the supporter_key on the donation doesn't match anyone in Salsa). I've seen this multiple times; I don't know why this is, but this is normal.


Salsa has a separate table for actions, making them structurally comparable to Civi Events. A good match in Civi is the Survey entity, which is used for petitions.

Export as above. Report on table "Action".
Condition: "Reference Name" is not empty.

Action.action KEY
Action.Reference Name
Action.Date Created
Action.Last Modified

Note: "Description" is likely to contain HTML that breaks the export. I find it helpful to export it as the last column.


Export "action" and "supporter_action" and "supporter_action_comment" in that order.
Condition: supporter_key IS NOT NULL

Here's the fields I grabbed with their mapping:
action.Reference Name

(I didn't actually map both the date fields to activity_date_time. I use "Date Created" unless it's NULL.

Letter to the Editor

This isn't a feature CiviCRM has, but it maps reasonably well to an activity when migrating.

Date_Created -> activity_date_time
supporter_KEY -> external_identifier
person_media_ID? -> seems important - but I can't find a matching table, and this data doesn't seem to be accessible from Salsa.
Letter_Subject -> details (concatenated)
Letter_Content -> details
Reference_Name -> subject


Programmatically creating custom fields in CiviCRM

To do this, you'll need to create a custom group, custom fields, option groups, and option values.

Custom Field Group

Don't export anything from Salsa - just create this manually, or using my Kettle transform.

Custom Fields, Option Groups, Option Values

Custom Columns

custom_column_key -> needed to relate to custom_column_options. Store in filter temporarily.
data_table (should = supporter_custom. In theory, we can probably export non-contact custom fields in this same process)
Date_Created -> created_date
label -> label
type -> data_type, html_type (can be bool, enum, text, varchar)
Description -> help_pre
Order -> weight
Name -> value*
custom_column_option_key -> value*
is_a_zero_index_enum -> (if this is true, use the "Name" as value. Else use custom_column_option_key as value).

Option Groups
These will be created automatically when you create custom fields.

Custom Column Options

custom_column_KEY -> a lookup field (against civicrm_custom_field.filter to get option_group_id)
custom_column_option_KEY -> value
value -> name
label -> label
isDefault -> is_default
isDisplayed -> is_active
_Order -> weight

Receive_Email field

Here's a TSV of how to interpret the "Receive_Email" field.
code meaning
-24 INACTIVE: (5.1.1 User Unknown)
-26 INACTIVE: Address contains RFC spec. invalid characters / is improperly formatted
-3 INACTIVE: UNSUBSCRIBED (actively unsubscribed by user)
-30 INACTIVE (Reported as Spam): Other Blacklist (BLACKLIST)
-32 (Unknown Status)
-35 INACTIVE (Reported as Spam): Outblaze
-4 (Unknown Status)
-42 INACTIVE (Recipient Initiated Spam Report): MSN/Hotmail/WebTV
-44 INACTIVE (Recipient Initiated Spam Report): UNTI (Juno/NetZero/FreeServers)
-45 INACTIVE (Recipient Initiated Spam Report): Yahoo
-46 INACTIVE (Recipient Initiated Spam Report): Comcast.net
-47 INACTIVE (Recipient Initiated Spam Report): AOL
-48 INACTIVE (Recipient Initiated Spam Report): RoadRunner
-50 ISP Specific Bounces: AIM.com Unactivated account
-51 INACTIVE (Recipient Initiated Spam Report): Excite
-52 INACTIVE (Reported as Spam): Earthlink
-53 (Unknown Status)
-54 (Unknown Status)
-60 Bad Address/Expired address, etc (n.b.: These addresses are all @democracyinaction.org)
-9 INACTIVE: DEATH (supporter has died)
0 0 - Inactive or Unknown(not subscribed)
1 1 - Imported or unknown (subscribed)
10 ACTIVE: CONFIRMED (Double opt-in)
3 ACTIVE: CLIENT (Client has directly signed up)