Your Mailchimp API key. Instructions for creating a key are linked here.

The server associated with your API key (it's at the end of the key)

The identifier for your audience in Mailchimp. Instructions for finding it are linked here

The identifier for your newsletter group in Mailchimp. Unfortunately, this is kind of a pain to find. See purple box below.

A merge field in Mailchimp signifying a dollar amount donated. (A number, not "true" or another word.) For News Revenue Hub customers, this will look something like "SUM365"

The time period, in days, to look at. 30 seems pretty good to me.

How to use this report

A lot of Memberkit reports are a-ok if you don't automate them. This one is different. It's calculating the % of new newsletter

subscribers who donated money in their first 30 days of membership, and it's meant to be run every week. Each row in the

"Raw data" tab is a snapshot of the last 30 days -- so if you run it every week, each row contains some contacts from the prior

week's run. There's no good way to extract many months of data that would support this calculation. (At least with the data

that News Revenue Hub stores on each contact. If you've got another provider, maybe you can.) So, we have to run this

report every week, and build the data slowly over time. So yeah, you'll want to automate this report.

1. Make a copy of this spreadsheet.

2. Update the API key, server, list ID, newsletter id, and donation field, highlighted in yellow above.

3. Click Memberkit > Run report

4. Give the script permisssion to run. This will look scary, and you will need to click "Advanced" on the prompt to continue.

5. After the report has run, check the "Raw data" tab. There should be a new row with today's date.

6. Delete all the rows in the "Raw data" tab, keeping the headers.

7. Automate this report, click Tools > Script editor. Once the editor has opened, click Edit > Current project's triggers.

Click the Add trigger button, and set the trigger to run the "go" function every Monday morning. It'll look like this --------------->>>

7. It's a little thing, but once you've got this running, I'd move the "Config" tab all the way to the right. It's ugly.

How to find your newsletter group ID

1. Go to the Mailchimp API playground. You'll need to specify which server you're using at the beginning of the URL.

---> API_SERVER.api.mailchimp.com/playground/

2. Click "Lists", and then click the list that contains the newsletter group.

(If you cannot see the list you want, you will need to click the filter button. I like to filter by "since campaign last sent"

and a recent date, formatted like 2020-04-01.)

3. Click the dropdown labeled "Subresources" and select "interest categories".

4. Click the interest category that contains your newsletter.

5. Click the dropdown labeled "Subresources" and select "interests".

6. Click the interest that is your newsletter. The identifier we want is just labelled "id", not "list_id" or "category_id".

7. Have a snack, you've earned it.

How to adapt this report for other membership vendors

The DONATION_FIELD above is specific to News Revenue Hub. It's a numeric field, signifying the amount of money

the contact has given in the last year. For us, it was the easiest way to identify all contacts who've donated, regardless

of if they've given enough money to cross the threshold to become a member. If you have a similar, numeric field like this

that's great! Put it's name in the DONATION_FIELD cell above and proceed. If not, here's where to start...

1. Go to Tools > Script Editor

2. Scroll down to line 80, where there's a comment labelled "CHANGE THIS IF YOUR DONATION FIELD IS NOT NUMERIC"

3. Confused? Call your friendly, neighborhood software developer. They'll get it pretty quickly.

Copyright 2020, WHYY and Colorado Public Radio.

This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.