Step 1 - Create a User Account on MailChimp¶

First, you must have a MailChimp Account. See below

Step 2 - Add Python and SQL Scripts to your Database¶

Your system admin will need to add 3 scripts to the Special Content section of your database. Only the Python script requires any editing. Each script has a specific set of instructions along with the code to copy and paste into your database.

• A Python script, where you will need to add information relating to your church and your MailChimp account.

• A SQL Script - MailChimpMembersOfOrg for the list of mail list members

• A SQL Script - MailChimpOrgsSql for the list of mail list orgs

Step 3 - Set up your Mailing List involvements in TouchPoint¶

You will need to create an involvement in TouchPoint for each mailing list, each with a specific required Extra Value, and some optional Extra Values.

Follow the instructions below to create these orgs in your database.

Step 4 - Create a Mail Chimp Campaign and Send an Email¶

After you have completed Steps 1-3 above, you are ready to create an email in Mail Chimp and send to your mailing list. You can have more than one involvement in TouchPoint that you can use for your mailing lists. For example, you might have one org for your church-wide newsletter, one for a children’s ministry newsletter, and another for students. When you run the Python MailChimpSync all involvements with the MailChimp Extra Value will be pulled into MailChimp, each as a separate list.

So, before you can select the Recipient List in Mail Chimp you must have already run the Python Script named MailChimpSync. After you have done that, you will see your list(s) under Lists in the header in Mail Chimp. When you are ready to send your email, just follow the directions in Mail Chimp and select the appropriate List for your recipients.

MailChimp does allow you to send Test Emails by entering one or more email addresses separated by a comma into the Test Email field. We always recommend you do that.

Unsubscribe¶

When someone clicks Unsubscribe in the email you sent via MailChimp the next time you run the MailChimpSync in TouchPoint, those people will be dropped from the mailing list org and will now display as Previous Members of that org. So, it is important to always sync with MailChimp just prior to sending another email. Even if you do not run the sync, those who unsubscribe will not be sent any more emails via Mail Chimp. However, in order for you to know who should and should not receive your email, you will want to keep your org up-to-date by running the MailChimpSync. Then you will be able to use the involvement to know who should receive your email.

Python Script: MailChimpSync¶

This is the script that runs the TouchPoint end of the process and contains your MailChimp account information as well as information about your church.

Step 1

Go to Administration > Set Up > Special Content > Python Scripts and click the green +New Python Script File.

Enter this as the name of the file: MailChimpSync and click the blue Submit button.

Step 2

Select all of the code below, then copy and paste it into this new script and click Save.

Step 3

Now edit the file as follows:

• Enter the MailChimp username for your church’s MailChimp account where you see ‘your username’

• Paste in the mailchimppassword (the API key you created)

• In the endpoint, replace the us2 portion of the URL in the script with whatever is at the end of your MailChimp API key (it is after the dash). It will be us2 or us3 for example.

• Replace the information for the following fields for churchinfo:

  • company

  • address 1

  • city

  • state

  • zip

  • country

  • phone

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215

#Roles=MailChimp,Admin
# MailChimpSync
import json

#---------------------------------------------
# modify the following values for your church
mailchimpuser = 'your username'
mailchimppassword = 'your api code' # MailChimp API-Key
endpoint = "https://us2.api.mailchimp.com/3.0/"
churchinfo = {
    "company": "Bellevue Baptist Church",
    "address1": "2000 Appling Road",
    "city": "Cordova",
    "state": "TN",
    "zip": "38016",
    "country": "US",
    "phone": "901-347-2000"
}
#---------------------------------------------
# Other requirements
# Add a checkbox type Extra Value to the org called MailChimpList and set it to True
# By default, if there is a Leader of the org,
# their email address and name will be used when creating the MailChimp list as the default FromName and FromEmail.
# You can also set these specifically with two text type Extra Values called FromName and FromEmail
# By default, a MailChimp list will have a Permission Reminder set to
# 'You are receiving this because you are subscribed to this list'.
# You can explicitly set that to what you want by creating another text ExtraValue called PermissionReminder

headers = { 'content-type': 'application/json' }

def Log(text):
if model.DataHas("LogFile"):
    model.LogToContent(model.Data.LogFile, text)
else:
    print(text)

def RestGet(url):
    returnvalue = model.RestGet(url, headers, mailchimpuser, mailchimppassword)
    return json.loads(returnvalue)

def RestPost(url, data):
    returnvalue = model.RestPostJson(url, headers, data, mailchimpuser, mailchimppassword)
    return json.loads(returnvalue)

def RestDelete(url):
    model.RestDelete(url, headers, mailchimpuser, mailchimppassword)

def GetOrgMembers(orgid):
    sql = model.Content('MailChimpMembersOfOrg').replace('@p1', str(orgid))
    return q.QuerySql(sql.replace('SELECT * FROM mailchimplist',
                                'SELECT * FROM mailchimplist WHERE MailChimpError IS NULL'))

def CreateList(org):
    listinfo = {
    "name": "{0} ({1})".format(org.OrganizationName, org.OrganizationId),
    "contact": churchinfo,
    "permission_reminder": org.PermissionReminder,
    "campaign_defaults": {
        "from_name": org.FromName,
        "from_email": org.FromEmail,
        "subject": "",
        "language": "en"
        },
    "email_type_option": False
    }
    Log("Creating List {0}".format(org.OrganizationName))
    obj = RestPost(endpoint + "lists?fields=id", listinfo)
    listid = obj["id"]
    org.MailChimpListId = listid
    model.AddExtraValueTextOrg(org.OrganizationId, "ListId", listid)
    AddMergeFields(listid)
    AddMembers(org)

def AddMergeFields(listid):
    CreateMergeFieldsUrl = endpoint + "lists/{0}/merge-fields?fields=merge_id"
    obj = RestPost(CreateMergeFieldsUrl.format(listid),
                { "tag": "PEOPLEID", "name": "PeopleId", "required": True, "type": "text" })
    obj = RestPost(CreateMergeFieldsUrl.format(listid),
                { "tag": "DB", "name": "Db", "type": "text", "default_value": model.DatabaseName })

def AddMembers(org):
    members = GetOrgMembers(org.OrganizationId)
    AddTheseMembersToList(members, org)

def AddTheseMembersToList(members, org):
    membersarray = [];
    for m in members:
        membersarray.append( {
            "email_address": m.EmailAddress,
            "status": "subscribed",
            "merge_fields": {
                "FNAME": m.PreferredName,
                "LNAME": m.LastName,
                "PEOPLEID": m.PeopleId,
                "DB": model.DatabaseName
                }
        })
    CreateMemberUrl = endpoint + "lists/{0}?fields=total_created,error_count,errors"
    pagesize = 100
    nummembers = len(membersarray)
    Log("Starting to Add {0} Total Members to List({1})".format(nummembers, org.OrganizationName))
    for i in range(0, nummembers, pagesize):
        segment = membersarray[i : i + pagesize]
        data = { "members": segment }
        Log("Adding {0}-{1} Members to List({2})".format(i + 1, i + pagesize, org.OrganizationName))
        obj = RestPost(CreateMemberUrl.format(org.MailChimpListId), data)
        for em in obj["errors"]:
            err = em["error"]
            Log(err)
            query = "EmailAddress = '{0}'".format(em["email_address"])
            model.AddExtraValueText(query, 'MailChimpError', err)

def RemoveTheseMembersFromList(listmembers, org):
    DeleteMemberUrl = endpoint + "lists/{0}/members/{1}"
    for m in listmembers:
        id = m["id"]
        Log("Removing list({0}) member({1})".format(org.OrganizationName, id))
        obj = RestDelete(DeleteMemberUrl.format(org.MailChimpListId, id))

def DropTheseMembersFromOrg(orgmembers, org):
    for m in orgmembers:
        Log("Dropping OrgMember {0} from {1}".format(m.PeopleId, org.OrganizationName))
        pid = int(m.PeopleId)
        oid = int(org.OrganizationId)
        model.DropOrgMember(pid, oid)

def ProcessUnsubscribes(org):
    orgmembers = GetOrgMembers(org.OrganizationId)

    FetchUnsubscribesUrl = endpoint + "lists/{0}/members/?status=unsubscribed&offset={1}&count={2}&fields=total_items,members.id,members.email_address"
    listunsubs = []
    offset = 0
    pagesize = 500
    while True:
        mlist = RestGet(FetchUnsubscribesUrl.format(org.MailChimpListId, offset, pagesize))
        totalitems = mlist["total_items"]
        if totalitems == 0:
            break;
        Log("GetListMembers {0}-{1} of {2} for {3}".format(offset + 1, offset + pagesize, totalitems, org.OrganizationName))
        members = mlist["members"]
        listunsubs.extend(members)
        if(len(listunsubs) == totalitems):
            break;
        offset += pagesize
    listunsubids = [m["id"] for m in listunsubs]
    drops = list(filter(lambda m: m.id in listunsubids, orgmembers))
    DropTheseMembersFromOrg(drops, org)

def Synchronize(org):
    orgmembers = GetOrgMembers(org.OrganizationId)
    FetchMembersUrl = endpoint + "lists/{0}/members/?offset={1}&count={2}&fields=total_items,members.id,members.email_address"
    listmembers = []
    offset = 0
    pagesize = 500
    while True:
        mlist = RestGet(FetchMembersUrl.format(org.MailChimpListId, offset, pagesize))
        totalitems = mlist["total_items"]
        if totalitems == 0:
            break;
        Log("GetListMembers {0}-{1} of {2} for {3}".format(offset + 1, offset + pagesize, totalitems, org.OrganizationName))
        members = mlist["members"]
        listmembers.extend(members)
        if(len(listmembers) == totalitems):
            break;
        offset += pagesize

    orgmemberids = [m.id for m in orgmembers]
    listmemberids = [m["id"] for m in listmembers]
    addids = list(set(orgmemberids) - set(listmemberids))
    removeids = list(set(listmemberids) - set(orgmemberids))
    adds = list(filter(lambda m: m.id in addids, orgmembers))
    removes = list(filter(lambda m: m["id"] in removeids, listmembers))

    RemoveTheseMembersFromList(removes, org)
    AddTheseMembersToList(adds, org)

def GetList(org):
    if org.MailChimpListId is None:
        return None
    Log("GetList " + org.OrganizationName)
    FetchListUrl = endpoint + "lists/{0}?fields=id,name,campaign_defaults,date_created".format(org.MailChimpListId)
    obj = RestGet(FetchListUrl)
    if "status" in obj and obj["status"] == 404:
        return None
    return obj

def ProcessOrg(org):
    list = GetList(org)
    if list is None:
        CreateList(org)
    else:
        ProcessUnsubscribes(org)
        Synchronize(org)
    model.AddExtraValueDateOrg(org.OrganizationId, "LastMailChimpSyncDt", model.DateTime)

def ProcessOneOrg(orgid):
    sql = model.Content("MailChimpOrgsSql") + " WHERE o.OrganizationId = " + str(orgid)
    org = q.QuerySqlTop1(sql)
    ProcessOrg(org)

def ProcessAllOrgs():
    FetchListsUrl = endpoint + "lists?fields=lists.id,lists.name,lists.campaign_defaults,lists.date_created"
    sql = model.Content("MailChimpOrgsSql")
    orgs = q.QuerySql(sql)
    for org in orgs:
        ProcessOrg(org)

if model.DataHas("OrgId"):
    ProcessOneOrg(model.Data.OrgId)
else:
    ProcessAllOrgs()

# do not change the following Log statement,
# the background process progress page looks for this.
Log("Background Process Completed")

SQL Script: MailChimpMembersOfOrg¶

When you run this script, it will produce a list of all the members of any orgs you have set up to be a MailChimp list. You can run the script for just one org by putting the org ID# for that org in the parameter field on the script and then clicking Run Script. It may be easiest to run the MailChimpOrgsSql script and use the involvement name links to see the list of members (the output of this script for the involvement you click).

The purpose of the file is to show you any problem emails. So, the list will display everyone in the org, with any problem emails listed at the top of the list. The error message will display beside the email address letting you know what is wrong with that email address. This will allow you to fix the email address prior to sending out your email.

These are the columns in this report:

• People ID

• email Address

• Preferred Name

• Last Name

• ID

• Mail Chimp Error

Step 1

Go to Administration > Set Up > Special Content > SQL Scripts and click the green +New SQL Script File.

Enter this as the name of the file: MailChimpMembersOfOrg and click the blue Submit button.

Step 2

Select all of the code below, then copy and paste it into the file and click Save. There is nothing in this file that needs to be edited.

Step 3

You can run the script now by clicking the Run Script button. However, until you set up an involvement to be a MailChimp mailing list org and have members in the org you will not get any results.

Before you send out an email to any given list, you will want to run this script in order to verify that all email addresses are correct.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

WITH members as (
SELECT p.PeopleId, EmailAddress = RTRIM(p.EmailAddress)
FROM dbo.People p
JOIN dbo.OrgPeopleCurrent(@p1) om ON om.PeopleId = p.PeopleId
),
nodupemails AS (
    (SELECT PeopleId, EmailAddress
    FROM (SELECT p.PeopleId, p.EmailAddress, ROW_NUMBER()
            OVER(PARTITION BY EmailAddress ORDER BY EmailAddress) row
          FROM members p
          WHERE LEN(ISNULL(p.EmailAddress, '')) > 0
         ) tt
    WHERE row = 1)
),
mailchimplist AS (
    SELECT PeopleId = CONVERT(VARCHAR, p.PeopleId), nd.EmailAddress, PreferredName = ISNULL(p.PreferredName,''), p.LastName,
        id=LOWER(CONVERT(VARCHAR(100),HASHBYTES('MD5', LOWER(CONVERT(VARCHAR(100),nd.EmailAddress))),2)),
        pe.Data MailChimpError
    FROM dbo.People p
    JOIN nodupemails nd ON nd.PeopleId = p.PeopleId
    LEFT JOIN dbo.PeopleExtra pe ON pe.PeopleId = p.PeopleId AND pe.Field = 'MailChimpError'
)
SELECT * FROM mailchimplist
ORDER BY MailChimpError desc, LastName, PreferredName

SQL Script: MailChimpOrgsSql¶

This script will run for all the MailChimp orgs or for just one org, if you enter the org ID# in the parameter field.

This will create a report showing the following information about each org:

• involvement ID

• involvement Name

• From Name (the person the email will come from)

• From Email (the email address of the person sending it)

• Permission Reminder (this is the reason the person is receiving the email)

• MailChimp List ID (each org will have its own unique ID# from MailChimp)

Step 1

Go to Administration > Set Up > Special Content > SQL Scripts and click the green +New SQL Script File.

Enter this as the name of the file: MailChimpOrgsSql and click the blue Submit button.

Step 2

Select all of the code below, then copy and paste it into the file and click Save. There is nothing in this file that needs to be edited.

Step 3

You can run the script now by clicking the Run Script button. However, until you set up an involvement to be a MailChimp mailing list org you will not get any results.

SELECT o.OrganizationId
    ,LinkForNext = '/RunScript/MailChimpMembersOfOrg/' + CONVERT(VARCHAR, o.OrganizationId)
    ,o.OrganizationName
    ,FromName = ISNULL(fn.Data, le.Name)
    ,FromEmail = ISNULL(fe.Data, le.EmailAddress)
    ,PermissionReminder = ISNULL(pr.Data, 'You are receiving this because you are subscribed to this list')
    ,MailChimpListId = li.Data
FROM dbo.Organizations o
JOIN dbo.OrganizationExtra e ON e.OrganizationId = o.OrganizationId AND e.Field = 'MailChimpList' AND e.BitValue = 1
LEFT JOIN dbo.People le ON le.PeopleId = o.LeaderId
LEFT JOIN dbo.OrganizationExtra fn ON fn.OrganizationId = o.OrganizationId AND fn.Field = 'FromName'
LEFT JOIN dbo.OrganizationExtra fe ON fe.OrganizationId = o.OrganizationId AND fe.Field = 'FromEmail'
LEFT JOIN dbo.OrganizationExtra pr ON pr.OrganizationId = o.OrganizationId AND pr.Field = 'PermissionReminder'
LEFT JOIN dbo.OrganizationExtra li ON li.OrganizationId = o.OrganizationId AND li.Field = 'ListId'

Required MailChimp Extra Value¶

You must have this Extra Value on any involvements you want to use as mailing lists with MailChimp.

Step 1

Go to the involvement Settings > Extra Values tab, select Show Ad Hoc Extra Values, and then click the green Add/Update Ad Hoc Extra Value button.

Step 2

Select Checkbox as the Type, enter MailChimpList as the Name, and check the box labeled Checkbox Value. This will set the value to True. Click the Save button.

Repeat this for any other mailing lists involvement in your database.

Optional Extra Values¶

By default, the name and email address of the Leader of the involvement will be used when creating the MailChimp list as the default FromName and FromEmail. However, if you’d like to specify that in the Extra Values you can do that.

Also, within MailChimp there is a default Permission Reminder letting recipients know why they received the email. You can add a customized reminder on the involvement by using an Extra Value.

Create the FromName Extra Value:

Step 1

Go to the involvement Settings > Extra Values tab, select Show Ad Hoc Extra Values, and then click the green Add/Update Ad Hoc Extra Value button.

Step 2

Select Text(multiline) as the Type, enter FromName as the Name, and enter the name of the person from whom the email should come. This does not have to match a record in the database. Click the Save button.

Create the FromEmail Extra Value:

Step 1

Go to the involvement Settings > Extra Values tab, select Show Ad Hoc Extra Values, and then click the green Add/Update Ad Hoc Extra Value button.

Step 2

Select Text(multiline) as the Type, enter FromEmail as the Name, and enter the full email address of the person from whom the email should come. Click the Save button.

Repeat these steps for any other mailing list involvements in TouchPoint.

Create the PermissionReminder

Step 1

Go to the involvement Settings > Extra Values tab, select Show Ad Hoc Extra Values, and then click the green Add/Update Ad Hoc Extra Value button.

Step 2

Select Text(multiline) as the Type, enter PermissionReminder as the Name, and enter the text that you want the recipient to see regarding why he is receiving the email. ex. You are receiving this because you are subscribed to the church Newsletter list. Click the Save button.

Repeat these steps for any other mailing list involvements in TouchPoint.

Correct typos in scripts.