What's a Direct Debit donor worth?

Donors who give on a regular basis via Direct Debit are an extremely valuable group for a charitable organisation, but when trying to evaluate their financial worth things become slightly complicated. In this blog post I'll describe how to create the above visualisation in Tableau (using dummy data) as a way of demonstrating the value of Direct Debit donors - and, conversely, the cost of Direct Debit donor attrition.

Now I've written before about how you really need to take the long-term view when assessing the value of a given acquisition channel, but I didn't refer very much to Direct Debit donors in that post. The main reason for this is because we're not talking about a single payment in this case, and because we can't know for sure how much in total these donors will contribute we sometimes project how much we can expect from them over a given period of time - in UK higher education we take 5 years as the default, in accordance with the Ross-CASE benchmarking guidelines. But is 5 years a realistic length of time over which to expect a donor to give on a regular basis? And if it isn't, then for how long on average does a Direct Debit donor give before cancelling their recurring payment?

These are the sorts of questions you might hear from direct marketing managers at your organisation, but my instincts tell me that these aren't perhaps the correct questions to ask. After all, whatever average we calculate today will look rather different months from now as what we're looking at isn't fixed - so we need a way to visualise the length of time over which Direct Debit donors are actively giving, and to highlight in trends in when they cancel.

There are five crucial pieces of data you'll need in order to build this chart:

• A unique identifier for each Direct Debit donor (known as Constituent ID if you're using Raiser's Edge as I am)

• The status of the Direct Debit - is it still active, or has it been cancelled?

• The date on which this donor set up their Direct Debit, and the period that falls within. I'm using Financial Year, which runs from August to July at my institution, but you may choose calendar year or something else entirely

• The date on which inactive Direct Debits were cancelled

• Using the above two dates, the Direct Debit retention period (in days)

With this information to hand, you're ready to build box plots in Tableau. For those of you unfamiliar with box plots, hopefully the diagram below pulled from Wikimedia will help with how to read them:

For those of you who are new to Tableau, building this isn't as complex as it may seem: just drag the Financial Year acquired dimension into Rows (drag it into Dimensions first if it appears in Measures, and make sure it's Discrete), then drag the sum of the Retention Period into columns, the unique identifier into Detail (so that we're breaking it down to the constituent level), change the marks to circles, and finally drag Direct Debit status into Colour so that we can define active and non-active Direct Debits.

I show you how this is done in 25 seconds below...

Next you'll need to do a bit of formatting, so begin by adding a dark border to your circles (it helps to keep their shapes defined when they overlap), then enlarge the font for the Rows (12pt perhaps for the values, and 14pt for the header). Now let's make our box plots by adding a reference line: right click on the X-axis to activate the Reference Line window, and select Box Plot from the options at the top - again, feel free to play around with the format if you'd like, but I've left it as the default. Before you hide the Columns header, add a series of reference lines to it at intervals of 365 days to mark where the end of each years falls - thus making it easier to read the chart as described below.

Finally, create a calculated field called "Retention period (years)" with the following value:

if INT([Retention period (days)]/365) = 0 then "less than a year"
else "less than " + str((INT([Retention period (days)]/365)+1)) + " years"
END

This is simply checking to see if, when the number of days are rounded down to the nearest year, it equals zero (so less than a year) or more than zero (less than the next number of years). This is useful for the tooltip, so that when someone hovers over a dot they don't have to work out in their head how long that DD has/had been active for. There are some additional tweaks to be made if you want to replicate my final version (increase the size of the circles, clean up the tooltips and incorporate the above calculated field into them, hide the background grid, and add a suitable title), but what does this show us at this point? With the caveat that this isn't "real" data, there are still things we can say:

• Look at the start of the shaded area in each year, where everything up to that point represents a  quarter of each year's Direct Debit donors. See how it's shifting leftwards? So long as all the dots up to that point are orange (cancelled DDs), we can conclude that it's generally taking less time for a quarter of the Direct Debit donors acquired in a given financial year to cancel.

• Look at the first orange dot in each year. The fact that in FY2009 it appears quite far to the right whereas from FY2011 there are lots clustered far to the left tells us that the time it takes for a Direct Debit donor acquired in a given financial to cancel has decreased.

So it's not just that we're losing a quarter of our Direct Debit donors acquired in a given year at a faster rate, but that they're cancelling much sooner than they used to - we're losing  bunch of them in no time at all now, where previously it took over a year before a single one was lost. Try this with your own Direct Debit donors, and you may see something similar. How does this answer our original question? Well, the "worth" of a Direct Debit is directly tied to its longevity, and what this chart demonstrates is that we shouldn't take a 5-year value for granted. If we acquired a £10/month DD then we'd attribute a 5-year value of £600 against that, and in all likelihood not a great deal is done to ensure that 5-year value is realised. What this chart demonstrates is just how misguided that projection is when so many DDs are turning from blue to orange so quickly, so it shifts the emphasis from the success of acquisition ("we've secured £600 over 5 years!") to the necessity of retention ("how will we retain this donor over 5 years to secure £600?"). If, by undertaking a similar data analysis exercise, you discover that the future you had in mind is less certain than you'd assumed, I hope that you don't cut your losses and write off what you know you'd previously been unable to secure - but that instead you'll start seriously planning for the future you want by investing in excellent donor care. It's the least these donors deserve.