Giving History for "PURE" Donors

Options
Lauren Fardella
Lauren Fardella ✭
Ancient Membership Facilitator 2 Name Dropper Photogenic
in Blackbaud Raiser's Edge NXT®: General Discussion
Hi,

I have been asked to pull a report of donors that ONLY gave to one fund and that one fund only and then create a giving history report

I thought I had it, but then when I ran the giving history some of the donors had other gifts not for that specific fund.  


I think I may be skipping a step or something in my criteria.


Any help would be appreciated :) 
Tagged:

Comments

  • Tim Hendrickson
    Tim Hendrickson ✭
    Tenth Anniversary Facilitator 1 Photogenic
    I came up with a somewhat roundabout way of doing what you need, but it seems to work.


    1.  Create a gift query with a minimum output containing Constituent Import ID and Fund ID.

    2.  Export this to Excel.

    3.  If you have any Fund IDs that should be lumped together (like same funds that have a year attached to them), you may want to clean those up in this file.

    4.  Create a Pivot Table with columns being the Fund ID and Rows being the Constituent Import ID.  Values will be Count of Fund ID, shown by "% of Row Total."

    5.  Select the whole pivot table and copy it.  Paste it as "Values only" into a new tab.

    6.  Change the Grand Total column to Max and the formula for the entire column to =MAX(RANGE containing all columns).

    7.  Turn on Filtering for the Max column and filter out anything that's less than 1.  Any rows remaining will only have single-fund contributors.  If you're looking for only those contributing to a specific fund at that point, you should be able to then filter on any of the Fund columns.

    8.  If you select the Constituent Import IDs and copy them into a new query, you should be able to run them through any kind of report you want.
  • Lauren Fardella
    Lauren Fardella ✭
    Ancient Membership Facilitator 2 Name Dropper Photogenic

    Tim Hendrickson:

    I came up with a somewhat roundabout way of doing what you need, but it seems to work.


    1.  Create a gift query with a minimum output containing Constituent Import ID and Fund ID.

    2.  Export this to Excel.

    3.  If you have any Fund IDs that should be lumped together (like same funds that have a year attached to them), you may want to clean those up in this file.

    4.  Create a Pivot Table with columns being the Fund ID and Rows being the Constituent Import ID.  Values will be Count of Fund ID, shown by "% of Row Total."

    5.  Select the whole pivot table and copy it.  Paste it as "Values only" into a new tab.

    6.  Change the Grand Total column to Max and the formula for the entire column to =MAX(RANGE containing all columns).

    7.  Turn on Filtering for the Max column and filter out anything that's less than 1.  Any rows remaining will only have single-fund contributors.  If you're looking for only those contributing to a specific fund at that point, you should be able to then filter on any of the Fund columns.

    8.  If you select the Constituent Import IDs and copy them into a new query, you should be able to run them through any kind of report you want.

    You lost me at Pivot Tables ;)   There has to be an easier way.  Anything in NXT that would help?

  • Aaron Rothberg
    Aaron Rothberg ✭
    Eighth Anniversary Facilitator 2 Name Dropper Photogenic
    Hi Lauren-


    My answer is a bit overkill, but I think I would create a Constituent query looking for anyone that gave a gift to the Interesting Fund within the time period and gift types you specify. I would then output each Constituent Id with two giving summary columns. My first summary column would contain only gifts toward the Interesting Fund. My second summary column would contain all gifts to all funds except the Interesting Fund. Sort by the two columns and delete anyone that has a positive value in the Interesting Fund column and a positive value in the all other funds column.


    Like I said it's a bit overkill, but it's quick and easy.


     
  • Lauren Fardella
    Lauren Fardella ✭
    Ancient Membership Facilitator 2 Name Dropper Photogenic
    Aaron Rothberg‍ That may be a better way...why is nothing easy :) 
  • Tom Klimchak
    Tom Klimchak ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic
    I'm thinking you can do this with a simple constituent query.  The two criteria:

    Total Number of Gifts >= 1 (where fund = FUND1)

    AND

    Total Number of Gifts <=0 (where Fund <> FUND1)


    Basically, find all the people who have given once or more to FUND1... and then from that group find the people who have ZERO gifts to everything else.


    Feel free to flower it up with other criteria.  Seems to work in my quick testing...

     
  • Tim Hendrickson
    Tim Hendrickson ✭
    Tenth Anniversary Facilitator 1 Photogenic
    Alternately you can use merged queries to filter out everything that isn't the one fund you need.


    1.  Create a constituent query of gifts that include only the fund you need.

    2.  Create a second constituent query of gifts that include all funds except that fund.

    3.  Run Merge Queries on these two queries using the SUB merge operator.


    If you need to run this to analyze multiple funds, I'd still use the other method.  Initially I wasn't sure if you needed this to identify donors just to just a single specific fund or to identify donors who had only given to any single fund you may have.  For me, using the previous method, I was able to distill my 600+ funds down to 200, then see how many people were single-fund donors for each of those 200 funds.

Categories