Matillion uses the Extract-Load-Transform (ELT) approach to delivering quick results for a wide range of data processing purposes: everything from customer behaviour analytics, financial analysis, and even reducing the cost of synthesising DNA.
The Mailchimp Query component in Matillion ETL for Amazon Redshift presents an easy-to-use graphical interface, enabling you to pull data from Mailchimp and load it directly into Amazon Redshift. Many of our customers are using this to combine marketing email data with other marketing and sales data to measure campaign effectiveness.
The connector is completely self-contained: no additional software installation is required. It’s within the scope of an ordinary Matillion license, so there is no additional cost for using the features.
Watch our tutorial video for a demonstration on how to set up and use the Mailchimp Component in Matillion ETL for Amazon Redshift.
This component runs a SQL Query on Mailchimp and copies the results to a table, via S3 staging buckets.This component is for data-staging – getting data into a table in order to perform further processing and transformations on it in Amazon Redshift. You should consider the target table temporary, as it will either truncate or recreate each time the components runs.
Above is a typical workflow for importing data from a Mailchimp server into Amazon Redshift.
- Mailchimp connects and executes SQL against the Mailchimp.
- The component reads the data from Mailchimp and streams it to a S3 Staging bucket. (Data is never written to disk).
- Matillion copies the data from S3 Stage buckets to Amazon Redshift using “Copy” command.
Before you can use the Mailchimp component there are a couple of prerequisites:
- The Mailchimp OAuth or API key has been setup and is accessible from the Matillion instance. You can read more about setting up Mailchimp OAuth in Matillion ETL here.
- You have provisioned your Amazon Redshift data warehouse and it is running on AWS platform.
Once these are met, your Matillion instance can reach the service via a Mailchimp endpoint URL available in your AWS console.
Using the Mailchimp Query Component in Matillion ETL for Amazon Redshift
You can find the Mailchimp Query Component under the “Load/Unload” folder in the Components panel. The following image shows the properties for a configured component:
- The options ‘API Key’, ‘Data Source’ and ‘Data Selection’ cover the details required to establish the connection to your Mailchimp Server.
- You will also need to specify a ‘S3 Staging Area’ which is the S3 bucket that will hold the results prior to loading into Amazon Redshift.
- For the ‘Schema’, you can identify the staging table on Amazon Redshift you want to load the data into. Each time you run the component, the table will drop and rebuild. You should therefore treat this table as a Staging table and subsequently move the data into a Persistent table as soon as possible.
The Mailchimp Query component retrieves data on a Mailchimp and loads it into an Amazon Redshift table. You will use a S3 bucket to stage the data. So each time you run the component, the table will truncate and reload. You may then use transformation job to enrich and manage the data in permanent tables.
Warning: This component is destructive as it truncates or recreates its target table on each run. Do not modify the target table structure manually.
The component has additional JDBC parameters supported by the Database Driver. The driver automatically determines the available parameters, and may change from version to version. None of the Connection Options are mandatory with the Mailchimp driver, as Matillion ETL usually provides sensible defaults.
Finally, you can use Advanced Mode to issue SQL Query against the MailChimp. You can therefore replace any variable definitions with the appropriate values and then issue the query against your Mailchimp Server.
The following image shows a Orchestration job that pulls data from a Mailchimp Server into a target table in Amazon Redshift and then copies the data to persistent table using a Transformation job.
In conclusion, we hope that gave you a good overview of the Mailchimp Query component in Matillion ETL for Amazon Redshift. Give it a go and let us know (email@example.com) if you need any support with this component.
[callout-content title=”Want to try the Mailchimp Query component in Matillion ETL for Amazon Redshift? Arrange a free demo, or start a free 14-day trial.”]
[button title=”Request a free demo” link=”https://info.matillion.com/matillion-etl-for-amazon-redshift-demo”]
[button title=”Get 14-day Free Trial” link=”https://matillion.com/etl-for-redshift/free-trial/” style=”ghost”]