Introduction
A great use of the query tool is monitoring the donations within a time frame. This will provide a deeper look into who is donating and how much they are donating. This can also be used to send out communications to recent donors inviting them to events, thanking them for their contribution, or sending them updates on the campaign they donated to.
Instructions
To filter by transaction date, add Transaction Date to the Filter section. It is important to use the Date field and not the Date Created field as manual created or imported transactions could have a created date different that the actual transaction date.
To set the filter to pull transactions within a relative data range, click on the formula button and choose “Last X Days from the drop-down.
You will now be able to enter the number of days you want the database to pull from, for example 2 years would be 730 days.
Note: The drop-down does have relative date options to select out of the box, such as yesterday, last month, or year to date, however, if the date range is not listed, the Last X Days formula will allow you insert the range you are looking for, like 730 days (2 years).
After you have set your filter, you will need to add fields that will display in your query to the Select section, such as Contact Id. If you want to query based on the sum of the transactions within a certain time frame, you can group the transactions by clicking the dropdown carrot next to Contact Id and selecting Group By.
Note: you can group by contact name, however grouping by ID is more efficient
Next, to display the sum of the transactions add payment amount to the select field and set the formula to Sum
Additionally, if you want to filter by a certain amount click the filter Icon next to the formula icon. This will automatically add the filter to the filter section.
You will now be able to set the filter to only search the database for donations of a certain amount. For example, if you only want to see those who have donated $100.00 or more, you would select Greater Than or Equal to in the drop-down and enter $100.00 in the value
If you would like to see how many donations a contact has made, add the transaction ID and set the formula to Count
Now you will be able to click the Run button and the query will pull all contacts who have donated $100.00 or more in the last 730 days. Each contact ID will only be listed once and you will be able to see how many donations they have made.
