Creating Supplemental Tables
A Supplemental Table stores relevant information for each record that can be drawn on for personalization of the campaign message or form document. One or more supplemental data sources can be combined with a distribution list using data extraction fields. Unlike a Profile Extension Table (PET), which stores aggregated data, a Supplemental Table holds unaggregated data. For example, John Doe performed multiple browses or made multiple purchases.
By default, Oracle Responsys allows 250 Supplemental Tables per account. Set an expiration date for Supplemental Tables that you only need temporarily.
Tip: For new accounts provisioned after 19A, system administrators can choose to have a predefined set of assets (a profile list, PETs, and supplemental tables) automatically included in the account, based on an industry vertical. For more information, see Preloaded Data Assets.
You can create a Supplemental Table with a few different methods:
- Import a table that's been saved in a comma-separated or tab-delimited format from some other program.
- Create an empty table by specifying the table's fields.
- Create an empty table by extracting the field definitions from documents.
Primary key
A primary key uniquely identifies a record in your table. If your supplemental table, for example, stores information on recipient purchases, a recipient's email address could be used as primary key to identify each purchase as belonging to that recipient. Or if, for example, your Supplemental Table stores a product catalog, the product SKU number or product ID could be used as the primary key to identify products.
Data extraction key
In Responsys, a data extraction key can be used as:
- A foreign key between a Supplemental Table and a campaign's or form's profile table.more
As a foreign key, a data extraction key (or keys) is used to match a record in a Supplemental Table to a record in a Profile List for personalization in a campaign or form.
Note: A data extraction key will only work for personalization if the designated data extraction key column names exist in both the Supplemental Table and the Profile List. For example, it is not possible to have a Supplemental Table column of CITY match a profile table column of HOME_CITY for data extraction key purposes.
If a Supplemental Table is referenced by a campaign as a non-lookup data source, or is declared in a form’s prefill data, the personalization data used comes from the first matching record found by the database, where the data extraction key values match those of the recipient in the Profile List.
- A match key used by a form submission's merge into a given Supplemental Table.more
When a data extraction key is used as a match key, it is for storing form submission data into a Supplemental Table by way of a Form Rule to target a Supplemental Table. Responsys tries to match the data extraction key values from the form with the data extraction key column values in the Supplemental Table. If a match occurs, the first found record in the Supplemental Table is updated with the form data. If a match does not occur, Responsys inserts the form data as a new record in the Supplemental Table.
Note: The input data name, or names, must match the Supplemental Table’s data extraction key column name, or names.
Note: Declaring more than one column as data extraction key creates a composite key. That is, all column values must be the same between the Profile List and Supplemental table to obtain a match. Declaring more than one data extraction key column in a Supplemental Table does not establish a match priority sequence; it is not possible to establish a priority match rule set via data extraction keys.
Recommended usage
Use the Data Directive to personalize a single message based on an enumeration of multiple qualifying records within a single Supplemental table. For example, you could list all the stores matching the recipient's city. In this case, you would declare the Supplemental Table as a Lookup Table in the Campaign Data Sources. For Forms, you would not add the Supplemental table to the Form’s Prefill Data, and would instead reference the Supplemental table via the lookuprecords() built-in function.