Mailchimp Email Report Generator Part 1

Working in marketing, I send a lot of emails with Mailchimp. Working with clients who are data-driven, I produce a lot of reports. For the past several years, producing Mailchimp email reports has been the bane of my existence.

For every email sent for my client (which averages at least one a day), I have to:

1. Log in
2. Find the report in the Mailchimp UI.
3. Download the overview report CSV
4. Click through other report details and download four more CSV files (Opens, Clicks, Bounces, Unsubscribes)
5. Eliminate extraneous data (Last names, member ratings, etc.)
6. Combine the data in a new Excel sheet
7. Format all the data (Font styling, creating tables) While I’ve produced enough of these over the four years I’ve been using Mailchimp to be able to do it fairly quickly, it’s not exactly the best use of my time. So before Flatiron was even on my radar, I’ve wanted to create an application that will automate this boring, clerical task.

Application Planning: User Stories

• A user will be able to see a list of their most recent campaigns
• A user will be able to click a button to preview report data for each campaign
• A user will be able to download a formatted Excel file featuring the report data for a specific campaign

Stretch User Stories:

• A user will be able to select two or more campaigns to create an aggregate report
• A user will be able to see aggregate report data based on an email’s email list, month, category, etc.

Application Planning: Technologies and Frameworks

After several false starts, I realized that I would need both a front- and a backend, as the Mailchimp API to be able to effectively communicate with the MailChimp API.

“Mailchimp does not support client-side implementation of our API using CORS requests.” – Mailchimp API documentation

I settled on a Rails API backend and a React/Redux frontend. The backend would ping the Mailchimp server, store the response in my own database and expose it to my frontend. With React I would be able to pass the data around with a Redux store and continuously build on a simple React UI as the application advances. Plus React component naturally lend themselves to the bite-size pieces of an email report generator app: campaign lists, reports, data tables, download buttons, etc. I also planned to make use of the React-Export-Excel library to export the formatted data to an Excel file.

Application Planning: Data Models

In the name of creating a MVP that I can begin to use right away, I started with one basic model: Campaigns. Campaigns have id’s, titles, subject lines, opens, clicks, bounces, etc. As I expand the application to work for more than just my specific use-case, I plan to add User, Report and ReportCampaigns models.

Getting Started

To begin, I spun up a Rails API, initialized a Git repo and created my Campaign migration and model.

$ rails new mcreports-backend –api
$ cd mcreports-backend
$ git init
$ rails g migration CreateCampaigns title:string

I also installed the Mailchimp and DotEnv gems.

While you can technically use the Mailchimp API without the gem, just by accessing the endpoints and using parameters, Mailchimp suggests using the gem. The DotEnv gem allows you to load environmental variables from .env in development.

While incorporating a sign-in via Oauth2 is on my list (that list keeps getting longer, doesn’t it?), in the interest of giving the application some legs, I made use of environmental variables to access my specific Mailchimp data.

I created a .env file, added it to my .gitignore file, and stored my server prefix API Key (the two items I’ll need for a successful API call) in my .env file.

SERVERY_KEY: us12346
API_KEY: MAGNUSARCHIVES_123

In part two, I’ll go over how I made my first API call and spun up a React/Redux frontend to display the data I received.