Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
2 of 4 tasks
cth84 opened this issue Aug 1, 2023 · 6 comments
Closed
2 of 4 tasks

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

cth84 opened this issue Aug 1, 2023 · 6 comments
Assignees
Labels
error:unforced type:bug Something is broken or incorrect update_type:models Primary focus requires model updates

Comments

@cth84
Copy link
Contributor

cth84 commented Aug 1, 2023

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.
@cth84 cth84 added the bug Something isn't working label Aug 1, 2023
@fivetran-joemarkiewicz
Copy link
Contributor

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

), 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.

@cth84
Copy link
Contributor Author

cth84 commented Aug 2, 2023

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

@fivetran-joemarkiewicz
Copy link
Contributor

fivetran-joemarkiewicz commented Aug 2, 2023

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 😄

@cth84
Copy link
Contributor Author

cth84 commented Aug 4, 2023

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

@fivetran-joemarkiewicz
Copy link
Contributor

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!

@fivetran-joemarkiewicz fivetran-joemarkiewicz added type:bug Something is broken or incorrect status:scoping Currently being scoped update_type:models Primary focus requires model updates and removed bug Something isn't working labels Aug 7, 2023
@fivetran-joemarkiewicz fivetran-joemarkiewicz self-assigned this Aug 7, 2023
@fivetran-jamie fivetran-jamie self-assigned this Oct 3, 2023
@fivetran-jamie fivetran-jamie added status:in_review Currently in review and removed status:scoping Currently being scoped labels Oct 3, 2023
@fivetran-reneeli
Copy link
Contributor

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
error:unforced type:bug Something is broken or incorrect update_type:models Primary focus requires model updates
Projects
None yet
Development

No branches or pull requests

4 participants