Project

General

Profile

Exporting Salsa for CiviCRM » History » Version 25

Jon Goldberg, 12/26/2014 06:15 PM

1 24 Jon Goldberg
{{lastupdated_at}} by {{lastupdated_by}}
2
3 1 Jon Goldberg
h1. Exporting Salsa for CiviCRM
4
5 2 Jon Goldberg
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.
6 1 Jon Goldberg
7 14 Jon Goldberg
h2. Exporting Data not on the Contact Entity
8 2 Jon Goldberg
9 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
10 1 Jon Goldberg
11 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.
12
13 11 Jon Goldberg
h3. Groups
14 12 Jon Goldberg
15 3 Jon Goldberg
* Create a new report.
16
* Select the type of report you would like to create: Standard report
17
* Use "Advanced Object Chooser".
18 9 Jon Goldberg
* Report "Supporter Groups", then "Groups".  Press "Save".
19 1 Jon Goldberg
* Select the "Columns" tab and select your columns.  I like just "Supporter Key" and "Group Name".
20 7 Jon Goldberg
* In Conditions, "Group Name" should be "Not Empty".
21 4 Jon Goldberg
* Save, Run the report.
22
* Click the "Export" link.
23 1 Jon Goldberg
24 11 Jon Goldberg
h3. Tags
25 12 Jon Goldberg
26 5 Jon Goldberg
* Create a new report.
27
* Select the type of report you would like to create: Standard report
28
* Use "Advanced Object Chooser".
29
* Report on "Tags", then "Tags Data", then "Database Table".  Press "Save".
30
* Select the "Columns" tab and select your columns.  I like just "Tag" and "Table Key".
31 1 Jon Goldberg
** "Table Key" is the Supporter Key.  It's similar to "entity_id" in CiviCRM.
32 7 Jon Goldberg
* In "Conditions", "Table Name" should equal "supporter".
33 10 Jon Goldberg
* Add another condition: "Tag.Tag" is "Not Empty".
34 1 Jon Goldberg
* Save, Run the report.
35
* Click the "Export" link.
36
37 11 Jon Goldberg
h3. Events
38 1 Jon Goldberg
39 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:
40
Event KEY
41
Reference Name
42
Event Name
43
Description
44
Start
45
End
46
Deadline
47
Maximum Attendees
48
49 1 Jon Goldberg
h3. Participants
50 16 Jon Goldberg
51
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"!
52
53
Note that if you're matching on a unique field from "Events", you can just export "Supporter Event" and not the "Event" table.
54
Here are the fields I exported and what I matched them to:
55
Supporter KEY	external_identifier
56
Event KEY	event_key
57
Status	status
58
Type	role
59
Date Created	register_date
60
61 19 Jon Goldberg
h3. Actions
62 17 Jon Goldberg
63 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.
64 1 Jon Goldberg
65 19 Jon Goldberg
Export as above. Report on table "Action".
66
Condition: "Reference Name" is not empty.
67
68
*Note*: "Description" is likely to contain HTML that breaks the export.  I find it helpful to export it as the last column.
69
70
h3. Supporter_Actions
71 11 Jon Goldberg
72 22 Jon Goldberg
Export "action" and "supporter_action" and "supporter_action_comment" in that order.
73 23 Jon Goldberg
Condition: supporter_key IS NOT NULL
74 20 Jon Goldberg
75
Here's the fields I grabbed with their mapping:
76 22 Jon Goldberg
supporter_action.supporter_key	
77 21 Jon Goldberg
action.Reference Name
78 22 Jon Goldberg
supporter_action.Date_Created
79
supporter_action_comment.Comment	details
80 20 Jon Goldberg
81 11 Jon Goldberg
h3. Chapters
82 12 Jon Goldberg
83 7 Jon Goldberg
* Create a new report.
84 1 Jon Goldberg
* Select the type of report you would like to create: Standard report
85 7 Jon Goldberg
* Use "Advanced Object Chooser".
86 8 Jon Goldberg
* Report "Supporter Chapter", then "Chapter".  Press "Save".
87
* Select the "Columns" tab and select your columns.  I like just "Supporter KEY" and "Chapter.Name".
88
* In Conditions, "Chapter.Name" should be "Not Empty".
89 5 Jon Goldberg
* Save, Run the report.
90 1 Jon Goldberg
* Click the "Export" link.
91
92 7 Jon Goldberg
h3. Receive_Email field
93 1 Jon Goldberg
94
Here's a TSV of how to interpret the "Receive_Email" field.
95
code	# of records	meaning
96
-24	11066	INACTIVE: (5.1.1 User Unknown) 
97
-26	1265	INACTIVE: Address contains RFC spec. invalid characters / is improperly formatted 
98
-3	10596	INACTIVE: UNSUBSCRIBED (actively unsubscribed by user) 
99
-30	8	INACTIVE (Reported as Spam): Other Blacklist (BLACKLIST) 
100
-32	11	(Unknown Status) 
101
-35	1	INACTIVE (Reported as Spam): Outblaze 
102
-4	1	(Unknown Status) 
103
-42	349	INACTIVE (Recipient Initiated Spam Report): MSN/Hotmail/WebTV 
104
-44	10	INACTIVE (Recipient Initiated Spam Report): UNTI (Juno/NetZero/FreeServers) 
105
-45	818	INACTIVE (Recipient Initiated Spam Report): Yahoo 
106
-46	69	INACTIVE (Recipient Initiated Spam Report): Comcast.net 
107
-47	545	INACTIVE (Recipient Initiated Spam Report): AOL
108
-48	1	INACTIVE (Recipient Initiated Spam Report): RoadRunner 
109
-50	10	ISP Specific Bounces: AIM.com Unactivated account 
110
-51	3	INACTIVE (Recipient Initiated Spam Report): Excite 
111
-52	18	INACTIVE (Reported as Spam): Earthlink 
112
-53	15	(Unknown Status) 
113
-54	1	(Unknown Status) 
114
-60	4	Bad Address/Expired address, etc (n.b.: These addresses are all @democracyinaction.org)
115
-9	5	INACTIVE: DEATH (supporter has died) 
116
0	1101	0 - Inactive or Unknown(not subscribed)
117
1	64352	1 - Imported or unknown (subscribed) 
118
10	3	ACTIVE: CONFIRMED (Double opt-in) 
119 4 Jon Goldberg
3	8924	ACTIVE: CLIENT (Client has directly signed up)
Go to top