Unica Interact Learning Tables

When you install the Unica Interact design time server, you also run a SQL script to set up the required tables in your data source that Unica Interact needs to run the learning service. This document provides a data dictionary describing the structure and content of the learning tables.

UACI_OfferTxAll

This table serves as a staging area for the summary level statistics gathered by the learning service at the offer level. The background aggregator process will merge the data in this table into the UACI_OfferStatsAll table, and remove the processed rows from UACI_OfferTxAll.

Field Type Length Null? Description
SeqNum INT64 false Unique ID for a record in this table. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
OfferID INT64 true The unique ID of the offer. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
AcceptCount INT32 true The total number of accepts for this offer that has happened since the last insert.
PresentCount INT32 true The total number of presents (contacts) for this offer that has happened since the last insert.
LogTime DATETIME true The timestamp of when this record was added to this table.

UACI_AttributeValue

This table contains all the attribute values on which the system is currently learning.

Field Type Length Null? Description
AttributeName VARCHAR 128 false Name of the attribute on which the built-in learning algorithm will learn. Must be a valid database column name.
AttributeValue VARCHAR 128 false Valid value of the attribute on which the built-in learning algorithm will learn.
AttributeType INT32 false The datatype of the attributeValue (0:numeric, 1:string).

UACI_OfferStatsLck

This table serves as a mutex to prevent multiple learning aggregator processes from running concurrently.

Field Type Length Null? Description
LastMergeTimeStamp DATETIME false This field represents the last time the learning aggregator successfully ran.
LastTxId INT64 false This represents the last seqNum of the TX table that was processed by the aggregator process. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
CurrentTableName VARCHAR 100 false The name of the table staging offer stats data is being persisted into. It is either UACI_OfferStatsTx or UACI_OfferStatsTx_2
PrevTableName VARCHAR 100 false The name of the table staging offer stats data is not being persisted into. It is either UACI_OfferStatsTx_2 or UACI_OfferStatsTx

UACI_OfferStats

This table contains the offer statistics per attribute value that will later be served as input into the naïve bayes algorithm. Reports also rely on this table to display accept probabilities.

Field Type Length Null? Description
OfferID INT64 false The unique ID of the offer. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values
AttributeName VARCHAR 128 false The name of the attribute that this record represents. Must be a valid database column name.
AttributeValue VARCHAR 128 false A valid string containing the value/instance of the attribute that this record represents.
AcceptCount INT64 true The total number of accepts for this offer given the attribute value.
PresentCount INT64 true The total number of presents for this offer given the attribute value.
AttributeType INT32 false The datatype of the attributeValue (0:numeric, 1:string).
AcceptRWA FLOAT true The running percent accept for this offer given the RWA configuration.
RejectRWA FLOAT true The running percent reject for this offer given the RWA configuration.
Predictability INT32 true A flag that indicates if the statistics for this record is useful in predicting. 0 is not predictive. 1 is predictive. Every time the aggregator is run, this value is updated.
startTime INT32 false The startTime is used for calculate the time before first Recency Time of each OfferID. This field is populated when learning version is V2.

UACI_OfferStatsTx

This is a staging table where the Learning service logs its contact and response occurrences. This table contains no aggregated information. The information here is processed by a background thread which will remove rows that have been processed. The data here is used to update the final table UACI_OfferStats.

Field Type Length Null? Description
SeqNum INT64 Unique ID of a record in this table. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
OfferID INT64 true Unique ID of an offer. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
AttributeName VARCHAR 128 true Name of the attribute that this record represents. Must be a valid database column name.
AttributeValue VARCHAR 128 true Value/instance of the attribute that this record represents.
AcceptCount INT32 true The total number of accepts for this offer given the attribute value since the last insert into this table.
PresentCount INT32 true The total number of presents for this offer given the attribute value since the last insert into this table.
LogTime DATETIME true The timestamp of when this record was added to this table.
AttributeType INT32 true The data type of the attribute value (0:numeric, 1:string).

UACI_OfferStatsTx_2

This is a staging table where the Learning service logs its contact and response occurrences. This table has exactly the same structure as UACI_OfferStatsTx, and they are used to persist the staging data alternatively. Note that data is logged to this table only when built-in learning version 2 is selected in the Interact configuration.

UACI_OfferStatsTx_Syn is a synonym pointing to either UACI_OfferStatsTx or UACI_OfferStatsTx, and used as the target for persisting learning staging data. i_uaciofferstatstx UACI_OFFERSTATSTX OFFERID ATTRIBUTENAME ATTRIBUTEVALUE.

UACI_OfferStatsAll

This table contains the summary learning statistics at the offer level.

Field Type Length Null? Description
OfferID INT64 false The unique ID of the offer. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values
AcceptRWA FLOAT false The overall RWA average across all attributes for accepts.
AcceptCount INT64 true The overall count across all attributes for accepts.
PresentCount INT64 true The overall count across all attributes for presents.
startTime INT32 false The startTime is used for calculate the time before first Recency Time of each OfferID. This field is populated when learning version is V2.

UACI_LRNTAB_Ver

Contains the version information of the current learning tables installation.

Field Type Length Null? Description
VersionID INT64 false pk, unique ID of version table. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
VersionNumber VARCHAR 50 false Full version (major, minor, point, and build)
Parameters VARCHAR 255 true Optional parameters. This string value is currently not used and is here for future use.
CreateDate DATETIME false THe date when the Interact learning tables were installed.

UACI_AttributeList

Contains the list of attributes and its corresponding column information to support the "learning with Arrays" feature. The contents of the columns specified here dictate the where the system should look for values to learn on (versus just hard coding the attributes in the configuration).

Field Type Length Null? Description
AttributeName VARCHAR 64 false The name of the attribute which this record represents.
AttributeNameCol VARCHAR 64 false Fully qualified path to the column that contains the instances of the attribute name to learn on.
AttributeValCol VARCHAR 64 false Fully qualified path to the column that contains the instances of the attribute value to learn on.

UACI_LearningAttributeHist

This table logs the history of learning attributes and their values.

Field Type Length Null? Description
RecordTime BIGINT false Pk.The timestamp this record happened in the format of yyyyMMddhhmm
AttrName VARCHAR 100 false Pk.The name of the attribute
StringValue VARCHAR 512 true The value of the attribute if it is string type
NumericValue DOUBLE true The value of the attribute if it is numeric type
DateValue TIMESTAMP true The value of the attribute if it is date type
Count INT false The number of this value happened since the previous presistence