The query tool can be used to find contacts based on their payment history, with filter options available to customise this further.
The vega preset query "All Contributors Donating More Than Defined Amount Between Dates' can also be used to set specific criteria on the type of payments with a predefined result set.
To add payment criteria in your query, navigate to the Payments & Recurring tab
There are two table sets available on this tab, Recurring Payments, and Payments.
The top table refers to recurrences on a contact, these are not the payments themselves but the recurring schedule.
The bottom table refers to all payments, including recurring payments.
Please see the sections below detailing how each of these table sets allows you to add query criteria specific to the types of payments you want to report on.
Recurring Payments
In the recurring payments section, the table includes 4 different payment values described below:
| Payment Value | Explanation |
| Any | This can be used to find contacts with a recurrence with specific Next Due Dates. |
| Ended | This can be used to find contacts with a recurrence with specific End Dates. |
| None | This can be used to find contacts who do not have a recurrence and can specify particular Next Due Dates. |
| Started | This can be used to find contacts with a recurrence with specific Start Dates. |
Each payment value will open a new window to allow you to set your specific criteria, multiple values can be added to create a complex query giving you customised results.
These are explained below:
| Payment Value Criteria | Explanation |
|
These are used to set if the contacts must meet this criteria or not. When using AND, regardless of other criteria used in the query contacts must meet this payment criteria to show in the query results. When using OR, contacts can meet the other query criteria or this payment criteria to show in the results. NONE can be used for the first query criteria as it does not require AND or OR. |
|
| This is used to determine where this query criteria is ordered in the whole query. This works with the above setting to write the query in the correct sequence. | |
|
|
This status relates to the status of the recurrence. If this is left blank then the query will show results for recurrences of any status. |
|
|
This filter gives the option to customise your query results based on the frequency of the recurrence. This is an easy way to find only monthly regular givers or only annual regular givers for example. If this is left blank then the query will show results for recurrences of any frequency. |
|
|
This is where you can set the recurring payment amount you would like to query on. If you're looking for any payment amount then the default values can be left. The payment amount from and to must both be given a value. |
|
This will show as Next Due, Recurring End, or Recurring Start depending on the payment value being used. This can be used to set specific dates to filter your results. |
|
|
As an alternative to using the payment date values above, a date range can be used instead. This allows you to create a query with a date range that will be calculated at the time the query is generated instead of fixed dates. For example, if Last 7 days is used the results will be refreshed each time the query is generated to use the last 7 days from the current date. This date range will use the same Next Due, Recurring End, or Recurring Start depending on the payment value being used. |
|
|
There is the option to find recurrences associated with a specific campaign to allow further customisation in your query. If this is selected as All then all recurrence will be considered, regardless of the campaign. The dropdown will display all active and inactive campaign headers. |
|
|
If a specific campaign is selected then a specific campaign action can also be selected. If All is chosen then the query will show results for all recurrences with any of the campaign actions from the header selected above. The dropdown will display all active and inactive campaign actions for the selected campaign header. |
|
|
This dropdown will display all attributes in the group Products. This can be used to find specific products/event tickets that have been purchased and will display additional output columns. Selecting All will only give results for product payments but leaving this blank will give results for all payments, regardless if they are a product or not. |
|
|
Here you can select a specific GL Code to filter the results based on recurrences with this GL Code. If this is left blank then all recurrences will be included in the results, regardless of the GL code used. |
|
|
|
This will display your list of pay type attributes to choose from. Selecting a specific pay type will allow you to query very specific recurrences. If this is left blank then all recurrences will be included in the results, regardless of the pay type. |
|
Add/ Add more values/ Remove |
Use Add to save your recurring payment criteria to your query. The same payment value can be used multiple times with different criteria to create the specific query you need, click Add more values to load a new blank form to add multiple filter options. Use Remove to remove a payment value option from your query. |
When a recurring payment criteria is added this will display additional output columns available to use:
Payments
Each table includes 8 value options:
| Payment Value | Explanation |
| Any | This will result in a row for each payment from the contact matching the criteria. For example, Any from $0- $1000 from 01/01/2020- 01/08/2020 could result in one contact appearing multiple times, for each payment made during this time period. |
| Average | This can be used to find all contacts with certain average payment criteria. The average payment is calculated by their total payments divided by their payment count. |
| Count | This is used to find contacts with certain criteria on the number of payments they have made. |
| First | This is used to include criteria based on a contact's first payment, by pay date. |
| Highest | This is used to include criteria based on a contact's highest payment value. |
| Last | This is used to include criteria based on a contact's last payment, by pay date. |
| None | This is used to find contacts who have not made any payments of the chosen criteria. This can be used to exclude particular payments from your query. |
| Total | This can be used to find contacts based on their total giving, between amounts in a specified date range. This can commonly be used instead of 'Any' when the individual payment amounts are not required information. This will not display the additional payment outputs but will automatically include a TotalAmnt output column. |
Each payment value will open a new window to allow you to set your specific criteria, multiple values can be added to create a complex query giving you customised results.
These are explained below:
| Payment Value Criteria | Explanation |
|
These are used to set if the contacts must meet this criteria or not. When using AND, regardless of other criteria used in the query contacts must meet this payment criteria to show in the query results. When using OR, contacts can meet the other query criteria or this payment criteria to show in the results. NONE can be used for the first query criteria as it does not require AND or OR. |
|
| This is used to determine where this query criteria is ordered in the whole query. This works with the above setting to write the query in the correct sequence. | |
|
|
This dropdown shows all available payment statuses which can be used to customise your query results. This can be used to easily find all pending transactions or cancelled payments for example. If this is left blank then payments of any successful status (Payment Successful, Posted, Posted to General Ledger) will be included in your query results. |
|
|
A specific payment type can be chosen here to only find payments of this type. This payment type refers to the options at the top of the payment form, as well as Recurring which can be used for any payments linked to a recurrence: Non-specific payments will all be added as Standard but this is an easy way to find and report on those specific payment types such as Membership or Recurring. |
|
|
This is where you can set the payment amount you would like to query on. If you're looking for any payment amount then the default values can be left. The payment amount from and to must both be given a value. |
|
This is where you can set the payment dates you would like to query on. These can be left blank to see payments from any date. The payment date from and to must both be given a value if being used. The payment date ranges below can be used instead of these fixed dates if preferred. |
|
|
As an alternative to using the payment date values above, a date range can be used instead. This allows you to create a query with a date range that will be calculated at the time the query is generated instead of fixed dates. For example, if 'Last 7 days' is used the results will be refreshed each time the query is generated to use the last 7 days from the current date. |
|
|
There is the option to find payments associated with a specific campaign to allow further customisation in your query. If this is selected as All then all payments will be considered, regardless of the campaign used. The dropdown will display all active and inactive campaign headers. |
|
|
If a specific campaign is selected then a specific campaign action can also be selected. If All is chosen then the query will show results for all payments with any of the campaign actions from the header selected above. The dropdown will display all active and inactive campaign actions for the selected campaign header. |
|
|
This dropdown will display all attributes in the group Products. This can be used to find specific products/event tickets that have been purchased and will display additional output columns. Selecting All will only give results for any product payments but leaving this blank will only give results for all payments without a product. |
|
|
Here you can select a specific GL Code to only show results for contacts with these specific payments. If this is left blank then all payments will be included in the results, regardless of the GL code used. |
|
|
|
This will display your list of pay type attributes to choose from. Selecting a specific pay type will allow you to query very specific payments, such as those only with a cheque pay type to follow up and convert to a new way of giving. |
|
Add/ Add more values/ Remove |
Use Add to save your payment criteria to your query. The same payment value can be used multiple times with different criteria to create the specific query you need, click Add more values to load a new blank form to add multiple filter options. Use Remove to remove a payment value option from your query. |
When payment criteria is added this will display additional output columns available to use:
Please Note- The 'Payment Summary Outputs' options on the Output Columns tab will display data for that contact for their lifetime giving period, the specific output column added from one of the filters above will display data for the date range specified.
For example, using total for a particular time period and the Pay Amount output will give the TotalAmt output column for the amount paid in the given time period. Adding TotalAmount from the Output Columns tab will show the lifetime total amount paid for the contact.
When payment criteria have been added to your query it will show on the left-hand side, underneath the output columns. You can click to open the criteria from here and make any necessary changes or remove the filter.
Remember to save your query after making changes and adding new filter criteria.
Comments
0 comments
Please sign in to leave a comment.