[Bug] Agent Work Time in Business Minutes (zendesk__ticket_metrics)

Is there an existing issue for this?

I have searched the existing issues

Describe the issue

We are evaluating the inclusion of schedules with v0.11.0 and noticed that agent_work_time_in_business_minutes for some tickets is showing higher than the full_resolution_business_minutes on the zendesk__ticket_metrics model. In these situations, the number is also higher than our existing production values using the v0.10.2 of the package.

We dug in and it looks like the issue is within the int_zendesk__ticket_work_time_business model.

The intercepted_periods cte which joins across the weekly_periods and schedule ctes seems to be where the issue takes place.

It is joining the status the ticket was in, going into the weekend, to both the week before (a holiday week) and the week after (non-holiday) and then counting that time twice (or multiple times if ticket was in a status over multiple schedules changes).

Relevant error log or model output

No response

Expected behavior

If a ticket status spans a weekend, in which the schedule changes, I would expect the ticket's status time to be properly aligned/split to each of those schedules.

dbt Project configurations

n/a

Package versions

packages:

package: dbt-labs/dbt_utils
version: 1.0.0
package: fivetran/fivetran_log
version: 0.7.4
package: fivetran/jira
version: 0.12.2
package: fivetran/github
version: 0.7.0
package: dbt-labs/codegen
version: 0.9.0
package: fivetran/zendesk
version: 0.11.0

What database are you using dbt with?

bigquery

dbt Version

Core:

installed: 1.3.0

Plugins:

bigquery: 1.3.0

Additional Context

This is a simple example ticket, that was opened at the end of a holiday week on Friday, after our business hours, then worked/solved on the following Monday.

The time it was open on Monday, seems to be applied twice, once to the prior schedule and then the current.

Ticket 110116-example.xlsx

Are you willing to open a PR to help address this issue?

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

Hi @cth84 thanks for opening this issue and digging into the nuances you are experiencing with the new schedule_holiday addition. There for sure seems to be something going awry that we will want to address.

I appreciate you digging into identifying the source of the issue seems to stem from the join in the int_zendesk__ticket_work_time_business model

dbt_zendesk/models/agent_work_time/int_zendesk__ticket_work_time_business.sql

Lines 84 to 102 in 31d6a5f

    
           ), intercepted_periods as ( 
        
               select  
        
                 weekly_periods.ticket_id, 
        
                 weekly_periods.week_number, 
        
                 weekly_periods.schedule_id, 
        
                 weekly_periods.ticket_status, 
        
                 weekly_periods.ticket_week_start_time, 
        
                 weekly_periods.ticket_week_end_time, 
        
                 schedule.start_time_utc as schedule_start_time, 
        
                 schedule.end_time_utc as schedule_end_time, 
        
                 least(ticket_week_end_time, schedule.end_time_utc) - greatest(weekly_periods.ticket_week_start_time, schedule.start_time_utc) as scheduled_minutes 
        
               from weekly_periods 
        
               join schedule on ticket_week_start_time <= schedule.end_time_utc  
        
                 and ticket_week_end_time >= schedule.start_time_utc 
        
                 and weekly_periods.schedule_id = schedule.schedule_id 
        
                 -- this chooses the Daylight Savings Time or Standard Time version of the schedule 
        
                 and weekly_periods.status_valid_ending_at >= cast(schedule.valid_from as {{ dbt.type_timestamp() }}) 
        
                 and weekly_periods.status_valid_starting_at < cast(schedule.valid_until as {{ dbt.type_timestamp() }})

It is peculiar how we did not edit this model directly in the schedule holiday update. The only update that would impact this model are the changes to the int_zendesk__schedule_spine model. Do you by chance see any duplicates or anything that seems off for this schedule_id for the days the tickets are showing differing results?

I am curious about the intercepted_periods results you are seeing as well. Would you be chance be able to share a sample of the ticket_id in question for the results of the intercepted_periods cte (you can grab the sql from your target/compiled folder and modify the sql for the model to only output the intercepted_period cte.

The above will help us identify and resolve the issue you are seeing. Thanks again for raising this to our attention and for working with us to resolve the issue.

Thanks for the assistance Joe!

It is peculiar how we did not edit this model directly in the schedule holiday update. The only update that would impact this model are the changes to the int_zendesk__schedule_spine model. Do you by chance see any duplicates or anything that seems off for this schedule_id for the days the tickets are showing differing results?

Nothing appears to be off with the records in int_zendesk__schedule_spine. We did just transition to Zendesk in April though, so unsure if we've got enough historical changes going on to evaluate that. The schedules I included in the previous doc are those around the time in question for this example ticket, coming from int_zendesk__schedule_spine.

This issue only appears to be happening on tickets that span a weekend, in which we change schedules (holiday week to non-holiday week or vice versa)

If a ticket started and finished within the week (holiday or not), no issue
If a ticket started after our last holiday week this year, so far (July 4th, week ending July 8th) we don't appear to see this issue (self contained all in one schedule's valid from, valid_until)

I am curious about the intercepted_periods results you are seeing as well. Would you be chance be able to share a sample of the ticket_id in question for the results of the intercepted_periods cte (you can grab the sql from your target/compiled folder and modify the sql for the model to only output the intercepted_period cte.

Here are the raw results, for this ticket, from the intercepted_period cte:
Intercepted_Periods - 110116.xlsx

Thanks for providing these additional details this is extremely helpful. I can see in the spreadsheet you provided that it does look like the open state of the ticket is in fact being counted twice for the second week.

Just to confirm for this ticket we should essentially see 39.74 working minutes as ticket was opened right at the end of the week and was in a new state for 3.91 minutes before then being opened over the weekend until then being closed 35.83 minutes in the next week. Correct? If so, then it seems we just need to deal with this duplicate row that is occurring in the following week which is a new schedule.

I believe this will be better triaged further over a live call if you are open to meeting with me? If so, you can schedule time with myself and my team via our Calendly. Let me know if these times don't work for you and we can schedule a time that works best for you.

Thank you again and I look forward to resolving this issue you are seeing 😄

Just to confirm for this ticket we should essentially see 39.74 working minutes as ticket was opened right at the end of the week and was in a new state for 3.91 minutes before then being opened over the weekend until then being closed 35.83 minutes in the next week. Correct? If so, then it seems we just need to deal with this duplicate row that is occurring in the following week which is a new schedule.

Correct!

I think the duplication is occurring because there doesn't appear to be any logic to split ticket status time across multiple schedules if it happens to cross over.

Here is a more extreme example of a ticket we had open for a 2 months (and it spanned 5 schedule changes due to holidays). Comparing its agent work time to full resolution in business minutes, we see it grow by a factor of ~5.

Ticket 8510 - example.xlsx

I grabbed some time next week for us,
Thanks

Thanks so much for sharing an additional example and for scheduling time next week to discuss in more detail. I look forward to chatting then and coming to a resolution to be applied in the package!

Hi @cth84! Thank you again for working with us through this issue. This has been included in our latest release of the package and as such we'll be closing this out!

	), intercepted_periods as (

	select
	weekly_periods.ticket_id,
	weekly_periods.week_number,
	weekly_periods.schedule_id,
	weekly_periods.ticket_status,
	weekly_periods.ticket_week_start_time,
	weekly_periods.ticket_week_end_time,
	schedule.start_time_utc as schedule_start_time,
	schedule.end_time_utc as schedule_end_time,
	least(ticket_week_end_time, schedule.end_time_utc) - greatest(weekly_periods.ticket_week_start_time, schedule.start_time_utc) as scheduled_minutes
	from weekly_periods
	join schedule on ticket_week_start_time <= schedule.end_time_utc
	and ticket_week_end_time >= schedule.start_time_utc
	and weekly_periods.schedule_id = schedule.schedule_id
	-- this chooses the Daylight Savings Time or Standard Time version of the schedule
	and weekly_periods.status_valid_ending_at >= cast(schedule.valid_from as {{ dbt.type_timestamp() }})
	and weekly_periods.status_valid_starting_at < cast(schedule.valid_until as {{ dbt.type_timestamp() }})