In this article you will find examples of how-to build complex segments using one-to-many data tables. If you need help or have additional questions about building segments in your account you can email our Support team.

Question: How do I get contacts that didn’t purchase in the past year, but pull contacts that purchased prior to that and include contacts that never purchased?

When building your segment, do not explicitly join to the extended table. Set the filter to find contacts that "Don’t have an extended table record matching”, and then add your date clauses to specify the timeframe. For example, Order Date >= 01/01/2022 and Order Date <= 01/01/2023. This will retrieve all contacts that did not place an order in 2022 but did purchase at other times, and also include contacts that never purchased. This segment clause example uses the "date between" operator:

Important Note: If you join to the extended table by selecting "Have an extended table record matching" from the filter, the query results will contain all orders for a contact, whether they meet the criteria or not, as long as one of the orders meets the criteria. 

Question: How do I query contacts that purchased 2 years in a row?

When building your segment, use the "BETWEEN" operator to query by date range for the year ranges. In the first group, choose to “Include contacts that Have an extended table record matching in the additional tables”, and then add your date clause to specify the timeframe for the first year. Then add the second group. For example, in order to pull contacts that purchased in both 2014 and 2015, query by Order Date between 1/1/2014 and 1/1/2015 and then add your clause to query by Order Date between 1/1/2015 and 1/1/2016.

See Segment Example below:

[IMAGE OF SEGMENT IN AIR, UNABLE TO MOVE FORWARD BECAUSE ADD NEW GROUP IS NOT YET AVAILABLE FOR SEGMENTS 10/17/2023]

Question: How do I query contacts that purchased in a specific year?

Set the filter to “Match”, and then add your date clause to specify the timeframe for the year. When building your segment, use the "Date Between" operator to query by date range. 

Question: How do I query contacts that have never made a purchase?

When building your segment, do not explicitly join to the extended table. Set the filter to find contacts that "Don't have an extended table record matching" and then add a clause to find order date is not null. This can work with any field on the extended table because if a contact does not have an order then there will be no record of them in the table.

Question: When more than one purchase exists for a contact, how can I specify which one to get?

When selecting which extended tables to include in your segment, you can also choose which field to sort on when viewing results & the sort order. [COME BACK TO VERIFY WHEN SEGMENTS ARE FULLY RELEASED IN AIR]

• Select ascending to sort A-Z, or lowest-to-highest, or oldest-to-newest.
• Select descending to sort Z-A or highest-to-lowest or newest-to-oldest.

To include multiple rows of data in your email, your content must specify that multiple rows may exist. In the Drag-and-Drop Editor, use the Repeater Cell Group button to define which cells may have repeating data. For each contact retrieved in your segment, the system will loop through each row of data and display the information in the content.

[IMAGE OF SEGMENT IN AIR, UNABLE TO MOVE FORWARD BECAUSE REPEATER GROUPS ARE NOT YET AVAILABLE IN AIR 10/17/2023]

Question: How do I query for exclusive product buyers?

When building your segment, you will create two clause groups. 

In the first segment group, set the filter to find contacts that "Have a separate extended table record matching”, and then add your clause to specify the product type.

Add another group to the segment and the the operator to AND.

Set the filter to find contacts that "Don't have an extended table record matching" and <> (does not equal the product type/product name from the first segment group). For example, to query exclusively for Shoe buyers, the segment would look like this, querying all possible records in the table:

[IMAGE OF SEGMENT IN AIR, CANNOT MOVE FORWARD BECAUSE CREATING GROUPS IN SEGMENTS IS NOT YET AVAILABLE 10/17/2023] - Create segment for screenshot in Yay account using the Order table, I added a field to it for product type.

Notes

Availability of segment clauses and custom tables depends on your license package. For more information on licenses, please visit https://www.delivra.com/pricing or email our Client Success team.

Resources

Visit our e-Learning hub for online modules to learn more about Segmentation & other Delivra features.