Giving in 20 years

shani traum:

So, I have a request for a report, and I think I know the answer to this question, but said I would ask the larger community for their advice.


I'm being asked for a query of donors who have given gifts in 20 distinct years.


Not necessarily in the last 20 years.


Not 20 consecutive years of giving.


A gift or gifts in some combination of years that results in giving in 20 years.


The only way I can think of doing this is summary giving for each year since 19XX.  Exporting the results to EXCEL and then using a formula to count for each donor how many years have gifts made and then sorting the results to find those with giving in 20 unique years. Once in Excel it's easy.  But first I'd have to build an export with 30+ summary giving calculations for the individual and 30 + for the spouse.


I do not want to do this.


Any other ideas?


Thanks!


Shani

 

Check out Larry's suggestion, can it handle 20 columns? If not, yes, you're looking at an Export with multiple fields to grab info from every year. I suggest setting up your export with 4-5 years, take the data to Excel and review to make sure you've got all the criteria you need correct like gift type, fund, gift date, etc. I've had to go in and edit each output when one criteria change was needed. It helps to get it right from the beginning.

A pivot table (or pivot report) might be helpful here. Export all your gifts individually, no need for summaries in RE, use the list to make a pivot table with one row per constituent and group your columns by gift year, then see who's got something in at least 20 different columns.

shani traum:

So, I have a request for a report, and I think I know the answer to this question, but said I would ask the larger community for their advice.


I'm being asked for a query of donors who have given gifts in 20 distinct years.


Not necessarily in the last 20 years.


Not 20 consecutive years of giving.


A gift or gifts in some combination of years that results in giving in 20 years.


The only way I can think of doing this is summary giving for each year since 19XX.  Exporting the results to EXCEL and then using a formula to count for each donor how many years have gifts made and then sorting the results to find those with giving in 20 unique years. Once in Excel it's easy.  But first I'd have to build an export with 30+ summary giving calculations for the individual and 30 + for the spouse.


I do not want to do this.


Any other ideas?


Thanks!


Shani

 

Yep - sadly that is how you do it.  But hey! once you have set it up, it's there. 

Great job! I think I would have fainted

shani traum:

 

/So, to share what I did…

 

Ryan Hyde:

Melissa Graves:

I was just informed that I may need to be doing a similar project to this and I hope pivots will get me there. I need to find anyone in the database who has ever given 5 consecutive years (no matter when). Wish me luck.

I often wonder if the people who ask us to come up with this sort of thing have any actual notion of how painful it is to do this kind of data sifting. Ugh! 


I wish you the very best of luck. 

 

To anyone who has ever had to pull a list for a colleague….


I would like a mailing list in spreadsheet format. It should be pretty easy to run this list out of the database. Really, a very simple query should do it. 


I need donors from Canada, but not all donors. 

They should be friends with Robertson Davies, or maybe just Brian Adams. 


I'll need every donor who's given more than one dollar, but since that's Canadian Dollars, it should be more than $.978USD, and up to $500 CAD, or $462.72USD. You can easily calculate this figure by cross-referencing it with South African donors with Canadian bank accounts who wire transfer their donation in Rands to the Canadian banks, deducting the transfer fee and exchange rates, and then the cross-national re-transfer and conversion needed when the money comes to our US account for Foundation but not USA or NEXT (all of this should be reflected in the spreadsheet). 


Also, anyone who ever gave, extrapolating for anyone who might give in the future, factoring in potential future gifts from yet-to-be-born children of Alumni who are as yet unmarried or not even dating anyone, but it seems likely that they sort of have a thing for this other Alum whom they might in fact meet at a future office party or Monologue event. (I think you can get that through Flags.)


Please exclude: Board contacts, Steward contacts, Solicitor contacts, Solicitor or event-list contacts entered through Sub-Sol., Contacts, Notes, First Name, Professional Title, and anyone who gave in 2007 even if they have expressed interest in sponsoring an event in 2008.


You might as well exclude any of the following: former Anarchists, current microwave enthusiasts, Met fans, people who play Nintendo Wii, people who aren't coming to the Office Warming party, anyone who is friends with anyone who was ever associated with us in any way, and just email them. 


But don't make the email long or include graphics or a web address or 800 number in these emails. 


Also, I want to review every name on the mailing list and the email lists and have you cross off people because I remembered to forget to remember that I saw them at the dry cleaner's in 2001 and they might have been friends with one Board member who is now deceased but they had said something about not liking to open a lot of mail but never said don't mail me; I just have a feeling about it, and I have 17 other spreadsheets to compare this feeling or inkling or notion to, so I'll have to have you look at those before you send out the list to the printer or send the email and I'll need to review and make changes at that point anyway. 


Thanks. 


I'll need this in an hour, it's urgent. 

So happy to stumble across this just as I started building a very complicated export!  Jen Claudy‍ so fun to read your story!  I am taking these tips and making my life at work easier tomorrow and getting this thing done!

shani traum:

So, to share what I did…

As my criteria was donors who gave in the last 3 years and have given in 20 years over time, I built a query of donors whose last gift was within the last 3 years and whose first gift was at least 20 years ago.

Exported those IDs out to EXCEL.

Built a gift query with gifts of the types I wanted (gifts and pledges only, not pledge payments) AND with constit ID one of (and then pasted in the IDs from the earlier export).  The end results looked something like

(ID one of… OR  ID one of… OR ID one of)… AND Gift Type one of…

I then loaded that into a Gift Export that had Constit ID and Gift Date as the exported fields.

In EXCEL, per my colleagues suggestion, I added columns for each Fiscal Year needed (mine went to 1953).

I sorted the exported info my gift date and then put an x in the corresponding FY column (so all gifts 10/1/17-9/30/18 had an X in the FY18 column, 10/1/16 – 9/30/17 had an x in the FY 17 Column, etc (not as annoying as it sounds).

Then I built the pivot table putting ID number as the row marker and each of the FY column names in the SUM section.

Then I coped the pivot table to a new tab, pasting as values only.

I added a final column to the results which was an =Count formula.  This counted how many FY years had a value appear for that constit. It wouldn’t total how many gifts, but how many years gifts were made.

And then I sorted by that last column and voila – my list!

Not as evil as I feared, once I knew what I was doing!

Good luck!

Shani