Tip of the Week: Calculate Your Average Resolution Time using CSV exports

As we described in Tip of the Week: Review Ticket Resolution Times with Reports, you can get a good snapshot of your support tickets using the Zendesk graphical reporting available to all accounts (go to the Report page under the Manage tab). If you want to do some more heavy duty analysis, however, it’s best to get at the raw data.

With a Zendesk Plus+ account, you can export this raw data in both csv and xml format. What you’ll get is an output of every ticket in your Zendesk. With the csv export, this means all the data from your ticket fields (i.e. Status, Requester, Type, as well as any of your custom fields); the xml report also exports all the comments from your tickets, as well as all your account data (your users, organizations, forums, etc.)

The csv export is good for doing summary analysis – you can open it up in a spreadsheet program to evaluate and combine specific columns. For instance, I want to check what Zendesk’s Average Resolution Time is per ticket – meaning, what is the average time it has taken us to solve a ticket over the life our help desk (and we’ve been using Zendesk longer than anyone ;-)). Compared to the previous Reporting Tip of the Week mentioned above — which would be like charting our batting average over the course of June 2010 — this is sort of like calculating our career batting average.

First I need to generate a csv export. To do so, go to the Reports page under the Manage tab. Scroll down to the section titled “Exports” (remember that exports are a Plus+ only feature).

Click the generate link to start the export (note that you can also schedule regular exports). You’ll get a notice that your report is being built.

Your Zendesk will send you an email shortly thereafter with a link from which you can download the csv file.

Next, I’m going to open up the csv file in my spreadsheet program. Depending on how many tickets you have in your Zendesk, this could take a moment. (It did with our file.) Across the top are the names of your ticket fields, as well as some additional data like Resolution Time, and the date at which the ticket was assigned to someone. Beneath that we have rows and rows of data.

I’m looking to calculate Zendesk’s lifetime average resolution time. I find that row in my spreadsheet program, and from there it’s a simple process of using the program to calculate the average of that row. Zendesk calculates resolution time in hours – and when I run it, I get an average of 99 hours, or about 4 days (which I get by dividing the 99 by 24). So Zendesk’s lifetime average resolution time is 4 days.

This sounds pretty good considering that while we want most tickets to get answered in one business day, other complicated tickets do take longer. Also, when I review the spreadsheet, I notice that there are a few outliers — tickets that sat in the system unsolved for one reason or another for over 5000 hours. These kinds of tickets are inevitable – either they sat pending for a long time waiting on a customer response that never came; or they were a ticket that got dealt with but never solved. So if you wanted to get more fancy, you could figure out a rule in your spreadsheet program to ignore these types of outliers in your calculations.

Additionally, you could filter down the data by the type of ticket it is; for instance, if you use Zendesk Groups. In our case, we receive and solve most tickets within our Level 1 support group (as opposed to escalating them or sending them to another group like Marketing). If I sort my spreadsheet according to group, I can focus just on the average resolution time of our Level 1 support tickets. When I do that the average drops to 36 hours.

Both of these are simple examples, but I encourage all you spreadsheet ninjas out there to try working with your complete Zendesk data and see what you can find.