Spreadsheet improvements 5/9: Single Source of Truth
This is the fifth deep dive of the “9 ways to improve your spreadsheets” blog series.
This post elaborates on the importance of ensuring a single source of truth for your reporting data and insight.
Written By Séan Smith
One data source for your KPI's
Firstly, let’s clarify. A single source of truth doesn’t mean having only one system that handles everything. Although this would be the ‘Nirvana’ of scenarios, it is unlikely and impractical for most businesses. Instead, it means knowing the origin of every KPI, ensuring consistent processing, and having confidence that this process meets your business needs.
We believe that sometimes it’s better to separate your ‘working data’ from the data you distribute. Consider how your datasets will interconnect. Documenting and understanding the size of data you expect to collate along with its purpose and plans for processing is important.
For instance, if you have marketing data and sales data, depending on who you are reporting to, you may want to see a comparison of these, don’t be afraid to merge these sets into a single source of truth spreadsheet. However, if you are doing a lot of work to calculate margins it may be better to have one spreadsheet for each process, then combine the data once you completed the calculations. You can reference the calculation sheets by automating excel updates on open, but it means there is less chance of affecting the more complex sheets.
Design
Imagine how your data will fit together and try to set up data in your spreadsheets as if you were designing tables for a database. If you can store several datasets together by date, then think about combining the raw data and store something more usable. For example, for Google Analytics data the report tends to be daily, for users, new users, sessions etc. Try combining these in an easy to filter layout.
We also advice separating out either source or purpose for ease of identification and documentation processes. If a data source has issues you won’t want this impacting all the other datasets it is linked to.
Future proofing
If your data will be used over extended periods, for multiple clients or products, ensure you have simple identifiers — e.g. client/product numbers, dates, regions, etc. This will guarantee that you can use one sheet for multiple data sets, so if there are alterations or calculations to be made (such as margin calculations), they need to be made in fewer places.
Futureproofing in this way is also setting you up for success when moving toward migration to databases, as fewer data sources to transfer means less data manipulation when that time comes.
While it’s tempting to create a spreadsheet that does EVERYTHING, remember the purpose we discussed. Aim to get the most out of your spreadsheet while keeping it manageable and focused on your business needs.
Would you like to talk to us about designing a way to future-proof your data strategy? We’d like to have a chat! Feel free to contact us for a free consultation.