Building a query to learn who has never opened an email in NetCommunity

Kathleen Duis

Has any one found a way to build a query in RE that will tell you who has never opened an email sent through NetCommunity? I'm able to build one that tells me 'These people have not opened at least one of the emails that you have sent.' but I can't figure out how to build one that tells me 'These people have not opened any of the emails that you have sent.' 



I know there isn't an easy to do this since NetCommunity isn't an option under summary information so it has to be built from scratch. I have built a query for a specific constituent group with the criteria of Email Name one of (each) {every email this group would have received} and Email opened? equals No but RE tells me that query is too complex. 



Any other ideas?

 

Gina Gerhard

Kathleen - Well this is really weird because literally just now I was working on EXACTLY the same thing.

• This was the first time I'd ever had this request from staff to look at a group of emails that were sent out and find only the people who did not open ANY of them.
• I tried a number of things using query, including creating a query for each email separately (which did seem to yield the correct results) but then I couldn't get any of the 'merge' operators to give me the correct result.

I actually ended up with this approach:

• Create query for each email (anyone who received the email) and output constituent name, ID, email name, and opened (true/false)
• Exported the results of these queries into a single spreadsheet. (I colored each grouping of emails so I could see better what was going on).
• Then sorted by name so each of the email results were grouped together.

I thought this way made it much easier to see what's going on and make sense of the data. But you then have to play with your Excel and delete out data manually.

• For anyone who has a TRUE value in the email open field - this means they opened that email so I would then delete out ALL the emails under their name ( since I only want people who didn't open ANY of them).
• When I'm done, I'll have a true representation of only those people who didn't open ANY of the emails.
• I can then use the IDs and create queries back in RE for my group.
• There might be some function in Excel to make this 'tweaking' easier but my list wasn't all that long, so I just did it manually.

Seemed like RE should be able to do this but I wasn't able to figure out any other way ---

 

Matt Page

Gina,



You might find the "Or" function in Excel helpful with that. I just discovered it recently. Basically the formula is =OR(A1,B1,C1,D1) and if any of those cells are "TRUE" it will come back with the value "TRUE".



Matt

Kathleen Duis

Gina, 



Thank you for passing along your step-by-step notes. We are looking to use online engagement as a segmentation factor so your system will be quite useful overall. 



It shouldn't be this difficult to get summary information for online engagement. 



Best,

Kate 

Gina Gerhard

Kate - I totally agree it shouldn't be this difficult so might be worth putting an Ideabank suggestion in (if you or someone else hasn't already) and asking for votes.



I'm curious, in Events for example can you structure a similar type of query?

• Ex: give me all the constituent who registered for one of these 5 events and attended = No
• Can you find the constituents who did not attend ANY of the events?

Kathleen Duis

There is a suggestion already. If you search for 'More ability to query emails sent through NetCommunity' it will come up. I wish I could find a permalink for it. 



We do not use the Events module so I can't speak to your question about querying in events. 

Kate 

Gina Gerhard

Thanks but cannot find in the BBNC community using that text string.

If anyone else finds it and can provide a link, I'd like to vote on it!



 

Holly Herbert

Hey Gina! The BBNC community should be live on this community platform on Monday and accessible through our top "Software and Services" menu.



If you need a faster response than Monday, you can find the current NetCommunity community here.