Contact us Blog
Home » Case studies

Apsona Multi-Step Reporting shines for complex donation reporting

TJ Warfield of Data Geeks Lab is used to tough reporting problems.

But it's hard to imagine a reporting problem more complex than the one Data Geeks Lab encountered when they worked with a large wildlife conservation non-profit to fix their Salesforce installation.

Data Geeks Lab specializes in Salesforce consulting for non-profits, with a mix of clients who are either just starting out with Salesforce or who have run into problems with their existing setups. Data Geeks Lab routinely recommends Apsona's Multi-Step Reporting to help clients find and report on the information they need, especially when it's contained on several different objects.

In this case, the non-profit -- which raises money for over 30 individual conservationists who work around the world to help save endangered animals -- had built a Salesforce instance that had become unwieldy and hard to maintain. After rebuilding the system to be more in line with best practice data structures, most of the challenges came down to getting gift information back out of the system. That's where TJ and Data Geeks Lab were able to use the power of Apsona's Multi-Step Reporting tool to simplify and streamline several reporting workflows.

Donation reporting simplified

When people give money to the main organization, they specify which programs they are supporting. Sometimes people give to just one program, such as a conservationist working to save African elephants. Other times they will send one donation and divide it up among half a dozen programs.

Thanks to the Nonprofit Success Pack's allocations functionality, keeping track of incoming funds is pretty straightforward. Each donation has at least one related record on the allocations object that tracks how it is to be split up among the conservation programs.

The problem comes when you try to create an efficient process to thank donors, or when you try to send reports to each conservationist with detailed information about the donations they've received. That's because key information about the donations lives on different objects, and it's hard to bring it all back together.

Every month, the main organization sends each conservationist a report showing the funding that has been donated for their program.

In addition to providing a total of all earmarked donations, the organization also lists each donor and their current and historical donation totals. Also, since many of the donors are affiliated with zoos or other wildlife organizations, they also list the separate donations these organizations have made.

This level of detail is crucial to allow the conservationists to communicate with donors and steward donors appropriately.

Before installing Multi-Step Reporting, the organization had come up with a cumbersome way of creating this report. Basically, they created rollup fields to summarize donations for the six different time periods they wanted to report on: year to date, last year, two years ago, three years ago, four years ago, and five years plus ago.

Of course, these rollups needed to be created on both the Contact and on the Account object, because some donations were from individuals and some from companies and organizations.

And the only way to get program-specific totals for each conservation program was to create a full set of rollup fields for each and every program.

Twelve rollup fields. Thirty different conservationists.

That's 360 rollups cluttering up both the contact and account screens. Not only was this cumbersome and a resource hog, but you can't even create and maintain that many rollup fields without custom code or a special add-on that starts at about $1000 a year.

This was the situation that Data Geeks Lab inherited when TJ went to work, and she solved it with Apsona's Multi-Step Reporting.

With Multi-Step, TJ was able to scrap all those rollup fields. Instead, all the heavy lifting is done inside the different steps of the Multi-Step tool. The process relies on three crucial features of Multi-Step that distinguish it from regular Salesforce reporting: filters, metrics, and conditional logic.

To create the report, the first step specifies the particular conservation program. Then, for each donation to that program, the Multi-Step report pulls the name and contact information for the donor, conditionally looking to see whether the donor is an individual or an organization. This links the data to the right contact or account object.

Then the report marches through six steps to produce the donation totals for each of the time periods needed. Instead of using rollup fields, Apsona's date filters and metrics produce the results. Each step of the report has a date range that it's looking at, and Multi-Step's metrics functionality can calculate the sum, count, or average amount of donations, without any need to export to Excel. No custom fields are required to make it work.

For donations from individuals, the report then looks up any affiliation the individual might have with a zoo or other conservation organization. If an affiliation exists, it then produces another six columns showing gifts from this organization over the same time periods as the individual gifts.

The end result is one report instead of 30, and a streamlined process that eliminates the need to maintain and update hundreds of rollup fields. The client saves the expensive annual license cost and the in-house Salesforce administrators don't have to waste time maintaining rollup fields.

Magic thank you letters with Multi-Step Reporting

Once the monthly donation report was sorted out, Data Geeks Lab found another use for Apsona's Multi-Step Reporting. This one was even more complex.

As all non-profits know, thanking donors is the first step of securing the next gift. Not only do thank you letters need to be prompt, accurate, and contain any necessary tax language, but they should also help reassure donors that their gifts are going to be put to good use, and be signed by the right person in the organization for the level of giving.

For an organization with 30 different programs, that requires a lot of customization.

Before Data Geeks Lab got involved, the organization had a slew of different thank you letter templates. There were variations for each conservation program, differences in tax language, and different signers based on the gift amount or total household giving.

That meant there were 30+ letter templates in the system for individuals, and another 30+ for organizations. Keeping these current was such a big job that it rarely happened, leading to templates that were going stale.

With the help of Apsona's Multi-Step Reports, TJ and the Data Geeks Lab team were able to cut through all of this complexity and provide a single template that would answer every need.

Enter the magic of Multi-Step's conditional statements.

Here's how it works. For each donation, the report first determines whether the donation is from an individual or an organization. With conditional logic, it links to the right object and pulls up the appropriate primary contact name (for gifts from accounts) or formal and informal greetings (for gifts from individuals).

Then it looks up the allocation for the donation and finds the full name of the program(s). Based on this info, it's able to conditionally pull in the appropriate header images and the current blurb text for the thank you language. (As the work of each conservation program evolves, it's easy to change the language to reflect current priorities as that language lives on the program's Account record and not in the report or merge template.)

Then, based on the source of the donation, the system determines what tax language needs to be inserted. For most gifts this is standard language, but some gifts come through giving platforms that produce their own tax letters. For gifts through these sources, the tax language is omitted.

But that's not the end of the conditional text. If the donor has given over $100 in the current year, the thank you letter includes a paragraph that shows them how they will be listed in the annual report, and asks them to confirm it either with the Development Director (if the total gifts for the year are under $1,000), or with the Executive Director (if the total gifts are over $1,000).

The signature block also changes depending on donation amount, from the Development Director for gifts under $500 to the Executive Director for gifts over $500.

The final result is a single thank you letter template that can be used for every donation. Once the gift is entered properly, no further tweaks are needed. One letter instead of dozens means that changes to the basic text of the letter only need to be made in one place. And all of the bits of conditional text are collected in one place. This makes maintaining and updating the letter much easier than when there were multiple copies.

TJ admits the creation of this single letter was a complex effort and took quite a few hours. She relied on Apsona's extensive documentation for common conditional formulas, and also reached out to the folks at Apsona for help with a couple of tricky pieces of Javascript. But the time invested has paid off several times already in reduced maintenance effort.

The only way to get the data you need

TJ acknowledges that the two examples from the conservation organization, complex as they are, don't even cover all the uses she's found for the Multi-Step Reporting add-on.

For example, in Multi-Step it's easy to compare average donation amounts over arbitrary time periods, something you can't do with rollup fields. Or you can easily prepare reports showing the count of database items, whether they are donations or cases or services provided.

For Data Geeks Lab, it's an indispensable tool. TJ says, "As soon as you start needing to filter records more than one jump away from your starting object, Multi-Step is the only way to get the data you need."