Login Metrics Widget

The Login Metrics widget displays a bar chart showing number of unique logins for web, mobile, and total over the past 60 days. (The default of 60 days can be changed on line 3 of the Python script.) By unique logins, we mean that any particular user is only counted once in each category. For example, if a certain user had 14 web logins and 8 mobile logins in the past 60 days, that user would be counted only once in each of the categories (web, mobile, and total).

Note

The required data for mobile logins is only available for the custom mobile app and not for the free mobile app. So if your church has people using the free app, this widget will not show accurate results for mobile logins.

The widget utilizes an HTML file and a Python script as shown below. (No SQL script is needed.) Since the information in this widget is the same for all users, Caching should be set to all users.

HTML Code

Below is the HTML code for the Login Metrics widget. As supplied by TouchPoint, the file name is WidgetLoginMetricsHTML.

 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
<div class="box">
    <div class="box-title hidden-xs">
        <h5><a href="#">{{WidgetName}}</a></h5>
    </div>
    <a class="visible-xs-block" id="giving-fc-collapse" data-toggle="collapse" href="#{{WidgetId}}-section" aria-expanded="true" aria-controls="{{WidgetId}}-section">
        <div class="box-title">
            <h5>
                <i class="fa fa-chevron-circle-right"></i>&nbsp;&nbsp;{{WidgetName}}
            </h5>
        </div>
    </a>
    <div class="collapse in" id="{{WidgetId}}-section">
        <div class="box-content center">
            <h4 class="text-center">Login Metrics - Last {{days}} days</h4>
            <div class="chart">
            </div>
        </div>
    </div>
</div>
<script type="text/javascript">
    var data = {{{counts}}};
    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback(function() {
        data = google.visualization.arrayToDataTable([
            ['Item', 'Logins'],
            ['Web', {{counts.web}}],
            ['Mobile', {{counts.mobile}}],
            ['Total', {{counts.total}}]
        ]);
        var options = {
            colors: ['#9575cd'],
            vAxis: {
                title: 'Unique logins'
            },
            legend: {
                position: 'none'
            }
        };
        var chart = new google.visualization.ColumnChart(document.querySelector('#{{WidgetId}}-section .chart'));
        chart.draw(data, options);
    });
</script>

Python Script

Below is the Python script for the Login Metrics widget. As supplied by TouchPoint, the file name is WidgetLoginMetricsPython.

 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
from System.DateTime import Now

Days = 60  # Report on logins for the past # of days entered here

def Get():
    today = Now
    StartDate = today.AddDays(-Days).Date
    strToday = today.ToString('d')
    strStartDate = StartDate.ToString('d')
    template = Data.HTMLContent

    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 > '{}'
    '''

    webquery = '''
    WITH loginUsers (userid) AS
    (
        select UserId from Users where Username in (
    	select distinct REPLACE(REPLACE(Activity, ' logged in', ''), 'User ', '') as Username from dbo.ActivityLog al
            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
    '''

    combinedquery = '''
    WITH loginUsers (userid) AS
    (
        select UserId from Users where Username in (
    	select distinct REPLACE(REPLACE(Activity, ' logged in', ''), 'User ', '') as Username from dbo.ActivityLog al
            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
    )
    ,appUsers (app_userid) AS
        (SELECT distinct userid
        FROM dbo.MobileAppDevices mad
        INNER JOIN dbo.People p on mad.PeopleId = p.PeopleId
        WHERE mad.lastseen > '{}'
    )
    SELECT count(*) as Count
    FROM webUsers FULL OUTER JOIN appUsers ON webUsers.web_userid = appUsers.app_userId
    '''

    counts = {}

    # Mobile app logins
    rowcolquery = appquery.format(strStartDate)
    data = q.QuerySql(rowcolquery)
    for i in data:
        counts["mobile"] = int(i.Count or 0)

    # Web user logins
    rowcolquery = webquery.format(strStartDate)
    data = q.QuerySql(rowcolquery)
    for i in data:
        counts["web"] = int(i.Count or 0)

    # Total unique login users
    rowcolquery = combinedquery.format(strStartDate, strStartDate)
    data = q.QuerySql(rowcolquery)

    for i in data:
        counts["total"] = int(i.Count or 0)

    Data.days = Days
    Data.counts = model.DynamicData(counts)
    print model.RenderTemplate(template)

Get()


Latest Update 04/30/2020

Added this article.