MailChimp Integration ===================== .. glossary:: 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 involvement 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 **involvement 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 involvement 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. :ref:`See below ` .. _ImportantNote: .. important:: When you open an account with MailChimp, make sure you purchase the account plan appropriate for your needs. Free accounts are available but are limited to only one mail list (audience), that is, you will be able to sync only one TouchPoint involvement to your Mail Chimp account. As of this writing, their Essentials Plan is limited to 3 mail lists, the Standard Plan is limited to 5 mail lists, and the Premium Plan allows unlimited mail lists. 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 :ref:`Python script `, where you will need to add information relating to your church and your MailChimp account. - A :ref:`SQL Script - MailChimpMembersOfOrg ` for the list of mail list members - A :ref:`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. :ref:`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. .. important:: When a user unsubscribes through MailChimp, as described above (clicking on the Unsubscribe link at the bottom of the email), you will not be able to add them back to the MailChimp audience by means of the TouchPoint sync, nor will Unarchiving them within Mailchimp be sufficient for them to receive emails again. Instead they (or you on their behalf) will first need to re-subscribe via the MailChimp sign up form. Then you will again be able to manage their subscription from within TouchPoint. For information about re-subscribing people in MailChimp, see their help article `Resubscribe a Contact | Mailchimp `_. | .. _YourMailChimpAccount: 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. .. _PythonScriptMailChimpSync: 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. .. code-block:: python :linenos: #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") .. _SQLScriptMailChimpMembersOfOrg: 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. .. code-block:: sql :linenos: 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 .. _SQLScriptMailChimpOrgsSql: 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. .. code-block:: sql 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' .. _MailingListOrgs: TouchPoint Mailing List Involvements ------------------------------------- Each database can have multiple mailing lists involvements. 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 involvement. Here are a few keys to integrating a TouchPoint mailing list with MailChimp: #. Each involvement must have the **MailChimp Extra Value**. See Step 1 below. #. Each involvement must have a **Leader** enrolled in the org. That is someone with the Member Type of a Leader. #. Each involvement must have the **Leader Member Type** set. #. Each involvement 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 involvement, 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. :ref:`Click here for more about the Leader and the EVs `. 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. .. figure:: https://i.tpsdb.com/./2017-11-13_11-15-47.png :target: # Repeat this for any other mailing lists involvement in your database. .. _LeaderOrFromNameFromEmail: 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. | | +--------------------+------------------+ | **Latest Update** | **09/06/2022** | +--------------------+------------------+ Correct typos in scripts.