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.
- 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.
- I click on the column header ("A") to select the column, then go to Date>Filter>Advanced Filter.
- Click "Copy to another location" -- otherwise the duplicate results are just hidden and can mess you up later.
- 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.
- 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.
- Start like we did above, with all your data in column A.
- 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.)
- Go to Insert>Function
- 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."
- In the new dialogue box, it is asking which cell you want to trim. Click in A4, then click OK.
- Now, B4 should be the same as A4, with any extra spaces removed except for one space between words.
- 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.