[Bug] Comments from former employees are classified as an "external_comment", throwing off downstream metrics

[brian-robillard1]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

This issue is manifesting in zendesk__ticket_metrics.first_reply_time_calendar_minutes being null for tickets where the agent replied.

The underlying issue is stg_zendesk__user.role is end-user for former employees.

Tracing this issue from start to finish.

1. zendesk__ticket_metrics.first_reply_time_calendar_minutes is null
2. int_zendesk__ticket_reply_times_calendar.first_reply_time_calendar_minutes is null
3. int_zendesk__ticket_reply_times has a left join where this happens:

left join ticket_public_comments as agent_comments
    on agent_comments.ticket_id = end_user_comments.ticket_id
    and agent_comments.commenter_role = 'internal_comment'
    and agent_comments.valid_starting_at > end_user_comments.end_user_comment_created_at

In this join, in order for the query to return a value for agent_responded_at, the agent_comments.commenter_role = 'internal_comment'.
4. This is set on int_zendesk__comments_enriched.commentor_role
5. This traces back to STG_ZENDESK__USER, where the user_id in question is listed as a end-user.

Relevant error log or model output

No response

Expected behavior

I would expect the model to account for this user being an employee at the time of response.

dbt Project configurations

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

vars:
  zendesk_source:
    zendesk_database: production
    zendesk_schema: goat_zendesk

Package versions

packages:
  - package: fivetran/zendesk
    version: [">=0.9.1", "<=0.9.1"]

What database are you using dbt with?

snowflake

dbt Version

1.1.0

Additional Context

I am not entirely sure this is a modeling issue ... maybe there's something that can be set upstream in zendesk to help resolve.

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.

[fivetran-joemarkiewicz]

Hi @brian-robillard1 thanks so much for opening this Bug report.

This is actually something we have come across before, and even see with our own internal implementation. As you mentioned above, this goes back to the user source table and the fact that it does not track historical changes. Therefore, we are unable to see what the users role was from a previous time. 😞

This could possibly be solved by snapshotting (however that is not something we would cover in this package), but I believe the best solution is for this to be addressed directly within the Zendesk connector. There is a Feature Request where someone is requesting a user history table. If this were implemented, we would be able to update the Zendesk dbt package models to account for the changing roles.

I would recommend either commenting in that existing FR or open a new one with any additional details. Unfortunately we cannot address this issue within the package, but in my opinion an upsteam update is the best option for resolving this.

[fivetran-joemarkiewicz]

Jumping back into this thread as it has gone a bit stale. As I mentioned in my previous response, this is likely best addressed by opening a feature request within the connector itself to allow the user table to retain historical records. If this is possible, then the package will be able to track the changes in role without needing snapshotting logic within the dbt package.

As a result, I will mark this issue as blocked and close it since we are unable to address the issue for the time being. I believe a possible future state solution is an enhancement to this package to take advantage of historical user data if that is available via the connector.