Using A Constituent Query To Identify And Target Lapsed Donors

One of the prime features of Altru is how we can mine data on our organization’s relationship with its community of supporters.  Along with identifying donors, members and patrons, it is also helpful to identify how our relationship with them has evolved or more appropriately, in the context of lapsed donors, changed.

While lapsed donors are a practical reality of advancement efforts, they should not all be considered a “lost cause”. With targeted communication, we can rekindle relationships and motivate them to resume their support of our mission. While we can set up an ad-hoc query, Altru does have a set of pre-built Smart Queries to easily identify this group.

Before we dive into these options, please remember it is important for us to understand if we want to target constituents based on their revenue (i.e. they were the donors) or their recognition (they have received soft credit for someone else’s contribution).

1. LYBUNT SMART QUERIES
Altru makes it convenient to target LYBUNT (Last Year But Unfortunately Not This year) via two smart queries based on either revenue or recognition.

To access either of these queries, just follow these simple steps:

1. Access the Analysis module and click on Information Library.
2. Click Add a smart query.
3. Click on one of the source views to select them: LYBUNT constituents (revenue) or LYBUNT constituents (recognition) and then click OK.
4. In the window displayed, use the Parameters tab to identify which time interval you would like to select for “This Year” and “Last Year”. To include all donors, keep the Selected Donors blank. Use this selection field to access a constituent query to narrow the field. For example, you can target current and past board members.
5. The Results tab can be used to review the output.
6. You can save this query via the Set save options tab if you want to use this smart query in other Altru processes such as mailings.
7. Like other query functions, you can use Browse to drill down into constituent records, or the Export functions to pull a list out of Altru.

2. SYBUNT SMART QUERIES
Similar to LYBUNT, Altru makes it convenient to identify SYBUNT (Some Year But Unfortunately Not This year) via the parameters of revenue or recognition credit.

1. Access the Analysis module and click on Information Library.
2. Click Add a smart query.
3. Click on one of the source views to select them: SYBUNT constituents (revenue) or SYBUNT constituents (recognition) and then click OK.
4. In the window displayed, you will note that the Parameters tab is slightly different. We only have “This Year” - there is no “Last Year” option. You still have the option to use a selection field to access a constituent query to narrow the field of constituents to a specific group of choice.
5. The Results tab can be used to review the output.
6. You can save this query via the Set save options tab if you want to use this smart query in other Altru processes such as mailings.
7. Like other query functions, you can use Browse to drill down into constituent records, or the Export functions to pull a list out of Altru.

3. REVENUE DYNAMICS/GIVING DYNAMICS SMART QUERY
The Revenue Dynamics (also known as Giving Dynamics) smart query allows us to target constituents based on transaction details, specifics of their revenue activity across two different periods of choice. So, along with two separate time periods, we can identify constituents based on the giving history: if they are new, recaptured, upgraded, same, downgraded, new lapsed and repeat lapsed.

1. Access the Analysis module and click on Information Library.
2. Click Add a smart query.
3. Click on the Revenue dynamics source view and then click OK.
4. In the window displayed, you will note that the Parameters tab we can pick between two date-based periods, the status of their giving and revenue details such as amount and revenue type.
5. The Results tab can be used to review the output.
6. You can save this query via the Set save options tab if you want to use this smart query in other Altru processes such as mailings.
7. Like other query functions, you can use Browse to drill down into constituent records, or the Export functions to pull a list out of Altru.

4. CUSTOM AD-HOC QUERIES
Along with smart queries mentioned above, one can also leverage ad hoc queries and the merge them for a more customized option. So, for example, suppose if we want to identify donors that gave in 2016 but not in 2017.

Step 1: Create a query of all donors who contributed in 2016

1. Access the Analysis module and click on Information Library.
2. Click Add an ad hoc query.
3. Click on the Constituents source view and then click OK.
4. Specify the revenue date range:
   a. Highlight Revenue.
   b. Drag Date into the criteria window and select your date range (2016).
5. If you prefer to include recognition credits:
   a. Select Recognition Credits node.
   b. Drag Effective date into the criteria window and select your date range (2016).
6. Specify the revenue type you would like to include:
   a. For donors, expand Revenue and select Application Details.
   b. Drag Application into the criteria window and select Donation.
7. If you would like to target a specific designation for the donation:
   a. Expand Revenue and highlight Application Details.
   b. Drag Designation System Record ID into the criteria window and select your designation(s).
8. Click on Set Save Options tab and name your query. Be sure to include the date range in the name of your query and to check the box to Create a Selection.
9. Save and close.

Step 2: Create a query of all donors who contributed in 2017
Repeat all the steps for above with the only difference being the date range should be that for 2017.  When you save the query make sure to include the date range in the name of your query and check to box to Create a Selection.


Step 3: Merge Queries to parse results
We can use the Merge Query function to help target donors in 2016 that did not give in 2017:

1. Access the Analysis module and click on Information Library.
2. Use the expand icon next to Donors 2016 and select Merge.
3. The Donors 2016 query will automatically populate as Query A.  Search for Donors 2017 query to be Query B.
4. You can then select the merge option to include records in Query A but not in Query B.
5. 5. Make sure that you check the box to Create a selection and give the query a name that clearly identifies the basis behind this merge criteria

We now have four very convenient options to identify lapsed donors and leverage communications in Altru to customize our communications and revive our relationship with them.

Please refer to some additional Knowledgebase articles for some additional information on analyzing contributions and constituents:
I need to report on donations to a specific designation or fund
What query filters should I use to find contributed revenue
I need to merge queries (includes video demo)