Constant Contact: How to Create List of People Who Never Opened an Email - Success!

Constant Contact is one of the bedrock platforms for non-profits to community with their supporters.  It has tremendous advantages, which outweigh its limitations.  However, there are several limitations, particularly in how you can export data.  For example, if you wanted a master list of all your contacts and their response to all your e-blasts (did they open, not open, bounced, etc), that is virtually impossible.  This bothers me because it is the organization's data and they pay for this service.  Recently, I wanted to know who on my list has never, ever opened any of our emails.  If people aren't going to open the e-mails, than why bother sending them.  I searched the web and couldn't find the answer, but saw that many people has the same issue as me.  

Here is a method that I just figured out.  It takes about 15 seconds per campaign, so if you have hundreds, this might take a while.  I had 70 blasts and the entire process (all the steps) took me only about 13 minutes.  This might seem like a lot of steps, but it's it pretty easy.

Step 1) Go to your list of Campaigns and create a new one called "Ever Opened" (or whatever you want).  You could even do this by year, if you wanted.

Step 2) Click on the Campaigns icon and a list of your most recent ones will come up.  For each one, right click (open in new tab) the percent number above "open rate".  Creating new tabs for each one is much quicker. 

Step 3) For each tab, under contacts, select the first box (to the left of where it says "name") and this will auto-select everyone.  This will also make the "manage list" option appear.  Click "manage list", than "add to list", and select your new group (which I called "Ever Opened").  Repeat this for all the tabs you opened.

Step 4) Go to your main contacts page, which provides your entire list on contacts.  Select all, click Export, than Export Contacts. Note: Leave the default export option alone, do not select or unselect anything new). Click on "Activity" on the upper left to go to the actual reports.  Download it to Excel. Note: you do not need to download the "Ever Opened" report, just the master list.

Step 5) Open Excel.  If you stayed with all the default setting, column F should be called "email lists".  I'm pretty good with Excel formulas, which is how I sorted everything.  In box G2, put in the following formula exactly "=SUMPRODUCT(--ISNUMBER(SEARCH({"Ever Opened"},F2)))>0" Note: you don't include the quotes, just everything inside it.  Note: If you decided to call your new list something else (not Ever Opened), just replace those words in the formula with the name of your list.

Step 6) Click on a random box and single click back on G2.  You will notice on the lower right hand side of the box is a little square.  Click and hold on that little box, and than drag your mouse directly down until it goes to the button of your list.  

In the G Column, if it says TRUE than that person has opened an email.  If it says FALSE than that person has never opened an email.  

Step 7) Sort the list and delete all of the "TRUE" people.  This will leave you with only people who have never opened any email.  If you want to delete all these people at once in Constant Contact, do the following.  Save the Excel file with the Never opened people.  Go back into Constant Contact, go to contacts, click "add contacts" and than "upload from file".  Select the Excel file we just created and upload.  Click to create a new list, which you can call "NEVER Opened".  Once uploaded, you can delete everyone on this list at once.  Ta-Da!