LoginMetrics for Churches with Campuses

The LoginMetrics python script produces a formatted report showing the total numbers of logins for the past 30 days by campus and by whether the login was on a mobile device or on a web browser.

http://i.tpsdb.com/loginmetrics.jpg

Sample LoginMetrics Report

Note

This article describes the python script needed by a church that has multiple campuses. If you do not use the Campus feature of TouchPoint, see LoginMetrics for Churches without Campuses.

Create the LoginMetrics Script

Step 1
Go to Administration > Setup > Special Content and select the Python Scripts tab.
Step 2
Click the green + New Python Script File button and enter the name of the file as LoginMetrics and press Submit.
Step 3
Copy all of the code below and then paste it into the new script file and press the blue Save Python Script button.
  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
from System.DateTime import Now
today = Now
MonthAgo = today.AddDays(-30).Date
strMo = str(today.Month)

strToday = today.ToString('d')
strMonthAgo = MonthAgo.ToString('d')

groups = [
    ["Total last 30 days", strMonthAgo]
]

churches = [
    ["Main", 1]
    ,["East", 2]
    ,["Inner City", 3]
    ,["Campground", 4]
    ,["South", 5]
    ,["CC", 6]
    ,["Total (last 30 days)", "1,2,3,4,5,6"]
]

appquery = '''
SELECT count(distinct p.peopleid) as Count
FROM dbo.MobileAppDevices mad
INNER JOIN dbo.People p on mad.PeopleId = p.PeopleId
WHERE mad.lastseen > '{}'
AND p.CampusId IN ({})
'''

webquery = '''
WITH loginUsers (userid) AS
(
    SELECT distinct userid
    FROM dbo.ActivityLog log
    WHERE ActivityDate > '{}'
    AND Activity like '%logged in'
)
SELECT count(distinct lu.userid) as Count
FROM loginUsers lu INNER JOIN dbo.Users u ON lu.Userid = u.UserId
INNER JOIN dbo.People p ON u.PeopleId = p.PeopleId
WHERE p.campusid IN ({})
'''

combinedquery = '''
WITH loginUsers (userid) AS
(
    SELECT distinct userid
    FROM dbo.ActivityLog log
    WHERE ActivityDate > '{}'
    AND Activity like '%logged in'
)
,webUsers (web_userid) AS
(
    SELECT lu.UserId
    FROM loginUsers lu INNER JOIN dbo.Users u ON lu.Userid = u.UserId
    INNER JOIN dbo.People p ON u.PeopleId = p.PeopleId
    WHERE p.campusid IN ({})
)
,appUsers (app_userid) AS
    (SELECT distinct userid
    FROM dbo.MobileAppDevices mad
    INNER JOIN dbo.People p on mad.PeopleId = p.PeopleId
    WHERE mad.lastseen > '{}'
    AND p.CampusId IN ({})
)
SELECT count(*) as Count
FROM webUsers FULL OUTER JOIN appUsers ON webUsers.web_userid = appUsers.app_userId
'''

class Row:
    def __init__(self, name):
        self.name = name
        self.cols = []

Data.rows = [] # create a list of rows


for church in churches:
    row = Row(church[0])
    # Mobile app logins
    rowcolquery = appquery.format(strMonthAgo, church[1])
    data = q.QuerySql(rowcolquery)
    for i in data:
        if i.Count is not None:
            row.cols.append(i.Count)
        else:
            row.cols.append("no count")
    # Web user logins
    rowcolquery = webquery.format(strMonthAgo, church[1])
    data = q.QuerySql(rowcolquery)
    for i in data:
        if i.Count is not None:
            row.cols.append(i.Count)
        else:
            row.cols.append("no count")
    # Total unique login users
    rowcolquery = combinedquery.format(strMonthAgo, church[1], strMonthAgo, church[1])
    data = q.QuerySql(rowcolquery)
    for i in data:
        if i.Count is not None:
            row.cols.append(i.Count)
        else:
            row.cols.append("no count")
    Data.rows.append(row)


Data.header = Row("Logins: last 30 days")
Data.header.cols.append("Mobile users")
Data.header.cols.append("Web users")
Data.header.cols.append("Combined (Unique)")
template = '''
<script>document.title = 'Login Metrics'</script>
<h3>Login Metrics</h3>
<table border="1" class="table" style="width: auto">
    <thead>
    <tr>
        <th>{{header.name}}</th>
        {{#each header.cols}}
            <th align="right">{{this}}</th>
        {{/each}}
    </tr>
    </thead>
    <tbody>
    {{#each rows}}
    <tr>
        <th>{{name}}</th>
        {{#each cols}}
            <td align="right">{{Fmt this "N0"}}</td>
        {{/each}}
    </tr>
    {{/each}}
    </tbody>
</table>
'''
print model.RenderTemplate(template)
Step 4

Modify the script with the campuses for your own church. Consult the list of campuses at Administration > Lookup Codes > Organizations: Campus. (Instead of “Campus” you may use a different label, as defined by the setting CampusLabel.)

Replace the numbers on line 20 ("1,2,3,4,5,6") with all the IDs for the campuses on your own database, separated by commas. Next, replace lines 14-19 with the descriptions and IDs for your campuses. You can add or remove lines, depending on whether you have more of fewer campuses than those listed in the sample code.

The default reporting period is the past 30 days. If you need to change this, modify the numbers on lines 3, 10, and 20. (Depending on changes you have already made to the script, this final line may be something other than 20. You are looking to change the number in the text ["Total (last 30 days)". For example, to modify the report to cover the past 60 days, change the 30 in these lines to 60.

Step 5
Click the Run Script button at the top of the form. Then, if you want the report available on the Reports Menu, click the link at the bottom of the report - Add Report to Menu.


Latest Update 03/05/2020

Added note directing users without campuses to companion article.

© 2020 TouchPoint Software, LLC.  All rights reserved.