Report Needed - Help!

Beth Muckler

Hi everyone - Hoping someone can help with this! And my brain is a little fried so I could be missing something totally obvious, but...


My boss wants a monthly report now showing acquired donors, returning donors and recaptured donors by month. For example, let's say last month we had a total of 1,000 donors. She wants me to report how many of those were new donors (easy), how many were returning donors and how many were recaptured.


For the life of me, I can't figure out how to easily run a report on the returning and recaptured. Even in NXT, I can't seem to narrow it down to the month, only the year over year, and this is driving me nuts. I can't imagine that I would actually have to go in and look at every gift.


It would be wonderful if we had a way to run a query on next-to-last gift instead of just last gift.


Any ideas or suggestions?


Thanks!

Aaron Rothberg

Hi Beth-


If I were tasked with this I would use an Export, create Gift Summaries for each month, and then calculate your acquired, retained, and recaptured donors in Excel.



 

Melissa S Graves

You are not alone. Maybe 15 years ago now I asked BB for this and they quoted me a crazy amount for a custom report - which I did not pay for. Then not long after they added the Donor Giving Analysis Dashboard which looked suspiciously like what I asked them to design - but it NEVER works on a month to month basis. Only year over year.


As far as I know you will have to do this manually for now the way Aaron describes. We only got NXT yesterday so I have not looked into whether or not this is possible but I believe you when you say it is not.


Maybe you can talk them into asking for this only quarterly, which is what I currently do.

Cathleen Mai

So if they gave the month before and give again this month you want them to show as returning, if  they skipped one month and then give they are recaptured, correct?

Joshua Bekerman bCLO bCRE

Depending on your definition of returning and recaptured you could create a constituent export with these fields:

1. Last Gift
   • gift date range of last month 
2. First Gift (include all dates)
3. Last Gift
   • gift date range with a To date = the last day of the month, two months ago (June if running for July gifts)

Then have Excel (or Crystal Reports) calculate the following:

• New Donors =
  • Last Gift (#1 above) is equal to First Gift
• Returning donors=
  • Last Gift (#3 above) is between whatever date range depending on your definition of "returning."  For example, if they are returning if they gave in the past year, then this Excel formula would be If the last gift (#3) is between 8/1/17 and 7/31/18.
• Recaptured=
  • If Last Gift (#3) is less than 8/1/17 (assuming recaptured = over a year since they last gave)

Or something like this.  Haven't had a chance to test it but I think this logic is logical.   

 

Melissa S Graves

No you are not really wanting to look at month over month - else the only retained donors you would ever have are your monthly donors and you already know who they are. Really what you want is:


Looking at this month's list of donors:

• How many are completely new (new)
• How many gave last fiscal year and then also made the gift this month (retained)
• How many gave previous to last fiscal year but not last fiscal year and then also made the gift this month (recaptured)

Ideally you may also want to look at upgrade/downgrades/same within retained and recaptured but that is a bonus if you can get the first 3!!


The donor giving analysis dashboard has all of these categories and it works if you put in a date range equal to a complete fiscal (or calendar) year. But the instant you put in a date range of a month - it changes the comparison to compare month over month instead of continuing to compare this month's gift to their giving in the previous fiscal years. No one does that. Why was the report designed this way?


This happens when at the end of the FY you do this awesome analysis and give them all this data based on the year. Then each month they want to now see how they are tracking. But you can't in this "awesomely" designed donor giving analysis. Even if you minimally could use a constituent query in the report and only have it show this month's donors - then the date range can remain the full FY. The problem is the date range you enter is twofold - picking the constituents it includes AND determining the dates to compare. That is just dumb.


EDITED - kept calling it the donor category report but I meant the donor giving analysis

Melissa S Graves

Melissa Graves:

No you are not really wanting to look at month over month - else the only retained donors you would ever have are your monthly donors and you already know who they are. Really what you want is:


Looking at this month's list of donors:

• How many are completely new (new)
• How many gave last fiscal year and then also made the gift this month (retained)
• How many gave previous to last fiscal year but not last fiscal year and then also made the gift this month (recaptured)

Ideally you may also want to look at upgrade/downgrades/same within retained and recaptured but that is a bonus if you can get the first 3!!


The donor giving analysis dashboard has all of these categories and it works if you put in a date range equal to a complete fiscal (or calendar) year. But the instant you put in a date range of a month - it changes the comparison to compare month over month instead of continuing to compare this month's gift to their giving in the previous fiscal years. No one does that. Why was the report designed this way?


This happens when at the end of the FY you do this awesome analysis and give them all this data based on the year. Then each month they want to now see how they are tracking. But you can't in this "awesomely" designed donor giving analysis. Even if you minimally could use a constituent query in the report and only have it show this month's donors - then the date range can remain the full FY. The problem is the date range you enter is twofold - picking the constituents it includes AND determining the dates to compare. That is just dumb.


EDITED - kept calling it the donor category report but I meant the donor giving analysis

 

WAIT - OMG I think I just solved this. YOU CAN put in a constituent query! Hang on for an update...

Beth Muckler

Cathleen Mai:
So if they gave the month before and give again this month you want them to show as returning, if  they skipped one month and then give they are recaptured, correct?

Not quite, no. If 1,000 constituents donated in July, I want to know how many of those 1,000 are brand new, how many are returning donors (have given in the last 12 months) or recaptured donors (have given, but not in the last year). 

Beth Muckler

Joshua Bekerman bCLO bCRE:

Depending on your definition of returning and recaptured you could create a constituent export with these fields:

1. Last Gift
   • gift date range of last month 
2. First Gift (include all dates)
3. Last Gift
   • gift date range with a To date = the last day of the month, two months ago (June if running for July gifts)

Then have Excel (or Crystal Reports) calculate the following:

• New Donors =
  • Last Gift (#1 above) is equal to First Gift
• Returning donors=
  • Last Gift (#3 above) is between whatever date range depending on your definition of "returning."  For example, if they are returning if they gave in the past year, then this Excel formula would be If the last gift (#3) is between 8/1/17 and 7/31/18.
• Recaptured=
  • If Last Gift (#3) is less than 8/1/17 (assuming recaptured = over a year since they last gave)

Or something like this.  Haven't had a chance to test it but I think this logic is logical.   

 

 

It's looking like I'll be doing something similar to this, yes, but it seems to be a very round-about way to do something that should be a standard metric that RE, and especially NXT, would have easily available.


Thanks for the answer!

Beth Muckler

Melissa Graves:

Melissa Graves:

No you are not really wanting to look at month over month - else the only retained donors you would ever have are your monthly donors and you already know who they are. Really what you want is:


Looking at this month's list of donors:

• How many are completely new (new)
• How many gave last fiscal year and then also made the gift this month (retained)
• How many gave previous to last fiscal year but not last fiscal year and then also made the gift this month (recaptured)

Ideally you may also want to look at upgrade/downgrades/same within retained and recaptured but that is a bonus if you can get the first 3!!


The donor giving analysis dashboard has all of these categories and it works if you put in a date range equal to a complete fiscal (or calendar) year. But the instant you put in a date range of a month - it changes the comparison to compare month over month instead of continuing to compare this month's gift to their giving in the previous fiscal years. No one does that. Why was the report designed this way?


This happens when at the end of the FY you do this awesome analysis and give them all this data based on the year. Then each month they want to now see how they are tracking. But you can't in this "awesomely" designed donor giving analysis. Even if you minimally could use a constituent query in the report and only have it show this month's donors - then the date range can remain the full FY. The problem is the date range you enter is twofold - picking the constituents it includes AND determining the dates to compare. That is just dumb.


EDITED - kept calling it the donor category report but I meant the donor giving analysis

 

WAIT - OMG I think I just solved this. YOU CAN put in a constituent query! Hang on for an update...

 

Waiting with baited breath! :-)

Beth Romano

Beth Muckler:

Hi everyone - Hoping someone can help with this! And my brain is a little fried so I could be missing something totally obvious, but...


My boss wants a monthly report now showing acquired donors, returning donors and recaptured donors by month. For example, let's say last month we had a total of 1,000 donors. She wants me to report how many of those were new donors (easy), how many were returning donors and how many were recaptured.


For the life of me, I can't figure out how to easily run a report on the returning and recaptured. Even in NXT, I can't seem to narrow it down to the month, only the year over year, and this is driving me nuts. I can't imagine that I would actually have to go in and look at every gift.


It would be wonderful if we had a way to run a query on next-to-last gift instead of just last gift.


Any ideas or suggestions?


Thanks!

just a suggestion, why don't you  put a gift attribute on the gifts,  ie. new, skip renew, and when the gifts are entered add the attribute to the batch for input.  You will then be able to export the information and summarize in excel or crystal reports.  I use package appeals for this and it works great when I need to report on campaign breakdowns.

Aaron Rothberg

After working on this for an hour I give up! This can easily be done if I could find a way to write an Excel =IF((AND statement with 3 logical conditions. I could only find instructions on how to build them with two conditions and that's not enough for what I tried to do below.

I'm not sure how to accomplish this rolling from month to month (e.g., donor has never given, they give in July 1, 2018 and August 1, 2018 and in July you want to see them as NEW and in August you want to see them as RETURNING, but I do see a way to do this fiscal year over fiscal year by Exporting gift summary data and using a couple formulas in Excel which you can store in a TXT file for easy reference.


Regardless of what Constituent Query Criteria you use to run this against, the solution I came up with would allow you to run this against the entire database to find your donor classifications; New, Returning, and Recaptured. Here's what I did:


Constituent Export with the following Gift Summary fields:


FY19 Total Gift Amount

FY18 Total Gift Amount

FY18 and before Total Gift Amount

FY17 and before Total Gift Amount


Before we get to Excel formula's let's spell out in notation how to compare these values to find our classifications.


New Donor:            FY19 > 0, FY18 and before = 0

Returning donor (for last FY):    FY19 > 0, FY18 > 0

Recaptured Donor:        FY19 > 0, FY18 = 0, FY17 and before > 0


Writing two conditions into an Excel formula is pretty straight forward. I'm having a hard time writing a formula with three logical ANDs as we would like to use for Recaptured Donors. Someone with more advanced Excel skills can probably do this in a simpler way, but I'm going to stick with using only two logical ANDs in each formula and use three columns to completely classify each donor status.


First classification column determines if someone is NEW or NOT NEW

Second classification column determines if someone is a DONOR or NON-DONOR

Third classification column determines if someone is LOST OR RECAPTURED and the recaptured part is dependant upon is someone is a DONOR or NON-DONOR


First classification formula:    =IF((AND(C2=0,B2>0)),"NEW DONOR","NOT NEW")

Second classification formula:    =IF(C2>0, "DONOR","NON-DONOR")

Third classification forumla:    =IF((AND(B2>0,D2=0)), "RECAPTURED IF DONOR", "LOST")

 

Aaron Rothberg

Another hour into this and I finally have something, but it's by no means simple.


Here are my columns:




Here are my formulas:


NEW DONOR: =IF(AND(D2=0,B2>0),"NEW DONOR","")

RECAPTURED: =IF((AND(E2>0,C2=0,B2>0)),"RECAPTURED","")

LYBUNT: =IF(AND(C2>0,B2=0),"LUBUNT","")

LOST: =IF((AND(E2>0,C2=0,B2=0)), "LOST","")

NON-DONOR (column J, says DONOR? above): =IF(AND(D2=0,B2=0),"NON-DONOR","")


The process would be to Export the query fields and paste these formulas into the correct columns and you get something like this:





PS. I will now take lots of aspirin and delete this grotesque monstrosity.


PPS. I can't spell LYBUNT

Cathleen Mai

Does anybody know how to share a Crystal report?

I think I have one that might be helpful here. I tried to attach it but the file type is not supported.

Suzie MacMillan 2

Beth Muckler:

Hi everyone - Hoping someone can help with this! And my brain is a little fried so I could be missing something totally obvious, but...


My boss wants a monthly report now showing acquired donors, returning donors and recaptured donors by month. For example, let's say last month we had a total of 1,000 donors. She wants me to report how many of those were new donors (easy), how many were returning donors and how many were recaptured.


For the life of me, I can't figure out how to easily run a report on the returning and recaptured. Even in NXT, I can't seem to narrow it down to the month, only the year over year, and this is driving me nuts. I can't imagine that I would actually have to go in and look at every gift.


It would be wonderful if we had a way to run a query on next-to-last gift instead of just last gift.


Any ideas or suggestions?


Thanks!

I did not read through all the answers before replying, someone might have posted something similar


This wont help you right away, but we use letter codes when entering gifts to differentiate between new, current, reactivated (those letter codes also change the wording on their acknowledgment letters). This makes it easier to create a query based on the letter code for the gift and using that query in a report. We also have a few different letters for monthly donors so that we can track cancels, upgrades, restarts & new. Basically using a feature we already use to create acknowledgment letters and making it useful for other reporting etc.


Hope that helps a bit and if you wanted more info on how we have it all set up, shoot me a message

Cathleen Mai

According to our BB Community Manager there seems to be a platform limitation, so I cannot attach the Crystal report. If you want it, just send me your email address.

It's very basic, I just set it up, certainly has room for improvements like total amounts etc., but its free ;-).


Also, there is a canned report called "Comparative report" under Analytical reports, where you can enter 2 different periods/date ranges.

That should give you total gift amounts, increase and decrease.



 

Beth Muckler

Aaron Rothberg:

Another hour into this and I finally have something, but it's by no means simple.


Here are my columns:




Here are my formulas:


NEW DONOR: =IF(AND(D2=0,B2>0),"NEW DONOR","")

RECAPTURED: =IF((AND(E2>0,C2=0,B2>0)),"RECAPTURED","")

LYBUNT: =IF(AND(C2>0,B2=0),"LUBUNT","")

LOST: =IF((AND(E2>0,C2=0,B2=0)), "LOST","")

NON-DONOR (column J, says DONOR? above): =IF(AND(D2=0,B2=0),"NON-DONOR","")


The process would be to Export the query fields and paste these formulas into the correct columns and you get something like this:





PS. I will now take lots of aspirin and delete this grotesque monstrosity.


PPS. I can't spell LYBUNT

That is amazing! Thank you so much for the time you spent on this! I can't wait to try it!

Jen Claudy

I've done something like this several times using Excel, and once using Access.  Excel formulas can be a huge time saver.  I suggest that you set up one tab for the results/formulas and one tab for the data.  Then when you want to refresh the report every month, you can export the fresh data from RE, copy-and-paste it into the master Excel document's Data Tab, and the formulas should update to show the new set of results.