Basic Reporting Part IVc: Membership Reports - Tips

Basic Reporting Part IVc: Membership Reports - Tips 

1. When working with member and membership reporting keep in mind that Members and Memberships are different. A member is a person. A membership is a subscription. A membership may have several members, if it’s a group membership (family membership or organizational membership). For example, if Mr. and Mrs. Joe Smith share a family membership, they are TWO members but ONE membership.
2. When writing member and membership reports or viewing member or membership data, be sure to know which number you want: # of members or # of memberships.
3. Also, when writing membership reports, think about how you want to see the data – do you want to see an alphabetical list of member names or do you want to see the data grouped/organized by membership?
4. The easiest way to get a list of contacts who are current members is to use the criteria Contact Type Equals Members (this can be done in advanced search and in any type of report).
5. The easiest way to get a list of people are expired members is to use the criteria Contact Type Equals Ex-Member. If you wish to exclude cancelled members (and only find expired/lapsed members), include the Membership Status field (the status field contains cancelled, lapsed, etc. Use criteria Membership Status does not equal cancelled to exclude inactive members who cancelled)
   
6. You can also use the Membership Status field (as a filter) to search for those whose membership is active, lapsed, expired, or cancelled to find active or inactive members (membership status is active or membership status is not active, etc. However, each membership record per person will have a status - that is, if someone joined in February 2016 and renewed in February they have 2 entries in the membership table. Each of those entries has it's own status. You need to use the Max function/filter or Last Node field (see Step #9 below) to tell the report to only look at the most recent membership entry when limited by status)
7. When working with memberships, it is ALWAYS better to renew, upgrade, downgrade or transfer an existing membership than creating a new membership for a contact. When contacts each have more than one separate membership it makes reporting harder due to having multiple statues (simultaneously having an expired and active membership, having multiple join dates, etc.). Below is an example of someone who has two memberships. This is going to create reporting difficulties.
8. It can help to think of one membership that has been renewed several times as a continuous chain, where each membership period in that chain is a link in the chain. For example, if your memberships last for one year and Joe Smith joins on 2/14/2015, renews on 2/13/2016 and renews again on 2/13/2017, he has three links in his membership chain:
   • Link #1= Feb. 2015 to Feb. 2016,
   • Link #2 = Feb 2016 to Feb 2017
   • Link #3 = Feb. 2017 – Feb. 2018

   Below is a picture of the membership history of someone who has four links in their chain. These are all transactions applied to one/the same membership.

9. The links in the membership chain/history of joins and renewals are referred to as “nodes.” To ensure that your report is just considering the current/most recent membership dates/status, add a criteria for Last Node (on the MemberView table) Equals True. Last Node Equals True tells the report to only look at the last link/node in the membership chain. This criteria can be added to advanced search as well as all types of reports.
10. If your members have more than one membership, one active and one expired, then be careful when writing reports that show members who were new/joined. They will have two join/membership start dates. To exclude people who may have a past/expired membership, use the Member Since field on the MemberView table. This field records the very first time someone became a member. That way, if they have an expired membership and they join again, they will not be counted on your report as a new member. [Hint: the default Membership: New Members by Month - Mailing List Report already has this criteria added] 
11. If your members have more than one membership, one active and one expired, then be careful writing reports that show memberships that have expired (using Membership End Date) because it will pull the inactive/old membership even if they have a current membership. To exclude people who are currently active members even if they have an expired membership add a criteria for “Contact Type is not [or is not equal to] Member” to the report (to exclude those who are current members) and use the Max/Min filter option on the MemberView End Date field (this tells the report to only look at/consider the most recent membership end date). This can only be done in advanced reports; it cannot be done in an Advanced Search or Express View reports. If you are trying to find a list of people who will be expiring in the future, this is much more difficult because you cannot use the contact type does not equal Member criteria as people who will be expiring have not yet expired/become an ex-member. Use the Max/Min filter option and/or Last Node field but be aware that if any of your contacts have more than one membership record they will still be pulled into this report even if they have already renewed one of their memberships (they will be pulled in if the other membership meets the criteria) [Hint: the default report Membership: Expiring Members by Month already has these filters in place/works this way but be sure to read the report description to understand the report's limitations]

    

12. The Membership Enrollment Type field (in advanced search and reporting) contains the values for things like Joined, Renewed, Upgraded, Downgraded, etc. The Membership Status field contains values for things like Cancelled, Expired, Lapsed, and Active.
13. One of the most complicated types of reports to write is to identify members who were active at a particular point in time (e.g. "Everyone who was an active member in February 2017). While this might seem like a straightforward question, it's actually very complicated. The reason why it is complicated is because eligible contacts could fall into one of three categories:
    • Members whose membership started before the target dates and ended during the target dates. For example, those who joined or renewed before February 2017 but whose membership end date falls within the time period (filter criteria = "membership end date between 02/01/2017 and 02/23/2017")
    • Members who started their membership during the target dates. For example, those who became a member during February 2017 (filter criteria = "membership start date between 02/01/2017 and 02/23/2017")
    • Members who started before the target dates and ended after the target dates. For example, someone who signed up for an 18 month membership in December 2017 that ends June 2019. This person neither starts nor ends during the time period but is active during the period. What criteria can we use to specify this? Filter criteria = "membership start date before 02/01/2018 and membership end date after 02/28/2018". On the surface, this criteria would appear to work (when combined with the above 2 criteria in an "ANY/OR" rule group). HOWEVER, this last criteria doesn't work, because the system evaluates each membership start date entry independent of the associated end date That is, the system is not looking at Jill Smith's 2018 membership record and looking to see if both the start and end dates on that one entry/record match the criteria. The system is looking at all of Jill Smith's membership records and seeing if any of the start dates match the criteria and also if any of the end dates match the criteria. If so, Jill would be pulled into the report. For example, consider the following:

       

      • Link/entry #1: start date 02/01/2016 - 01/31/2017
      • Ling/Entry #2: start date 03/01/2018 - 02/28/2019

       

      In this scenario, Jill was lapsed/not a member between 01/31/2017 - 03/01/2018. However, she has a start date before 02/01/2018 (Link #1's start date) and she has has an end date after 02/28/2018 (link #2). Therefore, she would be pulled into the report. We can potentially get around this by using the last node field and/or the Max/Min function in our filters to tell the system to only look at the most recent membership record/the last link in the chain.

    • You can see from this example how complicated it can become to write a report identifying active members during a past time period - you would need to include a rule group linking the above rules together with "OR" (a or b or c) and each rule in the rule group needs to include the date filter and the last node field = Y criteria and also use the Max function. If you have more than one membership per person, however, you will still have problems because your members will each have more than one last node.