Wednesday, July 27, 2005

Reminder -- send a reminder?

I've finished comparing the read receipts from two e-mail announcements. The first went out two weeks in advance, letting people know that a new online tool was going to launch on June 27. The second went out on June 27, telling people it was up and ready to use. Here's the time chart for the second message -- it looks very much like the first one.

Again, 60 percent of the e-mails were never opened; that's the bad news. The good news is that people who open the e-mails open them fast. Nearly 80 percent were opened on the first day; more than 90 percent by the second day; and more than 95 percent by the third day. Leaving aside the question of whether they read or absorbed the message, they did open it.

Was the reminder worth it? I think so. Here's a breakdown that shows how many people opened each message, with the percent of the total audience (1910 addressees):
  • Opened the first but not the second - 223 (12%)
  • Opened the second but not the first - 240 (13%)
  • Opened both - 551 (29%)
  • Total opened - 1,014 (53%)

The reminder e-mail accounted for 24 percent of the total opened e-mails.

The data shows 5 percent of the recipients deleted both messages without opening them. It may be interesting to know if they are doing that soon after the mailing -- that would indicate they just aren't taking our calls. But it's just 5 percent and there are bigger fish to fry.

I've still not absorbed the full impact of this data -- half of these e-mails are never opened. There are a couple of things that appear evident:

  • It looks like this particular corporate mailbox has a lousy brand. Readers don't expect value from its messages.
  • E-mail is fast, cheap and easy but it's not reaching a big segment of the population. We need to find another powerful vehicle that will appeal to the e-mail averse.

The basic data here -- e-mail never opened -- is ridiculously easy to gather. Anyone else going to try it?

I'm going to do similar experiments on mail from senior leaders to see how that changes the rate of mail opened.

Tuesday, July 19, 2005

Did I already say that I'm not an Excel expert?

I'm working through some of the read receipt stuff, which I'll post soon. However, I got some help from someone who really knows Excel (thanks, John!) and I wanted to pass on a couple of things now.

Like before, I described grabbing the little black box on the lower right hand corner of a cell and pulling it down to copy a formula. Well, turns out you can just double-click that little black box and the formula automatically copies down, as long as there are filled cells in the column to the left.

Secondly, I'm using something called a vlookup in Excel to compare two lists. Since I don't want to write an Excel Users Manual here, I'm not sure how much of that process I'll be posting. When the time comes, let me know if you need the details and we'll figure something out.

One other thing John mentioned -- Outlook allows the e-mail receiver to turn off the read receipt function from their end. How common is that, and will it mess up the data? We don't know, so that's something else we'll have to look into.

No one said this stuff was easy. It's just better than communicating in the dark.

Monday, July 11, 2005

No (big) surprises.

I already sprang the big surprise on the read receipt data -- most e-mails in this mailing didn't even get opened. Here's a chart over time of the e-mail opened, deleted without being read, and those which have still not been opened or deleted.

If you can't read it, the blue are opened e-mails, the Burgundy are e-mails deleted without being opened, and the yellow are still rotting in someone's e-mail box. You can see the launch day there on the far left. After two days most of the e-mails that were ever going to be opened were already opened.

The opened e-mails settled in at about 39 percent after eight or nine days and just stayed there. Deleted, unopened, grew a bit but there is certainly no mass exodus to clean out old e-mails. This particular e-mail was about a new on-line tool going live on June 27 -- just above the righthand corner of the legend there at the bottom.

We did a reminder e-mail the day the tool went live. I'll post that next time. Then I'm going to compare the two lists to see if the same people were e-mail openers, deleters and ignorers. I want to see if the day-of-launch e-mail reached new people or just reminded the same group that opened the first announcement.

Tuesday, July 05, 2005

Well, I feel kind of stupid here, continuing to post when no one has replied. I guess I need to be more controversial.

Or, it could be that the comments of all my readers have been secretly deleted by activist judges! I wonder...

OK, back to the great read receipt research. I want to share a couple of steps I skipped -- believe it or not -- that will come in handy as you're trying to clean up your data.

First of all, the lists of e-mail receipts and e-mail recipients may have some duplicates in them. If, like me, you use group mailing lists, or do multiple mailings, some people are going to get the message more than once. Those should be cleaned up before doing the final analysis to remove some of the error that is going to creep in. Here's how I do it.
  1. I have all the recipients, in Column A in MS Excel. In the previous post I indicated that there were 1927 names there. I'm going to filter out all the duplicate names.
  2. I click on the column header ("A") to select the column, then go to Date>Filter>Advanced Filter.
  3. Click "Copy to another location" -- otherwise the duplicate results are just hidden and can mess you up later.
  4. Create a new list range where you want the filtered records. I generally use column B on the same page, making sure it's empty, of course.
  5. Click "Unique records only" and then "OK." Column B will now have the de-duped list. In my case, I now show 1904 unique recipients instead of 1927.

With this many records, a few duplicates are not likely to change the results dramatically; with smaller samples it becomes more important. However, it's a great sniff test no matter how large your sample. If half your data disappears, you know something went wrong somewhere.

Sometimes your data will be hard to sort and analyze due to extra spaces in the records, especially at the front of a cell. Excel will not recognize that two entries are the same if one has a space or two before it and the other doesn't. For this problem, there's a great little function called Trim.

  1. Start like we did above, with all your data in column A.
  2. Make sure column B is empty, then select the first cell in column B that is next to your first entry in column A. (If A4 has your first entry, select B4.)
  3. Go to Insert>Function
  4. Type "trim" in the "Search for a function" field to find Trim. (If it doesn't come up, make sure "Or select a category" is set to "All.") Click "OK."
  5. In the new dialogue box, it is asking which cell you want to trim. Click in A4, then click OK.
  6. Now, B4 should be the same as A4, with any extra spaces removed except for one space between words.
  7. There are a lot of ways to extend this same formula to all the cells in column B so the whole list is trimmed. Here's what I do: I hold my cursor over cell B4, over the lower righthand corner of the cell where there is a black square inset in the black border. When my cursor turns from a white cross to a black cross, I click my left mouse button to grab it and just pull it straight down. It will fill all the cells as I go, until I release it.

In my next post, I'm going to look at the timing of the read receipts. Here's why:

  • I want to know if the percentage of people who open the e-mails changes as time goes by. Do people keep old e-mail to read or just to eventually delete it? (At home, when my wife puts aging fruit in the refrigerator, I call it "the fruit hospice." Fruit goes into the refrigerator not to eventually be eaten, but to die out of sight so it can be decently thrown out. Is unopened e-mail more than a week old essentially in hospice?)
  • What's my window for readership? At what point can I expect to have reached, say, 80 percent of those who will ever read it? That will tell me something about how far in advance of a deadline I should be communicating.

I'm now collecting read receipts for more e-mail communications so I have a larger data set on which to base my conclusions. Try to contain your excitement.