Hello!I'm feeling a bit defeated over this one and I need the hive mind to help crack it. I'm trying to see how many of our members also give straight up donations, and see how that might have changed over the years, or times of year. I can't think of how to marry the membership info with contribution date. I keep coming back to using something about them giving a donation fund gift within 12 months of a membership gift, but I don't know how to write that, and would that even work if I want to look at current and past fiscal years. Can anyone give me a nudge in the right direction? This could be an interesting thing to monitor and possibly use for picking out people to solicit for upgrades!Jenny
Hi Jenny,
This is such a hard nut to crack. The use cases for "did this happen during an active membership" include contributions not related to a membership, ticket purchases and event/attendance dates, not to mention any Interaction date (e.g. were they an active member at the time of that special activity?). Building a formula for this in Analytics doesn't work because... reasons. I've tried more times than I'd like to admit. Happy to expound but high level, we can't for a membership find contribution dates not associated with that membership without then losing track of the very membership start/expiration dates we need for comparison.
We could take a customization approach, but if when used it's not well tested and used with care, it could lead to inflated figures. Note here a Contributions cube customization wherein we have a widget that filters out all contributions associated with a Membership Level ( = (none) ), and thus all standard Membership fields associated with the remaining contributions show empty value replacements like (none) and 1900-01-01. Then for those remaining contributions, show their contribution dates and the level of any membership for that constituent where the contribution date is between the start and expiration dates.
We could use List Builder, but not without editing the SQL. For example going from this...
To this...
I'm working in v16 though, so not exactly what you'd need. In v15 the contributions would probably need to be filtered on fund in some way to eliminate membership related contributions.
I'm still trying to get that to work in List Builder, but thanks for the tip!
It would be nice to have some kind of count in our Individual Giving manager's dashboard.
How about the next best thing; giving to a membership fund and a donation fund in the same fiscal year? That's totally do-able, right? I've been fumbling with that too.If I have the Campaign Start Date in the rows and the formula indicates the constituent has both funds, this should get me the right number? I tried a different way using widget filters, but that was definitely not the right way.
There are couple of ways of going about this in Analytics. A first thought is to filter a widget on a given fiscal year, and also on fund set to include only the two membership and donation funds. Then with Constituent ID and Constituent Name information on Rows, add a Value [# unique Fund]. Then hover over the Value, click the funnel and filter to only include rows with a value > 1. This will then restrict the included list of constituents to those who gave to both funds in the selected fiscal year.
If you'd rather roll this up to an overall count of such constituents across fiscal years, still set the filter on fund including only the two funds. Then with Campaign Fiscal Year or Contribution Date Fiscal Year on Rows of a widget, add a Multi-Pass Aggregation value grouped by constituent that returns a 1 if they contributed to both funds, otherwise a 0.
SUM ( [Constituent ID] , IF ( [# unique Fund] > 1 , 1 , 0)
This same formula could be used in an Indicator widget filtered to a single fiscal year.