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.

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.
   
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#

He said, She said

The portion of the support to our ministry account from my partners (blue) and my wife’s (red). This reflects mostly where I have devoted my partnership development efforts over the past 21 years. The two blips—2005 and 2016—represent significant campaigns we ran at that time.

If you are married, did you marry your spouse before or after you went on the mission field? If after, was she or he also already a supported missionary? And if so, what portion of your current or lifetime support is a result of the contacts each of you brought into the marriage?

It may surprise you to learn that TntConnect can answer that question!

But not natively.

When I started using TntConnect 16 years ago this month, I spent a lot of time getting my data ship-shape. I imported every gift we had received in the 13 years before TNT. I got all of the addresses correct. And more.

I have always loved analyzing data and seeing how things work. Even in Excel (before using TNT) I had separated contacts based on whether they were on my wife’s partnership team or my own. We were married in 1997 and at the time had almost equal amounts of support, so at the time it was about 50/50. Five years later I started using TNT, and to retain this helpful information, I used the Family Side field to differentiate between “his” partners and “her” partners.

The Family Side field is intended to be used in conjunction with the Family Level field to define “his cousin” or “her sibling”. But given that very few of our contacts are relatives, I knew I would never use those two fields as designed.

So I repurposed the Family Side to represent “the side of the family this contact came from”. For every contact who has given a gift I marked them as His or Hers. I do have some marked as “Neither” because the relationship has been built with us as a couple, since we got married. But surprisingly, only 1.5% of all giving to our ministry account has come from these kinds of people. The remaining 98.5% has come from partners who joined our team more than 21 years ago OR have been referrals from those two teams as recently as last year.

Because I have every contact marked as “His”, “Hers”, or “Neither”, I can then view reports on the Analysis View:

Average Monthly Gifts by Family Side:

Lifetime Gifts by Family Side:

The most noticeable aspect of this is that lifetime, partners from my wife’s source are a greater proportion of our lifetime giving than they are of current giving (above).

The above charts are right out of TNT with no additional effort. I did export the Contribution Report to Excel and run more reports for my own delight (that’s how I generated the chart at the top), but that’s beyond the scope of this blog post.

My point, as always: Because I asked a question of my database, I figured out a way to get the answer. In other words, I let my needs drive my use of the software, rather than the software dictate to me what I need.