Contact Trend Widget

The Contact Trend widget displays a bar graph showing the trend of monthly contacts with breakdown by type. The types are those specified in the lookup codes for Contact Type.

The widget utilizes an HTML file, SQL script and Python script as shown below. Since the contact data displayed is the same for all users, Caching should be set to all users.

To customize the widget, make the following changes primarily to the SQL script:

  • Change the number of months to display by modifying line 2 of the SQL script. The maximum (and also the default) is twelve, but you can have fewer months displayed if desired. Subtract lines from lines 13-25 to match the new number of months you have set. Make sure the final line does not have UNION ALL at the end.
  • Specify the Contact Types to display by modifying lines 51-57, adding or subtracting lines as necessary and specifying the ContactType (which must exactly match a type in the lookup codes) and the display name at the end of the line (immediately following Type_).
  • List the Contact Types in line 60. Any contact types not in this list will be included under “Other”.
  • Adjust the HTML code to match by modifying lines 23-31 of the HTML code. If you have added, deleted or modified contact types in the SQL script, make corresponding adjustments to these lines in the HTML code.

Video

Below is a short video demonstrating some of the features of this widget.


HTML Code

Below is the HTML code for the Contact Trend widget. As supplied by TouchPoint, the name of the file is WidgetGivingChangeHistogramHTML.

 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
<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 visible-xs-block">
            <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">
            <div class="chart">
            </div>
        </div>
    </div>
</div>

 <script type="text/javascript">
      var {{WidgetId}} = function()  {
        var data = new  google.visualization.DataTable();
        data.addColumn("string", "Month");
        data.addColumn("number", "Card");
        data.addColumn("number", "Text");
        data.addColumn("number", "Phone");
        data.addColumn("number", "Email");
        data.addColumn("number", "Social");
        data.addColumn("number", "Visit");
        data.addColumn("number", "Letter");
        data.addColumn("number", "Other");

        data.addRows({{{rowdata}}});

        var options = {
            title: "Monthly Contact Trend",
            titlePosition: "none",
            legend: { position: "top", maxLines: 3 },
            bar: { groupWidth: "60%" },
            isStacked: true,
            chartArea: {width: "90%"}

        };

        var chart = new google.visualization.ColumnChart(document.querySelector("#{{WidgetId}}-section .chart"));

        chart.draw(data, options);
      }

    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback({{WidgetId}});
    WidgetCharts.{{WidgetId}} = {{WidgetId}};
</script>

SQL Script

Below is the SQL script for the Contract Trend widget. As supplied by TouchPoint, the name of the file is WidgetGivingChangeHistogramSQL.

 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
declare @dt2 DATETIME = getdate()
declare @dt1 DATETIME  = DATEADD(MONTH, -12, @dt2)  --Months to look back.  Change the 6 if a different number of months is desired (e.g. -3 for 3 month span)

--These are options to filter for a specific Ministry, ContactType or Contact Reason.
--ID#s come from tables in Admin>Lookup Codes, set to 0 if you do not want to filter
declare @min INT = 0   --Ministry ID
declare @type INT = 0  --Contact Type ID
declare @reas INT = 0  --Contact Reason ID


--Prepopulate a list of months in case there is a month with zero contacts
;with months as (
select DATEFROMPARTS (Year(@dt2), Month(@dt2), 1) m UNION ALL
select DATEADD(month, -1, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -2, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -3, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -4, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -5, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -6, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -7, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -8, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -9, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -10, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -11, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m UNION ALL
select DATEADD(month, -12, DATEFROMPARTS (Year(@dt2), Month(@dt2), 1)) m
),

contacts as
(SELECT
	t.Description ContactType
	, CASE WHEN c.ContactDate IS NOT NULL THEN DATEFROMPARTS (Year(ContactDate), Month(ContactDate), 1) ELSE 'No Date' END ContactMonthYear
FROM dbo.Contact c
left JOIN lookup.ContactType t ON c.ContactTypeId = t.Id
left JOIN lookup.ContactReason r ON c.ContactReasonId = r.Id
LEFT JOIN dbo.Ministries m ON c.MinistryId = m.MinistryId
WHERE (@dt1 IS NULL OR c.ContactDate >= @dt1)
AND (@dt2 IS NULL OR c.ContactDate <= @dt2)

--These are options to filter for a specific Ministry, ContactType or Contact Reason.
AND (@min = 0 OR @min = c.MinistryId)
AND (@type = 0 OR @type = c.ContactTypeId)
AND (@reas = 0 OR @reas = c.ContactReasonId)
)

SELECT
    FORMAT(months.m, 'MMM yy')
    --	,FORMAT(contacts.ContactMonthYear, 'MMM yy')

    --The Text (e.g. "Card Sent") for ContactType matches should be identical to those in description column under Admin>Lookup Codes>ContactType
    --Note that you do not have to have all of the types listed... the rest will be counted in "other"
    , SUM(CASE WHEN contacts.ContactType='Card Sent' THEN 1 ELSE 0 END) Type_Card
    , SUM(CASE WHEN contacts.ContactType='Text Message' THEN 1 ELSE 0 END) Type_Text
    , SUM(CASE WHEN contacts.ContactType='Phone Call' THEN 1 ELSE 0 END) Type_Phone
    , SUM(CASE WHEN contacts.ContactType='Email Sent' THEN 1 ELSE 0 END) Type_Email
    , SUM(CASE WHEN contacts.ContactType='Social Media' THEN 1 ELSE 0 END) Type_Social
    , SUM(CASE WHEN contacts.ContactType='Personal Visit' THEN 1 ELSE 0 END) Type_Visit
    , SUM(CASE WHEN contacts.ContactType='Letter Sent' THEN 1 ELSE 0 END) Type_Letter

    --Now find any contacts that do not match the rows above.  Any types listed above should be included in the Comma separated list below.
    , SUM(CASE WHEN contacts.ContactType NOT IN ('Card Sent','Phone Call','Email Sent','Text Message','Social Media','Personal Visit', 'Letter Sent')  THEN 1 ELSE 0 END) Type_Other
FROM
months
full join
contacts on months.m = contacts.ContactMonthYear
GROUP BY months.m
ORDER BY months.m

Python Script

Below is the Python script for the Contact Trend widget. As supplied by TouchPoint, the name of the file is WidgetGivingChangeHistogramPython.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
import datetime

def Get():
    sql = Data.SQLContent
    template = Data.HTMLContent
    results = q.QuerySqlJsonArray(sql)

    Data.rowdata = model.FormatJson(results)

    print model.RenderTemplate(template)

Get()


Latest Update 12/10/2020

Added this article.

© 2021 TouchPoint Software, LLC.  All rights reserved.