This Condition located on the Contributions category tab in Search Builder was created to help you find those in your database whose giving has changed - either increased or decreased. You can combine this with other Conditions such as Member Status, Recent Attendance Count, or Enrollment in a Financial Planning Class, in order to look at more specific groups of people.
The condition will be comparing two periods of time. The default (if you do not enter a number) is a 365-day period. However, you can enter a different number if you need to look at a different time period.
- The first period will be starting at 365 * 2 or 730 days ago through 365 days ago.
- The second period will be starting 365 days ago through today.
We highly recommend that you use a range of at least 365 days. Looking at a smaller range, you will find people whose giving has decreased, for example, because they give only once a year or quarterly.
You must have Finance role in order to return results in a search using this condition or to view the report mentioned below.
- Increase in Giving
- Enter a positive number as the percent to find increases.
- Decrease in Giving
- Enter a negative number if you are looking at decreases.
To find those whose giving has decreased by 90% or more,
you would use a Comparison of LessEqual
That will return those whose giving,
during the second period (most recent),
was 90% less than what they gave in the first period.
Said another way, their giving is now only 10% or less of what it used to be.
To find those whose giving more than doubled,
you would have a Comparison of Greater than a value of
Special Cases: No Longer Giving, or Just Started Giving
If you want to find those who used to give, but now no longer give
(their giving during the recent period is zero),
use a Comparison of Equal
-100000 (one hundred thousand).
The -100000 is a special flag to indicate this.
Conversely, to find those who have only started giving during the recent period,
use a Comparison of Equal and the special number
In this case, the special number / flag is positive.
Since these numbers are just large percentages, they will be included when using the Less than comparison or Greater than comparisons. So these special numbers are only useful when you want to isolate those with zero giving during the first or second periods.
Create a Giving Change Report¶
No doubt you will want to see the actual numbers for the individual and that is easy enough by just going to their Giving tab.
However, if you want to see a report specifically made to show this information at a glance, we have a special SQL script your System Admin can install on your database and then add to the Custom Reports.
You can name the file something other than GivingChange. However, it must exactly match the wording in both the Sql Script and in the line you copy for the Text File. In other words, if you put a space in the name of the file, making it Giving Change instead of GivingChange, you must change the Sql and the line you insert in CustomReports to include the space.
- Step 1
- Go to Administration > Special Content > Sql Scripts tab, and click New Sql Script File.
- Step 2
- Enter the name GivingChange, and click Submit
- Step 3
- Copy the following text and paste it into the editor for the script you just created.
SELECT Name, gc.PeopleId, gc.TotalPeriod1, gc.TotalPeriod2, gc.Change FROM GivingChange(365) gc JOIN dbo.People p ON p.PeopleId = gc.PeopleId JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId WHERE tp.Id = @qtagid ORDER BY gc.PctChange
- Step 4
Click Save Sql Script
If you run the script at this point, it will use your last search results to find people whose giving has changed.
- Step 5
- Now go to Administration > Special Content > Text Content, and click on the name CustomReports.
- Step 6
- Copy the following line of text and insert it at the bottom of the file just above the </CustomReports> line and then Save Changes.
<Report name="GivingChange" type="SqlReport" role="Finance" />
- Step 7
At this point, you can run the script and it will include anyone in your last search. At the bottom of the report is a link to Add Report to Menu. Click that if you want the report to display under the main Reports header.
If you run the report from the main header, it will always generate for the last search that you ran.
Run this Report¶
- Create a search which includes the Giving Change Condition, so that you can set the change percent and include in other conditions you need.
- Run the search.
- Select Blue Toolbar > Cloud > SQL Reports > GivingChange.
If you do not include the Giving Change Condition in your search, when you select the GivingReport from the menu, it will, by default use 365 days and will include everyone who was in your search results whose giving has changed.
You can also run the report from an organization or a tag, and the report will return everyone whose giving has changed - including anyone whose giving has either stopped or started.
If you prefer an Excel spreadsheet,
you can change
RunScriptExcel in the URL
and that will produce an Excel spreadsheet
using the last search that you ran for the criteria.
Only those with contributions will show up in the report results.
URL example: https://MYCHURCH.tpsdb.com/RunScriptExcel/GivingChange/ Of course, instead of MYCHURCH, you would have your church’s name as it appears in your TouchPoint URL.
If you don’t feel comfortable creating this SQL report, or have trouble with it, submit a Support Request using the ?Help link in your database and we can help you.