Load and Send Tables

To create a new table for use in a Load and Send Campaign:

1. Above the list of existing tables, click the New Item button. The Workspace is refreshed to show a blank Tables screen.

2. Place a check mark in the "Load & Send Table" check box. The system enters a default Display Name of "Load_And_Send."

3. You can use the default Display Name, or optionally enter your own custom name. In the "Display Name" field, enter a custom name for this new table. The "Table Name" field is automatically populated based on what you enter in the "Display Name" field. The "Display Name" value is automatically converted to all lower-case, and spaces are replaced with underscores.

4. Optionally, enter a different table name in the "Table Name" field. Only alphanumeric characters and underscores are valid entries in the Table Name (no spaces or symbols).

5. Click save. The system saves the new table and displays the Table details screen. From this screen, you can now create new fields (see "Create a New Field" below for more details) and define the Unique Identifier for the table (see "Define the Unique Identifier" below for more information on Unique Identifiers).

 

To view or edit an existing Load and Send table:

1. When the screen is displayed, a list of all the current tables is displayed in the left-hand side of the Workspace. Optionally, you can filter this list by typing in all or part of a table name in the "Search by Name" field.

2. Click on the table that you want to view. The Workspace is refreshed to show the details of the selected table.

3. Optionally, to view detailed information about the table, click the Table tab in the Tool Ribbon. The Item Details screen is displayed, showing who created the item, who modified it last, and what the last actions taken on the item were. On this screen, click "Related Items" in the Function Menu to see other items in the system that reference or utilize this table. When finished, click the Edit tab in the Tool Ribbon to return to the main edit screen.

4. Optionally, to rename the table, click Edit > Actions > Rename in the Tool Ribbon. A "Rename Item" dialog box is displayed. Enter a new name for the table, then click save new name.

5. Make any necessary changes to the Fields in this table (see Working with Fields for more details).

6. To save your changes, click Edit > Actions > Save in the Tool Ribbon.

 

In database terminology, a field (or "column") represents a single type of information that you're storing in your database, such as email addresses or ZIP codes, for example. A Load and Send table consists of one more fields, which can be populated with data received through an import file. Fields can be limited to the type of data that the field will accept. A "ZIP Code" field, for example, should only contain numbers.

To create a new field for a Load and Send table:

1. On the Table details screen, click Edit > Fields > New Field in the Tool Ribbon. A blank Field details screen is displayed.

2. In the "Display Name" field, enter a name for this new Field. The "Column Name" field is automatically populated based on what you enter in the "Display Name" field. The "Display Name" version of the field name is automatically converted to all lower-case, and spaces are replaced with underscores.

Note: The "Display Name" and the "Column Name" don't have to match. Column Names can be reused across tables, if for example, you have a situation where different Display Names are desired, but the Column Names should be the same.

3. Optionally, enter a different name in the "Column Name" field. Only alphanumeric characters and underscores are valid entries in the Column Name (no spaces or symbols).

The next step is to select the "Data Type" for your new field. The different types allow for proper formatting and handling of data when it is saved into that field. Once a Data Type has been set, it can't be changed, so it's important to know what the field will be used for in order to make the correct choice. As an example, creating a Date/Time field will allow you to use that field for date calculations. Data fields can be formatted for use in Campaigns such as formatting of dates, padding, decimal places, dollar signs, etc.

4. From the "Data Type" drop-down menu, select the appropriate data type. The "standard" data type options are:

• String -- Accepts all alphanumeric values, up to 255 characters in length.

• Integer -- Accepts round numbers, up to approximately 2 billion.

• Date / Time -- Accepts valid date values.

• Money / Decimal -- Accepts numbers with decimals.

In addition to the standard options, the platform supports the following "advanced" data types, which are used for more specific purposes within Messaging:

4. • Preference -- A special flag used to indicate a user preference, such as an opt-in / opt-out indicator. For more information on Preferences, see Permission Management - Preferences.

   • Email -- Validates that the value conforms to the standard email format, and allows you to select a Sender Profile (see Working with Fields for details); up to 255 characters in length.

   • Big Integer -- Used for values greater than 2 billion.

   • Long String -- Used for long text string values, such as a comment field; up to 8000 characters in length.

5. By default, a new field will be indexed, which improves Filter performance. If you don't want to index this field, uncheck the "Index this field" check box.

Note: Import performance is enhanced when fields that don’t require indexing are not indexed during the update process. Some fields such as names or parts of the postal address may not require such indexing. Indexing should be disabled only by database experts who have discussed the potential performance impact. Disabling indexing on a field will significantly slow down performance of Campaigns and Filters that use that field.

6. If you selected "Email" as the Data Type above in step 4, an additional check box labeled "Persistent Field" check box is displayed on the screen. This option is mainly intended for clients who use both Load and Send Campaigns, and regular Campaigns with a full, relational database. If you flag this Email field as a "Persistent Field," then the system will use this field to synchronize the consumers' opt-in / opt-out status across the two Campaign methods. Optionally, place a check mark in the "Persistent Field" check box. This option is available only at field creation time and can't be changed later.

• Load and Send Only customers: The decision to make the Email field Persistent depends on your business requirements and security restrictions. If you don't want to maintain any Personally Identifiable Information (PII) in the platform, then you should NOT mark the Email field as Persistent. In this manner, the Email data will be purged along with the rest of the temporary version, after the data retention period.

• Hybrid customers: For hybrid customers using both a relational database and a Load and Send table, you should mark the Email field as Persistent, as this field will be used to synchronize consumer opt-out requests received from the two different sending methods.

7. The check boxes listed in the "Additional Information" section are disabled, and are not actually configurable from here. These options are all set elsewhere within the platform, and are checked / unchecked by the system.

1. • Is this field the Status ID of another property: Status fields are a special type of field that are created automatically by the system when you assign a Sender Profile to a table.  

   • Is this field the Primary Key (PK) ID: The PKID is a special field that gets created automatically for every new table. This field contains a system-generated identifier that uniquely defines each record.

   • Is this field the Alternate Key (AK) value: The Alternate Key is also referred to as the Unique Identifier, and is defined on the main Table details screen. See Define a Unique Identifier for more details.

   • Is this field the Preference: Preference fields are a special type of field that indicate a consumer's subscription preferences. Preference fields are defined by selecting "Preference" as the Data Type.  

The "PURL Options" section allows you to set various options if this field is going to be used to generate a Personalized URL, or "PURL." A PURL utilizes data from a user’s record to customize a URL specifically for that recipient. When creating a PURL field, users have the option to select if the PURL will exist at the beginning or the end of the domain (ex. John.company.com or company.com/John). This option allows for the field prefix to be a given domain in order to create a personalized URL to send out to each recipient. This option is available only at field creation time and can't be changed later.

8. To set up the field as a PURL, check the PURL check box. From the "Select a Data Field" drop-down menu, select the field that will make up the PURL, then click the add button (plus-sign icon). Repeat this step as needed to select additional fields. Fields can be removed by clicking the delete button ("X" icon ) next to the field.

9. When finished, click save to save the new field and remain on the details screen for this field. Or, if you have additional fields you need to define, click save and new. The system saves the new field and displays a blank Field details screen, allowing you to enter the next new field. If you want to return to the Table screen, click the link in the top-right corner of the screen that shows the Table name.

 

Most information about a field is "locked" and can't be modified once you save that new field. The only editing options available for a field are its "display name" and indexing.

To view or edit an existing field within a Load and Send table:

1. On the Table details screen, click on the desired field name. The Field details screen is displayed.

2. Optionally, to rename the field, enter a new name in the "Display Name" text box (you can't modify the Column Name or the Data Type).

3. Optionally, you can toggle on or off indexing for this field.

4. To save your changes, click save. If you want to return to the Table screen, click the link in the top-right corner of the screen that shows the Table name.

 

In order to ensure that records in your table are created and updated properly, each table can be set up with a Unique Identifier (also referred to as the "Alternate Key"). The Unique Identifier can be based on a single field, or on a combination of more than one field.  

Note: A Unique Identifier is required for a Load and Send table.

For example, let's say you create a new "Customer" table to store consumer information, and you decide that you want to use a combination of the Customer ID and the Email Address as the two fields that will uniquely identify each customer. You would assign these two fields (in that order) as the Unique Identifier. When you save the table, the system performs several actions.

First, the system assigns an "Alternate Key Sequence Number" to both of those fields. In this case, Customer ID would be "1" and Email Address would be "2," since that's the order in which you defined the Unique Identifier.

Second, the system creates a new String type field on this table called "ak_<tablename>" that concatenates the Customer ID and Email Address fields. The combination of these two values now serves as the unique value that will identify each consumer. For example:

Consumer A: ak_customer = '234567+john.smith@yahoo.com'

Consumer B: ak_customer = '112234+jane.doe@gmail.com'

Consumer C: ak_customer = '765432+bobjohnson@aol.com'

Third, the system creates a new Numeric type field called "pk_<tablename>_id" that stores the "Primary Key ID," or "PKID." The PKID is largely hidden from the platform's end-user, as it's not anything that you would use in a Filter or in personalizing your message content. A new PKID is generated and assigned to each unique Alternate Key. Continuing the above example:

Consumer A: ak_customer = '234567+john.smith@yahoo.com'  pk_customer_id = '1'

Consumer B: ak_customer = '112234+jane.doe@gmail.com'  pk_customer_id = '2'

Consumer C: ak_customer = '765432+bobjohnson@aol.com'  pk_customer_id = '3'

Note: The Alternate Key field and the Primary Key ID field are both displayed within the "Inactive Fields" section of the Table detail screen. These fields are marked as "inactive" so that they don't appear within any "views" of this table, such as the list of fields available when selecting a Personalization field, for example.

These two layers of identifiers (sometimes called "surrogate IDs") are needed because the primary key on a record can never be changed. However, customers can, and often do, change their email address. The surrogate ID design accommodates the flexibility needed for something like an email address. If Consumer A in the above example gets a new email address, his Alternate Key would change, but his PKID would not. In this manner, you keep the consumer record intact (name, postal address, purchase history, etc.), but are still able to update the email address.

Unique Identifiers are not required. If you create a new table, and don't define a Unique Identifier, the system will simply utilize the PKID as the sole unique identifier for this table. In this situation, the system utilizes the PKID as the table's Global Unique Identifier (GUID).  

The step below describe how to define a Unique Identifier. When performing these steps, please note the following:

• Once a Unique Identifier has been defined and saved, it can't be changed.

• If you're using multiple fields in your Unique Identifier, be sure to add ALL of them, THEN save the changes. Don't add one field, then save, then try to add the second one.

• If using more than one field, the sequence in which you assign those fields is important, as the table will save them in the exact order they are listed.

To define the Unique Identifier:

1. On the Table details screen, located the desired field name within the list of fields.

2. Drag the desired field, and drop it within the "Unique Identifiers" section.

3. Repeat steps 1 and 2 as needed to add more fields to the Unique Identifier. If necessary, you can drag-and-drop the fields within the "Unique Identifiers" section to arrange them in the desired sequence.

4. In the Tool Ribbon, click Edit > Actions > Save.