Offer history configuration

Overview

This page provides baseline recommendations for configuring offer history tables for audience definitions in Redpoint Interaction (RPI).

Our recommendation is always to have only one set of offer history tables within an RPI tenant.

Reasons for having one offer history table

There are multiple reasons to try to maintain a single offer history table, but here are the primary reasons:

Consistent querying for selection and suppression purposes
Reporting

Exceptions

There are also cases where you may consider additional offer history tables; however this pattern should represent an exception case and not the standard:

Queue listeners: These types of messages may require separate offer history tables due to a few factors:
- The frequency at which they run
- Potential queuing that may occur if these activities are writing to the standard offer history table
Social exports/channels: These channels do not typically have a feedback loop, and a large volume of records may be sent out, including the same records being sent to multiple social/display channels. Key drivers for using multiple offer history tables for Social/Display channels are large volume campaigns.
Compliance: If there is a need to capture all fields that are used for personalization for compliance reasons, it may be beneficial to leverage an additional offer history for these comms. These data points may be needed for determining what exactly was sent to someone, when, and why.

Additional offer history best practices

In addition to having a single offer history table, there are other things to consider when creating and maintaining offer history tables.

Separate offer history schema

It is recommended that you create a separate schema in the primary data warehouse to keep the offer history tables in.

In RPI v7.4 and later: This is set in the RPI Tenant configuration, filling in the Write Schema setting with the name of the offer history schema. It defaults to using the primary data warehouse.
In RPI v7.3 and earlier: This is set in the RPI client in the configuration > system configuration section, filling in the OfferHistorySchema setting with the name of the offer history schema. It defaults to using the primary data warehouse.

This configuration setting needs to be made when planning the RPI deployment and should be one of the first things configured in RPI before doing anything else. If not completed before creating the Audience Definitions, you will need to fall back to the default configuration.

Configuring this setting provides a few benefits, which are listed below.

All data that RPI writes will occur in the offer history schema, and the data warehouse schema will only be read from.
All temp tables along with the offer history tables are written to the offer history schema. This keeps the primary data warehouse schema clean of any tables generated from RPI and prevents the primary data warehouse schema from being cluttered with temp tables.
In addition to offer history and temp tables, the following tables are written to the offer history schema:
- RPI_Web<*> tables, where the <*> represents the various web tables that are created by RPI for tracking the web/Realtime data.
- Suppression tables created for a channel.
- Tables created as part of the process of syncing data for native RPI channels.

Database table partitioning

Partitioning should be applied based on the timestamp in the offer history table. This will help with query performance and archiving as well. The timestamp will need to be used in the queries to offer history for the partition to be taken advantage of. So if partitioning is applied, end users should be educated on inclusion of the timestamp field in their selection rules whenever possible.

Offer history data archiving

To maintain optimally performing offer history tables, we recommend archiving data after a regular time interval in order to make the queries against offer history efficient. The amount of data that remains in the primary offer history table may depend on the business needs and the particular vertical of the business. Generally, we recommend 26 months of active offer history data and the remainder should be archived. The archived data should be put in another table within the database so that it can still be used if there is a need to look back beyond the 26 months.

Archiving can be done in various ways:

If partitioning is applied, the partition can be moved from the primary offer history table to the archive offer history table to maintain 26 months of data.
If partitioning is not applied to the offer history table, then the data will need to be copied out of the primary table, written to an archive table, and then deleted from the primary offer history table. This can be done using database scripts or RPDM automations.

Recommendation for maintaining a clean set of offer history tables

As you add and remove attributes from the audience definitions as you are building out an environment, be sure to delete any attributes (fields) that were created in the offer history table that will not be used. Given that RPI will not delete fields from offer history, this will need to be done in the database itself. If you have a field in production that has data in it, but it will no longer be used, then you should still keep the field and not drop it so that it can be referenced for interactions that used it previous to the field no longer being needed.

If you create multiple offer history tables when testing or doing dev work, be sure to clean up the tables that are not used, even in dev and test environments, otherwise they will never be removed and may cause confusion over time.

Common questions

I am setting up RPI for the first time. What fields should I consider when setting up audience definitions and offer history tables specifically?

At a minimum, any of the resolution IDs should be included in offer history. Why?

Selection
Suppression
Dedupe
The IDs must be defined in the offer history section of the audience definition to be available for using as a field to dedupe with. So, a field that is needed for deduplication by proxy needs to be written to offer history in order for it to be used for deduplication, but it is not required to be in the offer history table for any other reason.
Reporting

I originally set up RPI with a single channel for email, but I am now adding in a second channel. Should I consider a creating a second set of offer history tables?

Adding a channel does not require a new set of offer history tables to be used.

There are a couple of exceptions to this where you may want to introduce a separate offer history schema:

If you are introducing a channel like Facebook onboarding and you ONLY plan to use that offer history (OH) data in support of campaigns using that channel, and that channel may generate a lot of data in OH, you may want to consider a separate OH table to ensure campaigns against each channel execute optimally.
If you introduce a new resolution and audience definition in support of the additional channel, and it does not share a lot of the attributes that are being written to the current OH table, you may want to consider different OH tables. The reason is so that you do not want to end up with a wide OH table that generally has a lot of blank fields. One or two columns would not justify new OH tables, even if they were sparsely populated, but if there were 10 or more columns that would be empty a lot of the time, then it may make sense for a separate OH table.