Runtime environment staging tables to Campaign history tables mapping
The Interact contact history staging tables map to Campaign history tables. You must have one of the runtime environment staging tables for each audience level.
UACI_CHStaging contact history staging table mapping
This table shows how the UACI_CHStaging runtime environment staging table maps to the Campaign contact history table. The table names that are shown are the sample tables that are created for the default audience in the runtime tables and the Campaign system tables.
UACI_CHStaging Interact contact history staging table column name |
Campaign contact history table |
Table column name |
---|---|---|
ContactID | N/A | N/A |
TreatmentCode | UA_Treatment | TreatmentCode |
CampaignID | UA_Treatment | CampaignID |
OfferID | UA_Treatment | OfferID |
CellID | UA_Treatment | CellID |
CustomerID | UA_DtlContactHist | CustomerID |
ContactDate | UA_DtlContactHist | ContactDateTime |
ExpirationDateTime | UA_Treatment | ExpirationDateTime |
EffectiveDateTime | UA_Treatment | EffectiveDateTime |
ContactType | UA_DtlContactHist | ContactStatusID |
ContactStatusCode | UA_DtlContactHist | ContactStatusId |
UserDefinedFields | UA_DtlContactHist | UserDefinedFields |
ContactID is a key to join the UACI_CHOfferAtrib table with the UACI_CHStaging table. The userDefinedFields column can contain any data that you choose.
UACI_CHOfferAttrib contact history staging table mapping
This table shows how the UACI_CHOfferAttrib runtime environment staging table maps to the Campaign contact history table. The table names that are shown are the sample tables that are created for the default audience in the runtime tables and the Campaign system tables.
UACI_CHOfferAttrib Interact contact history staging table column name |
Campaign contact history table |
Table column name |
---|---|---|
ContactID | N/A | N/A |
AttributeID | UA_OfferHistAttrib | AttributeID |
StringValue | UA_OfferHistAttrib | StringValue |
NumberValue | UA_OfferHistAttrib | NumberValue |
DateTimeValue | UA_OfferHistAttrib | DateTimeValue |
UACI_RHStaging contact response history staging table mapping
This table shows how the UACI_RHStaging runtime environment staging table maps to the Campaign response history table. The table names that are shown are the sample tables that are created for the default audience in the runtime tables and the Campaign system tables.
UACI_RHStaging Interact response history staging table column name |
Campaign response history table |
Table column name |
---|---|---|
SeqNum | N/A | N/A |
TreatmentCode | UA_ResponseHistory | TreatmentInstID |
CustomerID | UA_ResponseHistory | CustomerID |
ResponseDate | UA_ResponseHistory | ResponseDateTime |
ResponseType | UA_ResponseHistory | ResponseTypeID |
UserDefinedFields | UA_ResponseHistory | UserDefinedFields |
SeqNum is a key that is used by the contact and response history module to identify data, but is not recorded in the Campaign response tables. The userDefinedFields column can contain any data that you choose.
Additional columns in staging tables
If you add columns to the staging tables, the contact and response history module writes them to the UA_DtlContactHist or UA_ResponseHistory tables in columns of the same name.
For example, if you add the column linkFrom to your UACI_CHStaging table, the contact and response history module copies that data to the linkFrom column in the UA_DtlContactHist table.
Additional columns in Campaign contact and response history tables
If you have additional columns in your Campaign contact and response history tables, add matching columns to the staging tables before you run the contact and response history module.
You populate extra columns in the staging tables by creating columns with the same names as your name-value pairs in your runtime session data.
For example, you create name-value pairs NumberItemsInWishList and NumberItemsInShoppingCart and add them to your UACI_RHStaging table. When a Log Offer Acceptance or Log Offer Rejection event occurs, the runtime environment populates those fields. The runtime environment populates the UACI_CHStaging table when a Log Offer Contact event occurs.
Use tables to include a score for an offer
You can use the user-defined fields to include the score that is used to present an offer. Add a column that is named FinalScore to both the UACI_CHStaging table in the runtime tables and the UA_DtlContactHist table in the Campaign system tables. Interact automatically populates the FinalScore column with the final score used for the offer if you are using built-in learning.
If you are building a customized learning module, you can use the setActualValueUsed method of the ITreatment interface and the logEvent method of the ILearning interface.
If you are not using learning, add a column that is named Score to both the UACI_CHStaging table in the runtime tables and the UA_DtlContactHist table in the Campaign system tables. Interact automatically populates the Score column with the score used for the offer.
Create new history tables in the Campaign and staging tables in the Interact
If you are using an audience level other than the Customer, then you will have to create new history tables in the Campaign, and new staging tables in the Interact.
For example, the below sample script is used in the HCL® DB2® design time database to create history tables in the Campaign for an audience level of type Account.
DROP TABLE ACCT_UA_ResponseHistory;
DROP TABLE ACCT_UA_DtlContactHist;
DROP TABLE ACCT_UA_ContactHistory;
CREATE TABLE ACCT_UA_ResponseHistory (
AccountID varchar(30) NOT NULL,
TreatmentInstID bigint NOT NULL,
ResponsePackID bigint NOT NULL,
ResponseDateTime timestamp NOT NULL,
WithinDateRangeFlg int,
OrigContactedFlg int,
BestAttrib int,
FractionalAttrib float,
DirectResponse int,
CustomAttrib float,
ResponseTypeID bigint,
DateID bigint,
TimeID bigint,
UserDefinedFields char(18),
CONSTRAINT ACCT_cRespHistory_PK
PRIMARY KEY (AccountID, TreatmentInstID,
ResponsePackID )
);
CREATE TABLE ACCT_UA_ContactHistory (
AccountID varchar(30) NOT NULL,
CellID bigint NOT NULL,
PackageID bigint NOT NULL,
ContactDateTime timestamp,
UpdateDateTime timestamp,
ContactStatusID bigint,
DateID bigint,
TimeID bigint,
UserDefinedFields char(18),
CONSTRAINT ACCT_cContactHist_PK
PRIMARY KEY (AccountID, CellID, PackageID )
);
CREATE INDEX ACCT_cContactHist_IX1 ON ACCT_UA_ContactHistory
(
CellID
);
CREATE INDEX ACCT_cContactHist_IX2 ON ACCT_UA_ContactHistory
(
PackageID ,
CellID
);
CREATE TABLE ACCT_UA_DtlContactHist (
AccountID varchar(30) NOT NULL,
TreatmentInstID bigint NOT NULL,
ContactStatusID bigint,
ContactDateTime timestamp,
UpdateDateTime timestamp,
UserDefinedFields char(18),
DateID bigint NOT NULL,
TimeID bigint NOT NULL
);
CREATE INDEX ACCT_cDtlContHist_IX1 ON ACCT_UA_DtlContactHist
(
AccountID ,
TreatmentInstID
);
ALTER TABLE ACCT_UA_ResponseHistory
ADD CONSTRAINT ACCT_cRespHistory_FK2
FOREIGN KEY (TimeID)
REFERENCES UA_Time (TimeID);
ALTER TABLE ACCT_UA_ResponseHistory
ADD CONSTRAINT ACCT_cRespHistory_FK4
FOREIGN KEY (DateID)
REFERENCES UA_Calendar (DateID);
ALTER TABLE ACCT_UA_ResponseHistory
ADD CONSTRAINT ACCT_cRespHistory_FK3
FOREIGN KEY (ResponseTypeID)
REFERENCES UA_UsrResponseType (
ResponseTypeID);
ALTER TABLE ACCT_UA_ResponseHistory
ADD CONSTRAINT ACCT_cRespHistory_FK1
FOREIGN KEY (TreatmentInstID)
REFERENCES UA_Treatment (
TreatmentInstID);
ALTER TABLE ACCT_UA_ContactHistory
ADD CONSTRAINT ACCT_cContactHist_FK2
FOREIGN KEY (DateID)
REFERENCES UA_Calendar (DateID);
ALTER TABLE ACCT_UA_ContactHistory
ADD CONSTRAINT ACCT_cContactHist_FK3
FOREIGN KEY (TimeID)
REFERENCES UA_Time (TimeID);
ALTER TABLE ACCT_UA_ContactHistory
ADD CONSTRAINT ACCT_cContactHist_FK1
FOREIGN KEY (ContactStatusID)
REFERENCES UA_ContactStatus (
ContactStatusID);
ALTER TABLE ACCT_UA_DtlContactHist
ADD CONSTRAINT ACCT_cDtlContactH_FK3
FOREIGN KEY (TimeID)
REFERENCES UA_Time (TimeID);
ALTER TABLE ACCT_UA_DtlContactHist
ADD CONSTRAINT ACCT_cDtlContactH_FK2
FOREIGN KEY (DateID)
REFERENCES UA_Calendar (DateID);
ALTER TABLE ACCT_UA_DtlContactHist
ADD CONSTRAINT ACCT_cDtlContactH_FK1
FOREIGN KEY (ContactStatusID)
REFERENCES UA_ContactStatus (
ContactStatusID);
alter table ACCT_UA_DtlContactHist add RTSelectionMethod int;
alter table ACCT_UA_ResponseHistory add RTSelectionMethod int;
The below sample script is used in the runtime time HCL DB2 database to create history staging tables in the Interact for an audience level of type Account.
DROP TABLE ACCT_UACI_RHStaging;
DROP TABLE ACCT_UACI_CHOfferAttrib;
DROP TABLE ACCT_UACI_CHStaging;
DROP TABLE ACCT_UACI_UserEventActivities;
DROP TABLE ACCT_UACI_EventPatternState;
CREATE TABLE ACCT_UACI_RHStaging (
SeqNum bigint NOT NULL,
TreatmentCode varchar(512),
AccountID varchar(30),
ResponseDate timestamp,
ResponseType int,
ResponseTypeCode varchar(64),
Mark bigint NOT NULL
DEFAULT 0,
UserDefinedFields char(18),
RTSelectionMethod int,
CONSTRAINT iRHStaging_PK1
PRIMARY KEY (SeqNum)
);
CREATE TABLE ACCT_UACI_CHOfferAttrib (
ContactID bigint NOT NULL,
AttributeID bigint NOT NULL,
StringValue varchar(512),
NumberValue float,
DateTimeValue timestamp,
CONSTRAINT ACCT_iCHOfferAttrib_PK
PRIMARY KEY (ContactID, AttributeID)
);
CREATE TABLE ACCT_UACI_CHStaging (
ContactID bigint NOT NULL,
TreatmentCode varchar(512),
CampaignID bigint,
OfferID bigint,
CellID bigint,
AccountID varchar(30),
ContactDate timestamp,
ExpirationDateTime timestamp,
EffectiveDateTime timestamp,
ContactType int,
UserDefinedFields char(18),
Mark bigint NOT NULL DEFAULT 0,
RTSelectionMethod bigint,
CONSTRAINT ACCT_iCHStaging_PK
PRIMARY KEY (ContactID)
);
CREATE TABLE ACCT_UACI_UserEventActivity
(
SeqNum bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
ICID bigint NOT NULL,
ICName varchar(64) NOT NULL,
CategoryID bigint NOT NULL,
CategoryName varchar(64) NOT NULL,
EventID bigint NOT NULL,
EventName varchar(64) NOT NULL,
TimeID bigint,
DateID bigint,
Occurrences bigint NOT NULL,
AccountID varchar(30) not null,
CONSTRAINT iUserEventActivity_PK
PRIMARY KEY (SeqNum)
);
create table ACCT_UACI_EventPatternState
(
UpdateTime bigint not null,
State varchar(1000) for bit data,
AccountID varchar(30) not null,
CONSTRAINT iCustomerPatternState_PK
PRIMARY KEY (AccountID,UpdateTime)
);
ALTER TABLE ACCT_UACI_CHOfferAttrib
ADD CONSTRAINT ACCT_iCHOfferAttrib_FK1
FOREIGN KEY (ContactID)
REFERENCES ACCT_UACI_CHStaging (ContactID);