[Feature] Update Zendesk SLA policies to look at historical agents

[jackiexsun]

Is there an existing feature request for this?

• [X] I have searched the existing issues

Describe the Feature

Currently the way the zendesk package calculates SLA policies is to check whether the user is an "agent" or "admin" role to meet the SLA. We are starting to deactivate former employees in Zendesk since they take up licenses so they won't be an "agent" or "admin" role anymore. This will retroactively impact our SLA policy calculations. Can we modify the package to use another way to determine if the user is an internal company user (like looking at the email domain) or enable the ability to use a zendesk user history table so we can see if the user was ever an "agent" or "admin"?

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.
• [X] No.

Anything else?

No response

[jackiexsun]

Hi @fivetran-jamie @fivetran-joemarkiewicz are we able to prioritize this soon? IT has started deprovisioning Zendesk users so I expect our metrics will change because of this.

[fivetran-joemarkiewicz]

Hey @jackiexsun I had planned to incorporate this into the current sprint, but had to deprioritize due to a different Zendesk ticket. I am planning to fold this into the upcoming sprint. We can chat with you further once someone from my team picks this up in the next sprint!

[cpmclaughlin]

@fivetran-joemarkiewicz I'm also very interested in this package handling former agents as agents rather than end users. Please apply this fix to reply times and resolution times as well as to SLAs. Would love to know when you'll be working on this and when to expect it to be resolved when possible. Thanks!

[fivetran-jamie]

@cpmclaughlin currently working on this! for your own case, would you only look at email domains, or would you want this additional internal-user criteria to be more flexible (ie reference other USER columns)?

[cpmclaughlin]

@fivetran-jamie so excited to hear that!!! More flexible internal-user criteria would be most helpful. Unfortunately, in our case email domain alone would not work. What I'd been considering using before I found that you all were about to work on this was

where ((signature is not null and signature is distinct from '') or (alias is not null and alias is distinct from ''))
        and (restricted_agent = TRUE or suspended = TRUE)
        and (email ilike '%modernhealth%' or email is null)

Let me know if you have any additional questions and thank you for working on this!!! Will be incredibly helpful to us.

[fivetran-jamie]

thank you @cpmclaughlin this is super helpful! figured email domain would be a popular route but not cover everything for some folks

i've added a zendesk__internal_user_criteria variable that would essentially be a where clause defining other criteria for tagging former agents, so in your case you'd configure that like

# dbt_project.yml
vars:
  zendesk__internal_user_criteria: "((signature is not null and signature is distinct from '') or (alias is not null and alias is distinct from '')) and (restricted_agent = TRUE or suspended = TRUE) and (email ilike '%modernhealth%' or email is null)"

i have a working branch if you'd like to try it out! the changes should be reflected in zendesk__sla_policies, zendesk__ticket_metrics, and zendesk__ticket_summary

packages:
  - git: https://github.com/fivetran/dbt_zendesk.git
    revision: feature/tag-former-agents
    warn-unpinned: false

@jackiexsun would you have bandwidth to try this branch out on our own Zendesk data as well?

[cpmclaughlin]

@fivetran-jamie will I (or rather, my data eng counter part) be able to define the variable in using the fivetran quickstart data models or only if we were using the transformations for dbt core? Right now, we're only using the quickstart for this instance of zendesk because we're already using the transformations for dbt core for another instance we have. If the new variable you're working on only works with transformations for dbt core, is it possible to set that up for multiple instances now? I think I saw a ticket for that too at some point. Thanks!

[jackiexsun]

@fivetran-jamie yes I can test this out!

[fivetran-jamie]

@cpmclaughlin Darn yeah you would only be able to define the variables if using transformations with dbt core, not with Quickstart.

But if the overall goal is to run the package on multiple zendesk instances, perhaps this blog post would be of use to you.

We do have a couple of tickets open to natively run the package on multiple sources and are heavily considering adding this functionality, but no firm timeline yet

[jackiexsun]

Hi @cpmclaughlin is this the criteria for identifying former agents that you landed on? I'm trying to do something similar for Fivetran, and got to

where suspended and email like '%@fivetran.com'

but it's not 100% accurate. Wondering if you got to a more accurate criteria? I asked Zendesk support about this problem and they were not helpful :/

@fivetran-jamie so excited to hear that!!! More flexible internal-user criteria would be most helpful. Unfortunately, in our case email domain alone would not work. What I'd been considering using before I found that you all were about to work on this was

where ((signature is not null and signature is distinct from '') or (alias is not null and alias is distinct from ''))
        and (restricted_agent = TRUE or suspended = TRUE)
        and (email ilike '%modernhealth%' or email is null)

Let me know if you have any additional questions and thank you for working on this!!! Will be incredibly helpful to us.

[fivetran-jamie]

this has been rolled out in v0.10.1 of the source package! as a minor change, the transform package should automatically pick it up! cc @cpmclaughlin