BUG - SLA_policies model doesn't have correct is_sla_breach

[jackiexsun]

Are you a current Fivetran customer? I work at Fivetran :)

Describe the bug zendesk__sla_policies doesn't always correctly identify SLA breaches (missed SLAs).

Steps to reproduce For example ticket_id = 18717

select *
from `digital-arbor-400.dbt_pkg_zendesk.zendesk__sla_policies`
where metric = 'first_reply_time' and sla_policy_name in ('Enterprise SLA - Support', 'Standard/Starter SLA - Support') and is_sla_breach and ticket_id = 18717

is breached but in Zendesk, it is achieved

Other ticket_id's where is_sla_breach is true when it should be false (these are all "first_reply_time" metrics) 19505 19539 19544

Expected behavior

Project variables configuration

name: 'prj_production'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'fivetran'

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
    - "target"
    - "dbt_modules"

on-run-start:
    - '{{create_udfs()}}' # Hook that executes macro to create UDFs on run start (look inside the macros folder)

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models
models:
  +persist_docs:
      relation: true
      columns: true
  prj_production:
      materialized: view
  zendesk:
    +schema: 'dbt_pkg_zendesk'
  zendesk_source:
    +schema: 'dbt_pkg_zendesk'

# This section is used for variables for dbt packages
vars: 
  zendesk_source:
    zendesk_schema: zendesk_new
    using_schedules: false        # Disable if you do not have the schedule and ticket_schedule tables, or if you do not want metrics reported in business hours
  zendesk:
    using_schedules: false        # Disable if you do not have the schedule and ticket_schedule tables, or if you do not want metrics reported in business hours

Package Version

packages:
  - package: fishtown-analytics/dbt_utils
    version: 0.6.6
  - git: https://github.com/fivetran/dbt_zendesk.git
    # TODO once the production version zendesk package incremental strategy is verified, we need to swap back to that version
    revision: fix/no-incremental-field-history
    warn-unpinned: false
  - package: fivetran/github
    version: [">=0.2.0", "<0.3.0"]

Warehouse

- [ x] BigQuery - [ ] Redshift - [ ] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context** **Screenshots**

Please indicate the level of urgency We're currently recalculating SLA policy achievement metrics ourselves, but we'd like to transition to use the package if possible. We'll transition to use it as soon as it is accurate.

Are you interested in contributing to this package?

• [ ] Yes, I can do this and open a PR for your review.
• [ ] Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• [ x] No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[fivetran-joemarkiewicz]

Hi @jackiexsun 👋

Thanks so much for calling this issue out and providing great details as to why it looks like the is_sla_breach metric is off for a few tickets.

Currently, the first_reply_time sla policy looks to ensure the first agent or admin response is within the policy designated. I was able to look into the tickets you highlighted above (perks of working at the same company 😸) and saw that it looks like a user that is shown as is_suspended was the employee first replying to all of these tickets.

Additionally, it looks like the employee has a role of end-user which makes sense why the package is not counting their first response, as the package is interpreting this individual as a customer (end-user). Further, I did look at the comments this user made to the tickets and see they are in fact within the sla policy. However, the fact that the comments were made by an end-user is what is throwing the metric off.

There are two scenarios I would like to chat about before tackling any next steps:

• Was this individual at one point an agent/admin and has since been updated to be an end-user now?
  • If this is the case then maybe it would be relevant to put a feature request in at the connector level to provide a history feature for the user table.
  • Otherwise, I am struggling to find a way to dynamically determine what the users role was at the time of the ticket.

OR

• Was this individual always an end-user and there are instances where we will want to account for end-user comments as long as they are associated with the parent organization?
  • In this case I can query the user table and see the individual is listed as a part of the main org, but are still shown as an end-user
  • This would require quite a bit of hacky updates to the package and I have a suspicion the user wasn't originally listed as an end-user.

Thoughts?

[jackiexsun]

Hey @fivetran-joemarkiewicz! Thanks for looking at this. You're right, we are having an issue for the user role specifically for "Sara Leon" being set to an end-user. Apparently her user was deleted from Zendesk so IT has been trying to recover that. I will follow up with IT on that. That being said, there are tickets where the first response was by an Agent/Admin, and they are still marked as being achieved but are "breached" in Zendesk. Tickets IDs: 16540 24985 26717

[fivetran-joemarkiewicz]

@jackiexsun Okay great that makes sense that it seems to be a misconfiguration of the users role for those tickets.

I just looked into the other tickets and it looks like for 16540 and 24985 the SLA was in fact achieved. For example, the ticket 24985 has an first response sla set at 2020-12-12 18:34:06 UTC with a breach time of 2020-12-13 02:34:06 UTC. I can then see the agent/admin first responded at 2020-12-12 18:36:04 UTC showing only 2 minutes of lapsed time occurred to achieve the sla. Does that match your finding?

Further, I have noticed that 26717 does in fact show as achieved, but the lapse time is clearly over the sla... let me look into this one a bit more.

[jackiexsun]

Further, I have noticed that 26717 does in fact show as achieved, but the lapse time is clearly over the sla... let me look into this one a bit more.

Thanks!

Sorry, I don't think I was communicating very clearly. The ticket IDs 16540 24985 are ticket IDs where the package says they are achieved but in Zendesk, they are breached.

26717 is shown as achieved in the package (which is correct as that also matched in Zendesk).

Ticket ID 22492 is another example of a ticket being marked as achieved in the package, but breached in Zendesk.

[fivetran-joemarkiewicz]

@jackiexsun I was able to look into 26717 and found out why the package sla_elapsed_time is 6251, but still shows as Achieved in Zendesk and the package.

It looks like the end-user for this ticket accidentally hit the solved button instead of submit when replying to a post. Because of this, the SLA was technically achieved because the ticket was solved before the sla was breached and a new sla was not applied after this.

However for 16540, 24985, and 22492 looking at the ticket first reply comments following the sla applied at time they seem to actually be within the sla window? I would be interested to find out how Zendesk is determining why these tickets are in fact breach events?

I'd be happy to chat with you live about this and show you what I am seeing for these tickets 😃

[fivetran-joemarkiewicz]

Hey @jackiexsun it was great chatting today! After we discussed how there may be a discrepancy with how Zendesk is calculating these metrics I decided to do some more digging into what publicly available docs are there on how Zendesk is calculating the first_reply_time sla achieved/breach events. After doing some digging I found Zendesk's documentation on this matter.

Like you had brought up on the call, it seems Zendesk will look at the ticket created date of the ticket for the first_reply_time, even if the first_reply_time sla was applied well after the ticket was created. I created a working branch and did a minor change (see below) which accounts for this. I have tested the branch on the four tickets we were chatting about above and found they match Zendesk's reporting of being breached/achieved respectively.

https://github.com/fivetran/dbt_zendesk/blob/547a9623a73704d5fdeb1f38a9290167e1e4dbe4/models/sla_policy/int_zendesk__sla_policy_applied.sql#L30

It would be great if you would be able to use the below packages.yml in your local environment and let me know if it looks like this remedies the discrepancy you have been seeing between this package and Zendesk.

packages:
    - git: https://github.com/fivetran/dbt_zendesk.git
      revision: bugfix/sla-first-reply-update
      warn-unpinned: false

Let me know! Thanks 😃

[fivetran-joemarkiewicz]

@jackiexsun thanks so much for your raising this issue and for helping come to a conclusion on the resolution within PR #52 😄.

These changes are now live within the latest v0.7.1 release of the zendesk package. Please feel free to open another issue if you encounter any other questions, or want to discuss this ticket further.

Thanks again!