Data Studio is relatively new dashboard tool from Google, launched in mid-2016. It’s a superb tool for creating professional looking reports, easily and quickly, and it connects seamlessly to other Google data sources (e.g. Analytics, AdSense, Sheets, …).
Do you work with data outside of Google’s ecosystem though?
I’ll go out on a limb here, and say, yes, most likely you do.
Perhaps you’re a digital marketing analyst looking at Facebook, Twitter, Instagram, MailChimp data (etc…) for example.
Many of us work with other web services and you want that data displayed in Data Studio. With the launch of native data Community Connectors, you can connect to your favorite web services and access data that lives outside the Google ecosystem directly!
This post shows you how to connect a Google Sheet to GitHub’s API, with Oauth and Apps Script. The goal is to retrieve data and information from GitHub and show it in your Google Sheet, for further analysis and visualization.
If you manage a development team or you’re a technical project manager, then this could be a really useful way of analyzing and visualizing your team’s or project’s coding statistics against goals, such as number of commits, languages, people involved etc. over time.
Note, this is not a post about integrating your Apps Script environment with GitHub to push/pull your code to GitHub. That’s an entirely different process, covered in detail here by Google Developer Expert Martin Hawksey.
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.