fbpx

At RadBee, we’re able to help many of our clients who are using Jira Service Management to better use EazyBI for monitoring the performance of their customer service teams and generate specific and targeted reports toward that goal.

This story is the product of an exceptionally complicated client support situation. We found ourselves closely interacting with EazyBI’s fantastic support team, in order to configure specific data import capabilities into EazyBI. You’re welcome to use the logic and code provided at the end, if you face a similar situation.  

Applicable scenario: Service calls are handled by several team, so we need to measure the performance of each

You’re the customer support manager and your customer support staff consists of several teams, which we’ll call “workgroups.” A support ticket can “move” several times from one workgroup to another. You need to measure the performance of each workgroup separately, throughout the life cycle of the ticket. 

Also, during the support ticket lifespan, there may be times during which the ticket status may be on pause while waiting for a customer response. During these pause periods, you don’t want the time to be counted.

Your customer support runs on Jira Service Management (“JSM”) and you configured in Jira the custom field WORKGROUP, of the type “Group Picker (single group).”  At any given time, this field indicates the group which is  currently assigned to the issue.

But Jira Service Management alone cannot provide a granular level of detail within a support ticket lifespan; JSM’s native SLA are on the issue lifespan and cannot be configured to account per each WORKGROUP. The solution: use the EazyBI App, to extend the capability of Jira Service Management , and report performance metrics for each workgroup separately.

The complexities in this scenario

Generally, it’s possible to import into EazyBI data related to custom fields from the Jira history file, and there are many standard custom fields for which data import requires simply ticking a box. For importing data related to calculated custom fields, JavaScript code can be added to the custom field definition via EazyBI’s Advanced Settings. Some custom fields are “EazyBI-friendly,” but our specific scenario is extra complicated. Why?

In our story, WORKGROUP is a “Group Picker (single group)” type of field. So far so good – with the right definition its history can be imported into the EazyBI cube and can be used as a dimension. And when EazyBI imports, from Jira’s issue changelog, the sequence of value changes for the WORKGROUP custom field, ticket time for each workgroup can be calculated. 

But when tickets pass between workgroups during the ticket lifespan, various additional situations which require calculations become relevant, and these are not trivial to implement. Out-of-the-box EazyBI only calculates the total elapsed time between the timestamp when the WORKGROUP value was first entered, and the timestamp when the same WORKGROUP value was changed for the last time. Without custom fields, EazyBI cannot take into account the time elapsed while a ticket is in each individual workgroup. 

In our scenario, EazyBI needed the capability to track to which WORKGROUP value the issue is assigned at any point in time. For each workgroup, there may be zero, one, or multiple time periods that need to be aggregated. Also, a ticket may come back to the same workgroup more than once, which means that a ticket could be assigned the same WORKGROUP value more than once. 

We also needed the ability to identify whether the issue has ACTIVE status – not paused while waiting for customer response. In other words, we needed to measure only the ACTIVE time that elapsed since any workgroup value was assigned and NOW. 

The solution

The solution works as follows:

  1. Because a support ticket may bounce around between any of the workgroups until it’s closed, we added via EazyBI’s Advanced Settings a JavaScript code to create the custom field definition WORKGROUP which can be imported as dimension, properties and change records can be imported. 
  2. In Jira Service Management , when a support ticket is created, the WORKGROUP field must be assigned a value. 
  3. During the ticket lifespan, until its resolution (issue status is OPEN): The ticket is considered ACTIVE as long as it is NOT in a status of “Waiting for customer.” While a ticket is active, the performance parameters of its workgroup are being measured.

    While the ticket is “Waiting for customer,” we PAUSE measurement of its workgroup performance. In other words, the time period in which measurements are paused will not count toward SLA metrics. To account for active and paused time, we added via EazyBI’s Advanced Settings a JavaScript code to create DAYS IN WORKGROUP that can be imported as a measure and property.

To summarize: we capture and measure the support ticket events, and stratify them per workgroup, only for the time periods in which the JSM ticket is deemed to be active. 

Here’s how this metric is calculated:

  • For OPEN tickets:
    1. ISSUE AGE is the time elapsed between ticket creation and NOW.
    2. ACTIVE TIME in each workgroup; the time elapsed between the ticket creation and NOW, minus the time period during which it was paused (waiting for customer). 
    3. ACCUMULATED ACTIVE TIME across all workgroups: the total ACTIVE TIME the ticket spends in all workgroups, with the ability to drill down to a level of workgroup and specific time period.
  • For CLOSED tickets: 
    1. ACTIVE TIME and ACCUMULATED ACTIVE TIME work the same as for OPEN tickets. 
    2. ISSUE AGE is provided by existing EazyBI measures, so no special implementation is required.

Based on these metrics we could measure and create reports for:

  • TIME TO FIRST RESPONSE in each workgroup – the ACTIVE time elapsed from the assignment of an issue to a certain workgroup until the time a first comment was made to the customer in that same workgroup. 

Notes 

  • This measure can get complicated if, before the customer response arrives, the ticket is assigned to another workgroup. 
  • Another complication arises if the ticket status is changed to “Waiting for customer,” but there is no record of “comment to customer” in the JSM database. We assume that before going into “waiting for customer” status, there would be some communication with the customer.
  • Unless the issue was handled entirely within the same workgroup, we can’t rely on the definition of TIME TO FIRST RESPONSE set in JSM for reporting the performance on EazyBI. For example in JSM, if the ticket has a changed WORKGROUP value before the first customer comment, the trigger for “time to first response” will never occur. Therefore, JSM’s measured time to first response may be inaccurate. 
  • TIME TO RESOLUTION (only ACTIVE time) is the accumulated active time elapsed between a ticket creation and its assumed CLOSE status. 

Here are the two measures configured for us by the EazyBI support team, using Java script that was added into EazyBI via System Settings / Advanced Settings:

  • For resolved tickets: A measure that calculates the ACTIVE TIME for each WORKGROUP value the ticket was assigned. Once the ticket is resolved, the measure calculates the time between transition to the last workgroup value and the ticket’s CLOSED status.
  • For unresolved tickets: A measure that calculates the ACTIVE TIME elapsed between the last transition into a WORKGROUP value and NOW.

In addition to these predefined measures, we added two measures under Define new calculated measure, to allow queries of the EasyBI Cube with and without the time dimension.

Report Examples

  • Active time of all closed issues in a workgroup, by month

SLA reporting by EazyBI

How many active days issues that were closed in a certain month spent in a certain workgroup

 

  • Trending: Active time of all open issues in a workgroup in a certain month

    SLA trending report by EazyBITrending report visualizing per day the accumulated active issues in a workgroup in a certain month

     

Code snippets

Defining the Jira Service Management Workgroup custom field as an entity in the EazyBI Cube
[jira.customfield_NNNNN]
data_type = "string"
dimension = true
multiple_values = false
separate_table = true
changes = true
Days in Workgroup
/*This measure works with the TIME dimension. It accumulates the active time of the ticket in a workgroup AFTER a transition from one workgroup to another. It doesn’t count days in the current workgroup, unless the issue is resolved.*/

[jira.customfield_dfwkgr]
name = "Days in Workgroup"
data_type = "decimal"
measure = true
multiple_dimensions = ["Time","Workgroup"]
split_by = ","
javascript_code = '''
var workgroupNotset = true;
var statusHistory = new Array(); // array of trasitions for results
var dateTo = null; // end date
var assignee = null; // workgroup
var new_wkgr = null; // next workgroup
var status = ""; // status
var excludeStatus = ["Waiting for customer"];
var resolution = false;
var startFrom = issue.fields.created;
var new_status = ""; // next status (status to)
var duration = 0; // how long issue been assigned to one assignee or status
// get first Wowkrgroup
if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
  var histories = issue.changelog.histories;
  for (var i = 0; i < histories.length; i++) {
    var history = histories[i];
    if (history.items && history.items.length > 0) {
      for (var n = 0; n < history.items.length; n++) {
      var item = history.items[n];
        if (item.field == 'Workgroup' && workgroupNotset){ // current assighee if no history of assignee change
          if(item.fromString) {
            wkgr = item.fromString;
          } else {
            wkgr = "(no workgroup)";
          }
          workgroupNotset = false;
          break;
        }
      }
    }
  }
}
// code to add [...] to the workgroup name that is imported from Jira
if (workgroupNotset) {
    if(issue.fields.customfield_10305) {
        wkgr = "[" + issue.fields.customfield_10305 + "]"
    }
    else wkgr = "(no workgroup)"
}
// main  algorithm to calculate time between transitions
issue.changelog.histories.forEach(function(history){
  history.items.forEach(function(historyItem){
    if (historyItem.field == "Workgroup" || historyItem.field == "status" || historyItem.field == "resolution"){
              dateTo = history.created;
          if (historyItem.field == "Workgroup"){ // Workgroup change
              if(historyItem.fromString != null){ // actual Workgroup
                wkgr = historyItem.fromString;
              } else {
                wkgr = "(no workgroup)";
              }
              if(historyItem.toString != null){ // set next workgroup
                new_wkgr = historyItem.toString;
              } else {
                new_wkgr = "(no workgroup)";
              }
              if(excludeStatus.indexOf(status) == -1 && ! resolution) {
                  duration = (Date.parse(dateTo) - Date.parse(startFrom)) / 1000 / 60 / 60 / 24;
                  statusHistory.push(dateTo.toString().substr(0,10) + "," + wkgr + "," + duration);
              } 
              startFrom = dateTo;              
              wkgr = new_wkgr;
          }
          if (historyItem.field == "resolution") {
            if(historyItem.to){
            if(historyItem.from == null && excludeStatus.indexOf(status) == -1){
              duration = (Date.parse(dateTo) - Date.parse(startFrom)) / 1000 / 60 / 60 / 24;
                  statusHistory.push(dateTo.toString().substr(0,10) + "," + wkgr + "," + duration);
            }
            resolution = true;
            }
            else resolution = false;
          }
          if (historyItem.field == "status"){ // status change
                status = historyItem.fromString; // moves from statuss
                new_status = historyItem.toString; // to next status
              if(excludeStatus.indexOf(new_status) > -1 && ! resolution) {
                  duration = (Date.parse(dateTo) - Date.parse(startFrom)) / 1000 / 60 / 60 / 24;
                  statusHistory.push(dateTo.toString().substr(0,10) + "," + wkgr + "," + duration);
              }
              else if(excludeStatus.indexOf(status) > -1 && excludeStatus.indexOf(new_status) == -1 && ! resolution) {
              startFrom = dateTo;    
              }
              status = new_status;
          }
        }
  });
});
issue.fields.customfield_dfwkgr = statusHistory.join("\n");
issue.fields.customfield_wgr_t = Date.parse(startFrom)/1000
'''

Time of the first comment made after changing WorkGroup
//This measure is allowing the reporting on time to first response per workgroup.
//
[jira.customfield_commentdatebyWG]
name = "Comments date by Workgroup"
data_type = "date"
json_fields = ["comment"]
javascript_code = '''
if (issue.fields.comment && issue.fields.comment.comments
&& issue.fields.comment.comments.length > 0) {
var commentsarray = issue.fields.comment.comments;
for (var i = 0; i < commentsarray.length; i++) {
var commenthist = commentsarray[i];
if (new Date(commenthist.created).getTime()/1000 > 
  issue.fields.customfield_wgr_t) {
var lcomafterwkgr = commenthist.created
break;
}
};
}
if(lcomafterwkgr){
issue.fields.customfield_commentdatebyWG = lcomafterwkgr}
'''
Cumulated active days
--For tickets in previous and current workgroup (does not work with time dimension)

SUM(
Filter(
Descendants([issue].CurrentHierarchyMember,[Issue].[Issue]),
(
[Measures].[Open issues],
[Workgroup].DefaultMember
)>0
OR
(
[Measures].[Issues resolved],
[Workgroup].DefaultMember
)>0
),
Cache(
Sum({PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
[Measures].[Days in workgroup] --measure imported from javascript custom field
)
)
)
+
--for open issues in good status time since they went to good status till the end of month or till today for current month

Sum(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- filter open issues in period using issue properties Created date and Resolution date only
IsEmpty([Issue].CurrentMember.get('Resolved at'))
AND
[Measures].[Issue status]<> "Waiting for customer" --to exclude bad status doublcheck the name of status here
)
,
CASE WHEN
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember) --for current month
THEN
DateDiffDays(
timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
Now())
ELSE DateDiffDays( --for historical months
timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
[Time].CurrentHierarchyMember.NextStartDate)
END
END
)
Cumulative active days open issues - Time
--cumulative days in workgroup, works only with time dimension

CASE WHEN 
      DateafterPeriodEnd(
        'today',
        [Time].CurrentHierarchyMember.PrevMember) 
THEN
SUM(
  Filter(
    Descendants([issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Open issues]>0
    OR
    [Measures].[Issues resolved]>0
  ),
  Cache(
  Sum({PreviousPeriods([Time].CurrentHierarchyMember),
      [Time].CurrentHierarchyMember}, 
    [Measures].[Days in Workgroup] --measure imported from javascript custom field
  )
)  
)

END
+
--for open issues in good status time since they went to good status till the end of month or till today for current month
CASE WHEN 
[Measures].[Open issues] > 0 
AND
      DateafterPeriodEnd(
        'today',
        [Time].CurrentHierarchyMember.PrevMember) 
THEN
  Sum(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
      -- filter open issues in period using issue properties Created date and Resolution date only
      IsEmpty([Issue].CurrentMember.get('Resolved at'))
      AND
      --not to show anything that is covered already by Javascript
      DateBeforePeriodEnd(
        timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
        [Time].CurrentHierarchyMember) 
      AND
      [Measures].[Issue status]<> "Waiting for customer" --to exclude bad status doublcheck the name of status here
      )
      ,
    CASE WHEN
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0
    THEN
      CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember) --for current month
      THEN 
        DateDiffDays(
        timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
          Now())
      ELSE DateDiffDays( --for historical months
      timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
        [Time].CurrentHierarchyMember.NextStartDate)
      END
    END
  )
END


Cumulative active days of all issues (open &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp; resolved)
--cumulative days in workgroup, works only with time dimension

CASE WHEN 
      DateafterPeriodEnd(
        'today',
        [Time].CurrentHierarchyMember.PrevMember) 
THEN
SUM(
  Filter(
    Descendants([issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Open issues]>0
    OR
    [Measures].[Issues resolved]>0
  ),
  Cache(
  Sum({PreviousPeriods([Time].CurrentHierarchyMember),
      [Time].CurrentHierarchyMember}, 
    [Measures].[Days in Workgroup] --measure imported from javascript custom field
  )
)  
)

END
+
--for open issues in good status time since they went to good status till the end of month or till today for current month
CASE WHEN 
[Measures].[Open issues] > 0 
AND
      DateafterPeriodEnd(
        'today',
        [Time].CurrentHierarchyMember.PrevMember) 
THEN
  Sum(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
      -- filter open issues in period using issue properties Created date and Resolution date only
      IsEmpty([Issue].CurrentMember.get('Resolved at'))
      AND
      --not to show anything that is covered already by Javascript
      DateBeforePeriodEnd(
        timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
        [Time].CurrentHierarchyMember) 
      AND
      [Measures].[Issue status]<> "Waiting for customer" --to exclude bad status doublcheck the name of status here
      )
      ,
    CASE WHEN
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0
    THEN
      CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember) --for current month
      THEN 
        DateDiffDays(
        timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
          Now())
      ELSE DateDiffDays( --for historical months
      timestamptodate([Issue].CurrentMember.get('Last workgroup timestamp')),
        [Time].CurrentHierarchyMember.NextStartDate)
      END
    END
  )
END


time in current Workgroup (counted from last change)
DateDiffDays(
  TimestampToDate(
    [Measures].[Last workgroup timestamp due] /
    [Measures].[Issues due]
  ),
  Now()
)*
[Measures].[Issues due]