Consecutive Year of Giving & Sequential Value Groups

Updated 05 Sep 2024
2 minute read

Print
Share
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

A donor’s length of consecutive giving can queried using the special system join of Sequential Value Groups. This join enables identifying a data point (such as a gift's year) that can then be used to count the number of times it appears consecutively (such as 2016, 2017, 2018, 2019, and 2020), as well as the minimum and maximum range of the consecutive span (such as 2016 and 2020).

For example, an institution might want to query for donors who have given consecutively for at least 5 years, displaying the number of consecutive years given as well as the start and end date for the range. This type of query can be configured in two primary steps:

Add a subquery filter based on consecutive giving.
Add a subquery export that returns the details of the consecutive giving.

The output of this type of query will resemble this example:

Filter the Query Base

To identify donors who have given consecutively for at least 5 years, first establish the sequential data point to count. In this example, the data point to be counted is the gift year.

Create a new query using Person as the base.
In the Filters section of the query summary page, click the subquery icon. An Edit Part popup appears to configure a dependent subquery.
In the Filters section of the popup, click Join. An Insert Query Part popup appears.
Locate and select the Sequential Value Groups join and click Continue. An Edit Part popup for the join appears.
In the Filters section of the popup, click Join. An Insert Query Part popup appears.
In the Person section, locate and select the Gifts join, and click Continue. The Gifts join popup appears.
Click Save. The Sequential Value Groups join popup appears.
Click Export. An Insert Query Part popup appears.
In the Gifts section, select Date and click Continue. The Gifts Date export appears on the Edit Part popup.
Edit the Gifts Date export and set desired Format Type and Format Mask values. In this example, Format Type is set to Date and Format Mask is set to “yyyy.”
Click Save to return to the original subquery filter configuration popup. Give the filter an appropriate name. This example uses “Gift Date Sequential Values” as the value for Name.
Click Save. The subquery filter appears in the Filters section of the Edit Query summary page.
In the Exports area of the summary page, click the subquery icon. An Edit Part popup appears.
Follow the same steps (2 through 11) used for the subquery filter process to configure the subquery export.
On the original subquery export popup, click Export. An Insert Query Part popup appears.
In the Sequential Value Groups section, select Count, Min, and Max, then click Continue.
To see all three data points, set the Output value to Concatenate and reorder the exports and add literal values (to provide context to the returned values) as desired.
Give the subquery an appropriate name and click Save.
As configured in this example, the query now returns values for Person ID, First, Last, and the configured values for Consecutive Years Giving.
The query can be further refined to limit results to a specific number of consecutive years within a specific range. Additional details are provided in the following Slate Bytes video.