Donor retention sounds like one number: of the people who gave last year, how many gave again this year? In practice the number shifts depending on what you count as a gift, which year you compare, and whether donors who have died since last year are still in the denominator. Before you build anything in Altru, settle those definitions. The queries are the easy part.
Decide what “retained” means at your organization
Write these choices down and share them with whoever reads the report, so the number means the same thing every time it’s run.
- Which revenue counts. Is a membership purchase a gift? Many museums and gardens report donors and members separately, because membership is closer to a purchase. Others count membership above a certain level as philanthropic. Pick one and stick with it.
- Which periods you compare. Fiscal year to fiscal year is the usual choice. Calendar year works too, but don’t mix them.
- Individual or household. If a spouse gave last year and the other spouse gave this year, is that a retained household or one lapsed donor plus one new donor? Household-level retention is usually closer to reality for family giving.
- How to treat deceased donors. More on this below, because it’s where retention reports most often go wrong.
Build it from two queries
Altru includes standard revenue and fundraising reports, but I’d confirm in your own database whether any of them calculate retention exactly the way you’ve defined it. If you’re unsure, check the reports list in a sandbox or ask Blackbaud support. Most administrators end up building retention from queries, because queries let you control every one of the definitions above.
- Prior-year donors. Create a constituent query (or a revenue query output at the constituent level) that returns everyone with qualifying revenue in the prior fiscal year. Filter on revenue type and date, and on designation or campaign if you’re tracking a specific fund. Save it as a selection.
- Current-year donors. Copy that query and change the date range to the current fiscal year. Save it as a second selection.
- Retained donors. Build a third query that includes constituents who are in both selections. That count, divided by the prior-year count, is your retention rate.
- Lapsed donors. Constituents in the prior-year selection but not the current one. This is your LYBUNT list (gave last year but unfortunately not this year), and it’s the list your development team actually wants to work from.
For year-over-year trends, repeat with earlier pairs of years. A SYBUNT list (gave some year but not this one) is the same approach with a wider prior date range.
Run the queries once and spot-check a handful of names by opening their constituent records. Make sure soft credits, recurring gift payments, and refunds are being treated the way you expect before you share any percentage.
Handling deceased donors
A donor who died during the year didn’t lapse. If they stay in the prior-year count, your retention rate drops for a reason that has nothing to do with stewardship, and their name lands on a lapsed list that someone might mail an appeal to. That second problem is the one that hurts.
The common practice:
- Exclude deceased constituents from the denominator. Add a filter to the prior-year query that removes anyone marked deceased. They aren’t donors you could have retained.
- Always exclude them from lapsed and solicitation lists. This isn’t optional. Check it every time you export.
- Think about the surviving spouse. If you report at the household level, the household may still be active and giving. If the gift came through a spouse’s record, make sure the surviving partner’s record and household reflect the change so they aren’t dropped or misaddressed.
- Track memorial and estate gifts separately. Gifts made in memory of a donor, or a bequest, are worth reporting, but they aren’t retention. Keep them in their own line.
All of this depends on the deceased flag actually being set. Retention reporting is a good excuse to audit it: pull donors with no gifts in several years and a known age range, compare against obituary notices or returned mail, and update records. Also check for duplicate constituent records. A donor who gave under two records looks lapsed on one and new on the other, which distorts both numbers.
Turning the queries into a report people will read
Most development directors don’t want a query. They want a short table: prior-year donors, retained, lapsed, deceased removed, new donors, and the rate, ideally for the last three to five years side by side. You can export each query’s count to a spreadsheet and assemble it by hand each month. That works, but it’s the step that tends to get skipped when things get busy.
A few habits help:
- Keep the query names consistent (for example, a shared prefix plus the fiscal year) so anyone can find and rerun them.
- Document the definitions on the first tab of the spreadsheet.
- Report first-year donor retention separately from repeat donors. First-time donors usually retain at a much lower rate, and blending them hides where the problem is.
Keeping the numbers current
If the monthly export-and-paste routine is the bottleneck, Social Good’s Data Bridge can sync Altru query results to Google Sheets, so the retained and lapsed queries feed a shared sheet without manual exports. The definitions and filters above still live in your Altru queries; Data Bridge just moves the results.
Quick answers
Should deceased donors count as lapsed?
No. Remove them from the prior-year count and from any lapsed or appeal list. Report memorial and estate gifts on their own line.
Should membership revenue count toward donor retention?
That’s a policy decision. Many organizations report member renewal and donor retention as separate metrics. Whatever you choose, apply it the same way every year.
