In this post, we’re going to see how to setup a Google Sheets and Mailchimp integration, using Apps Script to access the Mailchimp API.
The end goal is to import campaign and list data into Google Sheets so we can analyze our Mailchimp data and create visualizations, like this one:
Mailchimp is a popular email service provider for small businesses. Google Sheets is popular with small businesses, digital marketers and other online folks. So let’s connect the two to build a Mailchimp data analysis tool in Google Sheets!
Once you have the data from Mailchimp in a Google Sheet, you can do all sorts of customized reporting, thereby saving you time in the long run.
I use Mailchimp myself to manage my own email list and send out campaigns, such as this beginner API guide (Interested?), so I was keen to create this Mailchimp integration so I can include Mailchimp KPI’s and visualizations in my business dashboards.
For this tutorial I collaborated with another data-obsessed marketer, Julian from Measure School, to create a video lesson. High quality video tutorials are hard to create but thankfully Julian is a master, so I hope you enjoy this one:
(Be sure to check out Julian’s YouTube channel for lots more data-driven marketing videos.)
UPDATE: This article was originally written in 2017. Since then, the Crunchbase API is now part of their enterprise tier. The old API, which this article is based on, is no longer accessible. As a result, the code I share below will no longer return Crunchbase data. I leave it here for reference. It may be useful if you do use the paid tier.
The Crunchbase API is easily accessible with Apps Script, meaning you can retrieve Crunchbase company data to display, or analyze further, in your Google Sheet. This article shows you how to connect to the Crunchbase API.
How to import data from the Crunchbase API into Google Sheets
Crunchbase is a business information platform; a sort of giant database of information on organizations and people in the business world, especially the digital/technology/startup world.
They have an API so you can programmatically access and retrieve business data. There’s a free tier, which is what I’ll show in this article, and a paid, pro tier, which has a much richer dataset available through the API.
On the free tier, you’re limited to data on organizations and people profiles, through their Open Data Map. It’s a RESTful API with two endpoints (/odm-organizations and /odm-people) and you need to apply for basic access first to get a user key for access.
The charts themselves are a bit of a novelty. Yes, they’re aesthetically pleasing because of that resemblance to a real-world, tapering funnel, which reinforces their message, but a plain ole’ bar chart would be equally suitable and actually easier to read data from (because the bars have a common baseline).
However, they throw up some interesting techniques in Google Sheets and for that reason, merit this long article.
We’ll build them using tricks with the chart builder tool, then with two different types of funky formula and finally, and best of all, we’ll build a tool using Apps Script, as shown in this image: