PT Commons

{{lastupdated_at}} by {{lastupdated_by}}

h1. Exporting Salsa for CiviCRM

When logged in as a Manager, "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.

h2. 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

*Important*: When selecting foreign keys (e.g. "Supporter KEY") from the Columns tab, be sure to give a custom label.  This will cause the key to display correctly, instead of giving a link to the supporter's record.

h3. Groups

* Create a new report.

* Select the type of report you would like to create: Standard report

* Use "Advanced Object Chooser".

* Report "Supporter Groups", then "Groups".  Press "Save".

* Select the "Columns" tab and select your columns.  I like just "Supporter Key" and "Group Name".

* In Conditions, "Group Name" should be "Not Empty".

* Save, Run the report.

* Click the "Export" link.

h3. Tags

* Create a new report.

* Select the type of report you would like to create: Standard report

* Use "Advanced Object Chooser".

* Report on "Tags", then "Tags Data", then "Database Table".  Press "Save".

* Select the "Columns" tab and select your columns.  I like just "Tag" and "Table Key".

** "Table Key" is the Supporter Key.  It's similar to "entity_id" in CiviCRM.

* In "Conditions", "Table Name" should equal "supporter".

* Add another condition: "Tag.Tag" is "Not Empty".

* Save, Run the report.

* Click the "Export" link.

h3. Events

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

Description

Start

End

Deadline

Maximum Attendees

h3. Participants

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

h3. Donations

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).

Export on table "Donation" linked to table "Recurring Donation".  You'll want these fields:

Donation.donation KEY(donation)	

Donation.supporter KEY(donation)

Donation.Transaction Date(donation)

Donation.amount(donation)

Donation.Transaction Type(donation)

Donation.RESULT(donation)

Donation.First Name(donation)

Donation.Last Name(donation)

Donation.Email(donation)

Donation.Tracking Code(donation)

Donation.In Honor Name(donation)

Donation.In Honor Email(donation)

Donation.In Honor Address(donation)

Recurring donation.Transaction Date(recurring_donation)	

h3. Actions

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.

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

h3. Supporter_Actions

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:

supporter_action.supporter_key	

action.Reference Name

supporter_action.Date_Created

supporter_action_comment.Comment	details

h3. Chapters

* Create a new report.

* Select the type of report you would like to create: Standard report

* Use "Advanced Object Chooser".

* Report "Supporter Chapter", then "Chapter".  Press "Save".

* Select the "Columns" tab and select your columns.  I like just "Supporter KEY" and "Chapter.Name".

* In Conditions, "Chapter.Name" should be "Not Empty".

* Save, Run the report.

* Click the "Export" link.

h3. 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)|