It's a good idea to regularly backup your MailChimp subscriber list, especially if you have scripts or apps that might damage your list data.

If your first instinct is to export the list as CSV, STOP. MailChimp CSV export misses a few very important pieces of information, such as the original ID of the subscriber, ID of the subscriber list, and most importantly, the tags of the subscriber.

It is recommended to always use the MailChimp API for data export, because it will return data true to its original form. Refer to the previous post on how to run bulk export using MailChimp batch API.

This post will take it one step further, and show you how to export a subscriber list and save it into Google Sheets.


MailChimp API Key

Before we start, let's get the access token, also known as API key, of your MailChimp account.

Authorize with MailChimp

Batch Export Subscriber List

We have covered how to export export a subscriber list using the MailChimp batch API in a previous post. This is a quick rehash of the script.


const MailChimp = require('mailchimp-api-v3')
const api = new MailChimp(process.env.MAILCHIMP_API_KEY)

// Get all "/lists"
let result = await api.get('/lists')
if (result.lists.length === 0) return

// Get the first list result
const list = result.lists[0]
console.log(`Using List: ${list.name} - ID: ${list.id}`)
console.log("Exporting members. Please wait...")

// Batch get list subscribers
result = await api.batch({
	method: 'get',
	path: `/lists/${list.id}/members`,
	query: { count: 20 }
}, { verbose: false })
console.log(result.members)

We are using the batch API, which would never timeout on large queries, but the execution time could be over 30 seconds to a minute even for small queries.

For simplicity sake, we are only exporting the first of your subscribers lists (Line 10-11). If there's a particular list you want to use, see the Find List by Web ID post.

Also note we are limiting this query to 20 members (Line 16). This is to ensure the query returns relatively quickly. Feel free to expand the query count (Line 16) to a number that exceed your list size once you are ready to export the whole list.

Flatten the List Data

List data returned by MailChimp API is true to its original form, which is in a hierarchical format, also know as object model. If you have a MailChimp API key and run the script in previous step, you can explore the returned data model.

Spreadsheets store data in rows and columns, also known as tabular form. We need to first flatten the subscriber member data model into tabular form, where each member is a row. In order to preserve data fidelity, fields that are not simple text or numbers are formated to JSON strings.


// Collect all column names from subscriber object model
const keys = Object.keys(members[0])

// Flatten subscriber members into rows
let rows = members.map(member => {
	// Flatten each member attribute into column
    return keys.map(key => {
        const value = member[key]
        if (typeof value === "object") {
            // For complex fields, turn object into JSON string
            return JSON.stringify(value)
        } else {
            return value
        }
    })
})

// Insert column names (keys) as table headers
rows = [keys, ...rows]

There is no best answer for converting a hierarchical object model to tabular form. In this case we are trying to preserve as much information as possible for data backup purpose, hence converting complex fields into JSON (Line 10-11). For instance the member tags fields looks like follows,

[{ "id":16213, "name":"newsletter" }]

The extra brackets and braces preserves data fidelity, but the field is not easily editable in the spreadsheet. In later posts we will dig into how to further expand complex data structure to enable better analysis and bulk editing in Google Sheets.


Google Sheets Access Token

Now we have batch exported subscriber list data into tabular form, we can save the data into Google Sheets API. First let's get the access token.

Save List to Google Sheets

If this is your first time using Google Sheet API, consider reading through Google Sheets API Quick Start first.

Save to Google Sheets is straight forward since the data is already in tabular form. Here is the script in its entirety, including MailChimp export shown in previous steps (Line 1-45).


const MailChimp = require('mailchimp-api-v3')
const api = new MailChimp(process.env.MAILCHIMP_API_KEY)

// Get all "/lists"
let result = await api.get('/lists')
if (result.lists.length === 0) return

// Get the first list result
const list = result.lists[0]
console.log(`Using List: ${list.name} - ID: ${list.id}`)
console.log("Exporting members. Please wait...")

// Batch get list subscribers
result = await api.batch({
	method: 'get',
	path: `/lists/${list.id}/members`,
	query: { count: 20 }
}, { verbose: false })

// Get subscriber members from list
const { members } = result
if (members.length === 0) {
    console.log("No members in list")
    return
}

// Collect all column names from subscriber object model
const keys = Object.keys(members[0])

// Flatten subscriber members into rows
let rows = members.map(member => {
	// Flatten each member attribute into column
    return keys.map(key => {
        const value = member[key]
        if (typeof value === "object") {
            // For complex fields, turn object into JSON string
            return JSON.stringify(value)
        } else {
            return value
        }
    })
})

// Insert column names (keys) as table headers
rows = [keys, ...rows]

// Initialize Google API and Auth
const { google } = require("googleapis")
const auth = new google.auth.OAuth2()
auth.setCredentials({ access_token: process.env.GOOGLE_ACCESS_TOKEN })
const sheets = google.sheets({ version: "v4", auth })

// Create new document matching subscriber list name
const newSheetResp = await sheets.spreadsheets.create({
    resource: {
        properties: { title: list.name }
    }
})

// Insert subscriber members into new spreadsheet
const { spreadsheetId } = newSheetResp.data
const insert = {
    spreadsheetId,
    range: 'Sheet1!A1:E',
    valueInputOption: 'USER_ENTERED',
    insertDataOption: 'INSERT_ROWS',
    resource: {
        values: rows
    }
}
const insertResp = await sheets.spreadsheets.values.append(insert)

There are few things you can tweak in this script to fit your needs,

  • Line 9 picks the first subscriber list as the backup target. You can substitude a list of your choice here. See Find List by Web ID.
  • Line 17 limits the export count to 20. Increase it to match/exceed your list size.
  • Line 53-58, we are creating a new Sheets document to store the export. If you'd rather append to existing spreadsheet, comment out line 53-58, and replace line 63 with a document ID of your choice. See Google Sheets Quick Start on how to work with existing document ID.