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 of the ways you can get this data out of Little Green Light.

Use a dashboard widget to view retention data for all donors

If you want to see year-over-year giving for all of your donors, go to your dashboard and find the “Donor Giving Compared to Prior year” widget. Use the different options in the widget to see whose giving increased, stayed the same, or decreased, and also who didn’t give.

To load the search results and then export the data, select the views you want to use and click the graphic representing the totals for that data. You’ll then be able to click the Export results button at the top right of the Advanced Search area to begin creating a report.

Use a query in the Constituents tab to view retention data for a subset of constituents

If you want to see retention data on who among a subset of your donors gave year over year, you can use a custom constituent search and then export that data to create a report. You can use the year-over-year difference calculations using Excel formulas, or formulas in another spreadsheet program, in the resulting spreadsheet.

Follow the steps below to work through this process.

Step 1

To get started, 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, in our case, 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 Advanced options under the original search criteria. Set the second search criteria 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 can calculate the retention rate percentage on your own by dividing the number of constituents who gave both years by the number of constituents who gave last fiscal year (for the purposes of this example, it’s 16%).

Find gift totals from this group of donors 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

Returning 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:

So which of the donors gave year over year?

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 Advanced Search area of the screen:

Step 2

Now we’re in the report builder, with our search already defined. In the Customize tab, we can 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 prefer, 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).

Additional options for looking at year-over-year giving and retention

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

You can 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, navigate to the Reports area of your account. 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 constituent giving 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 showing 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