Built-in Queries

History of Constituents added to Groups

• Number of constituents added to a group at one time

• The user who acted

Groups are identified by ID number. 

History of Constituents removed from Groups

• Number of constituents removed from a group at one time

• The user who acted

Groups are identified by ID number. 

History of Deleted Constituents

• Constituents who were manually deleted

• The user who acted.

History of Deleted Credit Cards

• Credit cards that were manually deleted

• The user who acted

History of Deleted Donations

• Donations that were manually deleted

• The user who acted

History of manually added constituents

• Constituents who were manually added

• The user who acted

History of Merged Constituents

• Constituents who were manually merged

• The user who acted

History of Updated Constituents

• Constituents who were manually edited

• The user who acted

A Household total giving report

• This query reports (for all time, by default) the

• Constituents who are considered a Head of Household

• Total giving for all in the household

Everyone is considered a Head of Household if the system is not widely used, Consider including filters for Household Member Count greater than 1 and Sum Amount by Head of Household ID greater than 0 to identify true households.

Criteria for this query are set by default and cannot be altered.

All Membership Data

All Membership subscriptions for all time, including all constituents who are members of the same subscription

Consider sorting by Member Number and Subscription ID to segment members in the same subscription.

All Non Deceased Constituents for Mailing with Spouses

• All Constituents whose Deceased Date is blank;

• their primary address (if recorded); and

• any spouse name and their Deceased Date.

All Relationships

All Constituents who have at least one other Constituent linked to them with a Relationship, specifically those who have a Relation Constituent Number that is not blank

Constituents in Groups

All Constituents who are in at least one Group.

Output fields include all groups in one field.

Constituents who passed away in current year

• All Constituents who have a Deceased Date that is not blank; and

• the date provided is from the current year

Current Year Giving Report

• A summary of donation data for constituents who donated during the current year; and

• have a blank Deceased Date.

Deceased Constituents

Constituents with a Deceased Date that is not blank

First time donors in the past year (must filter on date)

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)

Constituents who have their first transaction based on First Gift Date Ever on or after the filtered date (not filtered by 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

A wide variety of data on Individual constituents, including:

• Primary address

• Email

• Phone numbers

This query excludes anyone who does not have all three contact points.

Lapsed donor report (1 Year)

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)

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

Primary donors who are not deceased, including their:

• Name

• Spouse name

• Primary address

Reports on gifts made within the date range specified in the filter section. Gifts are unfiltered as to just donations.

Mail Merge for Head of Household

• 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

• 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

All constituents who have an entry in the Merge History table where Old UUID is not blank.

When you merge constituents together, Salsa CRM tracks which records merged into the new record.

Old and new IDs are recorded in a Merge History table.

NCOA Bad Addresses All

All constituents and their primary addresses, where the primary address is NCOA Verified as Bad.

NCOA Bad Addresses Donors

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

All constituents and their primary addresses, where the primary address is NCOA Verified as Good.

NCOA Previous Addresses

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

All NCOA Verified records (G=Good, B=Bad, U=Unknown) for primary addresses.

Organizational Constituents and contact info

A wide variety of data on Organization constituents including their:

• Primary address

• Email

• Phone numbers

This query excludes anyone who does not have all three contact points.

Previous Calendar Year Giving Report

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

All constituents who 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, not alphabetically.

The query can be edited and saved to Saved Queries.

Active but lapsed recurring donations

Recurring credit card payments that are in arrears.

After running this query, review the Installments report for more information on the credit card status. The donor's credit card may have been compromised.

Active Recurring Donations

Unpaid, future, and recurring credit card payments.

All Cash Donations

• Primary Donor information

• Information for all donations entered as a Cash Payment Type

All Check Donations

• Primary Donor information

• Donation information for all donations entered as a Check Payment Type

All Co-donors

Anyone listed on a donation as a Co-Donor.

All Credit Card Donations with Auth Code

• Primary donor information

• 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

Comprehensive donation information with no filtering.

Use to export a file to sync with accounting or another system.

All Gift In-kind Donations

• Primary donor information

• Donation information for all donations entered as a Gift in Kind Payment Type

All Stock Donations

• Primary donor information

• Donation information for all donations entered as a (corporate) Stock Payment Type

CardConnect Reconciliation Query

Reconcile CardConnect funding batches with gift records in Salsa CRM.

Use the timestamp identify whether a gift arrived after the cut-off time for next-day funding.

Use the authorization code to link to 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.

Choose to additionally filter by Fund/Campaign/Appeal to find those donors who have not donated to a particular cause in a while. They need to have donated to that Fund/Campaign/Appeal at least once before.

Deactivated Recurring Donations

Deactivated, paid, recurring credit card payments. This sequence of payments could have been canceled in Engage or Deactivated in CRM.

Donation Totals by Appeal

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

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

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

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 constituents with a blank Deceased Date.

Donation Totals by Soft Credit

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

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

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

All donations that originated in Salsa CRM, using the filter Originated from Engage? equals "No".

This query could yield thousands of donations. Consider including a filter on Received Date or Constituent Group to reduce the number records returned.

Donations from Salsa Engage

All donations that originated in Salsa CRM by using the filter Originated from Engage? equals "Yes".

This query could yield thousands of donations. Consider including a filter on Received Date or Constituent Group to reduce the number of rows.

Donations with letters that have not been sent

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

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

All donations where the count of Funds associated with a donation is greater than 1.

Donations without Receipts

Transaction records with 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

A combination of Donation Source Activity Type and Donation Transaction Reason. This identifies Event or P2P donations synced to Salsa CRM.

Engage Event/Peer to Peer-Tickets

A combination of Donation Source Activity Type and Donation Transaction Reason. This identifies Event or P2P ticket purchases synced to Salsa CRM.

In Honor Donations

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

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, one 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

Matching gifts that have not been fulfilled.

Outstanding Pledge Balances per Constituent

This query reviews 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)

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

Pledges that have zero paid installments associated with them.

Pledges with split funds

All pledges where the count of Funds associated with a donation is greater than 1.

Pledges with written off installments

All pledges that have installments that have been marked as 'Write Off'.

Recurring Donation Totals

Donor names and total payments toward recurring donations.

This query will show only one row per donor.

Recurring Donations

All donors and all recurring donation payments, as well as whether there is a co-donor.

Individual payments are tracked on each row, therefore individual donors will be identified on multiple rows.

Soft Credit Donations

Donations where there is at least one soft credit, with separate rows for every soft credit.

Total Donations by Solicitor

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

All donations with the same Fund/Campaign/Appeal/Batch.

This helps you 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.