Working with Fields

1. On the Table details screen, click Edit > 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 Cheetah Digital by Zeta:

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 "Assign a Sender Profile to a Table" below for details); up to 255 characters in length.

   • Phone -- Validates that the value conforms to the standard phone number format, and allows you to select a Sender Profile (see "Assign a Sender Profile to a Table" below for details).

   • Facebook User ID

   • Twitter User ID

   • Push Registration ID -- Allows you to select an Application Group (see "Assign an Application Group to a Table" below for details); up to 255 characters in length.

   • LINE Contact MID -- Allows you to select a Sender Profile (see "Assign a Sender Profile to a Table" below for details).

   • 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. The Additional Information section allows you to set a set a variety of optional configuration settings that help define how data is handled within this new field. Optionally, select one or more of the Additional Information options, as described below.

The following options are available only at field creation time and can't be changed later..

• Store multiple values: If checked, allows more than one value to be stored within the field. If this field is updated, the new value is added to the existing value, instead of replacing it.

• Store values per Child system: If checked, allows separate values to be stored in this field if it exists within multiple Child systems. The system will physically store this data in the Child system database schema, making it inaccessible to the Parent system, or to any other Child systems. For more information on how this feature is used to configure a Parent / Child database architecture, please see the Hierarchy Rules Help topic.

• Force values to be unique to one record: If checked, mandates that values in this field are unique.

The following option can be selected at any time, either at field creation time, or later.

• Is this field a postal code: Indicates this field stores Postal Code data, for use in Send Time Optimization.

• Is this field a time zone: Indicates this field stores time zone data, for use in Send Time Optimization.

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.

• Alternate Key Sequence:Indicates that this field is part of the Unique Identifier for this table. If you're using multiple fields to create the Unique Identifier, those fields must be ranked into a particular sequence. This Alternate Key Sequence value indicates the position of this particular field in that sequence. See "Define the Unique Identifier" below for more details.

• 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" below 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.  For more information on Preferences, see Permission Management - Preferences.

• Is this field the Integration ID key for EDP: Integration IDs (or "Customer Keys") are intended for use by clients who use the Engagement Data Platform, and who need to support more than one email address or mobile phone number with the same "People" (or "Member") record. The Integration ID serves as a unique identifier to track recipient actions, such as sends, open, and clicks. This check box is enabled only for String, Integer, Email, Phone type fields. Also, a table can have only one Integration ID. If you check this item, and you already had another field selected as the Integration ID, the platform will automatically de-select the previous field.

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.

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

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

To view or edit an existing field within a 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. Optionally, set field restrictions for this field (see Set Field Restrictions below for more details on this process).

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

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:

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

2. In the Display Name field, enter a name for this new Calculated 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.

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

4. From the Data Type drop-down menu, select the appropriate data type:

• String -- accepts all alphanumeric values

• Integer -- accepts round numbers

• Date / Time -- accepts valid date values

• Money / Decimal -- accepts numbers with decimals

5. Optionally, select one or both of the following processing options:

• Do Not Overwrite: Check this option if you never want to overwrite an existing value in this field with a new value.

• Insert NULL: Check this option if you want the system to assign a NULL value to this field if the logic and rules do not equate to a value. In this scenario, the system will assign the record a NULL value, even if there is a value already in this field.

6. If you selected Integer as the data type, the system presents you with two additional processing options. Optionally, select one or both of the following options:

• Lead Scoring: Check this option if the logic and business rules for this field are being used to add up a final "Score" value.

• Increment: Check this option if the logic and business rules for this field are being used to increase or decrease an existing value. Note: This option is not available if you selected "Do Not Overwrite" above.

7. Click new rule / value pair.

8. The Rule Filter field is used to define or select the Filter used for this Rule / Value Pair. To use an existing Filter, either begin typing in the Filter name, or click the browse button (magnifying glass icon) to browse for and select it. You can also create a new Filter by clicking the new button (plus-sign icon). See Filters for more information on creating a Filter.

9. In the Value field, enter the value that will be assigned to all records that match the selected Filter.

10. Click apply changes. A new row is displayed on the screen, showing the Rule (i.e., the selected Filter) and the Value.

11. Repeat steps 7 through 10 as needed to define additional Rule / Value Pairs.

12. Optionally, to define the Default value, click the edit button (pen icon) within the Default row. In the Value field enter the value that is to be assigned to records that don't fit into any of the Rule / Value Pairs assigned above. Click apply changes.

13. By default, the Calculated Field will use an IF > ELSE IF structure. Optionally, click the IF > ELSE IF button to toggle to an IF > IF structure.

14. If you need to rearrange the sequence of Rule / Value Pairs, click on the grey section to the left of the row, and drag the entire row to its new location. Please note that you can't move the Default row. If you need to remove a Rule / Value Pair, click the remove button (X icon). The Rule / Value Pair is grayed-out to indicate that it's been marked for deletion. To complete the removal, click save.

15. If you want to execute the logic for this Calculated Field on a regular schedule, click auto-populate on schedule. Define the desired schedule as follows:Select either "Start

• Schedule Immediately or Start Schedule On. If you select Start Scheduled On, enter the start date and time.

• Define the recurrence frequency:

• Daily: Enter an interval (every X days).

• Weekly: Select one or more days of the week.

• Monthly: Select either:

1. A specific number ("15th of the month" for example).

2. Define a business rule to calculate a date ("second Tuesday of every month" for example).

• Yearly: Select either:

1. A specific month / date ("January 15th" for example).

2. Define a business rule to calculate a date ("second Wednesday in August" for example).

• Select the schedule starting time:

1. Once a day: Enter a specific start time.

2. Several times a day: Define an interval in either minutes or hours. Also, define the active window during which the field can be calculated by selecting the starting and sending times for this window.

• Select how long this automated schedule should run, either Do Not End Schedule or End Schedule On. If you select End Schedule On, enter the stop date and time.

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

16. 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 Calculated 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.

Note: When you use fields with enabled restrictions in your filters, you'll only be able to select from the allowed values defined by those restrictions. If a field does not have restrictions, you'll see a text box where you can manually enter any value.

For more information on how Cheetah Digital by Zeta 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:

1. On the Table details screen, click on the main identifying field for the desired channel:

• For the SMS Text channel, you must select a data field with a Data Type of "Phone."

• For the Email channel, you must select a data field with a Data Type of "Email."

• For the LINE channel, you must select a data field with a Data Type of "LINE Contact MID."

2. The Field details screen is displayed. Click on the Sender Profiles section to expand it. This section lists all of the Sender Profiles of the corresponding channel (SMS for "Phone" fields, Email for "Email" fields, or LINE for "LINE Contact MID" fields) that have been defined for this account.

3. Place a check mark next to one or more Sender Profiles.

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.

4. In the Tool Ribbon, 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.

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

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:

1. On the Table details screen, click on the main identifying field for the Push Notification channel. The Data Type for this field must be Push Registration ID.

2. The Field details screen is displayed. Click on the Application Groups section to expand it. This section lists all of the Application Groups that have been defined for this account.

3. Place a check mark next to one or more Application Groups.

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.

4. In the Tool Ribbon, 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.

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

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.

To deactivate a field:

1. On the Table details screen, locate the desired field name within the Active Fields section.

2. Click the change button (X icon) to the right of the field name. The system moves this field from the Active Fields section down to the "Inactive Fields" section.

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

To reactivate a field:

1. On the Table details screen, locate the desired field name within the Inactive Fields section.

2. Click the change button (X icon) to the right of the field name. The system moves this field from the Inactive Fields section up to the Active Fields section.

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

Note: This feature is not available for Load and Send Tables.

To generate the Field Statistics report:

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

2. Click on the Field Statistics section to expand it. If you previously generated counts for this field, the old report will be displayed here, along with the date / time that the report was generated.

3. Click calculate stats. A Calculating Stats message is displayed within the Field Statistic section. Click the refresh button (double blue arrow icon) to refresh the screen. When the process is finished, the results are displayed within the Field Statistics section. The report lists each unique value within this field, along with the number of times that each value appears.

4. If your account is configured as a Parent / Child system, the drop-down menu within the Field Statistics section is populated with the Parent system, and all Child systems. From this menu, select the desired account. The report is refreshed to show the statistics from the selected system.

5. Optionally, to download the Field Statistic report into an Excel spreadsheet, click the Excel button.

To reorder the fields in a table:

1. On the Table details screen, locate the desired field name within the Active Fields section.

2. The system offers the following options for moving this field:

• Drag the field from its current position, and drop it into its new position.

• Using the Sequence Number field, enter a new position for this field. The system highlights this field in green to indicate that it's been changed.

3. Repeat step 2 as needed to rearrange additional fields.

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