Tickets assigned to DSDS→ Service Type: "User Service Restoration","Security incident","user service request"
For the site view, based on the user log_in, the site of the user will be detected and the incidents created only in the similar site will be considered for the calculation.
Note: Only two sites to be considered for the reporting at this stage. For that, we need to consider tickets that are raised by the users based on the corresponding site. "Site ID": "STE000000003884", What are the codes of the sites to involve?
The dashboard's refresh frequency is set to daily, updating every midnight.
The data for the dashboard is sourced from a daily snapshot stored in BigQuery.
| Measures | Dimensions/fields | Notes | ||
|---|---|---|---|---|
| Backlog incidents | All incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site. | "Status" | Status: Resolved | SELECT * FROM `prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_HD_incidents` where Status in ('In progress','Pending', 'assigned') |
| Resolved incidents | Backlog incidents with "Status": "Resolved" | "Status" "Last Resolved Date" | We don't have status closed Closed date is only for cancelled incidents last resolved date is relevant for the calculation | |
| Critical incidents | Backlog incidents with "Priority"= "Critical" | "Priority" | Critical High Medium Low | SELECT * FROM `prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_HD_incidents` where Status in ('In progress','Pending', 'assigned') AND Priority='Critical' |
| Created incidents | the number of backlog incidents where the "Submit Date" falls within the specified calendar day. | "Submit Date" | Submit date and report date in the data set are same | |
| Unassigned incidents | Backlog incidents WHERE "Assignee Login ID" is Null | "Assignee Login ID" | Status can be 'Assigned' but it is not reliable, we have many records with assigned status but assignee column is null. | |
| Mean time to resolve incidents=MTTR=Total time spent resolving incidents/number of incident |
| "Last Resolved Date" "Submit date" |
| Indicators | Description | Source table technical name | Source field technical name |
|---|---|---|---|
| Backlog incidents | The number of incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site. | Joint: User ID | customer_login_id |
| backlog Incident daily growth rate | Calculates the number of backlog incidents in yesterday and the number of the day before yesterday. Then, computes the ratio by dividing the count of yesterday's backlog incidents by the count of backlog incidents of the day before yesterday. If the count of backlog incidents for the day before yesterday is 0, it returns NULL for the backlog ratio to avoid division by zero. | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| backlog Incident weekly growth rate | Calculates the number of backlog incidents in yesterday and the number of the day before yesterday. Then, computes the ratio by dividing the count of yesterday's backlog incidents by the count of backlog incidents of the day before yesterday. Repeat the calculation for the last seven day | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| Unassigned incident | Number of ongoing incidents at the reporting time with no assignee Backlog incidents WHERE "Assignee Login ID" is Null | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| Unassigned incident weekly growth rate | Calculates the number of unassigned incidents in yesterday and the number by the day before yesterday. Then, computes the ratio by dividing the count of yesterday's unassigned incidents by the count of backlog incidents of the day before yesterday. Repeat the calculation for the seven last day. | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| Critical incidents | The number of Backlog incidents with "Priority"= "Critical" | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| Critical incidents weekly growth rate | Calculates the number of critical incidents in yesterday and the number reported by the day before yesterday. Then, computes the ratio by dividing the count of yesterday's critical incidents by the count of backlog incidents of the day before yesterday. Repeat the calculation for the seven last day. | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| Oldest critical incidents (Time) | Descoped | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| Created tickets last 7 days | Created incidents: the number of backlog incidents where the "Submit Date" falls within the specified calendar day. To be reported in the dashboard for the last seven days | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| Resolved incidents | Backlog incidents with "last resolved day" falls within the specified calendar day. | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| Resolved incident daily - last 7 days | Backlog incidents with "last resolved day" falls within the specified calendar day. To be reported in the dashboard for the last seven days | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| MRTT of resolved incident | Mean time to resolve incidents=MTTR=Total time spent resolving incidents/number of incident 11)Retrieve the incident numbers with the 'Last Resolved Date' being yesterday. 2)Obtain the 'Submit Date' of each incident. 3)Calculate the Time to Resolve by subtracting the 'Submit Date' from the 'Created Date' for each incident. 4) Sum up the time to resolve for all incidents resolved yesterday. 5)Calculate the Mean Time to Resolve | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents | |
| MRTT daily growth rate | prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_incidents |