Salsa subject matter experts have created a list of built-in queries in the Advanced Query Wizard to provide a ready set of common algorithms you may need in a hurry but don't have time to create. Find these queries in the Reports > Queries > Advanced Query Wizard in the Built-in Queries tab.
This list of built-in queries below are sorted by Type of query and then by Description. The third column—Definition—represents a plain-language explanation of what the query is for and when to use it.
Use the Search all columns field to find the query you need based on the Descriptions and Definitions in the table below.
Type | Description | Definition |
---|---|---|
Audit Trail | History of Constituents added to Groups | This query reports (for all time, by default) the number of constituents added to a group at one time, and the user who performed the action. Groups are identified by ID number. |
Audit Trail | History of Constituents removed from Groups | This query reports (for all time, by default) the number of constituents removed from a group at one time, and the user who performed the action. Groups are identified by ID number. |
Audit Trail | History of Deleted Constituents | This query reports (for all time, by default) the constituents who were manually deleted, and the user who performed the action. |
Audit Trail | History of Deleted Credit Cards | This query reports (for all time, by default) the credit cards that were manually deleted, and the user who performed the action. |
Audit Trail | History of Deleted Donations | This query reports (for all time, by default) the donations that were manually deleted, and the user who performed the action. |
Audit Trail | History of manually added constituents | This query reports (for all time, by default) the constituents who were manually added, and the user who performed the action. |
Audit Trail | History of Merged Constituents | This query reports (for all time, by default) the constituents who were manually merged together, and the user who performed the action. |
Audit Trail | History of Updated Constituents | This query reports (for all time, by default) the constituents who were manually edited, and the user who performed the action. |
Constituent | A Household total giving report | This query reports (for all time, by default) the constituents who are considered a Head of Household and total giving for all. in the household. Since everyone is considered their own Head of Household if the system is not widely used, it would be prudent to include filters on Household Member Count greater than 1 and Sum Amount by Head of Household ID greater than 0 to identify true households. |
Constituent | All Membership Data | This query reports all Membership subscriptions for all time, including all constituents who are members of the same subscription. It would be prudent to include sorting on Member Number and Subscription ID in order to group together by the members in the same subscription. |
Constituent | All Non Deceased Constituents for Mailing with Spouses | This query reports all Constituents whose Deceased Date is blank, along with their primary address (if recorded) and any spouse name and their Deceased Date. |
Constituent | All Relationships | This query reports all Constituents who have at least one other Constituent linked to them with a Relationship. Specifically the query looks for Constituents who have a Relation Constituent Number that is not blank. |
Constituent | Constituents with Email (Sync with Engage) | This query reports which Constituents have a primary address and a primary email address that is not opted-out from receiving marketing communications, and could be synced to Engage. |
Constituent | Constituents with Emails (Sync with Engage) | This query reports which Constituents have a primary email address that is not opted-out from receiving marketing communications, and could be synced to Engage. Address verification has been eliminated from this query, unlike Constituents with Email (Sync with Engage). |
Constituent | Deceased Constituents | This query reports which constituents have a Deceased Date that is not blank. |
Constituent | First time donors in the past year (must filter on date) | This query reports constituents who have their first donation based on First Gift Date Ever on or after the filtered date. This query also attempts to Merge Spouses and restrict results to one row per donor based on address, email, and phone number being either blank or their primary value. |
Constituent | First Time Donors (filter on Date) | This query reports constituents who have their first transaction based on First Gift Date Ever on or after the filtered date (not filtered on donations only—pledges may be calculated). Report contains only name and address and restricts results to one row per donor based on address being either blank or their primary value. |
Constituent | Individual Constituents and contact info | This query reports a wide variety of data on Individual constituents, as well as their primary address, email, and phone numbers. This query excludes anyone who does not have all three contact points. |
Constituent | Mail Merge for donations | This query reports primary donors who are not deceased, including name, spouse name, and primary address. Donors made gifts within the date range specified in the filter section. Gifts are unfiltered as to just donations. |
Constituent | Mail Merge for Head of Household | This query reports heads of households who are not deceased, who want to be contacted (Preferred Contact Method does not equal "Do Not Contact") and who have a primary address. |
Constituent | Mail Merge for Spouses | This query reports all constituents who are not deceased, who want to be contacted (Preferred Contact Method does not equal "Do Not Contact") and who have a primary address. This query also merges any spouses together into one row. |
Constituent | NCOA Bad Addresses All | This query includes all constituents and their primary addresses, where the primary address is NCOA Verified as Bad. |
Constituent | NCOA Bad Addresses Donors | This query includes all constituents and their primary addresses, where the primary address is NCOA Verified as Bad and their Total Gift Amounts is greater than 0. |
Constituent | NCOA Good Addresses | This query includes all constituents and their primary addresses, where the primary address is NCOA Verified as Good. |
Constituent | NCOA Previous Addresses | This query includes all constituents and their non-primary address, where the address is NCOA Verified as Bad. These constituents had an address record replaced with a Good move address. |
Constituent | NCOA Results Summary | This query includes a simple count of all NCOA Verified records (G=Good, B=Bad, U=Unknown) for primary addresses. |
Constituent | Organizational Constituents and contact info | This query reports a wide variety of data on Organization constituents, as well as their primary address, email, and phone numbers. This query excludes anyone who does not have all three contact points. |
Constituent | Show all notes for all constituents | This query reports all constituents that have at least one Note entered on the Constituent record. Notes are sorted chronologically with the oldest shown first. Constituents are sorted by Constituent Number and not alphabetically but the query can be edited and saved to Saved Queries. |
Donation | All Cash Donations | This query reports basic primary donor and donation information for all donations entered as a Cash Payment Type. |
Donation | All Check Donations | This query reports basic primary donor and donation information for all donations entered as a Check Payment Type. |
Donation | All Co-donors | This query reports anyone who was listed on any donation as a Co-Donor. |
Donation | All Credit Card Donations with Auth Code | This query reports basic primary donor and donation information for all donations entered as a Credit Card Payment Type. Further fields can be added to this query to clarify from the Fund Items table, such as Credit Card Type. |
Donation | All Donations Made | This query reports comprehensive donation information with no filtering. This is useful if you need to export a file to sync with accounting or another system. |
Donation | All Gift in Kind Donations | This query reports basic primary donor and donation information for all donations entered as a Gift in Kind Payment Type. |
Donation | All Stock Donations | This query reports basic primary donor and donation information for all donations entered as a (corporate) Stock Payment Type. |
Donation | CardConnect Reconciliation Query | This query will help you to reconcile CardConnect funding batches with the gift records in Salsa CRM. The timestamp will help you if the donation came after the cut-off time for next-day funding. The authorization code will link up with the donations in CardPointe. |
Donation | Constituent's Last Donation Date (Filter on Fund/Campaign/Appeal) | This query reports donors by their Maximum Received Date by Constituent Number using an aggregate value. Use your own added Fund/Campaign/Appeal filter to find donors who haven't donated to a particular cause in a while; donor had to have donated to that Fund/Campaign/Appeal at least once before. |
Donation | Donation Totals by Appeal | This query reports a sum of all donations for each Appeal. Pledges or installment payments are excluded from this report. Any additional filters will restrict the results to just those that honor the filters, such as Received Date. |
Donation | Donation Totals by Campaign | This query reports a sum of all donations for each Campaign. Pledges or installment payments are excluded from this report. Any additional filters will restrict the results to just those that honor the filters, such as Groups. |
Donation | Donation Totals by Fund | This query reports a sum of all donations for each Fund. Pledges or installment payments are excluded from this report. Any additional filters will restrict the results to just those that honor the filters, such as Received Date. |
Donation | Donation Totals by Group | This query reports a sum of all donations for each appeal. Pledges or installment payments are excluded from this report. Any additional filters will restrict the results to just those that honor the filters, such as constituents with a blank Deceased Date. |
Donation | Donation Totals by Soft Credit | This query reports a sum of all donations for each Soft Credit constituent. Pledges or installment payments are excluded from this report. Any additional filters will restrict the results to just those that honor the filters, such as constituents with no Deceased Date. |
Donation | Donation Totals per In-Honor record | This query reports a sum of all donations for each constituent for whom tribute donations were made in their honor. Pledges or installment payments are excluded from this report. Any additional filters will restrict the results to just those that meet the criteria, such as a unique Constituent Number. |
Donation | Donation Totals per In-memory record | This query reports a sum of all donations for each constituent for whom tribute donations were made in their memory. Pledges or installment payments are excluded from this report. Any additional filters will restrict the results to just those that meet the criteria, such as a unique Constituent Number. |
Donation | Donation Update Query (filter on Fund/Campaign/Appeal/Batch) | This query helps to identify all donations with the same Fund/Campaign/Appeal/Batch so that you can perform a mass update on those records, such as moving them from one Fund/Campaign/Appeal/Batch to another in Step 4 of the Advanced Query Wizard. |
Donation | Donations with letters that have not been sent | This query reports transaction records that have a blank Letter Sent Date. In order to guarantee that all results are donations and have a letter attached, include filters on Donation Type = 'Donation' and and Thank You Letter 'is not blank'. |
Donation | Donations with receipts that have not been sent | This query reports transaction records that have Receipt Numbers but blank Receipt Sent Dates. In order to guarantee that all results have a Receipt attached, add a filter on Receipt Letter 'is not blank'. |
Donation | Donations without Receipts | This query identifies which transaction records have a Receipt Number which is blank. In order to guarantee that all results have a Receipt attached, add a filter on Receipt Letter 'is not blank'. |
Donation | In Honor Donations | This query identifies donation, donor, and honoree information based on whether the honoree's Constituent Number is not blank. Only primary addresses will be returned. If either donor or honoree does not have a primary address, the donation record is not returned in the results. |
Donation | In Memory Donations | This query identifies donation, donor, and memorial tribute data based on whether the memorial's Constituent Number is not blank. Only primary addresses will be returned. If either donor or memorial does not have a primary address, the donation record is not returned in the results. |
Donation | Outstanding Pledge Balances per Constituent | This query looks at the Donor, Donation, and Installment tables to identify and sum all unpaid installments. If the Donation Amount is zero, the original transaction is an unpaid recurring credit card transaction. If the Donation Amount is non-zero, then it is a pledge. |
Donation | Pledges Past Due (filter on date) | This query reports donor names, address, and aggregate data on installments which were scheduled on or before the filtered Installment Due Date but remain unpaid as of the date the query is run. |
Donation | Pledges that have been paid off | Salsa CRM considers a pledge where all installments have an associated payment to be 'Paid', regardless of whether the constituent remits the full, promised amount. To show only pledges for this query, and not all installment types, add a filter on Donation Type = 'Pledge'. |
Donation | Pledges that have never been paid |
This query reports pledges that have zero paid installments associated with them. |
Donation | Recurring Donation Totals | This query reports donor names and total payments toward recurring donations. This query will show only one row per donor. |
Donation | Recurring Donations | This query reports all donors and all recurring donation payments, as well as whether there is a co-donor. There will be multiple rows per donor because each payment gets its own row. |
Donation | Soft Credit Donations | This query identifies donations where there is at least one soft credit, with separate rows for every soft credit. |
Donation | Total Donations by Solicitor | This query identifies total gifts associated with a constituent who is listed in the Solicitor field on the donation record, not with a Solicitor Relation value. |