Salsa subject matter experts have created a list of built-in queries in the Advanced Query Wizard. If you're in a hurry, these pre-generated queries are for you. Find these queries in the Reports > Queries > Advanced Query Wizard in the Built-in Queries tab.
Use the Search all columns field to find the query you need based on the Descriptions and Definitions in the table below.
This list of built-in queries below is sorted by Description. The second column—Definition—represents a plain-language explanation of what the query is for and when to use it.
Audit Trail Queries
| Description | Definition |
|---|---|
| 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 acted. Groups are identified by ID number. |
| 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 acted. Groups are identified by ID number. |
| History of Deleted Constituents | This query reports (for all time, by default) the constituents who were manually deleted, and the user who acted. |
| History of Deleted Credit Cards | This query reports (for all time, by default) the credit cards that were manually deleted, and the user who acted. |
| History of Deleted Donations | This query reports (for all time, by default) the donations that were manually deleted, and the user who acted. |
| History of manually added constituents | This query reports (for all time, by default) the constituents who were manually added, and the user who acted. |
| History of Merged Constituents | This query reports (for all time, by default) the constituents who were manually merged, and the user who acted. |
| History of Updated Constituents | This query reports (for all time, by default) the constituents who were manually edited, and the user who acted. |
Constituent Queries
| Description | Definition |
|---|---|
| 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. |
| 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 to group by the members in the same subscription. |
| 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. |
| 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. |
| Constituents in Groups | This query reports all Constituents who are in at least one Group. Output fields include all groups in one field. |
| Constituents who passed away in current year | This query reports all Constituents who have a Deceased Date that is not blank and the constituent passed during the current year. |
| Current Year Giving Report | This query reports a summary of donation data for constituents who donated during the current year and have a blank Deceased Date. |
| Deceased Constituents | This query reports which constituents have a Deceased Date that is not blank. |
| First time donors in the past year (must filter on date) | This query reports on 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. |
| First Time Donors (filter on Date) | This query reports on 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). The report contains only name and address and restricts results to one row per donor based on the address being either blank or their primary value. |
| 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. |
| Lapsed donor report (1 Year) | This query reports on constituents who have a Last Gift Date in the previous year but not during the current year. The report contains the constituent name and address and restricts results to one row per donor based on the address being either blank or their primary value. |
| Lapsed donor report (Any previous year) | This query reports on constituents who have a Last Gift Date in the past but not during the current year. The report contains the constituent name and address and restricts results to one row per donor based on the address being either blank or their primary value. |
| 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. |
| 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. |
| 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 into one row. |
| Merged Constituent Details | When you merge constituents together, Salsa CRM tracks which records merged into the new record. The old and new IDs are recorded in a Merge History table. This query reports all constituents who have an entry in the Merge History table where Old UUID is not blank. |
| NCOA Bad Addresses All | This query includes all constituents and their primary addresses, where the primary address is NCOA Verified as Bad. |
| 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. |
| NCOA Good Addresses | This query includes all constituents and their primary addresses, where the primary address is NCOA Verified as Good. |
| 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. |
| NCOA Results Summary | This query includes a simple count of all NCOA Verified records (G=Good, B=Bad, U=Unknown) for primary addresses. |
| 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. |
| Previous Calendar Year Giving Report | This query reports a summary of donation data for constituents who donated during the previous year and have a blank Deceased Date. |
| 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 Queries
| Description | Definition |
|---|---|
| Active but lapsed recurring donations | This report identifies recurring credit card payments that are in arrears. There may be problems with the credit card on file; for example, the donor's credit card may have been compromised. After running this query, review the Installments report for more information on the credit card status. |
| Active Recurring Donations | This report identifies unpaid, future, recurring credit card payments. |
| All Cash Donations | This query reports basic primary donor and donation information for all donations entered as a Cash Payment Type. |
| All Check Donations | This query reports basic primary donor and donation information for all donations entered as a Check Payment Type. |
| All Co-donors | This query reports anyone who was listed on any donation as a Co-Donor. |
| 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. |
| 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. |
| 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. |
| All Stock Donations | This query reports basic primary donor and donation information for all donations entered as a (corporate) Stock Payment Type. |
| 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. |
| 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; donors had to have donated to that Fund/Campaign/Appeal at least once before. |
| Deactivated Recurring Donations | This report identifies deactivated, paid, recurring credit card payments. This sequence of payments could have been canceled in Engage or Deactivated in CRM. |
| 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 the Received Date. |
| 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 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 the Received Date. |
| 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 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 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 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. |
| Donations created in Salsa CRM | This query reports all donations that originated in Salsa CRM by using the filter Originated from Engage? equals "No". Since this query could possibly return tens of thousands of donations, it may be prudent to include a filter on Received Date or Constituent Group to reduce the number of rows. |
| Donations from Salsa Engage | This query reports all donations that originated in Salsa CRM by using the filter Originated from Engage? equals "Yes". Since this query could possibly return tens of thousands of donations, it may be prudent to include a filter on Received Date or Constituent Group to reduce the number of rows. |
| Donations with letters that have not been sent | This query reports transaction records that have a blank Letter Sent Date. To guarantee that all results are donations and have a letter attached, include filters on Donation Type = 'Donation' and Thank You Letter 'is not blank'. |
| Donations with receipts that have not been sent | This query reports transaction records that have Receipt Numbers but blank Receipt Sent Dates. To guarantee that all results have a Receipt attached, add a filter on Receipt Letter 'is not blank'. |
| Donations with split funds | This query identifies all donations where the count of Funds associated with a donation is greater than 1. |
| Donations without Receipts | This query identifies which transaction records have a blank Receipt Number. To guarantee that all results have a Receipt attached, add a filter on Receipt Letter 'is not blank'. |
| Engage Event/Peer to Peer-Donations | This query uses a combination of Donation Source Activity Type and Donation Transaction Reason to identify Event or P2P donations synced to Salsa CRM. |
| Engage Event/Peer to Peer-Tickets | This query uses a combination of Donation Source Activity Type and Donation Transaction Reason to identify Event or P2P ticket purchases synced to Salsa CRM. |
| 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. |
| 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. |
| Mail-Merge for Soft Credit Donors |
This query was designed for donations with many people listed as soft credits on a single gift, such as a matching gift program. This query result could potentially display the same donation multiple times—once for each soft credit. Modify the filters on this query to ensure only the gift you want to acknowledge is present on the query output. For example, filter on one Donation ID. |
| Matched donations that have not been fully paid | This query identifies matching gifts that have not been fulfilled. |
| 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. |
| 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. |
| 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'. |
| Pledges that have never been paid |
This query reports pledges that have zero paid installments associated with them. |
| Pledges with split funds | This query identifies all pledges where the count of Funds associated with a donation is greater than 1. |
| Pledges with written off installments | This query identifies all pledges that have installments that have been marked as 'Write Off'. |
| Recurring Donation Totals | This query reports donor names and total payments toward recurring donations. This query will show only one row per donor. |
| 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. |
| Soft Credit Donations | This query identifies donations where there is at least one soft credit, with separate rows for every soft credit. |
| 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. |
| Update Donations Query | 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. |