Bookshelf Home | Contents | Index | Search | PDF

Siebel Marketing Guide > Designing Marketing Program Plans > Using the Program Flow >

Enabling Multistage Segmentation and Targeting Using Campaign History

---

By default, any customer in your database can be included in any stage of a program plan. This means that segment members are not automatically restricted to people who were targeted or who responded to a previous stage. The following are some examples of reasons you might need to use campaign history to select segment members in a stage:

To include or exclude customers who were part of a particular program or campaign.
To exclude customers who have had marketing activity in the last 60 days.
To exclude customers who have been targeted more than five times in the last year.
To include customers who were targeted based on a particular wave.
To include customers who have responded by purchasing a product.
To include, as part of an output list, the most recent date that the customer was targeted.

This history information can be mapped directly from the Siebel transactional database and can be used to build segmentation or filter criteria. If the customer uses the Siebel Data Warehouse, the Promotion star schema may be used for mapping.

NOTE:  Siebel Analytics uses third-party products for the extract, transform, and load (ETL) process. This process draws specific data from the Siebel transactional database (OLTP), transforms the data, and loads the data into the Siebel Data Warehouse (OLAP). The load process builds a series of star schemas. Siebel Analytics uses these star schemas as the basis for reports and graphs.

For more information about this process and the Promotion star, see Siebel Data Warehouse Data Model Reference.

You can allow segmentation by creating attribute families based on stage campaign history in the following ways:

Create attribute families from the Siebel Data Warehouse.
Create attribute families from the Siebel Database.

For more information about attribute families, see Defining Attribute Families. For more information about mapping tables and joining tables and fields, see External Data Mapping.

Creating an Attribute Family From the Siebel Data Warehouse Campaign History

Creating attribute families based on stage campaign history allows you to use segmentation. One method is to use campaign history from the Siebel Data Warehouse.

To create an attribute family from the Siebel Data Warehouse campaign history

From the application-level menu, choose View > Site Map > Marketing Administration > External Data Mapping.
Click the Tables view tab and in the Tables list perform the following tasks:
In the Tables list, create mappings to W_PERSON_D and W_CAMPHIST_F.
In the Tables list, create the following new records for the W_PROGRAM_D table:
Create a record for W_PROGRAM_D and place a constraint on it for records in which the Type value is Campaign Occurrence. Give the table a reference name of Campaigns.
Create a record on W_PROGRAM_D and place a constraint on it for records in which the Type value is Stage Occurrence. Give the table a reference name of Stages.
Create a record on W_PROGRAM_D and place a constraint on it for records in which the Type value is Program Occurrence. Give the table a reference name of Programs.
Click the Joins view tab and create the following joins:
Create two table joins using the information in the following table:

Parent Field.Table	Child Field.Table	Cardinality
CON_ID.W_PERSON_D	CON_ID.W_CAMPHIST_F	N:N
PROG_ID.W_CAMPHIST_F	PROG_ID.Campaigns	N:

Create a start point join to the W_PERSON_D table.
From the show drop-down list, select the Attributes view.
In the Attribute Families list, create a new attribute family called Program HIstory, setting the Base Field to PROG_ID.
In the Attributes list, create new records as shown in the following list:

Code Table	Code Field	Label Table	Label Field
Programs	Name	Programs	Name
Stages	Name	Stages	Name
Campaigns	Name	Campaigns	Name

Click the Attribute Hierarchies view tab and create a three-level hierarchy as shown in the following list:
Level 1= Programs
Level 2 = Stages
Level 3 = Campaigns

Creating an Attribute Family From the Siebel Database Campaign History

Creating attribute families based on stage campaign history allows you to use segmentation. One method is to use campaign history from the Siebel database.

Use the following guidelines to create an attribute family from the Siebel transactional database campaign history:

Make sure that S_CAMP_CON and S_DD_USER_KEY are mapped in the Marketing Repository. For instructions, see Initializing Multistage Programs.
The following is a list and brief description of the tables you use:
S_CAMP_CON. The primary campaign history intersection table between the CAMPAIGNS and CONTACTS tables. CAMP_CON_NUM is the source code field in this table.
S_SRC. The table that stores campaign occurrences, campaigns, stages and programs and their details.
S_CONTACT. The table that stores the details about contacts.
S_DD_USER_KEY. A cross-reference table between external database keys and Siebel contact ID.
S_CALL_LST. The segments table.
S_S_DD_CAMP_WAVE. The lookup table for waves.
S_DD_LST_DRSTR. The table that stores the list distribution details.
S_ORG_FUL. The Vendor table.

To create an attribute family from the Siebel database campaign history

From the application-level menu, choose View > Site Map > Marketing Administration > External Data Mapping.
Click the Tables view tab and in the Tables list map the tables as shown in the following table.

Reference Name	Table Name	Restriction
Program Table	[DB Owner].S_SRC	CAMP_TYPE_CD = 'PROGRAM CONTAINER'
Stage Table	[DB Owner].S_SRC	CAMP_TYPE_CD = 'PROGRAM'
Campaign Table	[DB Owner].S_SRC	CAMP_TYPE_CD IS NULL
S_CAMP_CON	[DB Owner].S_CAMP_CON
S_DD_USER_KEY	[DB Owner].S_DD_USER_KEY

Click the Joins view tab and create the following joins:

Parent Table	Child Table	Parent Field	Child Field	Cardinality	Cache (Y/N)
External Source Table (Sort-Merge Join)	S_DD_USER_KEY	[CH Field 1]	Key1	1:1	N
		[CH Field 2]	Key2
		[CH Field n]	Key[n]
S_DD_USER_KEY	S_CAMP_CON	ROW_ID	DD_USER_KEY_ID	1:N	Y
S_CAMP_CON	Campaign Table	SRC_ID	ROW_ID	N:1	Y
Campaign Table	Stage Table	TEMPL_ID	ROW_ID	N:1	Y
Stage Table	Program Table	TEMPL_ID	ROW_ID	N:1	Y

From the show drop-down list, select the Attributes view.
In the Attribute Families list, create a new attribute family called Program HIstory, setting the Base Field to PROG_ID.
In the Attributes list, create new records as shown in the following list:

Code Table	Code Field	Label Table	Label Field
Programs	Name	Programs	Name
Stages	Name	Stages	Name
Campaigns	Name	Campaigns	Name

Click the Attribute Hierarchies view tab and create a three-level hierarchy as shown in the following list:
Level 1 = Programs
Level 2 = Stages
Level 3 = Campaigns

You can map a hierarchical attribute to the Program, Stage, and Campaign tables and use the resulting hierarchy in segmentation criteria. You need to map the ROW_ID (Campaign Table) for the base field, map the NAME for the Label, and map the ROW_ID for the Code field for each Hierarchical level. This mapping imports the containers, not the individual occurrences. To import the occurrences, the condition for the Program table must be changed to CAMP_TYPE_CD = `PROGRAM CONTAINER OCCURRENCE' and Stage table must be changed to CAMP_TYPE_CD = `PROGRAM OCCURRENCE'.

NOTE:  The Base Field for the attribute family should always be mapped to the ROW_ID of the lowest level. In our example, it is ROW_ID of the Campaign table.

To map a hierarchical attribute for use in segmentation criteria

From the application-level menu, choose View > Site Map > Marketing Administration > Attributes.
Select the Attribute Families view tab and in the Attribute Families list, create a new record.
Map the Campaign table to the base table and map ROW_ID to the base field using the following information.

Name	Base Table	Base Field
Program Hierarchy	Campaign	ROW_ID

In the Attributes list, create the following records.

Name	Code Table	Code Field	Label Table	Label Field
Program	Program	ROW_ID	Program	NAME
Stage	Stage	ROW_ID	Stage	NAME
Campaign	Campaign	ROW_ID	Campaign	NAME

Click the Attribute Hierarchies view tab and create a new record using the following information.

Name	Attribute Family
Program Hierarchy	Program Hierarchy

In the Attribute Hierarchy Levels list, create the 3 records using the following information.

Sequence	Attribute
1	Program
2	Stage
3	Campaign

Click the Attribute Families view tab.
In the Attribute Families list, select the program hierarchy attribute family and click Retrieve Data.

Bookshelf Home | Contents | Index | Search | PDF