The evolution of data monitoring changed dramatically over the past 10 years. Just as I designed my first research study, the Apple Watch came out, changing what we know about the possibilities of endless data collection for fitness behavior change.
(Side bar: My master’s thesis — Decreasing Bouts of Prolonged Sitting — required participants to wear a vibrating watch, months before Apple and Fitbit changed the smart wearable market forever)
The ease of data collection improved, yet a challenge remained, that of readily visualizing what I needed as a coach for my clients, or even what wanted to evaluate on my own athletic performance.
The problem: You are always stuck with what the data displays that come out-of-the-box.
The apps are great, and I could never design them, but every so often what you require as an athlete or coach requires more — creating custom charts, or playing with the data yourself.
Landing On A Process
Process 1.0
Like many data nerds out there, I started collecting data and graphing fitness behaviors in Microsoft Excel. It’s a great tool which gets the job done. However, I found myself at a point where I spent too much time processing the data and recreating graphs and less time on figuring out how to help my clients, or see my fitness patterns.
My process was: Clients enter data in Microsoft Excel, and I created graphs in Excel.
For anyone who works in Excel daily, unless you have a template set up, creating graphs in Excel can be quite the pain.
From the beginning of my research career (circa 2013) until 2019 I operated in Microsoft Excel. A chance conversation with a colleague in 2020 led me to a business intelligence tool, Microsoft Power BI.
Process 2.0
Microsoft Power BI is a Swiss-army knife of data processing and visualization. You can go down many, many rabbit holes with Power BI, but that’s it in a nutshell. What Power BI allows you to do is set up (or structure) your data one time, connect it to a dashboard (that provides awesome visuals), and hit refresh.
Now, you can have your speciality when it comes to Power BI, but, I hang out more on the creative design and analysis side of Power BI. I love getting the right data in the system to make the most immediate impact for my clients.
And that’s the secret! Figuring out how to use the data that we can grab in the most meaningful way. If not, this technical work is all for nothing.
The process now is: I build a (structured) Excel file with my clients, connect it to Power BI, and hit refresh.
Voilà! Your dashboards update, and you instantly save countless hours on all that busy work — copying and pasting cells from one to another, recreating equations across files, and moving graphs around and around and around…
The structured part is important because most Excel files are unstructured and have data all over the place. The formatting, colors, and spacing may be to die for, but they don’t play well when trying to connect them to anything.
Processes Continued
Discovering Power BI in early 2020 supercharged my coaching process, allowing me to analyze more data, in real-time (with a LIVE client), finding more insights, and discovering new fitness decisions to be made.
Just like Excel, you have to get into the habit of making the same line graph, KPI card, or dashboard, over and again. However, at a macro-level, you can make templates and themes to where all you have to do is rewire the dashboard to a new Excel file, and you’re set!
After 5 years of regular data collection and dashboard design, all focused on fitness behavior change, I can truly say that the combination of Excel and some business intelligence tool (preferably Power BI) became a cornerstone of my entire coaching model and philosophy.
If I can’t figure out how to objectively define a health or fitness target, then I can’t get it on a data sheet, which also means that we can’t analyze progress in any visual.
Throughout this blog, I mention using Excel. Google sheets is a very popular format that works as a data source and functions just the same. And if you really want to go down the data rabbit trail, then you can quickly discover how many other data sources can be connected to Power BI. If you can think of it, you can probably dashboard it!
If you’ve never tried Power BI, I encourage you to do so. It can enhance how you look at data and maybe your life too
