Query and Export
Running a query is a quick and flexible way to filter your list of supporters by any of the criteria available in Salsa. After the query is complete, Salsa provides a variety of actions you can take with the resulting supporters.
Running a Query
The query interface exists in a few places in the DIA system, but to do a query against your supporter list:
1. Click the "Supporter Management" tab
2. Click "Query/Export" in the sub-navigation bar
3. Use the Query Builder to construct your query
4. Hit the "Run Query" button
Clicking on the 'Condition Type' menu will provide you with a list of the categories available for to search. These should all be self-evident, and Salsa will populate the Condition field to the right with the fields relevant to the category.
If you would like to create more complex queries, simply click on the plus sign under 'Remove/Add.' Salsa primarily uses a 'logical and' system of querying, where the results must pass through the condition of each filter. To remove a condition, click the minus sign.
However, you can also set an 'Or' condition by clicking the 'add an OR condition' link under the colored box. If you would like to remove this condition, click the 'X' at the top right of the box.
Tip: When querying on Supporter Fields as a Condition Type, you can use the 'Compare to another field' dropdown menu in order to see which records in your list have the same data in two different fields. This can be handy for catching mistakes and cleaning up your list. Select your first condition, such as 'First Name', and then 'Equals.' Leave the actual Value blank, and select the field from the dropdown menu which you want to compare to the Condition (such as 'Last Name'), and click run query. This will return records where the first and last names are the same. If the two fields you picked are both empty, these records will be returned as well. If you want to eliminate these, click the plus sign to add an 'And' filter, select the first condition, then 'Does not equal' and leave the value blank.
Tip: If you'd like to query by multiple types of Unsubscribe codes, use the Supporter Fields condition type instead of the Unsubscribe condition type. Then select 'Receive Email' and multi-select the appropriate categories by holding down the Apple or Control key and clicking on the values. This is an 'Or' query, so it will return supporters in all of the ones you selected. A list of the numerical receive email values that we use in our database and their corresponding reasons can be found here.
Saving a Query
There may be some queries you use again and again. For instance, you may have three different media contacts groups, Television, Print, and Radio. You haven't put them all into a Media group, but you regularly send email blasts out to all your media contacts at once. Instead of having to build a query each time you want to target these three groups, you can build the query once, and save it.
When you go to the query builder screen, you'll see at the top of the list of query options a box labeled "Query Name." If you put a name in this box before you run your query, you will then have a saved copy of that query (the terms you put together to form the query, not the query result set itself) to use in later work. The process of creating a saved query is:
1. Name your query
2. Construct your query terms
3. Hit "Run Query"
Results
Once you have run a query, the total number of supporters who match the conditions appears at the top of the next page. If you scroll down, you can spot check the supporters who were returned to make sure they fit the parameters you described.
To change the conditions of a query, click on the 'edit/modify this query' link towards the top of the page. If you click the back button on your browser, it's possible that when you return to the query page your conditions will not have been saved.
Actions
On the results page, Salsa provides you with a number of options of what to do with the supporters who were returned.
Add to a group
You can add the supporters in your query set to a pre-existing group (you must create the group before you run the query).
Delete from a group
Allows you to remove the supporters in your query from a group that they are in. This does not remove them from your supporter list.
Tag supporters
This allows you to add a tag to all of the resulting supporters.
Remove tags
Allows you to bulk remove a tag from a supporter record.
Delete supporters
This deletes your supporters from your list entirely. This is useful with failed uploads, but please double-check before using this option, since it will completely remove supporters from your list. Restoring from backup involves billable hours and will almost certainly result in some lost information. If you have any doubts about what you're doing, export a local copy before deleting.
Update field
This allows you, for your entire query results, to change the value of a field. You can choose one of two options:
1. Updating all instances of a field in the found set to a single value (all First Name fields for the group brought back by the query will be set to "Tom")
2. Updating all instances of one field in the found set to the value of another (all First Name fields fields for the group brought back by the query will be set to the value held in the Last Name fields for each record).
Thus, if you have did an upload, and accidentally mismatched a field, you can simply move the data from one field to the other, and then erase the data in the first field by running the query again, and setting all the values to be blank.
Exporting
You can also easily export the results of your query by selecting how you would like them to appear (Tab delimited, Excel, HTML) as well as which fields you are interested in (you can select multiple), and clicking the Export Information button (Note: If your query results in greater than 30 thousand supporter records, you will not have the option to select distinct fields for export. In these instances, a report may better suit your needs. Feel free to contact support if you need any assistance with this.)
If the fields you wish to export do not appear in the list of fields, you may need to run a report. This may be the case when you query for Donation History, since Amount, Transaction Date, etc. do not appear in this list through queries. Exports can be useful for a number of reasons, but can be particularly good for data backups.
Exports and Reports both allow for Scheduled Export options, if there are particular exports you want to run regularly and automatically. After clicking for Advanced Export Options through the second step of your query, you can click on the small blue "Export Options" link under the "Export to a file..." heading.
Change the settings of this page to:
- - Run and export this report > On a schedule
- Start at this time (will pop up once schedule option is selected): Enter your date like 2008-04-30 and the time should be in military time
- File header: (Normal should be sufficient, though you can change this)
- Run the report: Select your frequency
- Send an email: If you want the report to send to multiple emails, you can comma separate the addresses entered
Lastly, If you click the 'Export all supporters' button on the Query/Export front page, Salsa will essentially run a query for all of your supporters and take you to the Results page where you can take any of the above actions with them.
Tips
Wildcards
When you specify Supporter Information, you can use wildcards. DIA uses the MySQL query syntax for queries, and you can use an underscore('_') to match any one character, and a percent sign ('%') to match all characters. But be sure to use the 'like' operator, otherwise we will try to match those characters! Wildcards can be used for almost all fields. For example, to match Jim or Jane, you could select: First Name like J%. If you wanted to match anyone with a street address, you would choose: Street like _%. This is especially handy for finding supporters with specific email addresses, for instance, %@yahoo.com.
Groups
In some categories, it is possible to select multiple conditions at the same time. For instance, within the Groups category, you can highlight multiple groups and the query will return supporters who are in either group (is a member of Group X or Group Y). You can also do this same query by selecting each group one at a time and utilizing an Or condition. If you wanted to return supporters who are members of both groups, you will need to do this one at a time using the regular 'And' query (plus sign).
Exclude vs. Include Group Overlap
If you're running a query on Groups, attempting to include Group A and exclude Group B, the exclude will override, so if a supporter is in both groups, they will not show up in the query results.
Using Queries to Delete a Bad Upload
While you could spend your time deleting unwanted records one by one, it's much easier, and faster, to do them in bulk. The best way to do that is to create a query which matches the records you'd like to delete, and then use the query results screen to delete them. Here's an example of how to do that:
To delete a bad upload from the query page:
1. From the drop-down list of categories, click "Supporter Data Fields"
2. From the middle drop-down choose "Source Details". Set this to equal the name of the file you uploaded.
3. Click the "Save Query and Take Action" button
4. On the query results screen, click the "Delete" button next to "Delete Supporters"
Finding Supporters With Missing Information
Occasionally you'll end up with supporter records without some portion of information that you need. But how to find all those records, so you can call those people, or email them, to try to wrest that sweet, sweet information from them? Here's how:
1. From the drop-down list of categories, click "Supporter Data Fields"
2. Choose the field from the supporter table you want to search on, like email address (Email)
3. Choose a condition, like "Is Empty" from the second drop down box
4. Click the "Add Condition" button, to add the condition to the list
5. Click the "Save query and take action" button
This should return to you the people with the conditions you chose, like "Email Address is empty," or "Street is empty and Email Address is empty." If you notice that some of the records returned still appear to have empty field values, you'll want to add an "And" condition for, for example, Email "not equal" (and then leave the Value field blank).
Postal Region
'Within Postal Region' is a handy category in the first drop-down menu, especially for targeted emails and events. You can select from a number of different distances a supporter lives from a specified zip code.
A/B Testing
The "Percentage" option under Advanced Features allows you to query percentiles of your list.
You can think of each percentile as a permanent, invisible and uneditable field on a supporter record: if a particular supporter is in percentile 29/100 today, s/he will always be in percentile 29/100. However, supporters are randomly allocated to percentiles at the time their records are created, so each percentile will represent a cross-section of your list.
For lists of sufficient size, you can use percentile features to test list performance and messaging strategies, especially when using the query interface to target an e-mail blast. (An e-mail panel should be at least 5,000 - and probably 10,000 - to have statistical viability.) Split lists into halves, or thirds, or just test to a small slice, before releasing a major message, by selecting mutually exclusive sets of percentiles.
