Querying on Cumulative Donors
Published
Finding all donors within a date range is simple enough, but how do you go about querying on donors according to what they’ve given cumulatively? This would actually require not one query, but two!
The first query we create will find all donations within that time period. Let’s say we wanted to find all donors who gave $500+ cumulatively in 2012; in that case, the first query would find all donations in 2012. The steps for that query are outlined below:
1. Click the Queries button on the menu bar
2. Select a category that you want to store this query in
3. Click New Query under Tasks
4. Name the query “All donations in 2012”
5. Set the Starting Criteria to Base/All Constituents
6. In the Data Return Type drop down menu, select Journal Entries
7. Under MATCH select All of My Criteria
8. Select Commonly Used Fields from the Available Fields drop down menu and click Journal Entry Date
9. Fill in 1/1/2012-12/31/2012
10. Select Commonly Used Fields from the Available Fields drop down menu and click Individual Transaction Received
11. Fill in Greater Than or Equal To .01
12. Click Save and Preview
We now have a query that contains all 2012 donations, but this includes donations from donors that gave below $500 cumulatively, as well as those that gave above. Now we will build a second query, using this first query as Starting Criteria, to find all donors who cumulatively gave $500+ in 2012. The Starting Criteria is another query in your database that determines the group of accounts or journal entries you are initially starting with. As a default, the Starting Criteria of any new query is set to Base>All Constituents, with “All Constituents” being a standard eTapestry query that contains a list of all constituent accounts (which means you’re starting off with a pool of all constituents).
In this second query we will use the Cumulative Received field to find donors who gave $500+ cumulatively, and since the Starting Criteria is a query that contains all donations within 2012, the cumulative totals will be calculated just from those donations. Here are the steps to create the second query:
1. Click the Queries button on the menu bar
2. Select a category that you want to store this query in
3. Click New Query under Tasks
4. Name the query “Donors who gave $500+ in 2012”
5. Set the Starting Criteria to “All donations in 2012”, the query we created in the first set of instructions
6. In the Data Return Type drop down menu, select Accounts
7. Under MATCH select All of My Criteria
8. Select Cumulative Amounts from the Available Fields drop down menu and click Cumulative Received
9. Fill in Greater Than or Equal To 500
10. Click Save and Preview
This query would be a list of donors who gave $500+ in 2012.
Oftentimes a user will attempt to query on cumulative donors all in one query, using the Journal Entry Date field and the Cumulative Received field within the same query, as pictured below:
On first glance, this query may appear to give us what we’re looking for; however, in this screenshot you can see that the Starting Criteria is set to Base>All Constituents. Since the Cumulative Received field looks to the query being used as the Starting Criteria for its calculations, this query will actually be looking for any donors who gave $500+lifetime (since the “All Constituents” query would contain all giving for each account in eTapestry), who also have a journal entry in 2012 (Contacts, Notes, Gifts, etc..). So not only will this query return more than just those that gave $500+ in 2012, it’s also going to include accounts that may not even have given within that year!
Due to the way the Cumulative Received field functions, querying on those that cumulatively gave a certain amount almost always requires two queries. The only exception to this is when you just want to query on lifetime giving, in which case no date range needs to be defined. In these instances, just a single query can be created using the Cumulative Received field (with the Starting Criteria set to Base>All Constituents).
Thank you for reading the blog; any comments or thoughts are appreciated!
The first query we create will find all donations within that time period. Let’s say we wanted to find all donors who gave $500+ cumulatively in 2012; in that case, the first query would find all donations in 2012. The steps for that query are outlined below:
1. Click the Queries button on the menu bar
2. Select a category that you want to store this query in
3. Click New Query under Tasks
4. Name the query “All donations in 2012”
5. Set the Starting Criteria to Base/All Constituents
6. In the Data Return Type drop down menu, select Journal Entries
7. Under MATCH select All of My Criteria
8. Select Commonly Used Fields from the Available Fields drop down menu and click Journal Entry Date
9. Fill in 1/1/2012-12/31/2012
10. Select Commonly Used Fields from the Available Fields drop down menu and click Individual Transaction Received
11. Fill in Greater Than or Equal To .01
12. Click Save and Preview
We now have a query that contains all 2012 donations, but this includes donations from donors that gave below $500 cumulatively, as well as those that gave above. Now we will build a second query, using this first query as Starting Criteria, to find all donors who cumulatively gave $500+ in 2012. The Starting Criteria is another query in your database that determines the group of accounts or journal entries you are initially starting with. As a default, the Starting Criteria of any new query is set to Base>All Constituents, with “All Constituents” being a standard eTapestry query that contains a list of all constituent accounts (which means you’re starting off with a pool of all constituents).
In this second query we will use the Cumulative Received field to find donors who gave $500+ cumulatively, and since the Starting Criteria is a query that contains all donations within 2012, the cumulative totals will be calculated just from those donations. Here are the steps to create the second query:
1. Click the Queries button on the menu bar
2. Select a category that you want to store this query in
3. Click New Query under Tasks
4. Name the query “Donors who gave $500+ in 2012”
5. Set the Starting Criteria to “All donations in 2012”, the query we created in the first set of instructions
6. In the Data Return Type drop down menu, select Accounts
7. Under MATCH select All of My Criteria
8. Select Cumulative Amounts from the Available Fields drop down menu and click Cumulative Received
9. Fill in Greater Than or Equal To 500
10. Click Save and Preview
This query would be a list of donors who gave $500+ in 2012.
Oftentimes a user will attempt to query on cumulative donors all in one query, using the Journal Entry Date field and the Cumulative Received field within the same query, as pictured below:
On first glance, this query may appear to give us what we’re looking for; however, in this screenshot you can see that the Starting Criteria is set to Base>All Constituents. Since the Cumulative Received field looks to the query being used as the Starting Criteria for its calculations, this query will actually be looking for any donors who gave $500+lifetime (since the “All Constituents” query would contain all giving for each account in eTapestry), who also have a journal entry in 2012 (Contacts, Notes, Gifts, etc..). So not only will this query return more than just those that gave $500+ in 2012, it’s also going to include accounts that may not even have given within that year!
Due to the way the Cumulative Received field functions, querying on those that cumulatively gave a certain amount almost always requires two queries. The only exception to this is when you just want to query on lifetime giving, in which case no date range needs to be defined. In these instances, just a single query can be created using the Cumulative Received field (with the Starting Criteria set to Base>All Constituents).
Thank you for reading the blog; any comments or thoughts are appreciated!
News
ARCHIVED | Blackbaud eTapestry® Tips and Tricks
07/29/2013 10:03am EDT
Leave a Comment