Giving Change Widget

The Giving Change histogram widget displays donors who have stopped, decreased, increased or started giving to the specified fund. The data, based on the fund ID specified, is segmented into giving change percentage ranges. A minimum giving amount threshold and percentage of change are also configurable. The default code compares the previous 365 days to the prior 365-day period.

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

To customize the widget, make the following modifications to the SQL script:

  • Set the fund ID by updating line 2.

  • Set the minimum percentage of change shown by updating line 3. (Do not include a percent sign.)

  • Set the minimum giving amount to include on the graph by changing line 4.

  • If desired, change the time period for comparison by modifying line 5.

Video

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


HTML Code

Below is the HTML code for the Giving Change 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
<div class="box">
    <div class="box-title hidden-xs">
        <h5>{{WidgetName}}</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", "%Change");
    data.addColumn("number", "Count");
    data.addColumn({"type": "string", "role": "tooltip", "p": {"html": false}});
    data.addColumn("number", "Count2");
    data.addColumn({"type": "string", "role": "tooltip", "p": {"html": false}});

    data.addRows({{{rowdata}}});
    var options = {
        title: "Giving Change Histogram",
        titlePosition: "none",
        isStacked: true,
        legend: { position: "none", maxLines: 3 },
        chartArea: {width: "60%"},
        vAxis: {title: "Percentage Change", textPosition: "out"},
        hAxis: {title: "Number of Giving Units"},
        colors: ["red", "green", "blue"],

        };
    var chart = new google.visualization.BarChart(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 Giving Change 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
--Roles=Finance
DECLARE @fundid INT = 1     -- put your fundid here
DECLARE @MinumumPctChange NUMERIC = 0; -- Allows you to filter out those with only limited change in giving, Set to 0 for no filtering
DECLARE @MinimumTotal NUMERIC = 250;  -- Minimum giving amount,  set at $250 to only graph "Core Givers"
DECLARE @PeriodDays INT = 365;  -- Look back period ... 365 for 1 year period

SELECT
    PctChange

FROM dbo.GivingChangeFund(@PeriodDays, @fundid) gc
JOIN dbo.People p ON p.PeopleId = gc.PeopleId
WHERE   gc.TotalPeriod1 + gc.TotalPeriod2 >= @MinimumTotal
        AND ABS(gc.PctChange) > @MinumumPctChange
ORDER BY gc.PctChange, ( gc.TotalPeriod2 - gc.TotalPeriod1 )

Python Script

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

 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
import json
from datetime import datetime

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

    list = q.QuerySqlJsonArray(sql)
    list2 = json.loads(list)

    # This section initializes an array to hold the Data that will be charted
    # [Bin Description, Negative Change count, Negative Change tooltips, Positive Change count, Positive Change tooltips]
    rows, cols = (6, 5)
    bincounts = [[0 for i in range(cols)] for j in range(rows)]

    bincounts[0][0] = "Stopped"
    bincounts[1][0] = "-100% to -10%"
    bincounts[2][0] = "-10% to 0"
    bincounts[3][0] = "0 to 10%"
    bincounts[4][0] = "10% to 100%"
    bincounts[5][0] = "Started"

    bincounts[0][2] = " "
    bincounts[1][2] = " "
    bincounts[2][2] = " "
    bincounts[3][2] = " "
    bincounts[4][2] = " "
    bincounts[5][2] = " "

    bincounts[0][4] = " "
    bincounts[1][4] = " "
    bincounts[2][4] = " "
    bincounts[3][4] = " "
    bincounts[4][4] = " "
    bincounts[5][4] = " "

    # Iterate through the list and put into appropriate countbins
    for i in list2:
        if i[0] < -100:
            bincounts[0][1] = bincounts[0][1] - 1
            bincounts[0][2] = str(-1 * bincounts[0][1]) + " Giving Units stopped giving this past year"

        elif i[0] < -10:
            bincounts[1][1] = bincounts[1][1] - 1
            bincounts[1][2] = str(-1 * bincounts[1][1]) + " Giving Units Decreased their giving by " + bincounts[1][
                0] + " this past year"

        elif i[0] < 0:
            bincounts[2][1] = bincounts[2][1] - 1
            bincounts[2][2] = str(-1 * bincounts[2][1]) + " Giving Units Decreased their giving by " + bincounts[2][
                0] + " this past year"

        elif i[0] < 10:
            bincounts[3][3] = bincounts[3][3] + 1
            bincounts[3][4] = str(bincounts[3][3]) + " Giving Units increased their giving by " + bincounts[3][
                0] + " this past year"

        elif i[0] < 100:
            bincounts[4][3] = bincounts[4][3] + 1
            bincounts[4][4] = str(bincounts[4][3]) + " Giving Units increased their giving by " + bincounts[4][
                0] + " this past year"

        elif i[0] >= 100:
            bincounts[5][3] = bincounts[5][3] + 1
            bincounts[5][4] = str(bincounts[5][3]) + " Giving Units Started giving this past year"

    Data.rowdata = json.dumps(bincounts)  # format into JSON

    print model.RenderTemplate(template)

Get()


Latest Update

12/10/2020

Added this article.