Designing Marketing List Formats >

Designing Marketing List Formats

The List Format Designer can generate lists in different formats for the following purposes.

• List Export Formats. For more information, see Defining List Export Formats.
• Email Server Formats. For more information, see Defining Email Server Formats.
• Campaign Load Formats. For more information, see Defining Campaign Load Formats.
• Data Load Formats. For more information, see Defining Data Load or Customer Synchronization Formats.
• Saved Result Set Formats. For more information, see Defining Saved Result Set Formats.

The following topics are not specific to any one list format. They provide additional information to enhance your understanding of the capabilities of list formats:

• Adding Columns to List Formats
• Combining Lists From Different Subject Areas
• Adding Calculated Fields and System Fields to a List Format
• Adding a System Data Expression as a Column in a List Format

Defining List Export Formats

A List Export format defines the customer data and other campaign-related information that is exported so that the campaign can be executed. For example, a list export format may provide a list file for the following purposes:

• Sending a list of customers and addresses to a direct mail vendor for printing and mailing.
• Distributing a call list to employees in a sales organization.
• Exporting the campaign list to another IT application.

In addition to using list export formats in campaigns, you can define them for a variety of uses. The only requirement is that the data be accessible by the BI Server.

For Siebel marketing users using the standard metadata from the Oracle BI Administration Tool for the data warehouse, the application provides examples of List Export formats in the following location in the Web Catalog:

Shared Folders/Marketing/Example List Formats

For Fusion marketing application users, examples of List Export formats are provided in the following location in the Web Catalog:

/Shared Folders/Marketing/Segmentation/List Formats

To create a list export format and add columns

1. From Oracle BIEE application, navigate to New - List Format.
2. From the list of Subject Areas in the popup, select a subject area that includes the columns for your export file.

   NOTE:  Be sure to determine whether to get data from the transactional database, the data warehouse, or another data source, and then select the corresponding Subject Area.

3. Expand the folders in the left selection panel and click each column name to add it to the format.
4. To modify the displayed name for a column, in the Column Properties dialog box, use the Custom Headings option.
5. Click the properties button on a column to apply custom formatting.

   For information about column formatting options, see List Format Column Properties and Formatting Options.

6. Add any filters to be applied to the list format contents every time a list is generated.

   NOTE:  If the campaign membership already constrains the expected set of output records, this step is not required.

7. If you plan to use this export format for campaign execution, add filters to constrain the output to a specific campaign wave or set of waves using system data expressions.
8. Click the save icon in the upper-right corner of the screen and follow the instructions in the dialog box.

To add filters to constrain output to the correct campaign waves

1. From the selection panel, add the column that corresponds to the Campaign Wave Id.
2. Select the Filter button to add a filter based on the column.
3. In the New Filter dialog box, click Add More Options button to display System Data list.
4. In the System Data list, select the Wave Id expression.
5. Click OK to add the filter.
6. Click the Delete button to remove the column from the output columns (unless you want to include the Wave Id as an included column).

   For information about adding system data expressions, see Adding a System Data Expression as a Column in a List Format.

7. Click the Save icon in the upper-right corner of the screen and follow the instructions in the dialog box.

To set options, headers, and footers for list export formats

1. Click the Options tab and select the following options based on the results you want to achieve:

   Attribute	Option
   Purpose	List Export
   (choose format)	Output Type: Delimited File Fixed Width XML (not valid for Database repository choice) Record Set element. Enter the XML tag name for the outer record set. Record element. Enter the XML tag name for each record. Repository Choice: File system Content Server Repository Content Check-in Type Title Security Account Database Generate Writeback Count. Select this check box to get the number of records inserted into the physical table. Include duplicate records. Select this check box to include duplicate records into the physical table. Connection Pool. Enter the connection pool name for access to the physical table.
   End of Field Delimiter	(any)
   Text Qualifier	Optional.
   File Name	The default name includes components for format name, job ID, time stamp, and file counter. You can edit the default name by removing any of these components, and adding in constants (such as your company name). To add more components, click Available System Data.
   Maximum Record Count	Optional. You can limit the quantity of records in your output. This is useful for creating a test list and for when you have to limit the number of contacts in the list.
   Include column headers	Optional.
   Order by all Non-measure columns left to right when no column is ordered explicitly	Check this to order (sort) the list as indicated in the prompt.

2. To set up a custom header or footer, click the Header and Footer tab.
3. To include text, click in the Header Content or Footer Content workspace.
4. If required, add any System Data expressions to the header or footer content.

   For more information, see Adding Marketing List Format Headers and Footers. For a list of system data expressions, see Adding a System Data Expression as a Column in a List Format.

5. Test your list format by previewing some sample contents of the list format.

   To preview a list format, see Previewing a Marketing List Format.

6. To combine data from multiple subject areas, select the Columns tab and click the Combine with list from another Subject Area icon located on the top right area of the screen.

   For more information, see Combining Lists From Different Subject Areas.

   NOTE:  You must apply column formatting to the combined results.

7. Click the save icon in the upper-right corner of the screen and follow the instructions in the dialog box.

Defining Email Server Formats

An Email Server format is used to export the members of an email campaign to the Oracle E-Mail Marketing Server. The Email Personalization Format provides the columns that can be used as merge fields to personalize the email message sent to each recipient. Siebel Marketing users can find examples of email personalization formats in the following location:

/Shared Folders/Marketing/Example List Formats

For Fusion marketing application users, examples of email personalization formats are provided in the following location in the Web Catalog:

/Shared Folders/Marketing/Segmentation/List Formats

To define an email server format

1. From Oracle BIEE application, navigate to New - List Format.
2. From the list of Subject Areas in the popup, select a subject area that includes the columns for your email server file.

   In most situations, the Subject Area corresponds to data pulled directly from the campaign history in the transactional database.

3. Expand the folders in the left selection panel and click each column name to add it to the format.
4. Add the columns from the Subject Area to be available as merge fields in the email template.
5. Ensure that you keep the following required columns for your e-mail personalization format list file. These columns are used by the E-Mail Marketing Server for processing activities and reporting purposes.
   • Email Address
   • Stage Member Id
   • Contact Party Id
   • Treatment Id
   • Campaign Id
   • Source Code
   • Forwarder Message
   • Personalized Text
   • Person First Name
   • Person Last Name
   • Current Date
   • Current Time
6. Make sure that the displayed name of the column exactly matches the values in the list in Step 5.
   • If a Subject Area column that you select from the required columns list does not have a column label that exactly matches the name in the list, in the Column Properties dialog box, use Custom Headings option to modify the heading.
   • You can add additional columns as needed. If you must modify the displayed name for a column, in the Column Properties dialog box, use Custom Headings option.
7. Remove the folder heading portion of the column header caption for all columns.

   The Email Marketing Server expects each column header in the email file to contain the column header, not the table header caption. For each column in the format, use the following steps to remove the folder heading portion of the column header caption:

   1. Select Column Properties on the column.
   2. In the Column Properties dialog, select the Custom Headings check box.
   3. Delete the value in the Folder Heading field.
   4. Click OK.
8. To apply any custom formatting for a column, click the properties button on the column.

   For information about column formatting options, see List Format Column Properties and Formatting Options.

9. Add a filter to constrain the output based on the runtime Campaign Wave by adding the system data expression (Wave Id) to the column formula in the following steps:
   1. Add the Wave Id column to the format.
   2. Click the Filter button and add the Wave ID System Data Expression in the filter.

      For more information about adding system data expression to a list format, see Adding a System Data Expression as a Column in a List Format.

   3. Click the Delete button to delete the Wave ID from the displayed columns.
10. If your email content needs to be filtered based on a secondary Qualified List Item, select the following check box in the Filter section of the Columns view:

    Re-qualify list results against original segment criteria

11. If necessary, click the Options tab and specify the following options:

    Attribute	Option
    Purpose	Email Personalization
    (choose format)	Delimited File
    End of Field Delimiter	Comma
    Text Qualifier	""
    File Name	The default name includes components for format name, job ID, time stamp, and file counter. You can edit the default name by removing any of these components, and adding in constants (such as your company name). To add more components, click "Available System Data".
    Maximum Record Count	Optional. You can limit the quantity of records in your output. This is useful for creating a test list and for when you have to limit the number of contacts in the list.
    Include Column Headers	Optional.
    Order by all Non-measure columns left to right when no column is ordered explicitly	Check this to order (sort) the list as indicated in the prompt.

12. Test your list format by previewing some sample contents of the list format. To preview a list format, see Previewing a Marketing List Format.
13. To combine data from multiple subject areas, click Combine with list from another Subject Area.

    For more information, see Combining Lists From Different Subject Areas.

    NOTE:  You must apply column formatting to the combined results.

14. Click the save icon in the upper-right corner of the screen.

Defining Campaign Load Formats

Campaign Load formats are used to load the members of a segment or a segment tree cell into the campaign history table in the transactional database. For Siebel marketing users, this task is done through EAI.

To create a campaign load format and add columns

1. From Oracle BIEE application, navigate to New - List Format.
2. From the list of Subject Areas in the popup, select a subject area that includes the columns for your campaign load file.

   NOTE:  Be sure to determine whether to get data from the transactional database, the data warehouse, or another data source, and then select the corresponding Subject Area.

3. Expand the folders in the left selection panel and click each column name to add it to the format.
4. To modify the displayed name for a column, in the Column Properties dialog box, use the Custom Headings option.
5. Click the properties button on a column to apply custom formatting.

   For information about column formatting options, see List Format Column Properties and Formatting Options.

6. Add any filters to be applied to the list format contents every time a list is generated.

   NOTE:  If the campaign membership already constrains the expected set of output records, this step is not required. If the customer records are already loaded into the campaign history and you are exporting these customers, it is not necessary to requalify the segment criteria.

7. If you plan to use this export format for campaign execution, add filters to constrain the output to a specific campaign wave or set of waves using system data expressions.
8. Click the save icon in the upper-right corner of the screen and follow the instructions in the dialog box.

To create a list format that writes to a table

1. Click the Options tab and select the following options:

   Attribute	Option
   Purpose	Campaign Load
   (choose format)	Database Database options: Enter the Oracle BI Server Connection Pool this list format will insert into. For example: Alias. Column property with TABLE.COLUMN format. Connection Pool. This is a string from the RPD (BI repository file). RPD stores all BI metadata which is used by BI server. For Online Transaction Processing (OLTP) use Fusion OLTP Connection Pool and for Data Warehouse (DW) use Oracle Data Warehouse Connection Pool.

2. Click the Columns tab and click the Edit Formula button.
3. In the Edit Column Formula dialog box, click the Column Formula tab.
4. Complete the fields in the dialog box to map the desired columns. In the Alias field, enter the physical column name of the table specified in Step 1.

NOTE:  Be sure to map the primary key to one column.

To set options, headers, and footers for formats

1. Click the Options tab and select the following options based on the results you want to achieve:

   Attribute	Option
   Purpose	Campaign Load
   (choose format)	Delimited File, Fixed Width, XML, or Database
   End of Field Delimiter	(any)
   Text Qualifier	Optional
   File Name	The default name includes components for format name, job ID, time stamp, and file counter. You can edit the default name by removing any of these components, and adding in constants (such as your company name). To add more components, click "Available System Data".
   Maximum Record Count	Optional. You can limit the quantity of records in your output. This is useful for creating a test list and for when you have to limit the number of contacts in the list.
   Include Column Headers	Optional
   Order by all Non-measure columns left to right when no column is ordered explicitly	Check this to order (sort) the list as indicated in the prompt.

2. To set up a custom header or footer, click the Header and Footer tab.
3. To include text, click in the Header Content or Footer Content workspace.
4. If required, add any System Data expressions to the header or footer content.

   For more information, see Adding Marketing List Format Headers and Footers. For a list of system data expressions, see Adding a System Data Expression as a Column in a List Format.

5. Test your format by previewing some sample contents of the format.

   To preview a format, see Previewing a Marketing List Format.

6. To combine data from multiple subject areas, click Combine with list from another Subject Area.

   For more information, see Combining Lists From Different Subject Areas.

   NOTE:  You must apply column formatting to the combined results.

7. Click the save icon in the upper-right corner of the screen and follow the instructions in the dialog box.

Defining Data Load or Customer Synchronization Formats

The Data Load format is used to import any type of data into the transactional database. The customer synchronization format is used to import a batch of new customers into the transactional database or to update a set of customer profiles. Siebel marketing users can find examples of data load formats in the following location:

/Shared Folders/Marketing/Example List Formats

Column Headings Must Match Field Names in Integration Components

If you use one of the standard subject areas provided for data load, the column names are already set to match the field names in the Integration Components in the enterprise application. To load data that does not exist in these subject areas, you must use other subject areas.

CAUTION:  Data Load and Customer Synchronization formats must have columns that exactly match the field names of the integration components where the data is loaded.

If necessary, use Custom Headings in the Column Properties dialog box to rename any columns whose label does not exactly match the name of the integration component field name in the enterprise application.

Preconfigured Integration Objects Used in Headers and Footers

The standard application provides the following preconfigured integration objects that Siebel marketing users can use in headers and footers:

• BI Account
• BI Contact
• BI Household
• Marketing Contact
• Marketing Person
• Marketing Prospect

In the standard repository (SRF file), only certain fields from the extension tables in the business objects for Contacts, Accounts, and Households are enabled for update by default using the BI Data Load process. For example, in the Contacts business component, only the extension columns Attribute 49 through Attribute 64 are set up for this purpose. If you must update additional extension columns, confirm that the business component fields are enabled for update through the corresponding Integration Component.

For more information about business components, see Siebel Marketing Installation and Administration Guide.

To define data load or customer synchronization formats

1. From Oracle BIEE application, navigate to New - List Format.
2. From the list of Subject Areas in the popup, select a subject area that includes columns to load into the enterprise application.
3. Expand the folders in the selection panel and click each column name to add it to the format.
4. Verify that you have included the required columns for the business component to load. For more information about business components, see Configuring Siebel Business Applications.
5. If necessary, in the Column Properties dialog box, use Custom Headings option to rename any columns whose label does not exactly match the name of the integration component field name in the enterprise application. For more information, see Column Headings Must Match Field Names in Integration Components.

   CAUTION:  Data Load and Customer Synchronization formats must have columns that exactly match the field names of the integration components where the data is loaded.

6. Apply filters to constrain the data to be loaded.

   For example, to load contacts that have been added to the data warehouse since 01/01/2004, you could add a filter similar to the following:

   Contact Created Date is greater than 01/01/2004 12:00:00 AM

7. Click the Options tab and select the following options:

   Attribute	Option
   Purpose	BI Data Load or Customer Synchronization
   (choose format)	Delimited File
   End of Field Delimiter	Comma
   Text Qualifier	""
   Maximum Record Count	1000

8. Click the Headers and Footers tab.
9. In the Headers and Footers field, enter the integration object name to load using the following format. See the following example:

   Format	Example
   # integration object name #	# BI Contact #

   CAUTION:  You must not add additional text or a system data expression to the header. Additionally, do not press enter at the end of the second line. For EAI formatting, there must not be an end-of-line character at the end of the header.

   For more information, see Preconfigured Integration Objects Used in Headers and Footers.

10. Test your list format by previewing some sample contents of the list format. To preview a list format, see Previewing a Marketing List Format.
11. To combine data from multiple subject areas, see Combining Lists From Different Subject Areas.
12. Click the save icon in the upper-right corner of the screen.

Defining Saved Result Set Formats

Saved Result Set formats are used to save result sets from segments and segment trees.

To create a saved result set format and add columns

1. From Oracle BIEE application, navigate to New - List Format.
2. From the list of Subject Areas in the popup, select a subject area that includes the columns for your export file.

   NOTE:  Be sure to determine whether to get data from the transactional database, the data warehouse, or another data source, and then select the corresponding Subject Area.

3. Expand the folders in the left selection panel and click each column name to add it to the format.
4. Click the properties button on a column to apply custom formatting.
5. Add any filters to be applied to the list format contents every time a list is generated.
6. Click the save icon in the upper-right corner of the screen and follow the instructions in the dialog box.

Adding Columns to List Formats

You select the columns in a subject area to include in a list format. The subject area for the request is listed in the selection pane with the tables and columns it contains.

After you have added a column, you can use the buttons on the column to modify the column formatting, add a formula, add a filter, or sort or split the contents.

CAUTION:  If you click the refresh button in the browser window before you finish creating a request, be aware that the browser reloads all frames and deletes your changes.

To add a column to the list format

1. From Oracle BIEE application, navigate to New - List Format.
2. Create a new list format or open an existing format.
3. Click columns in the selection pane to add them to the list format definition.
4. Use the column buttons shown in Table 7 to control the use of each column in the request.

To sort a list or split a list into multiple files

1. From Oracle BIEE application, navigate to New - List Format.
2. Locate the column or columns by which to sort or split.
3. Use the column buttons described in Table 7 to activate sorting or splitting.

Combining Lists From Different Subject Areas

You can use a List Format to combine two or more lists from different Subject Areas. For example, you can combine a set of customers from your data warehouse with a set of customers from your transactional database.

To combine data sets from multiple subject areas, you select a similar column set from each subject area. After you have combined two or more column sets, you can use standard set operators (Intersect, Union, Union All, and Minus) to determine the final result set.

Each column set from each subject area must have the same number of columns and the data types for corresponding columns must match.

To combine data sets across subject areas

1. From Oracle BIEE application, navigate to New - List Format.
2. Select a column set from your first subject area.
3. Add filters to control the contents of the first data set.
4. Click Combine with list from another Subject Area icon located at the top right-hand side of the List Format designer page.
5. Select the Subject Area for your second column set.
6. After you have added the columns for each column set, click the Combined Results link.
7. Click the buttons on the columns in the Combined Results to control the formatting, sorting, and splitting of the combined list.

Adding Calculated Fields and System Fields to a List Format

Use the column formula dialog box to add a calculated column or a column based on a system data expression to the list format. Calculated fields can be created using a wide variety of formulas based on other columns in subject area.

System Data are variables that can be added to a list format at run time. For example, if you are exporting a campaign file, you may want to include a column displaying the segment for each customer in the list. To do this, you add System Data for the Segment Name to the list format, and the server determines the correct segment for the file based on the campaign being executed. System Data can also be used for filtering the contents of the file based on the context of a campaign or other use.

Adding a System Data Expression as a Column in a List Format

To automatically populate campaign contact columns with values that are determined by the list generation process at run time, you must set up the Campaign Contact columns to use system data expressions.

If you use list export format for campaign execution, you can add columns to constrain the output using system data expressions. To add a filter to constrain the output based on the run-time campaign wave, add the system data expression wave ID.

To add a system data expression

1. From Oracle BIEE application, navigate to New - List Format.
2. In the List Format Designer, select a column from the selection pane.
3. Click Edit formula.
4. In the Edit Column Formula dialog box, select the Custom Headings check box.
5. Delete the text from the Column Formula workspace.
6. Click the Available System Data link.
7. In the System Data list, select a system data expression.

   For example, if you choose the Campaign Id expression, the following appears

   @{campaignID}{0}

   NOTE:  The value in the braces at the end of the expression is the default value for the expression. If you do not provide an input value for the list generation request, the default value is used.

8. To display the System Data Expression column in the file, leave the column in the column set at the top of the page.
9. To filter the content but not display the column, perform the appropriate steps from the following list:
   • Add the column.
   • Apply the data expression to the Column Formula.
   • Delete the column from the column set by clicking the Delete button on the column.

Configuring the System for New System Data Expressions

Your system administrator may need to add System Data Expressions in addition to data expressions that are automatically installed. For example, you might have customizations that require that additional information be passed to the list generation job by the Web Service. Or, if you are upgrading from a previous version of Siebel Marketing, new columns such as Treatment Id might not be included in your repository and configuration files. Your system administrator must modify the marketingwebexpressions.xml configuration file to add system data expressions.

To add new System Data Expressions

1. Open the marketingwebexpressions.xml file:

   <OBIEE_INSTALL_DIR>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\marketingwebexpressions.xml

2. Scroll to the bottom of the file and add your new expression.

   For example, if you want to add treatmentID, enter a new section:

   <WebExpression name="treatmentID" usequotes="true">

   <messageKey>kmsgMktgWebExprTreatmentId</messageKey>

   <default></default>

   </WebExpression>

3. Test to make sure that the new expression appears in the User Interface.
   1. Save your changes to the marketingwebexpressions.xml file.
   2. Restart the BI Presentation Services.
   3. From Oracle BIEE application, navigate to New - List Format.
   4. Create a list format or open an existing format.
   5. Click on Edit formula for a column, enter the cursor in the Column Formula area, and then select the Available System Data link.

      The new expression appears in the list.

   6. Click the new expression, and a formula is automatically populated.

      The treatmentID expression, similar to the following example, appears:

   '@{treatmentID}{}'

Using Extension Attributes as System Data Expressions

Your system administrator can use extension attributes as system data expressions by adding them to the marketingwebexpressions.xml file. The following is an example:

<WebExpression name="segment.offer" usequotes="true">
   <messageKey>kmsgMktgWebExprOfferName</messageKey>
   <default></default>
   <context>extensionAttribute</context>
   <width>10</width>
</WebExpression>

When finished editing the marketingwebexpressions.xml file, restart the server.

System Data Expressions Used in List Format Elements

Each list format element (Column formula, Headers/Footers, and Filters) can contain one or more system data expressions. Table 8 shows which System Data Expressions can be used in each list format element.

List Format Types and Valid System Data Expressions

All system data expressions cannot be used with every list format. Table 9 contains the list formats and shows which system data expressions can be used with each one.