Siebel Marketing Installation and Administration Guide > Designing Marketing Campaign Load Formats > Creating and Testing Campaign Load Formats >

Creating Campaign Load Formats

Before you begin creating campaign load formats, make sure the subject area in the Business Intelligence Administration Tool includes the required fields to load new customers and execute your campaigns. For example, if you are loading contact data, you must verify that the Subject Area used for the Campaign Load Format includes Contact First Name, Middle Name, Last Name, and so on. For more information about creating Subject Areas for list formats, see the Oracle Business Intelligence Enterprise Edition documentation.

Creating campaign load formats requires that you complete the following tasks:

• Creating a Campaign Load Format and Adding Columns
• Enabling System Data Expressions for Required Columns
• Assigning Integration IDs
• Requalifying List Results Against Original Criteria
• Setting Options, Headers, and Footers for Campaign Load Formats

Creating a Campaign Load Format and Adding Columns

Campaign load formats have columns that are always required, regardless of the type of customer data being loaded, for example, contacts, accounts, or prospects. The following procedure describes how to create a campaign load format and add columns to the format.

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 the Administration - Marketing screen, then the List Formats view.
   • If you log in to the Marketing module directly, 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 the required columns for your campaign load format.

   Subject areas are available for this purpose, including the following:

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

     NOTE:  Your administrator might have added additional subject areas.

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 each integration object and integration component, based on the type of party being loaded (contacts, accounts, or prospects).

   NOTE:  The column label must exactly match the field name of the integration component in the Siebel enterprise application repository (siebel.srf). For information about required integration component fields, see Enabling and Synchronizing Marketing Server Components and Field Names for Marketing Integration Components.

   1. To rename a column label that does not exactly match the field name of the integration component, click the properties button.
   2. Select the Custom Headings option.

      If you use one of the standard subject areas listed in Step 2, the column names matches the field names in the integration components in the Siebel enterprise application.

   3. Enter the necessary table and column heading.

      The table heading must match the integration component name and the column heading must match the field name.

5. Select the following required columns to add them to the campaign load format:
   1. From the Contact folder, add the Person UId column.
   2. From the Campaign Contact folder, add the following columns: Campaign Id, Segment Id, Load Number, Token Number, Scalability Batch Number, and Treatment Id.

      NOTE:  The Treatment Id column is an external key, and is now a required column. Depending on your upgrade history and system configuration, your current list formats might not include this column. If this is the case, add a System Data Expression for Treatment Id to the Business Intelligence Web configuration files (see Oracle Marketing Segmentation Guide) and add this expression as a column to your Campaign Load list formats (see Creating and Testing Campaign Load Formats).

Table 10 shows the mappings used in the preconfigured reports.

Enabling System Data Expressions for Required Columns

Several of the Campaign Contact columns required for the campaign load format must be populated with values that are determined by the list generation process at run time. To accomplish this, you set up the Campaign Contact columns to use system data expressions. The following procedure describes this task.

To enable system data expressions for required columns

1. Click the properties button on the Campaign Contact column.
2. In the dialog box, first click the Custom Headings check box.

   This makes sure the table and column name are not changed when you modify the formula in Step 3.

3. Clear the current formula from the Column Formula box.
4. Place your cursor in the Column Formula box, then click the Available System Data link.
5. Select the appropriate system data expression from the values in the following table.

   Column	Corresponding System Data Expression
   Campaign Id	Campaign Id
   Load Number	Load Number
   Scalability Batch Number	Email Batch Number
   Segment Id	Segment Id
   Token Number	Token Number
   Treatment Id	Treatment Id

   The expression is added to the formula. For example, the Campaign ID column would show the following formula:

   @{campaignID}{0}

   NOTE:  If you get an error message in the Formula dialog box, then ignore the error.

6. Click OK.

Assigning Integration IDs

Every campaign load format requires an integration ID, referred to as Key 1, that supplies the enterprise-wide ID representing that customer. For example, most corporate data warehouses already have unique customer IDs assigned as the enterprise customer ID. Campaign load expects this column to be included in the campaign history. The following procedure describes how to set up the campaign load format integration ID.

To assign integration IDs

1. Choose the Column from the Subject Area that provides the Integration ID for the Contacts Target Level.
2. Select the column in the right pane.
3. Click the formula button in the column.
4. Check the Custom Headings box.
5. Delete the existing formula and replace it with the appropriate value from Table 10.
6. Click OK.

Requalifying List Results Against Original Criteria

The following procedure describes how to requalify list results against original criteria. The members of the campaign load file must be evaluated against the segment criteria when generating the campaign load file. This option limits the information in the list file only to members of the segment, and any Qualified List Items constrained on the segment tree. There is no need to reload the campaign because it picks up the changes in the people who are added to the segment when the campaign is launched.

To requalify list results against original criteria

1. In the Columns view, in the Filters pane, select the following check box:

   Re-qualify list results against original segment criteria

2. The following line is automatically added to the filter criteria:

   Re-qualified against original segment criteria

Setting Options, Headers, and Footers for Campaign Load Formats

The following procedure describes how to set options, headers, and footers for campaign load formats. In the header, you can add an integration object name but you must not add a system data expression.

To set options, headers, and footers for campaign load formats

1. Click the Options tab and select the options in the following table.

   Attribute	Option
   Purpose	Campaign Load
   (choose format)	Delimited File
   End of Field Delimiter	Comma
   Text Qualifier	""
   Max # Records	1000

2. Click the Headers and Footers tab.
3. Enter the integration object name to load, using the following format (example also shown):

   Format	Example
   # integration object name #	# Marketing Contact #

   NOTE:  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.

4. Verify your campaign load format by previewing some sample contents of the list format.

   For instructions about how to preview a list format, see Oracle Marketing Segmentation Guide.

5. When you obtain the expected results, click the save icon to save your work.

About Siebel Contact and Campaign History Tables

When you load a campaign, the Campaign Load workflow process modifies data in the Siebel database after the Contact a integration component is mapped and the campaign load mapping data is modified.

• Contacts table (S_CONTACT). The Contacts table stores contact-level information.
• Campaign History table (S_CAMP_CON). The Campaign History table contains the history of contacts who qualify for campaigns, as well as the Campaigns ID, Segment ID, Wave Number, and so on.

If you include optional integration component fields such as Account or Contact Address in the campaign load format, other tables are updated with data according to the integration fields that are mapped.

For every record that qualifies for a campaign, the Campaign Load process uses the Contact component user key to determine if the contact record exists. Table 11 contains the table update rules.

NOTE:  If you use the Marketing Contact integration object, the lookup uses the User Key fields for Contacts and Accounts. Figure 1 shows the integration components for the Marketing Contact integration object. If you use the Marketing Person integration object, the lookup only requires the Campaign Id, Load Number, Token Number, Contact Id, or Prospect Id to confirm that the person is present in the transaction database.

Example of Configuring Campaign Load Format for Control Groups, Allocation Limits, and Stage Funneling

Campaign load formats support the use of control groups, allocation limits, and stage funneling. For more information on these features, see Siebel Marketing User Guide.

This topic gives an example of how to create campaign load formats for control groups, allocation limits, and stage funneling. You can use this feature differently, depending on your business model.

To create a campaign load format that supports control groups, allocation limits, and stage funneling

1. Navigate to the Administration - Marketing screen, then the List Formats view, and open the Campaign Load - Contacts Example list format.
2. Add a new column that uniquely identifies a target in the target level and change the formula to RANK([key column]). For example, in the Campaign Load - Contact and Prospect Example list format, the column has the following properties:
   • Table Heading: Contact
   • Column Heading: RANK ROW_WID
   • Column Formula: RANK(Contact.ROW_WID)
   • Hide: Yes
3. Add another column to the list form.
4. Change the Column Formula to use the previous Rank column to calculate control group flag based on a system variable called controlGroupPct. For example, in the Campaign Load - Contact and Prospect Example list format, the column has the following properties:
   • Table heading: Campaign Contact
   • Column Heading: Control Group Flag
   • Column Formula: CASE WHEN (RANK(Contact.ROW_WID) - TRUNCATE(RANK(Contact.ROW_WID)*@{controlGroupPct}{0}/100,0)*100/@{controlGroupPct}{0}) < 1 THEN 'Y' ELSE 'N' END
   • Hide: No
5. Add a filter to restrict the query count according to constraining stage wave Ids and the percentage allocation limit. For example, in the Campaign Load - Contact and Prospect Example list format, the filter is:

   Re-qualified against original segment criteria

   AND '@{constrainingStageFlag}{N}' = 'N' OR "Campaign Contact"."Load Wave Id" IN (@{constrainingWaveID}{'0'})

   AND '@{pctAllocationFlag}{N}' = 'N' OR (RANK(Contact.ROW_WID) - TRUNCATE(RANK(Contact.ROW_WID)*@{allocationLimit}{1000000}/100,0)*100/@{allocationLimit}{1000000}) < 1

   AND '@{pctAllocationFlag}{N}' = 'Y' OR RANK(Contact.ROW_WID) <= @{allocationLimit}{1000000}