Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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

Note
titleChannel 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

Note
* These fields are mapped in the campaign template, and therefore are populated automatically by MarketWide.


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 criteria 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

Note
* Mapped in the campaign template and populated automatically by MarketWide.


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

Note
* Mapped in the campaign template and populated automatically by MarketWide.


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

Note
* Mapped in the solicit template and populated automatically by MarketWide.


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