“Lapsed donor” sounds like one filter. In practice it’s a comparison between two time periods: people who gave in an earlier window and have not given in a later one. Altru’s query tools can answer that, but not with a single date filter. A single query with a date range tells you who gave in that range. It can’t tell you who is missing from the next one.
The approach that works is to build two lists and subtract one from the other. Here’s how to set it up, plus the decisions you should make before you trust the results.
Decide what “lapsed” means at your organization
Settle these questions before you build anything. Otherwise the query will be technically correct and still not match what your development director has in mind.
- Which periods? The common definitions are LYBUNT (gave Last Year But Unfortunately Not This year) and SYBUNT (gave Some Year But Unfortunately Not This year). Decide whether “year” means calendar year or fiscal year. Most cultural organizations report on the fiscal year, so the query should too.
- What counts as a gift? Altru records donations alongside memberships, tickets, and event registrations. A member who renewed but never made a separate gift probably shouldn’t be on a lapsed donor list. Decide whether membership revenue counts, and whether event revenue with a charitable portion counts.
- Payments or pledges? Someone with an open pledge and no payment yet this year may not belong on a lapsed list. Pick one rule and write it down.
- Individuals or households? If one spouse gave last year and the other gave this year, is the household lapsed? Your answer decides whether you query constituents or roll up to households.
Build it as two selections
1. “Gave in the earlier period”
Create a revenue query filtered to the earlier date range (for LYBUNT, last fiscal year). Narrow it to the revenue that counts as a gift under your definition, for example donation payments only. Return the constituent, then save the results as a selection of constituents. Make it a dynamic selection if you want it to recalculate each time it’s used.
2. “Gave in the current period”
Copy that query and change only the date range to the current fiscal year to date. Keep the revenue filters identical. If the two lists define “gift” differently, someone who renewed a membership this year could look active when they haven’t given a dollar. Save this as a second selection.
3. Subtract
Build a constituent query that includes people in the first selection and not in the second. That’s your lapsed list. Add the output fields you’ll actually use: name, primary email and address, last gift date, last gift amount, and total given in the earlier period. Smart fields work well for the summary values if your database already has them set up.
For SYBUNT, widen the first selection to cover several prior years (say, the last five fiscal years, not counting the current one) and keep the second selection the same.
Field names and filter options in the query designer vary with how your database is configured, so check the exact revenue type and application fields in your own environment. If you can’t find a way to separate donation payments from membership payments in a revenue query, Blackbaud support can tell you which fields your version exposes.
Filter out people you shouldn’t contact
A raw lapsed list will include people who should never get an appeal. Before anyone exports it, add exclusions to the final query:
- Deceased constituents
- Anyone with a do-not-solicit or do-not-mail/email preference relevant to the channel you’re using
- Board members and staff, if you handle them separately
- Organizations and foundations, if this list is meant for individual giving
- Major donors who have a relationship manager and shouldn’t get a mass appeal
Check it before you use it
Pick five or six names from the result and open their constituent records. Confirm each one really did give in the earlier period and really has nothing in the current one. Pay attention to soft credits and gifts made through a household or a donor-advised fund: those are where a “lapsed” donor often turns out to be giving under a different record.
Duplicates cause the same problem. If a donor has two constituent records, last year’s gift can sit on one and this year’s on the other, and the query will flag them as lapsed. Merging duplicates before a lapsed-donor mailing saves you an embarrassing letter.
Also compare the count with last year’s lapsed list, if you have one. A big jump usually means a filter changed, not that donors walked away.
Keep it running
Built on dynamic selections, the query recalculates whenever you run it, so you don’t need to rebuild it each month. Two habits help. First, name the selections clearly (for example, “Donors – prior FY – donation payments”) so nobody edits one and quietly breaks the lapsed list. Second, update the date ranges when the fiscal year turns over, or use relative date filters if your query options support them.
Many teams run the list monthly during the second half of the fiscal year, when there’s still time to reach donors before they become two-year lapsed.
Getting the list where your team works
The query lives in Altru, but the follow-up often happens somewhere else: a stewardship spreadsheet, or an email platform for a “we miss you” series. If your team is exporting this list by hand every month, Data Bridge can sync the results of an Altru query to Google Sheets, Mailchimp, ActiveCampaign, and other tools, so the downstream list stays current without repeated exports. Altru still holds the giving history. Data Bridge just keeps the copy up to date.
Quick answers
Can I do this with one query instead of two selections?
Not reliably. You need “gave then” and “didn’t give now,” and a single date filter can’t express the second half. The two-selection approach is the standard pattern.
Should members who didn’t donate be on the list?
Usually not. Lapsed members and lapsed donors are separate audiences with different asks. Build a separate membership query for expired members.
