Why Separate Your Indicator Framework and Reports in Spreadsheets?
Written and edited by Common Approach staff; summarized by Google Gemini
For many social purpose organizations, tracking impact often starts with a single spreadsheet. You define your outcomes, outputs, and indicators, and then you start entering data, perhaps in a format similar to Figure 1. Your indicator framework (the definitions of your metrics) lives alongside your indicator reports (the actual measured values over time).
Above: Figure 1
While this approach feels intuitive at first, Common Approach recommends a different strategy: keeping your indicator framework and indicator reports in separate tables (or tabs), as shown in Figure 2.
Above: Figure 2
Why this seemingly counterintuitive advice? As we’ve learned, a single combined table quickly becomes messy, error-prone, and difficult to manage.
The following is a breakdown of why separating your data is a more robust and future-proof approach.
A note on the Social Finance Fund template
​There is an intuitive way and a better way. We believe in the better way, but we also believe in meeting people where they are. For that reason, the template for the Social Finance Fund will look more like the intuitive way, with elements of the single tab structure. If it starts to get messy, it is a sign that you are ready for a different data structure. Talk to us—we’ll help you migrate your data to a better structure.
Handling evolving indicators
The table shown in Figure 1 looks clean and easy enough to understand, but for many organizations, that simplicity won’t last more than a year or two. Organizations typically refine their metrics over time as they learn and adapt. That simple single table becomes a jumble of text, numbers, and merged cells as you add new indicators, change measurement frequencies (monthly, quarterly, annually), and discontinue old indicators, as demonstrated in Figure 3. This jumble of information makes analysis a nightmare.
By separating the framework from the reports, changes to your indicators don’t break the structure of your historical data, keeping it clean and consistent.
Simplifying data analysis
Surprisingly, a single simple table severely limit your ability to perform basic data analysis in Excel or Google Sheets. Merged cells often prevent standard functions like filters and sorts from working correctly. Mixing annual, quarterly, and monthly data in the same columns (as shown in Figure 3) makes it difficult to create trendlines or aggregate totals without manual workarounds, which are prone to error.
By keeping your indicator framework separate from your indicator reports, you create a more structured, adaptable, and ultimately, more useful data system. This separation helps you avoid the common pitfalls of spreadsheet management, making your impact measurement efforts more accurate and efficient in the long run.
Managing anomalies and comments
In a single simple table, notes and explanations about specific data points (indicator reports) often get relegated to spreadsheet comments, as shown in Figure 4. These comments are hard to read, easily dismissed, and challenging to incorporate into analysis.
More like this
Steps for reviewing your ETL pipeline
Discussing the ETL pipeline, an often unaddressed step in the impact measurement process, and how reviewing and refining this pipeline can address many common hurdles & lead to efficient and reliable data.
Reflections and readiness: evolving our approach for the Social Finance Fund
Common Approach is continuing our work supporting the impact measurement approach of the Government of Canada’s Social Finance Fund. As we continue with this work, we want to share reflections on what we’ve learned and how we’re adjusting our strategy to better support the field.
Research brief: Why Social Enterprises Resist or Collectively Improve Impact Assessment
A summary of the findings of the paper, “Why Social Enterprises Resist or Collectively Improve Impact Assessment: The Role of Prior Organizational Experience and ‘Impact Lock-In’” which explores why some social enterprises are receptive and grateful when their investors tell them which impact metrics to track, while others resist investor influence over impact measurement.
