Annual Report Data Pull

Are you using a constituent query or a gift query?



 

When we run our listing for our annual report, we also run into these two challenges:

• Soft credits - You may need to export out both hard and soft-credit gifts to make sure you haven't missed anything
  • You'll probably get both spouses in your list - make sure to export out the spouse constituent ID so you can find the married couples. And if you have a recognition name it would probably be the same on both records (Mary Smith and John Jones) so you can find these types of dupes fairly easily.
  • Organizations and donors - If donor has given gift through their organization the gift is hard-credited to the organization and soft-credited to the individual. Do you want to list the gift in the report under the organization and/or the individual donor's name?  
• Anonymous - either individual gifts are marked anonymous or donors are marked fully anonymous
  • If the gift was anonymous, does the donor want to be listed in the report?
  • If the donor is anonymous, here's where your 'recognition name' can be helpful because you can enter that as 'Anonymous' on their record.

Nicely expained JoAnn!



I export my annual report lists using a Microsoft Word Merge File format. I have a query with an ask operator for the total gift amount so I can run each giving level separately. I export our primary addressee and that's all that I need for my list. For the major donor lists I also export the total gift amount as a reference and then delete it before I hand the lists over to be proofed before going to the printer.

You really need to do this through export. There are so many filtering and output options in Export that are really not available in a useable way through Query alone. I used to try to operate in Query myself, back when I was first learning the ropes. Believe me, you can save yourself so much spreadsheet cleanup in the final product if you just experiment around a bit with the Exports. See if someone at BB can walk you through it, or see if thee is an existing Export that you can just tweak.

Your query sounds accurate, but you'll need to use the export feature to de-dupe the results. You can use the same fields you have in the query output in the export output.

Cathy Johnson:

If you have the donor categories (your giving levels) already established that you will use in the donor report, then an easier way to pull this information is to run a Donor Category Report based on the query you created., or you can create a new query, which will filter out certain gifts that you may not want to include when determining that donor's cumulative giving for the year.



 You can set up the donor category table in configuration before you run the report.  By running this report, the system will automatically place the donor in the correct category based on the donor category table you create in configuration. EXample:



$100-499 Friend

$500-$999 Advocate

$1,000-2499 Partner

$2500-$4999 Senior Partner



You can tell the system to create an output query when you run the report, which will keep the listing in category order.



If you want more step by step detail, I would be happy to walk you through it over the phone.  If you would like me to call you, send me a message with your phone number.



Cathy Johnson

Nemours Fund For Children's Health



 

Jumping in late here, but I have two questions about the Donor Category Report for those who have experience running it. I like this solution and want to try it next year. I have some spare time, so I'm doing a few test-runs using the same date range that we used for our Annual Report this year just to see how close this report comes to what we listed. It's pretty close!



I'm coming across two issues:



1. Filters and Inclusion of Gifts: I seem to come up with a handful of missing records in each category, and in a few cases, this report is ranking donors in different categories than we'd come up with ourselves (I know we manually moved a couple of them so this is not the biggest concern for me). We have one organization in particular that is ripe for troubleshooting because they have only ever given us one gift, and that lone gift was enough to put them in our top rank. The only Filter I am using is Campaign, and this gift was coded with one of the Campaigns we're including already. It's not being excluded by gift type (it was Cash), the Gift date falls within the defined range, the donor does not wish to remain Anonymous, we're including all Inactive/Deceased/No Valid Addresses, so it's not that; I am not sure at all what gives, but it's just not showing up in the results. Maybe my brain is just tired, but I don't know what else to check.



Got it. Nevermind! 



2. Organization Names: It always bugged me that Organization records don't have anything analagous to Addressee/Salutation, because even Organizations sometimes want to be recognized by names other than what we have in RE:. I've heard of using Attributes as a surrogate field to hold alternative recognition names for Organizations, but I don't know if that will work in this case. Does using this report force us into accepting the default Organization name for display? This is how it looks when I peer into Name Formats > Constituents > Organizations on the Format tab. I'd hate to use this report to save myself time, but end up having to manually fix a bunch of names, so I'd prefer to pre-emptively edit Organization names for this wherever possible.



 

We created a notepad on organization records named 'Annual Report Listing' so we can put in the alternate name format and any additional notes. We then export this out during the annual report process to find these 'exceptions'.

 

I use a Constituent Attribute of "Donor Listing" on every record.  For individuals, it defaults to our Home Addressee (Dr. & Mrs. Robert C. Hernandez) and for organizations, it defaults to the Org Name.  Whenever entering a new Constituent, you select, copy, and paste either the Salutation or the Org Name into the Attribute Description (and add the current date).



But, as it's a text Attribute, it can be edited however necessary.  In the Attribute's Comment Field, I try to always indicate where the info came from (and if it's a long explanation, the Comment will be "see ConstNote" and there is a Constituent Notepad Type of "Addressee/Salutation/DonorListing" to contain that data).  We have several donors who regularly change how they want their names listed, so the Note also allows us to keep track of when and how we were instructed to change the listing.  That's come in handy before.



In the Comments, I will include prefixes of "REMOVE" for something that for whatever reason is not to be listed, ever, or "REVIEW" for something that needs to be reviewed before publication (like when one of the donors has passed, we use a footnoted indicator for that in that year but remove it and just list the surviving spouse in subsequent years).



At a former org, we used an Addressee/Salutation.  However, that means that if the wife is in the database, gives a gift, and wants her name listed as "Jane Doe", then later someone discovers her husband's name is John and adds that to RE, the Salutation automatically updates to Jane & John Doe...and that may not be what the donor (Jane) wants.



We also print the Donor Listing in the receipt portion of every acknowledgement letter (with an email and a phone number to contact the Development Office and make any changes) and also on reply cards/forms that we send out with appeal letters.  (We pre-fill as much information as possible...with the idea that reviewing and possibly editing information is easier and more likely to happen than filling in everything every time...plus it makes the reply card very customized which can only help in getting a higher rate of response.)

JoAnn Strommen:

Interesting way to use attribute.



Just to note, when using add/sal for Donor Listing and adding spouse John to record.  Listing will only change if you have used an add/sal format that includes both names if available.  If add/sal selected is just for the single constituent vs. a joint one, it will not change.



Do wish there was a less manual way to deal with listings when a spouse has passed.