Working with Fields
Overview
From the Tables screen, you can add, edit, and view the fields in a table. 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 table consists of one more fields, which can be populated with data received through various mechanisms, such as an import or an API request. Fields can be limited to the type of data that the field will accept. A "ZIP Code" field, for example, should only contain numbers.
Fields can also be populated based on business rules or Filters (see "Create a New Calculated Field" below for more details on these types of fields).
Features
|
To create a new Field:
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.
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.
In addition to the standard options, the platform supports the following "advanced" data types, which are used for more specific purposes within Messaging:
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.
The following options are available only at field creation time and can't be changed later..
The following option can be selected at any time, either at field creation time, or later.
The remaining 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.
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.
|
|
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 the field restrictions (if any) assigned to the field. To view or edit an existing field within a table:
|
View or Edit a 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 table consists of one more or fields, which can be populated with data received through various mechanisms, such as an import or an API request (see "Create a New Field" above for more details on this field type). Fields can also be populated based on business rules or Filters; these fields are called "Calculated Fields" (or "derived fields"). Calculated Fields can be used just like regular data fields – for example, you can create message Personalization and Filters based on these fields. Note: This feature is not available for Load and Send Tables. A common example of a Calculated Field is a "Score" field that's derived based on a consumer's purchase history, or activity, or some other attribute. You can define custom rules to calculate a consumer's Score, then use that Score value in a Filter to determine the audience of a marketing Campaign. You can create more complex scenarios where you're adding to or subtracting from a consumer's Score based on his or her actions. For example, if the consumer doesn't make a purchase in thirty days, you might decrease their Score value by some amount. Or, if they make a big purchase, you might increase their Score. Running complex calculations during Campaign processing, either within a Filter, or within Dynamic Content, can slow down the delivery of your Campaign. With the Calculated Fields feature, you can pre-calculate those values ahead of time, thereby improving the processing time to identify the audience, or to render content. You can schedule these calculations to update on a regular schedule, or initiate them based on the receipt of a data import. The results of the calculations are then inserted into the database, so that they're already available when the Campaign deploys, rather than having to calculate them "on the fly" at deployment time. A Calculated Field consists of one or more logical criteria. These criteria are referred to as "Rule / Value Pairs" because they consist of both a condition to be met (the "Rule") and a result (the "Value" to be assigned when the condition is met). Calculated Fields also support the use of a "default" value, which will be assigned to records that don't meet any of the defined Rules. The "Rule" for each Rule / Value Pair is defined through the use of a Filter. If a record is included in the Filter's result set, then that record meets that Rule, and will be assigned the corresponding Value. If your Calculated Field consists of more than one Rule / Value Pair, the system offers two different logical structures for how to join the rules together. The default option is an IF > ELSE IF structure. This structure is useful if you know you want a record to fall into only one rule, and to be assigned only one Value. The system works from the top down, evaluating records against the first rule. Only the non-matching records drop down to the second rule. Once a record matches a rule, it's removed from any further evaluations. Therefore, the sequence of the rules is important, because even if a record qualifies for more than one rule, it would receive only the value from the FIRST rule to which it matched. The second option is an IF > IF structure. In this structure, a record can potentially match to multiple rules, and therefore would receive multiple values, which get concatenated together. For example, if rule A was true and its value was "100;" and rule B was true and its value was "50," the system would store "10050" in the Calculated Field. The system evaluates every record against every rule, so even if a record met the first rule, it would still get evaluated against all subsequent rules as well. To create a new calculated field:
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 is 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.
Note: When a Calculated Field schedule runs, it derives and populates the field for every record in the table. Conversely, if you run a Calculated Field as part of an import process, the system derives and populates that field only for the records in the import file (see Data Maps for more details).
|
Create a New Calculated Field
|
Once you've saved a new field, you can optionally make the field "restricted." A restricted field will display only a specific set of values when you build Filter criteria using this field. For example, if you're creating a "State" field, you could restrict it to display only valid state names or abbreviations. Restricted fields are useful at preventing user data entry errors, because the system presents the user with a drop-down menu of acceptable values, instead of a free-form text entry field. Note: This feature is not available for Load and Send Tables. To make a restricted field:
|
Set Field Restrictions
|
When you define a new Campaign in Messaging, you must define the source table (usually something like "Customers" or "Recipients") that the Campaign will use to get its recipients, and to update status fields. For Campaigns in the Email, SMS, and LINE channels, the Campaign's source table must be linked to the Sender Profile that you want to use for your Campaign. For more information on how Messaging uses Sender Profiles to manage recipient eligibility, please see Permission Management: Sender Profiles. Note: The Push Notification channel utilizes Application Groups in place of Sender Profiles. See "Assign an Application Group to a Table" below for more details. To assign a Sender Profile to a Table:
Note: Once you assign a Sender Profile to a table, you can't remove that assignment. You can, however, later assign additional Sender Profiles to a field.
The system automatically creates a new "Sender Profile Status ID" field for each selected Sender Profile. The Status ID field uses Status ID Codes to indicate the eligibility of this consumer to be included in a Campaign. You'll see this new Status ID field listed on the Table screen with the default name of: "<Field name> <Sender Profile name> Status ID."
|
Assign a Sender Profile
|
When you define a new Campaign in Messaging, you must define the source table (usually something like "Customers" or "Recipients") that the Campaign will use to get its recipients, and to update status fields. For Push Notification Campaigns, the Campaign's source table must be linked to the Application Group that you want to use for your Campaign. The Application Group controls the app (or apps) to which your marketing message will be sent. Note: The Email, SMS Text, and LINE channels utilize Sender Profiles in place of Application Groups . See "Assign a Sender Profile to a Table" above for more details. To assign an Application Group to a Table:
Note: Once you assign a Application Group to a table, you can't remove that assignment. You can, however, later assign additional Application Groups to a field.
The system automatically creates a new "Application Group Status ID" field for each selected Application Group. The Status ID field uses Status ID Codes to indicate the eligibility of this consumer to be included in a campaign. You'll see this new Status ID field listed on the Table screen with the default name of: "<Field name> <Application Group name> Status ID."
|
Assign an Application Group
|
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:
To define the Unique Identifier:
|
Define the Unique Identifier
|
Messaging doesn't allow you to permanently delete unwanted fields from a table; however, you can deactivate a field. An inactive field still exists on the table, but it's removed from all "views" of this table (such as the list of fields available when selecting a Personalization field). Inactive fields can also later be reactivated if you find that you need to begin using this field again. To deactivate a field:
To reactivate a field:
|
Activate / Deactivate Fields
|
The platform allows you to generate a report showing counts of each unique value stored within a field. This report can optionally be used to create Field Restrictions (see "Set Field Restrictions" above for more details on this feature). Note: This feature is not available for Load and Send Tables. To generate the Field Statistics report:
|
Calculate Field Statistics
|
Messaging allows you to rearrange the fields in a table into a custom sequence. This sequence is only for presentation purposes, as it controls how the fields are displayed within the table "views" throughout the platform, such as in the Personalization Pane and the Record Lookup screen. You can customize the field sequence so that the most-commonly used fields are at the top in order to make it easier to find and select those fields. For example, let's say you often use the "First Name" field in your Campaign personalization. You could move the "First Name" field to the top of the table, which would cause it to display at the top of the list of fields in the Personalization Pane. To reorder the fields in a table:
|
Reorder Fields