Multiple "AND" filters for same field

Hi Meg, if I understand correctly you'd like to show all records where Event equals [i]either "task-completed" or "launched-initiative". Is this correct?





If so, then you'd want to use the "or" qualifier instead of "and". 





If this isn't what you had in mind, could you post a screen shot of the report?

---

I want to see customers that have done both items, not either of the two. The goal of my report is to identify which customers are doing a group of behaviors in a certain timeframe. 





For example, I know the Levite JCC is a customer that has done both of these actions, so I should get results. 





Here are some screenshots: 

---

Hi Meg:





Is the filtered field a string/text value or is it a picklist?  If it's a picklist, you should be able to use the contains filter to say Event contains value A and B.

---

I don't see a way to do a picklist, but would love that. I typed in the name of both event fields because it looked like that was my only option. Am I missing something? 

---

Hmmm.....the challenge here is that each event record has a single value for "Event" and the report filter evaluates one record at a time, so it wouldn't know that another record of a different type for that same customer also exists. 





Unless Scott has a rabbit he can pull out of his hat (which he sometimes does) 🙂....what you'd need to do is create a bionic rule to merge the two results via an inner join. Check out this article where bionic rules are explained. Specifically section 2.1.4  Merge task to create a single output.





How this would work is that you'd pull two filters on the data. The first fetch task would look for all Events where "launched-initiative" was present, the second fetch would look for all Events where "task-completed" was present. And then the merge task would pull a final list only where the same customer was present in both lists. 

---

This looks pretty complex. I am familiar with the concept of inner and outer joins, but this seems like a lot to complete a (in my mind) simple query. 





Before I start creating bionic rules - would I need to create them for every possible group of events that I want to compare? I have 100+ segment events and I may need to see how customers have completed a combination of 5 or 7 or 10 of those events over time. 

---

I have another idea that I think can work trying out another example using similar data on Page Names and Pageviews. Not sure if it gets you exactly what you are looking for...





For Show, I created Sum of Pageviews and Count_Distinct of Page (which is the page name in this example)


For By, using same as your example Date (by month) and Account Name


For Filter, I filtered using OR the two page names I am looking for AND where Count_Distinct of Page = 2.  





What this does return:


It will show all accounts that have met this within a month.  You will notice that if there is a month where an account does not have pageviews with BOTH pages, no pageviews will show.  So it will only show the data for the months where the condition is met.


The Pageview count is the sum for the two pages I filtered (not total pageviews for that account in a month)


Note: There is not a way to hide the Count_Distinct metric when creating the report view.





Hope this helps!

---

Hi Meg are you able to change the Equals operator to Contains?  You can then add two of these filters and use AND criteria.

---

The best data structure for these kinds of reports is to have each event be its own column or metric. This way you can say:





        Show me:   [Count of Event 1] , [Count of Event 2] , [Count of Event 7]


        By:              [Date] , [Account Name]


        Filter:          [Count of Event > 1] AND [Count of Event 2 > 0] AND [Count of Event 7 > 0]





This can be done easily using the newly introduced PIVOT action in Bionic Rules. You'd have to start with a FETCH task to query data from your existing table -- limit the fetch to data from yesterday, last 30 days, etc. depending on how often you'd execute the rule. 





Then pick the PIVOT task to flip the events you need. Drag the event field into the 'Pivot On' section. A pop-up would open and this is where you pick which event values matter to you. So if you usually work with 25 of the 200 events you track, you can add just those 25 here. You can also pick aggregations depending on the event type -- most events need a simple COUNT, but something like 'revenue' associated with a 'transaction' event might need a SUM instead. 





Once done, you'd have to write this pivoted data into a fresh table in MDA -- 'Load to MDA' action. From there on, you can directly report on this new table and create as many different groups as you need for your analysis. The rule frequency, as I mentioned in the beginning, controls how often data comes from your current table to the new one.





Hope this helps!

---