Top 5 Donors for Each of the Specified Funds

Aleksandra Marek

Hi there,

I'm trying to figure out how to create a query or export query) which shows me 10 specified funds and the top 5 donors for each of these funds and their respective accumulated gift amounts. I have been using the Analytical Reports function to create the Top Donor Report but that only alows me to list top 5 donors for only one fund (or collection of funds) while I want a page for each of the funds listing top 5 donors. The other option where I can get this information is by looking up the fund summary information record. However I need the information in a query format so that we can use this to link to our website through NetCommunity. Idealy the query will have the fund codes in the left hand column with Top Donor 1 name..Top Donor 2 name...etc, in the colums to the right of the fund codes. Can anyone help me to tell me what is the best way of generating such query?????? Your help will be much appreciated.

Gina Gerhard

Aleksandra -  For terminology, I think you're looking for a report or export for your results and not a query.  Your 'query' is for your specific set of funds.  Then you want to know who the top 5 donors are to those funds, so that's your export/report function.

• As you have found, the Top Donor Report will give you what you are asking for but just one fund at a time.  
  • I don't think there's a 'canned' function that will allow you to request a specific query of funds and then give you the results for each fund on a separate page.  
  • You would also have to be careful to specify the soft credit setting and the gift types in the Gifts tab to make sure you're pulling in exactly which gift types you want to consider.  
• How do you intend to show this data in NetCommunity?
  • If you create an output query from your top donor report, you could use this query in a constituent directory on BBNC but you'd need to create a separate directory for each fund I think.....

Aleksandra Ewing

Thanks for the feedback. I have created a Fund query to get some of these parametre's and then have attempted to use it in an export to get the Top 5 Donors. However if it's a Fund query it doesn't allow me to choose the top donors. Am I using the wrong query type? Or am I missing something when it comes to generating the export? Your help would be much appreciated. Thank you. 

Gina Gerhard

Aleksandra -  This might work:



Step 1: Create constituent query to find all donors to your 10 funds (with whatever other criteria you want, like dates)



Step 2: Create constituent export

• Include:  your constituent query from Step 1
• In Output:  
  • constituent ID, constituent name
  • Summary information
    • Gifts - in gift criteria window, gifts - put in any data or other parameters; in filters - choose 1st of your 10 funds
  • export out gift amount (this will give you constituent's total giving to that fund)
  • Go back up to summary information and pull over Gifts again
    • Gifts - do same as above, but choose 2nd of your 10 funds
  • repeat for the rest of your funds

This will give you each constituent's total giving to each fund, in columns going across.

Export to Excel, and then you can use sort to get your top donors for fund 1; you'd have to redo the sort for each fund.



I think that will give you what you want with the least amount of work? It will take you some time to set up the columns for each 10 funds, but once it's saved you can use again.  



Is this close to what you're trying to do?

 

John Heizer

Aleksandra Ewing:

Thanks for the feedback. I have created a Fund query to get some of these parametre's and then have attempted to use it in an export to get the Top 5 Donors. However if it's a Fund query it doesn't allow me to choose the top donors. Am I using the wrong query type? Or am I missing something when it comes to generating the export? Your help would be much appreciated. Thank you. 

A Fund Query just returns a list of Funds that match the criteria you give.  It looks like you want a list of people (Constituents) not a list of Funds.

John Heizer

Creating a Query like this for one fund isn't terribly difficult.  Getting one Query of top donors for multiple Funds broken out by Fund isn't possible in a single Query with the "normal" Query interface.  It might be possible using a complex SQL query using RODBA and multiple subqueries, but I think going that route would be more complex/expensive than it would be worth.

Aleksandra Marek

Thanks Gina for your advice. I have found it really helpful. It makes a lot of sense. I will try how it works and see if this will be what my boss is looking for. But I'm assuming that it is from just your suggested process. Thanks heaps.