Data tables are designed to store information about subscribers. Unlike custom fields, which store mainly static data (such as subscribers' gender and date of birth), data tables are made for storing data which may change often, for example, the subscriber's website activity (purchases, visited pages, etc.).
Data tables, based on the concept of relational databases, are a very powerful feature, since they let you create complex relationships between tables in order to extract behavioral information about your subscribers. You can easily filter out all subscribers who fulfill a list of predefined criteria. For instance, by linking custom fields containing information about gender and age with a data table storing purchase data, you may find all female subscribers between 20-30 years of age who have made at least three purchases with a total amount of at least $500 within the last 3 months.
Creating a data table
To create a new data table, follow these steps:
- Select Data Tables > Tables from the left-hand navigation and click Create a new data table.
- Specify the table name and description.
- Complete the fields that will characterize the first column of the table:
- Name: describes what is stored in the column
Column Type: the data type for the column
Column types in data tables
You may select one of the following data types for a column:
Text: more than 4000 characters
String: the length depends on the value of the textbox length (less than or equal to 4000 characters)
Number: from -2 147 483 648 to 2 147 483 647
Big Number: from -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807
Double: from 1.175494351x10–38 to 3.402823466x10+38
Date: e.g. 11/26/2018
DateTime: e.g. 11/26/2018 10:56:28 AM
Boolean: True/False or 0/1
Custom subscriber ID: string (less than or equal to 256 characters)
Subscriber Email: e.g. firstname.lastname@example.org
Subscriber phone number: Primarily consists of digits, but you may also enter +, left and right parenthesis ( and ), or zeros. Regardless of the input the phone number will be saved as an integer that is less than or equal to 20 characters. Here is how sample, valid input numbers will be saved in the database:
- +48501228855 = 48501228855
- (+48)501228855 = 48501228855
- +48 (501) 228855 = 48501228855
- 0048 501 22 88 55 = 48501228855
- Length: the maximum possible length of the column fields (this parameter cannot be set for all field types).
Primary key: determines whether this column is a primary key.
Primary and foreign keys
A primary key is one or more fields (columns) in a data table, the value of which uniquely identifies each entry in the table. The primary key must always have a unique index. The primary key is used to associate a table with keys in other tables.
A foreign key is one or more fields (columns) in a table that contain a reference to a field or primary key fields in another table.
- Required: determines whether this field should be mandatory.
- Default: determines which value should be in the field by default.
- Description: talks about what the column is used for.
- Usually you will need more than one column in your data table. Using the Add column button, you can create additional table columns and set their characteristics.
- Once you add all of the columns, click Save changes.
Creating data relationships
Tables can be related to each other by a specific field or fields. If tables are not linked, the data from them can only be used in messages but not in segmentation. When tables are linked, information from them can be used in both messages and segmentation.