Multi-Join Filters

Data Extensions

For a relationship to be used inside a data filter, both related items must be data extensions. One of the data extensions must have a defined primary key, and the other must have the primary key field(s) mapped to the other item. Only sendable data extensions can make use of relationships.

Data Relationships

Under My Data Relationships there is a help box which instructs you how to create relationships that can be used in a data filter. This functionality tries to enforce a Many (or one)-to-One relationship between tables where the field mappings from the "Many" table must map to the primary key(s) of the "One" table. The tree controls allow for navigation into data extensions, and support folders and subfolders.

There is also a help box which is checked when the relationship you choose can be used in a filter. This happens when the Many-to-One relationship is met between the two tables. The check box toggles as you build the relationship, and is unchecked if the relationship is broken.

When the data relationship tables are initially selected, an attempt to automatically map the keys for the Many-to-One relationship is made using the key information and the column names. If the foreign key field names in the "Many" sided table map to the primary keys in the "One" sided table, the mapping work is complete. If the names do not match, manual changes must be made to the data extension(s).

Data Filters

When making the filter you see the relationships you created as child folders under the data extension. In order for your relationships to appear you must pick a primary data extension that is "sendable". The folder name is be the data extension name. In the diagram below, note:

• Key fields are disabled, and are not filterable. You can use this field by selecting the field in the parent relationship rather than at they key level. Since the fields are joined, they represent the same attribute.
• Sub-relationships (leg-joins) where  A=B  and B=C appear as children under the parent relationship.
• You can have relationships at the same level. This happens if the primary relationship has more than one relationship tied to it.

Note: While you are limited by default to three relationships in a multi-join filter, you see as many relationships in the tree as you have created. But only those relationships that meet the "Can use in a filter" rule appears.

Scenario  

Northern Trail Outfitters wants to find all male subscribers who live in the north region of the US so they can send them coupons on ski equipment. However, this data is located on three separate tables, thus preventing them from running a filter on one data extension. The three data extension tables are: Subscriber DE, City DE, and State DE.

The filter they need to create must have two data relationships, one between Subscriber DE and City DE, and another between City DE and State DE. The diagram to the right explains the relationships between the data extensions that they have already created.

Note: The Many-to-One pattern is reflected from Subscriber DE to City DE, and from City DE to State DE.

Northern Trail Outfitters now creates a filter to pull their data.

From the Subscriber Tab, they select Data Filters and click Create Filter. Under Description they name their filter and under Data Source they select Data Extensions from the drop down. 

Next, they click the plus sign on Data Extensions that appears from the Data Extensions tree window. They select their sendable data extension, in this case Subscriber DE.

The grouping tree  (shown right) appears and shows the different levels of relationships among the data extensions: City De is a sub-relationship of Subscriber DE, and State DE is a sub-relationship of of City DE.In the Outer Grouping window that appears (above), they can expand the tree accordingly to see their data relationships.

They now proceed to set their filter logic. They need two separate filter rules. From the primary data extension they select Gender and set this attribute to be equal to "M" for male. They click the Add Condition Button and from State DE they select Region and set it equal to "North".  Their filter logic should match the image below:

Finally, they click Save.

Northern Trail Outfitters has created a multi-join filter that pulls data from multiple data extensions.