Advanced CSV use

We've created a sample spreadsheet (_XLSX format -- Excel 07 or newer is strongly recommended_) that shows some of the things you can do with the data. 

Download: tender_csv_export_sample_20100908b.zip

In the spreadsheet's worksheet called "WORKING DATA" we imported the source data from the CSV (under the source data header), and performed some initial calculations. Percentage of discussions that have been responded to, and percentage resolved are good "general health" statistics. 

01_calc_header3.jpg

As you can see above, we included several other calculations, such as average and median response and resolution times (both are enlightening in this example -- the average is much higher than the median, which means some items seem to fall off the radar for a long time). For you folks out there with large corporate clients, Angel investors, or Venture Capitalists to keep happy, we included sample SLA calculations. Take a look at the formulas in the spreadsheet to see how you can examine your performance against our sample SLAs (1 hour response, 1 day resolution). 

Further along, we also created 2 histograms to examine the initial response times and resolution times for all of the discussions in the CSV:

02_init_response_histo.jpg

03_resolution_histo.jpg

For these histograms, we chose to use non-linear bins, mainly to see how our responses stack up against 1 hour or 1 day goals. 

Quite possibly the most enlightening chart we created is the Average Initial Response by Created Day of Week chart. That's quite a mouthful to say (or to type), but you can see below that issues opened on Saturday or Sunday tend to have much higher initial response times, with the times dropping fast through Wednesday and peaking up again on Thursdays. This tells us we can improve our times by focusing on Mondays and Thursdays with more supporters. 

04_AvgInitReponseByCreatedDoW.jpg

An example you might wish to see is how are your supporters doing, from an initial-response/resolution standpoint. Below are example pie charts indicating which supporters have what share of initial responses and resolutions.

05_pie_charts.jpg

You can also see opens/closes and resolutions by Day of Week and Hour of Day in these charts. One indication in the Hour of Day chart is that new opened discussions start ramping up in the morning Eastern Time, a few hours before our LA and Portland teams are getting up for the day.

07_hour_of_day.jpg

08_day_of_week.jpg

There are several other charts in the spreadsheet, as well as a lot of sample formulas (you will certainly be able to note that we love the COUNTIFS function), and this is just scratching the surface of the analysis you can perform on your exported CSV data.