search

fivetran / dbt_zendesk

Fivetran's Zendesk Support dbt package
https://fivetran.github.io/dbt_zendesk/#!/overview
Apache License 2.0
26 stars 30 forks source link

zendesk fivetran DBT package is incorrectly calculating values from the source data #141

Open vinay-raju opened 9 months ago

vinay-raju commented 9 months ago

Is there an existing issue for this?

Describe the issue

zendesk fivetran DBT package is incorrectly calculating values from the source data -- there are several fields that are getting calculated with incorrect values as part of the fivetran DBT quickstart transformation for zendesk, I have already communicated to the zendesk support team and they guided me over here stating that this is due to an issue in the FIVETRAN DBT package

Relevant error log or model output

in correct values in the destination tables

Expected behavior

it is expected to get the data as is from the source but instead getting incorrect values

dbt Project configurations

nothing we are just using the default ones

Package versions

packages:

What database are you using dbt with?

snowflake

dbt Version

1.4

Additional Context

Hi, the main concern is regarding the data integrity of these following fields

1.number of tickets getting recorded as backlog per a day

there is a small difference in the number of tickets that are getting recorded as backlog every day int he attached sheet you can look at the backlog data which has the data fro destination(snowflake) and source(zendesk) and we can observe the difference in the number of records in each date, unfortunately we are not able to get the ticket id from the source but you can look at the number of tickets per day

2.requester wait time in business minutes

requester wait time is business minutes is matching for some data but is not consistent as we can see the difference for some of the tickets in the attached sheet

3.SLA target status and SLA metric status

the accepted values for SLA target status are active and completed as per the zendesk documentation and the accepted values for SLA metric status are achieved and breached while we don't have any direct fields mimicking these values we have is SLA active and is SLA breach and both are true or false fields, however these values are not matching with the values of zendesk as you can see in the document

4.first resolution time in minutes

first resolution time in calendar minutes is not matching as well with that of the source data it is shown in the sheet

5.requester wait time in calendar minutes

requester wait time in calendar minutes is matching for most of the cases but is only not matching for the cases where ticket status is either hold or open or new ans as per definition i understand that it is calculated only for the tickets that are having their statuses other than these three but i wonder why it is also being calculated for the tickets in these stages and showing a value and more over that value is not being matched

here is the document containing all the details -- https://docs.google.com/spreadsheets/d/1joYO92u4BZ-pZNjWrcA8XSTgq586VsZxVv9Rl2Fuv18/edit?usp=sharing

Are you willing to open a PR to help address this issue?

fivetran-joemarkiewicz commented 9 months ago

Hi @vinay-raju thank you for opening this issue with the much appreciated details around each issue you are experiencing. I see you have also scheduled time with our team tomorrow to discuss this live. My team and I will review the issues you identified above and we can discuss in more detail tomorrow.

Thanks and chat then!

fivetran-joemarkiewicz commented 9 months ago

Prior to our meeting tomorrow I was able to look into a few of the questions you highlighted above and have some findings that may be useful for you to know prior to our meeting. See below your questions and my responses:

1.number of tickets getting recorded as backlog per a day

This discrepancy is expected and noted within the DECISIONLOG of this data model. Essentially, Zendesk UI only reports backlog items for 23 hours in the day. This data model records backlog tickets for the full 24 hours. This is likely the result of the discrepancy you are seeing.

2.requester wait time in business minutes

It will likely be best to sync on this one in person.

3.SLA target status and SLA metric status

There currently is an open issue (Issue #131) where we have identified incorrect SLA metrics. We have developed fixes, but have not yet rolled out these changes. It would be great to go over our changes during the call and determine if the fixes we have prepared will address the errors you are seeing.

4.first resolution time in minutes 5.requester wait time in calendar minutes

Usually we do not see issues when it comes to requester wait time or resolution time in calendar minutes. We can sync on this tomorrow to determine what may be going on and if an update is required within the data model.

fivetran-joemarkiewicz commented 9 months ago

Thanks again @vinay-raju for meeting with @fivetran-avinash and myself today to go over the above questions.

Following that meeting there were a few action items needed before we dig further into these cases. Would you be able to share the results of the following:

We are aware that Zendesk calculates these metrics based off this article. However, this didn't seem to match the expected values in the Zendesk UI. If you can help clarify how these are being generated in the Zendesk UI that would be a huge help.

In the meantime, my team will continue working on the fix for the SLA discrepancies you noted. If you would find it useful to test these changes on your data before they go live let us know if you would be interested in exploring this option.

Thanks!

fivetran-joemarkiewicz commented 9 months ago

Hi @vinay-raju I hope all is well. I just wanted to follow up on the above message to see if you were able to dig into the cases mentioned to get clarification from Zendesk?

Additionally, the other concerns you had around SLAs being reported incorrectly should have been addressed within PR #136 which have been included in the latest v0.14.0 release of the package. Let me know if you have any questions around the SLAs!

fivetran-joemarkiewicz commented 8 months ago

Hi all,

Let me know if you have been able to upgrade and if you still see the same issues persisting. Additionally, feel free to share any insights you may have gotten from following up with Zendesk.

For the time being I will keep this ticket open but mark it as stale.

vinay-raju commented 8 months ago

Hi, sorry for the delayed response i am more than happy to update the package but last time when i have updated the package i have faced an issue where some the major data that we were using went missing from the package and this data is mostly from the external table called schedule from my current version so i just want to make sure this doesn't happen again if i update my package again can you help me on this??

fivetran-joemarkiewicz commented 8 months ago

Hi @vinay-raju! There was an issue previously where we were erronesouly removing tickets from the end models. This should have been addressed within the most recent release.

That being said, it is recommended to do some testing before upgrading your production environment to ensure the same issue you previously saw does not occur. Do you by chance have a test or staging environment or schema where you can run the upgraded package? Typically what we do internally is test the results in a new schema to ensure all looks accurate. If it does, then we move forward with upgrading the package version in our production schema.

Let me know if you have any questions when going through the testing process before fully upgrading. Thanks!

kinengetgo commented 3 months ago

Hey @fivetran-joemarkiewicz, I am facing an inconsistencies with the calculation of first_resolution_business_minutes where some values will not tally with the response called from the Zendesk API. I notice that some values that happen on a Monday will be calculated at 0 but the api returns a value. I am using dbt package 0.16.0. Thanks!

fivetran-joemarkiewicz commented 3 months ago

Thanks for chiming in here @kinengetgo. We would be happy to help here. Would you actually be able to open a separate issue via our support portal highlighting the issue you're seeing and some examples so we can more effectively help triage and understand what the issue is and what a possible solution could be.

Let me know if you have any questions. Thanks!

fivetran-catfritz commented 2 months ago

We have released v0.17.0 (link to release details), which should address issues with the *_business_minutes calculations.

fivetran-catfritz commented 1 month ago

We have released v0.18.0 (link to release details), which now can track historical schedule when that feature is enabled. It also fixes a bug with the model int_zendesk__schedule_spine where some date ranges were missing for some users. This may address additional discrepancies.