Oracle Marketing Segmentation Guide > Configuring Marketing Module Metadata > Setting Up Marketing Segmentation Metadata >
Controlling Marketing Vendor List Query Details
If there are dimensional attributes in your list output report that do not belong to the target level dimension and there is a 1:N relationship between the target level and the Dimensional attribute, special configuration might be needed. For example, if the list output report contains columns such as Contact-ID (target level) and email Address and there is a 1:N relationship between Contacts and Email Addresses, the list output report is similar to Table 10.
Table 10. Example of List Output Report
|
|
C1 |
E1@A1.com |
C1 |
E2@A2.com |
C1 |
E3@A3.com |
However, marketing vendor lists often require that only one row be created for every Contact (target level ID). This problem can be solved in the following ways:
- Adding an explicit filter such that only one email address is selected in the list output, such as in the following example:
WHERE <Email-Address Table>.<Type Column> = Primary
- Using a QLI on the Email-Address column. For more information, see Setting Up Marketing Qualified List Items.
- Setting up a simple measure and adding a filter.
For example, set up a simple measure such as RANK (Email-Address) by Contact-ID, and then assign a filter to this measure in the report as WHERE RANK(E...) = 1. This generates a unique number, starting from 1, for every combination of the Contact-ID and E-mail Address columns. The filter selects the first combination, eliminating duplicate rows. The following are the guidelines for setting up a simple measure and adding a filter.
- In the Administration Tool, select the business model for the list output for the target level (for example, Contact).
- Create a logical fact table and a logical column.
- In the Levels tab, select the Detail level for the target level dimension.
- Drag this measure to the List catalog in the Presentation layer, check metadata consistency, and save your work.
- In the Marketing Module, in the List Format Designer, add this column, put a filter equal to 1 and remove the column from the list format.
|