Query for New memberships

Chris Z

Is there a way to query on constituents who have a first time membership, and have never been members before?


I've tried looking in the Membership criteria (because we have the Membership module), but I'm not seeing a way to manipulate it so that we can omit constituents that may have a different category.  I tried looking in History at Total Years as Member and Total Memberships, both equal to 1, and a First Join Date within the last few months, but had no results.


Thanks in advance.

Ryan Hyde

I don't have the membership module, so there may be a much better way to do this.


Do you code your membership gifts differently than other gifts? Like, do they get a special appeal or fund or campaign? Or is there maybe a membership attribute that the membership module utilizes in some sort of accessable way?


If not, I don't know how to help, but if yes, then you could set up a constituent query as follows:

1. Summary # of Gifts > 0
   1. Gift Date = [the date range you want to look for new members in]
   2. Include the criteria that defines a membership gift (appeal, attribute, fund, campaign, whatever)
2. Summary # of gifts = 0
   1. Gift date < [start date of previous summary field]
   2. Include the criteria that defines a membership gift

So you're saying, this person has a membership gift in X timeframe but has zero membership gifts at any point before X timeframe. 


If your database is set up to have both spouses as separate constituents, then you'll want to set the query up to look at soft credits as well as hard credits to avoid situations where a membership gift may have been recorded on one constituent's record one year and on another constituent's record the previous year.


Finally, there's also a New Donors report that you might be able to tweak for your purposes, but I like to use queries for this sort of thing since you can then interact with the output.

 

Chris Z

You may be on to something Ryan.  What happens in RE is that a gift is entered, and then a membership is attached to the gift.


We use only a handful of funds that are dedicated to recording those gifts.  So, I hadn't thought of it before now, but if I look for "new donors" with the count of gifts, and a gift date, I truly wouldn't be searching for memberships at all.  I can use the funds specifically for memberships.  While this won't be perfect, it will get me close.


Thank you,

Chris

Ryan Hyde

Great! I'm glad this is a potential solution for you. Good luck

Gracie Schild

While I agree with Ryan that I'd rather use a query any day over a report, the Membership Reports/New Member Report will give you all "joins" within the date range you set.  You can get that in a query by doing a Membership Query, selecting History and Joined on Date, and making the date range you're after the criteria.  You have the same thing under Current (for currently active membership) but that might get things mixed up.  There are several basically canned reports that you can have on your dashboard that show joins, drops, and final numbers for each category each month - they can be adjusted to fit your needs. Our super consultant/trainer pointed out that the Membership module was one of the most labor intensive, and he wasn't kidding. In our case the confusion is increased by the fact that our museum is downtown and we are on the college campus about 13 miles away on the edge of town. Delays are inevitable, and confusion about special situations (and a lack of knowledge of RE by most of the Museum staff) makes it...um...challenging.


The other point Ryan brings up - Fund numbers/names - is a second way we track all of this.  The IAIA Foundation doesn't actually "count" the Museum Membership in its fundraising (long story) even though we process all the funds.  It's treated as a "passthrough" for the Foundation. So Membership has both its own Campaign and its own Fund. The appeal is either the renewal letter number or other way in which the membership came to us. When running numbers for the Foundation, we only include the campaign (Annual Campaign 17) that falls under its fundraising goals, so our Capital Campaign is separate, as is Museum Membership and Endowed funds (though that might change).