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 whether the contacts must meet these criteria or not. When using AND, regardless of other criteria used in the query, contacts must meet these payment criteria to show in the query results. When using NONE or OR, contacts can meet the other query criteria or this payment criteria to show in the results. |
|
| 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 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 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, or 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 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. |
|
|
|
This allows you to set these payment criteria as an output only. With this option, the query results will not be filtered based on these settings, but if a contact matches a payment to these settings, then the output columns will be displayed. |
Add Criteria
The Add Criteria drop-down button allows you to add further filters based on the payment fields. Multiple criteria of the same type can be added to include multiple campaigns, for example.
Note: Different types of criteria (e.g. Campaign, Campaign Action, Payment Attribute Type) are combined using AND. However, if multiple values of the same type are selected, they are grouped using OR.
For example:
WHERE Campaign = 'Quick Raffle'
AND (Campaign Action = 'Quick Raffle 1' OR Campaign Action = 'Quick Raffle 2')
AND (Payment Attribute Type = 'Contact Branch' OR Payment Attribute Type = 'Payment Workflows')
| Criteria | Description |
| Campaign |
This allows you to filter payments processed with the selected campaign(s). You have the option to select a campaign header to include all actions or specific campaign actions. When multiple campaign headers are added, the query will be filtered on payments matching any of the selected headers. This is the same for multiple campaign actions added. When multiple criteria are used, it should either be only campaign headers or campaign actions selected. |
| GL Code |
Adding criteria will filter the query results based on payments with the selected GL Code(s). |
| Pay Type | Adding criteria will filter the query results based on payments with the selected Pay Type(s). |
| Campaign Attribute |
These dropdowns will display attribute types and attributes that have been added to any campaigns. Adding criteria here will filter the query results based on payments where the campaign has the selected attribute(s). |
| Payment Attribute | Adding criteria here will filter the query results based on payments with the selected attribute(s). |
| Product |
These dropdowns will display attribute types and attributes in the Products Group. This can be used to find specific products/event tickets that have been purchased Selecting All will only give results for any product payments. |
Outputs
The outputs tab will only be available with the Any, First, Highest, and Last payment options, as these provide details on individual payments.
Each output that you would like to see in you're query results for this specific payment criteria can be selected using the checkbox on the left-hand side.
There is also the option to sort the results based on these output values using the sort dropdown and then the sort order when multiple outputs are included. For example, adding a sort of DESC and sort order 1 on Pay Amount will display your results from the highest payment amount to the lowest payment amount. When sort options are added, you will need to click the Update button at the end of the output row to save these settings.
Some of the output rows also have a filter dropdown and value box that can be used to further filter your query results, as shown below.
- Equal allows you to set a single value for this output that must be met.
- Greater/Less Than (or Equal) allows you to set a single value to be checked against. This can be used for the Transaction ID and Recurring Payment ID outputs, as they contain a number value.
- The IN or NOT IN options can be used to list possible match values. For example, Postcode IN '0612', '0620', '0600' will return results for all contacts who have either one of the three postcodes. Please note ' ' are required around each value, and they need to be separated by a comma.
- When using the Like or Not Like options, the % can be used as a 'wildcard' in place of any characters, for example, Pay Details Like %Insufficient Funds% will include all payments that include these words in the payment details.
- The options for IS NULL and IS NOT NULL are used to filter out blank values or find blank values. For example, Pay Details Is Not Null will provide results on all payments where there is a value in the field and can be used to find widget comments.
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.
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.
Remember to save your query after making changes and adding new filter criteria.
Comments
0 comments
Please sign in to leave a comment.