Admin: Offer History
Overview
Offer History refers to a table containing a unique record of each contact across channels, at multiple levels of resolution (e.g. individual/household/address). Each time an interaction containing a fulfilment activity is executed in RPI, a row is inserted for every member of the target audience along with information about the fulfilment activity (e.g. email, SMS, push).
The Offer History table stores data about the campaign (e.g. interaction name, offer name) and userdefined attributes (e.g. individual ID, household ID, contact information). Supplementary offer history tables (Offer History %%) facilitate the persistence of a user-defined set of attributes and other metadata to be captured at time of contact (e.g. campaign code, package codes, segment codes).
Offer History table data provides a holistic understanding of overall outbound contact strategy and cadence, and allows for users to leverage this data downstream in reporting or as criteria in rules within other campaigns.
Offer History tables
The Interaction offer history tables are created in the data warehouse when users create audience definitions in the Administration Interface.
The tables are described below:
Offer History
The main offer history table, which tracks a contact’s audience output membership and the offer received. The keys persisted are configurable at audience definition administration.
Offer History meta
This table persists audience output cell level metadata. The metadata persisted is configurable as part of the audience definition administration.
Offer History states
Use to track channel specific states or events that are captured post-execution of an offer activity.
Offer History content
This table tracks the version of an offer sent to an individual. If the offer contains dynamic content, the instance of the content sent to an individual is also tracked.
Offer History details
This table provides a synopsis of a specific fulfillment activity’s execution (useful for reporting purposes).
Offer History content meta
This table is used to persist metadata ascribed to individual pieces of content in a smart asset.
Sandbox tables
The “sandbox” tables are copies of the Offer History and Offer History Meta tables that are used for testing interactions.
Field descriptions
Fields referenced in the tables above are as follows:
RPContactID: Unique ID used to track an individual’s receipt of an offer.
DataflowID: Binds an individual to the instance of the audience used to select them.
OutputID: Audience output (offer cell) ID.
Output name: Audience output (segment) name.
WorkflowID: Tracks the workflow instance that ran the activity.
ChannelExecutionID: Tracks the instance of the activity/channel that fulfills the offer.
OfferTemplateInstanceID: The version of offer content sent via the offer activity.
OfferActivityName: Name of the offer activity.
ChannelName: Name of the fulfillment channel.
OfferChannelSubName: Name of the channel-specific activity that executed an element of an offer activity.
OfferName: Name of the offer.
FulfillmentCode: The user-supplied fulfillment code used for planning and reporting.
Timestamp: The time the record was committed to offer history.
AddressKey: Any channel specific address key (e.g., Email Address/Cell number).
Selected: Indicates if the record was sent to a third party channel (accounts for dedupes etc.).
FulfillmentState: The name of the fulfillment state that happened post-execution.
EventName: Additional event information (e.g., URL for Email click through).
MetricValue: Any associated value linked to the fulfillment state event.
OfferCode: The instance of dynamic content sent to an individual.
FirstExecutionDate: Date and time at which activity started execution.
LastExecutionDate: Date and time at which activity finished execution.
ExportFileName: Name of export file created during activity execution.
DeliveryMethod: Delivery method of channel through which fulfilled.
Activity Name: Name of activity.
ActivitySubName: Name given to the offer/channel list item when configuring offer activity’s set of channels.
InteractionName: Name of interaction in which activity executed.
IsRolledBack: Flag indicating whether rolled back.
RolledBackDate: Date and time of rollback.
TargetCount: Number of records targeted by fulfillment activity.
SeedCount: Number of seeds targeted by fulfillment activity.
ContentID: Web content’s Published folder ID.
Slot: ID representing content for particular slot to be received by specific individualAssetName: Name of the smart asset in respect of which metadata has been captured.
Metadata: Name/value pair representing metadata attribute name and value provided for the same.
Offer History queues
The offer history queuing mechanism is designed to minimize the potential for insert contention resulting in the occurrence of blocking at an RPI cluster’s offer history tables.
Two tables in each tenant’s Interaction_[tenantname] database facilitate the queuing mechanism:
op_OfferHistoryInsertQueue: this table is used by RPI to temporarily store details of jobs containing records to be inserted into the offer history or offer history sandbox table.
op_OfferHistoryInsertConfiguration: this table is used to store details of queues configured in the current RPI installation.
If no records are present, the queuing mechanism is not used. A single row in the table represents a single queue. The table contains the following columns:
QueueID: the key column, the value for which is auto-generated.
TimeoutMinutes: the time for which records are to be maintained in a queue, after which an insert is performed. This timeout defines the maximum length of time that an insert is permitted to hold up a queue before the next job in line is permitted to start inserting records.
MaximumInsertSize: defines the limit as to the maximum number of records for a job that can be executed via the queue. This column allows certain queues to be reserved for smaller inserts, minimizing the potential for blocking by larger inserts. If the number of records to be inserted in a job exceeds all available queue limits, the system chooses the queue with the largest MaximumInsertSize. If this column is Null, all jobs may use it.
When performing offer history or offer history sandbox inserts, if more than one queue exists, one insert job at each can be executed concurrently. The next job uses the queue with the lowest permissible MaximumInsertSize value. A queue with an appropriate MaximumInsertSize value that has been set will be selected in preference to one where he value has not been set. By adopting this approach, the system retains larger-capacity queues for larger inserts, minimizing the chance that a smaller insert will be blocked by a larger one.
The queuing mechanism is used when executing interaction workflows in both Test and Production mode. It is used when executing control, export and offer activities. A given activity’s results log describes the queue that was used.