eugene gordin

I’m a sucker for usage statistics, whether its energy usage through my Nest or app usage though graphs like the one Alfred beautifully provides its users. I wish more applications would provide this feature. Sure, some apps like TextExpander and Keyboard Maestro track some statistics, but graphs for this type of information are just more interesting.

Luckily, using Keyboard Maestro, Dropbox, and Excel, I created a way to automatically track and graph my app usage across both of my Macs. The end result¹ looks like this (click for larger image):

My goal was to track my usage of Trickster, a fantastic application that I knew I used frequently, but didn’t know exactly how often. Here’s how I did it.

Step 1: Keyboard Maestro

I created a single action Keyboard Maestro macro that is triggered when Trickster activates (see screenshot at right). I am not sure how Keyboard Maestro defines “activation,” but since Trickster is a dockless menubar application, I would imagine that the definition is pretty universal (also works with Adium, Mail and Safari). When this macro is triggered, Keyboard Maestro appends an existing plain text file with a date stamp, time stamp, and an identifier for the current Mac, each separated by tabs. The entire line is followed by a line break, making a new line for the next usage stat. All together, the line looks like this:

%ICUDateTime%MM/dd/yyyy hh:mm% %MacName%\n

The ICUDateTime text token allows you to have full control over the format for the date and time stamps, while the “\n” creates a new line (can also be done %LineFeed% but \n is shorter. Be sure to not include a space before the backslash, as this just crowds the data.

Step 2: Dropbox

To combine the usage statistics for multiple Macs, I used Dropbox, which is excellent for this type of use. Specifically, all you need to do is to make sure that the plain text file that you’re appending with Keyboard Maestro is located in your Dropbox folder. That’s it – gotta love Dropbox.

Step 3: Excel

I used Excel to process the text file and graph the results. I used Excel 2010 for Windows – which I use daily at work through Parallels, but Excel 2011 for Mac may also work with some adjustment (Numbers unfortunately won’t work as easily or automatically). I’m sure someone like the excellent Brett Terpstra or Dr. Drang or Gabe Weatherhead can come up with a better way to graph the results, but this was the most logical for me.

This was probably the most complicated step, but since my spreadsheet is available for download along with this post, it should be a bit easier for others to use and adapt it to their needs. My approach automatically updates my spreadsheet with the latest usage data from my plain text usage file upon opening the spreadsheet, but you could also simply paste the contents of your plain text file into the spreadsheet if it was set up that way. The spreadsheet includes instructions to point Excel to your data text file in order to enable the automatic updating.

For those interested in the details, the spreadsheet is taking advantage of some very useful Excel features behind the scenes. Specifically, the spreadsheet is automatically importing and parsing the plain text file upon opening the file using external connections. This data is then automatically processed into a PivotChart (“Graph” sheet) by way of a PivotTable (“Summary” sheet). The average usage values are themselves a PivotTable calculated from the PivotTable using dynamic named ranges. In addition to automatically refreshing the table with the latest data from your text file, the spreadsheet also uses a macro to automatically update the Summary and Graph worksheets when you activate those sheets.

My end goal is to set up a spreadsheet that calculates these statistics for a few more applications at once, but for now this works very well. I’m positive there are more efficient ways to generate (and share) these usage graphs, so please let me know if you come up with something better.

Download my App Tracking Spreadsheet (v0.4)

See all my Keyboard Maestro posts.