nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Generating annual total giving by giving range

Patrick Craine

Hi all,

I'm new to Raiser's Edge. I want to figure out how to determine the total dollars raised in a particular giving range during a particular year. Such as “We received XX dollars from those in giving range $500-999 in FY 2022-2023.”

Any suggestions on how to run this?

Thanks!

Find more posts tagged with

Comments

Karen Diener

@Patrick Craine:
Hi all,
I'm new to Raiser's Edge. I want to figure out how to determine the total dollars raised in a particular giving range during a particular year. Such as “We received XX dollars from those in giving range $500-999 in FY 2022-2023.”
Any suggestions on how to run this?
Thanks!

@Patrick Craine while there is a standard report (the Donor Category report) that you could possibly use to generate this, I prefer query and export. The reason I don't like the donor category report is that the output is PDF and I have always needed data in a different format for publication so it does not save me any time at all to use this report.

Set up a Constituent Query for anyone who gave at least $1 during the fiscal year using the Summary Information | Summary for Gift criteria. Be sure to select the gift types you want included in that summary, because if you do not, Raiser's Edge will apply a default set which might not be what you want. Also include / exclude gifts to specific Campaigns, Funds or Appeals as needed, and determine whether or not you will need to include soft credits.

Once you feel your query logic is sound, set up an Export to generate a list with the final details you need - probably include Head of Household only, and then include / exclude Deceased, No Valid Address, and Inactive as needed. Include constituent ID, whatever name fields you need, and Summary Information | Summary Gift Amount.

Once this is exported to Excel, sort by the total giving and apply levels like your “$500-999” donors. I always use a letter in front of these, like “A: $1m+”, “B: $500,000 - $999,999”, etc. After you have assigned a giving level to everyone you can do a pivot report to show how many donors in each level, and how much they gave.

It feels like a lot of steps but if this is something you will do regularly, you can re-use the queries and exports by updating dates. More than anything though, if you are publicizing a list of donors for any public purpose, this allows you ample opportunity to carefully scrutinize data, which is a critical component to generating this kind of list. In my opinion, this should NOT be a push-button type of output that does not get careful review.

Alex Wong

@Patrick Craine
Karen's export and excel method is good, better than the canned report option.

Other options:

Export from query (or Export module depending on how you want to count write off) and then use Excel formula instead of pivot table
- I used like this option better as I can easily re-export with new data from RE saved query/export and just “paste over” the raw data in the excel file. The formula for the report will auto-calculate and no additional work needed.

Raw Data table:

Donor ID	2024 Giving
1	$ 8,270.00
2	$ 1,860.00
3	$ 6,390.00
4	$ 6,191.00
5	$ 3,544.00
6	$ 7,265.00
7	$ 1,466.00
8	$ 5,426.00
9	$ 2,879.00
10	$ 555.00

Summary Table:

Donor Level	Min (include)	Max (exclude)	Count	Amount
Under $2000	$ 0.01	$ 2,000.00	3	$ 3,881.00
$2K+ Under $5K	$ 2,000.00	$ 5,000.00	2	$ 6,423.00
$5K+	$ 5,000.00	$ 8,271.00	5	$ 33,542.00

Once Summary Table is done, you can update Donor Level (name of the level) Min, Max as needed whenever you want. You can hide the Min/Max column so it isn't “shown”.

The 0.01 is “typed in” like all other min max amount, with the exception that the last (biggest level) is using a formula for =MAX(RawData[2024 Giving])+1 since Max is “exclude”, so we add a ONE to allow for that.

Count formula: =COUNTIFS(RawData[2024 Giving], ">="&[@[Min (include)]], RawData[2024 Giving], "<"&[@[Max (exclude)]])

Sum formula: =SUMIFS(RawData[2024 Giving], RawData[2024 Giving], ">="&[@[Min (include)]], RawData[2024 Giving], "<"&[@[Max (exclude)]])

Use Power Query, too much details to share, but this option is nice if you know power query
Use Power BI, the best option, which is what I use now, allows for easy build out of visualization that can easily be refreshed

Christine Robertson

@Karen Diener @Patrick Craine For those who have the Standard Reports in web view, Donor Category Report is available there and easily exports to .csv.

Alex Wong

@Christine Robertson
You are right, standard reports on webview is coming, for now only available to some selected orgs.

CSV option is good and for summary data. but if you want details, it is same “issue” as dbview canned report, you can't really output additional columns of data if needed. And then there is “Constituent Name” column, RE ID important, but isn't “consistently” produced, for Individual, you can output the Constituetn ID only, for Organization, you are stuck with name of org appended with Constituent ID which then you have to “extract”. I don't know if there is a direct option to only export the RE ID so it can be used in Query export such that additional info can be XLOOKUP into the detail report.

Dariel Dixon

@Patrick Craine You're been told the right way to handle this issue by @Karen Diener and @Alex Wong. However, those methods are a bit more advanced for someone who considers themselves a newbie. I think in this case, it might be worth looking at the Donor Category Report and just reporting on the numbers iff you think that would be enough. I would run it in webview like @Christine Robertson stated if you have access to them, but they are also available in DBV as a .PDF.

Donna Oleniczak

@Karen Diener @Patrick Craine, If you export the Donor Category Report you can select your export format - excel, csv etc.

Austen Brown

@Dariel Dixon - The WV Standard Reports section can export both CSV, PDF or both. No need to run this in DBV just to get a PDF output.

Darlene LeVielle

@Alex Wong Like Excel version here best, thanks. Quick question on BB RE approach on a related scenario:
Given 10 fiscal years and 14 distinct gift amount bins, each requiring aggregation on two metrics (gift count and total revenue), what's the most efficient architecture for pulling these aggregates at scale?

Specifically:

Parameterizing and looping over FY and gift size in a single query pipeline via some programmatic ETL, Power BI, etc.?

If above is not an option for an Org:

Or, in practice at very large orgs, handle by executing 10 FY discrete or all 10 at once, Gift type exports or queries then exporting to csv then use the excel template table noted here against very large raw datasets

Or using Which Specific canned Report - manually defining the 10 different fiscal year ranges and using Gift Size table to define bin amounts needed, then exporting Report results to csv not PDF?

Curious what might be considered the most scalable and maintainable way to structure this.

Alex Wong

@Darlene LeVielle
When “Standard Reports” (canned report) becomes available, (it is in preview for some customer, which I am one of them), it can better export to csv and would likely be your best option.

before that, a constituent query with 10 summary of gift amount field/column for each of the 10 FY will be best option in my opinion in excel. (sample 3 FY below)

Donor ID	2024 Giving	2023 Giving	2022 Giving
1	$ 8,270.00	$ 11,056.82	$ 11,853.59
2	$ 1,860.00	$ 3,385.80	$ 2,307.64
3	$ 6,390.00	$ 3,041.92	$ 5,534.02
4	$ 6,191.00	$ 10,796.94	$ 6,572.35
5	$ 3,544.00	$ 5,796.41	$ 5,491.77
6	$ 7,265.00	$ 5,575.57	$ 1,667.34
7	$ 1,466.00	$ 93.91	$ 145.55
8	$ 5,426.00	$ 5,669.85	$ 8,755.73
9	$ 2,879.00	$ 3,696.38	$ 4,575.11
10	$ 555.00	$ 556.53	$ 816.92

you can setup the Summary Table with the 14 gift amount bins in the rows, and then calculate the count and amount for each year. Once it is setup, updating simply means re-export and replace the above raw data file.

			2024		2023
Donor Level	Min (include)	Max (exclude)	Count	Amount	Count	Amount
Under $2000	$0.01	$2,000.00
$2K+ Under $5K	$2,000.00	$5,000.00
$5K+	$5,000.00	$8,271.00

Darlene LeVielle

Thanks @Alex Wong, this is very helpful! The chart that is needed to be filled in is about the total Fund split amt $'s and distinct gift count by fund split amount gift size bins. I think using a gift type query would work here. Thanks!

Alex Wong

@Darlene LeVielle
maybe i'm not understanding your reporting purpose, and where “fund split amount” comes into play here. I thought you were trying to get donor category (how much donor give in a given period of time and put their giving into “bucket” of giving threshold).

If somehow what fund is given to matters to your report, then you will be better off with a gift query where you output fund and fund split amount and this amount is used within your formula.

THough depending on how many gift records are in your database for the last 10 fiscal years, Excel may not be your option (1M records row is the max) and even if you don't hit the max, few hundred thousands records row is going to need a very powerful CPU on your computer.

You will have better luck using Power Query/Power BI

Darlene LeVielle

Thanks @Alex Wong,
That’s right. Two measures:

Distinct gift count

Total fund split amount, aggregated by Gift Size Bin.

Will use Salesforce Tableau Public Desktop (free version) to union the datasets and build the visuals. Our IT environment isn’t ready yet for backend integration using SKYAPI, Power Automate, or similar.

I know Power Query or Power BI would also work especially since we’re starting from .csv exports in Excel and might take fewer steps.

Appreciate the input.

Alex Wong

@Darlene LeVielle
I'm still not sure why you need to use “fund split amount”.

if aggregating by constituent (you are putting how much a constituent give within a period of time into gift size bin), then there is no need to aggregate by fund split amount, you only need to aggregate by gift amount.

Reason why you want to avoid “fund split amount” in the data table and for aggregation is due to complexity that can be avoided. Since “fund split amount” will make the data table to possbiliy have dup rows for the same gift but different fund split, trying to get a distinct count will require you to do more advanced excel formula: dynamic array.

this post will not go into how you can do dynamic array formula, if you interested, there are planty of tutorial that can teach you.

Instead my suggestion will change (if fund split amount is needed for purpose of reporting also on fund split related info) to using Power Query, Power BI or Tableau directly at this point, instead of using excel.

Darlene LeVielle

Hi @Alex Wong, I agree. For this, I will need to aggregate by gift. I won't need results by constituent. Thanks!