[Bug] Numbers not matching between Agent Replies and count_agent_comments about dbt_zendesk

[fivetran-joemarkiewicz]

Hi @gui-brandalley thanks for raising this issue. I believe I understand the source of this discrepancy is due to the following line in our package code

dbt_zendesk/models/zendesk__ticket_metrics.sql

Line 103 in e48922b

coalesce(ticket_comments.count_agent_comments, 0) as total_agent_replies,

As you can see we are calculating the number of agent comments as any agent comment regardless if it is internal or public. This is not consistent with what you are sharing how Zendesk counts this metric. Instead, I would assume this is being calculated by leveraging the public agent comments only. Would you be able to confirm if this seems to be the case for your tickets in question. If so, we can move forward with applying a fix to the models to account for this discrepancy.

[fivetran-avinash]

Hi @gui-brandalley 👋 ! We believe we have solved for this issue and will merge this into our next release in the coming days.

In the meantime, if you'd like to test this fix you can utilize our current branch where we are making the necessary changes. Here is the git config you'll need to use. Let us know if you have any questions!

packages:
  - git: https://github.com/fivetran/dbt_zendesk.git
    revision: bug/fixing-agent-replies-definition
    warn-unpinned: false 

[fivetran-joemarkiewicz]

These updates have since been included in the latest v0.11.0 release of the dbt_zendesk package! This should be live on the dbt hub at the top of the hour. As such I will close this issue.

Please feel free to comment here or open a new issue if you see your initial issue persisting. Thanks!

[gui-brandalley]

Hi @fivetran-joemarkiewicz
Apologies for the delay, I have just noticed some cases where the count_agent_comments is still 0 with version 0.11.0.
I can't find any clue about what could be the reason but if you need any more details please let me know.

Kind regards,
Guillaume

[fivetran-avinash]

Hi @gui-brandalley ! I believe we changed the count_agent_replies logic in this ticket but not count_agent_comments. Currently the logic holds in our model for agent comment count as:

        sum(case when commenter_role = 'internal_comment'
            then 1
            else 0
                end) as count_agent_comments

Is this the logic you'd expect? If it is, could you send us some cases of tickets that aren't fitting this logic? If not, could you let us know what logic you would be expecting?

[gui-brandalley]

Hi @fivetran-avinash ,
I can't find any count_agent_replies field in the zendesk_ticket_metrics model. Are you talking about total_agent_replies?

[fivetran-avinash]

HI @gui-brandalley , sorry, yes, total_agent_replies is what I meant.