Project

General

Profile

Exporting Salsa for CiviCRM » History » Version 46

Jon Goldberg, 10/16/2015 12:16 PM

1 24 Jon Goldberg
{{lastupdated_at}} by {{lastupdated_by}}
2
3 1 Jon Goldberg
h1. Exporting Salsa for CiviCRM
4
5 45 Jon Goldberg
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.
6 1 Jon Goldberg
7 41 Jon Goldberg
*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*.
8 40 Jon Goldberg
9 14 Jon Goldberg
h2. Exporting Data not on the Contact Entity
10 2 Jon Goldberg
11 25 Jon Goldberg
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
12 1 Jon Goldberg
13 8 Jon Goldberg
*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.
14
15 11 Jon Goldberg
h3. Groups
16 12 Jon Goldberg
17 3 Jon Goldberg
* Create a new report.
18
* Select the type of report you would like to create: Standard report
19
* Use "Advanced Object Chooser".
20 9 Jon Goldberg
* Report "Supporter Groups", then "Groups".  Press "Save".
21 1 Jon Goldberg
* Select the "Columns" tab and select your columns.  I like just "Supporter Key" and "Group Name".
22 7 Jon Goldberg
* In Conditions, "Group Name" should be "Not Empty".
23 4 Jon Goldberg
* Save, Run the report.
24
* Click the "Export" link.
25 1 Jon Goldberg
26 11 Jon Goldberg
h3. Tags
27 12 Jon Goldberg
28 5 Jon Goldberg
* Create a new report.
29
* Select the type of report you would like to create: Standard report
30
* Use "Advanced Object Chooser".
31
* Report on "Tags", then "Tags Data", then "Database Table".  Press "Save".
32
* Select the "Columns" tab and select your columns.  I like just "Tag" and "Table Key".
33 1 Jon Goldberg
** "Table Key" is the Supporter Key.  It's similar to "entity_id" in CiviCRM.
34 7 Jon Goldberg
* In "Conditions", "Table Name" should equal "supporter".
35 10 Jon Goldberg
* Add another condition: "Tag.Tag" is "Not Empty".
36 1 Jon Goldberg
* Save, Run the report.
37
* Click the "Export" link.
38
39 11 Jon Goldberg
h3. Events
40 1 Jon Goldberg
41 15 Jon Goldberg
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:
42
Event KEY
43
Reference Name
44
Event Name
45
Description
46
Start
47
End
48
Deadline
49
Maximum Attendees
50 46 Jon Goldberg
Date Created (is a useful proxy when "Start" isn't defined)
51 15 Jon Goldberg
52 1 Jon Goldberg
h3. Participants
53 16 Jon Goldberg
54
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"!
55
56
Note that if you're matching on a unique field from "Events", you can just export "Supporter Event" and not the "Event" table.
57
Here are the fields I exported and what I matched them to:
58
Supporter KEY	external_identifier
59
Event KEY	event_key
60
Status	status
61
Type	role
62
Date Created	register_date
63
64 28 Jon Goldberg
h3. Recurring Donations
65
66
Export on table "Recurring Donation".  Here's the field mapping:
67
68
Recurring Donation Key -> trxn_id
69
Supporter Key -> external_identifier
70
Transaction date -> create_date
71
Start Date -> start_date
72
RPREF -> invoice_id
73
RESULT (exclude 37, they're tests)
74
Amount -> amount
75
Pay Period -> use to remap to frequency_unit and frequency_interval
76
TERM -> installments
77
Status(?)
78
79
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.
80
81 27 Jon Goldberg
h3. Donations
82
83
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).
84
85 30 Jon Goldberg
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.
86
87 29 Jon Goldberg
Export on table "Donation" linked to table "Donate Page".  You'll want these fields (more or less):
88 27 Jon Goldberg
89
Donation.donation KEY(donation)	
90 1 Jon Goldberg
Donation.supporter KEY(donation)
91 42 Jon Goldberg
donate_page.Reference_Name  (contribution_page_id)
92
Donation.Date_Entered  
93 27 Jon Goldberg
Donation.Transaction Date(donation)
94 1 Jon Goldberg
Donation.amount(donation)
95 43 Jon Goldberg
Donation.Transaction Type(donation) (financial_type)
96
Donation.RESULT(donation) (only 0 and -1 should be accepted, others are tests)
97 42 Jon Goldberg
Donation.Tracking Code(donation)  (source)
98
Donation.Designation Code(donation)  (source - not sure what the differentiation between designation and tracking codes is)
99 1 Jon Goldberg
Donation.In Honor Name(donation)
100
Donation.In Honor Email(donation)
101 27 Jon Goldberg
Donation.In Honor Address(donation)
102
Recurring donation.Transaction Date(recurring_donation)
103 1 Jon Goldberg
Donation.Order_Info
104 42 Jon Goldberg
Donation.Form of Payment (payment_instrument)
105 27 Jon Goldberg
106 44 Jon Goldberg
*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.
107
108
109 19 Jon Goldberg
h3. Actions
110 17 Jon Goldberg
111 19 Jon Goldberg
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.
112 1 Jon Goldberg
113 19 Jon Goldberg
Export as above. Report on table "Action".
114
Condition: "Reference Name" is not empty.
115
116
*Note*: "Description" is likely to contain HTML that breaks the export.  I find it helpful to export it as the last column.
117
118
h3. Supporter_Actions
119 11 Jon Goldberg
120 22 Jon Goldberg
Export "action" and "supporter_action" and "supporter_action_comment" in that order.
121 23 Jon Goldberg
Condition: supporter_key IS NOT NULL
122 20 Jon Goldberg
123
Here's the fields I grabbed with their mapping:
124 22 Jon Goldberg
supporter_action.supporter_key	
125 21 Jon Goldberg
action.Reference Name
126 22 Jon Goldberg
supporter_action.Date_Created
127
supporter_action_comment.Comment	details
128 20 Jon Goldberg
129 31 Jon Goldberg
h3. Letter to the Editor
130
131
This isn't a feature CiviCRM has, but it maps reasonably well to an activity when migrating.
132
* Create a new report.
133
* Select the type of report you would like to create: Standard report
134
* Use "Advanced Object Chooser".
135
* Report "Letter Supporter", then "Letter".  Press "Save".
136
* Select the "Columns" tab and select your columns:
137
> Date_Created -> activity_date_time
138
> supporter_KEY -> external_identifier
139
> person_media_ID? -> seems important - but I can't find a matching table, and this data doesn't seem to be accessible from Salsa.
140
> Letter_Subject -> details (concatenated)
141
> Letter_Content -> details
142
> Reference_Name -> subject
143
* Save, Run the report.
144
* Click the "Export" link.
145
146 11 Jon Goldberg
h3. Chapters
147 12 Jon Goldberg
148 7 Jon Goldberg
* Create a new report.
149 1 Jon Goldberg
* Select the type of report you would like to create: Standard report
150 7 Jon Goldberg
* Use "Advanced Object Chooser".
151 8 Jon Goldberg
* Report "Supporter Chapter", then "Chapter".  Press "Save".
152
* Select the "Columns" tab and select your columns.  I like just "Supporter KEY" and "Chapter.Name".
153
* In Conditions, "Chapter.Name" should be "Not Empty".
154 5 Jon Goldberg
* Save, Run the report.
155 1 Jon Goldberg
* Click the "Export" link.
156
157 32 Jon Goldberg
h2. Programmatically creating custom fields in CiviCRM
158
159
To do this, you'll need to create a custom group, custom fields, option groups, and option values.
160
161
h3. Custom Field Group
162
163
Don't export anything from Salsa - just create this manually, or using my Kettle transform.
164
165
h3. Custom Fields, Option Groups, Option Values
166
167 33 Jon Goldberg
_Custom Columns_
168
* Create a new report.
169
* Select the type of report you would like to create: Standard report
170
* Use "Advanced Object Chooser".
171
* Report "Custom Columns".  Press "Save".
172
* Select the "Columns" tab and select your columns:
173 36 Jon Goldberg
> custom_column_key -> needed to relate to custom_column_options.  Store in filter temporarily.
174 34 Jon Goldberg
> data_table (should = supporter_custom.  In theory, we can probably export non-contact custom fields in this same process)
175 33 Jon Goldberg
> Date_Created -> created_date
176
> label -> label
177
> type -> data_type, html_type (can be bool, enum, text, varchar)
178
> Description -> help_pre
179
> Order -> weight
180 38 Jon Goldberg
> Name
181 33 Jon Goldberg
* Save, Run the report.
182 1 Jon Goldberg
* Click the "Export" link.
183 37 Jon Goldberg
* Do NOT import the Salsa name, it'll screw up Civi.
184 1 Jon Goldberg
185 35 Jon Goldberg
_Option Groups_
186 36 Jon Goldberg
These will be created automatically when you create custom fields.
187 35 Jon Goldberg
188 1 Jon Goldberg
_Custom Column Options_
189 33 Jon Goldberg
* Create a new report.
190
* Select the type of report you would like to create: Standard report
191
* Use "Advanced Object Chooser".
192
* Report "Custom Column Options".  Press "Save".
193
* Select the "Columns" tab and select your columns:
194 1 Jon Goldberg
> custom_column_KEY -> a lookup field (against civicrm_custom_field.filter to get option_group_id)
195 37 Jon Goldberg
> custom_column_option_KEY -> value
196
> value -> name
197 33 Jon Goldberg
> label -> label
198
> isDefault -> is_default
199
> isDisplayed -> is_active
200
> _Order -> weight
201
* Save, Run the report.
202
* Click the "Export" link.
203 7 Jon Goldberg
204 1 Jon Goldberg
h3. Receive_Email field
205
206 26 Jon Goldberg
Here's a TSV of how to interpret the "Receive_Email" field.
207
|*code*|*meaning*|
208
|-24|INACTIVE: (5.1.1 User Unknown)|
209
|-26|INACTIVE: Address contains RFC spec. invalid characters / is improperly formatted|
210
|-3|INACTIVE: UNSUBSCRIBED (actively unsubscribed by user)|
211
|-30|INACTIVE (Reported as Spam): Other Blacklist (BLACKLIST)|
212
|-32|(Unknown Status)|
213
|-35|INACTIVE (Reported as Spam): Outblaze|
214
|-4|(Unknown Status)|
215
|-42|INACTIVE (Recipient Initiated Spam Report): MSN/Hotmail/WebTV|
216
|-44|INACTIVE (Recipient Initiated Spam Report): UNTI (Juno/NetZero/FreeServers)|
217
|-45|INACTIVE (Recipient Initiated Spam Report): Yahoo|
218
|-46|INACTIVE (Recipient Initiated Spam Report): Comcast.net|
219
|-47|INACTIVE (Recipient Initiated Spam Report): AOL|
220
|-48|INACTIVE (Recipient Initiated Spam Report): RoadRunner|
221
|-50|ISP Specific Bounces: AIM.com Unactivated account|
222
|-51|INACTIVE (Recipient Initiated Spam Report): Excite|
223
|-52|INACTIVE (Reported as Spam): Earthlink|
224
|-53|(Unknown Status)|
225
|-54|(Unknown Status)|
226
|-60|Bad Address/Expired address, etc (n.b.: These addresses are all @democracyinaction.org)|
227
|-9|INACTIVE: DEATH (supporter has died)|
228
|0|0 - Inactive or Unknown(not subscribed)|
229
|1|1 - Imported or unknown (subscribed)|
230
|10|ACTIVE: CONFIRMED (Double opt-in)|
231 1 Jon Goldberg
|3|ACTIVE: CLIENT (Client has directly signed up)|
Go to top