Getting decent reports out of Kayako

KayakoKayako is a wonderful support ticketing system that we use at PICnet for all our Soapbox clients. As we get a better picture of the macro effects of our growth, we’re starting to realize that getting data out of Kayako is critical for us to understand how and where our clients need help.

As those who use Kayako know, there aren’t much of any reporting tools in eSupport product. Actually, there’s none. Luckily, Kayako has a well-supported forum community, from which I’ve been able to pull the following great SQL queries to run some basic reports. Detailed Report by Client by Time Range

SELECT FROM_UNIXTIME(t1.dateline) AS time_spent_entry_ts, t1.timespent, t1.timebillable, t5.fullname AS staff_worked, t1.notes AS billing_entry_notes, t3.fullname AS ticket_requestor_name, t4.title AS ticket_requestor_group_name, t4.usergroupid AS ticket_requestor_group_id, t2.ticketmaskid AS ticket_id, FROM_UNIXTIME(t2.dateline) AS ticket_posted_ts, t2.`subject` AS ticket_subject FROM swtickettimetrack AS t1 LEFT JOIN swtickets AS t2 ON (t1.ticketid = t2.ticketid) LEFT JOIN swusers AS t3 ON (t2.userid = t3.userid) LEFT JOIN swusergroups AS t4 ON (t3.usergroupid = t4.usergroupid) LEFT JOIN swstaff AS t5 ON (t1.forstaffid = t5.staffid);

Summary Report by Client by Time Range SELECT SUM(t1.timespent) AS time_spent, SUM(t1.timebillable) AS time_billed, MAX(t4.title) AS customer FROM swtickettimetrack AS t1 LEFT JOIN swtickets AS t2 ON (t1.ticketid = t2.ticketid) LEFT JOIN swusers AS t3 ON (t2.userid = t3.userid) LEFT JOIN swusergroups AS t4 ON (t3.usergroupid = t4.usergroupid) LEFT JOIN swstaff AS t5 ON (t1.forstaffid = t5.staffid) WHERE t1.dateline BETWEEN UNIX_TIMESTAMP('2007-03-01') AND UNIX_TIMESTAMP('2007-03-31') GROUP BY t4.usergroupid;

Detailed Report of Tickets with Custom Fields SELECT FROM_UNIXTIME(t1.dateline) AS time_spent_entry_ts, t1.timespent, t1.timebillable, t5.fullname AS staff_worked, t5.mobilenumber AS staff_phone, t5.email AS staff_email, t1.notes AS billing_entry_notes, t3.fullname AS ticket_requestor_name, t4.title AS ticket_requestor_group_name, t4.usergroupid AS ticket_requestor_group_id, t2.ticketmaskid AS ticket_id, t2.ticketid AS ticket_index, FROM_UNIXTIME(t2.dateline) AS ticket_posted_ts, t2.`subject` AS ticket_subject, t6.fieldvalue AS billing_name, t7.fieldvalue AS po_number FROM swtickettimetrack AS t1 LEFT JOIN swtickets AS t2 ON (t1.ticketid = t2.ticketid) LEFT JOIN swusers AS t3 ON (t2.userid = t3.userid) LEFT JOIN swusergroups AS t4 ON (t3.usergroupid = t4.usergroupid) LEFT JOIN swstaff AS t5 ON (t1.forstaffid = t5.staffid) LEFT JOIN swcustomfieldvalues AS t6 ON (t1.timetrackid = t6.typeid AND t6.customfieldid = '2') LEFT JOIN swcustomfieldvalues AS t7 ON (t1.timetrackid = t7.typeid AND t7.customfieldid = '3');

Nonprofit news, strategy, and tactics sent straight to your inbox
Sign up for the Soapbox Engage newsletter




This entry was posted on Monday, February 11th, 2008 at 8:00 am and is filed under nptech. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.