This query has no results, but when I check the data in Excel it should.

Shabbir Imber Safdar

At the Marine Mammal Center we're building email marketing strategies around a number of queries that segment our list into specific behaviors, such as "people that open and click our emails, but never give".   We've used engagement factors (1-6) to populate the data about emails received, emails opened, emails clicked, gifts, e-retail purchase, and teamraiser gifts.

 

When I download the data into excel, I can confirm that these engagement factors have been populated correctly.  Here's a short sample of that:

 

for that first email address, she received 88 emails, opened 78, clicked on 5, but had no gifts, ecommerce, or teamraiser activity.   I want to isolate people like this into a group, so I built this query:

I then ran it, and got no results.

I then double checked the data in excel with this formula, which I believe it equivalent to the query:

 

As expected, this finds about 3,400+ email addresses that fit this criteria.   So why doesn't the query find it?  Has anyone else seen this kind of behavior with queries?  

 

-Shabbir

Kent Gilliam

Try putting your first 3 criteria in parenthesis and the last 3 in parenthesis. I know it shouldn't make a difference but sometimes queries just have too much dynamic content requests that it can't process it. Let me know if this works.

Noah Cooper

The problem is the "equals 0" clauses. Try using (AND Engagement Factor 4 equals 0 OR Engagement Factor 4 is blank), with the same for the Engagement Factors 5 and 6.

Shabbir Imber Safdar

Noah Cooper:

The problem is the "equals 0" clauses. Try using (AND Engagement Factor 4 equals 0 OR Engagement Factor 4 is blank), with the same for the Engagement Factors 5 and 6.

This solution appears to generate a list of 3,795 people.  I had expected 4,300, so I have to go in and see exactly what the difference might be.   I'm going to take it for the moment that this is solved unless I find a specific structural reason for the difference.

 

Is there any chance LuminateOnline dev-team might consider having a value filled in as either "0" or "blank" bug-like behavior that should be scheduled to be fixed in a future release?  

 

-Shabbir