How to integrate Unica Campaign with Opportunity Detect

The integration between Unica Campaign and Opportunity Detect takes place at the data level; there is no integration of the user interface.

The feature that enables integration with Unica Campaign is the Opportunity Detect Expanded Outcome data source connector. The Expanded Outcome connector writes data to two database tables in a format that Unica Campaign can consume.

Opportunity Detect can process data in batches, or it can accept input data from a web service and function in a more interactive mode. This section includes usage examples for both batch and interactive modes.

About the Expanded Outcome tables

The Expanded Outcome connector writes the Outcome data to two database tables, which you must create using scripts provided with Opportunity Detect.

DB2 is the only supported database type for the Expanded Outcome tables.

The tables are:

A primary table that contains the text string specified in the Message field in the Action component.
A secondary table that contains the data specified in the Additional information field in the Action component.

You provide a base name for the Expanded Outcome tables when you run the ExpandedTable.sql script to create the tables. The script appends the number 1 to the name of the primary table, and appends the number 2 to the name of the secondary table.

For example, if you specify the base name ExpandedOutcome, the script creates two tables: ExpandedOutcome1 and ExpandedOutcome2.

Fields in the Expanded Outcome tables

These descriptions of the fields in the Expanded Outcome tables refer to scalar and tabular values, which are defined as follows:

Scalar: A single unit of data.
Tabular: A data set, as in a database row. In Opportunity Detect Outcomes, tabular data is saved in XML format.

Depending on how you specify the Outcome data, the Outcome can contain either type of value, or both types. If you include tabular data in a Unica Campaign integration, additional processing is required before Unica Campaign can consume it.

Table 1. Fields in the Expanded Outcome primary table
Field	Description	Data type
OUTCOMEID	Unique sequence ID. Used as the primary key to link to the secondary Expanded Outcome table.	Integer
AUDIENCEID	ID of the audience member for which the trigger system fired. Examples of an audience are account, customer, or household. The audience ID is stored as a string. Multi-column audience IDs are not supported.	NVARCHAR(60) If you use Oracle system tables and plan to integrate with Unica Campaign, you must change the data type of this field from NVARCHAR(60) to Varchar2(60) because Unica Campaign does not support the NVARCHAR(60) data type.
AUDIENCELEVEL	The single character audience code assigned on the Opportunity Detect Audience Levels page.	NVARCHAR(60) If you use Oracle system tables and plan to integrate with Unica Campaign, you must change the data type of this field from NVARCHAR(60) to Varchar2(60) because Unica Campaign does not support the NVARCHAR(60) data type.
COMPONENTID	Unique ID of the Action component that fired to generate the Outcome.	Varchar
OUTCOMEDATE	The timestamp of the final event that caused the Action component to fire.	Timestamp
RUNID	ID of the run, for batch mode only. The Run ID helps distinguish between the Outcomes of one run versus the Outcomes of runs before or after it. Because of the Run ID, you do not need to truncate the Outcome table after every run because you can query the table for all of the Outcomes in a specific run.	Integer
MESSAGE	The text string that was specified in the Message field of the Action component.	NVARCHAR(60) If you use Oracle system tables and plan to integrate with Unica Campaign, you must change the data type of this field from NVARCHAR(60) to Varchar2(60) because Unica Campaign does not support the NVARCHAR(60) data type.
PROCESSED	A flag that indicates whether the data has been consumed by Unica Campaign.	Integer

Table 2. Fields in the Expanded Outcome secondary table
Field	Description	Data type
OUTCOMEID	Unique sequence ID. Used as a foreign key to link the record to the primary Expanded Outcome table.	Integer
NAME	The name assigned in the Additional Information field of the Action component.	NVARCHAR(60) If you use Oracle system tables and plan to integrate with Unica Campaign, you must change the data type of this field from NVARCHAR(60) to Varchar2(60) because Unica Campaign does not support the NVARCHAR(60) data type.
VALUE	The scalar and tabular data that was specified in the Additional Information field of the Action component. Tabular values are saved in XML format.	Clob
DATATYPE	For scalar values, the data type can be one of the following. boolean currency date double integer string For tabular values, the data type is set to string, because tabular values are stored in XML, and the data type for XML is string.	NVARCHAR(60) If you use Oracle system tables and plan to integrate with Unica Campaign, you must change the data type of this field from NVARCHAR(60) to Varchar2(60) because Unica Campaign does not support the NVARCHAR(60) data type.

XML format of tabular values

Here is an example of the XML for a tabular value, where the record includes these fields:

Field_1
Field_2
Field_3

Example


			<SELECT name="S1">
				<ROW>
					<FIELD name="Field_1">abc</FIELD >
					<FIELD name="Field_2">123.45</FIELD >
					<FIELD name="Field_3">xyz</FIELD >
				</ROW >
			</SELECT >