“Who gave to us for the first time this year?” sounds like a one-filter question. In Altru it usually takes two or three, because a constituent’s giving history lives on many revenue records, and a simple revenue filter answers a different question: “who gave this year?” Those lists are not the same, and mailing the wrong one is how a 20-year donor gets a “thanks for your first gift” letter.
Below is how I build these lists, plus the closely related one: donors who have only ever given to one gift subtype, such as people who only give to the annual appeal or only to the animal sponsorship program.
Decide what “first-time” means before you build anything
Write down the definition and get development to agree to it. The common versions are:
- Brand-new donor: the constituent’s earliest donation falls inside your date range, and nothing came before it.
- Single-gift donor: exactly one donation, ever. A first-time donor from three years ago who never gave again belongs here, not in the first list.
- New to a fund or subtype: gave before, but this is their first gift to a particular designation or subtype.
Also decide what counts as a “gift.” Membership revenue, event registrations, and ticket sales are all revenue in Altru, but most development teams don’t count them. Pledge payments and recurring gift installments can turn one commitment into several revenue records. Settle these rules first, because each one changes the filter.
Building the first-time donor list
The idea is to find each constituent’s earliest donation and test whether it falls in your range. Filtering revenue by date alone won’t do that, since it matches anyone with any gift in the range.
- Start from constituents, not revenue. A constituent query returns one row per person, which is what you want for a mailing or a count. A revenue query returns one row per gift.
- Limit revenue to donations. Filter the revenue fields on transaction type and, if you use them, on the subtypes or designations your team agreed count as donations.
- Look at the earliest date. Altru’s query designer can summarize one-to-many fields such as revenue date (minimum, maximum, count). Filter so the minimum donation date is on or after the start of your range. If you don’t see summary options on the field you need, confirm with Blackbaud support or in your sandbox; a smart field that calculates first gift date is the usual alternative.
- Spot-check ten records. Open a handful of constituents from the results and read their revenue history. You will find out quickly whether old gifts or converted data are slipping past the filter.
The single-gift list uses the same setup, but instead of the minimum date you filter on the count of donation records equal to 1. Add a date filter on top if you only want single-gift donors from, say, the last 18 months.
Donors who only gave to one subtype
This one trips people up because a filter of “gift subtype = Annual Fund” returns anyone who has ever given to the annual fund, including major donors who also gave to the capital campaign. You need to exclude anyone with gifts outside that subtype. Two approaches work:
Use a selection to exclude
- Build a constituent query for anyone with a donation whose subtype is not the one you care about. Save it as a selection.
- Build a second constituent query for donations where the subtype is the one you care about.
- In the second query, add a filter that the constituent is not in the selection from step 1.
This is easy to explain to a colleague and easy to audit later, which matters when someone asks why a donor was on the list.
Compare counts
If summary filters are available, a donor whose total donation count equals their count of gifts in one subtype has only given there. In practice this is harder to set up in a single query, so I use the selection method unless I already have the counts as smart fields.
Watch for blank subtypes. Older or converted gifts often have no subtype at all, and depending on how you write the “not” filter, blanks may or may not be excluded. Test with a constituent you know has a blank-subtype gift.
Things that quietly skew these lists
- Households and soft credits. If one spouse gave last year and the other gives this year, the second may look brand-new. Decide whether you report at the individual or household level.
- Duplicate constituents. A returning donor entered as a new record will always look like a first-time donor. Duplicates are the most common source of embarrassing welcome letters.
- Refunds and adjustments. Check how refunded or written-off gifts appear in your query results so a reversed gift doesn’t count as someone’s one gift.
- Data conversion dates. If your history before going live on Altru was imported in summary form, “earliest gift” may really be “earliest gift since conversion.”
Put the list to work
A first-time donor list is most useful while the gift is recent. Run it monthly, not once a year, and use it for a welcome note, a specific thank-you about what the gift supported, and a second-gift ask a few months later. Single-subtype donors are a good audience for a gentle introduction to a second program, as long as the message doesn’t treat them as if they’ve never heard from you.
Save the queries with clear names and the definition in the description field, so whoever runs them next year gets the same answer you did.
Getting the list out of Altru automatically
Once the query is right, the remaining chore is exporting it to wherever your stewardship actually happens. Social Good’s Data Bridge syncs Altru query results to Google Sheets, Mailchimp, ActiveCampaign and similar tools, so a first-time donor query can feed a welcome audience or a shared tracking sheet without someone exporting and re-uploading a CSV each month.
