Acoustic Campaign databases contain two types of fields, system and custom fields (segmenting fields are a type of custom field).

• System fields are used by Campaign. They appear first in the Database Fields table.
• Custom fields are fields that you create based on your organization's needs. For example, you can create a field to store each contact's city or state. You can also create a numeric or date field to store information about a contact's age or birthday. You can add custom fields to a database at any time.

After you create a field (column name), you can modify only limited information, such as renaming the field and changing the default value and drop-down values for the selection fields. You can change the required setting if the data type permits changes. Note that you cannot change the selection databases for the country.

System fields

Acoustic Campaign uses and maintains the system fields, which are required and display automatically when you create a database.

System fields include the following:

• EMAIL TYPE - The default is Multipart MIME (HTML and text). The valid values for types are HTML and Text. You can edit this field to set a different global default preference. The contact can also edit the preference using a web form. If you update the type through a CSV file, the numeric representations are HTML = 0 and Text = 1. Campaign looks at the email type field of your contact record when it sends the email. If you create both HTML and text bodies and the database has HTML as the email type, the system sends a Multi-part (MIME) email with both bodies, and the contact's email client determines which body to display.  
• OPTED_OUT DATE - The date the contact opts out of the email

  When you export a list from Campaign, it contains a field for opt-out. This field has values of F (False), T (True), and U (Undeliverables).

  • F - The F value indicates false. The user is not opted out and will receive the email.
  • T - The T value indicates true. The user is opted out and will not receive the email.
  • U - The U value indicates undeliverables. The user has been marked as undeliverable and may or may not be mailed to depending on your undeliverable settings.
• LAST_MODIFIED - Stores the date of the database's most recent changes.
• OPTED_IN - The date the contact opts-in to the email.
• OPT_IN_DETAILS - The date and time the contact was added to a database.
• CREATED_FROM - Stores parent database information.
• EMAIL - Stores contact's email address.

Custom fields

You can create custom fields when creating or updating a database. Creating custom fields is the same as creating or adding fields to a database.

You can create custom fields in any combination of types, though it is recommended that you create a maximum of 255 fields (columns of fields). For example, there are 100 text fields, 50 yes/no fields, 50 numeric ones, and 55 date fields. If necessary, use the scroll bars to view the complete field database.

You can choose to require these fields (contacts must enter data in the field) or leave the fields as optional. You can also choose a default value for each field.

• The field data type is important. Queries and rules depend on the ability to search and compare information accurately.
• Numbers may not work well for search, compare, and math operations when entered as text.
• You cannot enter numbers that include text (alphanumeric, such as J10357a) as numerical data.
• Date, time, numeric, and segmenting fields require their specific field types for best operation.
• Fields with date type and system fields with date values will convert from (or to) your preferred date format upon import (or export) if you utilize the date formatting option when performing one or the other.
• Send time optimization (STO) will optimize send times based on contact behavior (the most common time the contact views the messages you send).
• Relational tables require stricter use of syntax and data types.
• Field names must be at least three characters.

Field types

Here's a list of field types and the type of information you can enter in those fields.

Data in your database must be valid to be read correctly. You can view the valid data types and ensure that your databases contain valid data.

List of field types

Data type	Details
Date	Campaign is expecting dates to be in the MM/DD/YYYY format. It accepts dates formatted as DD/MM/YYYY, but at this time, do not have a way to handle this format. It is up to the user to keep the date formats consistent with Campaign to be handled correctly. Blank values are also valid dates. Examples of invalid dates include: Two-digit years (MM/DD/YY), Preceding the month with the day (DD/MM/YYYY) English months or abbreviations in the date (01-SEP-2020) Any other delimiter than slashes (such as MM-DD-YYYY) Dates with time preceding or following (09/01/2020 12:00:00) Single-digit months or days Do not use the European date format (listing the day of the month first). Note that you can use the date format of your choice outside of the UI and then, upon import, you can choose to have Campaign convert the dates to the preferred format.
Text	This field only allows text, including alphanumeric characters and numbers treated as text, such as addresses. The backslash character is not supported unless escaped with another backslash. If you want to import this value in a text field: back\slash, you should import the value: back\\slash. Note: If you set a default value for a text field, Campaign only allows up to 255 characters. Otherwise, a text field will accept up to 4000 characters.
Numeric	This field, which is important for accurate results in queries and rules, allows only numeric values. Do not enter letters, spaces, non-numeric characters, or math symbols. You can include a single decimal (such as 127.4) but no more than one. You can also use long numbers. Valid numbers are: Positive and negative integers (22, -43, ...) Positive and negative decimals (11.4, -3.2, ...) Blank Invalid numbers are: Additional symbols, such as "$" or "%" or "+" or a comma Fractions, such as "1/4" Scientific notation, such as "1E4" Commas, such as "1,000" Leading or trailing spaces, such as "23.4" Character limitations: If you add or edit a contact directly through the UI , the maximum is 255 numbers. If you import a file, the maximum is 4000 numbers.
Selection	Enter a drop-down selection and click Add. You can change the order of the values in the database by selecting the value and clicking the Move Up or Move Down buttons. Click Remove to delete a value from the database. Valid data for the selection field type must match exactly. If the wrong capitalization is used, the values do not match. Maximum of 100 characters for the value.
Yes/No	This field allows only Yes or No values. After this data type is set, it becomes a required field. Valid data for the Yes/No field type must match "Yes" or "No" exactly. If the wrong capitalization is used, the values do not match.
Country	You must choose one country as your "default" from the drop-down list you (cannot add more than one). Valid data for the country field type must match exactly. If the wrong capitalization is used, the values do not match.
Time	Accepts 12h or 24hr format with seconds (hh:mm:ss). You must enter time in military time (24-hour clock format). For example, enter 13:30 for 1:30 P.M. To calculate time values past 12:00 PM, simply add the new hour to 12. Example: 1:00 PM equates to 12 plus 1 equals 13:00. Time fields must be between '00:00:00" and '23:59:59". In addition, '2:00' and '02:00' are valid formats. Note: Time may be relative to the Campaign server or the user, depending on the setting, such as send hour described below.
Email	Campaign currently accepts many email formats. The list below gives a general description of what email addresses work correctly. See Valid characters in email addresses for a comprehensive list. name@domain The 'at' symbol ('@') must be present and cannot be the first or last character. The length of the name must be less than 64 characters. The length of the domain must be less than 64 characters. All email addresses are forced to lowercase when the email is sent; therefore, any email addresses requiring uppercase is not delivered correctly by the ISP because Campaign changes the address to lowercase.
Send Hour	This user preference field is set through a drop-down list containing a number (0-168), each representing the weekday and hours of the day. Note that the time selected is based on GMT. See also send hour selection, automated behavior updates, and time formats. A zero in the send hour field indicates that the contact will receive the email immediately. This value can be present of if the contact was newly added to the list or if the contact has not yet taken an action on an email that allows Campaign to build behavioral patterns for the contact. Send time optimization will impact the time it takes to prepare the email for sending. For very large emails, this impact may be significant.
Segmenting	Allows only integers.
Phone number	The phone number field type in Campaign does not require numeric character values. Alphabet and punctuation are also allowed.
SMS opt-in	Follows the same rules as Yes/No above.
SMS opted-out date	Uses the same rules as Date above.
SMS phone number	Uses the same rules as Numeric above.

Other valid data

Name	Can contain: o A-Z o a-z o 0-9 o ! # $ % & * + - . = ? ~ | ` _ ^ The entire name can be surrounded by double quotes (though this is not supported by many ISPs). In this case, the following additional characters are allowed between the quotes: ( ) { } < > @ , ; : [ ] (space)
Domain	Can contain: Cannot contain two or more consecutive periods Must contain at least one period Domain - cannot begin or end with a period or dash The domain portion cannot be an IP address
Timestamp	Allows date and time in format mm/dd/yyyy hh:mm:ss
Sync ID	String field. If your business case requires it, you can use the Sync ID to associate your relational tables with your CRM relational tables.
Opt-in data and Opt-in Details	Campaign provides two fields in the contact database for storing this information: The first is the Opt-in Date which defaults to the date and time when the contact was added to Campaign via a web form, a bulk load, or a manual add. The second field is the Opt-in Details which by default contains the IP address of the opt-in (assuming this is enabled for the database). You often already have the source and opt-in date of when a contact joined your database in an external system. Both the AddRecipient and Bulk Import APIs support setting these fields and changing them for existing contacts. If you did not set these fields when you imported your database, you can do a UpdateOnly Bulk Import and include the contact identifier and these fields to set the information in Campaign. You can also do an import update against the database with a standard CSV file. If you choose this method, ensure your Opt-In Date field, in the CSV file, is formatted correctly. You also need to give this field a timestamp. On import, Campaign displays the time minus GMT (based on which user is viewing the data and what timezone that user has specified in their account settings). Example: You are on EST time, and you select 12pm. Depending on daylight savings time, that time is changed to either 7 AM or 8 AM. Again, using EST as an example, if you choose a date of 3/25/2021 at 3 AM, Campaign imports that date, which is displayed as 3/24/21 at 11 PM. This causes issues when you are trying to query for opt-ins on 3/25/2021. So be sure to take into consideration GMT minus your timezone. A contact's opt-in date might not be available or might not be mapped properly when the database was loaded. You often need to know when a contact joined your database so that you can send messages for anniversaries, reminders of when/where the contact opted in, and so on. There can also be scenarios where you need to change the opt-in date.

Field names can only be a maximum of 255 characters.

Read more about database characters.

Note: Campaign enforces field type via JSON API (the whole API call fails), adding a contact one at a time, or via Mobile SDK. Campaign doesn’t enforce field type via XML API, manual imports, or recurring imports.

Segmenting fields

The segmenting field is a special custom field Campaign used to create subsets of a database for testing or split sends. A database can contain up to 12 segmenting fields. Reasons for segmenting a database include testing offers, subject lines, or the creative elements of an email.

When you choose to segment a database, Campaign uses your parameters to assign a numeric value to this database field. For example, if you choose to segment a database into four equal parts, Campaign randomly assigns a number (1-4) to that segmenting field for each contact in the database. Different emails can be sent to each segment without actually changing the database. After an email, you can review reports to determine if one database segment was more successful than another.

Note: Campaign stores segmenting fields as numeric values, and you can import only numeric values into them. In the Field Type drop-down list, choose the segmenting field type.

Add fields to a database

You can add up to 400 fields (named columns) to a database in any combination of types. However, it is recommended that you not exceed 255 fields to avoid performance issues. There is no way to load the selection values in bulk for fields where the user chooses values. You must enter them individually.

After you add a field, you can edit the field at any time to set the value. However, you cannot change the field type and related settings.

1. Go to the Fields tab of Database Summary.
2. Click Add Database Field.
3. Enter a field name.

   Note: When merging two databases, the field names must match. For example, if database A has a field named 'Date of Birth' and database B has a field named 'DOB', these fields will not merge in the new database.

4. Select the field type.

   Note: The field data type is very important. Queries and rules depend on the ability to search and compare information accurately.

   • Numbers may not work well for search, compare, and math operations when entered as text. But numbers that include text, such as part numbers (J10357a), can't be entered as numerical data.
   • Date, time, and segmenting fields require specific data types for best operation.
   • Send time optimization will impact the time it takes to prepare the email for sending. After creating the database, you can add additional fields and add information to the fields.
5. Click Save. The table of database fields displays the new field in the database.
6. Click Edit Field to set the default value for the field.

After adding a new field to your database, the field should be available for selection in queries and dynamic content rules. However, it may not be immediately visible in the list because Campaign sorts database fields based on the case of the first letter of the field.

If the field you added begins with a lowercase letter, you must scroll below all of the fields beginning with upper case letters before you will find your new field. The sort order for fields in queries and dynamic content rules will sort and display similar to the example below.

Example:

• Address
• Email
• FirstName
• LastName
• Phone
• ZipCode
• address2
• dateofbirth
• state

Edit a field

Edit the field to set the default field values or drop-down selections.

Deleting a value from a selection field in a Campaign database does not also delete that value from contacts' records. After deleting a selection field value, you can go to the search tab of the database. You will see that all of the contacts with that value will continue to show that value there. However, if you click on the contact, you will not see the value in that field. This is because the value is not available for selection anymore.

You can run queries on this field value to see that it is still there for those contacts. Also, you can export the database, showing that the value still exists for those contacts.

1. In the Database Fields tab within Database Summary.
2. Click Edit next to the field name.
3. Enter a new default value, if needed, and click Save. All fields in this column will now have the default value.
   • You can change 'Required', unless it is a Yes/No selection. You can set the default value of a Yes/No field to 'Yes' or 'No'.
   • Fields with selections can have selections added, and their order rearranged (except Country).
   • Time, date, and numeric values must be in valid formats.
     • Time: 1:00. Do not use AM or PM. Use 24-hour time.
     • Date: mm/dd/year. February 12, 2021, must be entered as 02/12/2021. Do not use European notation. You may use the date formatting option upon import/export of your database to work with your files in your preferred date format outside the UI and then have Campaign convert them for you.
     • Numerical: No letters or spaces. A single decimal can be used, but not two decimals. Long numbers can also be used.
     • Text: Text entries can include any alpha-numeric character, including numbers.

Rename a field

Fields that are no longer in use, or can be more effectively used or merged using a different name, can be renamed. The information in them will be preserved or emptied.

Warnings

• If you rename a field, it can disrupt the personalization and reporting of active emails.
• The field will be renamed in programs where the field is used as the event trigger, queries (both select and were portion of the query), and active reports.
• Email personalizations using this database field will no longer be personalized correctly.
• Associated web forms containing this database field will not function properly.
• When merging two databases, the field names must match. Keep this in mind when naming fields. For example, if Database A has a field named 'Date of Birth' and Database B has a field named 'DOB', these fields will not merge in the new database.
• Review and determine if recurring imports may be impacted by the renaming. If so, you may need to cancel and reschedule the recurring import.

If you rename a database field used for universal behavior matching, universal behaviors continue to evaluate that database field for matching.

1. Go to Data, open the database, and go to the Database Fields tab.
2. Click the Rename link next to the field name. A form appears in which to enter the new name and choose whether to retain the field's contents or empty the contents.
3. Click OK. The field appears in the database with the new name.

Delete a field

You can delete a database field after the database has been saved. You can also rename database fields and their contents and, if needed, you can empty the fields.

Run the Field Usage report before making any modifications to the field.

Field usage report

1. In Data > Databases.
2. Click the name of your database, then, in the Fields tab, click Edit next to the field you would like to change.
3. On the following screen, click Run Usage, then Submit.
4. Click Data Job ID, then Download.
5. Evaluate the results. Referenced items may need to be deleted from the Shared/Private directory and deleted tab directory to remove the database field. However, if the field is in use, you may not want to delete the field.

Note: Any changes made to the list name could affect existing personalization in emails. Review all emails attached to the modified database and change any personalization associated with the renamed field.

The deleted field is shown in the recurring import

If a recurring import takes place and the field deleted in the database is present in the recurring import, that field is added back into the database.

Delete the field

1. Go to View Data.
2. Click the name of your database, then, under the Fields tab, check the box next to the field you would like to modify.
3. To delete a field, do the following.
   1. Click the Delete Field button, then click OK.
   2. Click Submit to delete your field.

The field usage report shows the field that you want to delete is used in different locations, such as queries, email, etc.

If you try to delete a database field and the field usage report shows that the database field is in use in several locations, but when you review those locations, you find that the field is actually not in use.

An issue may occur when a database field name matches exactly the name of a relational table field name. When the field usage report runs, it does not properly exclude the relational table fields, so if you have "FirstName" on the database and relational table, there is a chance that the usage report will flag that incorrectly as "in use". </p

To empty the contents of a field, check the box next to Empty the field's contents.

Do you still see the field in the database?

If you delete a field from your Campaign database, but you still see the field, this is because the field is referenced in a recurring Import field mapping file created before the field was deleted. Even after the field is deleted, the field is recreated the next time the import job runs.

To resolve this issue, cancel all recurring imports created since the field was created. Delete the field, then recreate the recurring imports so that the deleted field is no longer referenced.

Delete or empty database fields

You can delete a database field after the database has been saved. You can also rename database fields and their contents and, if needed, you can empty the fields.

Run the Field Usage report before making any modifications to the field.

1. In Data, go to View Data or Manage.
2. Click the name of your database, then, in the Fields tab, click Edit next to the field you would like to change.
3. On the following screen(s), click Run Usage, then Submit.
4. Click Data Job ID, then Download.
5. Evaluate the results. Referenced items may need to be deleted from the shared/private directory and deleted tab directory to remove the database field. However, if the field is in use, you may not want to delete the field.

Note: Any changes made to the list name could affect existing personalization in emails. Review all emails attached to the modified database and change any personalization associated with the renamed field.

The deleted field is shown in the recurring import

If a recurring import takes place and the field deleted in the database is present in the recurring import, that field is added back into the database.

1. Go to View Data.
2. Click the name of your database, then, under the Fields tab, check the box next to the field you would like to modify.
3. To delete a field, do the following.
   1. Click the Delete Field button, then click OK.
   2. Click Submit to delete your field.

The field usage report shows the field that you want to delete is used in different locations, such as queries, email, etc.