Extract, transform and load (ETL) is a process in database usage, especially in data warehousing. In email marketing this can be crucial, as (recipient) data is used and/or stored in multiple databases and must be available for sending email, performing analytics and automating triggered campaigns.
The ETL process:
How does the ETL proces look like using your email service provider?
- Extracting data from the source(s)
- Transforming it to fit operational needs
- Loading it into the end target (ESP, database or data warehouse)
Extracting data
Many databases and data warehouses contain data from different source systems, each with a potentially different data format. Common data source formats are relational databases and flat files, or outside sources acquired through web spidering or scraping. The extraction phase converts those data sources into a single format that can be used in the transformation process. This data extraction can be performed by utilizing APIs.
Transforming the data
In this 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, transformations such as joining, sorting, selecting and aggregating may be required to meet the business and technical needs of the target database.
Loading the data
In this phase, the data is loaded into the end target – a data warehouse, ESP database or CRM. In some cases, cumulative information overwrites existing information. The frequency of updating extract data can range from monthly to weekly, daily or even hourly.
Other uses in email marketing
Another way companies use ETL is to permanently transfer information from one application to another, such as when a new database vendor or email service provider is selected. ETL can transform the data into a usable format for the new application.
