Using reports to analyze year-over-year giving and retention

In this article:

Why analyze year-over-year giving and retention?

Development offices frequently need to report retention (also known as consecutive-year giving) metrics in the form of a question such as, "How many donors from last year also gave this year, and what were the total dollar amounts given by those donors in each year?"

This article explains some ways you can get this data out of Little Green Light.

Create a query in the Constituents tab

Step 1

Run a query to get back the list of people who made a gift in the "Last fiscal" year. You can see right away that the count of donors last year was 37.

NOTE: Searching on fiscal year dates relies on the "Fiscal Year" setting in your Little Green Light subscription settings. Whatever fiscal year start date your account currently has selected will determine the gift dates that are pulled into your gift date search and any report you create. You can verify or update the fiscal year start date by navigating to Settings > Subscription settings and scrolling till you see the "Fiscal Year" setting. 

Step 2

To find out how many of these donors also gave in the current fiscal year, add an additional query by clicking the “Advanced options” link under the original search. Set the second search to  “Gift dates” equals “This fiscal” year, leaving the query operator (“and all of the following are true”) as is. 

You can see that out of the 37 people who gave last year, 6 also gave this year. NOTE: You would need to calculate that percentage on your own (for the purposes of this example, it’s 16%).

Find gift totals from this group of constituents for last year and this year

To pull the dollar totals, we’ll want to go to the Fundraising tab, where we can see summary totals of gifts. But before we head there, it will be handy to create a list of the 37 constituents we’re interested in.

Step 3

In this step, we return to our search criteria. We have the 37 constituents who made a gift last year in our search result. Next we select all 37, click Save selected, and save them as a list, which we name “Donors last fiscal (2023)”.

Query for gift data in the Fundraising tab

Step 4

To get the dollar totals for the gifts from last fiscal, run a query for last year’s gifts for the "Donors last fiscal" list of constituents. The total is $83,260. You might also want to know what open pledge amount remains from that year as well; this is 54,400.

Step 5

Now we want to re-run this query, changing the date range to "This fiscal". So, we will again use our list of 37 constituents (rather than all who gave "This fiscal") to pull our gift totals. The gift total is $1,300, and the open pledge amount is $0.

Now you can copy and paste the data from the summary rows and drop them into a spreadsheet table:

But who were those masked donors?

Now we want to know more about the individual donors we’ve been talking about. Who were the donors who gave in both years, who gave last year but not this year, and how much did they give exactly?

Step 1

Return to the Constituents tab and again pull up a search for everyone who gave last fiscal year (37 constituents). We can research them onscreen. Or, to get a table into a spreadsheet with exactly the data points we want, click the Export results button in the upper right corner of the screen:

Step 2

Now we’re in the report builder, with our search already defined. In the Customize tab, we can now select which fields we want. There are a couple of ways to go about this:

a. We can choose a pre-defined template from the list, such as the "Total Gifts by Year" template, which will give us details about giving by constituent for each of the past five years:

b. Or, we can select our own set of fields one by one. Some fields to take advantage of for year-over-year comparison are the "Total Giving (this fiscal)" and the "Total Giving (last fiscal)" fields. If you wish, you can further customize the data that will be calculated in these total giving fields (for instance, if you want to look only at gifts to an annual giving campaign).

Option 1: Pull your data together using pre-built reports

On a related note, you could also run some very useful reports that have been pre-defined in the Reports area. These reports have pre-defined both the search criteria and the customized criteria for which fields will display in the report.

To run these pre-built reports, you can go straight to the Reports area and in Constituent Reports, click Export to run the report or Edit to open it and play around with it before saving and exporting.

Option 2: Export year-over-year data to create a custom report

For a snapshot of your consecutive giving data for the past two years, with a total over three years, go to the Fundraising tab. In the View menu on that page, select the "Giving by constituent/year" option.

Your consecutive giving data for the past two fiscal years loads automatically, and you can view it onscreen.

Now you can export this data by clicking the Export results button in the upper right of the page:

You're taken to the report builder, where you can choose a report template that will allow you to view your year-over-year giving in a spreadsheet format:

To save your report, click on the Save Export button, give your report a name, and click the Save Export button in the bottom right of the screen.

Finish your analysis in Excel

To calculate changes from one year to the next, you can use Excel or another spreadsheet program. Here's a quick video on how that might look.

The formula shown in the video is "=D2-E2", which returns the difference between the total for this fiscal versus the total for last fiscal in row 2. If that number is negative, it means giving has decreased year over year. When you paste the formula, it automatically adjusts the row numbers for each cell you paste it into.

Related Articles