badsender logo

Relational database or flat file ? What to choose as an email service provider?

07/06/2022

Why write this article? Because there is a tension, a misunderstanding. It's a subject that often comes up when we work with advertisers who want to change their ESP. We will therefore try to explain what differentiates an ESP called "Single-table" or a "Multi-table".

Small disclaimer, this article was originally written for a French speaking audience. The terms will be different in English. For mono-table in French, we will say "flat-file" in English. While for "multi-table" we will say "relational database" in English.

What is a table?

A table in this case, is data that is structured in rows and columns.

The rows contain information (within the framework of a customer relationship strategy) relating to an individual, the columns define a particular type of data (name, first name, email, date of birth, ... .

Column 1Column 2Column 3
boomboingpfff
bimbombam

Yeah, yeah, it's like an XLS file?

Not far off! Clearly, the XLS file is often the most well-known data table used by everyone.

NameFirst nameEmailDate of last order
IngalsCarriecarrie.i@gmail.com05/05/2022
OllesonNellienellie.with.love@hotmail.com18/10/2021
JennyWildercocotteminute320@yahoo.com01/05/2021

To return to the specific data of the CRM, we will find :

  • Contact information: email address, phone number, postal address, ...
  • Personal information: name, first name, date of birth, ...
  • Technical information: registration date, registration ip, registration source, ...

But what does mono and multi-table mean?

Mono-table

It is an email solution (router) in which all contact information is stored in a single data table.

Line = individual

Column = data type

NameFirst nameEmailDate of last orderOptinDate of birth
OllesonNellienellie.with.love@hotmail.com18/10/2021Yes02/08/1869
IngalsCarriecarrie.i@gmail.com05/05/2022Yes03/08/1870
JennyWildercocotteminute320@yahoo.com01/05/2021Yes03/07/1873

In most single table routing solutions, you can add columns as needed. But be careful, there is often a limit, and if you start to have very many columns, the system is not used properly.

Multi-table

It is a campaign management solution in which information about contacts can be found in different linked data tables (relational tables).

Tables are generally linked together by numerical identifiers. For example, a "USERID" is present for each individual in the "USERS" table. This "USERID" will make it possible to know to whom a command of the "ORDERS" table belongs.

Example of a table containing all the contacts (USERS):

USERIDNameFirst nameEmailOptinDate of birth
765OllesonNellienellie.with.love@hotmail.comYes02/08/1869
345IngalsCarriecarrie.i@gmail.comYes03/08/1870
134JennyWildercocotteminute320@yahoo.comYes03/07/1873

The date of birth is placed directly in this table because there can only be one date of birth per individual.

Example of a linked table containing all orders (ORDERS):

ORDERIDUSERIDORDER_DATEORDER_TOTALDELIVERY_STATUS
4335634505/05/2022120 €SHIPPED
4201165406/05/202212 €LOST
3165376518/10/202134 €DELIVERED
3063276504/09/2021432 €DELIVERED
2345689012/04/202154 €DELIVERED

We could also have given many other examples of tables. A shopping cart table, with all the active, validated or abandoned carts. This table could be linked to the contact table, the table containing orders, the product catalog, etc.

Actito's Datamart Studio

They all have relational databases in fact?

In reality, all campaign management solutions are built on a relational database. The categorization that is made is mainly to know if the end user has control over a single table, or over several in the tool's interface.

Even seemingly single-table solutions are based on relational databases. This is essential to store clicks, openings, ...

Therefore, one must be able to distinguish:

  • The system tables, which will be useful to the platform to operate (statistics, targeting on non-openers), but to which you will not have direct access.
  • Tables that you can set up in your campaign tool interface, into which you can inject data and which you can use in your targeting.

What are the differences in use?

From a functional point of view, there is a major difference between single-table and multi-table solutions. Moving from the former to the latter often means a significant upgrade in tools.

The fundamental difference is that in multi-table routing platforms, you will be able to target, personalize and automate on richer data.

The simplest example to understand is once again the order (this does not mean that multi-table solutions should only be considered if the notion of order is present in your business). In a single-table context, you will be limited to relatively simple data or data that will be pre-calculated in another system: date of last purchase, average order amount, ...

On the other hand, on a multi-table, you will be able to work in much greater detail. For example, average amount of orders between two dates, preferred product category for buyers who have placed at least two orders in the last six months, calculation of RFM type scores (Recency, Frequency, Amount), ...

Calculating a number of days between two events or an average is strictly impossible in a single-table routing solution.

Customization and conditional blocks

Here are some ideas for using relational data for customization that are more complicated to implement in single-table tools:

  • Conditional blocks on a product or service category
  • Automated product suggestion based on purchase history
  • Proposal to become a member of a loyalty program based on purchase history
  • ...

Targeting and segmentation

Relational data will allow you to enrich your segmentation. Whether it's by inclusion (all the people who have visited 2x such page of my website in the last 12 months and who have been customers for less than a year) or by exclusion (all the people who have had a contact with my customer service in the last 12 months).

Automation

The above examples also work for automation strategies. Whether it is to create more specific triggers or to set up different branches in your scenarios depending on the type of customer, products and services subscribed to, events (customer service, visit to the point of sale, etc.).

Scenario in Selligent Marketing Cloud

What are the differences between multi-table tools?

To simplify things, let's say that there are two different models, one that we'll call open, the other pre-packaged. Obviously, there are nuances and solutions that fall between the two models, but it's a good basis for understanding the difference.

In the model "open"You are in full control of the tables, columns, relations, ... you can virtually integrate all your data.

If it sounds great when you say it like that, beware. The more open a model is, the more complex it is to implement. You'll need to design the entire data model, make sure it's consistent, and most importantly, you'll need to configure the data flows from your other information systems to feed your marketing campaign management solution.

It is a model that is mainly intended for large companies. Here are some examples of solutions belonging to this category: Selligent, Salesforce Marketing Cloud, Adobe Campaign, HCL Unica, Actito, Emarsys, ...

On the other hand, there is the "pre-packaged". In this one, depending on the activity of your company (trade, subscription, charity, ...) a series of relational tables will be pre-configured. Most of the time you will have little freedom in customization. In some solutions you will be able to add tables, in others only attributes (new columns).

The advantage of this model is that you will have many tools ready to use: automatic scoring, scenario libraries, simplified integrations with ecommerce solutions, ...

Many "new" solutions fall into this category, such as (among several dozen others) Braze or Klaviyo.

So, do I need the multi-table?

It all depends on what you are going to do with it! If your emailing solution is only used to send campaigns, each time to the same people, without advanced segmentation and you do not want to trigger automations based on events, then a single-table solution should be enough for you.

But we must be clear. This situation is rare! Most of the time you need relational data to do good marketing. Segmenting, personalizing and automating is the basis of a good email marketing strategy. And it's going to be very complicated to do it without the right solution.

Need a strategic coaching for emailing ? We can also offer you :

Badsender also conducts training on the subject of the emailing strategy and on the choice of a routing tool !

Leave a Reply

Your email address will not be published.