Siebel Marketing Installation and Administration Guide > 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, read Defining List Export Formats.
• Email Server Formats. For more information, read Defining Email Server Formats.
• Campaign Load Formats. For more information, read Defining Campaign Load Formats.
• Data Load Formats. For more information, read Defining Data Load or Customer Synchronization Formats.
• Saved Result Set Formats. For more information, read 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 their use in campaigns, you can define list export formats for a wide variety of uses. The only requirement is that the data be accessible by the Analytics Server.

If you are using the standard metadata from the Siebel Analytics Administration Tool for the Siebel Data Warehouse, the application provides examples of List Export formats in the following location in the Web Catalog:

Shared List Export Formats/Marketing/Example List Formats

To create a list export format and add columns

1. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing screen > List Formats.
   • If you log in to the Marketing module directly, click the Marketing screen tab and then click Create a New List Format.
2. From the list of Subject Areas on the right, select a subject area that includes the columns for your export file.

   NOTE:  Be sure to determine whether to get data from the Siebel transactional database, the Siebel 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, read 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 formula button to add a filter based on the column.
3. In the Edit Filter dialog box, click Add and select System Data.
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, read 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)	Delimited File, Fixed Width, XML, or Database XML options: Record Set element. Enter the XML tag name for the outer record set. Record element. Enter the XML tag name for each record. Database options: Table. Enter the table in which to store the records. Connection Pool. Enter the connection pool name for access to the 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.
   Max # Records	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, read Adding Marketing List Format Headers and Footers. For a list of system data expressions, read 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, read Previewing a Marketing List Format.

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

   For more information, read 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 Siebel Email 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. You can find examples of email personalization formats in the following location:

Shared Email Server Formats/Marketing/Example List Formats

To define an email server format

1. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing screen > List Formats.
   • If you log in to the Marketing module directly, click the Marketing screen tab and then click Create a New List Format.
2. From the list of Subject Areas on the right, 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 Siebel 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. Add the required columns.

   The following columns are required for all email server formats:

   • First Name
   • Last Name
   • Email Address
   • Camp Con Id
   • Contact Id
   • Prospect Id
   • Batch Number

     NOTE:  When you add the Batch Number column, you must include the column in the sort order and assign a split value. For more information, read Adding Columns to List Formats.

6. Make sure that the displayed name of the column exactly matches the values in the list in Step 3.
   • 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 need to modify the displayed name for a column, in the Column Properties dialog box, use Custom Headings option.
7. Remove the table 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 table 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 Table 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, read 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, read 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".
    Max # Records	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, read Previewing a Marketing List Format.
13. To combine data from multiple subject areas, click Combine with list from another Subject Area.

    For more information, read 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

A Campaign Load formats are used to load the members of a segment or a segment tree cell into the campaign history table in the Siebel transactional database through EAI.

To create a campaign load format and add columns

1. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing screen > List Formats.
   • If you log in to the Marketing module directly, click the Marketing screen tab and then click Create a New List Format.
2. From the list of Subject Areas on the right, select a subject area that includes the columns for your campaign load file.

   NOTE:  Be sure to determine whether to get data from the Siebel transactional database, the Siebel 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, read 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 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".
   Max # Records	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, read Adding Marketing List Format Headers and Footers. For a list of system data expressions, read 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, read Previewing a Marketing List Format.

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

   For more information, read 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 Siebel transactional database. The customer synchronization format is used to import a batch of new customers into the Siebel transactional database or to update a set of customer profiles. You can find examples of data load formats in the following location:

Shared Data Load Formats/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 Siebel enterprise application. To load data that does not exist in these subject areas, you need to 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 Siebel enterprise application.

For customer data (Contacts, Accounts, and Prospects), the standard application provides example subject areas that are already set up for Analytics Data Load. The standard application provides the following three subject areas that can be used for this purpose:

• Campaign Load - Contacts
• Campaign Load - Accounts
• Campaign Load - Prospects

Preconfigured Integration Objects Used in Headers and Footers

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

• Analytics Account
• Analytics Contact
• Analytics Household

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 Analytics 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 need to update additional extension columns, confirm that the business component fields are enabled for update through the corresponding Integration Component.

For more information about the available fields in the Analytics Data Load integration components, read Field Names for Marketing Integration Components.

To define data load or customer synchronization formats

1. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing screen > List Formats.
   • If you log in to the Marketing module directly, you click the Marketing screen tab and then click Create a List Format.
2. From the list of Subject Areas on the right, select a subject area that includes columns to load into the Siebel 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 Siebel Business Component to load. For more information about business components, read 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 Siebel enterprise application. For more information, read Column Headings Must Match Field Names in Integration Components.

   CAUTION:  Data Load and Customer Synchronization formats must have columns that exactly match the fields 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	Analytics Data Load or Customer Synchronization
   (choose format)	Delimited File
   End of Field Delimiter	Comma
   Text Qualifier	""
   Max # Records	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 #	# Analytics Contact #

   CAUTION:  You should 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, read 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, read Previewing a Marketing List Format.
11. To combine data from multiple subject areas, read 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. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing screen > List Formats.
   • If you log in to the Marketing module directly, click the Marketing screen tab and then click Create a New List Format.
2. From the list of Subject Areas on the right, select a subject area that includes the columns for your export file.

   NOTE:  Be sure to determine whether to get data from the Siebel transactional database, the Siebel 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. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing screen > List Formats.
   • If you log in to the Marketing module directly, you click the Marketing screen tab and then click Create a 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 11 to control the use of each column in the request.

To sort a list or split a list into multiple files

1. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing screen > List Formats.
   • If you log in to the Marketing module directly, click the Marketing screen tab and then click Create a List Format.
2. Locate the column or columns by which to sort or split.
3. Use the column buttons described in Table 11 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 Siebel 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. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing screen > List Formats.
   • If you log in to the Marketing module directly, you click the Marketing screen tab and then click Create a 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.
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 need to 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. Using one of the following methods, navigate to the List Designer start page:
   • In the Siebel Marketing application, navigate to Administration - Marketing > List Formats.
   • If you log in to the Marketing module directly, click the Marketing screen tab and then click Create a List Format.
2. In the List Format Designer, select a column from the selection pane.
3. Click the fx button.
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.

Using Extension Attributes as System Data Expressions

You 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 you are done editing the x file, click Reload Metadata in the Marketing window. You do not need to stop and restart a server or log out and back in to the application.

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 12 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 13 contains the list formats and shows which system data expressions can be used with each one.