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

Mass Add/Remove from WITHIN a field

The Mass Change a Field feature historically was a “find and replace” only. For all selected contacts, whatever was in the field was replaced by what you were now adding. That is, you had to replace the entire contents.

TntConnect 3.2 added an enhancement to that feature whereby you can add or remove values in a list. This works for any field with list items, such as the User fields.

So let’s say that I work in an athletic ministry and I track the favourite teams of my partners because I like to reference those teams in my conversation. Here are my partners and their favourite teams:

  • Huck Finn: Canucks
  • George Jetson: Canadiens
  • Fred Flintstone: Maple Leafs, Blue Jays
  • Elmer Fudd: Blue Jays

In this list, “Blue Jays” creates a problem because…

…for Elmer Fudd it is the entire field contents

1154_MassChange1

but for Fred Flintstone it is only part of the field:

1154_MassChange2

The comma between Maple Leafs & Blue Jays is important! The Mass Change can only work if commas separate the entries.


Add a comma-separated value

It’s an Olympic year and my partners are all excited about our team, so let’s say I want to Add “Canadian Olympic Team” to all four of the above contacts:

  1. Group Actions | Mass Change a Field
  2. Select the field to be updated (this is my User 1, renamed to “Favourite Team”)
  3. Select the action I want to take: Add to Comma Separated List
  4. Enter the value I want to add (“Canadian Olympic Team”)
  5. Press OK

1154_MassChange3

This will add (1) a comma and (2) the new text. So the new field values will look like this:

  • Huck Finn: Canucks, Canadian Olympic Team
  • George Jetson: Canadiens, Canadian Olympic Team
  • Fred Flintstone: Maple Leafs, Blue Jays, Canadian Olympic Team
  • Elmer Fudd: Blue Jays, Canadian Olympic Team

Remove comma separated value

Similarly, I can remove items from a list. Let’s say the Blue Jays moved to Toledo so I want to remove that from everyone.

  1. Group Actions | Mass Change a Field
  2. Select the field to be updated (“Favourite Team”)
  3. Select the action I want to take: Remove From Comma Separated List
  4. Enter the value I want to remove (“Blue Jays”)
  5. Press OK

The new field values will look like this:

  • Huck Finn: Canucks, Canadian Olympic Team
  • George Jetson: Canadiens, Canadian Olympic Team
  • Fred Flintstone: Maple Leafs, Canadian Olympic Team
  • Elmer Fudd: Canadian Olympic Team

Finally, remember the easy way to lookup a value inside a field is to use the “contains” option:

1154_MassChange4

 

What if I have nothing to say?

If I am committed to writing a blog post every week—without fail—and yet I find myself with nothing to say, should I write one anyway?

Yes.

Why? Because consistency matters.

If I miss a week or two, should I profusely apologize to my quiet but committed readers for missing, followed by an explanation of why I missed?

No.

Why? Because they are not that committed.

Bob, aren’t you saying the exact opposite with those two questions?

Yes and No.

Boy that’s helpful.


Okay. Here’s my point. When it comes to ministry newsletters (often called a “prayer letter”), consistency DOES matter. Every person—and/or every organization—has a different cycle of consistency. Some ministries encourage their staff members to write a monthly newsletter; others encourage a quarterly newsletter with interim letters or notes to key groups (e.g., quarterly for all people and monthly for donors only).

Regardless of the frequency, I have two axioms about newsletters:

  1. Be somewhat faithful to the schedule… that is, be consistent.
  2. NEVER apologize in a newsletter

The first book I ever read on “major donor development” was called The 7 Deadly Diseases of Ministry Marketing by Doug Brendel. Though written for corporate donor work, it profoundly impacted how I raise my personal support. One of the most significant principles I learned was that my partners are not sitting at their mailbox anxiously awaiting my newsletters, nor secretly condemning me each day it is late.

To think that they are checking me off for sending the newsletter on time actually suggests I am the center of their world. I’m probably not.

The book also jokingly made the comment that one fundraiser could send the exact same letter twelve months in a row and raise more funds than someone who sends twelve radically different letters in a row. Why? Because consistency matters.

Next question: Without checking any history or logs, answer one of these questions:

  • How many newsletters have you sent in the last 365 days?
  • How often do you send your newsletters… monthly? quarterly? six-weeks? bi-monthly?
  • What date do you drop your letter in the mail?

Before reading further: This is NOT a guilt blog post. I am not suggesting you should write your letters with any specific frequency, on any specific day, etc.

But I am suggesting that consistency matters. And this is how and why this is a TNT tip and not just a “support raising principle”.

A common business principle is that you cannot effectively evaluate what you cannot measure. Since March 2006 I have used a Group | Log History to record sending my newsletter each month. This helps me easily review:

  • How many newsletters I send each year
  • How many people are on my newsletter list (the number ebbs and flows over time)
  • That any individual partner received a specific newsletter
  • The content of each newsletter… it’s a great way for me to look back if I have a question such as, “When did I write about Quebec?”

2018-06-06-NewsletterLog.jpg

My newsletters have been surprisingly consistent over the past 12 years. I write 5-7 ministry newsletters per year. I also, typically, write two “Family Updates” separate from our ministry newsletter. And I send 1 or 2 special gift appeals each year. So on average I send 9-11 pieces per year not including at least one handwritten thank you note to every partner.

I almost hate to say this, but when I do coaching of missionaries, more often than not the most common thing I see is too few communications. Ministry is busy and time flies, and I forget to keep my partners in the loop. This is also why I strongly encourage missionaries to use an Annual Plan. I think this helps plan and execute good communications with our partners.