Extract, Transform and Load (ETL) is a process in database usage and especially in data warehousing. This can be of great importance in email marketing as (recipient)data is used and or stored in multiple databases and needs to be available for sending email and preforming analytics and triggered campaigns.
The ETL process consists of:
Extracting data from the source(s)
Transforming it to fit operational needs
Loading it into the end target (ESP, database or data warehouse)
Many databases and data warehouses contain data from different source systems. Each separate system may also use a different data format. Common data source formats are relational databases and flat files, or even fetching from outside sources such as through web spidering or scraping. The goal of the extraction phase is to convert those data sources into a single format which can be used in the transformation process. The extracting of data can be done by utilizing API‘s.
In the transform stage a series of rules or functions are applied to the extracted data. Some data sources will require little or no manipulation of data. In other cases, one or more transformations like joining, sorting, selecting, aggregation and so on may be required to meet the business and technical needs of the target database.
The load phase loads the data into the end target, usually a data warehouse, but this might also be the ESP database or a CRM system. Some will overwrite existing information with cumulative information. The frequency of updating extract data can be anything from hourly, daily, weekly or monthly basis.
Other uses in Email Marketing
Another way that companies use ETL is to move information to another application permanently. This is usually the case when a new database vendor or Email Service Provider is selected. ETL can be used to transform the data into a format suitable for the new application to use.