Load and Send Tables

The Tables screen is also used to manage tables used for the platform's Load and Send feature. Load and Send allows you to import "campaign-ready" files containing all the information needed to build and deploy the Campaign (such as personalization fields, and contact information like Email Address). This data is loaded into a special table dedicated specifically to the Load and Send feature, rather than into the regular relational database tables. Tables that are intended for use in a Load and Send Campaign differ slightly from regular relational database tables, as follows:

  • Table joins, calculated fields, and restricted fields aren't supported.

  • Certain Data Types aren't supported.

  • The table MUST have a Unique Identifier defined.

Create a New Load and Send Table

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).

View or Edit a Load and Send Table

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.

Create a New Load and Send Field

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.

  1. 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.

  1. 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 Cheetah Digital by Zeta:

        • 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.

  1. 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.

  1. 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.

  1. 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.

        • 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.

  1. 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.

  2. 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.

View or Edit a Load and Send Field

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.

Define the Unique Identifier

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.