The anniversary mailing, the “20 years with us” pin, the donor prospect list for a capital campaign: they all start with the same question. Who has been a member the longest? In Altru that question sounds like one query. In practice it depends on what “longtime” means to you, and on how clean your membership history is.
This guide covers how to define tenure so the list holds up, the query approaches that work, where a smart field may or may not help, and the data problems that quietly make a 15-year member look like a newcomer.
Decide what “longtime” means before you build anything
Three people on your staff can ask for “longtime members” and mean three different lists. Pin it down first:
- Original join date. “Member since 2004,” regardless of gaps. Easiest to explain, and the version members themselves tend to quote.
- Continuous years. Unbroken membership with no lapse longer than your grace period. This is the stricter version, and the one most recognition programs actually mean.
- Total years as a member. Add up every term, gaps excluded. Fair to people who lapsed for a year during a move, but harder to calculate.
Also decide whether you’re counting the constituent or the membership. If a couple’s household membership was originally in one spouse’s name and later moved to the other, a constituent-based query may split their history in two.
Approach 1: earliest membership date in a membership query
For the “member since” definition, a membership query is the simplest route. Build it on current, active memberships so you’re only looking at people who are members today, then bring in the earliest relevant date from the membership’s history: the join date if your records carry one, or the earliest membership transaction date if they don’t.
Sort ascending and you have your list. Filter for anything earlier than a cutoff (say, 10 or 25 years ago) to get a milestone group.
Field names and what’s available vary depending on how your database was set up and migrated, so open a few membership records you know well and compare what the query returns to what you see on screen. If a member you know joined in the 1990s shows a 2015 date, read the migration section below before trusting the rest of the list.
Approach 2: count the terms
When you need continuous or total years, the earliest date isn’t enough. You need the history of terms. Query membership transactions (joins, renewals, rejoins, upgrades) with the transaction date and the expiration date for each, one row per transaction.
From there, the work is easier in a spreadsheet than inside a query:
- Export the transaction rows and sort by membership, then by date.
- For each membership, compare each term’s start to the prior term’s expiration. A gap longer than your grace period breaks continuity.
- Calculate years since the most recent break (continuous) and the sum of term lengths (total).
- Bring the result back as a list or a constituent attribute if you need to segment on it in Altru.
Upgrades and downgrades mid-term can look like extra terms if you count rows blindly. Count distinct membership years, or exclude upgrade transactions from the count, rather than counting every transaction.
Is there a smart field for years of membership?
Smart fields are the natural place for a value like this, since they calculate once and stay queryable. Altru includes a range of membership-related smart fields, but I can’t promise there is one that calculates tenure the way your organization defines it, especially continuous years with your own grace period. Check the smart field list in your database, or ask Blackbaud support whether a tenure-type smart field exists for your version. Test anything you find against members whose history you already know.
If nothing fits, the spreadsheet calculation above stored as an attribute is a reasonable stand-in. The catch is that it goes stale. You’ll need to re-run it on a schedule, at least before each recognition mailing.
Data problems that hide longtime members
- Migration dates. If your history came over from an older system, older terms may have been collapsed into one record, or the start date may be the conversion date. Spot-check members you know have been around for decades.
- Duplicate constituents. A member who renewed under a second record (new email, maiden name, a typo) has their history split, and each half looks shorter. Merging duplicates often surfaces tenure you didn’t know you had.
- Gift memberships. If a grandparent bought the first few years, the early terms may sit on the purchaser’s record, not the member’s. Decide whether those years count.
- Rejoins vs. renewals. If staff processed late payments as rejoins, your data may show breaks that weren’t real lapses under your policy.
Keeping the list current
Once the query is built, the recurring work is getting the results somewhere people can use them: a recognition spreadsheet the membership manager updates, or an email segment for an anniversary thank-you. Data Bridge can sync an Altru query to Google Sheets, Mailchimp, ActiveCampaign, and similar tools, so a “member since” or terms query can feed a sheet or audience without a manual export each time. The tenure logic itself still comes from your query and your definitions.
Quick answers
Should lapsed members appear on a longtime list?
Usually not for recognition, since you’re thanking current members. For a reactivation campaign, a list of lapsed members with long past tenure is often one of the best audiences you have.
How do I handle households?
Pick one constituent per membership (the primary member, typically) to avoid sending two anniversary letters to one address, and check that household history wasn’t split across records.
