Runtime environment staging tables to Unica Campaign history tables mapping

The Unica Interact contact history staging tables map to Unica 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 Unica 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 Unica Campaign system tables.
Note:

By default, successfully processed records in this table and UACI_CHOfferAttrib are deleted by the CH/RH ETL process. If, for any reason, such records are not deleted and you want to delete them, the following SQL statements can be used for deletion.

DELETE FROM UACI_CHOfferAttrib where ContactID in (SELECT ContactID FROM UACI_CHStaging where Mark > 0);

DELETE FROM UACI_CHStaging where Mark > 0;

If you want to delete the records that were processed with failure, which are not deleted by CH/RH ETL, the following SQL statements can be used.

DELETE FROM UACI_CHOfferAttrib where ContactID in (SELECT ContactID FROM UACI_CHStaging where Mark = -1);

DELETE FROM UACI_CHStaging where Mark = -1;

Table 1. Contact History

UACI_CHStaging

Unica Interact contact history staging table column name

Unica 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 Unica 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 Unica Campaign system tables.

Table 2. Offer attributes

UACI_CHOfferAttrib

Unica Interact contact history staging table column name

Unica 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 Unica 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 Unica Campaign system tables.
Note:

By default, successfully processed records in this table are deleted by the CH/RH ETL process. If, for any reason, such records are not deleted and you want to delete them, the following SQL statements can be used for deletion.

DELETE FROM UACI_RHStaging where Mark > 0;

If you want to delete the records that were processed with failure, which are not deleted by Ch/RH ETL, the following SQL statements can be used.

DELETE FROM UACI_RHStaging where Mark = -1;
Table 3. Response history

UACI_RHStaging

Unica Interact response history staging table column name

Unica 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 Unica 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 Unica Campaign contact and response history tables

If you have additional columns in your Unica 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 Unica Campaign system tables. Unica 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 Unica Campaign system tables. Unica Interact automatically populates the Score column with the score used for the offer.

Create new history tables in the Unica Campaign and staging tables in the Unica Interact

If you are using an audience level other than the Customer, then you will have to create new history tables in the Unica Campaign, and new staging tables in the Unica Interact.

For example, the below sample script is used in the IBM DB2® design time database to create history tables in the Unica 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 IBM DB2® database to create history staging tables in the Unica 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);