[Bug] Numbers not matching between Agent Replies and count_agent_comments

Is there an existing issue for this?

I have searched the existing issues

Describe the issue

Hi,

While checking the numbers between count_agent_comments using the dbt model in zendesk__ticket_metrics table and the number of agent replies in Zendesk explore, I can see the numbers are not matching.

Do you know why there is such a big discrepancy in numbers?

Relevant error log or model output

No response

Expected behavior

I would expect the numbers from count_agent_commands, count_public_agent_comments or total_agent_replies to match the numbers I get for agent replies in Zendesk explore but none of them are matching.

dbt Project configurations

vars:

Needed to keep the surrogate_key function treating null as empty strings following upgrade of db-utils

surrogate_key_treat_nulls_as_empty_strings: true
using_domain_names: False #Disable if you are not using domain names
using_organization_tags: False #Disable if you are not using organization tags
zendesk__ticket_passthrough_columns: [custom_contact_reason,custom_coupon_code,custom_order_number,custom_carrier,custom_client_contact_reason_no_order,custom_client_contact_reason_order]

Package versions

packages:

package: dbt-labs/codegen
version: ['>=0.7.0', <0.10.0]
package: dbt-labs/dbt_utils
version: ['>=0.8.0', '<2.0.0']
package: fivetran/zendesk
version: [">=0.10.0", "<0.11.0"]

What database are you using dbt with?

bigquery

dbt Version

dbt Version: 1.4

Additional Context

No response

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.

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.

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

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!

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

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?

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?

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