Flexing the Contribution Report

A few moments ago a good friend wrote to ask for advice on the Contribution Report(That links to the 2017 post about this report.)

“We wanted to see last year’s totals, but when we run the report, it shows us February 2018 through February 2019. We then changed the report to go back one more month, but it still shows us two months this year. How can we see only 2018?”

She added that they tried to look at the twelve month total (export) for each partner but that also spanned the year-end.

1158-DefaultView

I offered her one answer and a few workarounds:

Answer: “You are right. You can go back X number of months, but what you cannot do is enter a date range to display months from X to Y.” The report was never designed to do this, which is about its only limitation.


Instead, I offered four options to potentially get the info they sought:

  1. Normally I just send the history of months to Excel the do my calculations and summary in Excel.
  2. If all she wanted was a total for all of last year (or X years in the past) change the report to years and press Update. Then she will get the total per year for each partner.
    1158-PickYears
  3. Export the current group (Group Actions > Export Current Group), but instead of picking “Twelve Month Total”, select “Prev Year Total” to export the stored value for 2018 for each contact.
  4. Use the awesome “Gift Query” which will return all gifts for the selected time frame. Copy and paste the Query text below into File > Utilities > Run Query, and then change the dates to what you want.
select OrgGiftCode, GiftDate, Amount, OrgPaymentMethod, FileAs, MailingAddressBlock, UserName as FundRep FROM (GiftView INNER JOIN Contact on GiftView.ContactID = Contact.ContactID) LEFT JOIN [User] on Contact.FundRepID = [User].UserID
where giftdate between #01/01/2018# and #12/31/2018#
Advertisements

Lookup Donors by Time Frame

Today’s question: “I’m working on sending an end of year ask. I’d like to filter for a list of ‘Everyone who gave a gift in the last 12 months’. How do I do this?”

This is a great idea and it is super-easy. I am asked this question often. In fact, in TntConnect there are multiple ways to get this same information. For one thing, TntConnect stores (in the Contact Table) a number of gift “details” for every contact. That is, these fields are stored in the same table as the names, addresses, emails, church names, etc., which means they are exportable.

The list of exportable fields is at the bottom of this post.

Do a Lookup | By Field | Twelve Month Total / is greater than / 0.

Or, another way is to use the Gift Table, using a Lookup by Giving.

Lookup | By Giving | [date range].

Doing lookups by giving offers so much flexibility. My wife and I, for example, send cookies to any partner who gives $50/month (that is, $600 / year). Now some partners may have missed a month, so their “average” is under $50, but their pledge is $50. Or their pledge may be $25, but they gave a $300 extra gift last summer, pushing their total giving over $600 for the year. So I’ll do a series of lookups to capture any contact who meets any of these four criteria:

  • Twelve Month Total / is at least / 600
  • Monthly Pledge / is at least / 50
  • Average Monthly Gift / is at least / 50
  • By Group / ‘Always gets cookies’ [e.g., includes relatives who do not give at all, a few neighbors and co-workers, as well as some long-term partners whose giving has dropped below the automatic $600 cookie threshold]

Helpful “Giving Details” fields that TntConnect stores for every contact in your database:

  1. Pledge Amount
  2. Pledge Frequency
  3. Pledge Received
  4. Monthly Pledge
  5. Pledge Start Date
  6. First Gift Date

Most recent gift fields

  1. Last Gift Date
  2. Last Gift Amount & Currency
  3. Last Gift Organization
  4. Last Gift Donor ID
  5. Last Gift Payment Method
  6. Good Until (or ‘Current Until’… when their next expected gift is)

Giving Summaries

  1. Prev Year Total
  2. Year Total
  3. Twelve Month Total
  4. Lifetime Total
  5. Lifetime Number of Gifts
  6. Largest Gift
  7. Average Monthly Gift

You can view these gift details right on the TNT Tab by clicking on the little drop-down arrow next to the “Individual” button:

Contact_DetailsTNTTab

NEW Lookup using the Contribution Report

Another way to lookup donors by time frame is by using a brand new feature in TntConnect 3.1: The “Lookup” button on the Contribution Report.

The Contribution Report has many different filters for time, type of gift, and currency. Once you see the contacts you are looking for, just press the “Lookup” button.

ContributionReportLookup

The only drawback to the Contribution Report is that it only goes backwards in time. You cannot select a date range (for example, you could view the past 24 months, but you cannot view only 12-24 months ago).