Marketing Cloud Data Filter

Question

I want to use 3 DE in Data Filters namely A, B & C. Relationship between A & B is 1:M and Relationship between A & C is 1:M I want to Segment result from DE A based on filter on values of B & C. Is it feasible using Data Filter or I need to use SQL?

Answer 1

I would not recommend using Data Filters with more than one Data Relationship - as per my own experience put into this old question: Data relationships and filters

I have tried this approach few times since I posted the above question, and can only confirm it still does not return any rows in case where multiple Data Relationships are in use.

The behaviour of the construction you are outlining will not be as expected, and I would at any time use SQL instead - giving me full control of how especially 1:M relationships are managed and queried.

Answer 2

SQL will be the easiest and most reliable way. Since I'm not sure about what attributes you want to lookup and filter, however just for an idea, your SQL query will be something like this:

SELECT
Attribute1,
Attribute2,
Attribute3,
Attribute4
From TableA TA
JOIN TableB TB ON TA.1:M = TB.1:M
JOIN TableC TC ON TA.1:M = TC.1:M
WHERE TA.Attribute1 = 'FilterValue'
AND TA.Attribute2 > 'FilterValue'

Here's an example: Let say I have My Customer Data in TableA, Delivery Address in TableB, and Product Details in Table C. And my 1:M is CustomerID (common between A&B and A&C). And I want to filter customer who bought Product name: Apple and their Delivery/city is in Auckland. And I want to build a Data Extension from this filtered data - having SubscriberKey, FirstName, LastName and Email.

SELECT
SubscriberKey,
FirstName,
LastName ,
Email

From TableA TA
JOIN TableB TB ON TA.CustomerID  = TB.CustomerID 
JOIN TableC TC ON TA.CustomerID  = TC.CustomerID 
WHERE TC.ProductName = 'Apple'
AND TB.DeliveryCity = 'Auckland'

Running the SQL query will push the filtered data into the selected/designated data extension.