How to create ITS Monthly Metrics

Description

WSU Information Technology Services Department has implemented metrics to measure our effectiveness and our customer satisfaction. While all of our metrics cannot be pulled from information stored within TeamDynamix (TDX), whenever possible we attempt to gather the metrics from TDX using automated reports. The procedures below walks a person through the process of creating the metric in TDX, setting up an automated delivery and how to pull the metric from the received.  The metrics not pulled from TDX are listed with contacts.

NOTE: Due to the constant changing of the methods of how we measure, additions and deletions of what we measure and who is responsible for gathering the data, this document may change quickly. Please notify the administration team if the document is not helpful in any area. 

Resolution

The current month's metric spreadsheet is due on the first Friday of each Month for stats registered during the previous month. All automated reports from TDX are processed and delivered to the TDX Administrators at 5am on the first day of each month.  The reports include data only from the first to last day of the previous month.  The administrators will then work with the TDX data (generally in Excel spreadsheets) and place the received results into the current Month's document. 

1. TeamDynamix preparation
   1. TDX administrators need to work closely with ITS business units/Directors to determine what metrics we would like to report and how we want to pull that metric from the raw data
   2. If determined the data is best pulled from TDX, then either a Ticket report or an Analysis report will need to be created. This report is to be set up to pull the data needed from tickets/projects that had the required action (Closed, Opened, Survey Completed, etc.) "last month"
   3. Since we allow testing of tickets as a general a filter needs to be added to remove tickets that contain "test" in the Title
   4. Maximum rows need to be set at the maximum of 50,000 to ensure all data is pulled
   5. Visibility should be set for "Everyone with this Application"
   6. Add email recipients to the Email Delivery as follows:
      1. Recipients: All TDX Administrators by name. Currently Anita Barrett, Warren Glore and PMO Student1
      2. Interval: Monthly on the 1st day at 5:00am
      3. Format: Generally Excel
      4. Send If Empty: Yes
2. Metric documentation preparation
   1. Find last month's metrics file which will be in the named format of 'yyyymm01 ITS Strategic Metrics' in the TEAMS ITS site under files/ Metrics folder
   2. Open the file and click on the Final tab at the bottom of the page
   3. Update all the current Sparklines to include the past month's data
   4. Click on the column header Sparkline to highlight the entire column
   5. Right-click on the lettered column header and then click Insert. 
   6. Change the newly created column header's name to the first day of the new month
   7. Save as the new document with the current 'yyyymm01 ITS Strategic Metrics'
   8. While on the WSU network or on the WSU VPN, browse to \\filesvc-312.ad.wichita.edu\UCATS_Share\ITS Metrics YYYY and place the new file here.
3. Moving the metric raw data to proper location
   1. Browse to \\filesvc-312.ad.wichita.edu\UCATS_Share\ITS Metrics Raw Data\
   2. Create a folder yyyymm where yyyy is the current calendar year and mm is the current calendar month
   3. Open each automated email received from notify@teamdynamixapp.com around 5:00am on the 1st
   4. Copy/Drag and drop the attached file to the newly created folder
   5. Repeat until all the automated report attachments have been added to the new folder
   6. Right-Click in the new yyyymm folder and create a new folder called 'Completed'
4. Pulling metric data from automated reports, adding data to metric document
   1. Metric Name: Desktop Support - Percent of Assigned Service Request Tickets Closed Within Resolved SLA 80% of Time (Row 2)
      1. File Name: 02 DesktopSupportTicketsClosedwithin7BusinessDays-....
      2. Add Column 1 = If(create to Resolve(op)<8,"Yes","No")
      3. Add Cell 1 = Count(All ticket rows)
      4. Add Cell 2 = Countif(All Tickets in Column 1, "Yes")
      5. Add Cell 3 = (Cell 2/Cell 1) Change cell to % with 1DP
      6. Enter data into metric report
      7. Save data spreadsheet and move to Completed folder
   2. Metric Name: 04 Percent of Desktop Tickets with Asset Recorded (Row 4)
      1. File Name: DesktopSupportTicketswithAssets-...
      2. Add Column 1: = IF(asset count >0, "Yes")
      3. Add Cell 1: = COUNTIF(All Coulmn 1="Yes")
      4. Add Cell 2: = Count (All rows)
      5. Add Cell 3: = Cell 1/Cell2 Change Cell to % with 1DP
      6. Enter data into metric report
      7. Save data spreadsheet and move to Completed folder
   3. Metric Name: Help Desk - percent of tickets closed in less than .6 hours (Row 7)
      1. File Name: Desktop Support Average Resolve Time on Service Requests Report-...
      2. Add Cell 1: = Sum of all (op) hours
      3. Add Cell 2: = Count (All rows)
      4. Add Cell 3: = (Cell 1/Cell 2) Change Cell to % with 1 DP
      5. Enter data into metric report
      6. Save data spreadsheet and move to Completed folder
   4. Metric Name: Help Desk - Number of Tickets resolved by the Help Desk (Row 8)
      1. File Name: DesktopSupportAverageResolutionTimeonIncidentsRepo-
      2. Add Cell 1: = SUM(all Create to Resolve op)
      3. Add Cell 2: = Count(all ticket rows)
      4. Add Cell 3: = Cell 1/Cell 2 Change Cell to % with 1 DP
      5. Enter data into metric report
      6. Save data spreadsheet and move to Completed folder
   5. Metric Name: 90% of DHCP/DNS tickets completed in 9 business hours (Row 12)
      1. File Name: HelpDeskAverageResolveTimeonServiceRequests-
      2. Add Cell 1: = CountIf(All Act Hours,"<.6")
      3. Add Cell 2: = Count(All Act Hours rows)
      4. Add Cell 3: =Cell 1/Cell 2 Change Cell to % with 1 DP
      5. Enter data into metric report
      6. Save data spreadsheet and move to Completed folder
   6. Metric Name: Closed tickets per month divided by tickets opened per month for ITS overall (Row 16)
      1. File Name: HelpDeskAverageResolveTimeonServiceRequests-
      2. Add Cell 1: = COUNT(All Rows)
      3. Enter data into metric report
      4. Save data spreadsheet and move to Completed folder
   7. Metric Name: 80% of Telecommunications work orders completed by target date (Row 20)
      1. File Name: DHCPDNSTicketsCompletedin2BusinessHours-
      2. Add Cell 1: =COUNT(All Rows)
      3. Add Cell 2: =COUNTIF(All Create to Resolve op Rows,"<2.01")
      4. Add Cell 3: =Cell 2/Cell 1 Change Cell to % with 1 DP
      5. Enter data into metric report
      6. Save data spreadsheet and move to Completed folder
   8. Metric Name: Close all Access door lock tickets within 4 business hours 85% of the time  (Row 21)
      1. File Name: ITS datacenter route and switch tickets
      2. Subtract created date from the due date
      3. Total those <1
      4. Divide those <1 by the total number of tickets.
   9. Metric Name: Closed tickets per month divided by tickets opened per month for ITS overall (Row 16)
      1. File Name: TicketsOpenedLastMonth and ITSTotalTicketsClosedbyMonth-
      2. Total the number of tickets closed
      3.  Total the number of tickets opened
      4. Divide the number of tickets closed by the number of tickets opened
      5. Enter data into metric report
      6. Save data spreadsheet and move to Completed folder
   10. Metric Name: 80% of Telecommunications work orders completed by target date (Row 20)
       1. File Name: Telecommunications Work Orders Completed By Target Date Report after 2/2/22
       2. Reformat Column containing Resolved date to mm/dd/yyyy
       3. Add Column 1: =Resolved Date - Due Date, Populate with  =IF(Resolved Date column is less than or equal to Due date column,"1","0") This won't work if time is later in the day than original ticket - relook and give credit for any completed at anytime on the due date.  
       4. Add up total tickets and ticket count that met due date
       5. Enter data into metric report
       6. Save data spreadsheet and move to Completed folder
   11. Metric Name: Close all Access door lock tickets within 4 business hours 85% of the time (Row 21)
       1. File Name: 21 DoorLocksSLAReport-...
       2. Sum FALSE/ Total Tickets
       3. Enter data into metric report
       4. Save data spreadsheet and move to Completed folder
       5. NOTE: Switched to new ticket on 7/29/22. Check that it is pulling resolved tickets on inactive services.
   12. Metric Name: Close all Certificates tickets within 8 business hours 85% of the time (includes vendor time) (Row 22)
       1. File Name:CertificateTicketsSLAReport-....
       2. Add Cell 1: =COUNT(All Create to Resolve op Rows)
       3. Add Cell 2: =COUNTIF(All Create to Resolve op Rows,"<8.01")
       4. Add Cell 3: =Cell 2/Cell 1 Change Cell to % with 1 DP
       5. Enter data into metric report
       6. Save data spreadsheet and move to Completed folder
   13. Metric Name: Data Centers remain in environmental specifications 99% of the time (Row 23)
       1. Manually entered by John Rankin
   14. Metric Name: Service uptime -Authentication - ADFS - TDX - O365 - BB (Row 24)
       1. Manually entered by Jason Holmes
   15. Metric Name: Service uptime -Authentication - CAS - Banner - myWSU (Row 25)
       1. Manually entered by Jason Holmes
   16. Metric Name: Service uptime - Employee email (Row 26)
       1. Manually entered by Jason Holmes
   17. Metric Name: Service uptime - ERP Banner - Student Registration (Row 27)
       1. Manually entered by Jason Holmes
   18. Metric Name: Service uptime - ERP Banner - Employee Self Service (Row 28)
       1. Manually entered by Jason Holmes
   19. Metric Name: Service uptime - myWSU (Row 29)
       1. Manually entered by Jason Holmes
   20. Metric Name: Service Request Tickets resolved on time, percentage is greater than 85% of planned SLA (Row 30)
       1. File Name: ITS Average Resolve Time on Service Requests Report
       2. Find/Replace all False with False
       3. Add Cell 1: = COUNT(All Create to Resolve op)
       4. Add Cell 2: = COUNTIF(All SLA Resolve By Violated,"False")
       5. Add Cell 2: =Cell 2/Cell 1 Change Cell to % with 1 DP
       6. Enter data into metric report
       7. Save data spreadsheet and move to Completed folder
   21. Metric Name: Incident Tickets resolved on time, percentage is greater than 90% less than one business day. (Row 31)
       1. File Name: ITS Average Resolve Time on Incidents Report
       2. Find/Replace all False with False
       3. Add Cell 1: = COUNT(All Create to Resolve op)
       4. Add Cell 2: = COUNTIF(All SLA Resolve By Violated,"False")
       5. Add Cell 2: =Cell 2/Cell 1 Change Cell to % with 1 DP
       6. Enter data into metric report
       7. Save data spreadsheet and move to Completed folder
   22. Metric Name: Ticket Responsiveness is Satisfied or Very Satisfied (Row 34)
       1. File Name: ITSAverageResponseTimeonTicketsReport-...  under Analysis.
       2. Add Cell 1: =COUNT(All Sum of Create to Init Respond
   23. Metric Name: Actual ticket Responsiveness SLA - Someone saw the ticket and responded to it (automatic)  (Row 35)
       1. File Name:TicketsSatisfactionSurveyResults(Responsiveness)La-... under Analysis.
       2. Add Cell 1: =SUM(Satisfied Response Count + Very Satisfied Response Count)
       3. Add Cell 2: =(Cell 1/Total Response Count) Change Cell to % with 1 DP
       4. Enter data into metric report
       5. Save data spreadsheet and move to Completed folder
   24. Metric Name: Ticket solution is rated Satisfied or Very Satisfied 85 % of time, how well did the solution help the customer (Row 36)
       1. File Name:TicketsSatisfactionSurveyResults(Satisfaction)Last-...
       2. Add Cell 1: =SUM(Satisfied Response Count + Very Satisfied Responce Count)
       3. Add Cell 2: =(Cell 1/Total Response Count) Change Cell to % with 1 DP
       4. Enter data into metric report
       5. Save data spreadsheet and move to Completed folder
   25. Metric Name: Complete all termination tickets within 1 business hour 90% of the time (Row 37)
       1. File Name:UrgentDisableEmployeeSLAReport-...
       2. Find/Replace all False with False
       3. Add Cell 1: =COUNT(All Act Hours Rows)
       4. Add Cell 2: =COUNTIF(All SLA Resolved by Violated Rows,"False")
       5. Add Cell 3: =Cell 2/Cell 1 Change Cell to % with 1 DP
       6. Enter data into metric report
       7. Save data spreadsheet and move to Completed folder
   26. Metric Name: Percent of Projects tied to Mission of WSU (Row 39)
       1. File Name: Metric Project 6 quality points, submitted date, create from Project Request, found under Analysis tab on Project KPI
       2. Click on Run
       3. Review data in 2 columns "Mission, Goals and SPI Ties" and "Goals" which is the old way we tied ITS projects to WSU's mission;  total all projects with a mission or Goal.  Divide by total number of projects in portfolio.
       4. Enter data into metric report
       5. Save data spreadsheet and move to Completed folder
   27. Metric Name: Percent of projects with communication to sponsors at least once a month (Row 40)
       1. File Name: Metric Project 6 quality points, submitted date, create from Project Request, found under Analysis tab on Project KPI 
       2. Add a column in your file called Communication.
       3. Double check that the Project Manager is not also the Sponsor, the CIO is the sponsor for any internal ITS projects managed by ITS directors.
       4. On TDX report, look at the Feed, see that there is one email to sponsor each month.  Takes about 15 minutes, pull up the TDX Report and click down into each project to determine.  Capture in the file under Communication field, and divide the number with communitation to sponsor by total number of projects in the portfolio that month.  Verbal updates are also included - for example CIO gets a verbal update on projects where he in sponsor in PPM meetings.
       5. Enter data into metric report
       6. Save data spreadsheet 
   28. Metric Name: Percent of projects planned in advance of starting project (current month over the ITS projects in the portfolio now planned by at least 5 days) (Row 41)
       1. File Name: Metric Project 6 quality points, submitted date, create from Project Request
       2. Add a column "Known 5 days in advance", place yes or no if more than 4+ number of days before Submitted Date.  
       3. Divide number of yes by total number of projects in profolio.
       4. Enter data into metric report
       5. Save data spreadsheet 
   29. Metric Name:  Percent of projects with Project Request filled out (Row 42)
       1. File Name:Metric Project 6 quality points, submitted date, create from Project Request
       2. Total up all projects with True / total number of projects in portfolio.  Use column called "Created From Project Request".
       3. Enter data into metric report
       4. Save data spreadsheet
   30. Metric Name: Percent of Transformational and Innovational to Grow or Run projects (Row 44)
       1. File Name:  Metric Project 6 quality points, submitted date, create from Project Request
       2. Add up number of project that are NOT "Run or Grow"
       3. Divide by number of project in portfolio
       4. Enter data into metric report
       5. Save data spreadsheet and move to Completed folder
   31. Metric Name: Percent of tickets with an SLA (Row 52)
       1. File Name 52 W- Tickets with SLA assigned
       2. Select and delete all Telecom tickets as they have due dates and their own metric
       3. Select all tickets that are blank, subtract from total 
       4. Divide all tickets with a SLA with total tickets (without Telecom) for that month.
       5. Enter data into metric report
       6. Save data spreadsheet and move to Completed folder
   32. Metric Name: Help Desk - transfer tickets to other groups or created others' tickets for them (Row 53)
       1. File Name: 53 Help Desk in process tickets to other groups each month count
       2. Count all tickets for the given month
       3. Enter data into metric report
       4. Save data spreadsheet and move to Completed folder
   33. Metric Name: Actual ticket Responsiveness SLA - Someone saw the ticket and notified the requestor in the response (manual) (Row 55)
       1. File Name: 35 ITS Average Response Time on Tickets
       2. Total the number of tickets and divide by 30
       3. Analyze every nth ticket to determine whether criteria was met
       4. Divided number of tickets that notified the requestor in first two updates by 30
       5. Enter data into metric report
       6. Save data spreadsheet and move to Completed folder
   34. Metric Name: Percent of Completed Milestones/Storypoints for ITS projects  last month (Row 56)
       1. File Name: 57i Percent of Projects with Milestones or Story Points Director improved
       2. Sort Percent completed column to 100%
       3. Sort End column to this month
       4. Divide milestones and story points completed on time by total number of milestone
   35. Metric Name: Percent of Projects with at Least One Milestone/Story Point (Row 57)
       1. File Name: 57i Percent of Projects with Milestones or Story Points Director improved
       2. Open Portfolio "FY2023" and count how many projects are active
       3. Create PivotChart using report
          1. Rows: Director, Project
          2. Values: Story Points
       4. Divide the number of projects with at least 1 milestone by the total number of projects
       5. Enter data into metric report
       6. Save spreadsheet and move to Completed folder
   36. Metric Name: Project Satisfaction Survey, Percent of Sponsors Who Rank Them All 4-5 (Row 58)
       1. File Name: Project Sponsor Satisfaction Survey
       2. Open Projects tab
       3. Go to Project Surveys
       4. Click on Project Sponsor Satisfaction Survey
       5. View responses
       6. Divide responses that gave 4-5 by total number of responses
       7. Enter data into metric report
       8. Save responses as a PDF and move to Completed folder
   37.  Metric Name: Desktop Support - Percent of assigned incident tickets closed within Resolved SLA 80% of time. New 10/27/2022 (Row 59)
       1.  
   38.  Metric Name: Tickets older than one month compare last to this month (Row 60)
       1. File Name: 60 Tickets older than one month compare last month to this month
       2. Find report in TDX Metrics folder
       3. Status is one of New, In Process, and Open
       4. Run report
       5. Write down data
       6. Run the report again excluding types; I am interested in a badge!, Mifi, and loaner laptops
       7. Subtract the excluded types from the total
       8. Divide last month total by this month
       9. Enter data into metric report
       10. Save spreadsheet and move to Completed folder
   39.  Metric Name: Windows Support - Percent of assigned Service Request tickets closed within Resolved SLA 80% of time (Row 61)
   40. Metric Name: Documentation: Data Interfaces (Row 63)
   41. Metric Name: Reporting Services Average Tickets Open (64)
   42.  Metric Name: Application Admin - Average Open Tickets Per Week ​​​​​​​(Row 65)
   43. Metric Name: Application Admin - Average Open Tickets Per Week ​​​​​​​(Row 66) 
   Finalizing and sharing metric data
   1. Notify all Directors and/or Managers who are responsible for adding their own metric data that the document is ready. Remind them of the current document name.
   2. Review and modify any additional cells that need to be modified
   3. On or before the first Friday of the current month, notify all ITS Directors and the CIO that the metrics are ready to review and work with their teams.
   4. Work with the directors to change/modify any reporting cell or metrics needed.
   5. Distribute latest file to ITS_ Mass_Mailing@wichita.edu with any notes of changes or next steps needed.
   6. Load the metric file into the ITS Monthly Metrics folder.
   7. Put on BRM Teams site
5.