Query using grouping management querying-using-grouping-management
In this example, we want to run a query to find all email domains targeted over 30 times during previous deliveries.
-
Which table needs to be selected?
The recipient table (nms:recipient)
-
Fields to be selected in output columns?
Email domain and primary key (with count)
-
Data grouping?
Based on email domain with a count of primary keys above 30. This operation is carried out with the Group by + Having option. Group by + Having lets you group data (“group by”) and make a selection of what was grouped (“having”).
To create this example, apply the following steps:
-
Open the Generic query editor and choose the Recipient table (nms:recipient).
-
In the Data to extract window, select the Email domain and Primary key fields. Run a count on the Primary key field.
For more on primary key counts, refer to this section.
-
Check the Handle groupings (GROUP BY + HAVING) box.
-
In the Sorting window, sort email domains in descending order. To do this, check Yes in the Descending sort column. Click Next.
-
In Data filtering, select Filtering conditions. Go to the Target elements window and click Next.
-
In the Data grouping window, select the Email domain by clicking Add.
This data grouping window is only displayed if the Handle groupings (GROUP BY + HAVING) box was checked.
-
In the Grouping condition window, indicate a primary key count greater than 30 since we only want email domains targeted more than 30 times to be returned as results.
This window appears when the Manage groupings (GROUP BY + HAVING) box was checked: this is where the grouping result is filtered (HAVING).
-
In the Data formatting window, click Next: no formatting is necessary here.
-
In the data preview window, click Launch data preview: here, three different email domains targeted over 30 times are returned.
