Project

General

Profile

Exporting Salsa for CiviCRM » History » Revision 46

Revision 45 (Jon Goldberg, 06/22/2015 04:17 PM) → Revision 46/54 (Jon Goldberg, 10/16/2015 12:16 PM)

{{lastupdated_at}} by {{lastupdated_by}} 

 h1. Exporting Salsa for CiviCRM 

 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. 

 *IMPORTANT* - Before you begin - When exporting from Salsa, exporting to screen and exporting to a file yield different formats!    Be sure to be consistent, or you'll find that your field names change, your date formats change, etc.    *If you're exporting to give data to Palante, please use the scheduled export*. 

 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 
 Creation Date Created (is a useful proxy when "Start" isn't defined) 

 

 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. 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 
 Status(?) 

 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. 

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

 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) 	
 Donation.supporter KEY(donation) 
 donate_page.Reference_Name    (contribution_page_id) 
 Donation.Date_Entered   
 Donation.Transaction Date(donation) 
 Donation.amount(donation) 
 Donation.Transaction Type(donation) (financial_type) 
 Donation.RESULT(donation) (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.Order_Info 
 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. 


 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. Letter to the Editor 

 This isn't a feature CiviCRM has, but it maps reasonably well to an activity when migrating. 
 * Create a new report. 
 * Select the type of report you would like to create: Standard report 
 * Use "Advanced Object Chooser". 
 * Report "Letter Supporter", then "Letter".    Press "Save". 
 * Select the "Columns" tab and select your columns: 
 > 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 
 * Save, Run the report. 
 * Click the "Export" link. 

 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. 

 h2. Programmatically creating custom fields in CiviCRM 

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

 h3. Custom Field Group 

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

 h3. Custom Fields, Option Groups, Option Values 

 _Custom Columns_ 
 * Create a new report. 
 * Select the type of report you would like to create: Standard report 
 * Use "Advanced Object Chooser". 
 * Report "Custom Columns".    Press "Save". 
 * Select the "Columns" tab and select your 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 
 * Save, Run the report. 
 * Click the "Export" link. 
 * Do NOT import the Salsa name, it'll screw up Civi. 

 _Option Groups_ 
 These will be created automatically when you create custom fields. 

 _Custom Column Options_ 
 * Create a new report. 
 * Select the type of report you would like to create: Standard report 
 * Use "Advanced Object Chooser". 
 * Report "Custom Column Options".    Press "Save". 
 * Select the "Columns" tab and select your columns: 
 > 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 
 * 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)|
Go to top