As TouchPoint processes campaigns, promotion history information is automatically recorded, capturing complete details about every offer made to every customer or prospect. Promotion history is stored in three database tables, plus one table for each required marketing channel (e.g., direct mail, email, telemarketing):
Table | Purpose |
|---|---|
Campaign | Campaign-level summary information, one row per MarketWide campaign |
Segment | Segment-level information, one row per campaign segment |
PromoHistory | Solicited customer and prospect information, one row for each solicitation generated by a campaign |
Channel* | Channel-specific information such as email subject lines or envelope teasers, one row for each channel used by a campaign |
Channel Tables*
Most companies have at least two channel tables: one for direct mail and another for email.
MarketWide offers a great deal of flexibility in deciding what information is relevant for tracking purposes. As such, while certain fields are required in these tables, more columns can be added by a database administrator if needed. Additionally, table and field names can be changed according to the conventions or standards of each installation; however, once established, field names and datatypes must be consistent.
Field Mappings
Where applicable, MarketWide provides default values known as “mappings” for promotion history fields. These are set within its Campaign Template editor. Mappings essentially act as placeholders for system or campaign information that is known at the time a campaign runs. Example include the current date/time, or the UserID of the person running a campaign. For each table described in this document, its available mappings are indicated.
Campaign Table
The Campaign table keeps track of the highest level of promotional history, the campaign itself.
Required Fields | Data Type | Notes |
|---|---|---|
Campaign ID | Character | Unique Campaign Identifier; fixed or variable length |
Campaign Status | Character | Status of the campaign; used internally by MarketWide; one character |
Recommended Fields | Data Type | Notes |
Campaign Description* | Character | Description of the posted campaign; variable length |
Campaign Date* | Date/Time | Date the campaign was written to Promotion History |
Campaign Matches* | Number | Number of consumers selected by the campaign logic |
Campaign Universe* | Number | Number of consumers solicited by this campaign; may be fewer than Campaign Matches |
Total Solicits* | Number | Number of solicits output by the campaign; may be greater than Campaign Universe |
User ID* | Character | MarketWide User ID of the person running the campaign; variable length |
Other fields may be added if needed and are made available by TouchPoint during campaign creation.
Mappings
Following are the template mappings available for the Campaign table.
Mapping | Description |
|---|---|
Campaign Description | Description of the posted campaign |
Campaign Template | Name of the TouchPoint template used for the campaign |
Total Matches | Number of consumers who were selected by the campaign logic |
Total Kept | Number of consumers who were retained by the campaign logic; may be less than Total Matches |
Total Solicits | Total number of solicits output by the campaign; may be more or less than the number of consumers retained |
Total Unique Solicits | Number of consumers receiving at least one solicit by the campaign |
User ID | MarketWide userid of the person running the campaign |
Status | The campaigns status; internal MarketWide field |
Date_Time | Automatically inserts the date and time the campaign was run |
Ignore | The field is ignored but is displayed on the Campaign Report |
Zero | Sets the field to zero (0) |
Blank | Sets the field to blank |
Null | Sets a null value to the field |
Prompt | This field requires the user to enter a value |
Query | Adds custom SQL script to create your own automatic fill-in for the field |
Increment Max | The field is incrementally filled in, starting at one, for each subsequent campaign ran using the template. |
Primary Key and Indexes
Other than the primary key, no additional indexes are needed for the Campaign table.
Type | Field(s) |
|---|---|
Primary Key | Campaign ID |
Segment Table
The Segment table keeps track of the marketing segments within a campaign. A segment is a group of consumers who all meet the conditions specified by the segment; for example, people who placed at least three orders in the past 12 months with an average order size of $100.
The Segment table is one level “down” from the Campaign table and joins back to it via the Campaign ID column. It also requires a Segment ID.
Required Fields | Data Type | Notes |
|---|---|---|
Campaign ID | Character | Campaign Identifier; fixed or variable length; must use the same name, datatype, and length as the Campaign ID in the Campaign table |
Segment ID | Character | Unique Segment Identifier; fixed or variable length; unique within Campaign ID (i.e., two segments in a campaign cannot share the same Segment ID) |
Recommended Fields | Data Type | Notes |
Segment Description* | Character | Description of the segment entered by the user |
Segment Matches* | Number | Number of consumers who matched the criterial for this segment |
Segment Universe* | Number | Number of consumers for this segment who were solicited, may be fewer than Segment Matches |
Segment Solicits* | Number | Number of solicits output for this segment by the campaign; may be greater than Segment Universe |
Mappings
Following are the template mappings available for the Segment table.
Mapping | Description |
|---|---|
Segment Name | Name given to the segment by the user |
Segment Description | Description of the segment, entered by the user |
Segment Number | Sequential number of the segment within the campaign hierarchy, assigned by the system |
Match Count | Number of consumers selected by the segment |
Kept Count | Number of consumers “kept” for the segment (selected consumers can be kicked out of a campaign by householding rules or “nth” processing) |
Solicit Count | Total number of solicits sent to consumers matching this segment (consumers can be solicited more than once) |
Unique Solicit Count | Number of consumers selected for this segment who received at least one solicit |
Prompt | This mapping causes TouchPoint to prompt the user to enter a value for the assigned field |
Ignore | This setting effectively “hides” the field from TouchPoint; typically used for Segment table columns that are populated by other processes |
Zero | Sets the field to zero (0) |
Blank | Sets the field to blank |
Null | Assigns a null value to the field |
Primary Key and Indexes
Other than the primary key, no additional indexes are needed for the Segment table.
Type | Field(s) |
|---|---|
Primary Key | Campaign ID, Segment ID |
Promo History Table
The Promo History table keeps track of every solicit generated by the campaign, along with the unique database ID of each person solicited. A single person can receive multiple campaign solicits, and each one is tracked here. As such, this table can become quite large.
Required Fields | Data Type | Notes |
|---|---|---|
Customer ID | Character or Number | The unique database identifier for everyone solicited by the campaign |
Campaign ID | Character | Campaign Identifier; fixed or variable length; must use the same name, datatype, and length as the Campaign ID in the Campaign table |
Segment ID | Character | Segment Identifier; fixed or variable length; must use the same name, datatype, and length as the Segment ID in the Segment table |
Touch ID | Character | Unique identifier assigned to a solicitation (e.g., a catalog number or email offer ID); multiple consumers in a campaign can receive the same Touch ID |
Effort Number* | Number | Sequential touch number, generated by TouchPoint automatically; incremented for each consumer each time the consumer is solicited |
Channel Table* | Character | Name of the channel table storing solicitation details for the touch made to each individual targeted by the campaign |
Recommended Fields | Data Type | Notes |
Model scores or RFM variables | Character | Score of a customer or prospect at the time of solicitation. |
Household ID | Character or Number | The household identifier of everyone solicited by the campaign; often used by catalog campaigns |
Mappings
Following are the template mappings available for the Promo History table.
Mapping | Description |
|---|---|
Auto Assign | Automatically assigns a value to the field using another field on the database; similar to Pass-Thru |
Prompt | Prompts the user to enter a value during campaign creation |
Ignore | Instructs TouchPoint to ignore the field; used in cases where a Promo History column is updated by another process |
Query | Causes TouchPoint to run a custom SQL query to populate the field |
Zero | Sets the field to zero (0) |
Blank | Sets the field to blank |
Pass-Thru | Populates the field using a campaign Pass-Thru; similar to Auto Assign |
Channel Table | Name of the solicit channel table used by the touch; maps to the Channel Table field |
Solicit Template | Name of the solicit template being used by the touch |
Touch Description | Description of the touch entered by the user |
Touch Sequence | Sequential number for each touch; maps to the Effort Number field |
Primary Key and Indexes
The Promo History table uses a primary key and two indexes.
Type | Field(s) |
|---|---|
Primary Key | Customer ID, Campaign ID, Segment ID, Touch ID, Effort Number |
Index | Campaign ID, Segment ID |
Index | Campaign ID, Touch ID |
Channel Table(s)
Channel Tables are used to record channel-specific information such as catalog numbers or email subject lines. One channel table is required for each solicitation channel used by TouchPoint, and – other than required by MarketWide – the fields differ depending on the channel being used. Channel tables are configured using MarketWide’s Solicit Template Editor.
All Channel Tables
Required Fields | Data Type | Notes |
|---|---|---|
Campaign ID | Character | Campaign Identifier; fixed or variable length; must use the same name, datatype, and length as the Campaign ID in the Campaign table |
Touch ID | Character | Unique identifier for Touches (i.e., solicits) within a campaign |
Recommended Fields | Data Type | Notes |
Solicit Count* | Number | Number of consumers solicited by this touch |
Status* | Character | Status of the touch; used internally by MarketWide; one character |
Touch Description* | Character | Description of the touch, entered by the user |
Social Media Channel Table
MarketWide’s TouchPoint is integrated with Facebook, allowing campaigns to target Facebook custom audiences. In addition to the fields needed for all channel tables, the following fields are required or recommended for this channel.
Required Fields | Data Type | Notes |
|---|---|---|
Ad Name | Character | Facebook Ad Name; variable length |
Creative Name | Character | Facebook Ad Creative Name; variable length |
Ad Status | Character | Facebook Ad Status; one character |
Recommended Fields | Data Type | Notes |
StartDate | Date | Facebook Start Date |
EndDate | Date | Facebook Ad End Date |
Budget Amount | Currency | Facebook Ad Budget Amount |
BudgetType | Character | Facebook Ad Budget Amount |
BillingType | Character | Facebook Billing Type |
Goal | Character | Facebook Goal |
ImageSource | Character | Facebook Image Source |
LinkTarget | Character | Facebook Link Target |
PageID | Character | Facebook Page ID |
Body | Character | Facebook Ad Body |
Title | Character | Facebook Ad Title |
Email Channel Table
In addition to the fields needed for all channel tables, the following fields are required for the Email Channel table.
Required Fields | Data Type | Notes |
|---|---|---|
Subject Line | Character | Email subject line; variable length; must be Unicode for International email |
Preheader | Character | Email preheader string; variable length; must be Unicode for International email |
HTML File | Character | Email HTML template name; variable length |
Recommended Fields | Data Type | Notes |
(Other email fields as needed) | ||
Other Channel Tables
Additional tables can be created for channels such as Direct Mail, Telemarketing, and Catalog. As shown above, each of these must have the Campaign ID and Touch ID fields, along with whatever other columns are necessary to support the channel. Examples of these include a call center script (for telemarketing) or catalog number (for catalog).
Mappings
Following are the template mappings available for Channel tables. These are set in MarketWide’s Solicit Template Editor.
Mapping | Description |
|---|---|
Prompt | Prompts the user to enter a value during campaign creation |
Ignore | Instructs TouchPoint to ignore the field; used in cases where the column is updated by another process |
Query | Causes TouchPoint to run a custom SQL query to populate the field |
Zero | Sets the field to zero (0) |
Blank | Sets the field to blank |
Pass-Thru | This allows fields to be pulled from the database and output along with the solicit; for example |
Pull_From_Campaign | Allows campaign-level information to be carried through to a channel used within that campaign |
Status | Used for mapping the Channel Status field |
Solicit Count | Number of customers solicited by this touch |
Touch Description | Description of the touch, entered by the user |
Primary Key and Indexes
No indexes are needed on Channel tables. Following is the primary key.
Type | Field(s) |
|---|---|
Primary Key | Campaign ID, Touch ID |