Recent Inactive Today

This recipe will allow you to track people who have been active over the last year, but have recently become inactive for some reason. This presents an opportunity to minister to those persons. There may be a spiritual problem or perhaps they have moved, or joined another church. Of course, you will want to have policies in place and ministers who will reach out to these people.

The code will Email a report to selected leaders.

Installation

  1. Create a StatusFlag query such as the following:

    RecentAttendCount( Days=365 ) >= 12
    AND RecentAttendCount( Days=60 ) = 0
    

    This query finds those people who have had attendance recorded at any event or meeting 12 or more times in the past year. But have not had any attendance recorded in the past 60 days. You can use whatever numbers and criteria you want. This must be a StatusFlag query so we can track when the flag became active for a person. In this script, we assume the Flag is named F55:RecentInactive, but you can use your own number and name.

    Why use a Status Flag?

    The reason a StatusFlag query is used is because they run every day and they use a special Tag behind the scenes. When a person is tagged with a Tag, the date and time they are added to the tag is stored and that is how we know when they became inactive.

    See also

    Status Flags

  2. Create an EmailInactiveToday Python script file

  3. Copy and Paste the code from this text file (it will download onto your computer) Files/RecentInactiveToday.py.

  4. Modify the Constants section at the top of the script for your church.

  5. You can manually update your Status Flags from the menu with Admin > Batch Actions > Update Status Flags

  6. Test the script by running it interactively. It will only print the list on the screen and will not email recipients unless it is run from the morning batch process.

  7. Put the following somewhere in the MorningBatch Python script:

    model.CallScript('EmailInactiveToday')
    

Explanation of the code

The top section of the code is where all the constants for your church are located. You will want to modify these. Clients may contact support for help with these settings if you need it.

 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# Constants
StatusFlag = 'F55:RecentInactive' # put in your status flag saved query
StatusFlagDescription = '''
    They have attended something 12 or more times in the past year,
    but have not attended anything in the past 60 days.
'''
Subject     = 'Classified Inactive Today'
MailToIds   = '828612,819918' # a list of peopleids of recipients (comma separated)
SenderId    = 819918 # the peopleid of the sender (may be an assistant)
FromEmail   = 'karen@tpsdb.com' # the reply to address
FromName    = 'Karen Worrell' # the person's name who the email is from
DayToRun    = 1 # 0=Sun,6=Sat. 7=run every day
  • Line 16 below creates the query which will get the list of recipients
  • Line 17 saves an indicator in CorrectDay whether the report should be emailed this day or not.
  • Line 18 determines whether the report is weekly or daily.
16
17
18
MailTo = "peopleids='{}'".format(MailToIds)
CorrectDay = model.DayOfWeek == DayToRun or DayToRun == 7
DaysToLookBack = 7 if DayToRun < 7 else 0

Note

If the DaysToLookBack variable is 7, then that means that you want to run the code on only one day a week, say on a Tuesday after all attendance has been recorded on Monday. Then, those for whom the status flag has been set on their record in the past 7 days, will be included on the report.

If the DaysToLookBack is 0, then that means you will be running the code everyday, and you will only want to report on anybody for whom the status flag has just been set on that day.

  • Line 20 below constructs the query using DaysToLookBack and your StatusFlag.
  • Then line 21 executes the query, creating a temporary tag which is saved in tagid.
  • And line 22, gets the count of people in the tag which will be used later to determine whether to email anything or not.
20
21
22
query = "RecentFlagAdded( Days={0} ) = '{1}'".format(DaysToLookBack, StatusFlag)
tagid = q.TagQueryList(query)
count = q.TagCount(tagid)

The SQL script below will be used to return a list data about the inactive people. Note two items to be replaced in this SQL string:

  • The @p1 parameter on line 44. This will use the tagid we created and will serve to filter the query to just those inactive today.
  • The --whereclause comment text on line 45. This will be used to further filter the results into two sets, married and single.
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SELECT
    la.PeopleId
    ,la.Name2
    ,la.LastAttend
    ,la.OrganizationId
    ,la.OrganizationName
    ,o.LeaderId
    ,o.LeaderName
    ,p.Age
    ,ms.Description AS MemberStatus
    ,ma.Description AS MaritalStatus
    ,la.HomePhone
    ,la.CellPhone
    ,la.EmailAddress AS Email
FROM LastAttends la
JOIN dbo.People p ON p.PeopleId = la.PeopleId
JOIN dbo.Organizations o ON o.OrganizationId = la.OrganizationId
JOIN lookup.MemberStatus ms ON ms.Id = p.MemberStatusId
JOIN lookup.MaritalStatus ma ON ma.Id = p.MaritalStatusId
JOIN TagPerson tp ON la.PeopleId = tp.PeopleId AND tp.Id = @p1
--whereclause
ORDER BY p.Age, p.Name2

Next, on lines 48 and 49, we create two different SQL statements, both using the original SQL as the starting point. The --whereclause comment is replaced with code to select just marrieds, and then just singles.

48
49
marriedSql = sql.replace('--whereclause', "WHERE ma.Description = 'Married'")
singleSql = sql.replace('--whereclause', "WHERE ma.Description <> 'Married'")

Lines 51-77 create an HTML template which will be used to render the report using {{handlebars}} as placeholders.

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
<div>
  {{#each this}}
  <p>
    <a href='{{ServerLink}}/Person2/{{PeopleId}}' target="person">
        <strong>{{Name2}}</strong>
    </a> &nbsp;&nbsp; {{Age}}, {{MaritalStatus}}, {{MemberStatus}}
    <br>
    <div style="margin-left:1em;">
        Last Attend: <b>{{LastAttend}}</b>
        <a href='{{ServerLink}}/Org/{{OrganizationId}}'>
            <em>{{OrganizationName}}</em>
        </a> &mdash;
        {{#if LeaderId}}
            <a href='{{ServerLink}}/Person2/{{LeaderId}}' target="person">
            {{LeaderName}}</a>
        {{else}}
            No Leader
        {{/if}}
        {{#if HomePhone}}<br>H {{HomePhone}}{{/if}}
        {{#if CellPhone}}<br>C {{CellPhone}}{{/if}}
        {{#if Email}}<br><a href='mailto:{{Email}}'>{{Email}}</a>{{/if}}
    </div>
  </p>
  {{/each}}
</div>

The page string creates an HTML wrapper for dividing the recent inactives into two reports, one for Single and the other for Married.

79
80
81
82
83
84
85
86
<div>
    <h2>The following have been classified Inactive today</h2>
    <p>{0}</p>
    <h3>Single</h3>
    {1}
    <h3>Married</h3>
    {2}
</div>

Next a function called ConstructEmailReport is defined.

  • Lines 90 and 91 retrieve the data for the married and single lists from the two SQL queries created earlier on line 45. They are also filtered by the tag created on line 18.

    See also

    q.QuerySql()

  • Lines 93 and 94 render the HTML reports for the Married and Single lists.

  • Line 96 combines the two reports using the page wrapper and the resulting string becomes the return value of the function.

89
90
91
92
93
94
95
96
def ConstructReport():
    marriedList = q.QuerySql(marriedSql, tagid)
    singleList = q.QuerySql(singleSql, tagid)

    Single = model.RenderTemplate(template, singleList)
    Married = model.RenderTemplate(template, marriedList)

    return page.format(StatusFlagDescription, Single, Married)
  • Line 98 prints the count which is displayed if the report is run interactively (not in batch)

  • Then if there will results to show (count > 0), The Report is constructed on line 100 and saved in body

  • Line 101 determines whether the email should be sent on line 102.

    See also

    model.Email()

  • Finally, the body of the email is printed on the screen for the interactive user.

 98
 99
100
101
102
103
print "<p>Count: {}</p>".format(count)
if count > 0:
    body = ConstructReport()
    if model.FromMorningBatch and CorrectDay:
        model.Email(MailTo, SenderId, FromEmail, FromName, Subject, body)
    print body

© 2019 TouchPoint Software, LLC.  All rights reserved.