Impact Evaluation for Non-Profits with Drawloop
Measuring the impact of a non-profit is critical to both its growth and constituent base. This generally takes the form of an “annual report” style document, including a combination of large blocks of text and compelling charts. Salesforce dashboards and reports with charts offer great functionality, but at times fall short; the formatting options available may not allow us to change how a bar chart looks, and it’s not feasible to deliver dashboards to non-Salesforce users.
Instead of usual Arkus blog posts, we’re going to take this hypothetical journey as a non-profit, and talk about the organization’s needs for stylized impact dashboarding with social outreach programs and fundraising that relies heavily upon corporate and foundational gifts.
Our Salesforce instance has grown over the past few years with the Non-Profit Starter Pack to track our contacts, donations, and events, and customizations to track our causes, affiliated people, attributed donations, and survey results using one of the many survey tools available on the AppExchange. We’ve reached a point where our board and constituents have expressed interest in understanding metrics about our program growth and demographics, fundraising performance and variance from targets, and metrics about our organization. Some of our board members do not have Salesforce licenses, and require that the finished product is styled and “on-brand”, including logos, colors, and typefaces.
Data Before Design
The first step on this journey is to understand the objective of our impact evaluation report, and what data we need to display. Our board and core team wish to highlight metrics about where the organization is today, comparing data over time, and the breakdown of donors and causes. By listing these out in the table below, we’re able to better understand how data populates our dashboards, and how to visualize this in our final document.
| Reporting Need | Ways to Visualize | Where the Data Exists |
|---|---|---|
| Donations vs Target Funds Raised by Quarter | Bar chart with line graph for targets | Donations object, Reporting object |
| Average Organization Size by Annual Revenue Brackets | Pie / donut chart | Organizations object |
| Age Ranges of Volunteers | Bar chart | Contact object |
| Average Age of Volunteers | Metric | Contact object |
| % of Satisfied Volunteers (based on survey results) | Metric | Survey Results object |
| % of Volunteers Willing to Support Next Year (based on survey results) | Metric, pie / donut chart | Survey Results object |
| # of Full-Time Staff | Metric, line graph | Reporting object |
| Annual Budget | Metric, line graph | Reporting object |
All of these data points can be retrieved and displayed in a Salesforce dashboard based on our objects described above with the exception of the last two; not all of our staff are users of Salesforce, and we don’t handle any granular budgeting in Salesforce. To have the “# of Full-Time Staff” and “Annual Budget” appear in our report, we can either type them directly into the template, or store them in a new custom object within Salesforce. We’ve decided to call this our “Reporting” object, and it has a few fields that we’ll fill out every quarter to show the change of high level numbers over time. This structure keeps it dynamic, and prevents us from embedding hardcoded numbers into our template.
Building Reports & Templates
Now that we feel comfortable with our data, we can start to build the Salesforce reports and template to support our finished document built with Drawloop. To keep things simple, we can use a single report per object and handle any filtering and calculations with Drawloop’s Excel as Middleware feature, and in some cases this is required as Salesforce reports include null values for their averages (which will skew native dashboards). This feature unlocks the power that Excel offers, such as COUNTIF and SUMIFS, and even PivotTables; data from each Salesforce report will appear as a tab in an Excel file, and formulas on a separate tab can be used to handle any logic and calculations.
Here are a few benefits to offloading the logic into the Excel as Middleware feature:
-
Using “complex” formulas that are not supported by Salesforce, or would cause report proliferation
-
Defining temporary variables and segments that are used only in the context of reporting (e.g. government classifications vs internal tiers to define a program)
-
Data can be massaged or reformatted without incurring the technical debt of having Salesforce formula fields
A downside to consider is that we are baking logic into a single Excel workbook, which requires familiarity with how the data is being manipulated and how Excel formulas work, as they can range from simple to utterly complex. It is important to keep the source reports static as well, as any changes made to the filters could potentially throw off the produced dashboard (garbage in, garbage out).
Our summarized data in Excel becomes a series of “named ranges”, where a label is associated with one or many cells. This label becomes the bridge between a single datapoint in our Word template (with mail merge notation <<MyRangeName>>), or associated to a chart within the document. The rest of the logic is behind the scenes, as Drawloop pushes the Salesforce reports data into the Excel sheets, and then uses whatever ranges we’ve defined to populate our Word template.
Polishing & Delivery
Running the report a few times and comparing the data is the last step of the process before the finished document is ready for the spotlight. It’s best to go back to the Salesforce reports data, check the Excel sheets used by the “middleware” feature, and ensure that the numbers pass a sniff test (or are aligned with internal reporting already in place). The last step is to define how the document is sent out, whether ad hoc or on a scheduled basis, and the recipients. Recipients are not limited to Salesforce users, and the documents can be triggered on button click if you wanted to further customize the reporting process to reflect the current user’s details (e.g. name, title, etc).
We’ve left out the technical details for how to create each element in this journey, largely because the process is tailored to the specific use cases of an organization and its reporting needs. By leveraging a third-party app to handle the composition and distribution of your impact reporting, it’s possible to spend less time focusing on key reporting windows and re-focus that time on core activities within the organization.
Are you starting to think about impact reporting from Salesforce, or have you been leveraging the platform for your impact evaluation dashboards and decks already? Feel free to leave your questions and comments below, on our Facebook page, or via Twitter @RogerMitchell.
