The basics of direct marketing testing

November 16, 2015November 16, 2015 nickellingerhypotheses, starting, testingLeave a comment

It’s testing week here at Direct to Donor and we’re going to start with some simple principles, as is our Monday pattern. This is the first of many testing weeks, given the importance of the topic.

Unless it doesn’t work, in which case we might never speak of this again.

This is a great segue to the first rule of testing: that which works, works. That which doesn’t work, doesn’t work.

I know, it sounds like I’m stating the obvious, but there’s an oft-forgotten conclusion from that, which is that if you aren’t willing either to roll out with it or to scrap it, you shouldn’t test it.

Two different much-beloved CEOs have expressed to me over the years that the single best predictor of whether a mail piece would succeed or not is whether they liked the piece. If they liked it, it wouldn’t work; if they didn’t, it would.

Part of why they were and are much beloved is that it doesn’t matter whether they liked it or not – it was all about whether a tactic worked successfully. The mantra that goes with this is:

Or, as I would put it, it doesn’t matter if the source if the quote is a damn dirty Commie if it’s a good quote.

That said, there are some things that are beyond what your organization will accept. For example, an environmental organization shouldn’t use paper in their mailings from non-recycled old growth forests.

If that’s the case, then, don’t test it. A goal of testing is to find something that will be able to use in some larger capacity in the future. If that isn’t possible, it eliminates the need for the test.

That said, the list of sacred cows should be as small as possible. You’ll hear me say test everything and I mean it – other than things that are untenable, experimentation is the best and truest way of learning. Donor surveys are great, but they show what people think they would do and how they would react versus what they do do; it’s important not to confuse words and deeds.

There are things that are far more important to test than others. That’s why it’s important to start with a hypothesis and to test the fundamentals first. I love tests that nibble around the edges as much as the next person – if you can show me how to improve a piece with a different teaser and pick up 1.4% additional in response rate, I’m game. But the most important tests that you run will be about fundamentals – who are the people that I should be talking to and what offer am I giving them. Having a hypothesis will help with this and the broader it goes is better. A hypothesis like “I believe our lapsed donors will respond best to the means and message that brought them into the organization initially” works well because it lends itself to testing on various platforms with a variety of tactics and a success could mean large things for the organization. One like “I believe a larger envelope will work better” is restricted to one piece at one time and thus has limited ripple effect.

Finally, please learn from my mistakes and don’t test something by rolling out with it. I did this in my first year of nonprofit marketing. We had three underperforming mail pieces and I decided to replace them with new packages I had dreamt up. Thankfully, I was lucky – the success of one of my pieces paid for the abject failure of the other two. If I hadn’t been lucky, I might be blogging on effective panhandling tips right now. You don’t want to put your nonprofit in a position where hitting goals and achieving mission is based on your hunch.

This may be a bit of conventional wisdom. However, tomorrow, there is one piece of conventional testing wisdom that needs to be taken out back and shot for the benefit of your testing program.

The dirty dirty data tricks that dirty dirty people will use to try to get their way

November 13, 2015November 12, 2015 nickellingercharts, data analysis, data presentation, Excel, graphs, PowerPointLeave a comment

Matthew Berry, New York Times bestselling author and mediocre fantasy football advice-giver (this is a compliment; you have to listen to the podcast), does a column each year called “100 Facts.” In his intro, each time, he warns about the exercise he is going to undertake. Statistics can be shaded in whatever way you wish (I’m paraphrasing him), so he acknowledges that he is presenting the best facts to support his perceptions of players. But he goes further to say that’s all other fantasy football analysts are doing as well – he’s just the one being honest about it. It’s the analyst’s equivalent of Penn and Teller’s cups and ball trick with clear cups – just because you know how the trick is done doesn’t make it less entertaining.

With the knowledge of statistics comes the responsibility of presenting them effectively. My first and much beloved nonprofit boss used to say that if you interrogate the data, it will confess. I would humbly submit a corollary: if you torture the data, it will start confessing to stuff just to make you stop.

A well-wrapped statistic is better than Hitler’s “big lie”; it misleads, yet it cannot be pinned on you.
— How to Lie with Statistics by Darrell Huff

So here are some common tricks people will use to make their points. Arm yourself against this, less you be the victim of data presented with either malice or ignorance.

The wonky y-axis

The person presenting to you was supposed to increase revenue by a lot. In fact, s/he increased it by only a little. The weasel solution? Make a mountain out of that molehill:

Note that the difference between the top and bottom of the y-axis is only $10,000. Here’s what that same graph looks like with the y-axis starting at 0, as we are trained to expect unless there’s a very good reason:

Both are true, but the latter is a more accurate representation of what went on over the year.

Ignoring reference points

Let’s take a look at that last graph with the budgeted goal added in.

This tells a very different story, no? Always be on the lookout for context like this.

The double wonky y-axis

I’ve been saving a Congressional slide for this blog post. I make no claims about which side of this issue is true or right or moral or whatever. That said, this is also a good example of having quality debates with good data versus intentionally putting your spin on the ball.

This graph was presented by Congressman Jason Chaffetz in the debate over Planned Parenthood.

Hat tip to PolitiFact.

The graph seems to say that Planned Parenthood health screenings have decreased, abortions have increased, and now Planned Parenthood performs more abortions than health screenings.

But this is a case where the graph has two different y-axes. Looking at the data, you can see that there were still well more than double as many prevention services performed as abortions. When we look at the graph, it looks like the opposite is true.

Again, you may choose to do with this information what you will; there are many who would say one abortion is too many. However, to paraphrase Daniel Patrick Moynihan, you can have your own opinions, but not your own facts.

The outliers

This is one of those things that is less frequently used by people to fool you and more often overlooked by people who subsequently fool themselves.

Here’s a sample testing report.

This one seems like a pretty clean win for Team Test Letter. Generally, you are going to take the .2% point decrease in response rate in order to increase average gift by $7 and an additional 14.6 cents per piece mailed out. Game, set, match.

But one must always ask the uber-question, why. So you look at the donations. It turns out a board member mailed her annual $10,000 gift to the test package. No such oddball gifts went to the control package. Since this is not likely a replicable event, let’s take out this one chance donation out and look at the data again.
test3 An even cleaner win for Team Control. The test appears to have suppressed both response rate and average gift.

Percentages versus absolutes

Check out the attached graph of email open rates, where a new online team came it and the director bragged about the increase in open rates. I actually saw a variant of this one happen live.

Wow. Clearly, much better subject lines under the new regime, no? More people are getting our messages.

Well, for clarity, let’s look at this on a month by month basis.

So, something happened in July that spiked open rates. Maybe it’s the new team, but we must ask why. One of the common culprits, when you are looking at percentages, is a change in N, the denominator. Let’s look at the same graph, but instead of percentages, we are going to look at the number of people who opened the email.

Huh. Our big spike disappeared.

In looking into this, July is when we started suppressing people who had not opened an email in the past six months. This is actually a very strong practice, preventing people who don’t want to get email from you, have moved on to another address, or were junk data to begin with off of your files. As a result, your likelihood of being called spam goes down significantly.

So it wasn’t that twice as many people were opening emails; it was that half as many people (the good half) were getting the emails.

Correlation does not equal causation

The wonderful site FiveThirtyEight recently did a piece on how Matt Damon is more attractive in movies where he is perceived as being smarter. For example, see how dreamy Damon is perceived to be as super-genius Will Hunting. As Irene Adler says to Sherlock in the eponymous BBC series, brainy is the new sexy.

And you can look at this and think a logical conclusion: the smarter a Matt Damon character is in a movie, the more attractive that character is perceived to be. This is plausible even though dreaminess was judged from a still frame – if Matt Damon is wearing an attractive sweater, it’s one of the Bourne movies; if it’s WWII garb, probably Saving Private Ryan.

This conclusion would reason that when Damon plays Neil DeGrasse Tyson in the upcoming biopic, his resultant sexiness will distract from the physical mismatching casting.

There’s also the hypothesis posited by the author: “The more attractive Damon is perceived to be in a movie, the smarter he is perceived to be.” This says the reverse of the above: if Damon is attractive in a movie, he will be perceived to be smart. This too is plausible – we tend to overestimate the competence of people we find to be attractive (hence why there is no picture of me on the site – you would immediately start discounting my advice).

Or it could be an exogenous third factor that causes both. What if make-up artists want to symbolize dumbness by making actors unattractive (actually, since it’s Matt Damon, let’s say less attractive not unattractive)? Film is after all a visual medium and since they know people underestimate less attractive people, they aim to make less competent characters less attractive.

Those are the ways correlation can go: A can cause B, B can cause A, or C can cause A and B.

This is what we must guard against in drawing final conclusions, but rather continually refined theories. Let’s say you are seeing a general trend that your advocacy mail packages are doing better than your average mail package. It’s generally safe to say more advocacy mail packages would be better. But what if it isn’t the advocacy messaging, but that advocacy messages have a compelling reply device? Or that when you mailed your advocacy pieces, you were also in the news?

One of the key parts of determining the results of a test is learning what the test actually means. It’s important to strip away other possibilities until you have determined what the real mechanism is for success or failure. This is why, for the blog analysis last week, I did a regression analysis rather than a series of correlations – to control for autocorrelations.

You don’t have to be versed in all manner of stats; the most important thing it to keep asking why. From that, you can find the closest version to the truth.

7 direct marketing charts your boss must see today

November 12, 2015November 19, 2015 nickellingercharts, data analysis, data presentation, Excel, graphs, metrics, PowerPoint, statisticsLeave a comment

Yay! It’s my first clickbait-y headline!

I preach, or at least will be preaching, the gospel of testing everything. There have been times that it has been a rough year for the mail schedule, but then we get to a part of the year we tested into last year, so I know that the projections are going to be pretty good and our tweaks are going to work. It is those times that there are but one set of footprints on the beach, for it is the testing that is carrying me. So I eventually had to test out one of these headlines — my apologies in advance if it works.

The truth is that there are no such charts that run across all organizations. There are general topics that you need to cover with your boss – file health in gross numbers, file health by lifecycle segment, in-year performance, long-term projections, how your investments are performing.

But what you need to do is tell your story. You need to analyze all of the data, make your call, and present all of the evidence that makes your case and all of the evidence that opposes it.

This sounds simple, but how often do you see presentations that feature slides that educate to no end – slides that repeat and repeat but come to no point. Also, they are repetitive and recapitulate what has already been said.

On Monday, I brought up the war between art and science marketers. The secret to how the artists win is:

Stories with pictures

Yes, really. The human brain craves narrative and will put a story to about anything that comes in front of it. It also retains images better than anything else. There’s a semi-famous experiment where they gave noted oenologists (French for “wine snobs”)* white wine with red food coloring. The experts used all of the words that one uses to describe red wine, without ever noting that it was actually a white wine. When confronted with this, the so-called wine experts all resigned their posts and took up the study of nonprofit direct marketing to do something useful with their lives.

OK, I’m lying about that last part.

My point is that we privilege our sight over all other sense – in essence, we are all visual learners. When we see words on a slide, our brain, which is still trying to figure out why it isn’t hunting mastodons, sees the letters and has to pause to think “what’s with all of those defective pictures.”

So, as I’ve been writing a lot of defective pictures and I promised the seven direct marketing charts your boss must see today, let’s discuss a story that you would want to tell and how you would present it.

The idiot I replaced the idiot that I replaced cut acquisition mailings in 2012.

It spiked net revenue for a time, enough for him to find another job.

But that has really screwed us out of multiyear donors coming into 2015. You can see the big drop in multiyear donors in 2014 because they weren’t acquired two years earlier.

And multiyear donors are our best donors. You’ll also note that our lapsed reacquired donors have greater yearly value than newly acquired with about the same retention rate. Thus, my first strategic priority to focus more in reacquiring lapsed donors. Not as good as the multiyear donor that idiot made sure we didn’t have coming into the file this year, but pretty darn good.

Lapsed donors have actually decreased as a portion of our average acquisition mailing…

…yet they have been cheaper to acquire. In summary, they are better donors than newly acquired donors and they are cheaper to acquire, yet we’ve been reaching out to them less. Thus, we have an opportunity here.

Because of this insight and because my salary significantly lags the national average for a direct marketing manager of $67,675, I believe I deserve a raise. I’m now open for questions.

I swear that in many presentations, this would be over 30 slides and over an hour long. I’ve actually given some of those presentations and if someone was in one of those and is still reading this, I apologize.

Some key notes from this:

Note the use of color to draw attention to the areas that are important to you. Other data are there to provide background, but if you are giving the presentation, it is incumbent upon you to guide the mind of your audience. In fact, if you are giving the presentation, you may wish to present the chart/graph/data normally, then have the important colors jump out (or the less important ones fade away), arrows fly in, and text appear.
As mentioned, this is a different structure of presentation that would normally occur. Normally, there would be a section on file health, then one on revenues, one on strategic priorities, and so on. However, when you structure like that, the slide that makes the point of why you are doing the strategic priorities you are doing may be 50 slides early. You can say, “remember the slide that said X?” but regardless of what the answer is, the answer is really is no. You are smarter than that. You are going to use data to support narrative, not mangle your story to fit an artificial order of data.
There is one point per image (with the exception of #4, which had a nice segue opportunity) and no bullet points. Bullet points help in Web reading (hence my using them here), but they actually hurt memory and retention in presentations.

With this persuasive power, though, comes persuasive responsibility. Not in the sense that your PowerPoint will soon have you enough dedicated followers to form your own doomsday cult, although if that opportunity arises, please take the high road.

What I mean is as you get better and better at distilling your point, there will be a temptation to take shortcuts and to tilt the presentation so it favors your viewpoint beyond what is warranted. Part of this is ethical, to be sure – don’t be that type of person – but a larger part is that no one person is smarter than everyone else summed together. Even readers of this blog. If you omit or gloss over important data points, you aren’t allowing honest disagreement and insights among your audience that can come to greater understanding. By creating an army of ill-informed meat puppets, you are going it alone trusting on your knowledge and skill alone to get you through. There will be a day and that day may be soon when the insight you will need will be in someone else’s head.

You do have to prioritize for your audience. You may have noticed some other points you would have covered in these graphs – retention in this program is falling and cost to acquire donors is increasing. This person chose to focus on lapsed but didn’t hide the other metrics, which is sound policy.

So we will cap off the week tomorrow with tricks that other people use to shade their data. I debated doing this section because it could be equally used as a guide to shade your data. But you are trusting me and I’m trusting you. Knowledge is not good or bad in and of itself, but let’s all try to use it for good.

* Oenology is actually from the Greek words for “wine” and “study of,” but that isn’t funny…

Metric pairing for fun and nonprofit

November 11, 2015September 21, 2017 nickellingercharts, data analysis, metrics, statisticsLeave a comment

There is no one metric you should measure anywhere in direct marketing. Like Newton would have said if he were a direct marketer, each metric must have an equal and opposite metric.

The problem with any single metric is, as either or both of Karl Pearson and Peter Drucker said, that which is measured improves. My corollary to this is what isn’t measured is sacrificed to improve that which is measured.

So what metric dyads should you be measuring?

Response rate versus average gift: This one is the obvious one. If you measured only response rate, someone could reduce the ask string and lower the heck out of the amount of the ask to spike response rates. If you focused solely on gift amount, you could cherry pick leads and change the ask string to favor higher gifts. Put together, however, they give a good picture of the response to a piece.

Net income versus file health: Anyone could hit their net income goals by not acquiring new donors. More on this another time, but suffice it to say this is a bad idea, possibly one of the worst ideas. Likewise, an acquisition binge can increase the size of a donor base very quickly but spend money even more quickly.

Cost per donor acquired versus number of new donors acquired: If you had to design a campaign to bring in one person, you could do it very inexpensively – probably at a profit. Each successive donor because harder and harder to acquire, requiring more and more money. That’s why if only cost is analyzed, few donors will be acquired, and vice versa.

Web traffic (sessions or unique visitors) versus bounce rate: Measuring only one could mean many very poor visitors or only a few very good visitors. Neither extreme is desirable.

Click-through rate versus conversion rate: If only your best prospective donors click on something, most of them will convert. More click-throughs mean a lower conversion rate, but no one should be punished for effectiveness in generating interest.

List growth versus engagement rates: Similar to Web site metrics, you want neither too many low-quality constituents nor too few high-quality ones. Picture what would happen if someone put 1,000, 10,000, or 100,000 fake email addresses on your email list. Your list would grow, but you would have significantly lower open rates and click-throughs. Same with mail – as your list increases, response rate will go down – you need to find if the response rate is down disproportionately.

Gross and net revenue: Probably don’t even need to mention this one, but if you measure gross revenue only, you will definitely get it. You will not, however, like what happens to your costs.

Net revenue versus ROI: Usually, these two move in concert. However, sometimes, additional marginal costs will decrease ROI, but increase net revenue per piece as in the example yesterday. In fact, most examples of this are more dramatic, involving high-dollar treatments where high-touch treatments increase costs significantly, but increase net revenue per piece more. A smart direct marketing will make judgment calls balancing these two metrics.

Net revenue versus testing: This is clearly a cheat, as testing is not really a metric, but a way to increase your revenue is not to take risks, mailing all control packages, using the same phone script you always have, and running the same matching gift campaign online that you did last year. Testing carries costs, but they are costs that must be born to preserve innovation and prevent fatigue in the long run.

These are just a few of the metrics to look out for, but the most important part of this is that any one single metrics can be gamed (whether intentional or un-). One of the easiest ways to avoid this is thinking in the extreme – how would you logically spike the metrics. From there, you can find the opposing metric to make sure you maintain a balanced program.

The basics of direct marketing reporting – part two

November 10, 2015November 19, 2015 nickellingerdata appends, data presentation, Excel, metrics, reportingLeave a comment

Yesterday, we talked about the key metrics you want to look at in Excel – 13-14 indicators that speak to you about progress and testing results.

However, a direct marketing Muggle will look and these data and say “Huh. Interesting.” This is direct marketing Muggle code for “this is not interesting and it makes me think of my Algebra II class, which was taught by a nun.”

While you will want all of the data, you will want a skinnier, clearer chart for others, preferably with colors that call out what is actually important. Let’s look at a fairly standard test – your thesis was that extra personalization in the letter would increase average gift versus your control. Here’s what this could look like:

The first thing to notice is that your hypothesis was wrong – average gift didn’t go up. But now you have another decision – should you pay for the additional personalization in the future?

You, as a direct marketing professional, can read this chart. The increased personalization caused response rate to increase. As a result, gross income per piece went up and net income per piece went up. However, return on investment went down; the additional investment didn’t bring in as much as the investments before it. What would you recommend to your boss?

This is a judgment call based on your goals for your program. One good approach would be to call for a retest – possibly with even more personalization or to see if you can get the personalization costs down or different ask strings to try to boost average gift. This is clearly not in the 95% percentile one way or the other (which are other good fields to add to your spreadsheet when you get more advanced), so more testing would be good.

But I know which one I would mail more quantity of when the next test is done – I would use the personalization version as the control. For me, net per piece matters more than ROI. Our donors’ time is a scarce and valuable commodity. There are only so many times you have the opportunity to get in front of them, so if you have the opportunity to maximize their investment of their time, versus trying to go for cost control in borderline cases like this one.

Charity Navigator would disagree with me, as they focus on cost of fundraising, so that’s another point in my argument’s favor. Remember the Charity Navigator Constanza test – hear what they have to say and do the opposite and it will be to your benefit.

So now you have your course of action. Now you have to have other people see it your way. Time to explain it:

The first thing to note is that it’s legible. The second is quantity and absolute gross, net, and cost numbers are gone. These don’t have any relevance to the decision over what to roll out with. If you leave them in, there’s a natural human temptation to think biggest = better, especially when it’s called revenue and has a dollar sign in front of it. For a layperson, it’s good to eliminate these distractions.

Then we’ve color-coded the winning parts. Control wins on cost; test wins on response rate and ROI, gross income and net. This helps draw attention to the salient bits. It is amazing how much these little steps can help focus minds.

You will note that I left ROI in there, even though it is evidence that does not support the case you are trying to make. I’ve talked about testing as a central commandment on the direct marketer’s tablets. But testing is nothing if there isn’t intellectual honesty. You have to make the case, but also give your team all of the information to challenge you and make your arguments better.

This is usually where the aesthetic marketers get us data-driven marketers. They tell quality stories based not on what is true, but on what we wish were true.

We must become equally good storytellers, because a good story plus data beats just a good story. On Thursday, I’ll talk about how to present data in a compelling way, but first, we have to figure out how to measure our metrics.

The basics of direct marketing reporting

November 9, 2015November 12, 2015 nickellingerdata analysis, data presentation, Excel, metricsLeave a comment

So there have been some unjustified slaps at Excel over the past week, as well as against hamsters, Ron Weasley, and the masculinity/femininity of people named Kris. (The one against Clippy was totally justified.)

It seems only right, then, to talk about things that Excel is actually good at – doing calculations and presenting data.

There are two general schools of marketing people: art versus science. The art folks appreciate the aesthetics of marketing and aim toward beautiful design and copy. They will talk about white space and the golden ratio and L-shaped copy and such. They elevate fad into trend into fashion. They were responsible for the Apple “1984” commercial and don’t understand why the guy with bad toupee on late night commercials is really successful. They can read the nine-point font they are proposing for your Website and don’t care if it is actually usable.

The job of the science people is to make sure that these people don’t damage your organization too much.* Our motto is “Beauty is beautiful, but what else does it do?”, or it would be if we started having mottos. Our tools are the well-designed study, the impertinent question (e.g., “I understand that our brand guidelines say to use Garamond, but our testing shows Baskerville converts better. Would we rather stick to the brand guidelines or raise more money?”), and the clear data presentation.

This last one can be hard for us. Too often, when we present our data, the data goes up against a beautiful story that people wish was true and loses.

So we need to cover not only what data you want to collect (today), but how to present it compellingly (tomorrow).

A standard Excel chart for mail pieces

The things I like to see, in approximate order, are:

Enough things to identify the piece/panel/list
Quantity mailed
Response rate
Number of donors
Average donation
Gross revenue
Cost
Net revenue
Gross per thousand
Cost per thousand
Net per thousand
Return on investment
Cost to raise a dollar

That’s for a donor piece; for acquisition, I’d recommend adding cost to acquire.

So that’s what data to collect; tomorrow, we will look at how to present it.

* I am framing this as a battle largely for dramatic purposes. Ideally, you have a data person who respects the talents of a high-quality designer and a designer who likes to focus on what works. These together are stronger than any one alone.**

** But if you have to pick one, pick the scientist.

Semi-advanced direct marketing Excel statistics

November 6, 2015November 5, 2015 nickellingerblog, Excel, Facebook, social media, statisticsLeave a comment

In addition to not being a database, Excel is also not a statistics package. If you are going to do anything advanced, I highly recommend R. The programming language, not the John-Cleese-played Q replacement in The World is not Enough and Die Another Day.

Cheer up! Yes, it’s a lesser part in lesser Bond movies.
You, John Cleese, are still an international treasure.

Anyway, stats in Excel. We’ll start with correlations, as they can give us some insight into blog and Facebook traffic and interactions.

Wait, you argue – Facebook is not direct marketing. First, yes, you are correct. Second, no, you are wrong; there is a way that you can use Facebook as a direct marketer. I’ll talk about this more when I do a whole social media week (don’t worry, folks, I promise to spend time deflating the hype and hopefully producing things you can print out and get to board members and say “See? Let’s put money in places where it will make money!”). Third, because Facebook has limited value (but not zero) as a direct marketing vehicle, you can test things on there to see how they resonate with your audience. Granted, your Facebook audience and direct mail audience will probably be fairly dissimilar, but your online audience is probably similar to your Facebook audience. And what you are looking for is what makes compelling online content for you. So this is a way to make Facebook your testing ground before you put it on to a real platform (i.e., your Web site, your emails).

I’m going to demonstrate this on this blog’s stats because I have the data available. However, I’ve also done this with Facebook posts very successful. The prep work you will have to do for either blog posts or Facebook posts is to record your outcomes (view, likes, shares, and/or comments), to code the subject matter of each post, and to put in any other variables like day of the week that may be relevant. Here’s my version of this:

I have my blog posts on the left and the various factors on the right (and there are more tags, but I need to cut it off at some point to display it. Yes, I have a blog post that has zero views. If you would like to break the seal on a blog post about why we do segmentation, it’s here; I’m sure it would appreciate a visit.

I then went through this and deleted tags that only applied to a single post. Then I ran a correlation for each individual variable to page views. The correlation function looks like

=CORREL($B2:$B20,I2:I20)

And is expressed from 1 to -1.

Here’s what that looks like. Let’s look at the days of the week first, because there appears to be an effect here — Monday content has been king, with a strong correlation to page views. It will be interesting going forward to see in the long term whether that is the nature of the content (I’ve been trying to put introduction content on Mondays and get progressively more involved throughout the week) or that people are more interested in reading blog posts on Mondays. Nevertheless, I can probably do a better job of setting up the rest of the week as must-see content, since Tuesday, Wednesday, and Thursday are all a bit negative.

Images tend to correlate well to views as well, probably because they show better in social media. I’d been noticing this from just a glance as well, so you will probably be seeing more images here in the weeks to come. They will also be less boring images, since some of the lower performers were images of equations and Excel sheets. It is not coincidence that the tallest Python led this blog post.

And it looks like cultivation and multichannel efforts are winning while conversion, lifetime value, and personalization are not as strong, with negative correlations to page views. I won’t be acting on this immediately, but keeping an eye on it. And I do have a multichannel week planned in the near future, so we’ll be able to test whether that’s an artifact in the data.

However, you might notice that the reason cultivation is ranked so highly is that it is in the two top performing posts, which are Monday posts. Is it the topic or the day that made those strong? For this, you need regression.

Normally, you wouldn’t do this after only 20 blog posts. We are not going to be able to draw any statistically significant conclusions, but I do want to show you how it’s done.

Go to Data > Data Analysis > Regression
Select the range from your outcome variable as your Y range and the range of the independent variables you want to test in the X range like so:
Hit OK. You’ll get something that looks like this. In my case, it’s a really, really bad regression:

Yuck. The things you would normally be looking for are:

in R-squared, you are looking for as close to 1 as possible. One would mean your model is totally predictive. Zero means it predicts nothing at all.
In P-value per variable, you are looking for less than .05. That would show if there is a statistically significant relationship between any of the variables and your output. In this case, there isn’t and we can pretty much throw out the whole thing.
If there is a relationship, you want to look at the coefficient for two things:
- Is it positive or negative? Positive is good things; negative is bad things.
- How big is the relationship? In this one, if these were significant, it would be a bad idea to post about personalization again, as posting about it reducing views by 7. But it isn’t significant, so I’m not yet worried.

Hope this helps you with the stats side of Excel. Tune in next week, when we look at some of the things that Excel is actually good at.

Appending data in Excel

November 5, 2015November 6, 2015 nickellingerdata appends, data hygiene, database, ExcelLeave a comment

One of the primary reasons to have a database is to have data in various tables. A simple example is a database with a people table – a list of your donors – and a donations table. This allows for there to be a one to many relationship between the donors and donations. After all, a good donor will generally donate multiple times. By using a key in the people table, each donation is marked with that same key – e.g., all donations from donor 12398 have 12398 in their donor field and are associated with that record in the people table.

Excel can’t do this. As a flat file, all you can represent are one-to-one relationships. If you had to have multiple gifts in Excel, you would have duplicate donor records for each instance of the gift.

But you can append data to a record in Excel.

A while back, I wanted to do some homemade modeling on a file to add sex in as a variable (male/female in this case, to simplify the model). Unfortunately, I didn’t have a list of who was male or female in this particular file, so I used US census records to determine how many males and females there were with a given first name. This led to the odd conclusion that someone who had a name that 60 females and 40 males have in the US is a 60% woman. Thank goodness I was using this for modeling and not for assigning Mr. or Ms.

By the way, if you were wondering if there were any perfect half-female, half-male names, there were three: Ariel, Hong, and Kris. Sydney, Robbie, Frankie, and Kerry were almost androgynous, just missing the cut. The Excel sheet is here if you are curious.

To complete the model, I had to put the percentage of femaleness with each record. I used a somewhat fiddly function called VLOOKUP.

VLOOKUP tells Excel to look through a bunch of rows to find a particular value in the left column of an array, find it, and return another column in that array. To wit:

=VLOOKUP(A1, ‘[Male v Female first names.xlsx]Sheet1’!$A:$B,2,FALSE)

This assumes that A1 is the first name you want to look up. This then goes over to the document called Male v Female first names and looks at the first two columns, A & B. It searches for the name in column A. If it finds it, it returns back the value in B next to that value. So, if A1 is Hong, it finds Hong in the array, sees the value next to that is 50%, and puts it in the cell in question. The FALSE at the end just means you want an exact match, rather than the closest one.

It is by no means as pretty or efficient as a SQL JOIN statement. In fact, if you have a lot of these to do, as I did, copy and paste it for the full column, then get yourself some tea and a crossword puzzle while Excel labors like a hamster on a wheel. But, on the plus side, you don’t have to learn SQL. You still should, and we’ll cover SQL for direct marketers at some point, but you can’t learn everything immediately.

A brief instructional video about the benefits of data hygiene

November 4, 2015November 3, 2015 nickellingerdata hygiene, databaseLeave a comment

(Billy is walking to school. He is in black and white, but mostly white, between is grey blond hair, grey pale skin, grey khaki pants, and white white button up shirt. Only his shoes show black – certainly not his small town.)

Narrator: Woah. Hold on there, Billy.

(Billy stops and looks for the voice).

Narrator: Where you going in such a hurry?

(Billy holds up the books he is carrying and taps on his watch with a finger)

Narrator: Ah, going to school. And I suppose you haven’t even given a thought to DATA HYGIENE?

(Billy pauses, has a quizzical look, and proceeds to sniff his armpits.)

Narrator: No, not that type of hygiene. DATA hygiene. One of the things that makes America great (cut to stock footage of a flag waving and other good and wholesome things) is Americans doing positive, American things for other Americans, helping our neighbors, our pets, and our environment. For example, elderly people! Puppies! Parks! All of this is possible because of our positive, American civic associations of good people.

(Cut back to Billy nodding enthusiastically. If Billy were tragically stuck inside a paper bag, he could not act his way out)

Narrator: But what if ALL OF THIS CAME TO AN END? Were there no data hygiene, our civic associations won’t be able to help preserve our positive American way of life? They wouldn’t be able to reach new volunteers, get voluntary donations, or help out in your neighborhood.

(The previous images of an elderly person, a puppy, and a park, all disappear in rapid succession)

Narrator: THIS WOULD LEAD TO COMMUNISM!

Billy: Holy shit!

Narrator: Wait, you can talk? I thought we had you muted. Why didn’t you talk before?

(Billy shrugs.)

Narrator: There are two types of data: data that you control and user-entered data. Let’s take a tour of data you control.

(Billy is whisked away to an average city street)

Narrator: Here is the hustle and bustle of a nonprofit organization’s traffic. All sorts of data come through here on a daily basis, from caging vendors to development officers to syncs with other databases. And we can control these with some basic community standards. But you have to make sure these community standards are followed, from address 1 line standards…

(close-up on the street sign, which magically changes from Mockingbird Lane to Mockingbird Ln)

…to address line 2 standards…

(close-up on an apartment, labeled as 123 Main Street, Apartment 3-B, which changes to
123 Main St
Apt 3B)

…to pick lists that dictate what titles and suffixes can be used.

(A mailbox that says Reverend Timothy Lovejoy, III, changes to Rev Timothy Lovejoy III).

Narrator: It’s better that these things are cleaned up in the database…

(Billy opens a door and see workers in a dank, grey office working on various spreadsheets. A supervisor is whipping them if they don’t clean lists fast enough. He recoils in horror.)

So that hard-working Americans don’t have to fix them later. Fortunately, you have professionals doing data entry here, so that should limit duplication and errors. The same can’t be said for…

(Click. Billy is instantly transported to a seedy red light district. Crude graffiti adorn the spare brick walls and trash lays, some in bags, some not along the street).

Narrator: USER GENERATED DATA!

(A scantily clad woman of the night beckons to Billy. He starts to walk over)

Narrator: Billy! What do you think you are doing?

(Billy stops and gives the camera a sheepish shrug).

Narrator: Here in user-generated data, anything goes. In order to get people to fill out forms, you have to make it very easy. Most of the time, that means leaving out items on the form…

(a metal mailbox is embedded in the wall. It says simply “Jim”.)

… not validating standard fields or following capitalization rules…

(the neighbor’s mailbox is labeled “mt. cHris hartnet, mbs”

… having people put data wherever…

(the camera pans up to reveal a steel mill in the background labeled “Dr. Alice Humphries, DDS”)

… and just plain having jokers plant fake information.

(the building next to the mill is labeled “I. P. Freely.” Billy looks frightened.)

Narrator: Yes, this lack of cleanliness is the price you pay for freedom. American freedom! But we all have to do our part, or else the civic organizations won’t be able to reach anyone in this neighborhood. And you know what that means, don’t you Billy?

(Billy nods and mouths the word “communism”)

Narrator: That’s right. Here we have to use more broad hygiene techniques. Enforce your capitalization rules, not theirs. Every one of these houses should be run for address standardization, so the addresses are correct. They should also go through strict National Change of Address forms, so you know when someone has moved. If someone’s email address doesn’t work, make sure to suppress it or you will be labelled a spammer. And you know what happens to spammers.

(The camera shows a man in a stockade. Residents are throwing cans of Spam at him.)

Narrator: Appending data like telephone numbers, addresses, and email address when you don’t have them is also usually worth the investment. That way, you know if Jim back there is the same Jim who just wrote your nonprofit a big check.

(Click. Billy is back on his school grounds.)

Narrator: So, Billy, you know the importance of data hygiene, right?

(Billy nods enthusiastically and runs off camera)

Narrator: Data hygiene: it’s the right thing to do. America.

Deduplication 2: Deduplicate harder

November 3, 2015November 2, 2015 nickellingerdata hygiene, database, dedeuplication, ExcelLeave a comment

The deduplication exercise from yesterday talked about how to deduplicate clean, easy-to-use data. If you ever find data like that, please let me know.

In reality, there are always quirks in the data to work around. Here’s how to solve a few of them.

The middle name conundrum. There is no good way to deal with middle names in a database, because the way people use them is non-standard. For some people, a middle name is never used and sometimes a throwback to a bygone ancestor (e.g. ,Ronald Bilius Weasley). For some, it is an integral part of the first name (e.g. Mary Kate and Ashley Olson; that is just one long name, right?). Others skip right over the first name and go right to the middle (e.g., F. Murray Abraham). This is why a good database will have a salutations field you can fill in, so your letters go to Ron, Mary Kate, and Murray, and not Ronald, Mary, and F.

This, however, makes deduplication a bear. You want to preserve the original first name, but need to manipulate the data:

Insert two columns next to your first name row (more if you think people will have three or more names in the first name field)
Copy (Ctrl + C) the first name column into the first empty column.
Highlight the column and go to Data > Text to Columns
Choose Delimited and hit Next
Choose Space and hit Finish

This separates the names into separate columns. Dedup as normal based on these fake name columns, then delete these columns and you have clean(er) data.

Forms of names. An automated system won’t be able to tell you that Robert and Bob are the same person, but you can figure out close matches of names like Robert and Rob. The trick here is when you deduplicate for first name to put

=LEFT(A2,3)=LEFT(A3,3)

Instead of

=A2=A3

This compares the three first letters of cell A2 to the first three letters of cell A3. This solves your Rob/Robert’s and Nick/Nicholas’s of the world. If you want to be a little less restrictive, you can do two instead of three and solve for your Nicole/Nikki’s and your Jo/Joanne’s.

This handy trick also works well for addresses. Generally if the first 4-5 digits of the address are the same, they are close enough for government work (but still catches them if someone put Rd instead of Dr).

Adding as you go

Because Excel is a flat file and not an actual database, you will frequently have to sum gifts to get a total amount of giving. Let’s go back to our initial email list, now with gift amounts and the middle names broken out. Columns F, G, and H, are set up to compare A, C, and D.

Here’s how to proceed:

Set up column I with the statement =AND(F2,G2,H2) (and copy it for all of the rows). This returns TRUE if all of the columns are TRUE and FALSE if any are. This puts in one cell whether something is a duplicate or not.
Set up the first row of column J with the statement =IF(I1,J1+E2,E2). This is the trickiest bit. What this is saying is “Look at the row before this. If it’s the same as this row, add this donation to all of the previous donations that match this record. If not, just put in the one gift.”
Copy this column and paste values like you did with the TRUE/FALSE earlier.

Now, when you deduplicate, you’ll be getting the total amount of gifts for a donor, summed up.

There are certainly not perfect. But to do perfect, you need a database.

Direct to Donor

Direct marketing tips for the modern nonprofit

Menu

Month: November 2015