[Feature] New hours to solve measure

Is there an existing feature request for this?

I have searched the existing issues

Describe the Feature

My stakeholder wants to track a new definition of MTTR (mean time to resolution).
Today, we track full resolution time (i.e. total calendar days from ticket creation date to final solved date (i.e. latest solved date when ticket is reopened).

We actually only want to track the number of hours the ticket was not in the closed or solved status i.e. only tracking total time in open, pending, on hold. We call this Avg. Hours to Solve internally in our Looker instance.

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

Yes.
Yes, but I will need assistance and will schedule time during your office hours for guidance.
No.

Anything else?

No response

@fivetran-jessicacherny thanks for opening this feature request, this addition of a new "hours to solve" measure makes sense as an impactful addition to our end ticket_metrics model.

To confirm, I would imagine this solution would involve a new metric that expands upon the requester_wait_time_in_minutes metric. This expansion would include the addition of the pending status.

Current

dbt_zendesk/models/agent_work_time/int_zendesk__ticket_work_time_calendar.sql

Lines 13 to 14 in 62df5b5

    
           case when status in ('new', 'open', 'hold') then status_duration_calendar_minutes 
        
               else 0 end as requester_wait_time_in_minutes,

Proposed Update

case when status in ('new', 'open', 'hold') then status_duration_calendar_minutes 
     else 0 end as requester_wait_time_in_minutes, 
case when status in ('new', 'open', 'hold', 'pending') then status_duration_calendar_minutes 
     else 0 end as solve_time_in_minutes,

Similarly I know there are custom statuses that Zendesk allows users to create. So maybe it would make more sense to take the following approach.

case when status in ('new', 'open', 'hold') then status_duration_calendar_minutes 
     else 0 end as requester_wait_time_in_minutes, 
case when status not in ('solved', 'closed') then status_duration_calendar_minutes 
     else 0 end as solve_time_in_minutes,

Let me know what your thoughts are and if this matches what you are looking to incorporate into the package?

lgtm, @fivetran-joemarkiewicz!

Hi @fivetran-jessicacherny, I'll be working on this task this sprint and should have it ready for deployment by the end of it!

@fivetran-jessicacherny this is now live in the latest v0.13.0 release of the Zendesk package. Thanks again for working with us to incorporate these changes in the package!

	case when status in ('new', 'open', 'hold') then status_duration_calendar_minutes
	else 0 end as requester_wait_time_in_minutes,