RE Exports

Greg McNaughton · November 2021

Hi, in my RE export I need to have a column that shows which of the constituents are active regular givers, any ideas?

Alan French · November 2021

You could create a gift query for your regular gifts (however you define that - Recurring Gifts with a status of Active?) then in your export add a summary (at the bottom of the list). Choose “total number of gifts” and then on the Filters tab, select your query. That should give you a count for each constituent of how many active regular gifts they have.

Jess Moxsky · November 2021

I would say the best option here is to use constituent codes. Whether you build a query from scratch or reverse engineer it from a relevant report, you can then identify either your “active” givers with a constituent code, or do the same for your LYBUNT/SYBUNT donors and identify them with a constituent code.

Then, you can pull that constituent code into any exports you need. Alan's advice is also great if you just need to know how many gifts those constituents have given.

I want to say you can also set up business rules to flag donors who have a particular constituent code when you add a new gift to the record, essentially reminding you to update their constituent code. Hopefully some other folks will share some tips and tricks for this as well!

JoAnn Strommen · November 2021

Since reading your post I've thought about how I would do this. IMO, it comes down to query vs. a notation of some type. I would opt for a dynamic query such as Alan French‍ described.

While the first thing that came to mind was an attribute or possibly constituent code, the more I've considered it, I don't think I'd go that route. For me, a notation such as this does not fit the description for us to use a constituent code. If I went that route it would be an attribute. While Jess Moxley‍ presents possible options to add code to new 'qualifying' records, how will you remove coding from those to which it no longer applies? The adding/removing/updating maintenance is more than I would want to deal with. And what if a year from now you change your definition of 'active' or want to add more qualifiers such as amount or fund?

Running a query will provide up-to-date and flexible information without having to enter and maintain info on records.

My 2 cents.

Faith Murray · November 2021

I would say that @Alan French has some good advice. You can simply add a Gift Summary column to your Export with total number of qualifying gifts. Although, I have never needed to use the Query filter for this field. The Export already includes gift date, fund, and type which have been the only fields we've needed to define an “active” or “regular” donor.

Active and regular might be two different qualifications. Anyone with a gift in the past 12 (or 24, or 60) months would be active, depending on your definition. So by adding a date filter of 12 months to your Summary, it will output a number only for your Active donors.

Regular might look a little different.

If by regular, you mean Recurring, simply add a Recurring gift type filter to the Summary field, so show only the number of recurring gifts made in 12 months.
If by regular, you mean they give multiple gifts a year (of any type), then only a date filter is needed. It will output the number of gifts this year, and you can look for those 3+ gift per year donors.
If by regular, you mean that they give every year, then you will need to output 2-3 Gift Summary columns. Set the first Summary for this year, the second Summary for last year, and so forth. Your “regular” donors will be the ones with gifts in multiple year columns.

I would personally avoid using Constituent Codes to track Active donors. A robust mail file will need a dynamic definition. Someone who was “active” in April may no longer be “active” in June if they fail to renew their once-per-year April gift, as “Active” is based on recency. So unless you want to constantly update that constituent code every month for all lapsed/new donors – or unless you only mail once a year and so updating your data is less time-sensitive – stick to a Query or Export filter that can update itself every time you run the Export. Most people reserve Constituent Codes for long-term relationship markers, such as Foundation, Alumnus, Board Member, for this reason.

Jess Moxsky · November 2021

This is why I LOVE the community - so much amazing advice here!

Jeannie Goings · November 2021

I agree with @JoAnn Strommen and @Alan French. Dynamic query with as little maintenance as possible.

Catherine Burns · November 2021

Hi Greg. I do what Alan suggests and add a Summary that counts active regular gifts. However we also use constituent codes to track regular giving history. A donor may have a regular gift, then cancel it and later start again, so they will have to Regular Giver Constituent Codes both with start dates, and one with an end date. We have an internal rule that the active Regular Giver constituent code trumps everything else and is the primary constituent code on a record i.e. the uppermost code without an end date. With query I can output Primary Constituent Code, but with Export I cannot. This is a source of frustration for me. I have been known to do my export, and then export a query of the same donors (usually your export source) that includes Primary Constituent Code and back match them in Excel using a vlookup or index/match formula so I get the true Primary Constituent Code attached to my export data.

RE Exports

Comments

Categories