New Guest Email Campaign

See also

Download the whole script: Files/NewGuestEmailCampaign.py

This script sends 6 different emails each week in succession to members of an org based on which emails they have already received.

The email content is stored in Special Content HTML area. The name of each email in content is called NewGuestEmail-1, NewGuestEmail-2, etc. These same names will be used for Sub-Groups in the org to indicate that the guest has been sent that email. The subject of each email comes from the Title in the Special Content file.

Every time the script runs, a person will be sent the next email after the last one already sent for that person. If the person has not received any, the first email will be sent.

You will be able to schedule these emails to be sent once a week on the day and time of your choice.

You will need to modify the variables at the top of the script for your church (lines 1-4)

Also, there is a testing mode model.TestEmail = True, on line 5 that you must comment out or set to False when you are ready to go to production.

 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
orgid = 100 # your organizationid for your new guests
queuedby = 200 # peopleid for the sender
fromemail = 'pastor@church.com'
fromname = 'Guest Services'
model.TestEmail = True # SET THIS TO False WHEN READY TO GO TO PRODUCTION

model.Transactional = True # prevents sent email notices from being sent.

# this sql finds the the persons in the org that should be sent the numbered email.
sql = '''
WITH members AS (
	SELECT om.PeopleId, 
		LastGroup = (SELECT MAX(Value) FROM dbo.Split(Groups, CHAR(10)) WHERE Value LIKE 'NewGuestEmail-%')
	FROM dbo.OrgPeopleCurrent({0}) om
	JOIN dbo.People p ON p.PeopleId = om.PeopleId
	JOIN dbo.OrganizationMembers omm ON omm.PeopleId = p.PeopleId AND omm.OrganizationId = {0}
	WHERE omm.MemberTypeId = 220
),
sends AS (
	SELECT om.PeopleId, 
		SendNumber = CONVERT(INT, RIGHT(ISNULL(om.LastGroup, '0'), 1)) + 1
	FROM members om
)
SELECT PeopleId FROM sends WHERE SendNumber = {1}
'''

# build six queries for each numbered email to be sent

qlist = [] # the queries are stored in this list
for n in range(6): 
    query = q.SqlPeopleIdsToQuery(sql.format(orgid, n + 1)) # n = 0 to 5
    qlist.append(query)
    print n + 1, query, '<br>' # for debugging purposes
    
# use each query to send email and add sub-group

for n in range(6):
    emailname = 'NewGuestEmail-' + str(n+1)
    query = qlist[n]
    model.EmailContent(query, queuedby, fromemail, fromname, emailname)
    model.AddSubGroupFromQuery(query, orgid, emailname)