Note: The scenario below uses data from the DESelect Demo Data Package.
Scenario: You want to get the Company Name of all Accounts that don't have any Orders in the last 30 days.
Watch the video tutorial on how to implement this scenario or follow the steps below.
Note: this scenario uses feature Formula's in Subqueries, which is available in DESelect Plus and Advanced.
1. Selection Criteria
On the selections overview screen, click on New to create a new selection.
Drag data extension DESELECT_DEMO_Accounts to the Selected Data Extensions section.
Drag field Id to the Filters section:
- Choose criteria: Not In Results
- From Available Data Extensions, select data extension DESELECT_DEMO_Orders and drag-drop it on the drop-section in the newly created filter.
- Choose field AccountId
Now we have indicated we want to get the Accounts whose Id does not appear in the Orders data extension. However, we want to filter on only the Orders from the last 30 days, so we'll limit the results of the Orders data extension.
- Click on the funnel icon on the newly created filter.
- In the modal that opens up:
- Drag field Date to the Filters section
- Choose criteria: Is after or on
- Choose date filter type: Relative
- Choose: 30, Days, Before Now
2. Target Data Extension
Hit the Create Data Extension button, enter a name, eg. Accounts without recent Orders, and press Save.
Select the following fields by either double-clicking on them or dragging them to the section on the right:
- Company Name from DESELECT_DEMO_Accounts
Click Save Data Extension.
When you click Run Preview on the Preview screen, a results table will be shown with field Company Name from DESELECT_DEMO_Accounts.
The results table shows only Accounts that don't have an Order in the last 30 days. Note we did not enter an exact date; whenever we run this selection, we'll always get Accounts without an Order in the last 30 days as the date comparison is dynamic and based on the time of execution of the query.