Kayako 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;
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');
Donations
Events
Petitions
Forms
Shop
Directories