Querying donors who are new *only to a specific fund*

Erin Macleod

Hi all,


I'm fairly new to my organisation and therefore to RE (~2 months), so I'm wondering if someone has encountered this before at their org.

Situation: I'm reporting on a recent campaign that involved 3 funds, and I want to discover how many constituents gave to one of those 3 funds for the first time - for example, those who have never given to the 2019 Membership fund until this campaign, disregarding whether they gave to any years of Philanthropy fund or any years of the Building fund in the past/during this campaign. I want to figure out what it is about the recent campaign that made them decide to start giving to the Membership fund - it doesn't matter to me whether they've already given to other funds in the past, just whether they are giving to the Membership fund for the first time.
Dilemma: I can't figure out a way to filter out those who gave to past years of the Membership fund, without filtering out those who previously gave to the other funds as well. 


After thinking about it, my best guess was to query constituents on this basis:

> any gifts with the Membership campaign appeal ID

> AND (gift date less than the campaign start date

> AND "not one of" the previous years' Membership funds)

...but between the gift-date-less-than and the weird effect of exclusions in queries, I'm not getting usable results. 


Has anyone ever needed to report on constituents' history within a specific fund without touching any other funds' history? It doesn't have to be through Query specifically. Any tips appreciated!


Thanks,

EM

Kylea Johnson

I'd start with a query of the donors who gave to the funds in the time frame you're interested in. Since you want to look at giving history the best way to do that is through a constituent export. Set up criteria for the export fields so you just export the information from the fund you're interested in. You can run a pivot report to look more closely at fund giving history. = )


Or you could make a really cool crystal report but that's not everyone's cup of tea.


Let me know if that helps! If not I'm glad to brainstorm some more.

Alan French

I would use the 'summary information' fields at the bottom of a constituent query. You'd need to create two different criteria, for constituents who gave a total of $0 to the Membership fund before the start of the campaign (i.e. hadn't donated previously) AND have given a total of more than $0 to the Membership fund after the start of the campaign.

Erin Macleod

Alan French‍ ah, the summary info! I remember thinking it would be handy in an export, and didn't think to look at it in queries as well. Setting my query to include a gift summary of "total number of gifts = 0" and specifying any previous gift dates to the Membership fund has done the trick to get numbers (and helps me use other parts of the query more efficiently). Thanks so much!

Kylea Johnson‍ I will need to do further analysis on interaction with specific funds in future, so this is very helpful! I'm also super curious about the crystal reports - keep seeing them pop up on the forums. Anyplace in particular you'd recommend to start learning about them?

Kylea Johnson

I always forget about summary info.

Erin Macleod‍ I started with http://bekerman.com/blog/crystal-reports-track/ and supplemented with a LOT of google searches.