How to load data from Mandrill to Redshift (tutorial)

How to load data from SendGrid to Redshift

There’s a continually growing usage of services like Mandrill. Mandrill  is a transactional email API for Mailchimp Users and it is ideal for sending data-driven emails, including targeted and personalized one-to-one messages to your customers. But that traffic creates more data. How can you analyse the data you generate with Mandrill as part of your transactional email campaigns, on Amazon Redshift? This guide is going to provide you with a clear picture about how to load data from Mandrill to Redshift

Alternatively, in order to load your data from Mandrill to Redshift you can check Blendo that can handle this kind of problems automatically for you.

ETL your Mandrill data into your Data warehouse

We will use Mandrill’s API to access and extract e-mail related data and load it into Redshift for further analysis. You will need to write the code to get the data and make sure that this process will run every time new data are generated.

What is Mandrill?

Mandrill is a transactional email API for Mailchimp Users. Although in the past, Mandrill was perceived as a different product than Mailchimp, right now it is offered as a Mailchimp plugin. Mandrill is reliable, powerful and ideal for sending data-driven emails, including targeted and personalised one-to-one messages to your customers. You might wonder what are the differences between Mandrill and Mailchimp, as both of them handle the delivery of emails to your customers. Some key differences between the two are the following:

Continue reading

Load data from Mailchimp to SQL Data Warehouse

Load data from Mailchimp to SQL Data Warehouse

This post will help you define a process or pipeline, for getting your data from MailChimp and load it into SQL Data Warehouse for further analysis. Alternatively you can check Blendo that can handle this kind of problems automatically for you in a few clicks.

Load data from Mailchimp to SQL Data Warehouse

Information will be given on how to access and extract your data from MailChimp through its API and how to load it into SQL Data Warehouse. This process requires from you to write the code to get the data and make sure that this process will run every time new data are generated.

About Microsoft Azure SQL Data Warehouse

Load data to Azure SQL Data Warehouse

Load data to Azure SQL Data Warehouse

SQL Data Warehouse is the data warehousing solution that you can use if you are a user of Microsoft Azure. It’s an elastic data warehousing as a service solution, emphasising it’s enterprise focus. It also speaks SQL like the previous two solutions and it supports querying both relational and non-relational data.  It offers a number of enterprise-class features like support for hybrid cloud installations and strong security. It’s probably the less mature solution compared to the two others though, it’s still in “Preview” mode although accessible to existing Azure subscribers.

About MailChimp

Load data from mailchimp

Load data from mailchimp

MailChimp helps businesses observe their subscribers’ activities, send automated emails to them based on their behavior and preferences, optimize and target the appropriate audience for each campaign using specific tools, and monitor sales and website activity with revenue reports. Companies can add content and collaborate on campaigns that fit their brand using MailChimp’s Email Designer; edit campaigns, collaborate with their teams using MailChimp Editor; send one-to-one messages using Mandrill; collect signups from their tablet using Chimpadeedoo; and access all the services via mobiles using MailChimp Mobile.

Utilizing an open API, MailChimp encourages other systems and web applications to integrate with its email marketing engine.

Extract your data from MailChimp

First of all, Mailchimp updated its API to v3 recently, so make sure that anything you do will be with this version as the previous are all deprecated although still supported, for more information visit MailChimp API v3.0 documentation. MailChimp was always a promoter of APIs and encouraged integration with other systems. It has a rich API that exposes a large number of endpoints for interacting with the resources of the applications, more specifically there are endpoints for the following resources:

Continue reading

Load data from MailChimp to Google BigQuery

Load data from Mailchimp to Google BigQuery

The purpose of this guide is to help you define a pipeline, to load data from MailChimp to BigQuery for further analysis. Alternatively, you can use an ETL as a service product like Blendo that can handle this kind of problems automatically for you.

Information will be given on how to access and extract your data from MailChimp through its API and how to load it into BigQuery, this process requires from you to write the code to get the data and make sure that this process will run every time new data are generated.

What we will see:

  • An Intro to Google BigQuery and MailChimp.
  • Extract your data from MailChimp and MailChimp API (the hard way)
  • Prepare your MailChimp Data for Google BigQuery
  • Load Data from MailChimp to Google BigQuery
  • The best way to load data from MailChimp to Google BigQuery (the easy way)

About Google BigQuery

Load data from MailChimp to Google BigQuery

Load data from MailChimp to Google BigQuery

BigQuery is the data warehousing solution of Google. It’s part of the Google Cloud Platform and it also speaks SQL like Redshift does. Queries are executed against append-only tables using the processing power of Google’s infrastructure. It is also fully managed and is offered as a service over the cloud. You can interact with it through its web UI, using a command line tool while a variety of client libraries exist so you can interact with it through your application.

About MailChimp

Load data from MailChimp to Google BigQuery

Load data from MailChimp to Google BigQuery

MailChimp helps businesses observe their subscribers’ activities, send automated emails to them based on their behavior and preferences, optimize and target the appropriate audience for each campaign using specific tools, and monitor sales and website activity with revenue reports. Companies can add content and collaborate on campaigns that fit their brand using MailChimp’s Email Designer; edit campaigns, collaborate with their teams using MailChimp Editor; send one-to-one messages using Mandrill; collect signups from their tablet using Chimpadeedoo; and access all the services via mobiles using MailChimp Mobile.

Utilizing an open API, MailChimp encourages other systems and web applications to integrate with its email marketing engine.

Extract your data from MailChimp

First of all, Mailchimp updated its API to v3 recently, so make sure that anything you do will be with this version as the previous are all deprecated although still supported, for more information visit MailChimp API v3.0 documentation. MailChimp was always a promoter of APIs and encouraged integration with other systems. It has a rich API that exposes a large number of endpoints for interacting with the resources of the applications, more specifically there are endpoints for the following resources:

Continue reading

Load data from MailChimp to Redshift

Load data from Mailchimp to Redshift

The purpose of this guide is to help you define a pipeline, to load your data from MailChimp to Amazon Redshift for further analysis. Alternatively, you can use an ETL as a service product like Blendo that can handle this kind of problems automatically for you.

Information will be given on how to access and extract your data from MailChimp through its API and how to load it into Redshift, this process requires from you to write the code to get the data and make sure that this process will run every time new data are generated.
What we will see:

About MailChimp

Load data from MailChimp to Redshift

Load data from MailChimp to Redshift

MailChimp helps businesses observe their subscribers’ activities, send automated emails to them based on their behavior and preferences, optimize and target the appropriate audience for each campaign using specific tools, and monitor sales and website activity with revenue reports. Companies can add content and collaborate on campaigns that fit their brand using MailChimp’s Email Designer; edit campaigns, collaborate with their teams using MailChimp Editor; send one-to-one messages using Mandrill; collect signups from their tablet using Chimpadeedoo; and access all the services via mobiles using MailChimp Mobile.

Utilizing an open API, MailChimp encourages other systems and web applications to integrate with its email marketing engine.

About Amazon Redshift

Load data from MailChimp to Redshift

Load data from MailChimp to Redshift

Amazon Redshift is one of the most popular data warehousing solutions which is part of the Amazon Web Services (AWS) ecosystem. It is a petabyte scale, fully managed data warehouse as a service solution that runs on the cloud. It is SQL based and you can communicate with it as you would do with PostgreSQL, actually you can use the same driver although it would be better to use the drivers recommended by Amazon. You can connect either through JDBC or ODBC connections.

 

Extract your data from MailChimp

First of all, Mailchimp updated its API to v3 recently, so make sure that anything you do will be with this version as the previous are all deprecated although still supported, for more information visit MailChimp API v3.0 documentation. MailChimp was always a promoter of APIs and encouraged integration with other systems. It has a rich API that exposes a large number of endpoints for interacting with the resources of the applications, more specifically there are endpoints for the following resources:

Continue reading