It all started out with a simple request from my son’s football coach. What I didn’t know was how saying yes would help me evolve from a guy that was fairly handy with Excel to a BI analyst. So, at the urging of Steve Hughes, who is the BI Practice Lead at Magenic, I’m writing a blog series about this process. My goal: show the steps I took in transforming these stats into a full-fledged BI solution within Excel 2013. In this series I’ll show how I went from this:
First, the request: at the beginning of my son’s 6th grade football season, his coach asked me if I could take stats for the team. He was looking for the very basic: rushing yards and tackles. I had done it once the previous season, filling in for a parent that couldn’t be at the game. Simple stuff, really. Since having this information helps the coaches better plan for the next game, I said yes to the request.
Being the tech savvy over-achieving business analyst that I am, I thought about the easiest way to capture the data. Writing it down? Not a chance. I avoid picking up a pen or pencil whenever possible. Our family has an iPad, so why not use that? I did a bit of research, and settled on using Numbers to capture the data during the game, export the data, email it to myself, and import it into an Excel spreadsheet. But to use all this capability to just capture rushing yards and tackles? Kinda like driving a semi to pick up groceries. It should have been no surprise to those who know me when I added more stats to the spreadsheet – I mean, why not? Soon, I had added the play being run, passing yards, kickoff and punt yardage, penalties, touchdowns, etc.
Soon enough, I began amassing a fair amount of data. Using the iPad was fairly easy to capture the stats in real-time (thanks to parent helpers that were my spotters), and I had the added benefit of being an unofficial part of the team staff on the sideline. The evolution was already underway, as my Excel formulas were becoming more complex in summarizing stats by play and player. In other words, a bunch of SUMIFS and COUNTIFS-based formulas that did the job, but were cumbersome and cludgy (not to mention error prone). That said, the coaches couldn’t get enough of it and were actually using the data to tweak the game plan on a weekly basis.
Somewhere during mid-season I discovered is that the football stats were a rich data set that was readily consumable by Power Pivot, a feature available to me in Excel 2010 sitting on my desktop. The evolution of displaying stats takes an interesting turn, which I’ll talk about in the next blog. In a word: Slicers!
(Note for those who may be curious: the images shown in these blog posts are real stats, but the names and numbers of the players have been changed to mask their identity. For those of you who know me and are trying to get a glimpse of how your kid did last year on my son’s team – good luck.)