[Bug] <Missing business minutes metrics in zendesk support ticket metrics>

[elijamuel]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

we are currently using this zendesk package to manipulate the data that lands in our warehouse through DBT. The issue is that some of the data are missing specifically all business_minutes calculations in ticket metrics

The int tables they create in the same dataset are also not created unlike most of the other fields.

Relevant error log or model output

No response

Expected behavior

All the fields as listed in the package should be created/computed in the zendesk support ticket metrics

dbt Project configurations

vars:
zendesk_schema: landing_zendesk
using_schedules: False
using_domain_names: False
using_organization_tags: False

models:
zendesk:
+schema: staging_zendesk
zendesk_source:
+schema: staging_zendesk

Package versions

packages:

• package: fivetran/zendesk
  version: 0.11.0

What database are you using dbt with?

bigquery

dbt Version

version: '1.3.0'

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.

[fivetran-reneeli]

Hi @elijamuel! So to get the business_minutes fields, you'll need to set this variable to True. Currently in your config it is False. This is because business minutes relies on schedules being used.

using_schedules: True

It is outlined in the README, but I will make a note to make the connection between schedules and business minutes more clear in our next update to the package.

Let me know if you are able to see the fields populate after this!

[elijamuel]

Hi @fivetran-reneeli, thank you for the prompt response.

We have turned it on as advised but the business times are still not computed.
We checked the Zendesk support platform and have seen that one schedule (Hongkong Hours) is enabled in it (since October 22, 2023) but still had no extracted data (schedules and scheduly_holiday, thru Fivetran) and also no computed business times in it.

What should we do to enable it?

[elijamuel]

Closing the issue: schedule_holiday table has to be created for all business_minutes to be computed. Although, right now its computing incorrectly but that will be resolved with Fivetran support.

Thanks for the assistance!

[elijamuel]

Hi @fivetran-joemarkiewicz. repoening this because according to Fivetran Support, this issue I raised above falls under your scope. We have created the schedule_holiday and schedule tables but its still computing all business minutes as zero. Hope you can help us with this.

Although, right now its computing incorrectly but that will be resolved with Fivetran support.

cc. @IamElaya

[fivetran-joemarkiewicz]

Hi @elijamuel thanks for responding back to this issue. Would you be able to confirm if you have any data in the schedule_holiday or schedule tables? Similarly, do you have any data in the ticket_schedule table synced by the Fivetran connector?

[elijamuel]

Hi @fivetran-joemarkiewicz,

Here's the tables you requested:

• schedule_holiday contains 2 test holiday values encompassing the start date of all the tickets we have (May 1, 2023) and a recent one on November 1-3, 2023
• schedule contains below attached picture as values:
  
• ticket_schedule - apparently it was not resynced along the schedule and schedule_holiday tables. Will update if this resolves the issue

[elijamuel]

@fivetran-joemarkiewicz, resync-ing the ticket_schedule created no table and most likely contains no data.
Saw in the documentation that setting a schedule for tickets is only applicable for enterprise plans. Does this mean that lower billing plans cant have this functionality computed by the package?

[fivetran-joemarkiewicz]

Hi @elijamuel thanks for sharing more details. I believe you should still be able to gain access to the schedule feature in Zendesk regardless of having the Enterprise plan. Albeit my understanding of the various plans in Zendesk is slim to none, but I imagine if you have been able to create schedules, then you should have access to use those. Before going too far, are you able to view your business metrics in the Zendesk UI reporting tool? I would be curious if for a given ticket are you able to see the comparison of calendar vs business minutes?

Additionally, from my understanding the ticket_schedule table's purpose is to associate your various tickets with the corresponding schedule. However, if you only have one schedule, then that can be assumed to be the default schedule and our downstream model should make this connection. Would you be able to query an given ticket_id from the int_zendesk__ticket_schedules table in your warehouse and let me know what the results are? I would imagine you should see the Hong Kong timezone being populated for all the tickets.

[elijamuel]

Yes, the business metrics are accessible in Zendesk UI reporting tool.

As for the int_zendesk__ticket_schedules, there are available data in each ticket shown below

Would this mean that the business metrics in ticket_metrics should also be calculated correctly?

[fivetran-joemarkiewicz]

Thanks for sharing @elijamuel. Given this, I would assume the business metrics should be calculating properly. Would you be able to share one ticket as an example of the Zendesk UI properly recording the business minutes metrics and then the result for that same ticket from the end model of this Zendesk package? This will help me get a better picture of what may be going on.

If I am not able to identify what is going on or a solution following that, I may request we meet in over a video call to investigate more thoroughly. Thanks!

[elijamuel]

@fivetran-joemarkiewicz

Right now, all business metrics computed are all zeros which means almost all tickets we have are computed incorrectly.
Here are sample ids and their corresponding business metrics computed by the UI and by the package:

[fivetran-joemarkiewicz]

@elijamuel thanks so much for sharing and I see the issue you are encountering. I also just noticed you are using the v0.11.0 version of the Zendesk package. We actually identified some issues with that version a while back and have released some updates that should address the business metrics issues. Would you be able to upgrade to the latest version (v0.12.0) and let me know if you still see this issue?

Please note that when you upgrade I recommend running a dbt clean && dbt deps before rerunning the package in order to capture all the latest code updates.

If that doesn't seem to do the trick, you can reach out to my team via solutions@fivetran.com to communicate with us and we can schedule some time with you to meet over a video call to troubleshoot further. Let me know if upgrading the package helps!

[elijamuel]

@fivetran-joemarkiewicz, just to verify, can you send the step by step to upgrade to the latest versions? Should I have it deployed to prod before it will compute the correct values or will running it on a branch show the correct ones?

[fivetran-joemarkiewicz]

Hi @elijamuel I am not entirely sure what your environment setup is, but you could create a branch of your own dbt project and update the packages.yml to reference the latest Zendesk version. You can then run this and inspect the results to see if this looks good before pushing your changes to prod. As long as the underlying data is the same between prod and dev, you should be able to determine if the correct results are showing.

Let me know if you have any other questions. Thanks!

[IamElaya]

Hi @fivetran-joemarkiewicz, I have updated the zendesk package on a separate branch to v0.12.0 but the issue with the business metrics persists. I have sent an email to solutions@fivetran.com as well to see if we can troubleshoot this over video.

[fivetran-joemarkiewicz]

Hi @elijamuel thanks again for meeting with myself and @fivetran-reneeli today to troubleshoot the bug in more detail.

From our investigation, we found that the root of the issue seems to be that the Hong Kong schedule was being corrupted within the int_zendesk__schedule_spine model since the Hong Kong daylight savings time ended in 1979, and the package does not take into consideration that a timezone would have daylight savings for a period, and then stop. Therefore, the schedule spine stops for Hong Kong in 1979, and therefore does not report properly for business metrics for this timezone.

This is something we should be able to recreate on our end with out test data and will plan to have a branch that you may test in more detail in the coming week. Thanks again for your collaboration and helping us understand this scenario and how we should address it in the package. Be sure to follow here for more updates as we share them.

[IamElaya]

Hi @fivetran-joemarkiewicz, thank you to you and @fivetran-reneeli for helping troubleshoot, and for the summary of the issue discovered yesterday.

Sounds good, looking forward to your next update!

[fivetran-joemarkiewicz]

Hi @IamElaya I believe I may have found a solution! Would you be able to test the following version of the package in your packages.yml and let us know if this addresses the issues you are seeing?

packages:
  - git: https://github.com/fivetran/dbt_zendesk.git
    revision: bugfix/daylight-end-catch
    warn-unpinned: false 

Please note that once you update your packages.yml to reference the git branch as opposed to the dbt hub version of the Zendesk package you will need to also run dbt clean and then dbt deps to capture the updates.

Let us know if this resolves your issue. If so, we can work to incorporate these changes into the next release of the package. Thanks 😄

[IamElaya]

Hey Joe!

Thank you for this.
So I would add those lines of code in the root packages.yml like this?

[fivetran-joemarkiewicz]

@IamElaya that is very close to it! You would just want to remove (or comment out) the official zendesk version you have pinned. So it would be the following:

packages:
  - package: fivetrtan/quickbooks
    version: 0.11.0
  - package: fivetran/stripe
    version: 0.10.1
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  # - package: fivetran/zendesk
  #   version: 0.12.0
  - git: https://github.com/fivetran/dbt_zendesk.git
    revision: bugfix/daylight-end-catch
    warn-unpinned: false 

[IamElaya]

Gotcha! Sorry, was caught up with something yesterday.

The warn-unpinned line is giving me an error, would you happen to know how to troubleshoot it?

[fivetran-joemarkiewicz]

That shouldn't be an issue when you run dbt deps. That is just to ensure you don't get a warning when you run dbt deps. If you want, you can remove it but it shouldn't be an issue.

[fivetran-joemarkiewicz]

Hi @IamElaya I just wanted to check in and see if you were able to test the changes from within the branch?

[IamElaya]

Heya, I tried adding it today and the business minutes are still showing up as zero.

[fivetran-joemarkiewicz]

Hmm that is peculiar 🤔 I was able to recreate the issue locally and saw this branch addressed the issue. Did you run dbt deps after updating the packages.yml?

If you did, then I think it may make the most sense to meet again over a google hangout to discover why this adjustment hasn't solved the issue you are seeing.

[IamElaya]

Hmm yes let's do that over google hangout again! I may be missing some other step.

[fivetran-joemarkiewicz]

Thanks again for meeting with me today @IamElaya! Following our call from earlier it seems the fix proposed in the above branch does in fact correct the business metrics for your tickets in the Hong Kong timezone!

I will move forward with adding polishing the PR and work to include these changes in the next minor (v0.X.0) release of the dbt_zendesk package.

Thanks again for all your help!

[IamElaya]

No worries @fivetran-joemarkiewicz, and thank you for getting the fix for us. Appreciate it! Looking forward to the official fix!

[fivetran-joemarkiewicz]

@IamElaya thanks again for all the help with understanding and addressing this issue. The fix for this issue is now live within the v0.13.1 release of the dbt_zendesk package! Thanks again and please let us know if you see any other irregularities with the package.

As this issue has been addressed in the latest release, I will close out this ticket. Thanks again 😄