Recurring Giving Widget

The Recurring Giving Forecast widget displays a pie chart that shows the percent of the annual budget for the designated fund as projected to be met by recurring giving based on the current giving schedules.

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

To adjust the information for your church, you will need to make a few changes in the Python script. Modify line 1 with the ID of the desired fund, line 2 with the Name of the desired fund, and line 3 with the annual budget for the fund you are using.

Video

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


HTML Code

Below is the HTML code for the Recurring Giving Forecast widget. As supplied by TouchPoint, the file name is WidgetGivingForecastHTML.

 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
<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">{{fund}}</h4>
            <div class="chart">
            </div>
            <p class="text-center" style="margin-top:10px;">Based on annual budget of ${{FmtNumber budget}}</p>
        </div>
    </div>
</div>
<script type="text/javascript">
    var data = {{{results}}};
    var budget = {{budget}};
    var projected = Math.round(data.Combined.MonthlyAmt * 12);
    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback(function() {
    data = google.visualization.arrayToDataTable([
        ['Item', 'Dollars'],
        ['Projected Monthly Recurring', projected],
        ['Remaining Budget', budget - projected]
    ]);

    var options = {
        pieHole: 0.4,
        legend: 'none',
        chartArea: {
            left: 0,
            top: 0,
            width: '100%',
            height: '100%'
        }
    };

    var chart = new google.visualization.PieChart(document.querySelector('#{{WidgetId}}-section .chart'));
    chart.draw(data, options);
    });
</script>

SQL Script

The SQL script to use depends on whether your church is still using the legacy giving system or has migrated to the new Giving Pages.

Below is the SQL script for the Recurring Giving Forecast widget if your church uses the legacy giving system. As supplied by TouchPoint, the file name is WidgetGivingForecastSQL.

  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
with Members as (
  select tp.PeopleId, p.FamilyId
  from dbo.TagPerson tp
  join dbo.Tag t on t.Id = tp.Id
  join dbo.People p on p.PeopleId = tp.PeopleId
  where t.Name = @TagMembers
  and t.TypeId = 101 -- QueryTag
),
NonMembers as (
  select tp.PeopleId, p.FamilyId
  from dbo.TagPerson tp
  join dbo.Tag t on t.Id = tp.Id
  join dbo.People p on p.PeopleId = tp.PeopleId
  where t.Name = @TagNonMembers
  and t.TypeId = 101 -- QueryTag
),
Both as (
  select PeopleId, FamilyId, 'Member' MemberStatus
  from Members
  union
  select PeopleId, FamilyId, 'NonMember'
  from NonMembers
),
FamilyCounts as (
  select
    MemberStatus = 'Member',
    FamilyCnt = count(distinct FamilyId)
  from Members

  union all

  select
    MemberStatus = 'NonMember',
    FamilyCnt = count(distinct FamilyId)
  from NonMembers
),
RecurTable as(
    select PeopleId
    from dbo.RecurringAmounts
    where (FundID = @FundId or @FundId is null)
    and Amt > 0
),
RecurringCounts as (
  select
    MemberStatus = 'Member',
    RecurringCnt = count(distinct FamilyId)
  from Members m
  where exists(select null from RecurTable where PeopleId =  m.PeopleId)

  union all

  select
    MemberStatus = 'NonMember',
    RecurringCnt = count(distinct FamilyId)
  from NonMembers n
  where exists(select null from RecurTable where PeopleId =  n.PeopleId)
),
RecurringAmts as (
  select
    RecurringType = 'Monthly',
    b.MemberStatus,
    MonthlyAmt = isnull(sum(Amt),0)
  from dbo.RecurringAmounts a
  join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
  join Both b on b.PeopleId = mg.PeopleId
  where (a.fundid = @FundId or @FundId is null)
  and SemiEvery='E' and EveryN=1 and Period='M'
  group by b.MemberStatus

  union all

  select
    RecurringType = 'Weekly',
    b.MemberStatus,
    MonthlyAmt = 4.345 * isnull(sum(Amt),0)
  from dbo.RecurringAmounts a
  join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
  join Both b on b.PeopleId = mg.PeopleId
  where (a.fundid = @FundId or @FundId is null)
  and SemiEvery='E' and EveryN=1 and Period='W'
  group by b.MemberStatus

  union all

  select
    RecurringType = 'BiWeeky',
    b.MemberStatus,
    MonthlyAmt = 2.18 * isnull(sum(Amt),0)
  from dbo.RecurringAmounts a
  join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
  join Both b on b.PeopleId = mg.PeopleId
  where (a.fundid = @FundId or @FundId is null)
  and SemiEvery='E' and EveryN=2 and Period='W'
  group by b.MemberStatus

  union all

  select
    RecurringType = 'SemiMonthly',
    b.MemberStatus,
    MonthlyAmt = 2 * isnull(sum(Amt),0)
  from dbo.RecurringAmounts a
  join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
  join Both b on b.PeopleId = mg.PeopleId
  where (a.fundid = @FundId or @FundId is null)
  and SemiEvery='S'
  group by b.MemberStatus
),
Totals as (
  select
    MemberStatus,
    MonthlyAmt = sum(MonthlyAmt)
  from RecurringAmts
  group by MemberStatus
)
select
  RowType = isnull(t.MemberStatus, 'Combined'),
  FamilyCnt = sum(FamilyCnt),
  RecurringCnt = sum(RecurringCnt),
  MonthlyAmt = sum(MonthlyAmt)
from Totals t
join FamilyCounts c on c.MemberStatus = t.MemberStatus
join RecurringCounts r on r.MemberStatus = t.MemberStatus
group by rollup(t.MemberStatus)

Below is the SQL script for the Recurring Giving Forecast widget if your church has migrated to the new Giving Pages. As supplied by TouchPoint, the file name is WidgetGivingForecastSQL.

  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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
;with Members as (
  select tp.PeopleId, p.FamilyId
  from dbo.TagPerson tp
  join dbo.Tag t on t.Id = tp.Id
  join dbo.People p on p.PeopleId = tp.PeopleId
  where t.Name = @TagMembers
  and t.TypeId = 101 -- QueryTag
),
NonMembers as (
  select tp.PeopleId, p.FamilyId
  from dbo.TagPerson tp
  join dbo.Tag t on t.Id = tp.Id
  join dbo.People p on p.PeopleId = tp.PeopleId
  where t.Name = @TagNonMembers
  and t.TypeId = 101 -- QueryTag
),
Both as (
  select PeopleId, FamilyId, 'Member' MemberStatus
  from Members
  union
  select PeopleId, FamilyId, 'NonMember'
  from NonMembers
),
FamilyCounts as (
  select
    MemberStatus = 'Member',
    FamilyCnt = count(distinct FamilyId)
  from Members

  union all

  select
    MemberStatus = 'NonMember',
    FamilyCnt = count(distinct FamilyId)
  from NonMembers
),
RecurTable as(
    select sg.PeopleId
    from dbo.ScheduledGift sg
    join dbo.ScheduledGiftAmount a on a.ScheduledGiftId = sg.ScheduledGiftId
    where (a.FundID = @FundId or @FundId is null)
    and a.Amount > 0
),
RecurringCounts as (
  select
    MemberStatus = 'Member',
    RecurringCnt = count(distinct FamilyId)
  from Members m
  where exists(select null from RecurTable where PeopleId =  m.PeopleId)

  union all

  select
    MemberStatus = 'NonMember',
    RecurringCnt = count(distinct FamilyId)
  from NonMembers n
  where exists(select null from RecurTable where PeopleId =  n.PeopleId)
),
RecurringAmts as (
    select
        RecurringType = 'Monthly',
        b.MemberStatus,
        MonthlyAmt = isnull(sum(a.Amount),0)
    from dbo.ScheduledGiftAmount a
    join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
    join Both b on b.PeopleId = sg.PeopleId
    where (a.fundid = @FundId or @FundId is null)
    and sg.ScheduledGiftTypeId = 4
    group by b.MemberStatus

    union all

    select
        RecurringType = 'Weekly',
        b.MemberStatus,
        MonthlyAmt = 4.345 * isnull(sum(a.Amount),0)
    from dbo.ScheduledGiftAmount a
    join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
    join Both b on b.PeopleId = sg.PeopleId
    where (a.fundid = @FundId or @FundId is null)
    and sg.ScheduledGiftTypeId = 1
    group by b.MemberStatus

    union all

    select
        RecurringType = 'BiWeekly',
        b.MemberStatus,
        MonthlyAmt = 2.18 * isnull(sum(a.Amount),0)
    from dbo.ScheduledGiftAmount a
    join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
    join Both b on b.PeopleId = sg.PeopleId
    where (a.fundid = @FundId or @FundId is null)
    and sg.ScheduledGiftTypeId = 2
    group by b.MemberStatus

    union all

    select
        RecurringType = 'SemiMonthly',
        b.MemberStatus,
        MonthlyAmt = 2 * isnull(sum(a.Amount),0)
    from dbo.ScheduledGiftAmount a
    join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
    join Both b on b.PeopleId = sg.PeopleId
    where (a.fundid = @FundId or @FundId is null)
    and sg.ScheduledGiftTypeId = 3
    group by b.MemberStatus

    union all

    select
        RecurringType = 'Quarterly',
        b.MemberStatus,
        MonthlyAmt = isnull(sum(a.Amount),0) / 3
    from dbo.ScheduledGiftAmount a
    join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
    join Both b on b.PeopleId = sg.PeopleId
    where (a.fundid = @FundId or @FundId is null)
    and sg.ScheduledGiftTypeId = 5
    group by b.MemberStatus

    union all

    select
        RecurringType = 'Annually',
        b.MemberStatus,
        MonthlyAmt = isnull(sum(a.Amount),0) / 12
    from dbo.ScheduledGiftAmount a
    join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
    join Both b on b.PeopleId = sg.PeopleId
    where (a.fundid = @FundId or @FundId is null)
    and sg.ScheduledGiftTypeId = 6
    group by b.MemberStatus
),
Totals as (
  select
    MemberStatus,
    MonthlyAmt = sum(MonthlyAmt)
  from RecurringAmts
  group by MemberStatus
)
select
  RowType = isnull(t.MemberStatus, 'Combined'),
  FamilyCnt = sum(FamilyCnt),
  RecurringCnt = sum(RecurringCnt),
  MonthlyAmt = sum(MonthlyAmt)
from Totals t
join FamilyCounts c on c.MemberStatus = t.MemberStatus
join RecurringCounts r on r.MemberStatus = t.MemberStatus
group by rollup(t.MemberStatus)

Python Script

Below is the Python script for the Recurring Giving Forecast widget. As supplied by TouchPoint, the file name is WidgetGivingForecastPython.

 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
FundId = 9
FundName = 'Benevolence'
AnnualBudget = 48000  # in dollars
TagMembers = 'GivingForecast-Members'
TagNonMembers = 'GivingForecast-ActiveNonMembers'


def Get():
    sql = Data.SQLContent
    template = Data.HTMLContent
    params = {'FundId': FundId, 'TagMembers': TagMembers, 'TagNonMembers': TagNonMembers}

    model.CreateQueryTag(TagMembers,
                         'FamHasPrimAdultChurchMemb = 1 AND IncludeDeceased = 1')

    model.CreateQueryTag(TagNonMembers,
                         '''
                             ( 
                                 RecentFamilyAdultLastAttend( Days=365 ) = 1 
                                 OR IsFamilyGiver( Days=365 ) = 1
                             ) 
                             AND FamHasPrimAdultChurchMemb = 0 
                             AND IncludeDeceased = 1
                         ''')

    results = q.SqlFirstColumnRowKey(sql, params)
    Data.results = model.FormatJson(results)
    Data.fund = FundName
    Data.budget = AnnualBudget
    print model.RenderTemplate(template)


Get()


Latest Update

11/05/2020

Provided updated SQL script for church that have migrated to the new Giving Pages.