HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Time to Response


#1

Hi Ian,

is there any possibility to get min/max/avg time to response, eg. from receiving mail in inbox to the first public note?

For now, it would be just enough for me to run filter to see all requests that were not answered within 24h. I know that I can filter requests using a custom where clause (SQL), which I hope can do the trick. However I do not know what the syntax should be.

Best Regards,
Radim Kalas


#2

Hi Radim,

That type of report is something we’re going to be looking at for version 3 where we’ll be putting a new reporting system in place.

For now in terms of that filter I think you can get that pretty much by using the filter condition “number of public updates” IS 1. Most new requests have 1 public update by the customer. So you could do number of public updates is 1 and open/closed IS open. From there you could also add some time restrictions if you like as well.


#3

Hi Ian,

thanks for the answer. It’s good news, that you are planning such report for future.

My problem is, that I want to run the report for past requests to proof, that we fulfilled the 24h response criteria over that specified time (half a year). Any idea how to get this info from HelpSpot?

Best Regards, Radim


#4

Hi Radim,

Sorry for the delay. You could do probably do it with sql and maybe just the custom where. What database are you on? I’ll try and whip something together, but I’ll need to know the database to get the sql right.


#5

Hi Ian,

thank you very much for your kindly help. I’m on MySQL 5.0.45


#6

Radim, I added this to MySQL. It was implemented as a trigger. This is Ian’s board, so I don’t want to pretend to be anything but a customer. I’m not a consultant.

/*

name: orbawaretrigger
beastmaster: bek
date: Wednesday, January 31, 2007 12:56:03 PM
docs: http://someurl/wikidocs/SysAdmin/cs-070131-orbawaretrigger-notes.pdf
docs: http://someurl/wikidocs/SysAdmin/cs-070131-orbawaretrigger-notes.txt
ticket: https://someurl/spark/admin.php?pg=request&reqid=22940

mysql> create table orbAware ( awareid int(11) not null auto_increment, primary key (awareid), xRequest int(11), dtGMTOpened int(11), dtGMTAware int(11), awareSeconds int(11) );
Query OK, 0 rows affected (0.02 sec)

mysql> create index orbawarexrequest on orbAware(xRequest);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index orbawareopened on orbAware(dtGMTOpened);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index orbawareaware on orbAware(dtGMTAware);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index orbawareseconds on orbAware(awareSeconds);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe orbAware;
±-------------±--------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------------±--------±-----±----±--------±---------------+
| awareid | int(11) | NO | PRI | NULL | auto_increment |
| xRequest | int(11) | YES | MUL | NULL | |
| dtGMTOpened | int(11) | YES | MUL | NULL | |
| dtGMTAware | int(11) | YES | MUL | NULL | |
| awareSeconds | int(11) | YES | MUL | NULL | |
±-------------±--------±-----±----±--------±---------------+
5 rows in set (0.01 sec)

*/

delimiter //
create trigger orbawaretrigger after update on HS_Request for each row begin if OLD.iLastReadCount < 1 and NEW.iLastReadCount > OLD.iLastReadCount and NEW.xPersonOpenedBy=0 and NEW.xPersonAssignedTo <> NEW.xPersonOpenedBy and NEW.fOpen=1 and NEW.xOpenedViaID=3 then INSERT INTO orbAware SET xRequest=NEW.xRequest, dtGMTOpened=NEW.dtGMTOpened, dtGMTAware=UNIX_TIMESTAMP(current_timestamp), awareSeconds=TIME_TO_SEC(TIMEDIFF(current_timestamp, FROM_UNIXTIME(NEW.dtGMTOpened))); END IF; END; //
delimiter ;

We have a reporting server that we query this data from.


#7

Hi Radim,

As Bek posted a custom table with a trigger to track times would certainly allow for exactly data in this regard. It sounds like you’re also interested in what’s already in your install as well though so the query is probably still useful.

This is my first pass at it. It seems to work correctly, but there could be some edge cases which it doesn’t catch. This will be pretty slow on mysql because of it’s horrible subselect performance so try to add other conditions to limit the results like customer ID and a “before opened date” “after opened date” date range to limit how many requests it has to search through.

This is what should be in the custom where condition. It’s set to check for where the first response from a staffer was more than 24hours after the requests opened date. You can adjust this by changing the 86400 seconds (24 hours) to a different number of seconds which represents the delay you want to check for.

((SELECT dtGMTChange FROM HS_Request_History WHERE HS_Request_History.xRequest = HS_Request.xRequest AND fPublic <> 1 AND xPerson > 0 ORDER BY dtGMTChange ASC LIMIT 0,1) - dtGMTOpened > 86400)