Page History

Overview

There are two reports built using metrics from Kayako in Google Data Studio:

...

These reports do not automatically get their its data from Kayako, it has to be uploaded in CSV format. There are two data sources setup:

• EDSCKayakoMetrics.csv: https://datastudio.google.com/datasources/17xMiZxtBnL75EHe7sdqBEG2401Sb7mfC
  • Holds metrics for EDSC User Support.
  • CSV file: EDSCKayakoMetrics.csv
• EDSCKayakoTags.csv: https://datastudio.google.com/datasources/86a89539-323f-47f2-8c65-88c1f39bae3e
  • Holds metrics for EDSC User Support 2.
  • CSV file: EDSCKayakoTags.csv

How-To Get Data From Kayako

1. Login to Kayako and click on 'Reports' in the purple banner, then 'Manage Reports' in the white banner underneath. Type in "earthdata search" to find all EDSC reports.Image Added
2. You'll need to get 3 metrics: number of tickets, time to first response, and tags. The reports with "Auto" in the name will get you all of the information you need for the monthly ops deep dive. However, if you find there are missing tags or you need to get those metrics for a special report covering a different time period, you can use the other "Between Dates" reports. For those reports the time has to be reported in Unix Time. I typically use this website for time conversion: https://www.epochconverter.com/
3. Click on "Earthdata Search Metrics Auto - Number of Ticket in Last 12 Months" to view the report query. If you're using one of the "Between Dates" reports, this is where you would put in the Unix Time. The "Auto" reports will automatically give you data by month for the last 12 months. Click 'Run Report'.Image Added
4. When prepping for deep dive, remember to pull the numbers for the previous month (i.e. for September's deep dive presentation you'll be reporting metrics for August). Take that number and upload it into the CSV file included at the top of this page ("EDSCKayakoMetrics.csv"). You'll also need to add a new date to the CSV file in the format of YYYYMM. Image AddedImage Added
5. Next you'll need to get the time to first response metric. On the Kayako reports page, click on "Earthdata Search Metrics Auto - Time to Respond in Last 12 Months". For this report, you'll need to export as a .csv file to get the correct number. Image Added
6. The CSV will export the metric in seconds. Copy that number and put it in the "EDSCKayakoMetrics.csv file. Then take it and type into Google to convert seconds to days, and add that number to the "EDSCKayakoMetrics.csv" file as well.Image AddedImage AddedImage Added
7. Save and close the "EDSCKayakoMetrics.csv" file.
8. Now we can get the tag information. On the Kayako reports page open "Earthdata Search Metrics Auto - Tags" and click 'Run Report'.Image Added
9. You need to make sure the total number of tagged tickets for the month adds up to the total number of tickets you got from the previous reports. If not, use the "Earthdata Search Find Missing Tag Between Dates" Kayako report (with Unix time) to add tags to any missing tickets. Use the tag buckets from the top of this page.
10. Open the "EDSCKayakoTags.csv" file from the top of this page. Add a new date to the CSV file in the format of YYYYMM. Copy the number of tickets for each tag group over from the Kayako report. Make sure to put zero's in the CSV file for anything that didn't have a tag (if there are missing zero's the Data Studio report will break).Image Added
11. Save and close the "EDSCKayakoTags.csv" file.

How-To Get Data From Kayako