Sorry to make you wait for tips six through ten, but it seemed almost fitting, given that getting reports (especially the right reports) often involves some patience. In our first five tips, we focused on work that needs to happen before you even get to asking for a report. (If you missed part one of this topic, you can begin at the beginning by clicking here.) Today we turn our attention to the important details that will ensure that a specific report will be useful to you.
- Define what you want to see.
You should be as clear as possible about what should appear on the report. Cash, commitments, response rate, donors, solicitable prospects—one might think that the definitions of most, if not all, of these terms would be straightforward. But if you move across institutions or benchmark with others, you will quickly find that there is no universal agreement on definitions. To make sure your report shows the information you want to see, take the time to define every item that will appear.
Here are some examples:
|Record Type||Value in field “record_type”|
|Preferred City||Value in field “city” on the row where preferred_address_indicator = “Y”|
|Commitments||Sum of values in field “gift amount” on rows where gift_type_code is equal to “OG” (outright gift) OR “PL” (pledge) OR “MG” (matching gift)|
(using “CY” for “current year” instead of the specific year allows the definition and reports to be reused)
|Sum of values of field “gift amount” where (gift_type_code is equal to “OG” (outright gift) OR “PP” (pledge payment) OR “MG” (matching gift)) AND fiscal year of gift_date equals the current fiscal year|
Don’t worry if you aren’t sure how to write your definitions up in a way that will be clear to your programming partner. Because you are working together, your partner will help translate what you tell them into programmer-friendly definitions. While this may seem like a lot of work, it’s less work than having to figure out why a report isn’t showing what you thought it would! Also, remember that you will usually only define a particular term once, and then keep reusing it. It is very helpful to keep the definitions consistent across reports and over time.
- Answer additional key questions—Who? When? How?
You have determined what you’d like to see on the report, but other key questions remain. The first is “who” should be included in or excluded from the report. Be as specific as possible, e.g., donors who made at least one gift in the past five fiscal years, excluding those who have given in the current fiscal year. “When” asks you to define the timeframe to be covered by the report. Do you want to see counts of event attendees for the past month, past quarter, or past year? For what periods would you like to see comparative data?
Finally, you need to articulate “how” the data should be presented. Often this means specifying a sort order (e.g., alphabetical, by fiscal year, in descending order (largest to smallest) by most recent gift amount). Some reports may require you to define a hierarchy, a rule or rules that decide which value takes precedence if more than one applies. For example, a constituent hierarchy would spell out whether someone’s data should be counted in the “parent” figures or the “faculty/staff” figures when both are true of that individual and constituency is used to group data on the report. Or maybe you want to categorize people based on how they’ve connected with you philanthropically—you’ll need to spell out where someone who was an event sponsor and also made an outright gift in response to a direct mail piece should appear.
- Provide a report sample or mock-up.
If you found a report that you really like at a conference or through a colleague at another institution, get a copy of that report and share it with your programming colleague. If you have your own ideas about how the report should look, you should make a mock-up. I like using spreadsheets to make mine, because they already have a built-in column and row structure.
This process of laying out what you would like to see helps to raise other important questions. Do you want to see subtotals and, if so, based on what groupings? Do you want to see grand totals? Are there any calculations you need to see, such as average gift or percent change vs. last year? You may have defined these in step 6 as part of “what” you want to see, but this serves as another check to make sure you’ve spelled out the computerized calculations that will convert the data into more readily actionable information.
- Share responsibility for quality control.
The partnership between you and your programming colleague continues through the quality control process. One of the best things you can do is provide as many details as you can up front about what you are expecting to see. For example,
- Is there an existing report that the new report should tie out to?
- Are there specific individuals, groups, appeals, etc. that you are expecting to see on the report? Who or what are they?
- Do you have an idea of the range of the number of records that will be found or totals you are expecting to see?
If you are looking to see the number of recognition society members in a region, and you know that, on average across all regions, 15% of your donors are members of the recognition society, you should let the programmer know that a result that represents about 15% of the region’s donor population is what you expect to see. This will make it easier for your partner, who isn’t the end-user of the data, to assess the accuracy of the report.
Once you get a copy of the report, check it yourself! Does it tie out to other reports as expected? Does it include or exclude the people, classes, gifts, appeals, etc., as you expected? Does it make sense based on your understanding of your data, prospect pool, historical results, etc.? If the report includes individual names, transactions, etc., choose five to ten records at random and look up the details in the database. Do the records selected meet your “what,” “who,” and “when” criteria? Be as specific as you can about any discrepancies you find between what you wanted to see and what the report is showing when you share the information with your programming partner.
- Give feedback right away.
Programmers hear from us when something goes wrong, but we should also be giving positive feedback. A great way to thank them for their efforts is to share how the new report helped your team save time, recruit more volunteers, design the next appeal, or raise more money.
Even after receiving what you believed was the final version of the report, you may still discover that there is an issue. If you do, please alert your programming partner right away. The longer you wait, the harder it will be for them to pick the report up and jump back in. Also, waiting could allow the issue to spread to other reports being programmed as definitions are reused and other reports are programmed to tie out to the one in question.
In the immortal words of the Rolling Stones, “You can’t always get what you want, but, if you try sometimes, you just might find, you get what you need.” Following these ten tips—in partnership with a programming expert—will give you the best chance to “get what you need.”
Tammie L. Ruda