How do I query to filter out recipients who have not opened the last 5 emails they received?

[thesse1393]

I'm trying to remove targets from my workflow if they have not opened the last 5 emails we sent them. How would I do this?

[davidl14970702]

You could just use a split (based on the tracking logs table) to split outs people based opening those 5 internal names. Use AND statements in the split for the internal names. The complement generated will be your list of people that have not opened.

[thesse1393]

Thanks, but each of my users would have received different emails, so can't use internal names or email category to do this. Is there a way to identify recipients who didn't open the last 5 emails we sent them? Screen shots of steps would be so helpful.

[Garima_Gakhar · Accepted solution]

Hi @thesse1393, thessey​

I found one approximate solution to this, you could try once to see does that map to your requirement.

1. Create a Workflow with Query Activity for BroadLogRcp, where Targeting Dimension is equal to broadLogRcp (Recipient delivery logs) and Filtering Dimension is equal to Recipient tracking log.

2. Define the Filtering Condition as: Type (URL) not equal to Open.

3. Then link this Query Activity to a Deduplication Activity, you could choose to generate a Complement if required.

4. Within the Deduplication Activity, Edit the Configuration as below:

a) Targeting Dimension -> Temporary Schema

b) Identification of Duplicates -> on the basis of an expression for 'Recipient Email'

c) Deduplication method -> Doubles to keep: 5

5. Once the 'Result' is out, you could configure List to validate that indeed each of those Recipients which have been ranked for '5' have not Opened or Clicked any of the latest Deliveries.

6. So I verified this by checking on 2 records returned from the Deduplication Activity Results.

7. I checked on these two Recipients records within the BroadLogRcp Schema to fetch details of the last 5 Deliveries.

8. I then checked on Delivery and Tracking Logs of these Deliveries, emails were indeed sent to these Recipients but there was no Tracking Log associated with it.