Operational excellence, sometimes thought of as Best Practices*, is how you make sure that each time you collect and analyze data the results are what they should be. Robust, reliable, always. We’ll show you 3 tips that can lift your game here.
1. Use an Interface
When you’re importing data, often you don’t have much control of its format. The columns might be named funny, the data types might be different to what you need, they might change on you sometimes and so on. An interface might sound tricky but once you get the hang of it will make your life a lot easier.
Consider the raw data here we imported from a free API we like. There’s a lot of API technical data we don’t need, the column titles are not very neat and the Pokemon number is hidden in the supplied URL column.
You can try editing these directly, but it’s a lot of work. Even worse, if the API changes then all your work might be lost!
Instead we can create a new Sheet and build an interface there. To do this, create a new sheet, add in the headings you want and use the fill down feature to add the columns you want in the way that you want it. Our example is below.
In this sheet we have created our interface. We’ve tidied up the columns, referenced the columns in the raw data sheet, and extracted the Pokemon number from the URL. Now we can really start on the analysis.
2. Back Up Your Pipeline
If it’s on the computer, in the cloud, or even written down, unexpected events will happen. To prepare for this it’s important to back up your data pipeline. In the case of Apipheny this is easy. Click on Extensions > Apipheny > Configure Bulk Queries. This will give you a big blob of JSON that you can copy to a local file, back up in DropBox or wherever else will be secure. It’s a good idea to back up the sheet itself, it’s usually a good idea to remove the data from it first. This way nothing sensitive is saved in an unexpected spot and the file size stays manageable.
3. Write Tests as You Go!
After you’ve created your sheet it’s easy to assume everything will stay working. Unfortunately, there’s all sorts of unknowns – maybe someone else comes along and makes some updates, maybe six month later you forget the assumptions critical to the analysis. Tests are a great way to write down your assumptions and provide a worked example to the next person looking at your sheet.
In our example we created a new sheet with 2 tests.
We know that for each Pokemon entry they should have a number associated with it. Using a COUNTIF and NOT(BLANK()) we can check that the count of Names in our column is the same as the count of numbers.
We also know that each number should be positive. Using MIN() we can check that there are no numbers below 0.
You can use a little flourish to make the result Red or Green along with text stating directly if the test passes or fails. In this example we imagine a co-worker changed the number of Blastoise to -37593. Fortunately our test picked it up.
The other trick with tests is to write them as you are putting the spreadsheet together. It is a little extra work, but saving it all to the end makes the task a huge amount of work. Not only to you have to go back and remember all the assumptions about the data and how you processed it you miss out on the secret advantage of testing – writing the tests helps clarify the design itself! If something is easy to test, then it’s easy to reason with, to think about and means you understand it well. Any time you find yourself thinking “That’s hard to test” try breaking the problem down into smaller components. If it’s easy to test it will be easy to understand for the next person, even yourself, when the spreadsheet is opened 6 months later.
Conclusion
Using these 3 simple to understand, but deep techniques you can have robust spreadsheets. The knowledge is deep knowledge, you can apply this to a wide range of areas; any kind of spreadsheet application, even word processing or coding. Mostly though this little bit of extra work now pays dividends in the long term. How much would you pay to avoid a long night of debugging?
* Best Practices are really a subset of operational excellence. The problem is what best is will be different for every company, every person. In a competitive environment your strategy is going to be different you’re making different trade-offs. Even worse best practices today have a habit of turning into anti-patterns.
