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 |