Donor retention is a top priority for fundraisers and eTapestry can help.

Since donors often give in cycles, your engagement with donors should be relevant and timely. To focus your fundraising efforts on retention, consider reaching out to lapsed donors around the same time they gave last year.

As long as you consistently add all new donations to your database, you can use eTapestry’s queries and reports to identify and engage donors this year based on the same time period you successfully solicited them last year.

Here are step-by-step instructions to get you started.

Step 1: Build the Query – Lapsed Donors From This Time Last Year

To isolate lapsed donors in eTapestry, set up a compound query that excludes donors who have given already in the renewal time period (donors this year) from your list of donors who gave in the prior time period (donors last year).

To do this, you will need to build three separate queries:

Query 1: Gave This Time Last Year

Your first query will identify donors that you want to renew, specifically those that gave last year at this time with a received transaction greater than $0.00.

• Select Queries
• Select Manage Queries
• Select New Query beneath a relevant query category (like “LYBUNT & SYBUNT”)
• Name the query “Gave This Time Last Year” and add a description
• Confirm the Data Return Type is Accounts
• Confirm the Starting Query is Base – All Constituents
• Add criteria for Individual Transaction Received Greater Than $0.00
• Add criteria for Journal Entry Date with a Custom Date Range that has a Start Date of the first day of this month last year and an End Date of the last day of this month last year
• If you want to look at lapsed donors for a certain Fund, Campaign, or Approach, add that criteria as well
• Save and Preview to confirm results

Query 2: Gave This Year

The second query will identify all donors that have already given during your renewal period. If you want to isolate all lapsed donors, no matter the focus of their giving, then you can use the standard LYBUNT & SYBUNT query called “Gave This Year” or “Gave This Fiscal Year” for your second query and skip this step.

But, if you want to isolate lapsed donors for a certain Fund, Campaign, or Approach or donors, you’ll need to create a custom query with the following steps:

• Navigate to the same query category in which you built the first query
• Select New Query from the left hand Task menu
• Name the query “Gave This Year” (or “Gave This Fiscal Year”) and add a description
• Confirm the Data Return Type is Accounts
• Confirm the Starting Query is Base – All Constituents
• Add criteria for Individual Transaction Received Greater Than $0.00
• Add criteria for Journal Entry Date of This Year (or This Fiscal Year)
• If you want to look at donors who have not renewed for a certain Fund, Campaign, or Approach, add that criteria as well
  Save and Preview to confirm results

Query 3: Gave This Time Last Year but Not This Year

In order to view only donors that have not yet given this year, we must exclude donors that have given this year from our list of donors that gave this time last year. As such, the third query will be a compound query.

• Navigate to the same query category in which you built the first query
• Select New Compound Query from the left hand Task menu
• Name the query “Gave This Time Last Year but Not This Year” and add a description
• Confirm the Data Return Type is Accounts
• Select the Criteria option of Subtract Query 2 from Query 1
• For Query 1, select the relevant query category and query “Gave This Time Last Year”
• For Query 2, select the relevant query category and query “Gave This Year”
• Save and Preview to confirm results

Step 2: Build the Report – Giving History with Last Transaction Details

Take action to retain your lapsed donors with a custom report that shows donor name, contact information, and giving history.

• Select Reports
• Select Manage Reports
• Select New Report beneath a new or existing relevant report category (like “Donor Reports”)
• Name the report “Giving History with Last Transaction Details” and add a description
• Add columns for account data, including name, sort name, contact information, and any key user defined fields that are important for donor segmentation or communication
• Add columns for summary transaction data, including First Received Date, First Received Amount, Last Received Date, Last Received Amount, Largest Received Date, Largest Received Amount, Year to Date Received Amount, One Year Ago Received Amount, Two Years Ago Received Amount, Three Years Ago Received Amount, Four Years Ago Received Amount, and Five Years Ago Received Amount
• Add columns for last transaction data, including Last Transaction Date, Last Transaction Received Amount, Last Transaction Pledged Amount, Last Transaction Fund, Last Transaction Campaign, and Last Transaction Approach
• Save and Run Report with the query you just built to preview and confirm accuracy of columns

You may need to select different summary fields than ones listed above if you utilize households in eTapestry or if your organization reports on a fiscal year basis. If you track your households in separate accounts linked with a household relationship, add household giving summary columns, like “HH First Received Date” instead of “First Received Date.” If you’d prefer to see five years of giving history based on fiscal year instead of calendar year, add columns for fiscal year giving summary fields, like “Fiscal Year to Date Received Amount” instead of “Year to Date Received Amount.”

Step 3: Export the Report – Contact Info and Giving History Analysis

With your queries and report built, you can now export giving history for your lapsed donor list to Excel in order to assess and take action to renew your donors.

• Select Reports
• Select Manage Reports
• Select the report category where you built your report in Step 2
• Select Run Report beneath “Giving History with Last Transaction Details”
• Select the query category where you built your compound query (Query #3) in Step 1
• Select the Query “Gave This Time Last Year But Not This Year – A”
• Select a Report Format of Excel File – Download
• Select submit to export to Excel
• Use sort and filters in Excel to analyze largest donors, donors with consistent giving history, and any other details that identify potential next steps with these lapsed donors

Tip: To isolate lapsed donors whose last gift was this time last year, sort by Last Received Date in Excel. Keep in mind that if the donor gave multiple times last year their Last Received Date may not be in the time period you set in your query. If it’s not, this just means that they gave this time last year, but that is not their most recent gift. Don’t worry, they are still lapsed donors, since they have not given this year. But if you want to follow up with only those folks who have not given in the last year, feel free to exclude these donors for now.

Use your existing data to inform retention strategies

We all know the principle that it costs less and is easier to retain an existing donor than to acquire a new one. The good news is that, as long as you are diligently entering donation data, your existing donors are all in eTapestry to begin with. The right tools and analytics can give you a leg up in your effort to retain donors year over year. Get started with the report outlined in this post, but don’t stop there.

For more on donor retention in eTapestry, check out the webinar Retaining Your Donors: Using eTapestry for Annual Giving and these articles:

• 5 Standard eTapestry Features to Manage Donor Retention
• 3 Reports to Run in eTapestry Donor Database Software
• How to Retain Your Donors with a Standard eTapestry Query