$ lost from one year to another

Options
I would like to calculate how much $ our organization has lost from FY14 - FY15 in specific denominations (i.e. $25-49.99, $50-99.99, etc).  When i created a gift query for FY14 using this criteria and then a another gift query for FY15 using the same criteria and ran it through the FY14 query I got $0, which is impossible.  Am I missing something?  Is there a much easier way to go about doing this? 
Tagged:

Comments

  • Do you mean that you created queries for gifts in each financial year and then used a merge query to subtract one from the other? If so, this is correctly returning zero (0 gifts, not $0) as the merge query is not subtracting the dollar amounts but rather it is removing one group of gifts from another group of gifts, and no single gift can fall into both financial years.
  • Jesse -


    I think this might work:
    • For your FY 2014 gifts in that range - create gift export to show number of gifts and total amount.
    • For your FY 2015 gifts in that range - create gift export to show number of gifts and total amount
    Then just compare the results from your two exports
    • You'll see the difference in total number of gifts in that range from FY 2014 and FY 2015
    • And the difference in total amount of giving for that gift range in both as well.
    If you actually want to see the constituents who are making these gifts and the change in both years, that's more involved.

     
  • Hi Jesse!


    One easy way we do do this is we use the Association of Fundraising Professionals' (AFP) Growth in Giving Report! All of the information on how to run it is here: ​http://www.afpnet.org//Audiences/ReportsResearchDetail.cfm?ItemNumber=5181


    #4 at that link has directions for filling out #2. You can either do the export as they suggest (that's what we do, since we retain some control over whats being included) OR if you want really quick numbers you can use the Fundraising Effectiveness Project plug in in RE. 


    I'm sure what the others suggested works as well... and this was the easiest & simplest way we found. 
  • Gina Gerhard:

    Jesse -


    I think this might work:

    • For your FY 2014 gifts in that range - create gift export to show number of gifts and total amount.
    • For your FY 2015 gifts in that range - create gift export to show number of gifts and total amount
    Then just compare the results from your two exports
    • You'll see the difference in total number of gifts in that range from FY 2014 and FY 2015
    • And the difference in total amount of giving for that gift range in both as well.
    If you actually want to see the constituents who are making these gifts and the change in both years, that's more involved.

     

     

    can you elaborate?  i have total amounts for each range but if i simply subtract them isn't that not the answer to how much $ was lost?

  • Maybe I'm not understanding your question correctly, but let's say:


    In FY 2014 you got 120 gifts within the range of $25-49 and they all totalled $3,600

    In FY 2015 you got 100 gifts within that range and they all totalled $3,000.


    So isn't that a reduction of 20 gifts and a 'loss' of $600 of revenue in that gift range?  
  • Gina Gerhard:

    Maybe I'm not understanding your question correctly, but let's say:


    In FY 2014 you got 120 gifts within the range of $25-49 and they all totalled $3,600

    In FY 2015 you got 100 gifts within that range and they all totalled $3,000.


    So isn't that a reduction of 20 gifts and a 'loss' of $600 of revenue in that gift range?  

    no because some of those people might have given in FY14 but not in FY15 or vice versa and that is not Retention Rate/$.  I did a query for FY14 and a query for FY15 (ran through FY14 query) to see # retained and calculate %, but i could not do the same for $

  • So that's very different.  Because a retention rate is based on the individual constituent -- 


    I think that's in Reports - use the Analytical Report titled New Comparative Report.
    • You can use a query of anyone who donated in your gift range for either of the two years.
    • Then in the report, you can define the two years (and there other filters available as well).
    • The report will show you the constituent, total given in Year 1, Year 2, Currency variance (plus or minus $$) and the % variance.
    • At the end, it will show you totals and a count of how many constituents increased and how many decreased.
    So that might do the trick?
  • Jesse Calagna:

    I would like to calculate how much $ our organization has lost from FY14 - FY15 in specific denominations (i.e. $25-49.99, $50-99.99, etc).  When i created a gift query for FY14 using this criteria and then a another gift query for FY15 using the same criteria and ran it through the FY14 query I got $0, which is impossible.  Am I missing something?  Is there a much easier way to go about doing this? 

    In my last position as Annual Fund Officer, I created a rather complex process for measuring this, not only at year-end, but tracking the variance as "late" gifts and "early" gifts. This shed light on whether we we artificially ahead of PY (a large gift in 2 months earlier than usual, for instance) or not. I also tracked SYBUNT in this model (for the three previous years).


    The basic process was this.


    I created a query of all Annual Fund gifts (this was for my purposes to remove endowment giving, etc) for the CY and the 4 PYs with Campaign is one of AF2016, AF 2015, AF 2014, AF 2013, AF 2012.


    I created an Export where I exported total gifts for CY, PY, SY1, SY2, SY3 (if you truly only want to look at CY v. PY, then the Query and the Export only needs CY and PY). Each colmn needed to have the appropriate dates and filters to get the correct gifts flowing into them.


    I opened the export in Excel and built Pivot Tables with the appropriate filters to meet the groups I was looking for -- donors who already gave YTD both PY and CY ("Same"), Donors who have given YTD CY and gave PY, but later in the fiscal year ("Early") , Donors who gave YTP PY but haven't given in the CY ("late"), New Donors in CY, and CY donors who were a SYBUNT1, 2, or 3. I was able to see this in table form for each constituency as well (Trustees, Alumni, Parents, etc)


    All of this is a lot more complex than I think you are asking for, but it helped us know who to make sure we were reaching out to (the "late"). It is also a lot of work to set up the first tiem, but after that, it is a lot easier (you can paste new exports into the old Excel files to avoid rebuilding the Pivot tables from scrath (you still ahve to refresh the data to get the new gifts to be calculated).


    For the "lost dollars" I manually added a column calculating CY column minus PY column. Sorting that column then makes it easy to count the number of bigger gifts, smaller gifts, and same gifts. At the Cleveland Institute of Music during my tenure there, each was roughly a third, but thankfully the increases always topped the decreases (and the decreases often were due to death (which can also be controlled for if you export "Deceased" and include it in the Pivot Table filters).


    I admit what I did is a lot more work than needed if no one is going to do more of a deep dive into the information (actually following up with people who end up in differnt groups) and FOLLOW UP. But as a nerd, it was fun to create it. It took me several iterations and months to get it to where it was turn-key becasue I was learnign things as I went along. The FEP does all of this SO MUCH more quickly, but since everythign is tied to constituent ID, it is anonymous and not really easily actionable at the micro-level,


    Hope this makes some sense to someone.


    John
  • Jesse Calagna:

    I would like to calculate how much $ our organization has lost from FY14 - FY15 in specific denominations (i.e. $25-49.99, $50-99.99, etc).  When i created a gift query for FY14 using this criteria and then a another gift query for FY15 using the same criteria and ran it through the FY14 query I got $0, which is impossible.  Am I missing something?  Is there a much easier way to go about doing this? 

    So.... do you want to create something like this:


     
    FY14 FY15 Change 10 15 +50% 30 15 -50% 100 50 -50%
    Category
    $250+
    $100-$249
    $1-$99
    We actually just did that for the past 10 fiscal years. We were able to use an Export and then do the counting/summary for each category and year in Excel.  This just looks at all the donors in each fiscal year, puts them into a category, and then counts the number of donors in each category.  It really doesn't consider retention from one year to the next at all.


    Is that what you're looking to create?

Categories