FundSvcs Community

When I did it last I exported one column of giving for each FY then used a formula in excel to count to see who gave multiple times. On Wed, Jan 16, 2019 at 1:34 PM Ellen Mansfield <EllenMansfield@melmark.org> wrote: > A colleague is trying to identify planned giving prospects for our > organization. We use Raiser’s Edge. She wants to identify donors who have > made multiple gifts over the past 10 years, but not necessarily > consecutively. For instance, if she wants to look at donors who have made > 3 or more gifts in the last 10 years, how would she do that? She tried a > variation of the consecutive report and I tried just a query, but neither > worked. > > > > Thank you, > > > > Ellen > > > > Ellen Mansfield, MBA, CFRE > > Annual Giving and Grants Manager > > Melmark > > 2600 Wayland Rd. > > Berwyn, PA 19312 > > 610-325-4953 > > ellenmansfield@melmark.org > > > > > > Confidentiality note:The information, attachment and/or documents > contained in this document transmission may be confidential and/or > privileged and are intended only for the individual or entity named above. > Thank you. > > > > > -- *Derrick Shearer* *Director of Advancement Services* The Pingry School 131 Martinsville Rd. Basking Ridge, NJ 07920 dshearer@pingry.org 908-647-5555 ext. 1265

I would do a query based on the summary field to pull number of gifts (5+? 10+?) and modify the gift criteria to look at the 10+ period of years - THEN use export to pull a column for total $ given in each relevant year AND a column for the total number of gifts in each relevant year. Hope this reads clearly enough. Just remember the Blackbaud mantra that Query is a grouping tool - you need to use Export to get the actual data you want to look at. best regards, Katie Fritz On Wed, Jan 16, 2019 at 1:34 PM Ellen Mansfield <EllenMansfield@melmark.org> wrote: > A colleague is trying to identify planned giving prospects for our > organization. We use Raiser’s Edge. She wants to identify donors who have > made multiple gifts over the past 10 years, but not necessarily > consecutively. For instance, if she wants to look at donors who have made > 3 or more gifts in the last 10 years, how would she do that? She tried a > variation of the consecutive report and I tried just a query, but neither > worked. > > > > Thank you, > > > > Ellen > > > > Ellen Mansfield, MBA, CFRE > > Annual Giving and Grants Manager > > Melmark > > 2600 Wayland Rd. > > Berwyn, PA 19312 > > 610-325-4953 > > ellenmansfield@melmark.org > > > > > > Confidentiality note:The information, attachment and/or documents > contained in this document transmission may be confidential and/or > privileged and are intended only for the individual or entity named above. > Thank you. > > > > > -- *Katherine Fritz, bCRE-ProManager, Development OperationsPennsylvania SPCA*350 E. Erie Avenue | Philadelphia, PA 19134 Phone: 267.463.2313 E-mail: kfritz@pspca.org | Website: www.pspca.org *With a 97% live release rate the Pennsylvania SPCA is the largest no-kill animal welfare organization in the state. * *Make a donation <https://pspca.org/support-us/donate/> today to save a life.*

A colleague is trying to identify planned giving prospects for our organization. We use Raiser’s Edge. She wants to identify donors who have made multiple gifts over the past 10 years, but not necessarily consecutively. For instance, if she wants to look at donors who have made 3 or more gifts in the last 10 years, how would she do that? She tried a variation of the consecutive report and I tried just a query, but neither worked. Thank you, Ellen Ellen Mansfield, MBA, CFRE Annual Giving and Grants Manager Melmark 2600 Wayland Rd. Berwyn, PA 19312 610-325-4953 ellenmansfield@melmark.org<mailto:ellenmansfield@melmark.org> [cid:image001.png@01D4ADA0.2ADD5D30] Confidentiality note:The information, attachment and/or documents contained in this document transmission may be confidential and/or privileged and are intended only for the individual or entity named above. Thank you.

Query on gift summary in constituents - # of gifts and then add the criteria for the years. Chris Adams Christine E. Adams bCRE-Pro Senior Director of Advancement Services University of Hartford Office of Institutional Advancement 200 Bloomfield Avenue West Hartford, CT 06117 P 860.768.2407 C 860.461.6374 F 860.768.2454 E cadams@hartford.edu<mailto:cadams@hartford.edu> [BlackbaudCertified_RE_Professional] [University of Hartford]<http://www.hartford.edu/> This message is intended only for the use of the addressee and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please advise the sender by reply e-mail of the error and then delete this e-mail immediately. Thank you. From: Advancement Services Discussion List [mailto:FUNDSVCS@LISTSERV.FUNDSVCS.ORG] On Behalf Of Shearer, Derrick Sent: January 16, 2019 1:36 PM To: FUNDSVCS@LISTSERV.FUNDSVCS.ORG Subject: Re: [FUNDSVCS] RE report help When I did it last I exported one column of giving for each FY then used a formula in excel to count to see who gave multiple times. On Wed, Jan 16, 2019 at 1:34 PM Ellen Mansfield <EllenMansfield@melmark.org<mailto:EllenMansfield@melmark.org>> wrote: A colleague is trying to identify planned giving prospects for our organization. We use Raiser’s Edge. She wants to identify donors who have made multiple gifts over the past 10 years, but not necessarily consecutively. For instance, if she wants to look at donors who have made 3 or more gifts in the last 10 years, how would she do that? She tried a variation of the consecutive report and I tried just a query, but neither worked. Thank you, Ellen Ellen Mansfield, MBA, CFRE Annual Giving and Grants Manager Melmark 2600 Wayland Rd. Berwyn, PA 19312 610-325-4953 ellenmansfield@melmark.org<mailto:ellenmansfield@melmark.org> [cid:image003.png@01D4ADA0.C69A98A0] Confidentiality note:The information, attachment and/or documents contained in this document transmission may be confidential and/or privileged and are intended only for the individual or entity named above. Thank you. -- Derrick Shearer Director of Advancement Services The Pingry School 131 Martinsville Rd. Basking Ridge, NJ 07920 dshearer@pingry.org<mailto:dshearer@pingry.org> 908-647-5555 ext. 1265

Ellen: I'm afraid that type of query can't be done in Raiser's Edge itself. When I have had to do similar queries for my organization or for clients, I've exported a list of year-by-year giving (using a series of fields of Summary Information, Total Giving, and specifying the fiscal year date range for each needed fiscal year). I've done the real work in Microsoft Excel...for example, for your problem I would use the =COUNTIF formula. Thanks, Greg Get Outlook for Android<https://aka.ms/ghei36> ________________________________ From: Advancement Services Discussion List <FUNDSVCS@LISTSERV.FUNDSVCS.ORG> on behalf of Ellen Mansfield <EllenMansfield@MELMARK.ORG> Sent: Wednesday, January 16, 2019 1:34:14 PM To: FUNDSVCS@LISTSERV.FUNDSVCS.ORG Subject: Re: [FUNDSVCS] RE report help A colleague is trying to identify planned giving prospects for our organization. We use Raiser’s Edge. She wants to identify donors who have made multiple gifts over the past 10 years, but not necessarily consecutively. For instance, if she wants to look at donors who have made 3 or more gifts in the last 10 years, how would she do that? She tried a variation of the consecutive report and I tried just a query, but neither worked. Thank you, Ellen Ellen Mansfield, MBA, CFRE Annual Giving and Grants Manager Melmark 2600 Wayland Rd. Berwyn, PA 19312 610-325-4953 ellenmansfield@melmark.org<mailto:ellenmansfield@melmark.org> [cid:image001.png@01D4ADA0.2ADD5D30] Confidentiality note:The information, attachment and/or documents contained in this document transmission may be confidential and/or privileged and are intended only for the individual or entity named above. Thank you.

This is what I use to identify planned giving prospects. Summary – Total Number of Gifts, greater than or equal to X. Filter on the years you want to look at. Jon W. Garrow Director of Prospect Development Office of Development Montefiore Health System Albert Einstein College of Medicine 718.430.2564 office jon.garrow@einstein.yu.edu From: Advancement Services Discussion List [mailto:FUNDSVCS@LISTSERV.FUNDSVCS.ORG] On Behalf Of Adams, Christine Sent: Wednesday, January 16, 2019 13:39 To: FUNDSVCS@LISTSERV.FUNDSVCS.ORG Subject: Re: [FUNDSVCS] RE report help Query on gift summary in constituents - # of gifts and then add the criteria for the years. Chris Adams Christine E. Adams bCRE-Pro Senior Director of Advancement Services University of Hartford Office of Institutional Advancement 200 Bloomfield Avenue West Hartford, CT 06117 P 860.768.2407 C 860.461.6374 F 860.768.2454 E cadams@hartford.edu<mailto:cadams@hartford.edu> [BlackbaudCertified_RE_Professional] [University of Hartford]<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.hartford.edu%2F&data=02%7C01%7Cjon.garrow%40EINSTEIN.YU.EDU%7C9d899bd6d5cd4143043b08d67be1d5c1%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636832607221064559&sdata=H753bXfpjGmTHefbBdkNhw7VkDCrrqE1WGdatW4qkKA%3D&reserved=0> This message is intended only for the use of the addressee and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please advise the sender by reply e-mail of the error and then delete this e-mail immediately. Thank you. From: Advancement Services Discussion List [mailto:FUNDSVCS@LISTSERV.FUNDSVCS.ORG] On Behalf Of Shearer, Derrick Sent: January 16, 2019 1:36 PM To: FUNDSVCS@LISTSERV.FUNDSVCS.ORG<mailto:FUNDSVCS@LISTSERV.FUNDSVCS.ORG> Subject: Re: [FUNDSVCS] RE report help When I did it last I exported one column of giving for each FY then used a formula in excel to count to see who gave multiple times. On Wed, Jan 16, 2019 at 1:34 PM Ellen Mansfield <EllenMansfield@melmark.org<mailto:EllenMansfield@melmark.org>> wrote: A colleague is trying to identify planned giving prospects for our organization. We use Raiser’s Edge. She wants to identify donors who have made multiple gifts over the past 10 years, but not necessarily consecutively. For instance, if she wants to look at donors who have made 3 or more gifts in the last 10 years, how would she do that? She tried a variation of the consecutive report and I tried just a query, but neither worked. Thank you, Ellen Ellen Mansfield, MBA, CFRE Annual Giving and Grants Manager Melmark 2600 Wayland Rd. Berwyn, PA 19312 610-325-4953 ellenmansfield@melmark.org<mailto:ellenmansfield@melmark.org> [cid:image003.png@01D4ADA2.68FEC020] Confidentiality note:The information, attachment and/or documents contained in this document transmission may be confidential and/or privileged and are intended only for the individual or entity named above. Thank you. -- Derrick Shearer Director of Advancement Services The Pingry School 131 Martinsville Rd. Basking Ridge, NJ 07920 dshearer@pingry.org<mailto:dshearer@pingry.org> 908-647-5555 ext. 1265

Hello Ellen, If you are just looking for a solution that either counts them in or out based on total number of gifts over the period, one option would be to export using a constituent export and utilizing summary information. In this case, you might choose Summary Information/Total Number of Gifts And include gifts with dates that encompass the 10 year period) and apply appropriate filters as indicated by your criteria regarding campaign, funds, appeals, etc. and the gift types you are looking for you should be able to get a list of those who fall into the group. Michael [cid:image001.png@01D18E89.6C83DD60] Michael S. Dotson, Ph.D., bCRE-Pro Senior Director of Advancement Services Office of Advancement AUGUSTA UNIVERSITY 1120 15th Street, FI-1045 Augusta, GA 30912 t: 706-721-0063 f: 706-721-1641 Help make our data even better – send updates to ADVOPS@augusta.edu<mailto:ADVOPS@augusta.edu>. This e-mail and its attachments are confidential, intended only for the named recipient(s) above, and may contain information that is proprietary. If you have received this message in error or are not a named recipient, please notify the sender immediately at the above number and delete the e-mail message from your computer. Thank you. From: Advancement Services Discussion List <FUNDSVCS@LISTSERV.FUNDSVCS.ORG> On Behalf Of Ellen Mansfield Sent: Wednesday, January 16, 2019 1:34 PM To: FUNDSVCS@LISTSERV.FUNDSVCS.ORG Subject: Re: [FUNDSVCS] RE report help A colleague is trying to identify planned giving prospects for our organization. We use Raiser’s Edge. She wants to identify donors who have made multiple gifts over the past 10 years, but not necessarily consecutively. For instance, if she wants to look at donors who have made 3 or more gifts in the last 10 years, how would she do that? She tried a variation of the consecutive report and I tried just a query, but neither worked. Thank you, Ellen Ellen Mansfield, MBA, CFRE Annual Giving and Grants Manager Melmark 2600 Wayland Rd. Berwyn, PA 19312 610-325-4953 ellenmansfield@melmark.org<mailto:ellenmansfield@melmark.org> [cid:image001.png@01D4ADAF.0E181B90] Confidentiality note:The information, attachment and/or documents contained in this document transmission may be confidential and/or privileged and are intended only for the individual or entity named above. Thank you.

Hi Ellen, Here is a simple SQL query that will give your colleague the data she needs from RE.  If you need help implementing the script please feel free to contact me.

-Shawn

RE. Sample Output:

Gift Count	CONSTITUENT_ID	FULL_NAME
3	12485	Shawn Steele
5	16542	Dr. Robert Henderson
8	14582	Mrs. Mary Warner
8	10522	Mr. Mark Daniel Adamson, III

[ BEGIN SQL SCRIPT ]

with TotalGiftCounts as ( Select count(*) GiftCount, R.CONSTITUENT_ID from Gift G inner join Records R on G.CONSTIT_ID = R.ID where G.DTE > '12/31/2015' --and R.CONSTITUENT_ID = '257' group by R.CONSTITUENT_ID ) --Select * from TotalGiftCounts Select T.*,ISNULL(R.FULL_NAME,R.FIRST_NAME + ' ' + R.LAST_NAME) AS FULL_NAME From TotalGiftCounts T Inner join Records R on T.CONSTITUENT_ID = R.CONSTITUENT_ID Where Cast(GiftCount as int) > 2

[ END SQL SCRIPT ]

gift receipting made easy with ReceipterPro!

Shawn Steele
shawn@donortek.com
828-581-0225 - Office
860-459-2468 - Mobile
www.donortek.com