ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
API_KEYXXXXXXXXXXXXXXXXXXXXXXXXXX-usX
Your Mailchimp API key. Instructions for creating a key are linked here.
2
API_SERVERusX
The server associated with your API key (it's at the end of the key)
3
LIST_IDXXXXXXXXXX
The identifier for your audience in Mailchimp. Instructions for finding it are linked here
4
NEWSLETTER_IDXXXXXXXXXX
The identifier for your newsletter group in Mailchimp. Unfortunately, this is kind of a pain to find. See purple box below.
5
DONATION_FIELD
SUM365
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"
6
PERIOD30
The time period, in days, to look at. 30 seems pretty good to me.
7
8
How to use this report
9
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
10
subscribers who donated money in their first 30 days of membership, and it's meant to be run every week. Each row in the
11
"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
12
week's run. There's no good way to extract many months of data that would support this calculation. (At least with the data
13
that News Revenue Hub stores on each contact. If you've got another provider, maybe you can.) So, we have to run this
14
report every week, and build the data slowly over time. So yeah, you'll want to automate this report.
15
16
1. Make a copy of this spreadsheet.
17
2. Update the API key, server, list ID, newsletter id, and donation field, highlighted in yellow above.
18
3. Click Memberkit > Run report
19
4. Give the script permisssion to run. This will look scary, and you will need to click "Advanced" on the prompt to continue.
20
5. After the report has run, check the "Raw data" tab. There should be a new row with today's date.
21
6. Delete all the rows in the "Raw data" tab, keeping the headers.
22
7. Automate this report, click Tools > Script editor. Once the editor has opened, click Edit > Current project's triggers.
23
Click the Add trigger button, and set the trigger to run the "go" function every Monday morning. It'll look like this --------------->>>
24
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.
25
26
How to find your newsletter group ID
27
1. Go to the Mailchimp API playground. You'll need to specify which server you're using at the beginning of the URL.
28
---> API_SERVER.api.mailchimp.com/playground/
29
2. Click "Lists", and then click the list that contains the newsletter group.
30
(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"
31
and a recent date, formatted like 2020-04-01.)
32
3. Click the dropdown labeled "Subresources" and select "interest categories".
33
4. Click the interest category that contains your newsletter.
34
5. Click the dropdown labeled "Subresources" and select "interests".
35
6. Click the interest that is your newsletter. The identifier we want is just labelled "id", not "list_id" or "category_id".
36
7. Have a snack, you've earned it.
37
38
How to adapt this report for other membership vendors
39
The DONATION_FIELD above is specific to News Revenue Hub. It's a numeric field, signifying the amount of money
40
the contact has given in the last year. For us, it was the easiest way to identify all contacts who've donated, regardless
41
of if they've given enough money to cross the threshold to become a member. If you have a similar, numeric field like this
42
that's great! Put it's name in the DONATION_FIELD cell above and proceed. If not, here's where to start...
43
1. Go to Tools > Script Editor
44
2. Scroll down to line 80, where there's a comment labelled "CHANGE THIS IF YOUR DONATION FIELD IS NOT NUMERIC"
45
3. Confused? Call your friendly, neighborhood software developer. They'll get it pretty quickly.
46
47
Memberkit 1.0
48
Copyright 2020, WHYY and Colorado Public Radio.
49
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100