Hi all,
I've just discovered this group and I think it's going to be very useful! I'm very new to Analytics and I don't really have a data-driven mind so it's going to take me a while to get used to things!
Apologies if this is a v basic question, or if it's been asked before, but I'm trying to create a dashboard that will show me the number of members of a specific level each year to give a comparison to track the peaks & troughs of membership each year. I have this as an excel spreadsheet but it would be much better on Analytics. I've had a look in the Memberships folder of the Tessitura dashboard but that focuses on contributions made for the memberships rather than the number of members.
Ideally it would bring up results a little bit like this:
Thanks in advance!
Erica
I'm new to Analytics too and was looking for this - thanks Chris! We're also wanted to keep track of how many Members we had at each level year after year. Is there a way to add this as a widget as well?
Hi Emily,
Very glad the above was helpful. For YoY, are you thinking a year is the year in which their membership started, their membership expired, or their membership was at all active? Another way, if I have a membership that started on 1-Jun-2020 and expires on 30-Apr-2021, would you want to see me counted in a 2020 membership started bucket, in a 2021 membership expiration bucket, or in both a 2020 and a 2021 active members bucket?
I will tag in here as I have been trying to create something similar to what Emily has asked about and only been moderately successful- for PCT this could be the Membership Campaign it was assigned to (which is seperate from the Campaign the contributions might be booked to. Where it become a little extra complicated is that we might have a gift in December 2019, the membership wouldn't start until Jan 2020, but we would consider the membership part of our 2019 renewals. I currently work with inexact numbers and remind everyone we are looking at trend analysis and use a combination of membership and contribution widgets.
Ideally, we are looking to report number of active memberships by month, for example how many of each level were active at all in October 2019. But to start, YoY where their membership was active at all.
Tessitura Analytics doesn't have what I'll call a "Membership Active On Date" where you can use that field to filter to memberships that had an initiation date before and an expiration date after the selected date or date range. So in lieu of that, we can filter on initiation dates before and an expiration dates after the period we want to report on. In this first case, YoY.
We'll need a value per period (year), with a filtered value formula as shown in the "2020" example above. This is the total count of memberships, filtered to those with a membership expiration date after Jan 1, 2020, and filtered to those with an initiation date before Dec 31, 2020. I then duplicated the value from its Options menu, and edited the formula's filters for the other 2 years.
We can easily render this as a Column chart by changing the cart type on the Pivot widget.
When I've seen this done for a Monthly analysis, the same process is followed, but with a value formula per month, and expiration/initiation dates set to the before the end / after the start of that month, all within a given fiscal year.
Hi all,I, too, am new to Analytics and am also having trouble with this same issue. When I’ve tried using the formula suggested ([Total Member Count], [Expiration Date After 1/1/2020], [Initiation Date Before 12/31/2020]), I’m still having trouble and end up with an inaccurate count. Can anyone share a sample dash of this if you have gotten it to work? Or what exactly is the code for the Days to Date filter. Thank you!
Hi Sean,
To what are you comparing to determine inaccurate counts? If between Analytics and your comparison you can find an out of place membership, look at the initiation and expiration dates and see if they're what you'd expect the filter to catch.
Here is the detail from the Value formula filters in the Pivot table above:
HI Chris Wallingford -- I'm trying to set up a widget like the one above for number of memberships year over year as a column chart and I'm following your instructions above but I'm stuck. I'm trying to add the value for the year, but when I type in the formula in your image I get an error message and I can't seem to figure out how to set it up using the editor. Do you still have this widget? If so, would it be possible to get a copy of it? Thanks!
Hi Anne,
This is the one updated with the YOY in Level ... MembershipTrends.dash
Thank you!
I'm afraid I'm totally stumped. I'm getting different counts in different widgets and when I pull lists of members. For example, when I pull a list of active and pending Merieult members, I get 308, but my Level widget is showing 356. Is it possible that one is counting constituents and the other is counting memberships? (i.e. it is including duplicate constituents) And my Members by Level this FY and last widget is totally off. The dashboard you sent seems to be working but it is set up by calendar year rather than FY so I can't be totally sure since everything else is set up for FY. Thank you!
3302.Anne'sMembershipDashboard.dash
You can absolutely change the dates on that widget in the value filters to align with your fiscal years instead of calendar years.
Given that the values are counts of memberships with expiration after the start of the year AND initiation prior to the end of the year, I would very much expect, once adjusted to FY, for the values in this widget to NOT match any of the other widgets that are based on the FY of the Purchase Date.
It is difficult in a forum thread to dig into the details of the different widgets' filters and your list criteria. I suggest a Support ticket to address matching values. I can say that the Level widget is 4 FYs of memberships based on the Dashboard filter for Purchase Fiscal Current Year Offset in 0, -1, -2, and -3, and is filtered to exclude memberships with a Current Status in Deactivated or Inactive, while the Members by Level This FY and Last widget, is limited to only this and last FY, but not includes Deactivated and Inactive memberships.
Could I have a copy of the dash file to try this out? We would also like to calulate number of members for a given year
Chris Wallingford - The link to the sample dash doesn't seem to work - it says I do not have permissions to view it. Can you please repost it?
Carol Keeney, Marliese Schultz, links updated above, and some of the images... also direct link to dashboard here: MembershipTrends.dash
Thanks!