MailChimp Integration

MailChimp Integration

If you want to use MailChimp for your newsletters, instead of emailing them directly from TouchPoint, you can use our TouchPoint integration with MailChimp. This is fairly straightforward but does require some set up in your TouchPoint database as well as your church having a MailChimp account. We have the instructions below.

This integration allows you to create your email using the MailChimp templates and to use a recipient list from an organization in your TouchPoint database that we upload to your MailChimp account.

There are a few items of caution to understand about using MailChimp with TouchPoint:

  • These emails must be sent using an organization as your mailing list, not a Search Builder results list.
  • You cannot use email replacement codes inside any email sent via MailChimp other than the ones that are in MailChimp.
  • Because MailChimp uses the email address as the primary key, couples using the same email address will be sent only one email. This is true even if both spouses are in the organization used for the mailing.
  • The church must establish its own MailChimp account and pay those fees directly to MailChimp.

Note

We do not recommend using this method for all of your emails, but just your weekly, monthly newsletters or special communications.

TouchPoint Setup for MailChimp Integration

This integration has several parts.

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.

Step 3 - Set up your Mailing List Organizations in TouchPoint

You will need to create an organization 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 organization 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 organizations 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 organization to know who should receive your email.

Your MailChimp Account

Create an account for your church on MailChimp’s website.

Step 1

Click here to go to the MailChimp website.

Look for the Sign Up Free button and complete the form to create a user account for your church.

Make a note of the username as you will insert this into the script in your database.

Step 2

After logging into MailChimp, go to the top right corner (beside Help), and select Account from the drop-down menu.

Then select Extras > API Keys and create an API Key using the Create a Key button.

Copy and paste that key where it will be handy as you will put into the Python script that you will create in your database.

Step 3
If you want to use merge fields in your emails, log on to your MailChimp Account, click Settings > List Fields and MERGE Tags. These are fields you can use inside your emails sent via MailChimp. There are some defaults you can use, but you can also create others.

Important

We do not provide customer support for MailChimp, just for the TouchPoint side of the integration. If you need assistance using their product click the Help link in the top right corner of MailChimp’s website to search their Knowledge Base.

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

Important

After you paste the python script into your database be sure that the top lines of the code are aligned to the left, not indented. That is, lines 1-29 should not be indented, except for lines 11-17, as you see in the code below. You will see the line #s after you paste the code into your database.

Also, double-check to see that the endpoint and the API-Key have the same us code… it will be us2, us3, us16 or something similar. The code below has us2 in the endpoint, so you must change that to match your API-key.

#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. If you do not enter a parameter, the script will run for all orgs that have the MailChimp Extra Value.

The purpose of the file is to show you any problem emails. So, the list will display everyone in the org with any with 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 organization 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.

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:

  • Organization ID
  • Organization 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 organization to be a MailChimp mailing list org you will not get any results.
SELECT 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'

TouchPoint Mailing List Organizations

Each database can have multiple mailing lists organizations. We advise you to set these up as subscriptions so that your recipients can opt-in to whatever newsletters interest them. Also, if a recipient unsubscribes from MailChimp, this will get passed back to the TouchPoint database and drop the person from that mailing list org.

Here are a few keys to integrating a TouchPoint mailing list with MailChimp:

  1. Each org must have the MailChimp Extra Value. See Step 1 below.
  2. Each org must have a Leader enrolled in the org. That is someone with the Member Type of a Leader.
  3. Each org must have at least 1 member in addition to the Leader. This is true even when testing. Enroll one person as the Leader and one more person as a Member.

Caution

For each mailing list org, there is one Extra Value that is required (MailChimpList) and 3 Extra Values that are optional (FromName and FromEmail and PermissionReminder). The 3 optional Extra Values are FromName, FromEmail, and PermissionReminder.

Click here for more about the Leader and the EVs.

Required MailChimp Extra Value

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

Step 1
Go to the organization 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.
http://i.tpsdb.com/./2017-11-13_11-15-47.png

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

Optional Extra Values

By default, the name and email address of the Leader of the org 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 organization by using an Extra Value.

Create the FromName Extra Value:

Step 1
Go to the organization 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 organization 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 orgs in TouchPoint.

Create the PermissionReminder

Step 1
Go to the organization 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 orgs in TouchPoint.

© 2018 TouchPoint Software, LLC.  All rights reserved.