1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Number of replies per ticket

Discussion in 'Kayako reports (Kayako Classic)' started by Mercury_Mark, Jun 23, 2015.

  1. Mercury_Mark

    Mercury_Mark Member

    I'm after a report that for a set date range will return the average number of staff replies per closed ticket.

    That's for tickets that were closed within the date range.
     
  2. Gary McGrath

    Gary McGrath Staff Member

    Hi there,

    Something like this should do it:

    Code:
    
    SELECT AVG('Tickets.Total Replies') AS AverageReplies FROM 'Tickets' WHERE 'Tickets.Resolved Date' > mktime(0,0,0,01,5,2015) AND 'Tickets.Resolved Date' < mktime (23,59,59,12,31,2015)  GROUP BY 'Tickets.Department'
    
    
    Gary
     
  3. Kirsty

    Kirsty New Member

    Hi Gary thanks for the reply, we will try these out & get back to you :) Kirsty

     
  4. Mercury_Mark

    Mercury_Mark Member

    Thanks for your help Gary. Are you able to make an addition to ignore any tickets that received no replies?
     
  5. Mercury_Mark

    Mercury_Mark Member

    To expand, the results returned from your query gave a number of departments with an average less than 1.
     
  6. Gary McGrath

    Gary McGrath Staff Member

    you could add to the where clause: AND 'Tickets.Total Replies' >= 1

    Gary
     
  7. Mercury_Mark

    Mercury_Mark Member

    That works, thanks. Hopefully last question, how would I exclude replies from customers? Looking for number of staff replies only.
     
  8. Gary McGrath

    Gary McGrath Staff Member

    I think you can add to the where clause: and 'Tickets.Creator' = 'Staff'

    Gary
     
  9. Mercury_Mark

    Mercury_Mark Member

    I tried that, error returned: tickets is not related to ticketposts
     
  10. Mercury_Mark

    Mercury_Mark Member

    Apologies, it looks like it worked fine. Thanks
     
  11. Mercury_Mark

    Mercury_Mark Member

    Sorry, spoke too soon. The query does return results, but seems to be missing a lot of data. To check, adding Tickets.Creator' = 'Staff' wouldn't just return tickets that our staff started? I'm looking for all staff replies.
     
  12. Gary McGrath

    Gary McGrath Staff Member

    hmm... I think you would have to run a report against the ticket posts table instead then, this might work:

    Code:
    
    SELECT AVG('Tickets.Total Replies') AS AverageReplies FROM 'Tickets' WHERE 'Ticket Posts.Creator' = 'Staff' AND 'Tickets.Resolved Date' > mktime(0,0,0,01,5,2015) AND 'Tickets.Resolved Date' < mktime (23,59,59,12,31,2015)  GROUP BY 'Tickets.Department'
    
    
    If not, then a new query only using the ticket posts table would be required.

    Gary
     
  13. Mercury_Mark

    Mercury_Mark Member

    The above query returns: Table is not part of available table list: ticketposts
     
  14. Gary McGrath

    Gary McGrath Staff Member

    I think you need KQL 2 for it to work ( e.g. later version of Kayako )

    Gary
     
  15. Leanme Dunn

    Leanme Dunn New Member

    How do you do this report where you just enter in the name of the month?
     

Share This Page