Tables, or relational tables, are used to store data held in structured format within a database. There are different aspects of tables which can be used for writing MYSQL queries. Tables are formed with the insertion of columns and rows.
A table has a specified number of columns but can have N number of rows. Each row is identified by one or more values appearing in a particular column subset. The column subset, which uniquely identifies a row, is called the primary key. In Maropost, tables are the very useful aspect of storing data and different queries can be written to fetch details.
If a you want to send to campaigns only to a relational table then you would need to utilize your own unsubscribe mechanism and sync the unsubscribes within a relational table. Relational tables give you flexibility with writing your own SQL queries to modify the contact data within tables, but you cannot utilize Maropost’s unsubscribe mechanism directly.
To utilize our unsubscribe mechanism within relational tables, you would need to import the same contact emails (from the relational table) into a Maropost contact list and then create this segment:
In List: List A
Table: Table A
Always send to this segment (and similar segments based on different lists & tables). By doing this, contacts can unsubscribe from List A, and segments only fetch ‘subscribers’, so the unsubscribes will not be included in campaigns.
To gain access to relational table information for your Maropost account, please follow these steps:
1. Hover your cursor over your user name, and click ‘Connections’. Then, click the ‘Relational Tables’ tab along the top.
2. You will see all of the information needed to access your relational tables from Maropost.
To create a new table, please follow these steps:
1. Hover your cursor over the Maropost logo and click on ‘Email’. On the campaign index page, hover your cursor over ‘Contacts’ and click ‘Tables’.
2. This will open the tables index page. Hover your cursor over ‘Contacts’ again, and click ‘New Table’. Enter the name for the new table.
When creating a relational table file, please ensure you follow these three rules:
(i) Column order in file must match the table field.
(ii) Column header must match the name and syntax of table fields.
(iii) Dates in the file must be specific format; yyyy-mm-dd hh:mm (all lowercase).
NOTE: you must only use lowercase letters, numbers, and underscores in the table name. If you include ANY capital letters, spaces, or special characters in the name, you will receive a SQL error. You’ll notice that if you use a capital letter in the table name, you will still be able to create the table, but will receive an error when attempting to load data into it.
My Table << is not allowed
My_Table << is not allowed
my_table << is allowed
4. The first empty space is to name the column (eg. email, ID, gender). Then select the type of data this column will hold. The data types are explained below:
- String: This is a sequence of characters, which can either be a constant or a variable. The strength for the datatype is 255. The default value can be left blank.
- Integer: This is for a whole number, which can be positive, negative, or zero. The maximum value for the data type is 11. The default value can be left blank.
- Boolean: This is also known as true or false.
- DateTime: This is for data in the date/time format. The default value can be set as null.
- Text: This is for data that is used to put the ‘Text’ entities in the column. The maximum limit for the datatype is 65535 bytes.
- Email: This datatype can only store the email address of the contact. It is the only column in Maropost which can be set as sendable for tables.
5. After selecting the datatype for the column, we can give it a default value. The default value depends upon the datatype selected. The column can also be kept blank.
6. The next step is to enter the ‘Field Length’. Set the maximum value for the column, keeping in mind that there is already a maximum value set for every datatype at the backend which cannot be exceeded.
7. We can set a behaviour for the column created in this table. The options are listed in detail below:
(i) Auto-Increment – Automated process to increase the value for the column.
(ii) Primary Key – Uniquely identifying constraints for each record in the database. It should contain unique values. The primary key cannot contain a null value. A table can only have one primary key.
(iii) Allow Null – If no value needs to be provided. Empty data can be imported if the column has been set as ‘allow null’.
(iv) Sendable – How you set the relationship between the contact in a table and the contact that is in the contact database. If you wish to send an email to an email address that is stored within a relational table, then that contact’s email address must also exist in your contact database.
In Maropost, tables can be used in campaigns, content, segments, and workflows. There are separate APIs for tables in ‘Connections’ for every specific table.
There are two options for deleting with regards to tables; ‘Delete’, which will delete the whole table, including the data, and ‘Truncate’ will delete just the data within the table, but keep all other information.
Other options for using tables:
Campaigns: Maropost has the option to send the campaign to a particular table. It can be selected from the first page of campaign creation.
To establish a relationship between multiple relational tables, please follow these steps:
1. Click on a table name on the ‘Tables’ index page. Along the right side you will see an option for ‘Relationships’.
**The ‘Relationships’ option will only appear when there is no email data-type column or no sendable column in your relational table. If your table doesn’t have either of those columns, you must manually specify the link.**
2. By clicking ‘New’, you will be shown a pop-up box. In this pop-up box, the 1st drop-down is the column from your current table, the 2nd drop-down is the other relational table you want to establish a connection with, and the 3rd drop-down is the column from the other relational table.
**Only tables with sendable columns will appear in the 2nd drop-down.**
3. You can add as many relationships as you wish between multiple relational tables. E.g. If you have 3 separate tables (contact’s info, purchase info, and product details), you can establish relationships to connect all 3 tables to be able to pull and utilize the data within.
4. By choosing which columns that will bridge the relational tables together, you now have a link between two or more tables.
Note: When creating a segment and selecting the ‘In Table’ filter, only tables with a sendable column, as well as tables LINKED to tables with sendable columns, will appear.
Segments: Active contacts can be fetched from segments using the ‘Tables’ filter. In segments, columns for the corresponding table can be selected to add the specification for the contacts.