How to pull Mailchimp Data into Google Sheets with Google Apps Script

How to pull Mailchimp Data into Google Sheets with Google Apps Script


– How can you get your
Mail Chimp campaign data into a Google Sheet? Well, in this video
we’re going to show you how to build an app script that connects to the Mail Chimp API and
pulls the campaign information into Google Sheets so you
can analyze and visualize it. All the more, coming up right after this. (upbeat music) Hi there and welcome to another
video of Measure School.com where we teach you the data
driven way of digital marketing. My name is Julian and on
this channel we do marketing tech reviews, how to
videos, and tutorials, just like this one. So if you haven’t yet,
consider subscribing. Now pulling all your data
together so you can analyze it and visualize it in one tool
is sometimes really hard to do. Now as you might now, I’m
a big fan of Google Sheets because it lives in the
cloud and we can connect to different tools to pull
the data in regularly. But unfortunately there’s not
always a direct connection between the tools that you
want to get the data from and pull them into your spreadsheet
so you can analyze them. And this is where Appscript comes in. Appscript is like VBA for
Excel where you can write a little bit of Javascript
and pull the data in from various sources. So lately I’ve gotten a lot of questions how you can pull data from Mail Chimp into Google Spreadsheets. So I asked my friend Ben Collins
who’s really knowledgeable in Appscript, Google Sheets,
but also data analysis, to come up with a solution here for us. Now you can check out his
stuff at his YouTube Channel, Ben Collins, but also at
his blog Ben Collins.com and today Ben is going to
show us how we can build a little bit of an Appscript to pull data from the Mail Chimp API and
import it to Google Sheets so we can analyze our campaigns there. We’ve got lots to cover,
so Ben take it away. – [Ben] Hi everyone. This is Ben from benlcollins.com. And today I’m going to show you
how to bring your Mail Chimp data into a Google Sheets using their API. So open up a blank sheets and
then rename it to Mail Chimp for this project. What I’m going to do is set
up the headings in the Sheets, then we’ll open up the script
editor and start writing the code and then we’ll
jump into Mail Chimp to get the API key that
I need and then come back to the Sheets to finish it all off. So that’s the strategy. In the interest of time,
I’m just going to copy in these headings from another
sheet that I’ve been working on. And I’ll just show you
what they look like. I’m going to wrap the text
there and center them all. So these are the details,
the data that I want to get from the API. For example, when the campaign was sent, what the title was, the subject
line, how many recipients, the clicks, the open
rate, that sort of stuff, and then finally, actually
we can calculate these two. These are going to be calculations
that we’re going to use then to create a chart
showing the performance of all the campaigns over time. That’s the idea with this analysis. So let’s go and open up the Script Editor. So I go to Tools, to Script
editor, that, that opens up. You can immediately just clear
out the code that’s there. We don’t need that stuff. And again, let’s just rename
this project Mail Chimp. And then I’m going to
start by just creating the necessary API key and
list ID that I need to then run all my code so let’s just
for the moment put these in and then we’re going to
actually retrieve them from Mail Chimp. So I need an API key and
then a list ID as well. We’re going to go into Mail
Chimp now and get those two. So open up a new tab, open
up Mail Chimp, log in. And then Choose a profile,
and under extras here is this tab called, “API Keys.” And then you come down here
to create a key, click that, it’s going to create a new key for you and it will be this new
one at the top here. So let’s just click that
one and rename it to be “mailchimpsheets” or some other name and then its ready use. What we’re going to do
is copy that key there and insert that in between
these two little hyphens here which gives me my API key as
a text string ready to use in my application here. Now it’s really important
that you don’t actually give this out to anyone
which is why it’s blurred on my screen right now, you
won’t be able to see that one. Because anyone that has that API key can write a little bit
of code then to actually infiltrate your email list
and delete subscribers or add subscribers or
send out spam campaigns. So it’s really important
that only you have access and only you see this API key,
so don’t share that around. It’s perfectly okay to
have it here in your code but don’t save this code,
for example, onto the web without removing your API key first. Now the other thing we
need is this List ID so let’s go back to Mail Chimp. Let’s go to the menu here to Lists. Navigate to your list and
then click on your list here, open the list up and under
settings, you’ll see this one List name and defaults, just click that. And then here under List ID,
you’ll see the List ID here. This one here. So, we’re going to need
that number as well but that’s just very easy to
get under Lists, Settings. And I’m just going to add that in there and that’s me ready to go now
with the two pieces of code I need to make this function
run, or this application run. So what I’m going to do now
is actually create a function. We’re going to create
one function initially that just retrieves some data from the API and I’m going to take a look
at it and see what I get back and then from there we can
start to actually build out the analysis that we want to do. What I’m going to do is just actually move to the compact controls here
so we get a bit more space. And I’m just going to
copy in some code there so you’ll see just under the List ID, I’ve copied in this code, just
some comments to start with. Then a function called mailchimpCampaign where I specify the root of
the API and then the end points of the API that I want to access. And then this is the
really important one here, the parameters I need. The important one is this API key here. You can see this. What this does is when we
request the data from Mail Chimp, we pass it the API key we
specified up here at the top and that’s what allows Mail
Chimp, that’s what lets Mail Chimp know that we’re
a legitimate application and we’re okay to receive
some data from them. Let’s just put this in. So what this is going
to do is call the API, and then just pass the
data that’s returned and then we’ll be able to
display the output here in this Logger function. So let’s just close that function off. Hit save, and then what
we’re going to do now is run this function
called mailchimpCampaign and it’s going to ask
us for permission first because it hasn’t done
this before so it needs to access our data. Let’s see review, and it wants to connect to an external service, that’s
fine, we’ll click on Allow. We come up to View the Logs,
we should see some stuff in there so let’s click that. And there we go, we can
actually now see this is what the Mail Chimp API is returned to us. It’s obviously a lot of data,
it’s kind of difficult to read in this format but you can
see if we look in a little bit of detail there’s things like,
there for example, send_time. As a next step, let’s just
go ahead and get a little bit of the campaign data that we actually want and just, again, we’ll just log it. I’m just going to say find
out how many campaigns I have. And then, also, I’m going to
extract all the subject lines from those campaigns and display them. So let’s run it. Let’s view logs again. And there we go. This time 27 campaigns. And you can see the subject
lines of the different campaigns I’ve run so it looks like that’s great. It’s working now. It’s now pulling out specific
pieces of data that we want and that’s exactly the sort
of data we’re going to now put into the Google Sheet here. So what I want to do is
just put the word tripe here and all of this stuff here,
I’m just going to tab in which will just move, indents it. And then we’re going to
do a catch and this catch is the real way we should
be making sure that we catch any errors, we try this and
if we fail at some point with this fetching the API
will get an error message and we’ll log the error
message to make sure we know what’s going on there. So instead of all the
stuff we were doing here, I’m going to clear that
bit out and just paste in. It looks like a lot of code, don’t worry, this code is all available with some links underneath this video, you can
click and just copy this in. And the final code is available as well, so you can just see how
it all fits together. The ordering here is I’m
just creating a blank array with nothing in it and then
we’re going to loop through our data that we got back
from the URL fetch up. So when we called the API
we’re going to loop through all that campaign data and
just pull out these different pieces of it that we want, send
time, title, how many emails we sent, how many unique
clicks there were, that sort of stuff. And put all of that data into an array, a double array, if you like,
that we set up for the work with Google Sheets and then
if I have some campaigns in there that in fact were never sent, they were just drafts, then we’ll make sure that
we still capture them they just have this notation
that they were not sent. So finally let’s just check,
we’ve got that one there. Looks like we need one more
to close that four loop so we’ll just put that there
and then we’re going to log. Let’s just log that campaign data. And we’ll take a look
what that data looks like. So let’s save this file and let’s run it and then when we hit
enter, you can see these it’s an array again and
then it has an inner array and then zero item the first one. My very first Mail Chimp campaign. And then you can see the second one there, and then the third one there. And you can see it’s giving
me all of the details now. I’ve got the name of campaign, the date, the subject line, how many
people it was sent to, how many people opened it and
how many clicks there were. This is looking really good now. This is exactly what the data I want. It looks like it’s in the
right format now to paste into my Google Sheet. Underneath where we just logged that data, rather log it now. We’ll go ahead and actually
paste it into Google Sheets. So what I’m going to do
is just copy in again some code here which
I’ll talk through now. So we’re going to go ahead
and select a sheet called campaign analysis so we better
just make sure we called ours campaign analysis or whatever you like as long as it’s matching. And then we just worked out
how many rows of data I have and then how many columns
of data I have and I’m going to go ahead and sort of
paste some of that data into the spreadsheet there. So let’s just give that a try, save. We’re going to hit run. It’s going to need to
have permission again to use the spreadsheet
service so we’ll click that. We’ll click, “Allow.” We’ll let it run and let it finish running and then we should go
back to our spreadsheet and you can see there now I
have a whole bunch of data now in my spreadsheet that’s
all real campaign data from Mail Chimp so that’s great. Now, one thing I’d like to
do because for my charts what I really want is to have an open rate and a click rate and again
what we’re going to do is add a little loop
here underneath this one that just loops over
all of the rows I have and for each row it adds the
two formulas to calculate the percentage, open rate and click rate. So let’s save that and run it again. And then I go back to my
sheet and you can see I have the percentage rates there for open rates and click through rates. So that’s great. That’s my Mail Chimp data. It’s brought through the
API into my spreadsheet so I can now go ahead and
actually do some analysis. And there is absolutely
a huge amount you can do with this API, we’ve looked
at just one end point there. So let me show you just the
API documentation quickly. And you can see just if I scroll down, you can see all of the different endpoints that you can get to so you
can go and get some data, all about your automations if you want. So really there’s a huge
amount you could go and get from you Mail Chimp
account if you dig into it. So I’m going to show you one other example using the Mail Chimp API and this time I’m just going to paste in
the whole code in one go. We’ll run it and we’ll see what it does and we can talk about it briefly. So I’ll create new Sheets. And I’m going to call
this one, “List Growth.” So I’m going to just paste
in the List Growth data here and quickly just show you what
it’s going to do is, again, it’s going to go to the API. It’s going to use the List
ID to identify my list and it’s going to just
pull out the growth history month by month and put that
into a spreadsheet for me. And then just before I do that,
before we run it actually, let’s just add in the headings quickly. So I’ll just add those in. I’ll make this bold, centered,
and we’ll wrap them as well just so they stand out. Okay, great. Now what we’re going to do is run that and we should find some
data filling in here. So let’s come back to
the code and this time instead of MailchimpCampaign
we’ll do mailchimpListGrowth. We’ll run, we’ll hit run. mailchimpListGrowth. That’s running and let’s
go back to our Sheets and there we have the data. And you can see how
much the list has grown by what the list values
are so that’s another nice example of bringing some
data into your Google Sheets. Now what I’m going to
do here is just show you the next step. What I did with these was to
actually create some charts from this data and I’ll just show you that in my finished example
sheets which is the one the template below is
the one you can click on and you can go and check that out. You can check the final version out. So, let me just show you that one. Okay, so here’s the Mail
Chimp API data again. And you can see it’s
just all the same data that I had before and then
I’ve pulled out the columns I want and just tied them up
a little bit for my charts. And then I’ve gone ahead
and created this chart here to show the performance
of my campaigns over time and you can see this
blue line at the top here is the average open rate and
this orange line down here is my average click rate
so I can see quite clearly how my newsletters, how my
campaigns are doing over time. So that’s one example and
the other one I showed you was the List Growth so, again, let’s see. Same tables I showed you
that we’d just tidied up a little bit and then I’ve gone ahead and created this chart here. Just to show that monthly
growth of my email list. And there you go, that’s a quick run down of how we might use the
Mail Chimp API to pull data directly into our Google
Sheets using some Appscripts. All the code is available for you to use by clicking those links below. So that’s everything for me today. I hope you enjoyed that lecture and happy data analysis with Mail Chimp. Thanks, guys. – All right, so this is
how you can pull data from the Mail Chimp API
directly into your Google Sheets via Google AppScript. Now all the links that Ben has mentioned are in the description below
and if you have any questions please leave them in the comments below because Ben and I are going to
try and answer them for you. And if you want to learn
more about Google Sheets and Data Studio, then check
out Ben’s channel right here where you can learn more about this topic. Now if you like this video
please give us a thumbs up and subscribe to this channel
because we’ll bring you new videos every Wednesday. My name is Julian, until next time.

30 comments

  1. Can the app run automatically on a schedule or do you have to manually trigger it every time? It would be great to use this data in a data studio report

  2. [17-03-29 15:48:25:617 EAT] TypeError: Cannot read property "length" from undefined.
    How to fix it?

  3. This looks great! Would be such a timesaver.

    For me the data is being pulled and returned in the log but it's not appearing in the sheet. The name of my sheet is 'Campaign Analysis' as it is in the code. The error is:

    TypeError: Cannot call method "getRange" of null

    I'm using api_002_mailchimp/measureSchool3.gs from github.

    Thanks

  4. Julian, I have 7 years of history with Mailchimp and don't really need to fetch all the data. How to limit the campaigns I get with this script to the recent ones?

  5. Are you able to run the script just using the API Key and not using the List ID. Most companies probably don't follow Mailchimp's best practice for keeping 1 clean list and it would be cool if you could just get all the campaign data from all the different lists… Thanks!

  6. This works great! Is it possible to make a separate sheet that imports the subscribers info into each row as well? Email, Name, Last Name, etc…

  7. Could you please one video on fetching the facebook marketing campaigns data into Spreadsheet using Facebook Marketing APIs? Since there is no free tool for it, I think it will be very useful for all of us.

  8. Hi Ben

    Simply amazing! Hey could you help me please? I need to fetch subscriber's activity (campaigns sent, opens, clicks, etc.) for each subscriber 🙂

    Thanks!

  9. Hello, I get this error in log and just can not figure out, how to fix this.

    TypeError: Cannot read property "length" from undefined. mailchimp script

    I have copied all the source code, add API key and list ID. List works almost fine, but Campaigtn sheet has no data :/

    Will be greatfull for your help.

  10. I'm try to create the same report about list growth but per week, but i can not get the data from API per week.
    Plz help me with that. someone did it?
    Amazing tutorial

  11. Awesome thanks Ben and Julian. You speak a little fast but it's all good stuff!! I just got my first API to work very happy!!!

  12. Great video! Any idea how to restrict the scripts to a specific list? It seems to be pulling from all lists despite adding the specific list ID

  13. I'm working on a script that refreshes, fetches, and sorts by date every 15 minutes or so. I'll post when ready if anybody is interested

  14. I got this error: SyntaxError: Unexpected token: < (line 26, file "Code")
    it is this line: var json = JSON.parse(data);

    help? I imagine there were changes and updates since this video came out?

  15. I keep getting this error: SyntaxError: Unexpected token: < (line 21, file "Code")
    On line 21 is this code: var json = JSON.parse(data);

  16. I think this was a great video. Are there any other resources out there that talk about the same concept but connecting to email databases that are not as user friendly at Mailchimp? And potentially getting data back to the browser and not a google sheet? Thanks.

  17. Hi, do you by chance have the code to add a new row in google sheets with google apps script every time theres a new mailchimp subscriber in a particular list? i´ll be more than thankful if you can help with that

  18. I´m trying to include UNSUBSCRIBE, HARD/SOFT BOUNCES per campaign, but didn´t find a way. Do you know how to do that? Any hint or help is much appreciated. Thanks.

  19. Hello Guys! I keep having this error:

    [19-01-01 20:50:37:497 PST] {instance=53157668-7588-470f-891b-6041cacec20b, detail=The API key provided is linked to datacenter 'us19', type=http://developer.mailchimp.com/documentation/mailchimp/guides/error-glossary/, title=Forbidden, status=403}

    and I'm using this code :::

    /***************************************
    var API_KEY = 'b29cd90bca701e71eef52c0c55f36f49-us19';

    var LIST_ID = '029369f00c';

    /******************************************************************************

    * call the Mailchimip API to get campaign data

    * This gets all campaigns in an account

    */

    function mailchimpCampaign() {

    // URL and params for the Mailchimp API

    var root = 'https://us11.api.mailchimp.com/3.0/';

    var endpoint = 'campaigns?count=100';

    // parameters for url fetch

    var params = {

    'method': 'GET',

    'muteHttpExceptions': true,

    'headers': {

    'Authorization': 'apikey ' + API_KEY

    }

    };

    // call the Mailchimp API

    var response = UrlFetchApp.fetch(root+endpoint, params);

    var data = response.getContentText();

    var json = JSON.parse(data);

    Logger.log(json);

    }
    ******************************************/

  20. Para aquellos que tienen el error "The API key provided is linked to datacenter " verifiquen que coincida el data center de su "api_key" con la variable "root" (la url).
    ejemplo:
    var API_KEY = 'abcdefghijklmnopkrstuvw *-us12 * ';
    var root = 'https:// *us12 * .api.mailchimp.com/3.0/';

  21. I have a SharePoint list to log correspondence against our contacts and i was looking to create a link between MailChimp so that whenever a campaign is sent to i.e. 90 contacts, 90 correspondence records would be created within the Sharepoint list outlining the contact that the campaign was sent to, when the campaign was sent and other information, is this possible using the MailChimp API?

  22. do people here give a good feedback and comments to this video even try those codes to see if it is working?

Leave a Reply

Your email address will not be published. Required fields are marked *