BUG - First Reply Time metrics have mismatches

[alexei-colvin]

Are you a current Fivetran customer?
Yes. My name is Alexei, Analytics Engineer working at Colvin.

Describe the bug
The metrics First Reply Time for both, business hours and calendar hours, are having different numbers compared to the Fivetran UI for the same ticket ids.

Steps to reproduce

1. Go to Zendesk queries editor. Select ticket_id as row, select First Reply Time - Business Hours and First Reply Time - Calendar hours as metric.
2. Go to the dbt output table zendesk__ticket_metrics and query for the same metrics and the same ticket_ids.
3. The numbers for First Reply Time don't match.

Expected behavior
The numbers of the table zendesk__ticket_metrics should match with the Zendesk UI numbers.

Project variables configuration

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

profile: 'customer_experience'

source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

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

vars:
  zendesk:
    using_organization_tags: False
  zendesk_source:
    zendesk_database: thecolvin-data
    zendesk_schema: fivetran_zendesk


models:
  dbt_customer_experience:
    sources:
        materialized: ephemeral
    staging:
        schema: staging

  zendesk:
    +schema: staging    
    intermediate:
      +schema: staging
    sla_policy:
      +schema: staging
      materialized: view
    ticket_history:
      +schema: staging
      materialized: view
  zendesk_source:
    +schema: staging
    materialized: view

Package Version

packages:
  - package: fivetran/zendesk
    version: 0.7.0

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
In the screenshots you can see the mismatches and the workaround to fix the problem in calendar hours. But we need also a solution for Business hours.

Screenshots

Please indicate the level of urgency
The metrics are not matching with the Zendesk UI. The stakeholders are being affected in analysis and decision making.

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.
• 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 @alexei-colvin thanks so much for opening this issue.

It was great chatting with you the other day and I am looking into this discrepancy as we speak. I will post back here once I have an update and a branch for you to test out. 😄

[fivetran-joemarkiewicz]

Hi @alexei-colvin! I hope you had a great weekend.

I was able to investigate these discrepancies a bit more and found some ticket nuances within Zendesk the package was not accounting for. Would you be able to test the below branch version of the package and let me know if this resolves the calendar first reply times in question?

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

Once you replace your existing version of the package dependency with the version above, you can run dbt deps and then run your zendesk models and identify if the issues still persist. Let me know if have any questions!

[alexei-colvin]

Hello @fivetran-joemarkiewicz I hope you had a great weekend too! Thank you for working in this issue, and for the productive chatting last week!

Regarding this revision, I had tried and yes, the first_reply_time_calendar_minutes metric now match for all the tickets, super! There is still a lot of difference in the metric first_reply_time_business_minutes (more than 1000 minutes for a single ticket). This metric is also very important for our analysis. Are you going to do something regarding this one too?

Thanks for everything.

[fivetran-joemarkiewicz]

@alexei-colvin that's great to hear!

In regards to the first_reply_time_business_minutes I will most likely not (but will let you know if I find anything in my investigations that will result in an update) be updating this metric to reflect the Zendesk UI as our opinion is that this does not accurately capture the true length of time when tickets are exchanging schedules. However, I will put together a write-up on how we have defined our transformation logic so you may see why we have made our decision.

I will include this write-up in the next release, but will also share it here so you may chat about it more before the next release.

[alexei-colvin]

hello @fivetran-joemarkiewicz Thanks! I would really appreciate it if you could explain to me what's going on below, to understand why you think the package's results are better than Zendesk's regarding business times.

[fivetran-joemarkiewicz]

Hi @alexei-colvin apologies for the delayed response. Last week was Thanksgiving and I was not able to complete the explanation. I will post back within this Issue and Issue #54 with our DECISIONLOG and also will plan to include the DECISIONLOG.md within the package so others may understand the reason for our transformation logic.

I hope to respond back here by tomorrow with the completed DECISIONLOG 😄

[fivetran-joemarkiewicz]

@alexei-colvin thank you again for you patience with me putting together the DECISIONLOG.md for the business time metrics. This was a lot more intensive to document than I was initially intending haha

You can see the full writeup of this decision logic within the DECISIONLOG I have in the development branch. Also, I decided to add a variable that should allow you to override the decision logic and use the Zendesk logic instead if you wish. However, this will not be included as a functionality within the next release of the dbt package and is instead being provided so you may understand the differences between the package output and the Zendesk reports.

To leverage this variable you can add the below config to you dbt_project.yml

vars:
  using_zendesk_business_metrics_logic:    True         #False by default

Let me know if you have any questions when reading through my write-up!

[fivetran-joemarkiewicz]

Hi @alexei-colvin just wanted to check in if you had a chance to read through the DECISIONLOG above and if it helps answer your question about the business metrics differences between the dbt package and Zendesk?

[fivetran-joemarkiewicz]

Hi @alanzfu 👋 Would you be able to elaborate a bit more on how the ticket_metrics URL?

[alanzfu]

Apologies, I just realized that the zendesk__ticket_metrics URL refers to the URL of the ticket, whereas the ticket_metrics.url property from the API refers to the URL of the ticket_metrics JSON. Ignore comment!

[fivetran-joemarkiewicz]

Closing this issue as the merged PR above addresses the issue identified. Please feel free to open the ticket if this issue still persists.

[calder-holt]

@fivetran-joemarkiewicz we stumbled upon this thread because we've identified the same behavior the issue targets.

I see you mention a variable that was not added to a future release of using_zendesk_business_metrics_logic, is that still the case and is there a way to override the package to use the zendesk logic? We are having a hard time finding any reference to the variable in y'alls code base or on the branch mentioned above.

[fivetran-avinash]

Hi @calder-holt ! The variable appears to have been introduced for testing purposes only and was later removed in a later release after tests were validated. You can see in our DECISIONLOG our reasoning for deciding not to capture this data in the same way the Zendesk UI captures it, leading to the discrepancies you are seeing.

To override the package to use the zendesk logic, I'd take a look at this post on how to override the logic of one of our models while still leveraging the package as a whole. This should allow you to make the adjustments you need to achieve your desired outcome.

Let me know if you have any questions!