Wednesday, June 29, 2005

I was writing about using read receipts to measure the effectiveness of e-mail messages. The first step was getting the information into Excel, a task that looked easy, and then got a lot tougher. Here's why.

These response receipts come as an e-mail in three format flavors:

  1. Employee name / Read: Message Title /Date-Time Received / Size
  2. Employee name / Not Read: Message Title Date-Time Received / Size
  3. System Administrator / Message Delivered / Date-Time Received / Size
First, I just tried the obvious easy way -- I copied all the messages in the folder where I'd stored them and tried pasting them into an Excel spreadsheet. It worked! The e-mail headers pasted neatly into columns with the same labels as they have in Outlook: From / Subject / Received / Size.


I tried again later and it didn't work. All the message title information was in a single cell and there were no delimiters that could be used to separate the text to columns. I killed myself trying, finally figuring out how to select and export the data as an Excel file (In Outlook: File>Import and Export...>Export to file>Next>Microsoft Excel>Next>Select Folder to Export From>Save Exported File As (Browse)>Next>Finish).


I figured it out today. The key is to sort the e-mails by Subject in Outlook. If you do, you can simply cut and paste them into Excel. I then did a find-and-replace to cut out the title so I just had the names, whether it was read or deleted without being read, and when that happened.

OK, what does the data reveal?

Out of 905 read/not read replies, 85 percent had been opened. Just 15 percent were deleted without being read. Sounds pretty good, though we know what we don't know -- did the people who opened the message read and/or absorb the message.


What about those other responses -- the third "flavor" above. Turns out those are important. They show who actually received the message. You may also have the same information from your distribution list. Anyway, turns out my message went out to 1928 addresses. Now the numbers are not so hot -- 40 percent opened it, 7 percent deleted it without opening it, and a whopping 53 percent have yet to touch it.

Next time, an even deeper dive.

No comments: