Mo’ Lettah, Mo’ Bettah!

If you’re reading and heeding the fundraising gurus, you’re sending more letters than ever. Your letters are becoming more tightly focused and targeted and are multiplying like (blogs your choice of fecund entity here). Many are likely topical and timely one-shots that will never be used again but clog up GW’s letter lists. You can delete them, of course, but then sure enough you’ll want to use one as the basis for a new letter or at least need to see what you said way back then. So it’s better to keep a copy for future use or reference. GW’s letter library doesn’t yet have archive functionality, so I wrote this little utility while recovering from unplanned and most regrettable T-day excesses. “Drumstick” lets you select letters from the GW library, puts a copy in a folder of your choice, then deletes the letter from GW. Here are screenshots:

Letter is gone!

…but not forgotten!

Let me know if you want this.

I’m Making a List, I’m Checking It Twice…

I recently had the pleasure of beta testing MR’s new GW Events add-in. Sweeeeeet. One of my clients holds an annual tour of historic homes with a cast of thousands and all the trimmings. For them, GW Events should be a slam dunk. But for my smaller clients it’s more than they need. Here’s a twist on how to use existing GW functionality to efficiently address the basics of the events process: invitations and attendance tracking.

Once you establish the invitation MailingList for an event, either with a SmartList or by adding donors one at a time (or both), you’ll most likely send the invitations and establish a record of that interaction in Notes and Tasks. Okay, the event happens, and you need to follow up with attendees. How? You can’t use the invitation list (or the derived mailing interaction) because there are always no-shows and walk-ins. The obvious way to track event attendance is with a Group. You make a Group “2008 Gala Attendees”, look up each attending donor (a Greeter – ED/volunteer/intern – stationed at the door kept a list, right?), edit the donor, and check the Group box. Works. But what a PITA when the event was attended by 250 people – that’s a whole lot of typing in the search box – and me a 3-finger typist! But hold on – it needn’t be that tedious.

What’s the key difference between a GW SmartList and a MailingList? Here’s a hint: it has nothing to do with mailing. GW jocks know how easy it is to make MailingLists from SmartLists, and vice versa. No, the key difference is that MailingLists have that crucial checkbox, which makes them directly and instantly updateable and manual subset selectable. There’s no equivalent functionality for a SmartList. Almost as important for our present purpose is the ability to add single donors to a MailingList on the fly. So expand your understanding of MailingList to mean something more like “Communications and Interactions List”, and use it not just for generating the outgoing invitation, but to complete the feedback loop by recording attendance.

Step by step:

1. Establish the invitation MailingList, but give it a general name for the event. Instead of “2008 Gala Invitations”, just call it “2008 Gala”.

2. Use the MailingCenter to send the invites, making sure to record the interaction when you finish. GW won’t offer a useful name for the interaction, so change it to “2008 Gala Invitation”.

3. Prepare a turnaround document for use at the door and after the event. That way your Greeter can just check off names instead of having to write (or scrawl) the donor’s name. Create a Donors SmartList using MailingHistory/Any Mailing List to select “2008 Gala Invitation”. (Don’t use the invitation interaction, because the people with bogus address who didn’t get invites might still show up). Print the Donors by SmartList Report for that list, customizing the fields to show DisplayName and whatever else will be useful to the Greeter at the door.

4. Funnel all attendees past the Greeter. This is tricky, as you want to maintain your friendly welcoming vibe while making sure no one gets in without being counted. I’ve seen it done brilliantly with a variety of ploys, ranging from Ye Olde Name Tag to trays of drinks to door prize signup. Check off the names that are on the list and write in the names of walk-ins. Ask if the walk-ins are members or have ever been contacted by your org so you know whether you need to get contact info. I prefer to write it all down myself, as it is too easy to lose track of a new donor with poor penmanship or no motivation to write clearly because that mountain of shrimp is dwindling.

5. After the tent is packed up, go back to your GW MailingList with your stained, tattered sign-in sheets. Uncheck All. Then check the box for each donor who showed up. Search for each walk-in donor, and add him/or her if needed, remembering to check the new donor’s box when you get back to the list. That should be a whole lot less work than looking up every attendee and deciphering your Greeter’s scrawls.

6. Complete the feedback loop with a “dummy” mailing to the checked and amended list of attendees. Use the blank letter. Don’t click Print or Save, but do record the dummy mailing, using the interaction name “2008 Gala Attendee”.

7. That makes everything available just the way you want in the Donor record and on SmartLists. No-shows are donors with the “2008 Gala Invitation” mailing interaction but no “2008 Gala Attendee”. Walk-ins have the reverse. Normal attenddees have both.

I hope you all enjoy your Thanksgiving!

Mail Pattern Boldness

GiftWorks has terrific mailing tools. GW 2006 did mailings with ease and grace, and GW 2008 offers real improvements for high volume mailings to Donors. GW’s online help will get most new users through a mailing, and the excellent GiftWorks Mailing Center Guide offers valuable additional information and instructions – a “must read” before attempting a big mailing. But both the online help and Guide are missing some crucial details; neither gives enough information to allow users to choose confidently between the scenarios, and taken together they do not present all the mailing options.

Here’s a breakdown of mailing sources and possible outputs from each with discussion of when each might be appropriate.

1. Outside GiftWorks: No reason you have to use GW, right? If you have decent templates and are disciplined enough to use them always, the quickest and easiest path to presentable and professional ad hoc or one-off communications is outside of GW.  Yeah, I know, those communications aren’t tracked in Notes & Tasks – yoicks! But we all know it happens often enough that you should never assume the GW list of interactions is complete. If strong incentives exist some of this communication eventually shifts to the GW Mailing Center. If not, not.

A. EMail. If any of your organization’s life is lived via Outlook or Gmail, email addresses will inevitably migrate back and forth between GW and  personal address book(s), with the inevitable sync problems resulting in bounced emails from both sources.  TBOMK, none of my GW clients has ever sent an email to a single Donor by clicking Send Mail from the Donor screen. Relax – just because GW offers a single donor option doesn’t mean you have to accept the offer. Just keep sending emails to your donors as you always have and keep quiet about the problems it causes in GW.

B. Printed WP documents. Sure you could produce a one-off ask letter to a major donor (presumably as prep for the face-to-face ask) from inside GW, but why? Given GW’s rudimentary letter source editing capabilities, wouldn’t you rather use MS Word  (or OpenOffice Writer)? Almost certain to be faster and better to do it outside GW and record the interaction directly in Notes & Tasks. And if you need to collaborate with others on content or editing of an important letter you are forced out of GW from the get-go and into something like Google Docs or Basecamp or Comindwork.

Snail mail addresses are less volatile than email addresses, but the stakes are usually higher. So if you make much use of this you’ll want to strengthen your address hygiene practices accordingly. Whether you record the interaction in Notes & Tasks should depend on whether you or other need to know the letter was sent. YRMV, but none of my GW clients are consistent in how they decide or rigorous about putting an interaction note in GW. Sigh.

C. PDF. Not much use for this, other than producing secure, locked-down or password-protected attachments. It’s not easy to get at that functionality from most PDF printer drivers when they are used within GW.

2. GW Mailing Center. This includes Send Mail from GW’s Donor or SmartList screens. Once you have grokked in fullness GW SmartLists, using GW to spray emails and letters is like putting a good adjustable nozzle on a garden hose. It’s so crazy easy to send tightly targeted high quality mailings that the constraining factor is always budget. So bang for the buck is the first concern in evaluating the remaining GW mailing scenarios.  In-house mailing capacity  – staff knowledge, equipment – bears on the choice, but the real decider is letter content. Once you’re using color the cost analysis becomes more difficult and more important. Other common factors that can scoot you directly to a professional printer or mail house are doubled-sided printing, folding and stuffing, and envelope printing, none of which give anyone much joy in house.

A. Save the RTF document (and use WP for post processing). This is a work horse option. It takes tooooo lonnnng to print letters with lots of high res images from GW. But if you pull the images and use a placeholder to block out the image locations (either in the GW letter designer or in your WP for import into the letter designer), the letters will print most sprightly to a RTF file that you can suck into your WP app. It’s quick to add the images, then print from the WP (to local printer or as PDF for transmission to copy shop/printer – see 2C below). You will also use this option when you want a live human being to review the letters in order to insert personal comments in the printed letter (as opposed to adding a handwritten note). This option also gets around the problem noted in 2C below – so use it to do PDFs even if you aren’t adding images or notes.

B. Print directly to printer attached to GW computer or LAN. Works best for small mailings if the letter simple enough – a plain text block with the usual insert fields sprinkled in – and you can successfully define the letter in the Letter Library before maxing your frustration level. If not, see 3A below.  GW 2008’s printing control is good and easy to use – if you shoot yourself in the foot, GW will pause while you extract the slug and apply a bandage. I see far too few big mailings go this route, which suggests that GW has not yet achieved Ultimate Mailing Goodness. Best use of this option is to send Thank You letters and Donation Receipts in a timely manner – as soon as donations arrive and are entered into GW (best practice). Thank You letter should be in the donor’s mailbox before the check clears.

This option works for MOUSes (Mailing Of Unusual Size) only when you already have mondo equipment and an army of white smocked acolytes manning your train-length press. Although in theory GW can print a stack of letters as high as the Washington Monument, the practical limit for most organizations is lower. Much. Compute all-in cost-per-page (don’t forget opportunity cost when your Executive Director ducked out to Staples for ink cartridges came back with latté on his/her breath), or let your intern do it. Get per-page cost from local and online print shops. Compare. You’ll know when to move on to 2C or 3A.

C. Print to PDF. When you return from the residential rehab program for ink-jet cartridge/caffeine abusers you’ll probably be gung-ho for PDFs. Do your homework with the phone book and Google, and chances are good that you will find a local shop doing quality printing for a fraction of the price charged by the franchise shops (beware of hidden, unstated costs like “file opening” charges). Many print/copy shops will accept RTF docs like those you can generate from GW Mailing Center with the Save link. Don’t do it! How the final letters will come out is unpredictable, even if you were shown a proof. The printer drivers may be set up differently on a shop’s various computers, or settings may change between the time they do your proof and print the job, or they may direct the job to a printer with different margins and settings. In short, there are too many uncontrolled variables, and Murphy is waiting to gob-smack you with his Law. That’s one of the reasons behind PDFs, which effectively lock in output at the page level (or at least reduce the opportunities for mishap). If you don’t already have a PDF printer driver, I recommend the free PDF Redirect or PrimoPDF over Adobe. Unfortunately, printing directly from GW to PDF works better in theory than in practice. When you tell GW to print all the letters at once to a single file, there’s a good chance that the PDF printer driver will still prompt you for a file name for each letter or each page. You can configure some PDF printer drivers to concatenate successive PDFs to a single file, but that is too risky to be recommended. So don’t print from GW directly to a PDF printer, even if you figured out how to make it work once. Use option 2A above.  It is safer and likely faster to print to PDF from your WP, even at the cost of an additional step.

3. Export to file. Learn how to use this! Resistance is futile! This is the way to go for your big important mailings. The bar has been raised sooooooooo high for printed materials from even the smallest organizations. Your org might be all volunteer with an annual budget below four figures, but outfits no bigger than yours produce personalized letters that look as good as those from The Bill & Melinda Gates Foundation. Unfortunately, you can’t use GW to produce letters that take full advantage of the data you’ve conscientiously collected and stored in GW. The reason is “conditional formatting” – GW doesn’t do it (yet?). Don’t you want to put a line in the annual appeal letter about “how much fun everyone had at this year’s Gala – and you looked especially mahvelous!”, but that line appears just for those who attended? Of course! Can do it easily with conditional formatting in your WP’s mail merge. Can’t do it in GW. You know a donor is especially interested in birdwatching (he’s in the GW Group “Birdwatchers”), so you’d like to replace the default photo of Smiling Volunteers at your Annual Clean Up with the photo of the Ivory Billed Woodpecker taken at your Christmas Bird Count just on birdwatchers’ letters. Easy enough with your WP, not feasible in GW unless you want to slice and dice your donors into a zillion tiny SmartLists and cook up a static letter for each list. Even the simple stuff is just barely out of GW’s reach: You want to ask each donor to give 15% more than last year and you know it is more effective to ask for what you want than to make the donor do the math. Which is better? “Please consider giving 15% more than you gave last year ($77.50)” or “Last year you gave $77.50 – this year will you please give $90?”. Do whatever you have to – classes, books, consultants – to get good at this. Otherwise, you are leaving too much on the table.

You can export directly from the GW Mailing Center’s final step (in Other Tasks on the left), recording the exported records as interactions in Notes & Tasks just as if you sent letters from GW. What happens next depends on your WP chops, the size of the mailing, and your budget.

A. Export the XLS or CSV. If you are long on cash, short on WP experience, or don’t have time to do the fancy stuff you think is important, email the XLS/CSV to your local mail house with a copy of your letter text (don’t bother to format it unless you like doing that kind of thing). Get a quote, get approval, and move on. Let the mail house experts deal with the merge fields, formatting, printing, etc.. You must have better things to do than honcho a big mailing from step to tedious step, hoping you get it right before the timer dings. The next you’ll hear about your mailing will be when the remittance envelopes flood your PO box. All up, this might not be the least expensive option in first cost, but it might well be the most cost effective. ROI rules, even for NFPs.

B. Print directly to printer attached to GW computer or LAN. Maybe you are a black belt MS Word sensei. Maybe you do have the aforementioned “mondo equipment and an army of white smocked acolytes”. If your letter is on digital letterhead with logos, images, a text box for the BOD names, different headers/footers for first/second page, you want to do fancy conditional formatting, and your BOD had the foresight and endowment to invest in equipment and training, this is by far the lowest cost per page. But I do not think many GW installations are in this enviable position. If yours is, can I come work for you?

C. Print to PDF. Saved the best for last, uh-huh, uh-huh, I like it! This option gives you maximumum control and flexibility with minimum donkey work and stress. You do all the letter composition and fancy formatting in your WP, export the data from GW to a XLS/CSV, then use the WP’s mail merge to generate a PDF. If the PDF is small enough, you can email it to the print shop without leaving your Aeron, otherwise put it on a CD. The print shop will love it because you did all the hard stuff, eliminated all uncertainty, and there’s no back-and-forth to burn up their time. They’ll print the first page as a proof (just for color check, paper, scaling, etc.), you’ll give thumbs up or down, then off it goes. This also very likely to give you the lowest cost per page for MOUSes unless your org owns a modern laser printer and has volunteer help trained to use it.

PS: I wonder how GW 2008 Premium’s support for discount mailings is working out. Before you pony up for Premium, you should discuss with your local PO’s discount mail specialist whether GW’s letter output meets USPS requirements for address hygiene: CASS, DPV, Walk points, NCOA, etc. at your org’s target discount level. The rules get stiffer all the time. My guy said, “No certified CASS, no NCOA, no discount”. Presumably this situation will change when MR offers CASS and related services to GW users. When that happens, your choice will likely be based on final cost: most mail houses include address cleanup as part of their service at no extra charge, as one can’t do discount mailing without it. So even if GW Premium can do the whole mailing and your in-house cost per page is low, once you add in the per-donor cost of repeated cleanups (cleanup cert can’t be more than 90 days old) the total cost per mailpiece might be cheaper if you continue to outsource your MOUSes.

SlapHappy!

What do you do to escape when you’ve got a bad head cold? I write code. Sometimes when I’m “in flow” the better part of a day can pass before I remember that I have a body and that that body feels awful. Last week I decided I’d spend some down time putting together a more generalized and configurable version of the GiftWorks status and renewal date calculator I built for a GW client a year or so back.

GW can’t do automatic calculation of membership status and renewal, which is a minor drag for some membership organizations. If the rules are simple enough it’s almost possible to do it with SmartLists and GW’s UpdateFromSmartList functionality, but usually not quite. Even if you can do it, it’s a chore and a nuisance.

This program automates the process of calculating Donor Status and Renewal Date. I’m calling it “SlapHappy” because that’s how I felt when I started coding – wash down a handful of Excedrin, Benadryl and Sudafed with double sweetened Red Zinger, and you too can feel the goodness.

Here are some screenshots and descriptions of what the program does and how.

The first of the three tabs is a straightforward display of Donors and their Donations. The Renewal and Date and Status are from GiftWorks, and New Status is where SlapHappy will show the result of its calculation.

I’ve seen many different membership policies – I’m sure you have, too. But most seem to boil down to just a few quantifiable choices: how much money over how long a period, whether as yet unpaid pledges count,  and how many tiers of membership (e.g. Gold/Silver/Bronze, Steward/Patron/Supporter/Friend/Pissant) are defined.

Many organizations track membership with a separate Fund. That’s probably the orthogonal or “best practice” approach. But because I have seen orgs that use Campaigns or even Appeals to identify membership dues, I have included selection lists for all three.
You can play with SlapHappy until you get it right. That is, the results of the calculations don’t go into the GW database until you review them and pull the trigger. Likewise, the rules don’t get saved until you say so. So you can run the calculation over and over until you have the rules tweaked to fit your best understanding of your organization’s policy, be it blatant ad hockery or a vague and self contradictory document formally approved by a board of directors that has not quite got a complete grasp of formal logic.

Here’s a trial run. SlapHappy highlights changed statuses. Since the GW Sample Database doesn’t have any statuses, all the calculated values are highlighted.

Here’s another run after saving the results to the database and then changing the rules slightly (from 18 months to 9 months, IIRC).

So how can an organization best use this program? That depends on how important Status and Renewal Date are to your day-to-day operations. If membership is something that only matters when you are sending out appeals letters or renewal notices, you could run SlapHappy before each mailing. OTOH, if being a member has lots of benefits and perks, you’d probably want the calculation to be more-or-less in real time (or appear to be). In that case, once you get the rules and settings just right, you can run SlapHappy with command line switches using Windows’ Task Scheduler. You could set the timer to run the program every hour or even every five minutes – it takes almost no time to do even 5,000 donors.

If you are interested in trying SlapHappy in your own GW setting, let me know. Having developed this application with Microsoft’s latest and greatest – Visual Studio 2008 and the 3.5 .NET Frameow0rk – I can customize it with very little effort. So if this app is close to what you need, but not quite, I can tweak it for you with no pain to either of us.

***SURGEON GENERAL’S WARNING: Lifting the hood on GiftWorks is dangerous to your organization’s health. Test on a copy of your database first. Be sure to make a backup before each step (and do a restore once in a while to be sure the backups work). Be sure no one else is using the database. Document everything you do before you do it in case a large meteorite strikes your office.

Status Seekers

To use GW’s essential Status field to best effect we need to nail down its meaning in the GW information model. GW’s context help doesn’t do it, but the sample values give us some hints at MR’s thinking, so let’s see if we can make some sense of them:

(blank)
Active Donor
Inactive Donor
Pledging Donor
New lead
Customer
Deceased Donor (grayed out)
Unprocessed

Ooops!  The list starts with a data modeler’s non-no! A blank value is most appropriate for fields where the data comes from an external source. For a field like Status, which is either calculated or assigned (more on that below), a value of blank is unecessary and confusing. You could make almost as strong a case for getting rid of Unprocessed - what? Donors are processed like apples or anchovies?

So… maybe Status has to do with whether a donor is on the fundraising playing field? That could work. MR has made the value Deceased undeletable, probably because it is built into the Mailing process, and a deceased donor is certainly out of the game. But if this is what Status means (and I think it is), what is New lead doing in the list? That’s for the sales and marketing guys, not fundraisers. Since everyone in GW is a Donor, perhaps a better term for a new entry is Prospect, so let’s use that in place of New lead. And Customer? What the Sam Hill is that doing in Status? MR, that’s just plain bizarre! Maybe in Category…  But as a value for Status? Delete it!

Now, what about Pledging Donor? The problem with this value is that it overloads Status with information that can be readily calculated by inspecting the donations. Is anyone who has made a Pledge a Pledging Donor or is it just donors with unfulfilled pledges? Or with fulfilled pledges? Bah! You see the problem. Worse,  Pledging Donor would appear to overlap with the Active/Inactive values. Overload for sure – don’t need it – get rid of it!

So, for a basic list of values to indicate a donor’s Status with GW, I suggest the following values and their definitions:

Prospect: (default) newly entered in GW, no donations, no interactions, no solicitations or communications – should probably be on the Welcome letters list
Active: on the playing field, mailable, askable, etc. (Active Donor is redundant in this context). Active should not mean “has given”.
Inactive: exited the playing field for some reason other death
Deceased: (can’t delete this so may as well accept it gracefully)

Because GW does not have a built-in field to record the reason a donor becomes Inactive (ye olde Drop Reason), and because “Deceased” is already overloading this field with that info, it might be useful to add the most common reason for a donor to go Inactive:

Moved Away: donor is playing on a different field, either unknown to us (mail comes back Nixied) or donor is too far away to care about us any longer.

If your organization doesn’t do membership, this is the simplest set of values that can work, and using this list avoids confusion or ambiguity when you have to assign a Status value to a donor.

But many NFPs have “members”, either informally by common use of that term or with a formal membership policy ratified by the BOD (best practice). If either is true for you, consider redefining Prospect to mean “has not yet been a member” and splitting Active into Current Member and Lapsed Member, yielding this set of values:

Prospect
Current member
Lapsed member
Deceased
Moved away
Inactive

Still a workable list of values (although a purist would raise an eyebrow at the last three, suggesting they be combined into Inactive and a new field, “Drop reason” added).

If your organization uses membership levels (e.g. bronze-silver-gold, friend-supporter-patron-savior), you could split Current member into those values without doing any harm.  Of course that depends on how your organization defines membership.

Prospect
Bronze member
Silver member
Gold member
Lapsed member
Deceased
Moved away
Inactive

It might be easy to calculate membership status and level on the fly with a SmartList, or your membership policy might require a calculation you can’t implement with the tools available in GW. (If the latter case is true, consider whether your organization would be better served by a simpler policy.) If you can make the determination by SmartList, make a SmartList Category Membership Levels to store the SmartLists used to do the calculation. Whether you use a SmartList or a manual process, you’ll need to decide whether to store the calculated level in Status or in a Group (with Update All Items in a SmartList). There are advantages either way, but the crucial question is whether your organization needs to keep an historical record of a donor’s membership Status and level as it changes over time.

If you put the membership level directly in Status, it gets overlaid each time you recalculate. For most organizations this simpler approach is adequate. If necessary, you can probably infer (or even recalculate) prior status for a single donor from donation history. For some organizations, – those that are doing sophisticated analysis of campaign and appeal effectiveness and year-to-year giving patterns – having past Status/level stored in a group is worth the effort, as it makes the analyses much easier. If that’s how it is for your org, make a series of Groups for each membership period: 2008 Gold, 2008 Silver, 2008 Bronze, 2008 Lapsed, etc.  You will probably want to hide them so they don’t clog the Groups dropdown.

How often you will run the SmartLists and update Status (and the related Groups) depends on what your organization does with membership. For some organizations, membership matters only when the annual appeal letters are generated; different texts are used for current and lapsed members. In that case, Status and membership probably don’t need to be calculated until just before each appeal. For other orgs, membership has day-to-day significance; it determines admission to events, access to parks or preserves, discounts at shops, etc. If you take phone calls from donors while you’re looking at their record in GW, you might need to see the correct current Status in their Summary. In that case, you’d ideally like the membership calculation to be instant and automatic. GW doesn’t make that easy, but it is possible, as I’ve described elsewhere. Somewhere in the middle will probably do it for most orgs, and that can be done by making Status/membership update a scheduled task for your staff.

Does that do it for Status? Nope, because Status inevitably leads to Renewal Date. But that’s best saved for another post.

***SURGEON GENERAL’S WARNING: Lifting the hood on GiftWorks is dangerous to your organization’s health. Test on a copy of your database first. Be sure to make a backup before each step (and do a restore once in a while to be sure the backups work). Be sure no one else is using the database. Document everything you do before you do it in case a large meteorite strikes your office.

Consider the Source

Can you shoot yourself in the foot with GiftWorks’ Donor Source field? Yes! Whether you lose the foot or just a couple of toes depends on how carefully you aim before you squeeze the trigger. Heck, Mission Research will even wrap your hands around the gun!

From the GiftWorks Manual:

“Donor Source is a field in each donor record that lets you track how each donor came to know about your organization. You can use this to track the effectiveness of your outreach or recruitment efforts. For example, if you held a special informational event to introduce your organization to the community and handed out cards for people to fill out who want more information, you could enter a distinct “source” for that event and then track which new donors you entered as a result.”

Sure you can. Sure you could. But you shouldn’t. Here’s why: It’s a Bad Idea to use a field to track more than one item of information. If you follow the GW manual’s advice that’s exactly what you will be doing.

The first sentence in the manual is wrong but harmless.  Take a look at the values for Donor Source in a new GW database:

Imported
Board Referral
Contacted Us
Friend Of
Funder
Newsletter
Other Non-Profit
Training
Website
Other

It’s not a great list – incomplete, vague, overlapping and redundant – but it is correct in that it is general. Donor Source works most effectively if you use it to store the type of source for new donors, not the specific event or list or person that gave you the new donor. To understand why, think about how you might want to use the information. (And if you aren’t going to use it, don’t waste time entering it!)

Your organization is striving to become more efficient. One of the obvious strategies to lower your cost per new donor is to ask what have been your most productive and least expensive sources in the past. If you stuck with the default list for Donor Source, maybe adding a few general source types, you can get a pretty good answer by running the Source of New Donors report. However, if you garbaged up the list as suggested in the fateful third sentence quoted above by adding values like “2007 Earth Day”, “Susan’s Address Book”, “HS Homecoming Car Wash”, “2008 Phone-a-thon”, “Hiking Club List Share”, “2008 April Door-to-door”, you are SOL. Humpty Dumpty DOWN! On the sidewalk, yolk running everywhere…

Adding those overly granular values destroys the meaning of Donor Source by mixing it up with something quite different: participation in an event. GW already offers an elegant and robust data structure for storing affiliations or event participation: Groups. If you add events and specific sources (like Hiking Club List) to Donor Source, you’ll not be able to extract the general Donor Source types without a lot of work later – either “under the hood” Access queries (who, me?) or using the Update SmartList feature to concatenate Groups in an attempt to put Humpty back together again.

But it gets worse.  Say you signed up new donors at your table at 2008 Earth Day and added that value to Donor Source. What happened to the people who stopped by your table but were already in GiftWorks? They already had a value for Donor Source (even if it’s blank). You can’t overwrite it without totally destroying the meaning of Donor Source. But you want to know whom you talked to at Earth Day. Did you add a Group “Existing Members at Earth Day”? Gag! If you added a Group “2008 Earth Day” and put everyone you talk to in it, you stored the same data item in two places – redundant and a source of possible self-contradiction. Say you develop a list share agreement with the Hiking Club. Same problem, but worse, because it will come up each time you swap lists. Say you add 10 names out of Susan’s Address Book with a Donor Source value to match – fragmentation, dilution, trouble and woe – don’t you want to know that the other 30 names in her book are already donors so you can assign them to her at letter writing time? I think so…

What should you do?

1. Redefine Donor Source thus:

“Donor Source records how (not when or where) each donor became known to your organization.”

It has nothing to do with the donor’s knowledge of you. That’s it. Cross out the sentence starting “For example, if you held…”

2. Fix the list of values.  The exact values will vary org to org, but will look something like this:

Purchased list
Shared list
Board referral
Donor referral
Other referral
Contacted us
Newsletter
Event
Training
Website
(blank) – default

All of these values are general and do not refer to specific events and have no dates. If there’s a proper noun in a value in this list, that value is suspect.

3. Define Groups to record the specifics of each new batch of Donors: “2006 Hiking Club List”, “2007 Hiking Club List”, “Susan’s Address Book”, etc.. It’s easy to create a new Group on the fly when you use the GW Import to suck in a group of new Donors. But be careful to pre-match the import against GW and split it into “new” and “existing” batches, because you won’t want to overwrite the Donor Source field for existing donors, which the GW Importer will do without warning.

4. Work smart when you do bulk manual data entry, as off a paper signup sheet. Create a new Group that corresponds to the event/group/situation that produced the paper list and move it to the top of the Group list. Then edit the donor default fields in Settings so that Group is checked, and you won’t have to do it for each new name on the list. You will have to do it for anyone on the list who is already in GW: Edit Donor, scroll down to Groups, and check the entry (it should be conveniently at the top). For new donors you will also have to manually select Donor Source, because MR has (incomprehensibly and most annoyingly) left it off the list of settable Donor Field defaults.

If your Donor Source list is already messed up (count your toes), you can’t do much to fix it without “lifting up the hood” with Access, because MR has not included Donor Source in the fields you can update from a SmartList. But you can gradually make things better by hiding all the Donor Source values that should never have been added, and maybe use the “Add SmartList members to Groups” function to consolidate and shift data from Donor Source to Groups where it belongs.

Donor Source is an important and valuable piece of information, so handle with care.

***SURGEON GENERAL’S WARNING: Lifting the hood on GiftWorks is dangerous to your organization’s health. Test on a copy of your database first. Be sure to make a backup before each step (and do a restore once in a while to be sure the backups work). Be sure no one else is using the database. Document everything you do before you do it in case a large meteorite strikes your office.

GiftWorks Groupie

Among GiftWorks’ most useful, perhaps underused, and often misused features is Groups. A GW Group is in essence a user-defined check-box for a Donor. Checked mean the Donor is in the Group.  GW allows a practically unlimited number of Groups; each Donor is either in or out of each Group. Checked/unchecked, flagged, unflagged, in/out, on/off, true/false, yes/no – it’s all the same thing for Groups.

So what are Groups used for?

The GW online help suggests “board members, golf tournament sponsors or members”. Of those, I’d say only the second is an appropriate use. Donor membership is probably more successfully captured and managed with Donor Status, and board members should almost certainly be tracked with a GW Relationship (which means putting your organization in GW as a Donor!). But “golf sponsor” is a good example of the endless stream of more or less random bits of data you will want to save in GW, as opposed to keeping on a paper list or an Excel spreadsheet. Affiliations – ADK, VFW, AA – are often best recorded with a Group. Interests – birding, hiking, reenactments – are another example.

MR has announced an GW Events add-in, but many GW users have been doing the basics with Groups and probably will continue that way. If all you need is to know whom you invited and who came, you need one group for each. Don’t make the mistake of thinking that you can use GW’s record of the mailing with which you sent invitations, because if you also invite people with phone calls or personal email, they won’t have that interaction recorded. Better to make an “Invited to Gala” group at the outset, populate it initially from the mailing, and add to it when you find out at the last minute that the President is coming to town.

Which brings us to a great new feature of GW 2008 – the ability to maintain groups with SmartLists. Say you make a group “Needs a Hug”, check it off for every sourpuss Donor you encounter during the year and then print it out before the annual meeting. You work your way thru the entire list, doing your best until your arms are sore and your smile falls off. Meeting over, time to zero out the list and start again for next year. Easy: Settings/Customize/View Group/Update Items In This List/Remove SmartList Members from Groups.

Likewise, to initialize “Invited to 2008 Gala” you would create a SmartList to select everyone who received the invite mailing and use the above steps to add them to the group. Too bad it’s quite not as easy to record the attendees. But at least you can print out the list of invitees and check them off as they arrive, writing in any walk-ons at the end of the list. Also export the invitees SmartList to an Excel spreadsheet, including just Display Name. Then match the paper list of attendees and delete from the spreadsheet any names that aren’t checked. The GW Importer matches exactly on Display Name (if that’s all you give it), so use it to put the spreadsheet people in a new group “Attended 2008 Gala”. Beats looking up and editing each attendee, going into edit mode, and checking the group box. Search for the walk-ons one by one instead of adding them to the spreadsheet because the written name is not a reliable match with Display Name.

Have you ever wished you could “nest” SmartLists? That is, make membership in one SmartList part of the criteria for another? Do it by using the process described above to record membership in the first SmartList in a group, then checking that group in the second SmartList. The “gotcha” is that you’ll have to remember to update the group before running the second SmartList.

Another important tip about groups. If you have been even slightly sophisticated in your use of Households in GW, you should go here and download every script on the page. It’s a bunch of “helper scripts”, one of which will copy groups memberships from any household member to the household. Because most reporting and mailing is done at the household level, if group membership is on an affiliate, the household won’t be selected unless it is also in the group. This script does that. (Although many of these scripts are useful day-to-day, they are presented as “GiftWorks 2008 Database Upgrade Helpers” and can’t be found thru normal search or navigation of the MR website.)

How much does a Group “cost” in terms of database size? Very little, even less if you define the group so that most Donors are unchecked. Because there are only two values, efficient GW stores only Donor/Group ID pairs that correspond to “checked” – if a pair is missing it means the Donor isn’t in that Group. Even if most Donors are in a group it’s not worth flipflopping the definition even if your GW database has many donors.

***SURGEON GENERAL’S WARNING: Lifting the hood on GiftWorks is dangerous to your organization’s health. Test on a copy of your database first. Be sure to make a backup before each step (and do a restore once in a while to be sure the backups work). Be sure no one else is using the database.Document everything you do before you do it in case a large meteorite strikes your office.

So Far Away – Remote GiftWorks Servers

Remote access is something we usually connect with corporate life – people working from the road or telecommuting.  But consider a small NFP with an all-volunteer staff and no central office.  Typically, such an org starts tracking donors and donations with Excel spreadsheets emailed back and forth. Then the org emerges from the egg and scuttles about looking for donor management software. Sometimes it finds GiftWorks. But GW databases are too big to email around, and by that time the staff usually knows better – the dangers of forking and all that. So GW is installed on a computer that belongs to the president or treasurer or the membership coordinator and that person does everything related to GW, including analysis and reporting, emailing the results to the rest of the outfit.

If the org keeps growing, GW will become more than one person can handle. How will new users get to GW? Even if there’s now an office where the GW computer lives, do they have time to drive there? Should they have to? No. GiftWorks is marketed as a multi-user application – you can buy multiple licenses. But how does that work? If all the users are on a reasonably fast LAN (e.g. in the same office), it’s scary but tolerable. If they are far apart, it does not work well, because GW is built on the venerable (developed 1989-1992) JET database engine, an obsolete technology (Microsoft no longer ships Office products using it).  JET powered (excuse me) MS Access until a few years ago, and it’s under the hood of many other software products besides GiftWorks. Access, from the “one person, one CPU” era before LANs or WANs became widespread, is a standalone desktop database. Serving multiple users did not come easily to JET, and it never got very good at it. To paraphrase Johnson, “Sir, a [multi-user JET database] is like a dog’s walking on his hind legs. It is not done well; but you are surprised to find it done at all.” I’ll speak more about database engines in a later post.

If your org bought more than one GW license, you already know something about multi-user environments. The shared GW database lives on one computer only, and all GW users point to that shared location. The computer with the database is the server, and all the other computers are clients.

The logical way to hook up remote clients to GiftWorks is with a VPN (Virtual Private Network) that uses the Internet to connect distant locations securely as if they were physically nearby on a LAN. VPN web services are ubiquitous and cheap (Hamachi, one of the best, is free). You can have as many simultaneous GW users as you have licenses, all connected to the single GW server. But how well a VPN works with GiftWorks depends largely on how fast traffic moves between client and server. That’s because Access was built on the assumption that the data would always be close at hand. If there’s delay in the network (“latency”), GiftWorks over a VPN is a dog, a big smelly dog that’s been rolling in something unrecognizable. Unusable. Since latency is highly variable and uncontrollable without expensive dedicated provisioning, that rules out VPNs with GiftWorks for most orgs. If you’re reasonably sure you’ve got fast reliable Internet connections between all of your remote users and your GW computer and want to try the VPN approach, I’ll be glad to help you get it going. But don’t get your hopes up.

If a VPN won’t fly, or even crawl, then what? The best solution I have found is to run GW on the server itself and connect to the server with remote control software like pcAnywhere (good but pricey) or LogMeIn.com (good and free).  Doing this keeps all running GW apps close to the database, as JET prefers – nay, demands! The way I usually set this up requires at least two computers. One is the GW server, where the GW database lives. It is never used by anyone sitting in front of it. It can be in a corner or even stuck in a closet without keyboard/monitor/mouse. All it needs is a network connection to the Internet.  It runs a remote control host (pcAnywhere or LogMeIn or whatever). When someone far away wants to use GW, he/she connects to the server, takes remote control of it, starts GW and signs in. Even over a dial-up internet connection it will feel pretty much like he/she is sitting at the host. Latency won’t be an issue, nor will the other JET problems that can manifest with a slow connection. The other computers are local clients, used by whoever is in the office. They connect to the GW database normally over the LAN.

This setup provides for one remote user at a time and as many local users as there are client computers. If additional remote users are required, the remote access software can be installed on additional client machines. The key drawback to this provisioning is that one only person can use GW on any machine at a time, so each remote user must have exclusive use of a local computer. For most small outfits, one-at-a-time remote access is sufficient. I’ve yet to configure one of my GW clients with more, other than to confirm that it works. I suppose that you could go up to the practical working limit (~10 users) for Access in a multi-user environment, unless GW-related factors lower that limit.

How does it work? Pretty darn well. Remote users generally don’t notice any more delay than local users. It’s reliable and secure.

Downside? One or two more layers of logons and passwords to manage. You’re at the mercy of your ISP, and “merciful ISP” is an oxymoron.

Is there a better way? Don’t get me started! A real database engine plays well over even a shaky and slow VPN connection.

If you have a better way that doesn’t involve swapping out the engine, please let me know.

All Forked Up – A Tale from the enCrypt

All Hallows Eve is just a few days off, so let’s start with a horror story.

The phone rings: Sabrina. She’s the ad hoc DBW (= database weeyatch, one step lower than a DBA = database administrator) for Witches & Warlocks United – WWU.

“I think something’s wrong. I’m sure donations are missing. Now I’m really scared to put “last donation” stuff on our annual appeal letter.”

Uh-oh. Sabrina had mentioned missing donations before, but I blew her off thinking Darrin (WWU Exec Director) got behind entering donations. But Sabrina had just spent a week nitpicking a big mail list import, and WWU was getting ready to print appeal letters.

“And I can’t find some of the donors. After the last fundraiser I’m sure Darrin entered a whole bunch of the new people who came.”

Double uh-oh.  At the beginning of the summer I had set up a GiftWorks server in Darrin’s office so that Sabrina could get to the WWU GW database from the privacy of her own castle. Forked!!! I know exactly what has happened, and it’s Excedrin Headache #28. Some time after I installed the server and pointed Darrin’s GW at it, he switched back to the original local copy of the GW database. So he and Sabrina have been working on different copies for maybe 4 months. That is, the database lineage has ‘forked” and is diverging further each time either of them logs in.

It’s my bad. I made the new copy on the server. I should have deleted the old copy or renamed it to prevent this from happening. So it’s on me to fix it with the least possible impact on WWU’s preparations or schedule. Hmmm. Haven’t re-merged forked GW databases before. Tasty…

Two days later at Darrin’s office, Sabrina and I put on our steel toe boots and hardhats. The office is too hot for body armor, but we’re goin’ in…

1. First we need to pin down the date of the fork. Only Darrin enters donations. On the server, we check the “official” database for the last donation. July 2. We do an on-the-fly SmartList of the previous two weeks’ donation.
2. On Darrin’s computer we log in to the old/original/bogus GW database and do a SmartList of donations from June 13 – today, just to be sure when the fork happened and that there was no flipping back and forth between dbs. Yep, everything up to July 2 matches.  Several hundred donations after that do not appear on the server copy. A clean fork.
3. We run another SmartList on the old db to see every donor record added or modified after July 2.  Several hundred, which includes all the donors with donations. Refining to get just the new donors results in about 60.
4. Now we know the extent of the damage. It’s too many to do manual reentry of the data. So we’ll have to export from the old/bogus database and import into the offical/server database.
5. We create a SmartList on the old database with all Donations created after 7/2. Customize to select all fields. Export as XLS. Transfer to server.
6. From GW on server we open the XLS with Donations from the old database and begin the import mapping. Now comes our big shock: GW’s importer does not recognize some importnant fields in the export file, and will not be able to import pledges, matching gifts, or honoraria. It also appears that some credit card data cannot be imported. This means that at least some of the forked data will have to be transferred manually. Woe upon woe, but it’s a manageably small number of transactions abd we complete the import without further incident.
7. Until we examine the imported donations a most hideous problem is immediately apparent. Because Sabrina has spent the past weeks industriously grooming donor names, households, addresses on the server, the importer did not match donors for many of the imported donations and creeated new duplicate donors.
8. Follows a donation-by-donation inspection/comparison/resolution. Here’s the silver lining: we completely nailed the most efficient way to resolve and repair a list of donations that might be assigned to duplicate donors. Here’s the guaranteed fastest set of steps:
   1. Go Settings/Accounting/Transfer Donations
   2. Search the first 4-6 chars of last name on the suspect donation’s donor. Put the search chars on the clipboard so you don’t have to retype them.
   3. If only one version of the donor name turns up, it’s not a dupe, so move on.
   4. Determine which is the dupe (source donor) and select that donor.
   5. When the list of donations to transfer comes up, if there’s more than one you probably selected the wrong donor as dupe.
   6. Check the donation to transfer.
   7. In the box for the target donor, paste the search chars off the clipboard.
   8. OK your way thru the transfer.
9. It takes us an hour and a half to work thru the donations. We leave the pledges, etc for later.
10. Next, we create another SmartList in the old database for Donors who were created after 7/2 and have no donations. That’s new people Darrin added as prospects. Export all fields as XLS. Transfer to server.
11. From GW on server we open the XLS with Donors from the old database and begin the import mapping. Nothing tricky, except name. The only way to reliably match Donors from the old database to the new is to run Informal Salutation in the export file thru the name tool.  Weird, but it works.
12. For a final check, we run donor/donation reports on both old and new machines. There are still some “irregularities” that need attention, but it’s been a successful fix.

BTW, before each significant step above we did a backup, just in case. Didn’t have to use them, but it’s an inflexible rule: back up before monkeying around.

Lessons learned:

1. Always check “Show full database location” on the signin screen, and pay attention to it.
2. Whenever you copy a GW database, immediately go to Settings/Database/Manage/Rename and give it a huge obvious warning title like !!!BOGUS OLD Database!!!. Do the same to all usernames.
3. Do not assume you can easily transfer either Donors or Donations between GiftWorks databases. I was surprised at how difficult it is to do, compared say, to how easy it is to do similar tasks between Quicken or Quickbooks databases.

***SURGEON GENERAL’S WARNING: Lifting the hood on GiftWorks is dangerous to your organization’s health. Test on a copy of your database first. Be sure to make a backup before each step (and do a restore once in a while to be sure the backups work). Be sure no one else is using the database. Document everything you do before you do it in case a large meteorite strikes your office.