Query on appeal donors and their previous donations

Alex Reeve

Hi there,

We recently sent out a DM appeal and I am interested to know for each person who has responded to this, when they last donated before this time. I'm not sure how to get a list of the appeal donors and their last previous donation. All I can think of is coding them all with an attribute and then pulling up their entire donation history and looking through them all, but that seems very long-winded.

Any suggestions?

Thanks 

JoAnn Strommen

Alex Reeve:

Hi there,

We recently sent out a DM appeal and I am interested to know for each person who has responded to this, when they last donated before this time. I'm not sure how to get a list of the appeal donors and their last previous donation. All I can think of is coding them all with an attribute and then pulling up their entire donation history and looking through them all, but that seems very long-winded.

Any suggestions?

Thanks 

Alex,

When you received responses to your DM appeal, is it recorded as a gift in response to that appeal?  In other words, can you create a query pulling the donors to the appeal?  (ideally those you sent the appeal to should have the appeal recorded in their records on the appeal tab - it will also then show you who gave / did not respond)

I'm not 100% sure if this would work but it seems to.  Create your query of donors who gave to your appeal.  Go to Reports > Analytical Reports > First/Greatest/Latest Gift.  Select your query as records to use.  Set the date to use as a range that ends before the appeal gifts started.  Set filters/gift types etc.  The report that is generated will show their first, greatest, and latest gift before the appeal time frame.  Get you extra info but should get you what you need also.

Hope that works for you.

JoAnn Strommen

JoAnn Strommen:

Alex,

When you received responses to your DM appeal, is it recorded as a gift in response to that appeal?  In other words, can you create a query pulling the donors to the appeal?  (ideally those you sent the appeal to should have the appeal recorded in their records on the appeal tab - it will also then show you who gave / did not respond)

I'm not 100% sure if this would work but it seems to.  Create your query of donors who gave to your appeal.  Go to Reports > Analytical Reports > First/Greatest/Latest Gift.  Select your query as records to use.  Set the date to use as a range that ends before the appeal gifts started.  Set filters/gift types etc.  The report that is generated will show their first, greatest, and latest gift before the appeal time frame.  Get you extra info but should get you what you need also.

Hope that works for you.

Alex, just looked back at your post and see you may need more info on how to create a query to get your donor list.  No, you do not need to add an attribute.  You should be able to easily query on the donors. 

Maybe you haven't had much experience creating queries.  If not see Knowledgebase solution BB19796.  https://www.blackbaud.com/kb/index?page=content&id=BB19796&actp=search&viewlocale=en_US&searchid=1375977798454  You will want a 'constituent' query - the third option listed. 

Melissa Graves 2

Alex Reeve:

Hi there,

We recently sent out a DM appeal and I am interested to know for each person who has responded to this, when they last donated before this time. I'm not sure how to get a list of the appeal donors and their last previous donation. All I can think of is coding them all with an attribute and then pulling up their entire donation history and looking through them all, but that seems very long-winded.

Any suggestions?

Thanks 

Alex,

It is these questions that prompted me to submit a session for BBCON in the fall specifically on appeals. They are fairly simple records but very often not understood how to use to get the best bang for their buck.

A) did you add a record to the appeals tab an appeal indicating they were recipients of the appeal?

did you then also add the same appeal ID to each gift that was received in response to this appeal?

These are the only two steps that need to happen to properly be able to report on appeals.

Assuming none of these folks have given since the gift to this appeal, you then can go to Export and do a constituent export (to excel) and when you bring over gift fields - select that you want it to show 2 gifts (first question is enter the number of gifts to export - enter 2). Then you can use the order gifts by date (on the same tab as the number) and select descending to get the most recent two gifts. (if you select Ascending you get the first two gifts they made to your org). You can set any other criteria and then what other fields you want and then export - voila, you have your data.

Alex Reeve

Melissa Graves:

Alex,

It is these questions that prompted me to submit a session for BBCON in the fall specifically on appeals. They are fairly simple records but very often not understood how to use to get the best bang for their buck.

A) did you add a record to the appeals tab an appeal indicating they were recipients of the appeal?

did you then also add the same appeal ID to each gift that was received in response to this appeal?

These are the only two steps that need to happen to properly be able to report on appeals.

Assuming none of these folks have given since the gift to this appeal, you then can go to Export and do a constituent export (to excel) and when you bring over gift fields - select that you want it to show 2 gifts (first question is enter the number of gifts to export - enter 2). Then you can use the order gifts by date (on the same tab as the number) and select descending to get the most recent two gifts. (if you select Ascending you get the first two gifts they made to your org). You can set any other criteria and then what other fields you want and then export - voila, you have your data.

No forum post body was found

Alex Reeve

Melissa Graves:

Alex,

It is these questions that prompted me to submit a session for BBCON in the fall specifically on appeals. They are fairly simple records but very often not understood how to use to get the best bang for their buck.

A) did you add a record to the appeals tab an appeal indicating they were recipients of the appeal?

did you then also add the same appeal ID to each gift that was received in response to this appeal?

These are the only two steps that need to happen to properly be able to report on appeals.

Assuming none of these folks have given since the gift to this appeal, you then can go to Export and do a constituent export (to excel) and when you bring over gift fields - select that you want it to show 2 gifts (first question is enter the number of gifts to export - enter 2). Then you can use the order gifts by date (on the same tab as the number) and select descending to get the most recent two gifts. (if you select Ascending you get the first two gifts they made to your org). You can set any other criteria and then what other fields you want and then export - voila, you have your data.

Thank you both for your replies. Unfortunately I have not been using the appeals tab, instead the way that was shown to me when I joined (hadn't used RE before) was to create an import of the mailing distribution list, and create an attribute for it. So I've got a 'summer appeal 2013' attribute which will give me the basis for the recipients for the next mailing.

 I am guessing this is quite easy to add to appeals? Having not tried it yet I assume I can globally add the appeal to all of the 'attribute' people? Once I've got this sorted I will try the export.

 Thank you! 

Alex Reeve

Alex Reeve:

Thank you both for your replies. Unfortunately I have not been using the appeals tab, instead the way that was shown to me when I joined (hadn't used RE before) was to create an import of the mailing distribution list, and create an attribute for it. So I've got a 'summer appeal 2013' attribute which will give me the basis for the recipients for the next mailing.

 I am guessing this is quite easy to add to appeals? Having not tried it yet I assume I can globally add the appeal to all of the 'attribute' people? Once I've got this sorted I will try the export.

 Thank you! 

Update ...

Looks like the appeals tab was working, I assume this must automatically update from the appeals I am coding each gift to? As I have not done anything on this tab before. Anyway I managed to do the export and find what I was after - lots of lapsed donors hurrah!

Thank you

JoAnn Strommen

Alex Reeve:

Update ...

Looks like the appeals tab was working, I assume this must automatically update from the appeals I am coding each gift to? As I have not done anything on this tab before. Anyway I managed to do the export and find what I was after - lots of lapsed donors hurrah!

Thank you

yes, in entering the gifts if the gift is credited to the appeal, RE will automatically show the gift on the appeal tab. 

If you are doing analysis of your appeal, you may want to link the query of those getting the appeal to the fund record or at least enter the number of records solicited.  Otherwise you statistical reports will show 100 % successful.  

The other thing I like having query of those receiving appeal so that I can set up a business rule that will pop-up when entering my gifts that the constituent received the appeal.  Makes it so much easier to know when to apply the appeal without having to look at records or other paperwork.

Melissa Graves 2

JoAnn Strommen:

yes, in entering the gifts if the gift is credited to the appeal, RE will automatically show the gift on the appeal tab. 

If you are doing analysis of your appeal, you may want to link the query of those getting the appeal to the fund record or at least enter the number of records solicited.  Otherwise you statistical reports will show 100 % successful.  

The other thing I like having query of those receiving appeal so that I can set up a business rule that will pop-up when entering my gifts that the constituent received the appeal.  Makes it so much easier to know when to apply the appeal without having to look at records or other paperwork.

No - RE does not automatically add the appeal to the record just because they gave to that appeal. It only does this if you have set this up in business rules (Constituent Options). I have mine set up to NOT add the appeal.

By chosing to do this you are capturing on the appeals tab only those who gave to the appeal, not everyone who was solicited. I recommend using appeals and not attributes for this - the functionality is much better and your reporting options are far better. Joanne described one of the report issues if you do not have the correct info in your appeals.

I do not use such a query of those who received the appeal in a business rule because it is our policy that unless the reply slip we sent was returned, the appeald does not get credit. I try not to guess at the motivation of our donors - they may have been sent our appeal and never opened it but gave for some other reason. I do not project motivation - I prefer to have proof.

JoAnn Strommen

Melissa Graves:

No - RE does not automatically add the appeal to the record just because they gave to that appeal. It only does this if you have set this up in business rules (Constituent Options). I have mine set up to NOT add the appeal.

By chosing to do this you are capturing on the appeals tab only those who gave to the appeal, not everyone who was solicited. I recommend using appeals and not attributes for this - the functionality is much better and your reporting options are far better. Joanne described one of the report issues if you do not have the correct info in your appeals.

I do not use such a query of those who received the appeal in a business rule because it is our policy that unless the reply slip we sent was returned, the appeald does not get credit. I try not to guess at the motivation of our donors - they may have been sent our appeal and never opened it but gave for some other reason. I do not project motivation - I prefer to have proof.

As I didn't remember changing the business rule for this I asked BB what the defaults are for this business rule.  Their response:

The default settings for Configuration> Business Rules> Constituent Options> General Options> If a constituent gives to an appeal not listed on their Appeal tab.... are as follows:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Add the appeal to the constituent

Response:  Responded
Add the date the appeal was sent to the constituent is marked
Use Gift Date is marked under that
Update the No. Solicited for that appeal is marked

So if you don't want it to add the appeal to the record you need to change it. RE default is to add it.  

On using business rule, we only do two mailing appeals for year so I do like the pop-up that they got the mailing.  I have the mailing response piece for at least 95% of them but I just find the pop-up a great reminder. Yes they may have given for some other reason.  If I don't have the response slip, I don't have to select the appeal.